DATA 607 Week 5A Code Base
Chanice McKenzie
Introduction
The data for this analysis comes from the airline arrival-delay table provided in the assignment, originally sourced from Numbersense by Kaiser Fung (McGraw Hill, 2013). I recreated the table as a CSV and hosted it in my GitHub repository so the analysis can be reproduced from an internet-accessible source. (https://chanicemcken.github.io/Data-607-Week-5A-Assignment/airline_delays.csv). The main question is whether the airline with the better overall delay rate also performs better when the five destinations are compared individually.
Load Packages
I use tidyverse for importing, cleaning, transforming,
and visualizing the data, and knitr for displaying
formatted tables.
library(tidyverse)
library(knitr)Import the Data
The CSV recreates the original assignment table in wide format. The airline name is intentionally missing on the second row for each airline because the original source table uses a blank cell rather than repeating the airline name. Reading the file from a public URL instead of a local path makes the analysis reproducible on another computer.
airline_url <- "https://chanicemcken.github.io/Data-607-Week-5A-Assignment/airline_delays.csv"
airline_raw <- read_csv(
airline_url,
na = c("", "NA", "(blank)"),
show_col_types = FALSE
)
airline_raw## # 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 <NA> delayed 62 12 20 102 305
## 3 AM WEST on time 694 4840 383 320 201
## 4 <NA> delayed 117 415 65 129 61Inspect the Original Data
Before cleaning the data, I inspect its structure and count the missing values. This confirms that the blank airline cells are being recognized as missing rather than as valid airline names.
glimpse(airline_raw)## Rows: 4
## Columns: 7
## $ Airline <chr> "ALASKA", NA, "AM WEST", NA
## $ Status <chr> "on time", "delayed", "on time", "delayed"
## $ `Los Angeles` <dbl> 497, 62, 694, 117
## $ Phoenix <dbl> 221, 12, 4840, 415
## $ `San Diego` <dbl> 212, 20, 383, 65
## $ `San Francisco` <dbl> 503, 102, 320, 129
## $ Seattle <dbl> 1841, 305, 201, 61missing_values <- airline_raw %>%
summarise(across(everything(), ~ sum(is.na(.))))
kable(missing_values, caption = "Missing Values in the Original CSV")| Airline | Status | Los Angeles | Phoenix | San Diego | San Francisco | Seattle |
|---|---|---|---|---|---|---|
| 2 | 0 | 0 | 0 | 0 | 0 | 0 |
Populate the Missing Airline Names
The missing airline values are a result of the visual layout of the
original table, not unknown observations. I use fill() to
carry the most recent airline name downward so that every row contains
an airline value before reshaping the data.
airline_clean <- airline_raw %>%
fill(Airline, .direction = "down")
airline_clean## # 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 61Transform the Data From Wide to Long Format
The five destinations are currently stored as separate columns. I use
pivot_longer() to convert those city columns into two
variables: Destination and Flights. After this
transformation, each row represents one airline, one arrival status, one
destination, and its flight count.
airline_long <- airline_clean %>%
pivot_longer(
cols = -c(Airline, Status),
names_to = "Destination",
values_to = "Flights"
) %>%
mutate(
Airline = recode(
Airline,
"ALASKA" = "Alaska",
"AM WEST" = "AM West"
),
Status = str_to_lower(Status)
)
airline_long## # A tibble: 20 × 4
## Airline Status Destination 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 61Count Analysis and Validation
Before calculating percentages, I summarize the counts by airline and arrival status. I also verify that the total number of flights is unchanged after transforming the data from wide to long format.
count_summary <- airline_long %>%
group_by(Airline, Status) %>%
summarise(Flights = sum(Flights), .groups = "drop")
kable(
count_summary,
caption = "Flight Counts by Airline and Arrival Status"
)| Airline | Status | Flights |
|---|---|---|
| AM West | delayed | 787 |
| AM West | on time | 6438 |
| Alaska | delayed | 501 |
| Alaska | on time | 3274 |
wide_total <- airline_clean %>%
select(-Airline, -Status) %>%
unlist() %>%
sum(na.rm = TRUE)
long_total <- sum(airline_long$Flights, na.rm = TRUE)
validation <- tibble(
Check = c("Total flights in wide data", "Total flights in long data"),
Flights = c(wide_total, long_total)
)
kable(validation, caption = "Validation of Flight Totals")| Check | Flights |
|---|---|
| Total flights in wide data | 11000 |
| Total flights in long data | 11000 |
Overall Airline Comparison
Because the airlines have different numbers of flights, raw delay counts alone are not a fair comparison. I calculate the percentage of flights that were delayed and on time for each airline.
overall_performance <- airline_long %>%
group_by(Airline, Status) %>%
summarise(Flights = sum(Flights), .groups = "drop_last") %>%
mutate(
Total_Flights = sum(Flights),
Percentage = 100 * Flights / Total_Flights
) %>%
ungroup()
kable(
overall_performance,
digits = 2,
caption = "Overall Arrival Performance by Airline"
)| Airline | Status | Flights | Total_Flights | Percentage |
|---|---|---|---|---|
| AM West | delayed | 787 | 7225 | 10.89 |
| AM West | on time | 6438 | 7225 | 89.11 |
| Alaska | delayed | 501 | 3775 | 13.27 |
| Alaska | on time | 3274 | 3775 | 86.73 |
overall_performance %>%
filter(Status == "delayed") %>%
ggplot(aes(x = Airline, y = Percentage, fill = Airline)) +
geom_col(width = 0.6, show.legend = FALSE) +
geom_text(
aes(label = paste0(round(Percentage, 1), "%")),
vjust = -0.4
) +
labs(
title = "Overall Percentage of Flights Delayed",
x = NULL,
y = "Delayed Flights (%)"
) +
theme_minimal()
Overall, Alaska has a delay rate of about 13.27%, while AM West has a lower overall delay rate of about 10.89%. Based only on the aggregate percentages, AM West appears to have better arrival performance. However, the destination-level comparison below shows why the overall result does not tell the full story.
Compare Delay Percentages Across the Five Cities
Next, I calculate the delay percentage separately for each airline and destination. This allows the airlines to be compared within the same city rather than combining destinations that have very different numbers of flights.
city_performance <- airline_long %>%
group_by(Airline, Destination) %>%
mutate(Total_Flights = sum(Flights)) %>%
ungroup() %>%
mutate(Percentage = 100 * Flights / Total_Flights) %>%
filter(Status == "delayed") %>%
select(Airline, Destination, Delayed_Flights = Flights,
Total_Flights, Delay_Percentage = Percentage)
kable(
city_performance,
digits = 2,
caption = "Delay Percentages by Airline and Destination"
)| Airline | Destination | Delayed_Flights | Total_Flights | Delay_Percentage |
|---|---|---|---|---|
| Alaska | Los Angeles | 62 | 559 | 11.09 |
| Alaska | Phoenix | 12 | 233 | 5.15 |
| Alaska | San Diego | 20 | 232 | 8.62 |
| Alaska | San Francisco | 102 | 605 | 16.86 |
| Alaska | Seattle | 305 | 2146 | 14.21 |
| AM West | Los Angeles | 117 | 811 | 14.43 |
| AM West | Phoenix | 415 | 5255 | 7.90 |
| AM West | San Diego | 65 | 448 | 14.51 |
| AM West | San Francisco | 129 | 449 | 28.73 |
| AM West | Seattle | 61 | 262 | 23.28 |
ggplot(
city_performance,
aes(x = Destination, y = Delay_Percentage, fill = Airline)
) +
geom_col(position = position_dodge(width = 0.8), width = 0.7) +
labs(
title = "Delay Percentages by Airline and Destination",
x = NULL,
y = "Delayed Flights (%)",
fill = "Airline"
) +
theme_minimal() +
theme(
axis.text.x = element_text(angle = 30, hjust = 1),
plot.title = element_text(size = 12)
)
The city-level percentages show a different pattern from the overall comparison. Alaska has a lower delay percentage than AM West in all five destinations: Los Angeles, Phoenix, San Diego, San Francisco, and Seattle. Therefore, looking only at the overall percentages would lead to a different impression than comparing the airlines within each destination.
Overall and City-Level Discrepancy
To understand why the results reverse, I examine how each airline’s flights are distributed across the five destinations. A destination with many flights contributes much more to an airline’s overall delay percentage than a destination with relatively few flights.
flight_distribution <- airline_long %>%
group_by(Airline, Destination) %>%
summarise(Flights = sum(Flights), .groups = "drop_last") %>%
mutate(
Airline_Total = sum(Flights),
Share_of_Airline_Flights = 100 * Flights / Airline_Total
) %>%
ungroup()
kable(
flight_distribution,
digits = 2,
caption = "Distribution of Each Airline's Flights Across Destinations"
)| Airline | Destination | Flights | Airline_Total | Share_of_Airline_Flights |
|---|---|---|---|---|
| AM West | Los Angeles | 811 | 7225 | 11.22 |
| AM West | Phoenix | 5255 | 7225 | 72.73 |
| AM West | San Diego | 448 | 7225 | 6.20 |
| AM West | San Francisco | 449 | 7225 | 6.21 |
| AM West | Seattle | 262 | 7225 | 3.63 |
| Alaska | Los Angeles | 559 | 3775 | 14.81 |
| Alaska | Phoenix | 233 | 3775 | 6.17 |
| Alaska | San Diego | 232 | 3775 | 6.15 |
| Alaska | San Francisco | 605 | 3775 | 16.03 |
| Alaska | Seattle | 2146 | 3775 | 56.85 |
AM West has a very large share of its flights in Phoenix, where both airlines have relatively low delay rates. Alaska, in contrast, has a large share of its flights in Seattle, where delay rates are higher. Because the overall percentages are weighted by the number of flights in each city, AM West’s large number of Phoenix flights pulls its overall delay percentage downward. This produces a reversal in which AM West looks better overall even though Alaska has the lower delay percentage in every individual city. This is an example of why aggregated results should be interpreted together with subgroup comparisons.
Conclusions
The analysis shows that AM West has the lower overall delay percentage, approximately 10.89% compared with Alaska’s 13.27%. However, Alaska has the lower delay percentage in every one of the five destinations. The discrepancy occurs because the airlines do not have the same distribution of flights across cities, so cities with more flights have a greater effect on each airline’s overall percentage.
Based on these results, I would not evaluate airline performance using the overall rate alone. Comparing percentages within destination provides important context and prevents the unequal distribution of flights from hiding the city-level pattern. An extension of this analysis could include additional airlines, destinations, or time periods to determine whether the same relationship continues with a larger dataset.
Reproducibility Information
The data is imported directly from an internet-accessible CSV, so the analysis does not depend on files stored locally on my computer. I include the R session information below to document the environment and package versions used to render the report.
sessionInfo()## R version 4.5.2 (2025-10-31)
## Platform: aarch64-apple-darwin20
## Running under: macOS Sequoia 15.7.3
##
## Matrix products: default
## BLAS: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
## LAPACK: /Library/Frameworks/R.framework/Versions/4.5-arm64/Resources/lib/libRlapack.dylib; LAPACK version 3.12.1
##
## locale:
## [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
##
## time zone: America/New_York
## tzcode source: internal
##
## attached base packages:
## [1] stats graphics grDevices utils datasets methods base
##
## other attached packages:
## [1] knitr_1.51 lubridate_1.9.4 forcats_1.0.1 stringr_1.6.0
## [5] dplyr_1.2.1 purrr_1.2.1 readr_2.2.0 tidyr_1.3.2
## [9] tibble_3.3.1 ggplot2_4.0.2 tidyverse_2.0.0
##
## loaded via a namespace (and not attached):
## [1] sass_0.4.10 utf8_1.2.6 generics_0.1.4 stringi_1.8.7
## [5] hms_1.1.4 digest_0.6.39 magrittr_2.0.4 evaluate_1.0.5
## [9] grid_4.5.2 timechange_0.4.0 RColorBrewer_1.1-3 fastmap_1.2.0
## [13] jsonlite_2.0.0 scales_1.4.0 jquerylib_0.1.4 cli_3.6.5
## [17] rlang_1.1.7 crayon_1.5.3 bit64_4.6.0-1 withr_3.0.2
## [21] cachem_1.1.0 yaml_2.3.12 otel_0.2.0 tools_4.5.2
## [25] parallel_4.5.2 tzdb_0.5.0 curl_7.0.0 vctrs_0.7.1
## [29] R6_2.6.1 lifecycle_1.0.5 bit_4.6.0 vroom_1.7.0
## [33] pkgconfig_2.0.3 pillar_1.11.1 bslib_0.10.0 gtable_0.3.6
## [37] glue_1.8.0 xfun_0.56 tidyselect_1.2.1 rstudioapi_0.18.0
## [41] dichromat_2.0-0.1 farver_2.1.2 htmltools_0.5.9 labeling_0.4.3
## [45] rmarkdown_2.30 compiler_4.5.2 S7_0.2.1