Reading the data

airdata <- read_csv("https://raw.githubusercontent.com/Raharijao/Spring2024_Data_607_CUNYSPS/main/Week5/DATA607_assignment5_file.csv")
## New names:
## Rows: 5 Columns: 7
## ── Column specification
## ──────────────────────────────────────────────────────── Delimiter: "," chr
## (2): ...1, ...2 dbl (5): Los Angeles, Phoenix, San Diego, San Francisco, Seatle
## ℹ Use `spec()` to retrieve the full column specification for this data. ℹ
## Specify the column types or set `show_col_types = FALSE` to quiet this message.
## • `` -> `...1`
## • `` -> `...2`

Removes Space row

airdata <- airdata %>%
  filter(!is.na(...2))

Turning data into long format

long <- pivot_longer(airdata,
                     cols = -c(...1, ...2),
                     names_to = "city",
                     values_to = "count")

Fill the blanks for airline name

long <- long %>%
  fill(...1, .direction = "down")

Rename columns

long <- rename(long,
               airline = ...1,
               status = ...2)

Analysis

#Which city does each airline have the highest on time ratio? what about lowest?

In term of on time arrival ratio, Phoenix seems to be the destination where both airlines performs best.And San Francisco seems to be the city with the most delays for both of them.

long_ratio <- long %>%
   group_by(airline, city) %>%
   summarise(
     on_time = count[status == "on time"],
     delayed = count[status == "delayed"],
     ratio = on_time/delayed
   ) %>%
  arrange(desc(ratio))
## `summarise()` has grouped output by 'airline'. You can override using the
## `.groups` argument.
#long_ratio 

#overall ratio
long_ratio_overall <- long_ratio %>%
  group_by(airline) %>%
  summarise(
    mean = mean(ratio)
  )
long_ratio
## # A tibble: 10 × 5
## # Groups:   airline [2]
##    airline city          on_time delayed ratio
##    <chr>   <chr>           <dbl>   <dbl> <dbl>
##  1 ALASKA  Phoenix           221      12 18.4 
##  2 AM WEST Phoenix          4840     415 11.7 
##  3 ALASKA  San Diego         212      20 10.6 
##  4 ALASKA  Los Angeles       497      62  8.02
##  5 ALASKA  Seatle           1841     305  6.04
##  6 AM WEST Los Angeles       694     117  5.93
##  7 AM WEST San Diego         383      65  5.89
##  8 ALASKA  San Francisco     503     102  4.93
##  9 AM WEST Seatle            201      61  3.30
## 10 AM WEST San Francisco     320     129  2.48

Overall, ALASKA airlines seems to have a better on time ratio than AM WEST.

long_ratio_overall
## # A tibble: 2 × 2
##   airline  mean
##   <chr>   <dbl>
## 1 ALASKA   9.60
## 2 AM WEST  5.85

Which city do the airlines have the largest difference of on time ratio?

It seems like the largest difference in on time ratio is Phoenix, there is a difference of 6 between the on time ratio of ALASK and AM WEST.

long_ratio_dif <- long_ratio %>%
  group_by(city) %>%
  summarise(
    dif = abs(ratio[airline == "ALASKA"] - ratio[airline == "AM WEST"])
  ) %>%
  arrange(desc(dif))
long_ratio_dif
## # A tibble: 5 × 2
##   city            dif
##   <chr>         <dbl>
## 1 Phoenix        6.75
## 2 San Diego      4.71
## 3 Seatle         2.74
## 4 San Francisco  2.45
## 5 Los Angeles    2.08