Task

Submitted data file with at least some count analysis (+45)

Recreated File in Same Format as given, including missing data where there were empty cells in source data. (+5)

Placed file in Internet-accessible location, such as a publicly-accessible GitHub repo. Also OK (if less scalable) to generated and populated data frame from code. (+5)

Provided code to populate missing data. (+5)

Transformed data from wide to long format. (+10)

Compared percentage (not just counts) of either delays or arrival rates for two airlines overall. [Either with a chart, or a table; both would be exemplary]. Include text summarizes findings from this comparison. (+10)

Compared percentage (not just counts) of either delays or arrival rates for two airlines across five cities. [Either with a chart, or a table; both would be exemplary]. Include text summarizes findings from this comparison. (+10)

Describe discrepancy between comparing two airlines’ flight performances city-by-city and overall. (+5)

Assignment Tidying and Transforming Data

Explain discrepancy between comparing two airlines’ flight performances city-by-city and overall.(+5)

The chart above describes arrival delays for two airlines across five destinations. Your task is to: (1) Create a .CSV file (or optionally, a MySQL database!) that includes all of the information above. You’re encouraged to use a “wide” structure similar to how the information appears above, so that you can practice tidying and transformations as described below. (2) Read the information from your .CSV file into R, and use tidyr and dplyr as needed to tidy and transform your data. (3) Perform analysis to compare the arrival delays for the two airlines.

Approach

Step 1: Create database and table to insert given two airlines delay data (wide structure data) in postgreSQL. Step 2: Connect R to PostgreSQL Step 3: Read a SQL table into R Step 4: Save the SQL table as CSV Step 5: Read wide structure data from github into R. Step 6: Transform wide data to long structure data. Step 7: Compare two airlines arrival delays across five cities using plot percentage

Anticipated Challenges

converting wide structure data to long structure. Making comparison between two airlines across all cities.

Code Base

Load necessary libraries

library(DBI)
library(RPostgres)
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.2.0     ✔ readr     2.2.0
## ✔ forcats   1.0.1     ✔ stringr   1.6.0
## ✔ ggplot2   4.0.3     ✔ tibble    3.3.1
## ✔ lubridate 1.9.5     ✔ tidyr     1.3.2
## ✔ purrr     1.2.1     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

Create Database, Table, and Insert Data

The following SQL code creates the airlines_arrival_delay table, inserts the data provided in the assignment, and displays all the records in the table.

CREATE TABLE airlines_arrival_delay(“airline” VARCHAR(50), status VARCHAR(50), los_angeles Int,phoenix Int, san_diego Int,san_francisco Int,seattle Int); INSERT INTO airlines_arrival_delay(airline,status,los_angeles,phoenix,san_diego,san_francisco,seattle)VALUES (‘ALASKA’,‘on time’,497,221,212,503,1841), (NULL,‘delayed’,62,12,20,102,305), (NULL, NULL, NULL, NULL, NULL, NULL, NULL), (‘AMWEST’,‘on time’,694,4840,383,320,201), (NULL,‘delayed’,117,415,65,129,61); SELECT * FROM airlines_arrival_delay;

Connect R to PostgreSQL

con <- dbConnect(
  RPostgres::Postgres(),
  dbname = "Airlines_delay",
  host = "localhost",
  port = 5432,
  user = "postgres",
  password = Sys.getenv("DB_PASSWORD")
)
dbListTables(con)
## [1] "airlines_arrival_delay"
airlines_df <- dbReadTable(con, "airlines_arrival_delay")
head(airlines_df)
##   airline  status los_angeles phoenix san_diego san_francisco seattle
## 1  ALASKA on time         497     221       212           503    1841
## 2    <NA> delayed          62      12        20           102     305
## 3    <NA>    <NA>          NA      NA        NA            NA      NA
## 4  AMWEST on time         694    4840       383           320     201
## 5    <NA> delayed         117     415        65           129      61
#set first and second columns name empty.
names(airlines_df)[1:2] <- c(" ", " ")
write.csv(airlines_df, file="airlines_arrival_delay.csv", row.names = FALSE)

Load airline arrival delay dataset

url<-"https://raw.githubusercontent.com/lhamo07/Data-607-Assignment/refs/heads/main/Assignment-5A/airlines_arrival_delay.csv"
airlines_arrival_delay_df<-read.csv(file=url)
airlines_arrival_delay_df
##       X.    X..1 los_angeles phoenix san_diego san_francisco seattle
## 1 ALASKA on time         497     221       212           503    1841
## 2   <NA> delayed          62      12        20           102     305
## 3   <NA>    <NA>          NA      NA        NA            NA      NA
## 4 AMWEST on time         694    4840       383           320     201
## 5   <NA> delayed         117     415        65           129      61

Rename the first two un-labelled columns as “airline” and “status”

colnames(airlines_arrival_delay_df)[1:2] <- c("airline", "status")
airlines_arrival_delay_df
##   airline  status los_angeles phoenix san_diego san_francisco seattle
## 1  ALASKA on time         497     221       212           503    1841
## 2    <NA> delayed          62      12        20           102     305
## 3    <NA>    <NA>          NA      NA        NA            NA      NA
## 4  AMWEST on time         694    4840       383           320     201
## 5    <NA> delayed         117     415        65           129      61
airline_long_structure<-airlines_arrival_delay_df%>%
  fill(airline)%>%
  pivot_longer(cols=!(airline:status),
               names_to="city",
               values_to="flight_count",
                   values_drop_na = TRUE

               )
airline_long_structure
## # A tibble: 20 × 4
##    airline status  city          flight_count
##    <chr>   <chr>   <chr>                <int>
##  1 ALASKA  on time los_angeles            497
##  2 ALASKA  on time phoenix                221
##  3 ALASKA  on time san_diego              212
##  4 ALASKA  on time san_francisco          503
##  5 ALASKA  on time seattle               1841
##  6 ALASKA  delayed los_angeles             62
##  7 ALASKA  delayed phoenix                 12
##  8 ALASKA  delayed san_diego               20
##  9 ALASKA  delayed san_francisco          102
## 10 ALASKA  delayed seattle                305
## 11 AMWEST  on time los_angeles            694
## 12 AMWEST  on time phoenix               4840
## 13 AMWEST  on time san_diego              383
## 14 AMWEST  on time san_francisco          320
## 15 AMWEST  on time seattle                201
## 16 AMWEST  delayed los_angeles            117
## 17 AMWEST  delayed phoenix                415
## 18 AMWEST  delayed san_diego               65
## 19 AMWEST  delayed san_francisco          129
## 20 AMWEST  delayed seattle                 61

First, I used fill(airline) to fill in the missing airline names with the corresponding airline name above. Then, I used pivot_longer() to convert the data from a wide structure to a long structure. In cols = !(airline:status), all columns except the columns from airline through status are selected for transformation. The selected column names are stored in the new city column using names_to = “city”, and their corresponding values are stored in the flight_count column using values_to = “flight_count”. Finally, values_drop_na = TRUE removes rows where the flight count value is missing NA ### Percentage of delays rates for ALASKA airlines

alaska_summary<-airline_long_structure%>%
filter(airline=="ALASKA")%>%
  summarize(
    airline="ALASKA",
    total_delayed=sum(flight_count[status=='delayed']),
    total_on_time = sum(flight_count[status == "on time"]),
    grand_total   = total_delayed + total_on_time,
    delay_percent = round((total_delayed / grand_total) * 100,digits=2)
    )
alaska_summary
## # A tibble: 1 × 5
##   airline total_delayed total_on_time grand_total delay_percent
##   <chr>           <int>         <int>       <int>         <dbl>
## 1 ALASKA            501          3274        3775          13.3

Percentage of delays rates for AMWEST airlines

amwest_summary<-airline_long_structure%>%
filter(airline=="AMWEST")%>%
  summarize(
    airline="AMWEST",
    total_delayed=sum(flight_count[status=='delayed']),
    total_on_time = sum(flight_count[status == "on time"]),
    grand_total   = total_delayed + total_on_time,
    delay_percent = round((total_delayed / grand_total) * 100,digits=2)
    )
amwest_summary
## # A tibble: 1 × 5
##   airline total_delayed total_on_time grand_total delay_percent
##   <chr>           <int>         <int>       <int>         <dbl>
## 1 AMWEST            787          6438        7225          10.9
percentage_summary<-bind_rows(alaska_summary,amwest_summary)
percentage_summary
## # A tibble: 2 × 5
##   airline total_delayed total_on_time grand_total delay_percent
##   <chr>           <int>         <int>       <int>         <dbl>
## 1 ALASKA            501          3274        3775          13.3
## 2 AMWEST            787          6438        7225          10.9

Compared percentage of delays rates for two airlines overall.

ggplot(percentage_summary,aes(x=airline,y=delay_percent,fill=airline))+
  geom_col(width=0.5)+
   geom_text(
    aes(label = paste0(delay_percent, "%")),
    vjust = -0.5)+
    labs(
      title = "Overall Arrival Delay Percentage by Airline",
    x = "Airline",
    y = "Overall Delay Percentage (%)"
    )+
    theme_minimal()

From the above plot, I found that the overall delay percentage for ALASKA Airline is 13.27%, while AMWEST Airline is 10.83%. Based on this comparison, I would say that AMWEST Airlines appears to be more reliable for passengers who are concerned about flight delays.

Compared percentage of delays rates for two airlines across five cities.

city_summary<-airline_long_structure%>%
group_by(airline,city)%>%
    mutate(city_total=sum(flight_count),
           delay_percentage=round((flight_count/city_total)*100,digits=2))%>%
  

  filter(status=="delayed")
  
 
city_summary
## # A tibble: 10 × 6
## # Groups:   airline, city [10]
##    airline status  city          flight_count city_total delay_percentage
##    <chr>   <chr>   <chr>                <int>      <int>            <dbl>
##  1 ALASKA  delayed los_angeles             62        559            11.1 
##  2 ALASKA  delayed phoenix                 12        233             5.15
##  3 ALASKA  delayed san_diego               20        232             8.62
##  4 ALASKA  delayed san_francisco          102        605            16.9 
##  5 ALASKA  delayed seattle                305       2146            14.2 
##  6 AMWEST  delayed los_angeles            117        811            14.4 
##  7 AMWEST  delayed phoenix                415       5255             7.9 
##  8 AMWEST  delayed san_diego               65        448            14.5 
##  9 AMWEST  delayed san_francisco          129        449            28.7 
## 10 AMWEST  delayed seattle                 61        262            23.3
ggplot(city_summary,aes(x=city,y=delay_percentage,fill=airline))+
  geom_col(width=0.5,position="dodge")+
  geom_text(
    aes(label = paste0(delay_percentage, "%")),
    vjust = -0.5)+
    labs(
      title = "Overall Arrival Delay Percentage by Airline",
    x = "Airline",
    y = "Overall Delay Percentage (%)"
    )+

    theme_minimal()

From the above plot, I can conclude that Phoenix has the lowest flight delay percentage for both airlines. San Francisco has the highest delay percentage for AMWEST Airlines. The remaining cities have relatively similar flight delay percentages for both airlines.

Conclusion

Although the overall percentage of delays for ALASKA Airlines is higher than AMWEST Airlines, the city level comparison shows that AMWEST has a higher delay percentage in some cities. Therefore, if I were planning a trip, I would compare the delay percentages by city rather than relying only on the overall delay percentage.