Tidying and Transforming Data: Airline Arrival Delays

Why Alaska beats AM West in every city but loses overall

Author

Aniss Sahraoui

Published

October 4, 2026

1 Overview

This assignment takes a small table of airline arrival records, tidies it in R, and compares the two airlines’ delay rates. The table counts on time and delayed arrivals for two airlines, ALASKA and AM WEST, across five west-coast cities.

The interesting part is the result. Comparing the airlines overall gives the opposite answer to comparing them city by city, and the reason is worth understanding before trusting any summary statistic.

2 The Data File

I recreated the table as a CSV in exactly the shape it was given, including the blank cells. The airline name appears only on its first row, and an empty row separates the two airlines.

read_lines("https://raw.githubusercontent.com/AnissSahraoui/DATA607/main/Week5A/data/flights.csv") |>
  cat(sep = "\n")
,,Los Angeles,Phoenix,San Diego,San Francisco,Seattle
ALASKA,on time,497,221,212,503,1841
,delayed,62,12,20,102,305
,,,,,,
AM WEST,on time,694,4840,383,320,201
,delayed,117,415,65,129,61

The file is read from my GitHub repository, so this document runs on any machine.

raw <- read_csv(
  "https://raw.githubusercontent.com/AnissSahraoui/DATA607/main/Week5A/data/flights.csv",
  show_col_types = FALSE,
  name_repair = "unique_quiet"
)

raw
…1 …2 Los Angeles Phoenix San Diego San Francisco Seattle
ALASKA on time 497 221 212 503 1841
NA delayed 62 12 20 102 305
NA NA NA NA NA NA NA
AM WEST on time 694 4840 383 320 201
NA delayed 117 415 65 129 61

This is not tidy data. The first two columns have no names, the airline is missing on three rows, one row is entirely empty, and the five cities are spread across columns instead of being values of a single variable.

3 Tidying

3.1 Filling in the missing airline names

fill() carries the last non-missing value downward, which is exactly what the layout implies: the rows under ALASKA belong to ALASKA. The empty separator row is dropped by removing rows with no status.

filled <- raw |>
  rename(airline = 1, status = 2) |>
  fill(airline) |>
  filter(!is.na(status))

filled
airline status Los Angeles Phoenix San Diego San Francisco Seattle
ALASKA on time 497 221 212 503 1841
ALASKA delayed 62 12 20 102 305
AM WEST on time 694 4840 383 320 201
AM WEST delayed 117 415 65 129 61

3.2 Wide to long

pivot_longer() turns the five city columns into one city column, giving one row per airline, city and status.

long <- filled |>
  pivot_longer(
    cols = -c(airline, status),
    names_to = "city",
    values_to = "flights"
  )

head(long, 6)
airline status city flights
ALASKA on time Los Angeles 497
ALASKA on time Phoenix 221
ALASKA on time San Diego 212
ALASKA on time San Francisco 503
ALASKA on time Seattle 1841
ALASKA delayed Los Angeles 62

3.3 One row per airline and city

For comparing delays it is more useful to have on-time and delayed counts side by side, so I pivot the two statuses back into columns and calculate the totals and percentages.

flights <- long |>
  mutate(status = str_replace(status, "on time", "on_time")) |>
  pivot_wider(names_from = status, values_from = flights) |>
  mutate(
    total      = on_time + delayed,
    delay_rate = delayed / total
  )

flights |> mutate(delay_rate = percent(delay_rate, 0.1))
airline city on_time delayed total delay_rate
ALASKA Los Angeles 497 62 559 11.1%
ALASKA Phoenix 221 12 233 5.2%
ALASKA San Diego 212 20 232 8.6%
ALASKA San Francisco 503 102 605 16.9%
ALASKA Seattle 1841 305 2146 14.2%
AM WEST Los Angeles 694 117 811 14.4%
AM WEST Phoenix 4840 415 5255 7.9%
AM WEST San Diego 383 65 448 14.5%
AM WEST San Francisco 320 129 449 28.7%
AM WEST Seattle 201 61 262 23.3% 
tibble(
  rows               = nrow(flights),
  flights_in_table   = sum(flights$total),
  counts_match_input = sum(flights$total) == sum(raw[, 3:7], na.rm = TRUE),
  missing_values     = sum(is.na(flights))
)
rows flights_in_table counts_match_input missing_values
10 11000 TRUE 0

Every count from the original table is accounted for, with nothing lost in the reshaping.

4 Comparing the Airlines Overall

overall <- flights |>
  group_by(airline) |>
  summarise(
    on_time    = sum(on_time),
    delayed    = sum(delayed),
    total      = sum(total),
    .groups    = "drop"
  ) |>
  mutate(delay_rate = delayed / total)

overall |> mutate(delay_rate = percent(delay_rate, 0.1))
airline on_time delayed total delay_rate
ALASKA 3274 501 3775 13.3%
AM WEST 6438 787 7225 10.9% 
ggplot(overall, aes(x = delay_rate, y = fct_rev(airline), fill = airline)) +
  geom_col(width = 0.55) +
  geom_text(aes(label = percent(delay_rate, 0.1)), hjust = 1.15,
            color = "white", fontface = "bold", size = 4) +
  scale_fill_manual(values = c(ALASKA = col_alaska, `AM WEST` = col_amwest), guide = "none") +
  scale_x_continuous(labels = percent_format(), expand = expansion(mult = c(0, 0.05))) +
  labs(x = "Delayed arrivals", y = NULL) +
  theme_report +
  theme(panel.grid.major.y = element_blank())
Figure 1: Overall percentage of arrivals that were delayed.

Overall, AM WEST looks better. 10.9% of its arrivals were delayed, against 13.3% for ALASKA, a gap of 2.4%. Percentages matter here: AM WEST recorded 7,225 arrivals against ALASKA’s 3,775, so comparing raw counts of delays would say more about airline size than about punctuality.

5 Comparing the Airlines City by City

by_city <- flights |>
  select(airline, city, delay_rate) |>
  pivot_wider(names_from = airline, values_from = delay_rate) |>
  mutate(difference = `AM WEST` - ALASKA)

by_city |>
  mutate(across(where(is.numeric), ~ percent(.x, 0.1)))
city ALASKA AM WEST difference
Los Angeles 11.1% 14.4% 3.3%
Phoenix 5.2% 7.9% 2.7%
San Diego 8.6% 14.5% 5.9%
San Francisco 16.9% 28.7% 11.9%
Seattle 14.2% 23.3% 9.1% 
flights |>
  mutate(city = fct_reorder(city, delay_rate)) |>
  ggplot(aes(x = delay_rate, y = city, fill = airline)) +
  geom_col(position = position_dodge(width = 0.7), width = 0.62) +
  geom_text(aes(label = percent(delay_rate, 0.1)),
            position = position_dodge(width = 0.7), hjust = -0.12, size = 3.2) +
  scale_fill_manual(values = c(ALASKA = col_alaska, `AM WEST` = col_amwest), name = NULL) +
  scale_x_continuous(labels = percent_format(), limits = c(0, 0.34),
                     expand = expansion(mult = c(0, 0.02))) +
  labs(x = "Delayed arrivals", y = NULL) +
  theme_report +
  theme(legend.position = "top", panel.grid.major.y = element_blank())
Figure 2: Percentage of arrivals delayed, by city. ALASKA is lower in all five.

City by city, the answer reverses. ALASKA has a lower delay rate in every one of the five cities. The gap ranges from 2.7% in Phoenix to 11.9% in San Francisco. Both airlines struggle in the same places: San Francisco and Seattle are the worst for both, and Phoenix is the best for both.

6 The Discrepancy, and Why It Happens

6.1 Describing it

Alaska is better in Los Angeles, Phoenix, San Diego, San Francisco and Seattle, yet worse overall. Nothing is wrong with the arithmetic: both statements are true of the same data. This reversal is known as Simpson’s paradox.

6.2 Explaining it

The overall rate is not a plain average of the five cities. It is an average weighted by how many flights each airline runs in each city, and the two airlines fly very different routes.

share <- flights |>
  group_by(airline) |>
  mutate(share_of_flights = total / sum(total)) |>
  ungroup()

share |>
  select(airline, city, total, share_of_flights) |>
  pivot_wider(names_from = airline, values_from = c(total, share_of_flights)) |>
  mutate(across(starts_with("share"), ~ percent(.x, 0.1)))
city total_ALASKA total_AM WEST share_of_flights_ALASKA share_of_flights_AM WEST
Los Angeles 559 811 14.8% 11.2%
Phoenix 233 5255 6.2% 72.7%
San Diego 232 448 6.1% 6.2%
San Francisco 605 449 16.0% 6.2%
Seattle 2146 262 56.8% 3.6% 
city_order <- flights |>
  group_by(city) |>
  summarise(rate = sum(delayed) / sum(total)) |>
  arrange(rate) |>
  pull(city)

share |>
  mutate(city = factor(city, levels = city_order)) |>
  ggplot(aes(x = city, y = share_of_flights, fill = airline)) +
  geom_col(position = position_dodge(width = 0.7), width = 0.62) +
  geom_text(aes(label = percent(share_of_flights, 1)),
            position = position_dodge(width = 0.7), vjust = -0.4, size = 3.2) +
  scale_fill_manual(values = c(ALASKA = col_alaska, `AM WEST` = col_amwest), name = NULL) +
  scale_y_continuous(labels = percent_format(), limits = c(0, 0.85),
                     expand = expansion(mult = c(0, 0.02))) +
  labs(x = "Cities, least to most delay-prone", y = "Share of the airline's arrivals") +
  theme_report +
  theme(legend.position = "top", panel.grid.major.x = element_blank())
Figure 3: Where each airline flies, as a share of its own arrivals. The cities are ordered by how delay-prone they are overall.

This is the whole explanation:

  • AM WEST concentrates on Phoenix, its hub, which takes 72.7% of its arrivals. Phoenix is the easiest airport in this data, with a 7.8% delay rate across both airlines.
  • ALASKA concentrates on Seattle, its hub, which takes 56.8% of its arrivals, plus another 16.0% in San Francisco. Those are two of the three most delay-prone cities here; Seattle alone runs at 15.2%.

So AM WEST’s overall figure is dominated by its easiest airport, while ALASKA’s is dominated by its hardest ones. AM WEST looks better overall not because it handles any city better, but because it flies where delays are rare.

6.3 Removing the route effect

A fair comparison asks what AM WEST’s overall rate would be if it flew the same mix of cities as ALASKA. That is a weighted average of AM WEST’s own city rates, using ALASKA’s flight counts as the weights.

weights <- flights |> filter(airline == "ALASKA") |> select(city, alaska_flights = total)

standardised <- flights |>
  filter(airline == "AM WEST") |>
  left_join(weights, by = "city") |>
  summarise(rate = sum(delay_rate * alaska_flights) / sum(alaska_flights)) |>
  pull(rate)

tibble(
  comparison = c("ALASKA, actual",
                 "AM WEST, actual",
                 "AM WEST, if it flew ALASKA's mix of cities"),
  delay_rate = percent(c(rate("ALASKA"), rate("AM WEST"), standardised), 0.1)
)
comparison delay_rate
ALASKA, actual 13.3%
AM WEST, actual 10.9%
AM WEST, if it flew ALASKA’s mix of cities 21.4%

Once both airlines are put on the same routes, AM WEST’s delay rate rises from 10.9% to 21.4%, well above ALASKA’s 13.3%. The city-by-city comparison is the honest one, and the overall figure was measuring where each airline flies rather than how well it flies.

7 Conclusions

  • Tidying mattered. The source table buried the airline name in blank cells and spread the cities across columns. fill() and pivot_longer() turned it into one row per airline, city and status, after which every comparison was a single group_by().
  • Percentages, not counts. AM WEST recorded nearly twice as many arrivals as ALASKA, so counts of delays would have been misleading on their own.
  • The two comparisons disagree. ALASKA is better in all five cities, yet worse overall. The overall rate is weighted by route mix, and AM WEST runs 72.7% of its flights through the least delay-prone airport in the data.
  • ALASKA is the better airline here. Standardising to a common mix of cities confirms it: AM WEST would run at 21.4% on ALASKA’s routes, against ALASKA’s 13.3%.

The practical lesson is that an aggregate can reverse the truth of every group inside it. Whenever groups are pooled, it is worth asking what the pooling is weighted by.