Week 5 - Airline Delays

Author

Muhammad Ali

Airline Delays Analysis

Approach

Background Information

The goal of this analysis is to get comfortable with Exploratory Data Analysis (EDA) as well as the concept of tidying and transforming our data.

Dataset

With this analysis, we will need to recreate the data in a .CSV file with the exact information to the one below:

The Plan

Creating and importing the data
- Create the dataset in Google Sheets using the data above. Then import to R.
Data Cleaning
- Column names
- Handle Missing Values
- Normalizing the data so it is on the same scale
- Converting from a wide table to a long table format so it is easier for mathematical computation
Conduct an EDA
- Create visualizations of comparing arrivals or delays for 2 airlines overall
- Create visualizations of comparing arrivals or delays for 2 airlines across 5 cities
- Describe discrepancy between comparing two airlines’ flight performances city-by-city and overall with a visualization
- Explain discrepancy between comparing two airlines’ flight performances city-by-city and overall
Revise Explanations
- After each visualization or code chunk, make sure it is not too technical and easy to comprehend

Codebase

Packages To Install

Remove the ‘#’ to install package if you do not have it install already.

Code

# install.packages("tidyverse")

Libraries to import

Code

library(tidyverse)

Reading in the data

Code

raw_data <- read.csv("https://raw.githubusercontent.com/HaiderrX/CUNY-SPS-MSDS/refs/heads/main/DATA607/Week%205%20-%20Working%20with%20Tidy%20Data/week5_airline_delays%20-%20Sheet1.csv")

# head of data
head(raw_data)

  airline  status Los.Angeles Phoenix San.Diego San.Francisco Seattle
1  ALASKA on time         497     221       212           503   1,841
2         delayed          62      12        20           102     305
3                          NA                NA            NA        
4 AM WEST on time         694   4,480       383           320     201
5         delayed         117     415        65           129      61

Code

#glimpse of data
glimpse(raw_data)

Rows: 5
Columns: 7
$ airline       <chr> "ALASKA", "", "", "AM WEST", ""
$ status        <chr> "on time", "delayed", "", "on time", "delayed"
$ Los.Angeles   <int> 497, 62, NA, 694, 117
$ Phoenix       <chr> "221", "12", "", "4,480", "415"
$ San.Diego     <int> 212, 20, NA, 383, 65
$ San.Francisco <int> 503, 102, NA, 320, 129
$ Seattle       <chr> "1,841", "305", "", "201", "61"

Data Inspection

We need to fix the column names so all of them are lower cased. In addition we need to replace the “.” to an underscore for spacing
Get rid of that missing line as it is affecting certain columns by adding the NA value or converting them to characters.
Convert chr to numeric types
Fill in the missing airline for the “delayed” rows so that when we convert to a long format it is easier to manipulate the data.

Data Cleaning

Column Rename

Code

columns_renamed <- raw_data %>%
  rename(
    airline = airline,
    airline_status = status,
    los_angeles = Los.Angeles,
    phoenix = Phoenix,
    san_diego = San.Diego,
    san_francisco = San.Francisco,
    seattle = Seattle
  )

colnames(columns_renamed)

[1] "airline"        "airline_status" "los_angeles"    "phoenix"       
[5] "san_diego"      "san_francisco"  "seattle"

Filling Missing Values & Numeric Conversion

Code

clean_data_wide <- columns_renamed |>
  mutate(across(where(is.character), ~ na_if(trimws(.x), ""))) |>
  filter(if_any(everything(), ~ !is.na(.x))) |>
  mutate(across(-c(airline, airline_status),
                ~ parse_number(as.character(.x)))) |>
  fill(airline)

head(clean_data_wide)

  airline 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    4480       383           320     201
4 AM WEST        delayed         117     415        65           129      61

Code

glimpse(clean_data_wide)

Rows: 4
Columns: 7
$ airline        <chr> "ALASKA", "ALASKA", "AM WEST", "AM WEST"
$ airline_status <chr> "on time", "delayed", "on time", "delayed"
$ los_angeles    <dbl> 497, 62, 694, 117
$ phoenix        <dbl> 221, 12, 4480, 415
$ san_diego      <dbl> 212, 20, 383, 65
$ san_francisco  <dbl> 503, 102, 320, 129
$ seattle        <dbl> 1841, 305, 201, 61

Wide Table to Long Table

For this analysis, to make it easier for us to do mathematical computations, it is best if we convert the table from a wide to a long format.

Code

clean_data_long <- clean_data_wide |>
  pivot_longer(
    cols = c(los_angeles, phoenix, san_diego, san_francisco, seattle),
    names_to = "city",
    values_to = "value"
  )

clean_data_long

# A tibble: 20 × 4
   airline airline_status city          value
   <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        4480
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

Exploratory Data Analysis

Comparing Delays Across Airlines

Getting Overall Statistics

Code

overall <- clean_data_long |>
  group_by(airline, airline_status) |>
  summarise(total = sum(value), .groups = "drop") |>
  group_by(airline) |>
  mutate(pct = total / sum(total)) |>
  ungroup()

overall

# A tibble: 4 × 4
  airline airline_status total   pct
  <chr>   <chr>          <dbl> <dbl>
1 ALASKA  delayed          501 0.133
2 ALASKA  on time         3274 0.867
3 AM WEST delayed          787 0.115
4 AM WEST on time         6078 0.885

Filtering for Overall Delayed

We want to compare with delayed so lets filter for that and set it to a new variable.

Code

overall_delayed <- overall |>
  filter(airline_status == "delayed")

overall_delayed

# A tibble: 2 × 4
  airline airline_status total   pct
  <chr>   <chr>          <dbl> <dbl>
1 ALASKA  delayed          501 0.133
2 AM WEST delayed          787 0.115

Plotting Bar Plot

Code

ggplot(overall_delayed, aes(x = airline, y = pct, fill = airline)) +
  geom_col(width = 0.3) +
  geom_text(aes(label = scales::percent(pct, accuracy = 0.1)), vjust = -0.3) +
  scale_y_continuous(labels = scales::percent,
                     limits = c(0, max(overall_delayed$pct) * 1.2)) +
    labs(title = "Share of Flights Delayed, All Cities Combined",
       x = NULL, y = "Percent of flights delayed") +
  theme_minimal() +
  theme(legend.position = "none")

Based on this combined city plot, Alaska airlines leads with the highest overall delay rate at 13.3%, while AM West follows at 11.5%. Given a choice between these two airlines heading to the same city, one would be better off flying with AM West as there is a less chance of a delay.

Comparing Arrivals Across Airlines

Filtering for Overall Arrivals

We want to compare with delayed so lets filter for that and set it to a new variable.

Code

overall_arrival <- overall |>
  filter(airline_status == "on time")  

overall_arrival

# A tibble: 2 × 4
  airline airline_status total   pct
  <chr>   <chr>          <dbl> <dbl>
1 ALASKA  on time         3274 0.867
2 AM WEST on time         6078 0.885

Plotting Bar Plot

Code

ggplot(overall_arrival, aes(x = airline, y = pct, fill = airline)) +
  geom_col(width = 0.3) +
  geom_text(aes(label = scales::percent(pct, accuracy = 0.1)), vjust = -0.3) +
  scale_y_continuous(labels = scales::percent,
                     limits = c(0, max(overall_arrival$pct) * 1.2)) +
    labs(title = "Share of Flights Arrival on Time, All Cities Combined",
       x = NULL, y = "Percent of flights arrived on time") +
  theme_minimal() +
  theme(legend.position = "none")

With an added visual to support our previous findings, Alaska Airlines have an on time rate of 86.7% versus AM West’s 88.5%. The point remains that AM West’s airlines arrives on time more often than Alaska airlines.

Comparing Delays Across Cities

Filtering for Cities

Code

city_rates <- clean_data_long |>
  group_by(airline, city) |>
  mutate(pct = value / sum(value)) |>
  ungroup()

city_rates

# A tibble: 20 × 5
   airline airline_status city          value    pct
   <chr>   <chr>          <chr>         <dbl>  <dbl>
 1 ALASKA  on time        los_angeles     497 0.889 
 2 ALASKA  on time        phoenix         221 0.948 
 3 ALASKA  on time        san_diego       212 0.914 
 4 ALASKA  on time        san_francisco   503 0.831 
 5 ALASKA  on time        seattle        1841 0.858 
 6 ALASKA  delayed        los_angeles      62 0.111 
 7 ALASKA  delayed        phoenix          12 0.0515
 8 ALASKA  delayed        san_diego        20 0.0862
 9 ALASKA  delayed        san_francisco   102 0.169 
10 ALASKA  delayed        seattle         305 0.142 
11 AM WEST on time        los_angeles     694 0.856 
12 AM WEST on time        phoenix        4480 0.915 
13 AM WEST on time        san_diego       383 0.855 
14 AM WEST on time        san_francisco   320 0.713 
15 AM WEST on time        seattle         201 0.767 
16 AM WEST delayed        los_angeles     117 0.144 
17 AM WEST delayed        phoenix         415 0.0848
18 AM WEST delayed        san_diego        65 0.145 
19 AM WEST delayed        san_francisco   129 0.287 
20 AM WEST delayed        seattle          61 0.233

Filtering for delayed rates

Code

city_rates_delayed <- city_rates |>
  filter(airline_status == "delayed")

city_rates_delayed

# A tibble: 10 × 5
   airline airline_status city          value    pct
   <chr>   <chr>          <chr>         <dbl>  <dbl>
 1 ALASKA  delayed        los_angeles      62 0.111 
 2 ALASKA  delayed        phoenix          12 0.0515
 3 ALASKA  delayed        san_diego        20 0.0862
 4 ALASKA  delayed        san_francisco   102 0.169 
 5 ALASKA  delayed        seattle         305 0.142 
 6 AM WEST delayed        los_angeles     117 0.144 
 7 AM WEST delayed        phoenix         415 0.0848
 8 AM WEST delayed        san_diego        65 0.145 
 9 AM WEST delayed        san_francisco   129 0.287 
10 AM WEST delayed        seattle          61 0.233

Code

city_rates_arrival <- city_rates |>
  filter(airline_status == "on time")

city_rates_arrival

# A tibble: 10 × 5
   airline airline_status city          value   pct
   <chr>   <chr>          <chr>         <dbl> <dbl>
 1 ALASKA  on time        los_angeles     497 0.889
 2 ALASKA  on time        phoenix         221 0.948
 3 ALASKA  on time        san_diego       212 0.914
 4 ALASKA  on time        san_francisco   503 0.831
 5 ALASKA  on time        seattle        1841 0.858
 6 AM WEST on time        los_angeles     694 0.856
 7 AM WEST on time        phoenix        4480 0.915
 8 AM WEST on time        san_diego       383 0.855
 9 AM WEST on time        san_francisco   320 0.713
10 AM WEST on time        seattle         201 0.767

Plotting Bar Plot

Code

plot_city <- function(data, title, ylab) {
  data |>
    mutate(city = str_to_title(str_replace_all(city, "_", " "))) |>
    ggplot(aes(x = city, y = pct, fill = airline)) +
    geom_col(position = "dodge") +
    geom_text(aes(label = scales::percent(pct, accuracy = 0.1)),
              position = position_dodge(width = 0.9), vjust = -0.5, size = 3) +
    scale_y_continuous(labels = scales::percent,
                       limits = c(0, max(data$pct) * 1.2)) +
    labs(title = title, x = NULL, y = ylab, fill = "Airline") +
    theme_minimal()
}

plot_city(city_rates_delayed, "Share of Flights Delayed, by City", "Percent delayed")

With a side by side comparison we can see for each city the delay rates between Alaska airlines and AM West. In this plot, we see that in certain cities there are more delay rates than other cities, such as San Francisco and Seattle), and for the most part AM West have more of a delay rate in each city than Alaska airlines.

Code

plot_city(city_rates_arrival,  "Share of Flights On Time, by City", "Percent on time")

With the same format of seeing the data side by side, it is shown that Alaska airlines have more of an arrival rate than AM West. However for certain cities as previously mentioned both airlines don’t have similar numbers compared to the rest of the cities.

Discrepency Within the Data Across Airlines and by City

Interestingly enough, both plots seem to be telling two different stories:

Overall one airline is better than the other, in this case AM West.
City by City AM West airlines doesn’t perform better than the Alaska airlines.

This can be caused by certain cities performing better in terms of on-time arrivals as well as the amount of flights sent to a certain city. For example, it seems that there are more flights sent to Phoenix and San Diego versus San Francisco or Seattle.

Conclusion

The challenge in this assignment was figuring out the correct plot to display the information in an comprehensible way, and for all of them luckily was set to be a bar plot and the tools needed to fill up the plot.

In addition to figuring out the correct plot was drawing conclusions for why the data was being presented in the way it was presented, but nonetheless it builds up critical thinking skills. In this instance the discrepancy that an aggregated plot versus a city by city plot were showing two different things.

AI Use

Claude - Guidance for assignment