Week 2 Data Dive

In this weeks Data Dive, we will take an introductory look into the data. This will entail creating primitive look into the numerical meaning of some of the data. After looking at the initial summary of data, we will create three novel questions to guide further investigation, and answering at least one. Lastly, we will create a visualization to better showcase and understand the data.

Import and Viewing the Data

We begin by importing our libraries and data.

library(tidyverse)   #loading libraries
## 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")
## 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.

Data Explained

We will know take an initial look at the data. This data contains the flight information of all U.S. airports from October 2021 through October 2023. These dates are chosen as the information is provided by the FAA, the FAA has only provided this information through October 2023. The start date of October 2021 was chosen as this is when the impacts of travel from Covid was minimized.

Before we begin we must understand what a delayed flight is:

A flight is considered delayed when it arrived 15 or more minutes than the schedule. Delayed minutes are calculated for delayed flights only. When multiple causes are assigned to one delayed flight, each cause is prorated based on delayed minutes it is responsible for.


SOURCE: Bureau of Transportation Statistics, Airline Service Quality Performance 234

Numeric Summary

When viewing the data the first questions that pops into the mind is if any airlines or airports have a greater number of delayed flight. We can look a this through filter through the airports/airlines and viewing the amount of delays.

We begin by looking at the different Airports and Airline in this data.

adc |> 
  select(airport) |>     #We look at all the distinct Airports 
  distinct() |>
  count()
## # A tibble: 1 × 1
##       n
##   <int>
## 1   373
adc |> 
  select(carrier) |>      #We look at all the distinct airlines 
  distinct() |>
  count()
## # A tibble: 1 × 1
##       n
##   <int>
## 1    17

There are 373 distinct airports and 17 distinct airlines.

We can now look at a basic summary of the data.

adc |> 
  select(where(is.numeric)) |> 
  summary()
##      month        arrived_flights   total_delayed     air_carrier_delay 
##  Min.   : 1.000   Min.   :    1.0   Min.   :   0.00   Min.   :   0.000  
##  1st Qu.: 4.000   1st Qu.:   43.0   1st Qu.:   7.00   1st Qu.:   2.683  
##  Median : 6.000   Median :   90.0   Median :  17.00   Median :   7.370  
##  Mean   : 6.487   Mean   :  330.9   Mean   :  67.73   Mean   :  25.487  
##  3rd Qu.: 9.000   3rd Qu.:  217.0   3rd Qu.:  47.00   3rd Qu.:  20.270  
##  Max.   :12.000   Max.   :18388.0   Max.   :3479.00   Max.   :1096.180  
##                   NA's   :7         NA's   :16        NA's   :7         
##  weather_delay     national_aviation_system_delay security_delay   
##  Min.   :  0.000   Min.   :   0.00                Min.   : 0.0000  
##  1st Qu.:  0.000   1st Qu.:   0.75                1st Qu.: 0.0000  
##  Median :  0.520   Median :   3.10                Median : 0.0000  
##  Mean   :  2.478   Mean   :  16.24                Mean   : 0.2072  
##  3rd Qu.:  2.000   3rd Qu.:   9.56                3rd Qu.: 0.0000  
##  Max.   :222.900   Max.   :1391.74                Max.   :58.6900  
##  NA's   :7         NA's   :7                      NA's   :7        
##  late_aircraft_delay   cancelled           diverted       
##  Min.   :   0.00     Min.   :   0.000   Min.   :  0.0000  
##  1st Qu.:   1.30     1st Qu.:   0.000   1st Qu.:  0.0000  
##  Median :   4.65     Median :   1.000   Median :  0.0000  
##  Mean   :  23.28     Mean   :   8.914   Mean   :  0.7782  
##  3rd Qu.:  14.41     3rd Qu.:   5.000   3rd Qu.:  1.0000  
##  Max.   :1537.66     Max.   :1565.000   Max.   :153.0000  
##  NA's   :7           NA's   :7          NA's   :7         
##  total_delayed_minutes air_carrier_delay_minutes weather_delay_minutes
##  Min.   :     0        Min.   :     0            Min.   :    0.0      
##  1st Qu.:   380        1st Qu.:   134            1st Qu.:    0.0      
##  Median :  1084        Median :   448            Median :   22.0      
##  Mean   :  4555        Mean   :  1812            Mean   :  252.9      
##  3rd Qu.:  3053        3rd Qu.:  1348            3rd Qu.:  164.8      
##  Max.   :323449        Max.   :119425            Max.   :24324.0      
##  NA's   :7             NA's   :7                 NA's   :7            
##  national_aviation_system_delay_minutes security_delay_minutes
##  Min.   :    0.0                        Min.   :   0.000      
##  1st Qu.:   23.0                        1st Qu.:   0.000      
##  Median :  122.0                        Median :   0.000      
##  Mean   :  765.4                        Mean   :   9.648      
##  3rd Qu.:  411.0                        3rd Qu.:   0.000      
##  Max.   :84155.0                        Max.   :3551.000      
##  NA's   :7                              NA's   :7             
##  late_aircraft_delay _minutes
##  Min.   :     0              
##  1st Qu.:    69              
##  Median :   311              
##  Mean   :  1715              
##  3rd Qu.:  1089              
##  Max.   :158653              
##  NA's   :7

What we see is the quantile summary for the numeric data.

Our Business Questions

These questions will guide our initial approach and allow us to navigate into even deeper

  • Is there any airlines/airports that have an egregious amount of delays?

  • Which month is the worst for average delays compared to total flights.

  • What types of delays are most common through the different airlines/airports?

All these questions are basic, but can guide airlines and airports in improving flight scheduling by examining the delay trends.

Solving One Business Question

We will focus on the first business questions. We want to know whether there is a specific airline or airport with a greater amount of delayed flights. We can view this by averaging the total delayed flights and group them by airports and airline. We compare the mean of delayed flights with the total flights to acquire a better picture.

adc |> 
  group_by(carrier, airport)|> 
  summarise(mean_flights = mean(arrived_flights),
            mean_del_flights = mean(total_delayed),
            total_flights = sum(arrived_flights),
            total_del_flights = sum(total_delayed)) |> 
  arrange(desc(mean_del_flights)) 
## `summarise()` has grouped output by 'carrier'. You can override using the
## `.groups` argument.
## # A tibble: 1,945 × 6
## # Groups:   carrier [17]
##    carrier airport mean_flights mean_del_flights total_flights total_del_flights
##    <chr>   <chr>          <dbl>            <dbl>         <dbl>             <dbl>
##  1 DL      ATL           17492.            2312         209902             27744
##  2 AA      DFW           11911.            2305         142930             27660
##  3 AA      CLT            8794.            1601.        105531             19209
##  4 WN      DEN            6928.            1490.         83139             17886
##  5 WN      LAS            6129.            1474.         73549             17686
##  6 WN      MDW            5918.            1343          71013             16116
##  7 UA      EWR            5132.            1335.         61582             16022
##  8 B6      JFK            3933.            1285.         47191             15421
##  9 WN      DAL            5325.            1240.         63902             14886
## 10 UA      DEN            6758.            1170.         81090             14042
## # ℹ 1,935 more rows
adc |> 
  group_by(airport) |> 
  summarise(mean_flights = mean(arrived_flights),
            mean_del_flights = mean(total_delayed),
            total_flights = sum(arrived_flights),
            total_del_flights = sum(total_delayed)) |> 
  arrange(desc(mean_del_flights)) 
## # A tibble: 373 × 5
##    airport mean_flights mean_del_flights total_flights total_del_flights
##    <chr>          <dbl>            <dbl>         <dbl>             <dbl>
##  1 DEN            2225.             443.        271459             54028
##  2 DFW            1962.             382.        276711             53924
##  3 MCO            1359.             374.        146784             40360
##  4 DAL            1619.             372.         66378             15267
##  5 JFK            1453.             352.        136554             33132
##  6 ATL            1892.             300.        317910             50350
##  7 LAS            1219.             299.        175519             43040
##  8 MDW            1303.             295.         76882             17412
##  9 LGA            1283.             290.        169414             38226
## 10 ORD            1619.             286.        260587             46027
## # ℹ 363 more rows
adc |> 
  group_by(carrier) |> 
  summarise(mean_flights = mean(arrived_flights),
            mean_del_flights = mean(total_delayed),
            total_flights = sum(arrived_flights),
            total_del_flights = sum(total_delayed)) |> 
  arrange(desc(mean_del_flights)) 
## # A tibble: 17 × 5
##    carrier mean_flights mean_del_flights total_flights total_del_flights
##    <chr>          <dbl>            <dbl>         <dbl>             <dbl>
##  1 WN            1018.             238.        1307149            305255
##  2 AA             645.             141.         874145            190872
##  3 B6             349.             109.         273058             85373
##  4 UA             479.              90.2        626473            118018
##  5 NK             343.              81.8        232400             55480
##  6 HA             283.              65.6         73865             17131
##  7 AS             234.              46.2        228384             45003
##  8 F9             142.              43.7        155482             47981
##  9 OH             193.              36.1        214450             40071
## 10 9E             195.              30.6        231941             36473
## 11 QX             157.              25.6         88791             14497
## 12 G4              77.5             25.4        116877             38373
## 13 DL              NA               NA              NA                NA
## 14 MQ              NA               NA              NA                NA
## 15 OO              NA               NA              NA                NA
## 16 YV              NA               NA              NA                NA
## 17 YX              NA               NA              NA                NA

Looking at the numeric summaries, we see Denver International has the largest average amount of delayed flights, but also has one of the largest number of flights within this data.

Looking at the carriers, Southwest Airlines has the largest average delayed flights , and one of the largest amount of total flights.

If we wanted to further divulge into this business question we could try and understand the type of delays within DEN airport and Southwest Airlines.

Visual Summaries

We will now look at a visual summary of the delayed flights. We can visualize the amount of delayed flights by each airline.

adc|> 
  group_by(month, carrier, airport) |>
  ggplot() +
  geom_bar(mapping = aes(x = carrier, fill = total_delayed)) +
  labs(title = "Delayed Flights By Airline",
       x = "Airline", y = "Total") +
  scale_x_discrete(guide = guide_axis(angle = 90)) +
  theme_classic() 
## Warning: The following aesthetics were dropped during statistical transformation: fill
## ℹ This can happen when ggplot fails to infer the correct grouping structure in
##   the data.
## ℹ Did you forget to specify a `group` aesthetic or to convert a numerical
##   variable into a factor?

And now we can see the total amount of delayed minutes by month.

adc |>
  group_by(month, carrier, airport) |>
  ggplot() +
  geom_col(mapping = aes(month, total_delayed_minutes)) + 
  theme_light()
## Warning: Removed 7 rows containing missing values (`position_stack()`).