Airline Delays
Zaina Hassan
GitHub
Repository
Approach
For this assignment, I will recreate the provided airline arrival data in a CSV file using a wide format similar to the original table. The dataset contains the number of on-time and delayed flights for Alaska Airlines and AM West across five destinations: Los Angeles, Phoenix, San Diego, San Francisco, and Seattle. I will upload the CSV file to my GitHub repository and import the data into R. The assignment specifically asks for the original information to be recreated and then tidied and transformed using tidyr and dplyr. After importing the data, I will check for missing values and make any necessary adjustments before transforming the dataset from wide to long format. I will then calculate the percentage of delayed flights for each airline overall and compare their performance. Next, I will calculate the delay percentages for each airline across each of the five destinations to determine whether the city-level results differ from the overall results. Finally, I will compare these findings and discuss why the overall performance of the airlines may give a different impression than their performance when each destination is examined separately. One complication I anticipate is handling the structure of the original dataset, including any missing values or empty cells, while making sure they are represented correctly when the data is imported into R. I will also need to carefully transform the five destination columns from wide to long format without losing the airline or arrival status information. Another potential complication is that the two airlines have different numbers of flights across each destination, so comparing only the number of delayed flights could be misleading. To account for this, I will compare percentages rather than relying only on raw counts.
Code
- Load the Data
# Load packages
library(tidyverse)## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.2.1 ✔ readr 2.2.0
## ✔ forcats 1.0.1 ✔ stringr 1.6.0
## ✔ ggplot2 4.0.3 ✔ tibble 3.3.1
## ✔ lubridate 1.9.5 ✔ tidyr 1.3.2
## ✔ purrr 1.2.2
## ── 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 errorsairline_data <- read.csv(
"https://raw.githubusercontent.com/zxinah/DATA-607-Data-Acquisition-Management-/refs/heads/main/airlinedelays.csv",
check.names = FALSE
)
# View the original wide-format data
airline_data## airline status Los Angeles Phoenix San Diego San Francisco Seattle
## 1 ALASKA on time 497 221 212 503 1841
## 2 delayed 62 12 20 102 305
## 3 AM WEST on time 694 4840 383 320 201
## 4 delayed 117 415 65 129 61# Examine the structure
str(airline_data)## 'data.frame': 4 obs. of 7 variables:
## $ airline : chr "ALASKA" "" "AM WEST" ""
## $ 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 61- Check for Missing Values
# Check for Blank/Missing Values
colSums(is.na(airline_data))## airline status Los Angeles Phoenix San Diego
## 0 0 0 0 0
## San Francisco Seattle
## 0 0# Display rows containing any missing values
airline_data[!complete.cases(airline_data), ]## [1] airline status Los Angeles Phoenix San Diego
## [6] San Francisco Seattle
## <0 rows> (or 0-length row.names)The blank airline cells are initially imported as empty strings rather than NA values, so I will convert the empty strings to NA before filling the missing airline names.
- Populate Missing Data
# Convert blank airline cells to NA
airline_data$airline[airline_data$airline == ""] <- NA
# Fill missing airline names with the value above
airline_data <- airline_data %>%
fill(airline)
# Check the cleaned data
airline_data## 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 61# Check again for missing values
colSums(is.na(airline_data))## airline status Los Angeles Phoenix San Diego
## 0 0 0 0 0
## San Francisco Seattle
## 0 0- Transform Wide Data to Long Format
airline_long <- airline_data %>%
pivot_longer(
cols = -c(airline, status),
names_to = "destination",
values_to = "flights"
)
# View tidy data
airline_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 61# Examine structure
str(airline_long)## tibble [20 × 4] (S3: tbl_df/tbl/data.frame)
## $ airline : chr [1:20] "ALASKA" "ALASKA" "ALASKA" "ALASKA" ...
## $ status : chr [1:20] "on time" "on time" "on time" "on time" ...
## $ destination: chr [1:20] "Los Angeles" "Phoenix" "San Diego" "San Francisco" ...
## $ flights : int [1:20] 497 221 212 503 1841 62 12 20 102 305 ...The original dataset stored each destination in a separate column. Using pivot_longer() transformed these columns into a single destination variable with the corresponding flight counts stored in the flights variable. This produces a tidy long format dataset that can be grouped and analyzed more easily.
- Overall Airline Performance
overall_performance <- airline_long %>%
group_by(airline, status) %>%
summarise(
flights = sum(flights),
.groups = "drop"
) %>%
group_by(airline) %>%
mutate(
total_flights = sum(flights),
percentage = flights / total_flights * 100
)
overall_performance## # A tibble: 4 × 5
## # Groups: airline [2]
## airline status flights total_flights percentage
## <chr> <chr> <int> <int> <dbl>
## 1 ALASKA delayed 501 3775 13.3
## 2 ALASKA on time 3274 3775 86.7
## 3 AM WEST delayed 787 7225 10.9
## 4 AM WEST on time 6438 7225 89.1# Keep only delayed flights for comparison
overall_delays <- overall_performance %>%
filter(status == "delayed") %>%
select(
airline,
delayed_flights = flights,
total_flights,
delay_percentage = percentage
)
overall_delays## # A tibble: 2 × 4
## # Groups: airline [2]
## airline delayed_flights total_flights delay_percentage
## <chr> <int> <int> <dbl>
## 1 ALASKA 501 3775 13.3
## 2 AM WEST 787 7225 10.9- Overall Delay Percentage Chart
ggplot(
overall_delays,
aes(
x = airline,
y = delay_percentage,
fill = airline
)
) +
geom_col() +
labs(
title = "Overall Delay Percentage by Airline",
x = "Airline",
y = "Delayed Flights (%)"
) +
theme_minimal() +
theme(
legend.position = "none"
)
The overall comparison shows that Alaska Airlines had a delay rate of
approximately 13.3%, while AM West had a lower delay rate of
approximately 10.9%. Based only on the overall percentages, AM West
appears to have better arrival performance. However, because the two
airlines operate different numbers of flights across the five
destinations, the overall percentages may not fully represent their
performance within each individual city.
- Performance by Destination
city_performance <- airline_long %>%
group_by(
airline,
destination,
status
) %>%
summarise(
flights = sum(flights),
.groups = "drop"
) %>%
group_by(
airline,
destination
) %>%
mutate(
total_flights = sum(flights),
percentage = flights / total_flights * 100
)
city_performance## # A tibble: 20 × 6
## # Groups: airline, destination [10]
## airline destination status flights total_flights percentage
## <chr> <chr> <chr> <int> <int> <dbl>
## 1 ALASKA Los Angeles delayed 62 559 11.1
## 2 ALASKA Los Angeles on time 497 559 88.9
## 3 ALASKA Phoenix delayed 12 233 5.15
## 4 ALASKA Phoenix on time 221 233 94.8
## 5 ALASKA San Diego delayed 20 232 8.62
## 6 ALASKA San Diego on time 212 232 91.4
## 7 ALASKA San Francisco delayed 102 605 16.9
## 8 ALASKA San Francisco on time 503 605 83.1
## 9 ALASKA Seattle delayed 305 2146 14.2
## 10 ALASKA Seattle on time 1841 2146 85.8
## 11 AM WEST Los Angeles delayed 117 811 14.4
## 12 AM WEST Los Angeles on time 694 811 85.6
## 13 AM WEST Phoenix delayed 415 5255 7.90
## 14 AM WEST Phoenix on time 4840 5255 92.1
## 15 AM WEST San Diego delayed 65 448 14.5
## 16 AM WEST San Diego on time 383 448 85.5
## 17 AM WEST San Francisco delayed 129 449 28.7
## 18 AM WEST San Francisco on time 320 449 71.3
## 19 AM WEST Seattle delayed 61 262 23.3
## 20 AM WEST Seattle on time 201 262 76.7# Keep delayed flights only
city_delays <- city_performance %>%
filter(status == "delayed") %>%
select(
airline,
destination,
delayed_flights = flights,
total_flights,
delay_percentage = percentage
)
city_delays## # A tibble: 10 × 5
## # Groups: airline, destination [10]
## airline destination delayed_flights total_flights delay_percentage
## <chr> <chr> <int> <int> <dbl>
## 1 ALASKA Los Angeles 62 559 11.1
## 2 ALASKA Phoenix 12 233 5.15
## 3 ALASKA San Diego 20 232 8.62
## 4 ALASKA San Francisco 102 605 16.9
## 5 ALASKA Seattle 305 2146 14.2
## 6 AM WEST Los Angeles 117 811 14.4
## 7 AM WEST Phoenix 415 5255 7.90
## 8 AM WEST San Diego 65 448 14.5
## 9 AM WEST San Francisco 129 449 28.7
## 10 AM WEST Seattle 61 262 23.3- Compare Airlines Across Five Cities
city_comparison <- city_delays %>%
select(
destination,
airline,
delay_percentage
) %>%
pivot_wider(
names_from = airline,
values_from = delay_percentage
)
city_comparison## # A tibble: 5 × 3
## # Groups: destination [5]
## destination ALASKA `AM WEST`
## <chr> <dbl> <dbl>
## 1 Los Angeles 11.1 14.4
## 2 Phoenix 5.15 7.90
## 3 San Diego 8.62 14.5
## 4 San Francisco 16.9 28.7
## 5 Seattle 14.2 23.3- City by City Delay Percentage Chart
ggplot(
city_delays,
aes(
x = destination,
y = delay_percentage,
fill = airline
)
) +
geom_col(
position = "dodge"
) +
labs(
title = "Delay Percentage by Destination and Airline",
x = "Destination",
y = "Delayed Flights (%)",
fill = "Airline"
) +
theme_minimal() +
theme(
axis.text.x = element_text(
angle = 45,
hjust = 1
)
)
The city-by-city comparison shows a different pattern from the overall
results. Alaska had a lower delay percentage than AM West in all five
destinations. In Los Angeles, Alaska’s delay rate was approximately
11.1% compared with 14.4% for AM West. In Phoenix, the rates were 5.2%
and 7.9%, respectively. Alaska also had lower delay rates in San Diego
(8.6% vs. 14.5%), San Francisco (16.9% vs. 28.7%), and Seattle (14.2%
vs. 23.3%). Therefore, when each destination is examined individually,
Alaska consistently had the lower percentage of delayed flights.
- Final Comparison
# Overall delay percentages
overall_delays## # A tibble: 2 × 4
## # Groups: airline [2]
## airline delayed_flights total_flights delay_percentage
## <chr> <int> <int> <dbl>
## 1 ALASKA 501 3775 13.3
## 2 AM WEST 787 7225 10.9# Delay percentages for each destination
city_comparison## # A tibble: 5 × 3
## # Groups: destination [5]
## destination ALASKA `AM WEST`
## <chr> <dbl> <dbl>
## 1 Los Angeles 11.1 14.4
## 2 Phoenix 5.15 7.90
## 3 San Diego 8.62 14.5
## 4 San Francisco 16.9 28.7
## 5 Seattle 14.2 23.3Results
The overall analysis showed that Alaska Airlines had 501 delayed flights out of 3,775 total flights, resulting in a delay rate of approximately 13.3%. AM West had 787 delayed flights out of 7,225 total flights, resulting in a lower overall delay rate of approximately 10.9%. Based on the overall percentages, AM West appears to have better arrival performance. However, the city by city comparison showed a different pattern. Alaska had a lower delay percentage than AM West in all five destinations. In Los Angeles, the delay rates were approximately 11.1% for Alaska and 14.4% for AM West. In Phoenix, they were 5.2% and 7.9%; in San Diego, 8.6% and 14.5%; in San Francisco, 16.9% and 28.7%; and in Seattle, 14.2% and 23.3%, respectively. Therefore, while AM West had the lower delay percentage overall, Alaska had the lower delay percentage in every individual destination.
Conclusion
The analysis demonstrates that comparing the airlines only by their overall delay percentages can give a different impression than comparing them within individual destinations. This discrepancy occurs because the airlines operated very different numbers of flights across the five cities. AM West operated a particularly large number of flights to Phoenix, where delay rates were relatively low, while a large proportion of Alaska’s flights were to Seattle, where delay rates were higher. As a result, the distribution of flights across destinations influences the overall percentages. Although Alaska had a lower delay percentage in every individual city, AM West had the lower delay percentage when all destinations were combined. This is an example of Simpson’s paradox, where a pattern observed within individual groups reverses when the groups are combined. The results demonstrate the importance of examining both aggregated and grouped data before drawing conclusions about performance.