Assignment 5A - Flight Data

Author

Carol Campbell

Published

October 4, 2026

Approach

We are tasked with recreating a .csv file provided for us in the assignment. We are free to use any software, including SQL of our choosing, and read it into R for analysis and data manipulation in order to determine which airline has better on-time performance. Is there one carrier who is more reliable than the other and does their location matter?

My plan is to recreate the table in Excel, save it as a .csv file and load it in my Github repository. From there, I will import the file into R using the read_csv function into r. Next I will tidy the data, including pivoting from wide to long format and other exploratory data analysis (EDA) to generate sufficient data that support my final answer.

Create the data

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

library(ggplot2)
library(kableExtra)


Attaching package: 'kableExtra'

The following object is masked from 'package:dplyr':

    group_rows

Read file from Github

Using “read_csv” I imported the raw file from my github repository.

airline_delay = (read_csv("https://raw.githubusercontent.com/carolc57/DATA607/refs/heads/main/cc_airlinedelays.csv"))

Rows: 5 Columns: 7
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (2): airline, status
dbl (5): Los Angeles, Phoenix, San Diego, San Francisco, Seattle

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

airline_delay

# A tibble: 5 × 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 <NA>    <NA>               NA      NA          NA              NA      NA
4 AM WEST on time           694    4840         383             320     201
5 <NA>    delayed           117     415          65             129      61

str(airline_delay)

spc_tbl_ [5 × 7] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
 $ airline      : chr [1:5] "Alaska" NA NA "AM WEST" ...
 $ status       : chr [1:5] "on time" "delayed" NA "on time" ...
 $ Los Angeles  : num [1:5] 497 62 NA 694 117
 $ Phoenix      : num [1:5] 221 12 NA 4840 415
 $ San Diego    : num [1:5] 212 20 NA 383 65
 $ San Francisco: num [1:5] 503 102 NA 320 129
 $ Seattle      : num [1:5] 1841 305 NA 201 61
 - attr(*, "spec")=
  .. cols(
  ..   airline = col_character(),
  ..   status = col_character(),
  ..   `Los Angeles` = col_double(),
  ..   Phoenix = col_double(),
  ..   `San Diego` = col_double(),
  ..   `San Francisco` = col_double(),
  ..   Seattle = col_double()
  .. )
 - attr(*, "problems")=<pointer: 0x000001cef1afc1e0>

Remove empty rows; fill empty cells

After importing the file, I see that row three is entirely empty, so I will remove it. Afterwards I will use the fill function to carry the airline names downward to fill the empty N/A cells with the same data.

#install.packages("janitor")
library(janitor)


Attaching package: 'janitor'

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

    chisq.test, fisher.test

airline_delay_mod <- remove_empty(airline_delay, "rows")

airline_delay_mod <- airline_delay_mod|>
  fill(airline, .direction = "down")


#Capitalize the first letter of the first two columns
airline_delay_mod <- airline_delay_mod |> 
  rename_with(str_to_title, .cols = 1:2)

kable(airline_delay_mod, caption = "Tidy Airline Delay")

Tidy Airline Delay
Airline	Status	Los Angeles	Phoenix	San Diego	San Francisco	Seattle
Alaska	on time	497	221	212	503	1841
Alaska	delayed	62	12	20	102	305
AM WEST	on time	694	4840	383	320	201
AM WEST	delayed	117	415	65	129	61

Pivot to long data frame

Now that my data is “tidy” with missing values replaced, I will now pivot it to a long format to convert the city columns into two variables, Destinations and Flights. Transforming the data into this manner facilitates grouping and analying the dataset.

airline_delay_long <- airline_delay_mod |>
  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)
  )

head(airline_delay_long, 20)

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

Compare count and percentage of on-time flights for the two airlines.

overall_performance <- airline_delay_long |>
  group_by(Airline, Status) |>
  summarise(
    Flights = sum(Flights),
    .groups = "drop"
  ) |>
  group_by(Airline) |>
  mutate(
    Total_flights = sum(Flights),
    Percentage = round((Flights / Total_flights * 100),1)
  )

kable(overall_performance, caption = "Overall Performance")

Overall Performance
Airline	Status	Flights	Total_flights	Percentage
AM West	delayed	787	7225	10.9
AM West	on time	6438	7225	89.1
Alaska	delayed	501	3775	13.3
Alaska	on time	3274	3775	86.7

Here we see that AM WEST has the lowest overall delay rate at 10.9% compared to Alaska’s 13.3%. Whereas the two airlines are pretty close in their respective on-time rates with AM West leading at 89.1% compared to Alaska’s 86.7%

##Let’s focus on on-time arrival rates between two by using the filter function

overall_performance %>%
  filter(Status == "on time") %>%
  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 On-Time Flights",
    x = NULL,
    y = "On Time (%)"
  ) +
  theme_classic()

Overall AM West had a slightly better on-time arrival rate than Alaska, however this comparison may be somewhat skewed. Other factors such as destination may tell a different story.

Compare count and percentage of on-time flights for the two airlines across five cities.

city_ontime <- airline_delay_long |>
  group_by(Airline, Destination, Status) |>
  summarise(Flights = sum(Flights), .groups = "drop") |> 
  group_by(Airline, Destination) |> 
  mutate(Total_flights = sum(Flights)) |>                 
  mutate(Percentage = round((Flights / Total_flights * 100), 1)) |>
  filter(Status == "on time") |>
  select(Airline, Destination, Ontime_Flights = Flights,
         Total_flights, OnTime_Percentage = Percentage)  
  
  
kable(city_ontime, caption ="City On-time Performance")

City On-time Performance
Airline	Destination	Ontime_Flights	Total_flights	OnTime_Percentage
AM West	Los Angeles	694	811	85.6
AM West	Phoenix	4840	5255	92.1
AM West	San Diego	383	448	85.5
AM West	San Francisco	320	449	71.3
AM West	Seattle	201	262	76.7
Alaska	Los Angeles	497	559	88.9
Alaska	Phoenix	221	233	94.8
Alaska	San Diego	212	232	91.4
Alaska	San Francisco	503	605	83.1
Alaska	Seattle	1841	2146	85.8

On time by City chart

ggplot(
  city_ontime,
  aes(
    x = Destination,
    y = OnTime_Percentage,
    fill = Airline
  )
) +
  geom_col(
    position = "dodge"
  ) +
  labs(
    title = "On-Time Percentage by Destination and Airline",
    x = "Destination",
    y = "On-Time Flights (%)",
    fill = "Airline"
  ) +
  theme_classic() +
  theme(
    axis.text.x = element_text(
      angle = 45,
      hjust = 1
    )
  )

Key takeaways on City specific On-time performance

1.This graph indicates that Alaska outperforms AM West across the board For each city—Los Angeles, Phoenix, San Diego, San Francisco, and Seattle—Alaska’s bar is higher. Even without exact numbers, the visual gap is obvious.

2.The performance gap varies by destination Largest gap: Likely San Francisco or Seattle, where Alaska’s bar is much higher.

Smallest gap: Possibly Phoenix, where the difference looks narrower.

This suggests that AM West struggles more in certain markets than others.

Both airlines show destination‑specific variability Some cities have noticeably higher on‑time percentages for both airlines (e.g., Seattle), while others show lower performance (e.g., San Francisco).
Alaska’s operational consistency stands out The red bars (Alaska) stay relatively high and stable across destinations. AM West’s teal bars fluctuate more and generally sit lower.