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

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.

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

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.

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.

City-Level Airline Performance Comparison

After examining the overall results, I will compare the two airlines separately within each of the five cities.

For each airline and city, I will calculate the percentage of flights that were delayed. I will then compare these percentages across the cities.

The results will be presented using:

• A summary table of city-level percentages.
• Grouped bar charts to show differences between the airlines.
• A short written interpretation of the main patterns.

Looking at the results by city will provide more detail than the overall comparison alone and will help determine whether the overall results are consistent across individual cities.

Analysis of Overall and City-Level Differences

One of the main purposes of this analysis is to determine whether the overall comparison agrees with the city-level comparisons.

If the overall results and the city-level results lead to different conclusions, I will investigate why this occurs. One possible explanation is that the airlines have different distributions of flights across the five cities.

The overall percentage is effectively a weighted result because cities with more flights contribute more to the overall percentage. Therefore, an airline’s overall performance can be affected by where it operates more flights.

For example, an airline may have a higher delay percentage in several individual cities but still have a different overall percentage if it operates a larger share of its flights in a city where its delay rate is lower.

This part of the analysis will help demonstrate why it is important to consider both aggregated results and results within individual groups.

Data Considerations

There are several data-related issues that I expect to address during the analysis:

• The original dataset is not in tidy format and will need to be restructured.
• Blank header cells may need to be handled before the data can be transformed.
• Flight counts must be converted to numeric values.
• Percentage calculations must use the correct denominator.
• Raw flight counts should not be used as a direct measure of airline performance when the airlines have different numbers of flights.
• The overall results and city-level results need to be interpreted separately.
• Any difference between the overall and city-level comparisons should be explained using the distribution of flights across cities.

Reproducible Analysis

I will use the following steps to make the analysis reproducible:

• Store the recreated CSV file in a public GitHub repository.
• Read the dataset from the GitHub raw URL rather than from a local computer path.
• Use R and tidyverse functions for the data transformation and analysis.
• Keep the data-cleaning and transformation steps in the Quarto document.
• Avoid using local file paths so that the code can be run on another computer.
• Make sure that the complete analysis can be reproduced in a clean R environment.

Reproducibility

Code

Manual Verification-Step 1

Manual Verification-Step 2

Automated Validation

Final Output

This structured approach ensures that the dataset is recreated faithfully, transformed properly, analyzed rigorously, and interpreted clearly.

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

csv_name <- "Airline_delays_wide.csv"

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

list.files()

 [1] "5A_Approach_rpubs.txt"               
 [2] "5B_Approach_rpubs.txt"               
 [3] "Airline_delays_wide.csv"             
 [4] "backup"                              
 [5] "Data-607-Assignment-5A-CodeBase.html"
 [6] "Data-607-Assignment-5A-CodeBase.Rmd" 
 [7] "Data-607-Assignment-5A_Approach.html"
 [8] "Data-607-Assignment-5A_Approach.rmd" 
 [9] "Data-607-Assignment-5B_Approach.html"
[10] "Data-607-Assignment-5B_Approach.rmd" 
[11] "github-copy.txt"                     
[12] "rpubs-5a-code-base.txt"              
[13] "rsconnect"                           

dataset <- "https://raw.githubusercontent.com/suffyankhan77/Assignment5A-DATA-607/main/Airline_delays_wide.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

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_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() +
  scale_y_continuous(labels = scales::percent_format())

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") +
  scale_y_continuous(labels = scales::percent_format()) +
  coord_flip()

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

# A tibble: 10 × 5
   Airline City          city_total airline_total weight
   <fct>   <fct>              <int>         <int>  <dbl>
 1 ALASKA  Seattle             2146          3775 0.568 
 2 ALASKA  San Francisco        605          3775 0.160 
 3 ALASKA  Los Angeles          559          3775 0.148 
 4 ALASKA  Phoenix              233          3775 0.0617
 5 ALASKA  San Diego            232          3775 0.0615
 6 AMWEST  Phoenix             5255          7225 0.727 
 7 AMWEST  Los Angeles          811          7225 0.112 
 8 AMWEST  San Francisco        449          7225 0.0621
 9 AMWEST  San Diego            448          7225 0.0620
10 AMWEST  Seattle              262          7225 0.0363

city_delayed %>%
  left_join(weights, by = c("Airline", "City")) %>%
  select(Airline, City, pct_delayed, city_total, weight) %>%
  arrange(City, Airline)

# A tibble: 10 × 5
   Airline City          pct_delayed city_total weight
   <fct>   <fct>               <dbl>      <int>  <dbl>
 1 ALASKA  Los Angeles        0.111         559 0.148 
 2 AMWEST  Los Angeles        0.144         811 0.112 
 3 ALASKA  Phoenix            0.0515        233 0.0617
 4 AMWEST  Phoenix            0.0790       5255 0.727 
 5 ALASKA  San Diego          0.0862        232 0.0615
 6 AMWEST  San Diego          0.145         448 0.0620
 7 ALASKA  San Francisco      0.169         605 0.160 
 8 AMWEST  San Francisco      0.287         449 0.0621
 9 ALASKA  Seattle            0.142        2146 0.568 
10 AMWEST  Seattle            0.233         262 0.0363