library(DBI)
library(RPostgres)
library(dplyr)
library(tidyr)
library(ggplot2)
library(knitr)DATA 607 Week 5A – Airline Delays Approach
Introduction
Airline performance can be evaluated by comparing the number and percentage of flights that arrive on time or experience delays. However, comparisons based only on flight counts may be misleading because airlines do not operate the same number of flights in every city.
The objective of this assignment is to organize, tidy, and analyze flight-arrival data for Alaska Airlines and AM West across five destinations. The final analysis will compare the overall delay percentages of the two airlines and their delay percentages within each destination.
PostgreSQL was used to store the original observations in their supplied wide format. R was used to retrieve the data, populate the intentionally missing airline names, transform the data from wide to long format, calculate percentages, and present the findings.
Data Source
The original dataset was provided by the instructor as an image as part of the course assignment Tidying and Transforming Data. Because the source was not provided in a machine-readable format, I manually recreated the table as airline_delays.csv, preserving the original structure, flight counts, and empty airline cells.
The recreated CSV file was then imported into PostgreSQL for storage and retrieved into R for reproducible data preparation and analysis.
The airlines are:
- Alaska Airlines
- AM West
The destinations are:
- Los Angeles
- Phoenix
- San Diego
- San Francisco
- Seattle
The recreated source dataset contains four rows representing on-time and delayed flights for the two airlines. As in the supplied source table, the airline name is intentionally blank in two rows.
Data Dictionary
| Variable | Description |
|---|---|
airline |
Name of the airline |
status |
Flight-arrival category: on time or delayed |
los_angeles |
Number of flights for Los Angeles |
phoenix |
Number of flights for Phoenix |
san_diego |
Number of flights for San Diego |
san_francisco |
Number of flights for San Francisco |
seattle |
Number of flights for Seattle |
Database Design
The recreated source data were saved as airline_delays.csv. I used pgAdmin 4 to create the PostgreSQL database data607_week5a and the table public.airline_delays_raw.
The PostgreSQL table preserves the original wide-format structure of the source data. A generated identifier named record_id was added to preserve the original row order. The remaining columns contain the airline name, flight status, and flight counts for each of the five destinations.
The two empty airline cells from the original source were preserved as missing values when the CSV file was imported into PostgreSQL. These values were intentionally not populated in the database because the assignment requires reproducible code to populate the missing data during the R data-preparation process.
PostgreSQL Table Creation
The database and table were created in PostgreSQL using pgAdmin 4. The SQL definition used to create the table is included below to document the database structure used for this assignment.
CREATE TABLE public.airline_delays_raw (
record_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
airline VARCHAR(50),
status VARCHAR(20) NOT NULL,
los_angeles INTEGER NOT NULL,
phoenix INTEGER NOT NULL,
san_diego INTEGER NOT NULL,
san_francisco INTEGER NOT NULL,
seattle INTEGER NOT NULL
);The airline column permits missing values so that the empty cells in the original source can be preserved. The destination columns store the original flight counts without modification.
Planned Approach
The analysis will follow these steps:
- Retrieve the original wide-format records from PostgreSQL.
- Confirm that the table contains four records and two intentionally missing airline values.
- Populate the missing airline names using the preceding valid airline name.
- Transform the five destination columns from wide format into long format.
- Create one row for each airline, flight status, and destination combination.
- Validate the transformed data, expected airlines, destinations, statuses, and flight counts.
- Calculate the number of on-time flights, delayed flights, and total flights for each airline.
- Calculate the overall delay percentage for each airline.
- Calculate delay percentages for each airline within each of the five destinations.
- Present the comparisons in tables and charts.
- Describe the difference between the overall and city-level comparisons.
- Explain how different flight distributions across cities produce the discrepancy.
- Publish the reproducible report on RPubs and place the project files in a public GitHub repository.
The delay percentage will be calculated as:
\[\text{Delay Percentage} = \frac{\text{Delayed Flights}} {\text{On-Time Flights} + \text{Delayed Flights}} \times 100\]
Data Retrieval from PostgreSQL
The required packages are loaded before connecting to PostgreSQL.
The database password is retrieved from the local .Renviron file and is not stored in this document or published on GitHub.
con <- dbConnect(
RPostgres::Postgres(),
host = "127.0.0.1",
port = 55433,
dbname = "data607_week5a",
user = "postgres",
password = Sys.getenv("DATA607_DB_PASSWORD")
)The original wide-format records are retrieved directly from PostgreSQL. The missing airline values remain unchanged during this retrieval step.
airline_delays_raw <- dbGetQuery(
con,
"
SELECT
record_id,
airline,
status,
los_angeles,
phoenix,
san_diego,
san_francisco,
seattle
FROM public.airline_delays_raw
ORDER BY record_id
"
)
knitr::kable(
airline_delays_raw,
col.names = c(
"Record ID",
"Airline",
"Status",
"Los Angeles",
"Phoenix",
"San Diego",
"San Francisco",
"Seattle"
),
align = c("r", "l", "l", "r", "r", "r", "r", "r"),
caption = "Original wide-format airline-delay data"
)| Record ID | Airline | Status | Los Angeles | Phoenix | San Diego | San Francisco | Seattle |
|---|---|---|---|---|---|---|---|
| 1 | ALASKA | on time | 497 | 221 | 212 | 503 | 1841 |
| 2 | NA | delayed | 62 | 12 | 20 | 102 | 305 |
| 3 | AM WEST | on time | 694 | 4840 | 383 | 320 | 201 |
| 4 | NA | delayed | 117 | 415 | 65 | 129 | 61 |
Initial Database Validation
Before beginning the transformation and analysis in R, the PostgreSQL table was inspected. The initial validation confirmed:
4total records2populated airline cells2intentionally missing airline cells- Complete numeric counts for all five destinations
No missing airline values were filled and no delay percentages were calculated during this initial database-validation stage.
validation_summary <- data.frame(
validation_check = c(
"Total records",
"Populated airline cells",
"Missing airline cells"
),
result = c(
nrow(airline_delays_raw),
sum(!is.na(airline_delays_raw$airline)),
sum(is.na(airline_delays_raw$airline))
)
)
knitr::kable(
validation_summary,
col.names = c("Validation Check", "Result"),
align = c("l", "c"),
caption = "Validation of the Original PostgreSQL Records"
)| Validation Check | Result |
|---|---|
| Total records | 4 |
| Populated airline cells | 2 |
| Missing airline cells | 2 |
Populating Missing Airline Names
The original source data contain two intentionally missing airline names. Each missing value belongs to the same airline identified in the preceding row.
To populate these missing values reproducibly, the airline names are filled downward using the preceding valid airline name. The original flight counts and flight-status values are not modified.
airline_delays_filled <- airline_delays_raw %>%
tidyr::fill(airline, .direction = "down")
knitr::kable(
airline_delays_filled,
col.names = c(
"Record ID",
"Airline",
"Status",
"Los Angeles",
"Phoenix",
"San Diego",
"San Francisco",
"Seattle"
),
align = c("r", "l", "l", "r", "r", "r", "r", "r"),
caption = "Wide-format data after populating missing airline names"
)| Record ID | 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 |
Transforming the Data from Wide to Long Format
The original dataset stores the five destinations in separate columns. To create a tidy structure for analysis, these destination columns are transformed from wide format into long format.
The transformation creates two new variables: destination, which identifies the city, and flight_count, which contains the corresponding number of flights. The airline and flight-status variables are preserved.
airline_delays_long <- airline_delays_filled %>%
pivot_longer(
cols = c(
los_angeles,
phoenix,
san_diego,
san_francisco,
seattle
),
names_to = "destination",
values_to = "flight_count"
) %>%
mutate(
destination = recode(
destination,
los_angeles = "Los Angeles",
phoenix = "Phoenix",
san_diego = "San Diego",
san_francisco = "San Francisco",
seattle = "Seattle"
)
)
knitr::kable(
airline_delays_long,
col.names = c(
"Record ID",
"Airline",
"Status",
"Destination",
"Flight Count"
),
align = c("r", "l", "l", "l", "r"),
caption = "Airline-delay data transformed from wide to long format"
)| Record ID | Airline | Status | Destination | Flight Count |
|---|---|---|---|---|
| 1 | ALASKA | on time | Los Angeles | 497 |
| 1 | ALASKA | on time | Phoenix | 221 |
| 1 | ALASKA | on time | San Diego | 212 |
| 1 | ALASKA | on time | San Francisco | 503 |
| 1 | ALASKA | on time | Seattle | 1841 |
| 2 | ALASKA | delayed | Los Angeles | 62 |
| 2 | ALASKA | delayed | Phoenix | 12 |
| 2 | ALASKA | delayed | San Diego | 20 |
| 2 | ALASKA | delayed | San Francisco | 102 |
| 2 | ALASKA | delayed | Seattle | 305 |
| 3 | AM WEST | on time | Los Angeles | 694 |
| 3 | AM WEST | on time | Phoenix | 4840 |
| 3 | AM WEST | on time | San Diego | 383 |
| 3 | AM WEST | on time | San Francisco | 320 |
| 3 | AM WEST | on time | Seattle | 201 |
| 4 | AM WEST | delayed | Los Angeles | 117 |
| 4 | AM WEST | delayed | Phoenix | 415 |
| 4 | AM WEST | delayed | San Diego | 65 |
| 4 | AM WEST | delayed | San Francisco | 129 |
| 4 | AM WEST | delayed | Seattle | 61 |
Validation of the Long-Format Data
After the wide-to-long transformation, each row represents one airline, one flight status, and one destination combination.
Because the dataset contains two airlines, two flight statuses, and five destinations, the transformed dataset is expected to contain 20 rows. The transformed data are validated below to confirm the expected structure and to ensure that no flight counts are missing.
long_format_validation <- data.frame(
validation_check = c(
"Total long-format rows",
"Number of airlines",
"Number of flight statuses",
"Number of destinations",
"Missing flight counts"
),
expected = c(20, 2, 2, 5, 0),
actual = c(
nrow(airline_delays_long),
n_distinct(airline_delays_long$airline),
n_distinct(airline_delays_long$status),
n_distinct(airline_delays_long$destination),
sum(is.na(airline_delays_long$flight_count))
)
)
long_format_validation <- long_format_validation %>%
mutate(
result = ifelse(expected == actual, "PASS", "CHECK")
)
knitr::kable(
long_format_validation,
col.names = c(
"Validation Check",
"Expected",
"Actual",
"Result"
),
align = c("l", "c", "c", "c"),
caption = "Validation of the Long-Format Airline Data"
)| Validation Check | Expected | Actual | Result |
|---|---|---|---|
| Total long-format rows | 20 | 20 | PASS |
| Number of airlines | 2 | 2 | PASS |
| Number of flight statuses | 2 | 2 | PASS |
| Number of destinations | 5 | 5 | PASS |
| Missing flight counts | 0 | 0 | PASS |
Count Analysis by Airline
Before calculating percentages, the number of on-time flights, delayed flights, and total flights are summarized for each airline.
The total number of flights is calculated by adding all on-time and delayed flights across the five destinations. This count analysis provides the basis for calculating and comparing the overall delay percentages of the two airlines.
airline_count_summary <- airline_delays_long %>%
group_by(airline) %>%
summarise(
on_time_flights = sum(
flight_count[tolower(status) == "on time"]
),
delayed_flights = sum(
flight_count[tolower(status) == "delayed"]
),
total_flights = sum(flight_count),
.groups = "drop"
)
knitr::kable(
airline_count_summary,
col.names = c(
"Airline",
"On-Time Flights",
"Delayed Flights",
"Total Flights"
),
align = c("l", "r", "r", "r"),
caption = "Flight Count Analysis by Airline"
)| Airline | On-Time Flights | Delayed Flights | Total Flights |
|---|---|---|---|
| ALASKA | 3274 | 501 | 3775 |
| AM WEST | 6438 | 787 | 7225 |
Overall Delay Percentage by Airline
Flight counts alone are not sufficient for comparing the two airlines because they operated different numbers of flights. Therefore, the overall delay percentage is calculated for each airline.
The delay percentage is calculated by dividing the number of delayed flights by the total number of flights and multiplying by 100.
overall_delay_summary <- airline_count_summary %>%
mutate(
delay_percentage = (delayed_flights / total_flights) * 100
)
knitr::kable(
overall_delay_summary,
digits = 2,
col.names = c(
"Airline",
"On-Time Flights",
"Delayed Flights",
"Total Flights",
"Delay Percentage (%)"
),
align = c("l", "r", "r", "r", "r"),
caption = "Overall Delay Percentage by Airline"
)| Airline | On-Time Flights | Delayed Flights | Total Flights | Delay Percentage (%) |
|---|---|---|---|---|
| ALASKA | 3274 | 501 | 3775 | 13.27 |
| AM WEST | 6438 | 787 | 7225 | 10.89 |
Overall Comparison
Alaska had 501 delayed flights out of 3,775 total flights, resulting in an overall delay percentage of approximately 13.27%. AM West had 787 delayed flights out of 7,225 total flights, resulting in an overall delay percentage of approximately 10.89%.
Although AM West had more delayed flights in absolute numbers, it also operated substantially more flights. When the airlines are compared using percentages rather than counts, AM West had the lower overall delay rate. Therefore, AM West had better overall performance based on the delay percentage.
Delay Percentage by Destination
The overall comparison does not show how the airlines performed within individual destinations. To examine this difference, the flight counts are grouped by airline and destination.
For each airline and destination, the total number of flights, delayed flights, and delay percentage are calculated. This allows the two airlines to be compared separately across Los Angeles, Phoenix, San Diego, San Francisco, and Seattle.
city_delay_summary <- airline_delays_long %>%
group_by(airline, destination) %>%
summarise(
total_flights = sum(flight_count),
delayed_flights = sum(
flight_count[tolower(status) == "delayed"]
),
.groups = "drop"
) %>%
mutate(
delay_percentage = (delayed_flights / total_flights) * 100
) %>%
arrange(destination, airline)
knitr::kable(
city_delay_summary,
digits = 2,
col.names = c(
"Airline",
"Destination",
"Total Flights",
"Delayed Flights",
"Delay Percentage (%)"
),
align = c("l", "l", "r", "r", "r"),
caption = "Delay Percentages by Airline and Destination"
)| Airline | Destination | Total Flights | Delayed Flights | Delay Percentage (%) |
|---|---|---|---|---|
| ALASKA | Los Angeles | 559 | 62 | 11.09 |
| AM WEST | Los Angeles | 811 | 117 | 14.43 |
| ALASKA | Phoenix | 233 | 12 | 5.15 |
| AM WEST | Phoenix | 5255 | 415 | 7.90 |
| ALASKA | San Diego | 232 | 20 | 8.62 |
| AM WEST | San Diego | 448 | 65 | 14.51 |
| ALASKA | San Francisco | 605 | 102 | 16.86 |
| AM WEST | San Francisco | 449 | 129 | 28.73 |
| ALASKA | Seattle | 2146 | 305 | 14.21 |
| AM WEST | Seattle | 262 | 61 | 23.28 |
Visual Comparison of Airline Delay Percentages
The percentage comparisons are also presented graphically to make the differences between the two airlines easier to identify.
The first chart compares the overall delay percentage of Alaska and AM West. The second chart compares their delay percentages within each of the five destinations.
ggplot(
overall_delay_summary,
aes(
x = airline,
y = delay_percentage,
fill = airline
)
) +
geom_col(width = 0.6) +
geom_text(
aes(label = paste0(round(delay_percentage, 2), "%")),
vjust = -0.4
) +
labs(
title = "Overall Delay Percentage by Airline",
x = "Airline",
y = "Delay Percentage (%)"
) +
theme_minimal() +
theme(
legend.position = "none"
)
Delay Percentage by Destination
The following chart compares the delay percentages of Alaska and AM West within each of the five destinations.
ggplot(
city_delay_summary,
aes(
x = destination,
y = delay_percentage,
fill = airline
)
) +
geom_col(
position = "dodge",
width = 0.7
) +
geom_text(
aes(
label = paste0(round(delay_percentage, 2), "%")
),
position = position_dodge(width = 0.7),
vjust = -0.3,
size = 3
) +
labs(
title = "Delay Percentage by Airline and Destination",
x = "Destination",
y = "Delay Percentage (%)",
fill = "Airline"
) +
theme_minimal() +
theme(
axis.text.x = element_text(
angle = 45,
hjust = 1
)
)
Overall vs. City-Level Comparison
The overall results show that Alaska has a higher delay percentage than AM West. However, the overall percentages do not necessarily represent the pattern observed within each individual destination.
To compare the airlines city by city, the following table identifies the airline with the lower delay percentage within each destination.
city_comparison <- city_delay_summary %>%
group_by(destination) %>%
arrange(delay_percentage, .by_group = TRUE) %>%
mutate(
city_rank = row_number()
) %>%
ungroup() %>%
filter(city_rank == 1) %>%
select(
destination,
airline,
delay_percentage
)
knitr::kable(
city_comparison,
digits = 2,
col.names = c(
"Destination",
"Airline with Lower Delay Percentage",
"Delay Percentage (%)"
),
align = c("l", "l", "r"),
caption = "Airline with the Lower Delay Percentage in Each Destination"
)| Destination | Airline with Lower Delay Percentage | Delay Percentage (%) |
|---|---|---|
| Los Angeles | ALASKA | 11.09 |
| Phoenix | ALASKA | 5.15 |
| San Diego | ALASKA | 8.62 |
| San Francisco | ALASKA | 16.86 |
| Seattle | ALASKA | 14.21 |
City-Level Comparison
The city-level comparison shows a different pattern from the overall comparison. Overall, AM West has the lower delay percentage, at approximately 10.89%, compared with 13.27% for Alaska.
However, when the airlines are compared separately within each destination, Alaska has the lower delay percentage in all five cities. Alaska’s delay percentages are approximately 11.09% in Los Angeles, 5.15% in Phoenix, 8.62% in San Diego, 16.86% in San Francisco, and 14.21% in Seattle.
Therefore, the overall comparison suggests that AM West performs better, while the city-by-city comparison shows that Alaska performs better within every destination. This demonstrates that the overall percentages alone can give a different impression of airline performance.
Explaining the Overall and City-Level Discrepancy
The difference between the overall and city-level results can occur because the two airlines do not operate the same proportion of their flights in each destination.
To examine this effect, the following analysis calculates the number and percentage of each airline’s total flights that are associated with each destination.
flight_distribution <- city_delay_summary %>%
group_by(airline) %>%
mutate(
airline_total_flights = sum(total_flights),
flight_share_percentage =
(total_flights / airline_total_flights) * 100
) %>%
ungroup() %>%
select(
airline,
destination,
total_flights,
flight_share_percentage,
delay_percentage
) %>%
arrange(destination, airline)
knitr::kable(
flight_distribution,
digits = 2,
col.names = c(
"Airline",
"Destination",
"Total Flights",
"Share of Airline Flights (%)",
"Delay Percentage (%)"
),
align = c("l", "l", "r", "r", "r"),
caption = "Distribution of Flights and Delay Percentages by Destination"
)| Airline | Destination | Total Flights | Share of Airline Flights (%) | Delay Percentage (%) |
|---|---|---|---|---|
| ALASKA | Los Angeles | 559 | 14.81 | 11.09 |
| AM WEST | Los Angeles | 811 | 11.22 | 14.43 |
| ALASKA | Phoenix | 233 | 6.17 | 5.15 |
| AM WEST | Phoenix | 5255 | 72.73 | 7.90 |
| ALASKA | San Diego | 232 | 6.15 | 8.62 |
| AM WEST | San Diego | 448 | 6.20 | 14.51 |
| ALASKA | San Francisco | 605 | 16.03 | 16.86 |
| AM WEST | San Francisco | 449 | 6.21 | 28.73 |
| ALASKA | Seattle | 2146 | 56.85 | 14.21 |
| AM WEST | Seattle | 262 | 3.63 | 23.28 |
Flight Distribution Comparison
The following table compares how the flights of each airline are distributed across the five destinations. This information helps explain why the aggregated overall percentages can differ from the city-level comparisons.
flight_distribution_comparison <- flight_distribution %>%
select(
airline,
destination,
flight_share_percentage
) %>%
pivot_wider(
names_from = airline,
values_from = flight_share_percentage
)
knitr::kable(
flight_distribution_comparison,
digits = 2,
align = c("l", "r", "r"),
caption = "Percentage Distribution of Each Airline's Flights by Destination"
)| destination | ALASKA | AM WEST |
|---|---|---|
| Los Angeles | 14.81 | 11.22 |
| Phoenix | 6.17 | 72.73 |
| San Diego | 6.15 | 6.20 |
| San Francisco | 16.03 | 6.21 |
| Seattle | 56.85 | 3.63 |
Explanation of the Discrepancy
The overall and city-level comparisons produce different conclusions because the airlines do not have the same distribution of flights across the five destinations.
The overall delay percentage is a weighted result. Destinations with more flights have a greater effect on an airline’s overall percentage than destinations with fewer flights. Therefore, the overall percentage is not simply the average of the five city-level delay percentages.
In this dataset, Alaska has a lower delay percentage than AM West within each of the five destinations. However, the airlines operate different numbers and proportions of flights across those destinations. These different weights change the aggregated results, causing AM West to have the lower overall delay percentage even though Alaska has the lower delay percentage in every individual city.
This reversal between the aggregated comparison and the comparisons within individual groups is an example of Simpson’s paradox. It demonstrates why both overall percentages and group-level percentages should be examined before drawing conclusions from aggregated data.
Conclusion
This analysis transformed the original airline-delay data from a wide, partially incomplete format into a complete and tidy long-format dataset.
The original missing airline names were populated reproducibly in R, and the five destination columns were transformed into a single destination variable. Count analysis and percentage calculations were then used to compare Alaska and AM West.
Overall, Alaska had 501 delayed flights out of 3,775 total flights, for a delay percentage of approximately 13.27%. AM West had 787 delayed flights out of 7,225 total flights, for a delay percentage of approximately 10.89%.
Although AM West had the lower overall delay percentage, Alaska had the lower delay percentage within each of the five destinations. The difference is explained by the unequal distribution of flights across destinations, which gives different weights to the city-level results when the overall percentages are calculated.
The analysis demonstrates the importance of examining both aggregated and group-level percentages when comparing performance.
Database Connection Cleanup
The PostgreSQL connection is closed after the required records have been retrieved and validated.
dbDisconnect(con)AI Use
ChatGPT was used to help interpret the assignment requirements, organize the planned approach, improve the English writing, design the PostgreSQL table, and provide coding guidance. I recreated and imported the airline data, ran and reviewed the code, validated the database records, and will independently review and confirm the conclusions of the final analysis.