Airline Delay Data

The given data had arrival information for Alaska Airlines and AM West throughout five destinations. I first recreated the data in a wide form alike to the original source table.

airline_data <- data.frame(
  Airline = c("ALASKA", "", "AM WEST", ""),
  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_data
##   Airline  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
write.csv(airline_data, "airline_delays.csv", row.names = FALSE)

Reading CSV

I plan to read the CSV file into R in order to clean and tranform the data.

airline_raw <- read.csv("airline_delays.csv")

airline_raw
##   Airline  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

Clean Vacant Ariline Names

The given table has vacancies on the airline name on each delayed row because the airline name is implied by the row above it. I converted these blank values to missing values and then filled them using the aforementioned airline name.

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
library(tidyr)

airline_clean <- airline_raw %>%
  mutate(Airline = na_if(Airline, "")) %>%
  fill(Airline)

airline_clean
##   Airline  Status Los_Angeles Phoenix San_Diego San_Francisco Seattle
## 1  ALASKA on time         497     221       212           503    1841
## 2  ALASKA delayed          62      12        20           102     305
## 3 AM WEST on time         694    4840       383           320     201
## 4 AM WEST delayed         117     415        65           129      61

Tranformation - wide to long

To make tidy the data, I have to transform the five destination columns into two variable - destination city & # of flights.

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

airline_long
## # A tibble: 20 × 4
##    Airline Status  City          Flights
##    <chr>   <chr>   <chr>           <int>
##  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

Airline Analysis

I first compared the general percentage of delayed flights for every airline. Utilizing percentages unstead of just counts makes for a more compelling comparison since the airlines contain distinct total flight counts.

overall_delays <- airline_long %>%
  group_by(Airline, Status) %>%
  summarise(Flights = sum(Flights), .groups = "drop") %>%
  group_by(Airline) %>%
  mutate(Percentage = Flights / sum(Flights) * 100)

overall_delays
## # A tibble: 4 × 4
## # Groups:   Airline [2]
##   Airline Status  Flights Percentage
##   <chr>   <chr>     <int>      <dbl>
## 1 ALASKA  delayed     501       13.3
## 2 ALASKA  on time    3274       86.7
## 3 AM WEST delayed     787       10.9
## 4 AM WEST on time    6438       89.1

In general, Alaska had a delay rate of about 13.27 % wheras AM West had a delay rate of about 10.89%. From this reading, AM West is apparently better due to a more efficent timing.

#Delay Rates Across Cities

city_delays <- airline_long %>%
  group_by(Airline, City) %>%
  mutate(
    Total_Flights = sum(Flights),
    Percentage = Flights / Total_Flights * 100
  ) %>%
  filter(Status == "delayed") %>%
  select(Airline, City, Flights, Total_Flights, Percentage)

city_delays
## # A tibble: 10 × 5
## # Groups:   Airline, City [10]
##    Airline City          Flights Total_Flights Percentage
##    <chr>   <chr>           <int>         <int>      <dbl>
##  1 ALASKA  Los_Angeles        62           559      11.1 
##  2 ALASKA  Phoenix            12           233       5.15
##  3 ALASKA  San_Diego          20           232       8.62
##  4 ALASKA  San_Francisco     102           605      16.9 
##  5 ALASKA  Seattle           305          2146      14.2 
##  6 AM WEST Los_Angeles       117           811      14.4 
##  7 AM WEST Phoenix           415          5255       7.90
##  8 AM WEST San_Diego          65           448      14.5 
##  9 AM WEST San_Francisco     129           449      28.7 
## 10 AM WEST Seattle            61           262      23.3
Glancing at the airlines idnicate that throughout the 5 destinations, Alaska has a lower delay percentage than AM West in each city. This is different from the general comparison, where AM West had the lower all-in-all delay percentage. Thus, the city-by-city analysis paints a different story. 
This difference happens because of distinct distributions of flights throughout the five cities. For instance, AM West contains a great count of flights in Phoeniz, where both airlines have fairly low delay rates. Alska, on the other hand, has a large # of flights in Seattle, in which delay rates tend to be higher. Consequently, combining all cities shifts the weight of the observations and allows AM West's general delay rate seem lower despite Alska performing on its individual urban locales.

Added Visual

library(ggplot2)

ggplot(city_delays, aes(x = City, y = Percentage, fill = Airline)) +
  geom_col(position = "dodge") +
  labs(
    title = "Airline Delay Percentages by City",
    x = "City",
    y = "Delay Percentage"
  ) +
  theme_minimal()

Conclusion

Ultimately, the percentages provides ample telling of AM West’s delay rate. Around 10.89% of AM West flights were delayed compared to about 13.27% of Alaska flights. Looking at the general results, AM West seems to have done better. Taking a more urban focus showcases that Alaska had a lower delay percentage than AM West in all 5 spots. This implies that overall percentages don’t give the clear picture. The difference essentially comes from hwo flights are distributed throughout cities. AM West contained a plethora of flights in Phoeniz, where delay rates were more or less low, while Alaska contained numerous flights in Seattle, where the delay rates were noticeably greater.