Airline Delays

Assignment 5A: Code Base

Introduction

Given the chart below, we were tasked with performing analysis to compare arrival delays from the two airlines

This required creating a .csv file of the information. It resulted in a messy wide structured dataframe when loaded into R. After transforming and tidying the data, we could compare the arrival delays by airline and by city using tables and charts.

Load in Data

The raw data is loaded in from an excel-created .csv file, hosted on github.

url <- "https://raw.githubusercontent.com/jocslater-code/DATA607/refs/heads/main/Assignment5A/AirlineDelays.csv"
df_raw <- read.csv(url)
df_raw

        X     X.1 Los.Angeles Phoenix San.Diego San.Fransisco Seattle
1  ALASKA on time         497     221       212           503    1841
2         delayed          62      12        20           102     305
3                          NA      NA        NA            NA      NA
4 AM WEST on time         694    4840       383           320     201
5         delayed         117     415        65           129      61

Tidy Data

Before any analysis can be performed, the flight data must be tidied up. In the raw form, it is in a wide format, with rows of NA’s, column name convention issues, and missing airline information. First the columns are renamed, the airline names are filled in, the NA row is removed, the data is converted into a long format, and finally the city names are edited for convention.

# Rename columns
df_clean <- df_raw %>%
  rename(airline = X, status = X.1) 

# Fill in missing airlines
df_clean[2, "airline"] <- "ALASKA"
df_clean[5, "airline"] <- "AM WEST"

# Remove empty row
df_clean <- df_clean[-3, ]                    
  
# Transform into long format  
df_long <- df_clean %>%  
  pivot_longer(
    cols = -c(airline, status),
    names_to = "city",
    values_to = "flight_count")
  
# Clean up city names
df_long <- df_long %>%
  mutate(city = gsub("\\.", " ", city)) 

Calculations

We calculate each airline’s percentage of delays overall and per city.

# Calculate df with delays per city, including breakout per airline
df_city <- df_long %>%
  group_by(airline, city) %>%
  summarise(
    total_flights = sum(flight_count),
    delayed_flights = sum(flight_count[status == "delayed"]),
    delay_pct = (delayed_flights / total_flights)*100,
    .groups = "drop"
  )

# Calculate df with delays per airline
df_airline <- df_long %>%
  group_by(airline) %>%
  summarise(
    city = "Overall",
    total_flights = sum(flight_count),
    delayed_flights = sum(flight_count[status == "delayed"]),
    delay_pct = (delayed_flights / total_flights)*100,
    .groups = "drop"
  )

# Calculate df with delays per city, NO breakout per airline
df_city_combined <- df_long %>%
  group_by(city) %>%
  summarise(
    total_flights = sum(flight_count),
    delayed_flights = sum(flight_count[status == "delayed"]),
    delay_pct = (delayed_flights / total_flights) * 100,
    .groups = "drop"
  ) %>%
  arrange(desc(delay_pct)) 

df_overall <- bind_rows(df_city, df_airline)

Delay percentage broken down by city and airline is then displayed in a chart format using a great table, as well as a bar plot. The same information is also shown grouped just per city with the airline data combined.

gt_table <- df_overall %>%
  mutate(delay_pct = delay_pct / 100) %>% 
  gt(groupname_col = "airline", rowname_col = "city") %>%
  tab_header(
    title = md("**Flight Delays Summary**"),
    subtitle = "Percentage of delayed flights by city and overall"
  ) %>%
  cols_label(
    total_flights = "Total Flights",
    delayed_flights = "Delayed Flights",
    delay_pct = "Delay Rate"
  ) %>%
  fmt_integer(columns = c(total_flights, delayed_flights)) %>%
  fmt_percent(columns = delay_pct, decimals = 1) %>%
  tab_style(
    style = list(
      cell_fill(color = "#f0f0f0"),
      cell_text(weight = "bold")
    ),
    locations = cells_body(
      rows = city == "Overall"
    )
  )
gt_table

Flight Delays Summary
Percentage of delayed flights by city and overall
	Total Flights	Delayed Flights	Delay Rate
ALASKA
Los Angeles	559	62	11.1%
Phoenix	233	12	5.2%
San Diego	232	20	8.6%
San Fransisco	605	102	16.9%
Seattle	2,146	305	14.2%
Overall	3,775	501	13.3%
AM WEST
Los Angeles	811	117	14.4%
Phoenix	5,255	415	7.9%
San Diego	448	65	14.5%
San Fransisco	449	129	28.7%
Seattle	262	61	23.3%
Overall	7,225	787	10.9%

gt_city_table <- df_city_combined %>%
  mutate(delay_pct = delay_pct / 100) %>% 
  gt() %>%
  tab_header(
    title = md("**Combined Delays by City**"),
    subtitle = "Aggregated across all airlines"
  ) %>%
  cols_label(
    city = "City",
    total_flights = "Total Flights",
    delayed_flights = "Delayed Flights",
    delay_pct = "Delay Rate"
  ) %>%
  fmt_integer(columns = c(total_flights, delayed_flights)) %>%
  fmt_percent(columns = delay_pct, decimals = 1)

gt_city_table

Combined Delays by City
Aggregated across all airlines
City	Total Flights	Delayed Flights	Delay Rate
San Fransisco	1,054	231	21.9%
Seattle	2,408	366	15.2%
Los Angeles	1,370	179	13.1%
San Diego	680	85	12.5%
Phoenix	5,488	427	7.8%

ggplot(df_city, aes(x = reorder(city, delay_pct), y = delay_pct, fill = airline)) +
  geom_col(position = "dodge") +
  scale_y_continuous(labels = scales::percent_format(scale = 1)) +
  labs(
    title = md("Flight Delays Summary"),
    x = "City",
    y = "Delay Percentage",
    fill = "Airline",
  ) +
  theme_minimal() +
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),
    panel.grid.major.x = element_blank()
  )

ggplot(df_city_combined, aes(x = reorder(city, delay_pct), y = delay_pct)) +
  geom_col(fill = "steelblue", alpha = 0.8) +
  geom_text(aes(label = sprintf("%.1f%%", delay_pct)), vjust = -0.5, fontface = "bold") +
  scale_y_continuous(
    labels = scales::percent_format(scale = 1),
    expand = expansion(mult = c(0, 0.15)) 
  ) +
  labs(
    title = "Combined Delays by City",
    subtitle = "Aggregated across all airlines",
    x = "City",
    y = "Delay Percentage"
  ) +
  theme_minimal() +
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1, size = 10),
    panel.grid.major.x = element_blank(),
    plot.title = element_text(face = "bold")
  )

Discussion and Next Steps

It is clear from the visualized data that although both airlines trend together for each city, AM West consistently experiences more delays. Overall, AM West has about 3% more delays than Alaska. For both airlines, San Francisco had the highest delay rate, at almost 22%.

Because these are all arrival delays, I would be interested in exploring the associated departure delays from the airports each plane is leaving from. This would help us determine if the planes are arriving late because they are departing late or if their is something in the typical route that causes the delay. We could also expand the analysis by pulling in information from other cities, airline, and years.