Assignment 5 - Airline Delays: Tidying and Transforming Data
Sarah Abdelrahman
2026-10-04
Introduction
This analysis examines arrival delays for Alaska Airlines and AM West across five destinations: Los Angeles, Phoenix, San Diego, San Francisco, and Seattle. The goal is to tidy and transform the original data and compare the airlines’ overall performance with their performance across individual destinations.
The data are based on the airline-delay table from Numbersense by Kaiser Fung, McGraw Hill, 2013.
Load Packages and Data
The airline delay data were recreated from the source table in wide format and stored in a publicly accessible GitHub repository.
The raw CSV file is available at:
library(tidyverse)
url <- "https://raw.githubusercontent.com/sarahcunyms2026-source/Week-5-Airline-Delays/refs/heads/main/airline_delays.csv"
airlines <- read.csv(
url,
na.strings = "",
strip.white = TRUE
)
airlines## 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 AM WEST on time 694 4840 383 320 201
## 4 <NA> delayed 117 415 65 129 61Inspecting the Data
Before transforming the data, I checked its structure and examined it for missing values. This helps verify that the data were imported correctly and identifies any cleanup that may be required.
str(airlines)## 'data.frame': 4 obs. of 7 variables:
## $ Airline : chr "ALASKA" NA "AM WEST" NA
## $ Status : chr "on time" "delayed" "on time" "delayed"
## $ Los_Angeles : int 497 62 694 117
## $ Phoenix : int 221 12 4840 415
## $ San_Diego : int 212 20 383 65
## $ San_Francisco: int 503 102 320 129
## $ Seattle : int 1841 305 201 61colSums(is.na(airlines))## Airline Status Los_Angeles Phoenix San_Diego
## 2 0 0 0 0
## San_Francisco Seattle
## 0 0The inspection identified two missing values in the
Airline column. These correspond to the delayed rows in the
original source table, where the airline names were intentionally left
blank. These blank cells were preserved when recreating the CSV
file.
Handling Missing Data
To populate the missing airline names, I used fill()
from the tidyr package. This carries the airline name from
the preceding row down into the corresponding delayed row.
airlines_clean <- airlines %>%
fill(Airline, .direction = "down")
airlines_clean## 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 61I then checked the cleaned dataset to confirm that the missing values had been populated successfully.
colSums(is.na(airlines_clean))## Airline Status Los_Angeles Phoenix San_Diego
## 0 0 0 0 0
## San_Francisco Seattle
## 0 0After using fill(), no missing values remained in the
dataset.
Transforming the Data from Wide to Long Format
The original dataset is in wide format, with each destination stored
in a separate column. To create a tidy dataset for analysis, I used
pivot_longer() to combine the five destination columns into
two variables: Destination and Flights.
airlines_long <- airlines_clean %>%
pivot_longer(
cols = Los_Angeles:Seattle,
names_to = "Destination",
values_to = "Flights"
) %>%
mutate(
Destination = str_replace_all(Destination, "_", " ")
)
airlines_long## # A tibble: 20 × 4
## Airline Status Destination 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 61The resulting long-format dataset contains one row for each combination of airline, flight status, and destination. This structure makes it easier to group, summarize, and compare airline performance.
Count Analysis
Before comparing percentages, I summarized the number of on-time and delayed flights for each airline across all five destinations. This provides an initial view of the overall flight volume and delay counts.
overall_counts <- airlines_long %>%
group_by(Airline, Status) %>%
summarise(
Total_Flights = sum(Flights),
.groups = "drop"
)
knitr::kable(
overall_counts,
caption = "Overall Flight Counts by Airline and Status"
)| Airline | Status | Total_Flights |
|---|---|---|
| ALASKA | delayed | 501 |
| ALASKA | on time | 3274 |
| AM WEST | delayed | 787 |
| AM WEST | on time | 6438 |
Alaska had 501 delayed flights and 3,274 on-time flights. AM West had 787 delayed flights and 6,438 on-time flights.
However, these counts should not be compared by themselves because AM West operated substantially more flights overall than Alaska. Therefore, percentages provide a more appropriate comparison.
Overall Delay Percentage
Because the two airlines operated different numbers of flights, comparing delay counts alone would not provide a fair comparison. Therefore, I calculated the percentage of flights that were delayed for each airline.
overall_delay <- airlines_long %>%
group_by(Airline) %>%
summarise(
Total_Flights = sum(Flights),
Delayed_Flights = sum(Flights[Status == "delayed"]),
Delay_Percentage = Delayed_Flights / Total_Flights * 100,
.groups = "drop"
)
overall_delay_table <- overall_delay %>%
mutate(
Delay_Percentage = round(Delay_Percentage, 2)
)
knitr::kable(
overall_delay_table,
caption = "Overall Delay Percentage by Airline"
)| Airline | Total_Flights | Delayed_Flights | Delay_Percentage |
|---|---|---|---|
| ALASKA | 3775 | 501 | 13.27 |
| AM WEST | 7225 | 787 | 10.89 |
Overall Comparison
Although AM West had more delayed flights than Alaska (787 compared with 501), AM West also operated substantially more flights overall.
When the number of delayed flights is considered as a percentage of total flights, Alaska had an overall delay rate of approximately 13.27%, while AM West had an overall delay rate of approximately 10.89%.
Therefore, based only on the overall delay percentages, AM West appears to have better overall arrival performance than Alaska.
Overall Delay Percentage Visualization
ggplot(
overall_delay,
aes(
x = Airline,
y = Delay_Percentage,
fill = Airline
)
) +
geom_col(width = 0.6) +
geom_text(
aes(
label = paste0(
round(Delay_Percentage, 2),
"%"
)
),
vjust = -0.5
) +
labs(
title = "Overall Delay Percentage by Airline",
x = "Airline",
y = "Delay Percentage"
) +
scale_y_continuous(
expand = expansion(mult = c(0, 0.15))
) +
theme_minimal() +
theme(
legend.position = "none"
)
Delay Percentage by Destination
To determine whether the overall results are consistent across individual destinations, I calculated the delay percentage for each airline within each of the five destinations.
city_delay <- airlines_long %>%
group_by(Airline, Destination) %>%
summarise(
Total_Flights = sum(Flights),
Delayed_Flights = sum(Flights[Status == "delayed"]),
Delay_Percentage = Delayed_Flights / Total_Flights * 100,
.groups = "drop"
)
city_delay## # A tibble: 10 × 5
## Airline Destination Total_Flights Delayed_Flights Delay_Percentage
## <chr> <chr> <int> <int> <dbl>
## 1 ALASKA Los Angeles 559 62 11.1
## 2 ALASKA Phoenix 233 12 5.15
## 3 ALASKA San Diego 232 20 8.62
## 4 ALASKA San Francisco 605 102 16.9
## 5 ALASKA Seattle 2146 305 14.2
## 6 AM WEST Los Angeles 811 117 14.4
## 7 AM WEST Phoenix 5255 415 7.90
## 8 AM WEST San Diego 448 65 14.5
## 9 AM WEST San Francisco 449 129 28.7
## 10 AM WEST Seattle 262 61 23.3City-by-City Comparison
To make the comparison easier to read, I transformed the results so that the two airlines appear side by side for each destination.
city_comparison <- city_delay %>%
select(
Airline,
Destination,
Delay_Percentage
) %>%
mutate(
Delay_Percentage = round(Delay_Percentage, 2)
) %>%
pivot_wider(
names_from = Airline,
values_from = Delay_Percentage
)
knitr::kable(
city_comparison,
caption = "Delay Percentages by Destination"
)| Destination | ALASKA | AM WEST |
|---|---|---|
| Los Angeles | 11.09 | 14.43 |
| Phoenix | 5.15 | 7.90 |
| San Diego | 8.62 | 14.51 |
| San Francisco | 16.86 | 28.73 |
| Seattle | 14.21 | 23.28 |
The city-by-city comparison shows that Alaska had a lower delay percentage than AM West in every destination.
Alaska’s delay percentages were 11.09% in Los Angeles, 5.15% in Phoenix, 8.62% in San Diego, 16.86% in San Francisco, and 14.21% in Seattle.
In comparison, AM West’s delay percentages were 14.43% in Los Angeles, 7.90% in Phoenix, 14.51% in San Diego, 28.73% in San Francisco, and 23.28% in Seattle.
Therefore, when the airlines are compared within each individual destination, Alaska performed better in all five cities.
Delay Percentage by Destination Visualization
ggplot(
city_delay,
aes(
x = Destination,
y = Delay_Percentage,
fill = Airline
)
) +
geom_col(
position = "dodge"
) +
geom_text(
aes(
label = paste0(
round(Delay_Percentage, 2),
"%"
)
),
position = position_dodge(width = 0.9),
vjust = -0.4,
size = 3
) +
labs(
title = "Delay Percentage by Airline and Destination",
x = "Destination",
y = "Delay Percentage",
fill = "Airline"
) +
scale_y_continuous(
expand = expansion(mult = c(0, 0.15))
) +
theme_minimal() +
theme(
axis.text.x = element_text(
angle = 45,
hjust = 1
)
)
Overall vs. City-by-City Discrepancy
The results show an important discrepancy between the overall comparison and the city-by-city comparison. Overall, AM West appears to perform better, with a delay rate of approximately 10.89%, compared with 13.27% for Alaska. However, when the airlines are compared separately within each destination, Alaska has a lower delay percentage than AM West in all five cities.
This apparent contradiction occurs because the two airlines do not have the same distribution of flights across destinations. AM West operates a very large proportion of its flights to Phoenix, where delay rates are relatively low. In contrast, Alaska operates a large proportion of its flights to Seattle, where delay rates are higher.
As a result, the overall percentages are influenced by the different numbers of flights that each airline operates in each destination.
Therefore, the aggregated overall comparison makes AM West appear to perform better even though Alaska performs better within every individual destination. This reversal between the aggregated and subgroup results is an example of Simpson’s Paradox.
Conclusion
This analysis demonstrates why percentages and subgroup comparisons are important when evaluating airline performance.
Although AM West had the lower overall delay rate (10.89% compared with 13.27% for Alaska), Alaska had the lower delay rate in all five individual destinations.
The difference occurs because the airlines have very different distributions of flights across destinations. AM West operates a large proportion of its flights to Phoenix, where delay rates are relatively low, while Alaska operates a large proportion of its flights to Seattle, where delay rates are higher. These differences in flight volume affect the aggregated percentages.
The analysis also demonstrates the importance of tidying and transforming data before drawing conclusions. The source data were recreated in wide format while preserving the original missing cells. R was then used to populate the missing airline names and transform the data from wide to long format.
The tidy dataset made it easier to group the observations, calculate counts and percentages, compare the airlines overall and by destination, and identify the discrepancy between the aggregated and city-level results.