Airline Delays Tidying Data

Introduction

In this assignment, I will work with a small dataset about flight arrivals. It shows the number of on-time and delayed flights for two airlines, Alaska and AM West, across five cities: Los Angeles, Phoenix, San Diego, San Francisco, and Seattle.

The data is given as a table in a “wide” format. Each city has its own column, and each airline has two rows, one for on-time flights and one for delayed flights. This format is easy for people to read, but it is not tidy, which makes it harder to analyze in R. The goal of this assignment is to recreate this table as a CSV file, clean and reshape it using tidyr and dplyr, and then compare the arrival delays of the two airlines.

Planned Approach

First, I will use an LLM to help me transfer the table into Excel and save it as a CSV file in the same wide format. I will upload it to GitHub and read it into R.

Next, I will use tidyr and dplyr to clean the data, so that each row shows one airline in one city with its on-time and delayed flights.

Then, I will compare the two airlines by calculating their delay rates, both overall and for each city, and show the results with tables and charts.

Anticipated Challenges

The messy parts of the data, like the missing airline names, the missing column names, and the empty row, may cause R to read the file incorrectly, so I will need to check it carefully after loading. Reshaping the data into the right tidy format may also take some trial and error. Another challenge is that the airlines have very different numbers of flights in each city, so I will compare delay rates instead of raw numbers. Finally, the overall results might not match the city-by-city results, and I will need to explain this difference clearly.

library(tidyr)

library(dplyr)

Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

airline_delays <- read.csv("https://raw.githubusercontent.com/esradogan3/airline_delays_tidy_data/refs/heads/main/airline_assignment_5a.csv")
head(airline_delays)

        X     X.1 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                          NA      NA        NA            NA      NA
4 AM WEST on time         694    4840       383           320     201
5         delayed         117     415        65           129      61

Renaming column names:

airline_delays <- airline_delays %>%
  rename(airline = X, arrival_status = X.1)
head(airline_delays)

  airline arrival_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                                 NA      NA        NA            NA      NA
4 AM WEST        on time         694    4840       383           320     201
5                delayed         117     415        65           129      61

Handling with the empty row:

airline_delays <- airline_delays %>%
  filter(arrival_status != "")
head(airline_delays)

  airline arrival_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

Replacing empty cells with NA and filling them with the right Airline name. Then, making he cities values, and adding a new column as city. Changing arrival_status column to on_time and delayed:

airline_delays <- airline_delays %>%
  mutate(airline = na_if(airline, ""))%>%
  fill(airline)%>%
pivot_longer(cols = Los.Angeles:Seattle, names_to = "city", values_to = "flights")%>%
pivot_wider(names_from = arrival_status, values_from = flights)%>%
  rename(on_time = `on time`)%>%
  mutate(city = gsub(".", " ", city, fixed = TRUE))
head(airline_delays)

# A tibble: 6 × 4
  airline city          on_time delayed
  <chr>   <chr>           <int>   <int>
1 ALASKA  Los Angeles       497      62
2 ALASKA  Phoenix           221      12
3 ALASKA  San Diego         212      20
4 ALASKA  San Francisco     503     102
5 ALASKA  Seattle          1841     305
6 AM WEST Los Angeles       694     117

Adding a new column “delay_ratio”:

airline_delays <- airline_delays %>%
mutate(delay_ratio = delayed / (on_time + delayed))
print(airline_delays)

# A tibble: 10 × 5
   airline city          on_time delayed delay_ratio
   <chr>   <chr>           <int>   <int>       <dbl>
 1 ALASKA  Los Angeles       497      62      0.111 
 2 ALASKA  Phoenix           221      12      0.0515
 3 ALASKA  San Diego         212      20      0.0862
 4 ALASKA  San Francisco     503     102      0.169 
 5 ALASKA  Seattle          1841     305      0.142 
 6 AM WEST Los Angeles       694     117      0.144 
 7 AM WEST Phoenix          4840     415      0.0790
 8 AM WEST San Diego         383      65      0.145 
 9 AM WEST San Francisco     320     129      0.287 
10 AM WEST Seattle           201      61      0.233 

Calculating overall_ratio:

overall_delays <- airline_delays %>%
  group_by(airline) %>%
  summarise(overall_ratio = sum(delayed) / sum(delayed + on_time))
print(overall_delays)

# A tibble: 2 × 2
  airline overall_ratio
  <chr>           <dbl>
1 ALASKA          0.133
2 AM WEST         0.109

Comparing the overall delay ratio on the chart:

library(ggplot2)
ggplot(overall_delays , aes(x =airline, y = overall_ratio, fill = airline)) + geom_col()

Using of LLm

In this assignment, I used an LLM as my tutor. It did not write any ready-made code for me; instead, it helped me step by step, like a teacher, while I wrote the code myself.

References

Anthropic. (2026). Claude (Opus 5.5) [Large language model]. https://claude.ai