Class 607, Assignment 4a

Author

Troy Tournat

Published

October 1, 2026

Directions: Tidying and Transforming Data

The chart above describes arrival delays for two airlines across five destinations. Your task is to:

Create a .CSV file (or optionally, a MySQL database!) that includes all of the information above. You’re encouraged to use a “wide” structure similar to how the information appears above, so that you can practice tidying and transformations as described below.
Read the information from your .CSV file into R, and use tidyr and dplyr as needed to tidy and transform your data.
Perform analysis to compare the arrival delays for the two airlines.
Your code should be in an R Markdown file, posted to rpubs.com, and should include narrative descriptions of your data cleanup work, analysis, and conclusions. Please include in your homework submission: The URL to the .Rmd file in your GitHub repository. and The URL for your rpubs.com web page.

Introduction

This dataset was created in Visual Studio Code using PostgreSQL extension and was created by copying the source, including the format.

Tackling the problem: First I’ll need to replicate the dataset, then import into R. I’ll do this by replicating my code from the previous assignment. Then I’ll need to do the data cleaning in R including restructuring to prepare for an analysis to compare the delay data only.

Anticipated data challenges: not a challenge, but just tedious to clean up. Also will need to review what exact analysis to do.

Citations:

Anthropic. (2025). Claude Opus 4.5 [Large language model]. (https://claude.ai/) Accessed September 30, 2026. Link to chat.

Analysis

#Not shown: Location on computer where I am grabbing the data and creating a saved location

Code

#Install packages needed 
pacman::p_load(
  # Package Install and Management
  pacman,           # package install/load
  janitor,          #clean up data names 
  
  # Project and File Management
  readr,            # import data
  httr,             #github passkey 
  usethis,          #r environment
  RPostgres,        #SQL 
  DBI,              #SQL 
  rstuioapi,        #api
  knitr,            #displaying data in quarto
  
  # General Data Management
  dplyr,            # data management
  tidyr,            # data management
  skimr,            # data management 
  lubridate,        # work with dates
  zoo,              # work with dates
  tidyverse,        # work with dates
  scales,           # ggplot 
  gmodels,          # freq tables SAS 
  patchwork        #data visualization, side by side 

)

Installing package into 'C:/Users/tournat/AppData/Local/R/win-library/4.6'
(as 'lib' is unspecified)

Code

#Load data
#connect to database 
sql <- dbConnect(
  RPostgres::Postgres(),
  host     = Sys.getenv("PG_HOST"),
  port     = as.integer(Sys.getenv("PG_PORT")),
  dbname   = Sys.getenv("PG_DB"),
  user     = Sys.getenv("PG_USER"),
  password = Sys.getenv("PG_PASSWORD")
)

#extract data created 
numbersense <- dbGetQuery(sql, "SELECT * FROM numbersense;")

numbersense

Code

#desktop
#movies <- read.csv(paste0(csv_location, "movies_edit.csv"), header=TRUE, colClasses = "character",  na.strings = c("NULL")) 

##Review and clean data 

#cleaning names 
numbersense <- numbersense %>% clean_names

Data cleaning

Code

#reformat 
numbersense_cleaned <- numbersense%>% 
  rename(
    airlines = x, 
    airline_timeliness = x_2
  )%>% 
  mutate(
    airlines = case_when( 
      airlines == "ALASKA" ~ "alaska", 
      airline_timeliness == "delayed" & los_angeles == "62" ~ "alaska", 
      airlines == "AM WEST" ~ "am west", 
      airline_timeliness == "delayed" & los_angeles == "117" ~ "am west"
      ))%>% 
  drop_na()

#reformat better 
numbersense_cleaned2 <- numbersense%>% 
  rename(
    airlines = x, 
    airline_timeliness = x_2
  )%>% 
  # copy the airline name down into the blank cells below it
  fill(airlines, .direction = "down") %>%
  mutate(
    airlines = str_to_lower(airlines),
    airline_timeliness = str_to_lower(airline_timeliness),
    total_row = rowSums(across(-c(airlines, airline_timeliness)))
    )%>%
  adorn_totals("row", name = "total")%>% # janitor:: 
  drop_na()

numbersense_cleaned2

Code

#two airlines 
#alaska 
alaska <- numbersense_cleaned2%>% 
  filter(airlines == "alaska")

alaska <-  alaska%>%
  select(airlines, airline_timeliness, total_row)%>% 
  pivot_longer(airlines)%>% 
  select(name, airline_timeliness, total_row)%>% 
  mutate(pct_total = total_row / sum(total_row))

alaska

Code

#am west 
amwest <- numbersense_cleaned2%>% 
  filter(airlines == "am west")

amwest <- amwest %>%
  select(airlines, airline_timeliness, total_row)%>% 
  pivot_longer(airlines)%>% 
  select(name, airline_timeliness, total_row)%>% 
  mutate(pct_total = total_row / sum(total_row))

amwest

Analysis 1 Compared percentage (not just counts) of either delays or arrival rates for two airlines overall.

Code

pct <- numbersense_cleaned2 %>%
  filter(airlines != "total") %>%
  select(airlines, airline_timeliness, total_row)%>% 
  mutate(pct_total = total_row / sum(total_row), .by = airlines)  #rather than separating, but need to filter out the total

pct_table <- pct %>%
  select(airlines, airline_timeliness, pct_total) %>%
  pivot_wider(names_from = airline_timeliness, values_from = pct_total)%>% 
  mutate(across(-airlines, ~ percent(.x, accuracy = )))  #recommended to get percents 

kable(pct_table, col.names = c("Airline", "On time", "Delayed"))  # display in quarto
#graphs 

#better
#colors()  

pct |>
  ggplot(aes(x = airlines, y = pct_total, fill = airline_timeliness)) +
   geom_col() +
    geom_text(
    aes(label = percent(pct_total, accuracy = 0.1)),
    position = position_stack(vjust = 0.5),   # centered in each segment
    color = "white",
    size = 4,
    fontface = "bold"
  ) +
  scale_y_continuous(labels = percent) +
  scale_fill_manual(values = c("delayed" = "maroon", "on time" = "steelblue1")) +
  labs(
    title = "Percent of arrival timeliness by airline",
    x = "", 
    y = "", 
    fill = "legend"
  ) +
  theme_minimal(base_size = 10) + 
  theme(axis.text.x = element_text(size = 11))

Table 1: Share of flights by airline and timeliness

Airline	On time	Delayed
alaska	86.7%	13.3%
am west	88.6%	11.4%

Summary: Overall we see that Am West had slightly more on time flights (89%) compared to Alaska airlines (87%) across all airports.

Analysis 2 Compared percentage (not just counts) of either delays or arrival rates for two airlines across five cities. [Either with a chart, or a table; both would be exemplary]. Include text summarizes findings from this comparison.

Code

#pivot longer for every city

##am west## 
amwest_pct <- numbersense_cleaned2 %>%
  filter(airlines == "am west") %>%
  pivot_longer(
    cols = -c(airlines, airline_timeliness),
    names_to = "airport",
    values_to = "value"
  )%>% 
  mutate(pct_total = value / sum(value), .by = airport)%>% 
  select(airlines, airline_timeliness, airport, value, pct_total)

#create table
amwest_table <- amwest_pct %>%
  select(airline_timeliness, airport, pct_total) %>%
  pivot_wider(names_from = c(airline_timeliness), values_from = pct_total)%>% 
  mutate(across(-airport, ~ percent(.x, accuracy = )))  #recommended to get percents 

kable(amwest_table, 
      col.names = c("Airport", "On Time", "Delayed"), 
      caption = "Percent of Am West flights on time vs delayed, by airport city")  # display in quarto

Percent of Am West flights on time vs delayed, by airport city
Airport	On Time	Delayed
los_angeles	77.10%	22.90%
phoenix	92.10%	7.90%
san_diego	85.49%	14.51%
san_francisco	71.27%	28.73%
seattle	76.72%	23.28%
total_row	88.64%	11.36%

Code

#create stacked bar chart 
amwest_plot <- amwest_pct |>
  ggplot(aes(x = airport, y = pct_total, fill = airline_timeliness)) +
   geom_col() +
    geom_text(
    aes(label = percent(pct_total, accuracy = 0.1)),
    position = position_stack(vjust = 0.5),   # centered in each segment
    color = "white",
    size = 3,
    fontface = "bold"
  ) +
  scale_y_continuous(labels = percent) +
  scale_fill_manual(values = c("delayed" = "maroon", "on time" = "steelblue1")) +
  labs(
    title = "Am West airlines",
    x = "", 
    y = "", 
    fill = "legend"
  ) +
  theme_minimal(base_size = 10) + 
  theme(axis.text.x = element_text(size = 10))


##alaska## 
alaska_pct <- numbersense_cleaned2 %>%
  filter(airlines == "alaska") %>%
  pivot_longer(
    cols = -c(airlines, airline_timeliness),
    names_to = "airport",
    values_to = "value"
  )%>% 
  mutate(pct_total = value / sum(value), .by = airport)%>% 
  select(airlines, airline_timeliness, airport, value, pct_total)

#create table
alaska_table <- alaska_pct %>%
  select(airline_timeliness, airport, pct_total) %>%
  pivot_wider(names_from = c(airline_timeliness), values_from = pct_total)%>% 
  mutate(across(-airport, ~ percent(.x, accuracy = )))  #recommended to get percents 

kable(alaska_table, 
      col.names = c("Airport", "On Time", "Delayed"), 
      caption = "Percent of Alaska airlines flights on time vs delayed, by airport city")  # display in quarto

Percent of Alaska airlines flights on time vs delayed, by airport city
Airport	On Time	Delayed
los_angeles	88.91%	11.09%
phoenix	94.85%	5.15%
san_diego	91.38%	8.62%
san_francisco	83.14%	16.86%
seattle	85.79%	14.21%
total_row	86.73%	13.27%

Code

#create stacked bar chart 
alaska_plot <- alaska_pct |>
  ggplot(aes(x = airport, y = pct_total, fill = airline_timeliness)) +
   geom_col() +
    geom_text(
    aes(label = percent(pct_total, accuracy = 0.1)),
    position = position_stack(vjust = 0.5),   # centered in each segment
    color = "white",
    size = 3,
    fontface = "bold"
  ) +
  scale_y_continuous(labels = percent) +
  scale_fill_manual(values = c("delayed" = "maroon", "on time" = "steelblue1")) +
  labs(
    title = "Alaska airlines",
    x = "", 
    y = "", 
    fill = "legend"
  ) +
  theme_minimal(base_size = 10) + 
  theme(axis.text.x = element_text(size = 10))

#combining two visuals 
(amwest_plot + alaska_plot) +
  plot_layout(guides = "collect", widths = c(1, 1)) +
  plot_annotation(title = "Percent of two airlines arrival timeliness, by airport city")

Share of flights by airline and timeliness

Summary: Overall we see that the arrival timeliness followed a similar pattern by Am West and Alaska airlines as well as by city. For instance, for both airlines, Phoenix airport had the highest on time arrivals (92%,95% respectively) and San Franciso had the lowest on time arrivals (71%, 86% respectively).

However it’s noticeable that Alaska airlines has a higher on time percentage across all cities compared to Am West airlines, even though the total on time percent of Alaska airlines (87%) is lower than Am West airlines (89%).

Analysis 3 Describe discrepancy between comparing two airlines’ flight performances city-by-city and overall

Code

###number summaries city by city 
#look at timeliness number summary 
amwest_subset <- amwest_pct%>% 
  filter(airline_timeliness == "on time", airport != "total_row")

amwest_subset |>
  summarise(
    amwest_airports = n(),
    mean_on_time_rate = mean(pct_total),
    median_on_time_rate = median(pct_total),
    min_on_time_rate = min(pct_total),
    max_on_time_rate = max(pct_total)
  )

Code

alaska_subset <- alaska_pct%>% 
  filter(airline_timeliness == "on time", airport != "total_row")

alaska_subset |>
  summarise(
    alaska_airports = n(),
    mean_on_time_rate = mean(pct_total),
    median_on_time_rate = median(pct_total),
    min_on_time_rate = min(pct_total),
    max_on_time_rate = max(pct_total)
  )

Code

numbersense_cleaned2

Code

#Am West airlines 

#into phoenix (regardless of timeliness)
4840 +415

[1] 5255

Code

#total (regardless of timeliness) 
6138 +787

[1] 6925

Code

#percent of all flights into phoenix 
5255 / 6925

[1] 0.7588448

Code

#Alaska airlines 
#into seattle (regardless of timeliness)
1841 + 305

[1] 2146

Code

#total (regardless of timeliness) 
3274 + 501

[1] 3775

Code

#percent of all flights into seattle  
2146 / 3775

[1] 0.5684768

Summary: When we look city-by-city we see that Alaska airlines had the higher average of on time arrivals (89%) compared to am west airlines (80.5%). However, looking overall, the opposite is true.

This is because of the different count data we are using. When we look at the initial dataset, we see that the 79% of all am west flights that were on time came into phoenix airport, and 76% were their total flights regardless of timeliness. We saw that phoenix tended to have a better on time arrival across both airlines.

In comparison, Alaska airline flights mostly flew into Seattle (57%) which had a more difficult on time arrivals across both flights.

Conclusion

It was interesting to parse out the dataset in different ways. I struggled more than I thought I would on extracting the correct percents! I would like to play more with the formatting, especially graphs and tables in quartro.