library(tidyverse)   
## Warning: package 'tidyverse' was built under R version 4.3.2
## Warning: package 'ggplot2' was built under R version 4.3.2
## Warning: package 'tibble' was built under R version 4.3.2
## Warning: package 'tidyr' was built under R version 4.3.2
## Warning: package 'readr' was built under R version 4.3.2
## Warning: package 'purrr' was built under R version 4.3.2
## Warning: package 'dplyr' was built under R version 4.3.2
## Warning: package 'stringr' was built under R version 4.3.2
## Warning: package 'forcats' was built under R version 4.3.2
## Warning: package 'lubridate' was built under R version 4.3.2
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.4
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.4.4     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.0
## ✔ purrr     1.0.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
library(readr)
adc <- read_csv("ADC_2022_ed.csv") #loading data
## Rows: 20345 Columns: 18
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (2): carrier, airport
## dbl (16): month, arrived_flights, total_delayed, air_carrier_delay, weather_...
## 
## ℹ 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.
adc_names <- read_csv("adc_names.csv") #Names for Airlines and Airports 
## Rows: 17 Columns: 2
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (2): carrier, carrier_name
## 
## ℹ 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.

Week 3 Data Dive

This weeks Data Dive will focus on creating 3 groups: Month, Carrier, and Airport.

Month Group By

For the first group we will focus on the month. In this deep dive we will focus on seeing what month has the largest weather delays. We begin by looking at the mean weather delay for all months.

adc |>    
  group_by(month) |>   
  summarise(mean_wath_delay = mean(weather_delay, na.rm = TRUE)) |> 
  select(month, mean_wath_delay)
## # A tibble: 12 × 2
##    month mean_wath_delay
##    <dbl>           <dbl>
##  1     1            2.24
##  2     2            2.05
##  3     3            2.03
##  4     4            1.96
##  5     5            2.59
##  6     6            2.83
##  7     7            3.58
##  8     8            3.74
##  9     9            1.90
## 10    10            1.19
## 11    11            1.82
## 12    12            3.82
adc |>
  group_by(month) |> 
  summarize(mean_weath_delay = mean(weather_delay, na.rm = TRUE)) |> 
  ggplot(mapping = aes(month, mean_weath_delay)) +
  geom_col() + 
  scale_x_discrete(limits = month.name) +
  labs(title = "Weather Delay Mean per Month"  , x = "Month", y = "Weather Delay Mean") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

We see that there is an increase in weather delays in the months of May - August, dips down through September - November, and picks back up for the month of December. However, we must also pay attention to the amount of total delays that have occurred in those months.

adc |>
  group_by(month) |> 
  mutate(weath_delay_to_tot_delay = weather_delay / total_delayed) |>
  ggplot(mapping = aes(month, weath_delay_to_tot_delay)) +
  geom_col() + 
  scale_x_discrete(limits = month.name) +
  labs(title = "Weather Delay / Total Delayed per Month"  , x = "Month", y = "Weighted Weather Delay") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))
## Warning: Removed 658 rows containing missing values (`position_stack()`).

We can now clearly see that December still has the most amount of Weather Delays per Total Delays, followed closely behind by August.

We can look at the intervals in chunks of 0.10, of the weighted weather delay.

adc <- adc |>
  group_by(month) |> 
  mutate(weath_delay_to_tot_delay = weather_delay / total_delayed) |> 
  mutate(weath_ratio_bin = cut_interval(weath_delay_to_tot_delay, n = 10, labels = c("10%", "20%", "30%","40%","50%","60%", "70%", "80%", "90%", "100%"))) 
  
ungroup(adc) 
## # A tibble: 20,345 × 20
##    month carrier airport arrived_flights total_delayed air_carrier_delay
##    <dbl> <chr>   <chr>             <dbl>         <dbl>             <dbl>
##  1    12 9E      ABE                  27             7              4.37
##  2    12 9E      ABY                  69             3              0.61
##  3    12 9E      AEX                  62            14              5.28
##  4    12 9E      AGS                  47            10              5.12
##  5    12 9E      ALB                 131            34             12.1 
##  6    12 9E      ATL                1691           257             61.5 
##  7    12 9E      ATW                  93            19              7.53
##  8    12 9E      AUS                  26             1              0   
##  9    12 9E      AVL                  53             6              3.61
## 10    12 9E      AZO                  62            10              3.55
## # ℹ 20,335 more rows
## # ℹ 14 more variables: weather_delay <dbl>,
## #   national_aviation_system_delay <dbl>, security_delay <dbl>,
## #   late_aircraft_delay <dbl>, cancelled <dbl>, diverted <dbl>,
## #   total_delayed_minutes <dbl>, air_carrier_delay_minutes <dbl>,
## #   weather_delay_minutes <dbl>, national_aviation_system_delay_minutes <dbl>,
## #   security_delay_minutes <dbl>, `late_aircraft_delay _minutes` <dbl>, …

We will now see some random sampling of the data and viewing the result of which bin was chosen when sampled.

samp_w <- adc |>         # Random Sample of Weighted Weather Delay to Total Delayed
  sample_n(1) |>
  select(month, weath_delay_to_tot_delay, weath_ratio_bin)


samp_w
## # A tibble: 12 × 3
## # Groups:   month [12]
##    month weath_delay_to_tot_delay weath_ratio_bin
##    <dbl>                    <dbl> <fct>          
##  1     1                   0      10%            
##  2     2                   0      10%            
##  3     3                   0      10%            
##  4     4                   0      10%            
##  5     5                   0      10%            
##  6     6                   0.0909 10%            
##  7     7                   0.0191 10%            
##  8     8                   0.0233 10%            
##  9     9                   0      10%            
## 10    10                   0.0258 10%            
## 11    11                   0      10%            
## 12    12                   0      10%

In each month, the 10% bin was the most probable to show up, indicating most of the weather delays in all months are roughly only 10% of all delays in each month.

Carrier Group By

Next we investigate Carriers and the delays caused by carriers. We can look at carrier total delays and the total minutes the carriers caused.

We will start by investigating which airline has the largest amount of delays. In this case the airline with the most amount of delays is South West Airlines. Our next three groupings will all encompass WN as the main airline we will investigate.

car_delay_total <- adc |> 
  group_by(carrier) |> 
  summarise(car_tot_del = (sum(total_delayed, na.rm = TRUE))) |> 
  select(carrier, car_tot_del) |> 
  arrange(desc(car_tot_del))

print(car_delay_total)
## # A tibble: 17 × 2
##    carrier car_tot_del
##    <chr>         <dbl>
##  1 WN           305255
##  2 AA           190872
##  3 DL           140056
##  4 OO           119580
##  5 UA           118018
##  6 B6            85373
##  7 YX            58949
##  8 NK            55480
##  9 F9            47981
## 10 AS            45003
## 11 MQ            41891
## 12 OH            40071
## 13 G4            38373
## 14 9E            36473
## 15 YV            21795
## 16 HA            17131
## 17 QX            14497
adc |>
  group_by(carrier) |>
  ggplot() +
  geom_col(mapping = aes(x = carrier, y= air_carrier_delay)) +
  labs(title = "Total Carrier Delay's"  , x = "Carrier", y = "Total Delays")
## Warning: Removed 7 rows containing missing values (`position_stack()`).

adc |>
  group_by(carrier) |>
  ggplot() +
  geom_col(mapping = aes(carrier, air_carrier_delay_minutes)) +
  labs(title = "Total Carrier Delay Minutes"  , x = "Carrier", y = "Total Minutes Delays")
## Warning: Removed 7 rows containing missing values (`position_stack()`).

We see that WN (South West Airlines) is high on both graphs, but when accounting for total minutes delayed by airlines, AA, DL, and OO (American Airlines, Delta, SkyWest Airlines) all had large amounts as well.

###############   Creating a column for each carriers total minute delays  ##################

adc <- adc |> 
  group_by(carrier) |> 
  mutate(car_tot_del = (sum(total_delayed, na.rm = TRUE))) |> 
  mutate(carr_to_tot_del_min =total_delayed_minutes / car_tot_del) |> 
  ungroup()
adc 
## # A tibble: 20,345 × 22
##    month carrier airport arrived_flights total_delayed air_carrier_delay
##    <dbl> <chr>   <chr>             <dbl>         <dbl>             <dbl>
##  1    12 9E      ABE                  27             7              4.37
##  2    12 9E      ABY                  69             3              0.61
##  3    12 9E      AEX                  62            14              5.28
##  4    12 9E      AGS                  47            10              5.12
##  5    12 9E      ALB                 131            34             12.1 
##  6    12 9E      ATL                1691           257             61.5 
##  7    12 9E      ATW                  93            19              7.53
##  8    12 9E      AUS                  26             1              0   
##  9    12 9E      AVL                  53             6              3.61
## 10    12 9E      AZO                  62            10              3.55
## # ℹ 20,335 more rows
## # ℹ 16 more variables: weather_delay <dbl>,
## #   national_aviation_system_delay <dbl>, security_delay <dbl>,
## #   late_aircraft_delay <dbl>, cancelled <dbl>, diverted <dbl>,
## #   total_delayed_minutes <dbl>, air_carrier_delay_minutes <dbl>,
## #   weather_delay_minutes <dbl>, national_aviation_system_delay_minutes <dbl>,
## #   security_delay_minutes <dbl>, `late_aircraft_delay _minutes` <dbl>, …
samp_c <- adc |>
  group_by(carrier) |>
  select(carrier, air_carrier_delay_minutes, carr_to_tot_del_min) |> 
  sample_n(1) 

samp_c
## # A tibble: 17 × 3
## # Groups:   carrier [17]
##    carrier air_carrier_delay_minutes carr_to_tot_del_min
##    <chr>                       <dbl>               <dbl>
##  1 9E                           2174             0.218  
##  2 AA                            539             0.00485
##  3 AS                            432             0.0189 
##  4 B6                           4008             0.0987 
##  5 DL                            589             0.00427
##  6 F9                            266             0.0337 
##  7 G4                            208             0.0204 
##  8 HA                          15630             1.57   
##  9 MQ                            210             0.0130 
## 10 NK                            934             0.0737 
## 11 OH                            231             0.00844
## 12 OO                           1794             0.0209 
## 13 QX                           2086             0.310  
## 14 UA                            130             0.00478
## 15 WN                           2205             0.0132 
## 16 YV                           1051             0.0858 
## 17 YX                           7236             0.515

Airport Group By

We can now group by Airport, we will create a group by of airport, and then create additional columns to view total flights, total delayed flights, and the avergae of th delayed minutes per airport. We can then plot this information while only looking at the 5% as we only care about the “worse” airports.

(Note : We can label the worse airport as the airports with the most amount of averaged delayed minutes)

adc |> 
  group_by(airport) |> 
  summarize(
    total_flights = sum(arrived_flights), 
    total_delayed_flights = sum(total_delayed), 
    average_delay_minutes = mean(total_delayed_minutes), 
    ) |> 
  filter(average_delay_minutes >= quantile(average_delay_minutes, 0.95, na.rm = TRUE)) |>
  select(airport, total_flights, total_delayed_flights, average_delay_minutes) |>
  ggplot() +
  geom_col(mapping = aes(x = airport, y= average_delay_minutes)) +
  labs(title = "Top 5% of Average Delay Minutes by Airport"  , x = "Airport", y = "Average Delay Minutes") + 
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

We can create a new column of the probability an airport will be chosen due to delays. The new column probability_airport can be plotted using geom_point to show the Airports on the X axis and the probability on the Y axis.

adc |> 
  group_by(airport) |> 
  summarize(
    total_flights = sum(arrived_flights), 
    total_delayed_flights = sum(total_delayed), 
    average_delay_minutes = mean(total_delayed_minutes), 
    ) |> 
  mutate(probability_airport = average_delay_minutes/ sum(average_delay_minutes, na.rm = TRUE)) |>
   filter(probability_airport >= quantile(probability_airport, 0.95, na.rm = TRUE)) |> 
  ggplot() +
  geom_point(mapping = aes(x = airport, y = probability_airport, color = probability_airport), size = 3) +
  labs(title = "Probability of Delay by the Top 5% Worse Airports"  , x = "Airport", y = "Probability of a Delay") + 
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  scale_color_gradient(low = "lightblue", high = "darkblue")

DFW has the highest probability of a delayed flight of the top 5% worse airports. However this must also be viewed in the amount of flights those airports have compared to others. We can see in the previous code DFW has one of the highest total flights within the time frame as well as the highest probability of a delayed flight.

On the other hand, we see ATL has a lower portability with a higher number of total flights.

adc |> 
  group_by(airport) |> 
  summarize(
    total_flights = sum(arrived_flights), 
    total_delayed_flights = sum(total_delayed), 
    average_delay_minutes = mean(total_delayed_minutes), 
    ) |> 
  mutate(probability_airport = average_delay_minutes/ sum(average_delay_minutes, na.rm = TRUE)) |>
  filter(probability_airport >= quantile(probability_airport, 0.95, na.rm = TRUE)) |>
  arrange(desc(total_flights)) |>
  select(airport, total_flights, total_delayed_flights, average_delay_minutes)
## # A tibble: 19 × 4
##    airport total_flights total_delayed_flights average_delay_minutes
##    <chr>           <dbl>                 <dbl>                 <dbl>
##  1 ATL            317910                 50350                21488.
##  2 DFW            276711                 53924                33173.
##  3 DEN            271459                 54028                29503.
##  4 ORD            260587                 46027                22952.
##  5 CLT            191795                 33202                17137.
##  6 LAX            191163                 35400                17976.
##  7 LAS            175519                 43040                18119.
##  8 LGA            169414                 38226                21589.
##  9 PHX            165218                 32139                13980.
## 10 MCO            146784                 40360                25757.
## 11 JFK            136554                 33132                27039.
## 12 EWR            129763                 34142                19050.
## 13 MIA            106842                 25278                14423.
## 14 FLL             84235                 21812                13952.
## 15 MDW             76882                 17412                15881.
## 16 DAL             66378                 15267                19723.
## 17 SFB              9166                  3184                19000.
## 18 PIE              7829                  2652                15861.
## 19 PGD              6152                  2672                16986.

Combination of Airport and Carrier

Now we will look at which Airport and Carrier Combination is most likely to lead to a delay.

     #   First Version 

adc |> 
  group_by(airport, carrier) |> 
  summarize(
    total_flights = sum(arrived_flights), 
    total_delayed_flights = sum(total_delayed), 
    average_delay_minutes = mean(total_delayed_minutes), 
    ) |> 
  mutate(probability_of_delayed_flight = total_delayed_flights/ total_flights) |>
  filter(probability_of_delayed_flight >= quantile(probability_of_delayed_flight, 0.95, na.rm = TRUE)) |>
  arrange(desc(probability_of_delayed_flight)) |>
  select(airport, total_flights, total_delayed_flights, probability_of_delayed_flight)
## `summarise()` has grouped output by 'airport'. You can override using the
## `.groups` argument.
## # A tibble: 374 × 4
## # Groups:   airport [373]
##    airport total_flights total_delayed_flights probability_of_delayed_flight
##    <chr>           <dbl>                 <dbl>                         <dbl>
##  1 ATY                 2                     2                         1    
##  2 DFW                 3                     3                         1    
##  3 GSO                 1                     1                         1    
##  4 IAD                 2                     2                         1    
##  5 IFP                 1                     1                         1    
##  6 MDT                 1                     1                         1    
##  7 COU                 3                     2                         0.667
##  8 CSG                 3                     2                         0.667
##  9 SRQ               114                    65                         0.570
## 10 AGS                 9                     5                         0.556
## # ℹ 364 more rows
      # Result of selecting carrier is the same as before as we grouped by both carrier and airport and didnt ungroup, however now we can see the the carrier information which is import

adc |>    
  group_by(airport, carrier) |> 
  summarize(
    total_flights = sum(arrived_flights), 
    total_delayed_flights = sum(total_delayed), 
    average_delay_minutes = mean(total_delayed_minutes), 
    ) |> 
  mutate(probability_of_delayed_flight = total_delayed_flights/ total_flights) |>
  filter(probability_of_delayed_flight >= quantile(probability_of_delayed_flight, 0.95, na.rm = TRUE)) |>
  arrange(desc(probability_of_delayed_flight)) |>
  select(airport, carrier, total_flights, total_delayed_flights, probability_of_delayed_flight) |> 
  print() |> #Print all rows to show the whole table 
  filter(probability_of_delayed_flight > 0.55) |>   #only graph the first 10 rows 
  ggplot() +   #Shows the probability of the first 10 rows summed
  geom_col(mapping = aes(x = carrier, y= probability_of_delayed_flight)) +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))
## `summarise()` has grouped output by 'airport'. You can override using the
## `.groups` argument.
## # A tibble: 374 × 5
## # Groups:   airport [373]
##    airport carrier total_flights total_delayed_flights probability_of_delayed_…¹
##    <chr>   <chr>           <dbl>                 <dbl>                     <dbl>
##  1 ATY     OO                  2                     2                     1    
##  2 DFW     9E                  3                     3                     1    
##  3 GSO     OO                  1                     1                     1    
##  4 IAD     F9                  2                     2                     1    
##  5 IFP     G4                  1                     1                     1    
##  6 MDT     YV                  1                     1                     1    
##  7 COU     YV                  3                     2                     0.667
##  8 CSG     YV                  3                     2                     0.667
##  9 SRQ     OO                114                    65                     0.570
## 10 AGS     AA                  9                     5                     0.556
## # ℹ 364 more rows
## # ℹ abbreviated name: ¹​probability_of_delayed_flight

adc |>    
  group_by(airport, carrier) |> 
  summarize(
    total_flights = sum(arrived_flights), 
    total_delayed_flights = sum(total_delayed), 
    average_delay_minutes = mean(total_delayed_minutes), 
    ) |> 
  mutate(probability_of_delayed_flight = total_delayed_flights/ total_flights) |>
  filter(probability_of_delayed_flight >= quantile(probability_of_delayed_flight, 0.95, na.rm = TRUE)) |>
  arrange(desc(probability_of_delayed_flight)) |>
  select(airport, carrier, total_flights, total_delayed_flights, probability_of_delayed_flight) |>  #Print all rows to show the whole table 
  filter(probability_of_delayed_flight > 0.55) |>
  ggplot() +
  geom_point(mapping = aes(x = airport, y = carrier, color = probability_of_delayed_flight), size = 3) +
  labs(title = "First 10 Rows of Airport & Carrier Grouping"  , x = "Airport", y = "Carrier") + 
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  scale_color_gradient(low = "lightblue", high = "darkblue")
## `summarise()` has grouped output by 'airport'. You can override using the
## `.groups` argument.

After grouping by carrier and aiport we see that the first six combination of airport and carrier have a ration of flights to delay of 1. Meaning every flight, within that combination, was delayed. However, flight totals are relatively small compared to other airport-carrier combinations.

We do see that Skywest Airline (OO), has is seen 3x times in 3 different airports within the first 10 rows. Indicating that Skywest Airline has a worse track record than other airlines, no matter the airport. Mesa Airlines also is seen 3x, however the total flight number is relatively small.

write.csv(adc, "adc_week_3.csv", row.names = FALSE)
adc_names
## # A tibble: 17 × 2
##    carrier carrier_name          
##    <chr>   <chr>                 
##  1 9E      Endeavor Air Inc.     
##  2 AA      American Airlines Inc.
##  3 AS      Alaska Airlines Inc.  
##  4 B6      JetBlue Airways       
##  5 DL      Delta Air Lines Inc.  
##  6 F9      Frontier Airlines Inc.
##  7 G4      Allegiant Air         
##  8 HA      Hawaiian Airlines Inc.
##  9 MQ      Envoy Air             
## 10 NK      Spirit Air Lines      
## 11 OH      PSA Airlines Inc.     
## 12 OO      SkyWest Airlines Inc. 
## 13 QX      Horizon Air           
## 14 UA      United Air Lines Inc. 
## 15 WN      Southwest Airlines Co.
## 16 YV      Mesa Airlines Inc.    
## 17 YX      Republic Airline