DATA 607-Assignment 5A – Airline Delays: CodeBase

Bikash Bhowmik —- 03-Oct-2026

Introduction

In this assignment, I will analyze airline on-time performance data for two airlines, Alaska and Amwest, across five cities. The original dataset is provided in a wide format, with separate counts for on-time and delayed flights for each airline and destination.

The main goal of this assignment is to recreate the dataset in R, convert it into a tidy format, and compare the performance of the two airlines. I will first look at the overall performance of each airline and then compare their performance within each city. The comparison will focus on percentages instead of raw flight counts.

An important part of the analysis will be to examine what happens if the overall comparison gives a different result from the comparisons within individual cities. I will use the data to explain why this difference can occur.

Data Analysis Approach

The analysis will begin by recreating the original airline delay dataset and transforming it into a tidy format. I will calculate the percentage of delayed flights for each airline overall and within each city. The results will be presented using tables and visualizations to compare Alaska and Amwest. Finally, I will compare the overall and city-level results to examine how differences in flight distribution across cities may affect the overall comparison.

Dataset Preparation

First, I will recreate the original dataset programmatically in R using a wide format similar to the source data. Because the original wide-format table contains blank cells used as visual grouping, I will use R code to populate those missing labels before transforming the data into tidy format.

After creating the dataframe, I will export it as a CSV file using write.csv(). The CSV file will then be uploaded to a public GitHub repository. For the analysis, I will read the data directly from the GitHub raw URL so that the work can be reproduced without relying on a local file.

The dataset will contain information for:

• Airlines: Alaska and Amwest
• Flight status: On Time and Delayed
• Destination cities:
  • Los Angeles
  • Phoenix
  • San Diego
  • San Francisco
  • Seattle

Dataset Preparation (and intentionally include missing structural values)

Below I create the dataset in wide format to match the original structure (cities as columns). To satisfy the “populate missing data” requirement, I intentionally leave the airline name as NA on the second row of each airline block and later fill it in programmatically.

airline_wide_raw <- tibble::tibble(
  Airline = c("ALASKA", NA, "AMWEST", NA),
  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_wide_raw

# A tibble: 4 × 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 AMWEST  on_time           694    4840         383             320     201
4 <NA>    delayed           117     415          65             129      61

Data Loading and Validation

After importing the CSV file into R, I will examine the data before making any transformations.

I will:

• Check the column names and overall structure.
• Identify and handle any blank or missing structural values.
• Make sure that all flight counts are stored as numeric values.
• Check the totals for each airline.
• Check the totals for each city.
• Confirm that the data are complete and consistent.

If any structural values are missing because of the original wide format, I will handle them programmatically before continuing with the analysis.

airline_wide <- airline_wide_raw %>%
  tidyr::fill(Airline, .direction = "down")

airline_wide

# A tibble: 4 × 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 ALASKA  delayed            62      12          20             102     305
3 AMWEST  on_time           694    4840         383             320     201
4 AMWEST  delayed           117     415          65             129      61

Save CSV, upload to GitHub, then read back via Raw link (reproducibility)

This writes the recreated wide dataset to a CSV.

csv_name <- "Airline_delays_wide_data.csv"

if (!file.exists(csv_name)) {
  write.csv(airline_wide, csv_name, row.names = FALSE)
}

## list.files()

dataset <- "https://raw.githubusercontent.com/BIKASHBHOWMIK15/Data-607/main/Assignment5/Airline_delays_wide_data.csv"

airline_wide_from_source <- if (nzchar(dataset)) {
  read.csv(dataset, check.names = FALSE)
} else {
  read.csv(csv_name, check.names = FALSE)
}

airline_wide_from_source

  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  AMWEST on_time         694    4840       383           320     201
4  AMWEST delayed         117     415        65           129      61

Data Transformation to Tidy Format

The original data are in a wide format, so I will transform them into a tidy, long format using tidyr and dplyr.

The final tidy dataset will contain one row for each combination of airline, city, and flight status, with the corresponding flight count.

The main variables will be:

• airline
• city
• status
• count

The status variable will identify whether the flights were on_time or delayed, while count will contain the number of flights.

Using tidy data will make it easier to calculate percentages and perform consistent comparisons across airlines and cities.

Converted from wide format (cities as columns) to long format where each row is:

Airline, Status, City, Count

airline_long <- airline_wide_from_source %>%
  as_tibble() %>%
  pivot_longer(
    cols = -c(Airline, Status),
    names_to = "City",
    values_to = "Count"
  ) %>%
  mutate(
    Airline = as.factor(Airline),
    Status  = as.factor(Status),
    City    = as.factor(City),
    Count   = as.integer(Count)
  )

airline_long %>% arrange(Airline, City, Status)

# A tibble: 20 × 4
   Airline Status  City          Count
   <fct>   <fct>   <fct>         <int>
 1 ALASKA  delayed Los Angeles      62
 2 ALASKA  on_time Los Angeles     497
 3 ALASKA  delayed Phoenix          12
 4 ALASKA  on_time Phoenix         221
 5 ALASKA  delayed San Diego        20
 6 ALASKA  on_time San Diego       212
 7 ALASKA  delayed San Francisco   102
 8 ALASKA  on_time San Francisco   503
 9 ALASKA  delayed Seattle         305
10 ALASKA  on_time Seattle        1841
11 AMWEST  delayed Los Angeles     117
12 AMWEST  on_time Los Angeles     694
13 AMWEST  delayed Phoenix         415
14 AMWEST  on_time Phoenix        4840
15 AMWEST  delayed San Diego        65
16 AMWEST  on_time San Diego       383
17 AMWEST  delayed San Francisco   129
18 AMWEST  on_time San Francisco   320
19 AMWEST  delayed Seattle          61
20 AMWEST  on_time Seattle         201

# Total flights by airline
airline_long %>%
  group_by(Airline) %>%
  summarise(total_flights = sum(Count), .groups = "drop")

# A tibble: 2 × 2
  Airline total_flights
  <fct>           <int>
1 ALASKA           3775
2 AMWEST           7225

# Total flights by airline and city
airline_long %>%
  group_by(Airline, City) %>%
  summarise(total_flights = sum(Count), .groups = "drop") %>%
  arrange(Airline, desc(total_flights))

# A tibble: 10 × 3
   Airline City          total_flights
   <fct>   <fct>                 <int>
 1 ALASKA  Seattle                2146
 2 ALASKA  San Francisco           605
 3 ALASKA  Los Angeles             559
 4 ALASKA  Phoenix                 233
 5 ALASKA  San Diego               232
 6 AMWEST  Phoenix                5255
 7 AMWEST  Los Angeles             811
 8 AMWEST  San Francisco           449
 9 AMWEST  San Diego               448
10 AMWEST  Seattle                 262

Overall Airline Performance Comparison

Next, I will compare the overall performance of Alaska and Amwest.

For each airline, I will:

1. Calculate the total number of flights.
2. Calculate the total number of delayed flights.
3. Calculate the percentage of flights that were delayed.
4. Calculate the percentage of flights that were on time if needed.
5. Present the results in a summary table and/or visualization.

The main comparison will use percentages rather than the number of delayed flights. This is important because the two airlines may have different total numbers of flights.

overall_perf <- airline_long %>%
  group_by(Airline, Status) %>%
  summarise(n = sum(Count), .groups = "drop") %>%
  group_by(Airline) %>%
  mutate(
    total = sum(n),
    pct = n / total
  ) %>%
  ungroup()

overall_perf

# A tibble: 4 × 5
  Airline Status      n total   pct
  <fct>   <fct>   <int> <int> <dbl>
1 ALASKA  delayed   501  3775 0.133
2 ALASKA  on_time  3274  3775 0.867
3 AMWEST  delayed   787  7225 0.109
4 AMWEST  on_time  6438  7225 0.891

overall_delayed <- overall_perf %>%
  filter(Status == "delayed") %>%
  transmute(
    Airline,
    delayed = n,
    total,
    pct_delayed = pct
  )

overall_delayed

# A tibble: 2 × 4
  Airline delayed total pct_delayed
  <fct>     <int> <int>       <dbl>
1 ALASKA      501  3775       0.133
2 AMWEST      787  7225       0.109

overall_delayed %>%
  ggplot(aes(x = Airline, y = pct_delayed)) +
  geom_col(fill = "lightblue") +
  geom_text(
    aes(label = scales::percent(pct_delayed, accuracy = 0.1)),
    vjust = -0.3,
    size = 4
  ) +
  scale_y_continuous(
    labels = scales::percent_format(),
    expand = expansion(mult = c(0, 0.1))
  ) +
  labs(
    title = "Overall Percentage of Delayed Flights by Airline",
    x = "Airline",
    y = "Percentage of Delayed Flights"
  )

city_perf <- airline_long %>%
  group_by(Airline, City, Status) %>%
  summarise(n = sum(Count), .groups = "drop") %>%
  group_by(Airline, City) %>%
  mutate(
    total = sum(n),
    pct = n / total
  ) %>%
  ungroup()

city_delayed <- city_perf %>%
  filter(Status == "delayed") %>%
  transmute(Airline, City, delayed = n, total, pct_delayed = pct)

city_delayed %>% arrange(City, Airline)

# A tibble: 10 × 5
   Airline City          delayed total pct_delayed
   <fct>   <fct>           <int> <int>       <dbl>
 1 ALASKA  Los Angeles        62   559      0.111 
 2 AMWEST  Los Angeles       117   811      0.144 
 3 ALASKA  Phoenix            12   233      0.0515
 4 AMWEST  Phoenix           415  5255      0.0790
 5 ALASKA  San Diego          20   232      0.0862
 6 AMWEST  San Diego          65   448      0.145 
 7 ALASKA  San Francisco     102   605      0.169 
 8 AMWEST  San Francisco     129   449      0.287 
 9 ALASKA  Seattle           305  2146      0.142 
10 AMWEST  Seattle            61   262      0.233 

city_delayed %>%
  ggplot(aes(x = City, y = pct_delayed, fill = Airline)) +
  geom_col(position = "dodge") +
  geom_text(
    aes(label = scales::percent(pct_delayed, accuracy = 0.1)),
    position = position_dodge(width = 0.9),
    hjust = -0.1,
    size = 3.5
  ) +
  scale_y_continuous(
    labels = scales::percent_format(),
    expand = expansion(mult = c(0, 0.15))
  ) +
  coord_flip() +
  labs(
    title = "Percentage of Delayed Flights by City",
    x = "City",
    y = "Delayed Flights (%)",
    fill = "Airline"
  )

city_winner <- city_delayed %>%
  group_by(City) %>%
  slice_min(order_by = pct_delayed, n = 1, with_ties = TRUE) %>%
  ungroup() %>%
  select(City, Airline, pct_delayed) %>%
  arrange(City)

city_winner

# A tibble: 5 × 3
  City          Airline pct_delayed
  <fct>         <fct>         <dbl>
1 Los Angeles   ALASKA       0.111 
2 Phoenix       ALASKA       0.0515
3 San Diego     ALASKA       0.0862
4 San Francisco ALASKA       0.169 
5 Seattle       ALASKA       0.142 

weights <- airline_long %>%
  group_by(Airline, City) %>%
  summarise(
    city_total = sum(Count),
    .groups = "drop"
  ) %>%
  group_by(Airline) %>%
  mutate(
    airline_total = sum(city_total),
    weight = city_total / airline_total
  ) %>%
  ungroup() %>%
  arrange(Airline, desc(weight))

weights %>%
  knitr::kable(
    col.names = c(
      "Airline",
      "City",
      "City Total",
      "Airline Total",
      "Flight Weight"
    ),
    digits = 3
  )

city_delayed %>%
  left_join(weights, by = c("Airline", "City")) %>%
  select(Airline, City, pct_delayed, city_total, weight) %>%
  arrange(City, Airline) %>%
  knitr::kable(
    col.names = c("Airline", "City", "Delayed %", "Total Flights", "Flight Weight"),
    digits = 3
  )