Load the Data

library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(tidyr)
library(ggplot2)

airlines <- read.csv(
  "https://raw.githubusercontent.com/mj-nurse/mnurse-data_607/refs/heads/main/assignment-05a-tidying-data/airline_delays.csv",
  na.strings = ""
)

airlines <- airlines %>%
  fill(Airline)

airlines
##   Airline  Status Los_Angeles Phoenix San_Diego San_Francisco Seattle
## 1  ALASKA on time         497     221       212           503    1841
## 2  ALASKA delayed          62      12        20           102     305
## 3 AM WEST on time         694    4840       383           320     201
## 4 AM WEST delayed         117     415        65           129      61

Tidy the Data

airlines_long <- airlines %>%
  pivot_longer(
    cols = Los_Angeles:Seattle,
    names_to = "City",
    values_to = "Flights"
  )

airlines_long
## # A tibble: 20 × 4
##    Airline Status  City          Flights
##    <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 AM WEST on time Los_Angeles       694
## 12 AM WEST on time Phoenix          4840
## 13 AM WEST on time San_Diego         383
## 14 AM WEST on time San_Francisco     320
## 15 AM WEST on time Seattle           201
## 16 AM WEST delayed Los_Angeles       117
## 17 AM WEST delayed Phoenix           415
## 18 AM WEST delayed San_Diego          65
## 19 AM WEST delayed San_Francisco     129
## 20 AM WEST delayed Seattle            61

Overall Airline Performance

overall_summary <- airlines_long %>%
  group_by(Airline, Status) %>%
  summarise(
    Total_Flights = sum(Flights),
    .groups = "drop"
  ) %>%
  group_by(Airline) %>%
  mutate(
    Percentage = round(
      Total_Flights / sum(Total_Flights) * 100,
      2
    )
  )

overall_summary
## # A tibble: 4 × 4
## # Groups:   Airline [2]
##   Airline Status  Total_Flights Percentage
##   <chr>   <chr>           <int>      <dbl>
## 1 ALASKA  delayed           501       13.3
## 2 ALASKA  on time          3274       86.7
## 3 AM WEST delayed           787       10.9
## 4 AM WEST on time          6438       89.1

Findings: AM West had the better on-time performance, with about 89.11% of flights arriving on time compared with 86.73% for Alaska.

Performance by City

city_summary <- airlines_long %>%
  group_by(Airline, City, Status) %>%
  summarise(
    Total_Flights = sum(Flights),
    .groups = "drop"
  ) %>%
  group_by(Airline, City) %>%
  mutate(
    Percentage = round(
      Total_Flights / sum(Total_Flights) * 100,
      2
    )
  )

city_summary
## # A tibble: 20 × 5
## # Groups:   Airline, City [10]
##    Airline City          Status  Total_Flights Percentage
##    <chr>   <chr>         <chr>           <int>      <dbl>
##  1 ALASKA  Los_Angeles   delayed            62      11.1 
##  2 ALASKA  Los_Angeles   on time           497      88.9 
##  3 ALASKA  Phoenix       delayed            12       5.15
##  4 ALASKA  Phoenix       on time           221      94.8 
##  5 ALASKA  San_Diego     delayed            20       8.62
##  6 ALASKA  San_Diego     on time           212      91.4 
##  7 ALASKA  San_Francisco delayed           102      16.9 
##  8 ALASKA  San_Francisco on time           503      83.1 
##  9 ALASKA  Seattle       delayed           305      14.2 
## 10 ALASKA  Seattle       on time          1841      85.8 
## 11 AM WEST Los_Angeles   delayed           117      14.4 
## 12 AM WEST Los_Angeles   on time           694      85.6 
## 13 AM WEST Phoenix       delayed           415       7.9 
## 14 AM WEST Phoenix       on time          4840      92.1 
## 15 AM WEST San_Diego     delayed            65      14.5 
## 16 AM WEST San_Diego     on time           383      85.5 
## 17 AM WEST San_Francisco delayed           129      28.7 
## 18 AM WEST San_Francisco on time           320      71.3 
## 19 AM WEST Seattle       delayed            61      23.3 
## 20 AM WEST Seattle       on time           201      76.7

Findings: When the airlines were compared within each city, Alaska had a higher on-time percentage than AM West in all five cities.

Comparing On-Time Performance by City

on_time_city <- city_summary %>%
  filter(Status == "on time")

ggplot(
  on_time_city,
  aes(x = City, y = Percentage, fill = Airline)
) +
  geom_col(position = "dodge") +
  labs(
    title = "On-Time Percentage by City and Airline",
    x = "City",
    y = "On-Time Percentage"
  )

## Conclusion

Overall, AM West had a higher on-time percentage than Alaska, but Alaska performed better in every individual city. This happened because the two airlines had very different numbers of flights in each city, which affected the overall percentages. AM West had many flights in Phoenix, where on-time performance was high, while Alaska had many flights in Seattle, where the rate was lower. This is an example of Simpson’s paradox, where the overall trend is different from the trends within separate groups.