TidyData5A

Approach

I will input the data and then clean the original data removing NA cells. Transform the data into a format that is easier to analyze, and finally compare the two airlines’ performance overall and across five cities.

Code Base

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

flights <- data.frame(airline=c("Alaska", NA, "AM West", NA),
                      status=c("on time", "delayed", "on time", "delayed"),
               los_angeles=c(497, 62, 694, 117),
               phoenix=c(221, 12, 4840, 415),
               san_diego=c(212, 20, 383, 65),
               san_francisco=c(503,102, 320, 129),
               seattle=c(1841,305, 201, 61)
               )

flights

  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

Next remove empty cells or to better say fill in the airline.

flights <- flights %>%
  fill(airline)

flights

  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

Pivot_longer()

Convert data from wide to long format using pivot_longer()

flights_long <- flights %>%
  pivot_longer(
    cols=c(los_angeles, phoenix, san_diego, san_francisco, seattle),
    names_to = "City",
    values_to = "Count"
  )

flights_long

# A tibble: 20 × 4
   airline status  City          Count
   <chr>   <chr>   <chr>         <dbl>
 1 Alaska  on time los_angeles     497
 2 Alaska  on time phoenix         221
 3 Alaska  on time san_diego       212
 4 Alaska  on time san_francisco   503
 5 Alaska  on time seattle        1841
 6 Alaska  delayed los_angeles      62
 7 Alaska  delayed phoenix          12
 8 Alaska  delayed san_diego        20
 9 Alaska  delayed san_francisco   102
10 Alaska  delayed seattle         305
11 AM West on time los_angeles     694
12 AM West on time phoenix        4840
13 AM West on time san_diego       383
14 AM West on time san_francisco   320
15 AM West on time seattle         201
16 AM West delayed los_angeles     117
17 AM West delayed phoenix         415
18 AM West delayed san_diego        65
19 AM West delayed san_francisco   129
20 AM West delayed seattle          61

Comparing on-time flights

overall <- flights_long %>%
  group_by(airline) %>%
  summarise(
    on_time_percentage = sum(Count[status == "on time"], na.rm = TRUE) /
      sum(Count, na.rm = TRUE) * 100
  )

overall

# A tibble: 2 × 2
  airline on_time_percentage
  <chr>                <dbl>
1 AM West               89.1
2 Alaska                86.7

From the data, we see percentages of on-time flights by airline. AM West is on-time 89% overall and Alaska is on-time 86% overall.

Comparison across all five cities

five_city_comparison <- flights_long %>%
  group_by(airline, City) %>%
  summarise(
    on_time_percentage = sum(Count[status == "on time"], na.rm = TRUE) /
      sum(Count, na.rm = TRUE) * 100
  ) %>%
  ungroup()

`summarise()` has regrouped the output.
ℹ Summaries were computed grouped by airline and City.
ℹ Output is grouped by airline.
ℹ Use `summarise(.groups = "drop_last")` to silence this message.
ℹ Use `summarise(.by = c(airline, City))` for per-operation grouping
  (`?dplyr::dplyr_by`) instead.

five_city_comparison

# A tibble: 10 × 3
   airline City          on_time_percentage
   <chr>   <chr>                      <dbl>
 1 AM West los_angeles                 85.6
 2 AM West phoenix                     92.1
 3 AM West san_diego                   85.5
 4 AM West san_francisco               71.3
 5 AM West seattle                     76.7
 6 Alaska  los_angeles                 88.9
 7 Alaska  phoenix                     94.8
 8 Alaska  san_diego                   91.4
 9 Alaska  san_francisco               83.1
10 Alaska  seattle                     85.8

The table compares the percentage of flights that arrived on time for AM West and Alaska across the five cities. Alaska had a higher on-time percentage than AM West in all five cities. Both airlines had their highest on-time percentages in Phoenix, with Alaska at around 94.85% and AM West at 92.10%. San Francisco had the lowest on-time percentages for both airlines. Overall, the results show that Alaska had better on-time flights than AM West in each of the five cities.