DATA 607 Week 5A – Airline Delays Approach

Author

Patricio Romero

Published

October 1, 2026

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 will store the original observations in their supplied wide format. R will 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 total number of flights and delayed 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.

library(DBI)
library(RPostgres)
library(dplyr)
library(tidyr)
library(ggplot2)
library(knitr)

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"
)

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:

4 total records
2 populated airline cells
2 intentionally 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 of the Original PostgreSQL Records
Validation Check	Result
Total records	4
Populated airline cells	2
Missing airline cells	2

Planned Deliverables

The completed assignment will include:

The original wide-format CSV file
Reproducible R and Quarto code
The populated and tidied long-format dataset
Overall airline delay percentages
City-level airline delay percentages
Tables and charts comparing the airlines
A written explanation of the results and discrepancy
A public GitHub repository
An RPubs report
A video presentation

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.