knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE)
library(tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.2.1     ✔ readr     2.2.0
## ✔ forcats   1.0.1     ✔ stringr   1.6.0
## ✔ ggplot2   4.0.3     ✔ tibble    3.3.1
## ✔ lubridate 1.9.5     ✔ tidyr     1.3.2
## ✔ purrr     1.2.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(knitr)
library(scales)

## 
## Attaching package: 'scales'
## 
## The following object is masked from 'package:purrr':
## 
##     discard
## 
## The following object is masked from 'package:readr':
## 
##     col_factor

Step 1: Building the Raw CSV File

As mentioned above, data rarely arrives neatly formatted. In order to practice t real-world data cleaning, I need to recreate the source table exactly as it appears in reality like messy cells, missing values, and all.

The Missing Airline Names: The original table doesn’t repeat ‘ALASKA’ on the delayed row or ‘AM WEST’ on its delayed row. It leaves those cells blank because humans reading a printout visually group them. Software, however, will treat those empty cells as missing values (NA). I must keep them blank in your CSV so my code learns how to fix them.
The Empty Separator Row: Placing a completely empty row between ALASKA and AM WEST simulates untidy spreadsheet layouts where users add blank rows for visual spacing.
The Formatting: Notice numbers like “1,841” have commas. In raw files, commas turn numbers into text strings, which breaks calculations. I want to leave them in the CSV to handle string cleaning later. thereafter, the file is created it was stored at github, the link is as follows:

https://github.com/Muhammad-Imran91/607/blob/main/data.csv

The following code then, will upload this datafile direct from the github.

# I have Used the direct raw GitHub URL
data_url <- "https://raw.githubusercontent.com/Muhammad-Imran91/607/main/data.csv"

raw_df <- read_csv(data_url, show_col_types = FALSE)

# Standardize or rename first column if needed (e.g., if first column is named `...1` or `X1`)
if (!"Airline" %in% colnames(raw_df)) {
  raw_df <- raw_df %>% rename(Airline = 1)
  
}

Step 2: Cleaning and Preparing the Data

Before I can analyze anything, I have to bring the dataset into a structured format where every row represents an observation and every column represents a variable. * Dropping Empty Rows: Remove rows that contain no useful information. The blank row between airlines was put there for human eyes. Machines get confused by empty rows, so I drop any row where all columns are empty. * Filling Missing Airline Names (Down-Filling): Fill the empty airline cells with the correct airline name. Since row 2 belongs to ALASKA’s delayed flights and row 5 belongs to AM WEST’s delayed flights, I use a ‘downward fill.’ This looks at the cell above and copies ‘ALASKA’ down into row 2, and ‘AM WEST’ down into row 5. * Converting Text to Numbers: Turn formatted strings like “1,841” into actual numbers like 1841. I cannot perform mathematical operations on text. Removing commas and converting the flight counts into integers lets your code do math on them

kable(raw_df, caption = "Folloing is a Raw Untidy Input Data")

Folloing is a Raw Untidy Input Data
Airline	Status	Los Angeles	Phoenix	San Diego	San Francisco	Seattle
ALASKA	on time	497	221	212	503	1841
NA	delayed	62	12	20	102	305
NA	NA	NA	NA	NA	NA	NA
AM WEST	on time	694	4840	383	320	201
NA	delayed	117	415	65	129	61

cleaned_wide_df <- raw_df %>%
  # Filter out completely empty rows
  filter(!if_all(everything(), is.na)) %>%
  # Fill missing Airline names downward
  fill(Airline, .direction = "down") %>%
  # Parse numeric columns formatted with commas
  mutate(across(c(`Los Angeles`, Phoenix, `San Diego`, `San Francisco`, Seattle), 
                ~ as.numeric(gsub(",", "", .))))

kable(cleaned_wide_df, caption = "Following is a Cleaned Wide Data Structure")

Following is a Cleaned Wide Data Structure
Airline	Status	Los Angeles	Phoenix	San Diego	San Francisco	Seattle
ALASKA	on time	497	221	212	503	1841
ALASKA	delayed	62	12	20	102	305
AM WEST	on time	694	4840	383	320	201
AM WEST	delayed	117	415	65	129	61

Step 3: Transforming Wide Data to Long Data

Unpivoting Cities (Gathering/Lengthening): Collapse the 5 city columns into a single column called ‘City’, with a second column called ‘Count’ holding the values. Instead of having five separate columns for cities, I stretch the data vertically. Now every individual flight count has its own row stating the Airline, Status, City, and Count.
Pivoting Flight Status (Widening Status): Split ‘on time’ and ‘delayed’ status into two distinct numeric columns side-by-side. Having ‘on_time’ and ‘delayed’ as two separate columns on the same row makes calculating performance rates straightforward. For every Airline + City pair, I can directly add them together to get total_flights.
Calculating Performance Metrics: Compute key performance ratios for each observation:
Total Flights = On-Time Flights + Delayed Flights
Delay Rate = Delayed Flights / Total Flights
On-Time Rate = On-Time Flights / Total Flights

flights_long <- cleaned_wide_df %>%
  pivot_longer(
    cols = c(`Los Angeles`, Phoenix, `San Diego`, `San Francisco`, Seattle),
    names_to = "City",
    values_to = "Count"
  ) %>%
  pivot_wider(
    names_from = Status,
    values_from = Count
  ) %>%
  rename(
    on_time = `on time`,
    delayed = delayed
  ) %>%
  mutate(
    total_flights = on_time + delayed,
    delay_rate = delayed / total_flights,
    on_time_rate = on_time / total_flights
  )

kable(flights_long, caption = "Tidied Long Data Format with Delay Rates")

Tidied Long Data Format with Delay Rates
Airline	City	on_time	delayed	total_flights	delay_rate	on_time_rate
ALASKA	Los Angeles	497	62	559	0.1109123	0.8890877
ALASKA	Phoenix	221	12	233	0.0515021	0.9484979
ALASKA	San Diego	212	20	232	0.0862069	0.9137931
ALASKA	San Francisco	503	102	605	0.1685950	0.8314050
ALASKA	Seattle	1841	305	2146	0.1421249	0.8578751
AM WEST	Los Angeles	694	117	811	0.1442663	0.8557337
AM WEST	Phoenix	4840	415	5255	0.0789724	0.9210276
AM WEST	San Diego	383	65	448	0.1450893	0.8549107
AM WEST	San Francisco	320	129	449	0.2873051	0.7126949
AM WEST	Seattle	201	61	262	0.2328244	0.7671756

Step 4: Overall Airline Delay Analysis

Detailed Breakdown

1. Summing Aggregate Totals: ALASKA has 62 + 12 + 20 + 102 + 305 = 501 delayed flights out of 3,775 total flights. AM WEST has 117 + 415 + 65 + 129 + 61 = 787 delayed flights out of 7,225 total flights.
Calculating Overall Percentages: ALASKA Overall Delay Rate = 501 / 3,775 = 13.3% AM WEST Overall Delay Rate = 787 / 7,225 = 10.9%

Looking purely at aggregate numbers across all destinations, AM WEST appears to be the better airline. It delays only 10.9% of its flights, whereas ALASKA delays 13.3%. If a customer asks I for a recommendation based purely on overall averages, I would be tempted to conclude that AM WEST is more reliable.

overall_summary <- flights_long %>%
  group_by(Airline) %>%
  summarise(
    total_on_time = sum(on_time),
    total_delayed = sum(delayed),
    total_flights = sum(total_flights),
    overall_delay_rate = sum(delayed) / sum(total_flights),
    overall_on_time_rate = sum(on_time) / sum(total_flights)
  )

kable(overall_summary, 
      digits = 4, 
      col.names = c("Airline", "On-Time Count", "Delayed Count", "Total Flights", "Overall Delay Rate", "Overall On-Time Rate"),
      caption = "Below is an Overall Flight Performance Summary")

Below is an Overall Flight Performance Summary
Airline	On-Time Count	Delayed Count	Total Flights	Overall Delay Rate	Overall On-Time Rate
ALASKA	3274	501	3775	0.1327	0.8673
AM WEST	6438	787	7225	0.1089	0.8911

Key Findings & Insights / Overall Performances

Volume Comparison

AM WEST operates significantly larger flight operations, handling 7,225 total flights compared to ALASKA’s 3,775 flights.
AM WEST accounts for ~65.7% of all combined flights in this dataset, while ALASKA accounts for ~34.3%.

Punctuality & Delay Rates

AM WEST performed better overall in punctuality, achieving an 89.11% on-time rate and maintaining a lower overall delay rate (10.89%).
ALASKA recorded a higher delay rate at 13.27%, with an on-time rate of 86.73%.
Out of every 100 flights, ALASKA experiences roughly 2.4 more delayed flights than AM WEST on an aggregate basis.

Combined Operations Baseline

Across both airlines combined, a total of 11,000 flights were recorded (9,712 on-time and 1,288 delayed).
The combined overall delay rate across all flights sits at 11.71%, giving an aggregate on-time reliability score of 88.29%.

Important Analytical Note (Simpson’s Paradox)

While AM WEST shows a lower aggregate delay rate overall (10.89% vs. 13.27%), this dataset is a classic example of Simpson’s Paradox in data science:

AM WEST operates a massive majority of its flights out of Phoenix (a clear-weather hub with low baseline delay rates across all carriers).
ALASKA operates a higher proportion of its flights out of Seattle and San Francisco (airports prone to fog and poor weather delays).
When evaluated city-by-city, ALASKA actually achieves a lower delay rate than AM WEST in every single individual destination city. However, because AM WEST’s flight volume is concentrated heavily in a sunny hub, its overall aggregate delay number appears superior.

ggplot(overall_summary, aes(x = Airline, y = overall_delay_rate, fill = Airline)) +
  geom_col(width = 0.5) +
  scale_y_continuous(labels = percent_format(accuracy = 0.1)) +
  labs(title = "Overall Delay Rate by Airline",
       y = "Percentage of Delayed Flights",
       x = "Airline") +
  theme_minimal() +
  geom_text(aes(label = percent(overall_delay_rate, accuracy = 0.1)), vjust = -0.5)

city_summary <- flights_long %>%
  select(Airline, City, delayed, total_flights, delay_rate)

kable(city_summary, 
      digits = 4,
      col.names = c("Airline", "City", "Delayed Count", "Total Flights", "Delay Rate"),
      caption = "Delay Rate Breakdown by City")

Delay Rate Breakdown by City
Airline	City	Delayed Count	Total Flights	Delay Rate
ALASKA	Los Angeles	62	559	0.1109
ALASKA	Phoenix	12	233	0.0515
ALASKA	San Diego	20	232	0.0862
ALASKA	San Francisco	102	605	0.1686
ALASKA	Seattle	305	2146	0.1421
AM WEST	Los Angeles	117	811	0.1443
AM WEST	Phoenix	415	5255	0.0790
AM WEST	San Diego	65	448	0.1451
AM WEST	San Francisco	129	449	0.2873
AM WEST	Seattle	61	262	0.2328

Key Insights & Analysis / performances city by city

ALASKA Wins Across All 5 Cities * In San Francisco, AM WEST’s delay rate (\(28.73\%\)) is nearly 12 percentage points higher than ALASKA’s (\(16.86\%\)).
* In Seattle, AM WEST’s delay rate (\(23.28\%\)) is 9 percentage points higher than ALASKA’s (\(14.21\%\)).
* Across Los Angeles, Phoenix, and San Diego, ALASKA consistently stays \(3\%\) to \(6\%\) lower in delay rates compared to AM WEST.

Explaining the Simpson’s Paradox (Flight Volume Distribution)

AM WEST’s Volume Concentration: Out of AM WEST’s 7,225 total flights, 5,255 flights (72.7%) are routed to Phoenix—the city with the lowest delay rate for both airlines (\(5.15\%\) and \(7.90\%\)). Because almost three-quarters of AM WEST’s flights fly into a low-delay airport, its overall average delay rate is pulled down artificially.
ALASKA’s Volume Concentration: Out of ALASKA’s 3,775 total flights, 2,751 flights (72.9%) are routed to Seattle (\(2,146\)) and San Francisco (\(605\))—cities with high baseline delay rates due to weather and air traffic congestion. This heavy weighting in high-delay airports pulls ALASKA’s overall aggregate average up.

ggplot(flights_long, aes(x = City, y = delay_rate, fill = Airline)) +
  geom_col(position = "dodge") +
  scale_y_continuous(labels = percent_format(accuracy = 0.1)) +
  labs(title = "Delay Rate by Airline Across 5 Cities",
       y = "Percentage of Delayed Flights",
       x = "Destination City") +
  theme_minimal() +
  geom_text(aes(label = percent(delay_rate, accuracy = 0.1)), 
            position = position_dodge(width = 0.9), vjust = -0.5, size = 3)

How can ALASKA be better in every single city, yet AM WEST appear better overall?

Human Explanation of the Paradox

This statistical phenomenon is known as Simpson’s Paradox. It occurs when an underlying ‘hidden variable’, in this case, where each airline concentrates its flight volume; distorts the aggregated results.

Look at the Hub Destinations & Flight Distributions
AM WEST is centered in Phoenix: AM WEST flew 5,255 out of its 7,225 total flights to Phoenix. That means over 72% of AM WEST’s entire operation is concentrated in Phoenix. Phoenix happens to have exceptionally favorable weather and low delay rates for all airlines (~5% to 8% delays).
ALASKA is centered in Seattle & San Francisco: ALASKA flew 2,146 out of its 3,775 total flights to Seattle and San Francisco. This represents 57% of ALASKA’s entire operation. These airports suffer from heavy fog, severe weather delays, and high air traffic congestion (~14% to 28% delays across all carriers).

Why the Overall Average Lies

Skewed Weights for AM WEST: AM WEST’s aggregate score is heavily skewed by Phoenix’s easy flying conditions. Because 72% of its flights are in sunny Phoenix, its poor delay performance in cities like San Francisco (28.7%) gets drowned out in the total calculation.
Skewed Weights for ALASKA: Even though ALASKA handles San Francisco and Seattle much better than AM WEST does, the massive volume of flights ALASKA operates in those difficult weather hubs pulls down its overall aggregate performance.

## Word document successfully saved as 'All_Assignment_Tables_R.docx'!

IS 607 Assignment - Tidying and Transforming Data

Muhammad Imran

2026-10-03