Assignment 5A: Airline Delays

Approach

For this assignment, I will begin by recreating the provided airline delay dataset while preserving any missing values from the original data. I will then use R to identify and populate the missing data as appropriate. After preparing the dataset, I will transform it from wide format to long format so that the flight status information can be analyzed more easily.

Next, I will perform a count analysis and calculate the percentage of delayed flights for each airline, and review the city-by-city results and explain how differences in flight volume or grouping may affect the interpretation of airline performance.

Introduction

This assignment analyzes arrival delay data for Alaska Airlines and AM West across five destinations: Los Angeles, Phoenix, San Diego, San Francisco, and Seattle. The purpose of the analysis is to practice tidying and transforming data in R while comparing airline performance using flight delay percentages. The analysis will examine both overall airline performance and performance within each individual destination.

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 errors

Recreating the Original Dataset

airline_wide <- tibble(
  Airline = c("ALASKA", NA, "AM WEST", NA),
  Status = c("on time", "delayed", "on time", "delayed"),
  
  Los_Angeles = c(497, 62, 694, 117),
  Phoenix = c(221, 12, 4840, 415),
  San_Diego = c(212, 20, 383, 65),
  San_Francisco = c(503, 102, 320, 129),
  Seattle = c(1841, 305, 201, 61)
)

airline_wide
# 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      61

Here I manually created a table called airline_wide and stored all airline info in. Created the Airline column using vector c().

glimpse(airline_wide)
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, 61

Looking for Missing Data

is.na(airline_wide)
     Airline Status Los_Angeles Phoenix San_Diego San_Francisco Seattle
[1,]   FALSE  FALSE       FALSE   FALSE     FALSE         FALSE   FALSE
[2,]    TRUE  FALSE       FALSE   FALSE     FALSE         FALSE   FALSE
[3,]   FALSE  FALSE       FALSE   FALSE     FALSE         FALSE   FALSE
[4,]    TRUE  FALSE       FALSE   FALSE     FALSE         FALSE   FALSE
colSums(is.na(airline_wide))
      Airline        Status   Los_Angeles       Phoenix     San_Diego 
            2             0             0             0             0 
San_Francisco       Seattle 
            0             0

Populating the Missing Airline Values

airline_clean <- airline_wide |>
  fill(Airline)

Here I fill the missing values in the airline column based on carried over airline name going downward.

Confirming the Missing Values are Gone

colSums(is.na(airline_clean))
      Airline        Status   Los_Angeles       Phoenix     San_Diego 
            0             0             0             0             0 
San_Francisco       Seattle 
            0             0

The original source contained two blank airline labels in the delayed rows. These were represented as NA values and then populated using fill() so that each observation contained an airline name.

Saving the Wide Data as a CSV

write_csv(
  airline_wide,
  "airline_delays_wide.csv"
)

Reading the CVS back into R

airline_data <- read_csv(
  "airline_delays_wide.csv",
  show_col_types = FALSE
)

Filling the Missing Airline Names in the Imported Data

airline_clean <- airline_data |>
  fill(Airline)

Transforming Data wide to long

airline_long <- airline_clean |>
  pivot_longer(
    cols = Los_Angeles:Seattle,
    names_to = "City",
    values_to = "Count"
  )

Here we turn multiple columns into fewer coulums and more rows and put the old column names into a new column called city.

Cleaning the City Names

airline_long <- airline_long |>
  mutate(
    City = str_replace_all(City, "_", " ")
  )

str_replace_all() replaces text, so that we can get for example “Los Angeles” as a column with no under scores.

Looking at The Tidy Data

glimpse(airline_long)
Rows: 20
Columns: 4
$ Airline <chr> "ALASKA", "ALASKA", "ALASKA", "ALASKA", "ALASKA", "ALASKA", "A…
$ Status  <chr> "on time", "on time", "on time", "on time", "on time", "delaye…
$ City    <chr> "Los Angeles", "Phoenix", "San Diego", "San Francisco", "Seatt…
$ Count   <dbl> 497, 221, 212, 503, 1841, 62, 12, 20, 102, 305, 694, 4840, 383…

Counting Analysis

count_analysis <- airline_long |>
  group_by(Airline, Status) |>
  summarise(
    Total = sum(Count),
    .groups = "drop"
  )

count_analysis
# A tibble: 4 × 3
  Airline Status  Total
  <chr>   <chr>   <dbl>
1 ALASKA  delayed   501
2 ALASKA  on time  3274
3 AM WEST delayed   787
4 AM WEST on time  6438

Here I Start with airline_long, group by airline and status, and add all counts together.

airline_long |>
  group_by(Airline, Status)
# A tibble: 20 × 4
# Groups:   Airline, Status [4]
   Airline Status  City          Count
   <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

Calculating Overall Delay Percentage

overall_summary <- airline_long |>
  group_by(Airline) |>
  summarise(
    Total_Flights = sum(Count),
    Delayed_Flights = sum(Count[Status == "delayed"]),
    .groups = "drop"
  )

overall_summary
# A tibble: 2 × 3
  Airline Total_Flights Delayed_Flights
  <chr>           <dbl>           <dbl>
1 ALASKA           3775             501
2 AM WEST          7225             787

Here we calculate the results separately for ALASKA and AM WEST, sum all on-time and delayed flights together for each airline, and setting up a row for total delays.

overall_summary <- overall_summary |>
  mutate(
    Delay_Percent = Delayed_Flights / Total_Flights * 100
  )

overall_summary
# A tibble: 2 × 4
  Airline Total_Flights Delayed_Flights Delay_Percent
  <chr>           <dbl>           <dbl>         <dbl>
1 ALASKA           3775             501          13.3
2 AM WEST          7225             787          10.9

Delay Percentage=(DelayedFlights​/Total Flights)×100

overall_summary <- overall_summary |>
  mutate(
    Delay_Percent = round(Delay_Percent, 2)
  )

overall_summary
# A tibble: 2 × 4
  Airline Total_Flights Delayed_Flights Delay_Percent
  <chr>           <dbl>           <dbl>         <dbl>
1 ALASKA           3775             501          13.3
2 AM WEST          7225             787          10.9

Here we take the delayed flights, divide by total flights, multiply by 100, and save the result as the delay percentage.

Overall Airline Delay Comparison

ggplot(
  overall_summary,
  aes(
    x = Airline,
    y = Delay_Percent,
    fill = Airline
  )
) +
  geom_col() +
  labs(
    title = "Overall Delay Percentage by Airline",
    x = "Airline",
    y = "Delayed Flights (%)"
  ) +
  theme_minimal() +
  guides(fill = "none")

The overall comparison shows that ALASKA had a higher percentage of delayed flights than AM WEST. ALASKA’s delay rate was approximately 13%, while AM WEST’s was approximately 11%. Based on the aggregated data, AM WEST had the lower overall delay percentage.

Airline Delay Comparison by City

city_summary <- airline_long |>
  group_by(Airline, City) |>
  summarise(
    Total_Flights = sum(Count),
    Delayed_Flights = sum(Count[Status == "delayed"]),
    .groups = "drop"
  ) |>
  mutate(
    Delay_Percent = Delayed_Flights / Total_Flights * 100,
    Delay_Percent = round(Delay_Percent, 2)
  )

city_summary
# A tibble: 10 × 5
   Airline City          Total_Flights Delayed_Flights Delay_Percent
   <chr>   <chr>                 <dbl>           <dbl>         <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.9 
 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.3

This gives us one result for every airline-city combination.

city_comparison <- city_summary |>
  select(
    City,
    Airline,
    Delay_Percent
  ) |>
  pivot_wider(
    names_from = Airline,
    values_from = Delay_Percent
  )

city_comparison
# A tibble: 5 × 3
  City          ALASKA `AM WEST`
  <chr>          <dbl>     <dbl>
1 Los Angeles    11.1       14.4
2 Phoenix         5.15       7.9
3 San Diego       8.62      14.5
4 San Francisco  16.9       28.7
5 Seattle        14.2       23.3

When the airlines are compared city by city, ALASKA has a lower delay percentage than AM WEST in all five destinations. The largest differences appear in San Francisco and Seattle, where AM WEST has substantially higher delay percentages. These results differ from the overall comparison, which showed AM WEST with the lower overall delay rate.

Comparison Chart

ggplot(
  city_summary,
  aes(
    x = City,
    y = Delay_Percent,
    fill = Airline
  )
) +
  geom_col(position = "dodge") +
  labs(
    title = "Delay Percentage by Airline and City",
    x = "City",
    y = "Delayed Flights (%)"
  ) +
  theme_minimal()

Looking into Flight Volume

flight_volume <- airline_long |>
  group_by(Airline, City) |>
  summarise(
    Total_Flights = sum(Count),
    .groups = "drop"
  )

flight_volume
# A tibble: 10 × 3
   Airline City          Total_Flights
   <chr>   <chr>                 <dbl>
 1 ALASKA  Los Angeles             559
 2 ALASKA  Phoenix                 233
 3 ALASKA  San Diego               232
 4 ALASKA  San Francisco           605
 5 ALASKA  Seattle                2146
 6 AM WEST Los Angeles             811
 7 AM WEST Phoenix                5255
 8 AM WEST San Diego               448
 9 AM WEST San Francisco           449
10 AM WEST Seattle                 262

Simpson’s Paradox

The overall comparison and the city-by-city comparison lead to different conclusions. AM WEST has a lower delay percentage than ALASKA. However, when each city is examined separately, ALASKA has the lower delay percentage in all five destinations. This difference occurs because the airlines operate very different numbers of flights in each city. Cities with larger flight volumes have more influence on the overall percentage, so the combined results can differ from the pattern observed within each individual city.

Conclusion

This analysis showed how the structure and grouping of data can affect interpretation. After recreating the dataset, filling the missing airline labels, and transforming the data from wide to long format, delay percentages were calculated overall and by city. Although AM WEST had the lower overall delay percentage, ALASKA had the lower delay percentage in every individual city. The difference was caused by the unequal distribution of flight volumes across destinations, demonstrating why both aggregate and subgroup-level results should be examined before drawing conclusions.