Flight Delay

Introduction

We were asked to analyze flight data across two carriers, Alaska and AM West Airlines. Nobody likes flight delays, it impacts customer satisfaction and causes operational inefficiency. This analysis will evaluate the arrival times for these two airlines and measure their delay arrival.

Business Question

Which airline has better on-time performance? Specifically, we want to know if one carrier is more reliable than the other across different locations.

Strategy

1. Data Ingestion We are going to recreate the raw untidy table in R from the instructions we were given.
2. Data Tidying Since we know the data in not in tidy format, we are going to pivot the data from a wide format (where city names are column headers) to a long format using tidyr::pivot_longer(), to make eash row represent a single Airline, Status, City, Flight_Count

Load packages

# Load packages
pacman::p_load(tidyverse)

Data acquisition and cleaning

# Data Source
url <- "https://raw.githubusercontent.com/Dave-Melchor/Data-607-Data-Acquisition-and-Management/refs/heads/main/data/raw/airline_delays.csv"

# Bring in data
flight_raw <- read_csv(url)

Rows: 4 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.

# Fill-in missing values
flight_raw <- flight_raw |> 
  fill(Airline)

# Pivot the data long
flights <- flight_raw |> 
  pivot_longer(
    cols = c(`Los Angeles`, Phoenix, `San Diego`, `San Francisco`, Seattle),
    names_to = "City",
    values_to = "Flight_Count"
  )

Airline comparison

# Find overall percent difference between airlines
airline_summary <- flights |> 
  group_by(Airline) |> 
  summarise(
    `Total Flights` = sum(Flight_Count),
    `Total Delayed` = sum(Flight_Count[Status == "Delayed"]),
    `Delay Percent` = round((`Total Delayed` / `Total Flights`), 3) * 100
  )

airline_summary

# A tibble: 2 × 4
  Airline `Total Flights` `Total Delayed` `Delay Percent`
  <chr>             <dbl>           <dbl>           <dbl>
1 ALASKA             3775             501            13.3
2 AM WEST            7222             787            10.9

Overall, AM West has a lower delay rate of 10.9% when compared to Alaska 13.3%.

City by city comparison

# Find city by city percent difference 
city_summary <- flights |> 
  group_by(Airline, City) |> 
  summarise(
    `Total Flights` = sum(Flight_Count),
    `Total Delayed` = sum(Flight_Count[Status == "Delayed"]),
    `Delayed Percent` = round((`Total Delayed` / `Total Flights`), 3) * 100) |> 
  arrange(City)

city_summary

# A tibble: 10 × 5
# Groups:   Airline [2]
   Airline City          `Total Flights` `Total Delayed` `Delayed Percent`
   <chr>   <chr>                   <dbl>           <dbl>             <dbl>
 1 ALASKA  Los Angeles               559              62              11.1
 2 AM WEST Los Angeles               808             117              14.5
 3 ALASKA  Phoenix                   233              12               5.2
 4 AM WEST Phoenix                  5255             415               7.9
 5 ALASKA  San Diego                 232              20               8.6
 6 AM WEST San Diego                 448              65              14.5
 7 ALASKA  San Francisco             605             102              16.9
 8 AM WEST San Francisco             449             129              28.7
 9 ALASKA  Seattle                  2146             305              14.2
10 AM WEST Seattle                   262              61              23.3

City by city, AM West Airlines has higher percent delays in every city when compared to Alaska.

Conclusion

When comparing the airlines city by city, Alaska Airlines actually outperforms AM West in every single location, maintaining a lower percentage of delayed flights across the board. However, because AM West operates a massive volume of its flights in Phoenix (5,255 out of 7,225 total flights)—where delays are naturally low—those favorable numbers pull down their overall delay average. Conversely, the majority of Alaska’s flights originate in Seattle (2,146 out of 3,775 total flights), a location with higher overall delay rates. AM West appears superior at an aggregate level because of its heavy flight presence in a low-delay city.