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.
This weeks Data Dive will focus on creating 3 groups: Month, Carrier, and Airport.
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.
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
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.
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