A small table is portrayed that gives on-time and delayed arrivals for Alaska and AM West at five West Coast airports. The problem is which airline has better reliability and does it depend on aggregation of counts.
In R, I am going to use packages tidyr and dplyr to delete the empty row, fill the missing names of airlines and pivot the data into one line per airline and city. After that, I am going to compare the percentage of delays overall and for each city using summary table and grouped bar chart, validating the numbers against the original source. The difficult parts are the messy wide format of the table that leads to generation of NA values that have to be dealt with and comma-separated numbers that have to be entered manually as integers.
Code Base
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(tidyverse)
Warning: package 'tidyverse' was built under R version 4.6.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
# A tibble: 10 × 6
airline city on_time delayed total delay_rate
<chr> <chr> <dbl> <dbl> <dbl> <dbl>
1 ALASKA Los Angeles 497 62 559 0.111
2 ALASKA Phoenix 221 12 233 0.0515
3 ALASKA San Diego 212 20 232 0.0862
4 ALASKA San Francisco 503 102 605 0.169
5 ALASKA Seattle 1841 305 2146 0.142
6 AM WEST Los Angeles 694 117 811 0.144
7 AM WEST Phoenix 4840 415 5255 0.0790
8 AM WEST San Diego 383 65 448 0.145
9 AM WEST San Francisco 320 129 449 0.287
10 AM WEST Seattle 201 61 262 0.233
The initial data table is large and has structural holes, hence, I clean it up in four main ways. Firstly, I removed the completely empty row that acts as a separator. Secondly, I populated the missing airlines by dragging the airline name downwards. Thirdly, I transformed the five city columns into long format. Lastly, I transformed the status to have total flights as a separate column for each airline and city.
# A tibble: 5 × 3
city ALASKA `AM WEST`
<chr> <dbl> <dbl>
1 Los Angeles 0.111 0.144
2 Phoenix 0.0515 0.0790
3 San Diego 0.0862 0.145
4 San Francisco 0.169 0.287
5 Seattle 0.142 0.233
Alaska has the lower delay rate in all five cities, which goes against the previous conclusion we drew from when we looked at the overall delay rates of just the airlines.
clean |>group_by(airline) |>mutate(share_of_flights = total /sum(total)) |>ungroup() |>select(airline, city, share_of_flights) |>pivot_wider(names_from = airline, values_from = share_of_flights)
# A tibble: 5 × 3
city ALASKA `AM WEST`
<chr> <dbl> <dbl>
1 Los Angeles 0.148 0.112
2 Phoenix 0.0617 0.727
3 San Diego 0.0615 0.0620
4 San Francisco 0.160 0.0621
5 Seattle 0.568 0.0363
The majority of the flights that Alaska flies are towards Seattle, which has relatively high delays for both the airlines whereas, for AM West, the majority of its flights are directed to Phoenix, which rarely sees any delay for either of the airlines. AM West’s overall average has been dragged down by its Phoenix flights despite being inferior to Alaska at every single airport.
Conclusion
AM West has the smaller overall percentage of delays, but Alaska has the smaller delay percentage at each of the five airports. From the point of view of an individual who will choose an airline to fly in a certain route, the comparison at the city level is the correct one, favoring Alaska, since the overall statistic mainly represents where each airline operates. The sample considered is only a snapshot of one moment in time and includes only five airports. Furthermore, there is no explanation for what makes delays different between airlines as weather and hub issues could be causes but were not checked here.