Assignment 5A: Airline Delays, Tidying and Transforming Data
Author
Noelle
Published
September 25, 2026
Introduction
This report tidies and analyzes a small table of arrival delays for two airlines, Alaska and AM West, across five destinations (Los Angeles, Phoenix, San Diego, San Francisco, Seattle). The table comes from Numbersense by Kaiser Fung (McGraw-Hill, 2013), as given in the DATA 607 assignment. We recreate it as a CSV in its original “wide” layout (blank cells included), clean and reshape it with tidyr and dplyr, and compare the airlines’ delay percentages, first overall and then city by city. The two comparisons disagree, and we explain why.
The data file airline_delays.csv was typed in by hand from the source table and lives in this GitHub repository so the report is reproducible. We checked it against an independent total: 11,000 flights (3,775 Alaska + 7,225 AM West).
Approach
I will start with the airline delays table and kepep its original layout in a CSV file. I then will clean the blank rows and airline names and change the data into a format with one row for each airline, city, and delay category. From there, I will calculate the percentage of delayed flights for each airline overall and for each city. Finally, I will compare the results and explain why the overall comparison gives a different impression from the city-by-city comparison.
Step 1: Read the data
The CSV keeps the source layout on purpose: the airline name appears only on the first row of each pair, the first two header cells are blank, and a blank row separates the airlines.
read_csv() names the two blank headers ...1 and ...2 and keeps the spacer row as a row of NA values.
Step 2: Clean the table and fill in the missing airline names
We name the two columns, drop the spacer row, and copy each airline name down into the blank cells below it. The order matters: if we filled first, the spacer row would inherit “ALASKA” and would no longer be entirely NA, so it could not be dropped.
clean <- raw |>rename(airline =1, status =2) |>filter(!if_all(everything(), is.na)) |># drop the all-NA spacer row firstfill(airline, .direction ="down") # then fill the airline name downclean
# A tibble: 4 × 7
airline status `Los Angeles` Phoenix `San Diego` `San Francisco` Seattle
<chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
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
# A tibble: 20 × 4
airline status city flights
<chr> <chr> <chr> <dbl>
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
stopifnot(nrow(flights_long) ==20) # 2 airlines x 2 statuses x 5 citiesstopifnot(sum(flights_long$flights) ==11000)
Step 4: Compute delay percentages
We compare percentages, not counts, because AM West operates almost twice as many flights as Alaska. For each airline and city we put on_time and delayed side by side, then divide delayed flights by all flights. The overall rate sums the counts first and then divides; it is not the average of the five city percentages.
ggplot(overall, aes(airline, pct_delayed, fill = airline)) +geom_col(width =0.55) +geom_text(aes(label = scales::percent(pct_delayed, accuracy =0.1)), vjust =-0.4) +scale_y_continuous(labels = scales::percent, limits =c(0, 0.16)) +scale_fill_manual(values =c("ALASKA"="#1F4E79", "AM WEST"="#D98E04")) +labs(title ="Overall share of flights delayed", x =NULL, y ="Delayed flights") +theme_minimal() +theme(legend.position ="none")
When I combined all five cities, Alaska had 501 delays out of 3,775 flights, or 13.3%. AM West had 787 delays out of 7,225 flights, or 10.9%. Based on the overall rates, AM West performed better by about 2.4 percentage points.
# A tibble: 5 × 5
city ALASKA `AM WEST` gap_pp better
<chr> <dbl> <dbl> <dbl> <chr>
1 Los Angeles 0.111 0.144 -3.3 ALASKA
2 Phoenix 0.0515 0.0790 -2.7 ALASKA
3 San Diego 0.0862 0.145 -5.9 ALASKA
4 San Francisco 0.169 0.287 -11.9 ALASKA
5 Seattle 0.142 0.233 -9.1 ALASKA
stopifnot(nrow(city_compare) ==5)stopifnot(all(city_compare$better =="ALASKA"))stopifnot(all(abs(city_compare$gap_pp -c(-3.3, -2.7, -5.9, -11.9, -9.1)) <1e-9))ggplot(by_city, aes(city, pct_delayed, fill = airline)) +geom_col(position ="dodge") +geom_text(aes(label = scales::percent(pct_delayed, accuracy =0.1)),position =position_dodge(width =0.9), vjust =-0.3, size =3) +scale_y_continuous(labels = scales::percent, limits =c(0, 0.33)) +scale_fill_manual(values =c("ALASKA"="#1F4E79", "AM WEST"="#D98E04")) +labs(title ="Share of flights delayed, by city", x =NULL, y ="Delayed flights", fill =NULL) +theme_minimal() +theme(legend.position ="bottom")
When I checked each city separately, Alaska had a lower delay rate in all five cities. The differences ranged from 2.7 to 11.9 percentage points, with the largest gaps in San Francisco and Seattle.
Why the two comparisons disagree (Simpson’s paradox)
An airline’s overall delay rate is a weighted average of its city rates, and the two airlines use very different weights. The table shows how each airline’s flights are spread across the cities.
# The weighted sum of city rates reproduces the overall rate exactlycheck <- mix |>summarise(x =sum(share_of_airline * pct_delayed), .by = airline) |>pull(x)stopifnot(isTRUE(all.equal(check, overall$pct_delayed)))
Phoenix is the easiest city for both airlines, and 73% of AM West’s flights go there. Seattle and San Francisco are hard for both, and 57% of Alaska’s flights go to Seattle. To compare the airlines fairly, we give both the same city mix (each city’s share of all 11,000 flights) and re-weight their own city rates:
When I compared all flights, AM West looked better, with a delay rate of 10.9% versus 13.3% for Alaska. But Alaska had a lower delay rate in every city. This reversal is called Simpson’s paradox.
The difference comes from where each airline flew. About 73% of AM West flights went to Phoenix, where delays were lowest, while 57% of Alaska flights went to Seattle, where delays were more frequent. After I applied the same city mix to both airlines, the adjusted rates were 9.2% for Alaska and 14.5% for AM West.
Conclusions and Findings
When I compared all flights, AM West looked better, with a delay rate of 10.9% versus 13.3% for Alaska. But Alaska had a lower delay rate in every city. This reversal is called Simpson’s paradox. About 73% of AM West flights went to Phoenix, where delays were lowest, while 57% of Alaska flights went to Seattle, where delays were more frequent. Using the same city mix, I got adjusted rates of 9.2% for Alaska and 14.5% for AM West. I trusted the city-by-city comparison more because it compares the same destinations. Based on these data, I would choose Alaska. Before I trust an overall percentage, I needed to check the groups and their share of the total. My analysis covers only five cities, with no dates, flight distances, or definition of delayed. I would check more destinations, seasons, and times of day, then test whether the differences are statistically significant.
AI Use
Anthropic. (2026). Claude Sonnet 5 [Large language model]. https://claude.ai. Accessed September 24, 2026.
I used Claude to proofread my report, make my explanations clearer, and help me find errors when my R code did not run. I reviewed the suggested corrections and completed my final code, analysis, checks, and interpretation myself.
