Assignment 8: Exploring Flights Data

Question 1: Dataset Dimensions

The flights dataset contains 336,776 observations and 19 variables.

glimpse(flights)
## Rows: 336,776
## Columns: 19
## $ year           <int> 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2…
## $ month          <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ day            <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ dep_time       <int> 517, 533, 542, 544, 554, 554, 555, 557, 557, 558, 558, …
## $ sched_dep_time <int> 515, 529, 540, 545, 600, 558, 600, 600, 600, 600, 600, …
## $ dep_delay      <dbl> 2, 4, 2, -1, -6, -4, -5, -3, -3, -2, -2, -2, -2, -2, -1…
## $ arr_time       <int> 830, 850, 923, 1004, 812, 740, 913, 709, 838, 753, 849,…
## $ sched_arr_time <int> 819, 830, 850, 1022, 837, 728, 854, 723, 846, 745, 851,…
## $ arr_delay      <dbl> 11, 20, 33, -18, -25, 12, 19, -14, -8, 8, -2, -3, 7, -1…
## $ carrier        <chr> "UA", "UA", "AA", "B6", "DL", "UA", "B6", "EV", "B6", "…
## $ flight         <int> 1545, 1714, 1141, 725, 461, 1696, 507, 5708, 79, 301, 4…
## $ tailnum        <chr> "N14228", "N24211", "N619AA", "N804JB", "N668DN", "N394…
## $ origin         <chr> "EWR", "LGA", "JFK", "JFK", "LGA", "EWR", "EWR", "LGA",…
## $ dest           <chr> "IAH", "IAH", "MIA", "BQN", "ATL", "ORD", "FLL", "IAD",…
## $ air_time       <dbl> 227, 227, 160, 183, 116, 150, 158, 53, 140, 138, 149, 1…
## $ distance       <dbl> 1400, 1416, 1089, 1576, 762, 719, 1065, 229, 944, 733, …
## $ hour           <dbl> 5, 5, 5, 5, 6, 5, 6, 6, 6, 6, 6, 6, 6, 6, 6, 5, 6, 6, 6…
## $ minute         <dbl> 15, 29, 40, 45, 0, 58, 0, 0, 0, 0, 0, 0, 0, 0, 0, 59, 0…
## $ time_hour      <dttm> 2013-01-01 05:00:00, 2013-01-01 05:00:00, 2013-01-01 0…
summary(flights)
##       year          month             day           dep_time    sched_dep_time
##  Min.   :2013   Min.   : 1.000   Min.   : 1.00   Min.   :   1   Min.   : 106  
##  1st Qu.:2013   1st Qu.: 4.000   1st Qu.: 8.00   1st Qu.: 907   1st Qu.: 906  
##  Median :2013   Median : 7.000   Median :16.00   Median :1401   Median :1359  
##  Mean   :2013   Mean   : 6.549   Mean   :15.71   Mean   :1349   Mean   :1344  
##  3rd Qu.:2013   3rd Qu.:10.000   3rd Qu.:23.00   3rd Qu.:1744   3rd Qu.:1729  
##  Max.   :2013   Max.   :12.000   Max.   :31.00   Max.   :2400   Max.   :2359  
##                                                  NA's   :8255                 
##    dep_delay          arr_time    sched_arr_time   arr_delay       
##  Min.   : -43.00   Min.   :   1   Min.   :   1   Min.   : -86.000  
##  1st Qu.:  -5.00   1st Qu.:1104   1st Qu.:1124   1st Qu.: -17.000  
##  Median :  -2.00   Median :1535   Median :1556   Median :  -5.000  
##  Mean   :  12.64   Mean   :1502   Mean   :1536   Mean   :   6.895  
##  3rd Qu.:  11.00   3rd Qu.:1940   3rd Qu.:1945   3rd Qu.:  14.000  
##  Max.   :1301.00   Max.   :2400   Max.   :2359   Max.   :1272.000  
##  NA's   :8255      NA's   :8713                  NA's   :9430      
##    carrier              flight       tailnum             origin         
##  Length:336776      Min.   :   1   Length:336776      Length:336776     
##  Class :character   1st Qu.: 553   Class :character   Class :character  
##  Mode  :character   Median :1496   Mode  :character   Mode  :character  
##                     Mean   :1972                                        
##                     3rd Qu.:3465                                        
##                     Max.   :8500                                        
##                                                                         
##      dest              air_time        distance         hour      
##  Length:336776      Min.   : 20.0   Min.   :  17   Min.   : 1.00  
##  Class :character   1st Qu.: 82.0   1st Qu.: 502   1st Qu.: 9.00  
##  Mode  :character   Median :129.0   Median : 872   Median :13.00  
##                     Mean   :150.7   Mean   :1040   Mean   :13.18  
##                     3rd Qu.:192.0   3rd Qu.:1389   3rd Qu.:17.00  
##                     Max.   :695.0   Max.   :4983   Max.   :23.00  
##                     NA's   :9430                                  
##      minute        time_hour                  
##  Min.   : 0.00   Min.   :2013-01-01 05:00:00  
##  1st Qu.: 8.00   1st Qu.:2013-04-04 13:00:00  
##  Median :29.00   Median :2013-07-03 10:00:00  
##  Mean   :26.23   Mean   :2013-07-03 05:22:54  
##  3rd Qu.:44.00   3rd Qu.:2013-10-01 07:00:00  
##  Max.   :59.00   Max.   :2013-12-31 23:00:00  
## 
dim(flights)
## [1] 336776     19
nrow(flights)
## [1] 336776
ncol(flights)
## [1] 19

Question 2: Largest Departure Delay

We can identify the flight with the largest departure delay by finding the maximum value or sorting the dataset in descending order.

# Option C
maxdep <- max(flights$dep_delay, na.rm = TRUE)

maxdep_id <- which(
  flights$dep_delay == maxdep
)

flights[maxdep_id, 10:12]
## # A tibble: 1 × 3
##   carrier flight tailnum
##   <chr>    <int> <chr>  
## 1 HA          51 N384HA
# Show complete flight information
flights[maxdep_id, ]
## # A tibble: 1 × 19
##    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
##   <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>
## 1  2013     1     9      641            900      1301     1242           1530
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## #   hour <dbl>, minute <dbl>, time_hour <dttm>
# Option D
sortf <- arrange(flights, desc(dep_delay))

select(
  sortf,
  carrier,
  flight,
  tailnum,
  everything()
) %>%
  head(10)
## # A tibble: 10 × 19
##    carrier flight tailnum  year month   day dep_time sched_dep_time dep_delay
##    <chr>    <int> <chr>   <int> <int> <int>    <int>          <int>     <dbl>
##  1 HA          51 N384HA   2013     1     9      641            900      1301
##  2 MQ        3535 N504MQ   2013     6    15     1432           1935      1137
##  3 MQ        3695 N517MQ   2013     1    10     1121           1635      1126
##  4 AA         177 N338AA   2013     9    20     1139           1845      1014
##  5 MQ        3075 N665MQ   2013     7    22      845           1600      1005
##  6 DL        2391 N959DL   2013     4    10     1100           1900       960
##  7 DL        2119 N927DA   2013     3    17     2321            810       911
##  8 DL        2007 N3762Y   2013     6    27      959           1900       899
##  9 DL        2047 N6716C   2013     7    22     2257            759       898
## 10 AA         172 N5DMAA   2013    12     5      756           1700       896
## # ℹ 10 more variables: arr_time <int>, sched_arr_time <int>, arr_delay <dbl>,
## #   origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>,
## #   minute <dbl>, time_hour <dttm>

Question 3: Average Departure Delay Per Date

Missing values must be removed or ignored when calculating average departure delays.

# Option A: Remove cancelled flights
not_cancelled <- flights %>%
  filter(!is.na(dep_delay))

not_cancelled %>%
  group_by(year, month, day) %>%
  summarise(
    mean = mean(dep_delay),
    .groups = "drop"
  )
## # A tibble: 365 × 4
##     year month   day  mean
##    <int> <int> <int> <dbl>
##  1  2013     1     1 11.5 
##  2  2013     1     2 13.9 
##  3  2013     1     3 11.0 
##  4  2013     1     4  8.95
##  5  2013     1     5  5.73
##  6  2013     1     6  7.15
##  7  2013     1     7  5.42
##  8  2013     1     8  2.55
##  9  2013     1     9  2.28
## 10  2013     1    10  2.84
## # ℹ 355 more rows
# Option B: Ignore missing values
flights %>%
  group_by(year, month, day) %>%
  summarise(
    mean = mean(dep_delay, na.rm = TRUE),
    .groups = "drop"
  )
## # A tibble: 365 × 4
##     year month   day  mean
##    <int> <int> <int> <dbl>
##  1  2013     1     1 11.5 
##  2  2013     1     2 13.9 
##  3  2013     1     3 11.0 
##  4  2013     1     4  8.95
##  5  2013     1     5  5.73
##  6  2013     1     6  7.15
##  7  2013     1     7  5.42
##  8  2013     1     8  2.55
##  9  2013     1     9  2.28
## 10  2013     1    10  2.84
## # ℹ 355 more rows

Question 4: Tail Number With Lowest Average Arrival Delay

First, calculate the average arrival delay for each tail number. Then, arrange the results from lowest to highest.

tailnum_delays <- flights %>%
  filter(
    !is.na(tailnum),
    !is.na(arr_delay)
  ) %>%
  group_by(tailnum) %>%
  summarise(
    avg_arr_delay = mean(arr_delay),
    .groups = "drop"
  ) %>%
  arrange(avg_arr_delay)

# Display the lowest average arrival delay
head(tailnum_delays, 10)
## # A tibble: 10 × 2
##    tailnum avg_arr_delay
##    <chr>           <dbl>
##  1 N560AS          -53  
##  2 N315AS          -51  
##  3 N517AS          -40.5
##  4 N592AS          -35.5
##  5 N7AYAA          -35  
##  6 N585AS          -34.5
##  7 N915DN          -34  
##  8 N512AS          -31.5
##  9 N594AS          -31.3
## 10 N564AS          -31
# Display the correct tail number
tailnum_delays %>%
  slice_min(avg_arr_delay, n = 1)
## # A tibble: 1 × 2
##   tailnum avg_arr_delay
##   <chr>           <dbl>
## 1 N560AS            -53
# Compare the multiple-choice options
tailnum_delays %>%
  filter(
    tailnum %in% c(
      "N844MH",
      "N928DN",
      "N427SW"
    )
  ) %>%
  arrange(avg_arr_delay)
## # A tibble: 3 × 2
##   tailnum avg_arr_delay
##   <chr>           <dbl>
## 1 N427SW            157
## 2 N928DN            201
## 3 N844MH            320

Question 5: First and Last Departure Times

Some flights depart after midnight, so not every day’s last recorded departure occurs before 12 AM.

daily_departures <- not_cancelled %>%
  group_by(year, month, day) %>%
  summarise(
    first = min(dep_time),
    last = max(dep_time),
    .groups = "drop"
  )

daily_departures %>%
  arrange(desc(last)) %>%
  head(20)
## # A tibble: 20 × 5
##     year month   day first  last
##    <int> <int> <int> <int> <int>
##  1  2013     2     7    27  2400
##  2  2013     2    11     1  2400
##  3  2013     3    15    11  2400
##  4  2013     3    22    37  2400
##  5  2013     3    25    13  2400
##  6  2013     4     2     9  2400
##  7  2013     4     4    14  2400
##  8  2013     4    20     7  2400
##  9  2013     5    21   110  2400
## 10  2013     6    17     2  2400
## 11  2013     7     7     4  2400
## 12  2013     7    13    19  2400
## 13  2013     7    17     2  2400
## 14  2013     7    28     1  2400
## 15  2013     8    10    13  2400
## 16  2013     8    20    54  2400
## 17  2013     9     2     8  2400
## 18  2013     9    12     2  2400
## 19  2013    10    30   455  2400
## 20  2013    11    27    28  2400

Question 6: Proportion of Flights Delayed More Than One Hour

Calculate the proportion of flights with departure delays greater than 60 minutes for each month.

monthly_delays <- flights %>%
  group_by(month) %>%
  summarise(
    total_flights = n(),
    delayed_over_hour = sum(
      dep_delay > 60,
      na.rm = TRUE
    ),
    proportion = mean(
      dep_delay > 60,
      na.rm = TRUE
    ),
    percentage = proportion * 100,
    .groups = "drop"
  ) %>%
  arrange(desc(proportion))

monthly_delays
## # A tibble: 12 × 5
##    month total_flights delayed_over_hour proportion percentage
##    <int>         <int>             <int>      <dbl>      <dbl>
##  1     7         29425              3820     0.134       13.4 
##  2     6         28243              3494     0.128       12.8 
##  3    12         28135              2553     0.0942       9.42
##  4     4         28330              2535     0.0916       9.16
##  5     3         28834              2340     0.0837       8.37
##  6     5         28796              2309     0.0818       8.18
##  7     8         29327              2295     0.0796       7.96
##  8     2         24951              1654     0.0698       6.98
##  9     1         27004              1821     0.0688       6.88
## 10     9         27574              1330     0.0490       4.90
## 11    10         28889              1344     0.0469       4.69
## 12    11         27268              1086     0.0402       4.02
ggplot(
  monthly_delays,
  aes(x = factor(month), y = percentage)
) +
  geom_col(fill = "steelblue") +
  labs(
    title = "Flights Delayed Over One Hour by Month",
    x = "Month",
    y = "Percentage of Flights"
  ) +
  theme_minimal()

Question 7: Destinations With the Most Carriers

Count the number of unique airlines serving each destination.

destination_carriers <- flights %>%
  group_by(dest) %>%
  summarise(
    carriers = n_distinct(carrier),
    .groups = "drop"
  ) %>%
  arrange(desc(carriers))

# View the destinations with most carriers
head(destination_carriers, 20)
## # A tibble: 20 × 2
##    dest  carriers
##    <chr>    <int>
##  1 ATL          7
##  2 BOS          7
##  3 CLT          7
##  4 ORD          7
##  5 TPA          7
##  6 AUS          6
##  7 DCA          6
##  8 DTW          6
##  9 IAD          6
## 10 MSP          6
## 11 MSY          6
## 12 PIT          6
## 13 STL          6
## 14 BNA          5
## 15 CLE          5
## 16 DEN          5
## 17 IND          5
## 18 LAS          5
## 19 LAX          5
## 20 PBI          5
# Identify all destinations tied for the maximum
destination_carriers %>%
  filter(carriers == max(carriers))
## # A tibble: 5 × 2
##   dest  carriers
##   <chr>    <int>
## 1 ATL          7
## 2 BOS          7
## 3 CLT          7
## 4 ORD          7
## 5 TPA          7
# Compare all multiple-choice options
destination_carriers %>%
  filter(
    dest %in% c(
      "CLT",
      "ORD",
      "ATL",
      "TPA",
      "BOS"
    )
  ) %>%
  arrange(desc(carriers))
## # A tibble: 5 × 2
##   dest  carriers
##   <chr>    <int>
## 1 ATL          7
## 2 BOS          7
## 3 CLT          7
## 4 ORD          7
## 5 TPA          7

Question 8: Distance and Average Arrival Delay

Step 1: Group Flights by Destination

by_dest <- group_by(flights, dest)

Step 2: Summarise the Data

delay <- summarise(
  by_dest,
  count = n(),
  dist = mean(distance, na.rm = TRUE),
  delay = mean(arr_delay, na.rm = TRUE)
)

head(delay)
## # A tibble: 6 × 4
##   dest  count  dist delay
##   <chr> <int> <dbl> <dbl>
## 1 ABQ     254 1826   4.38
## 2 ACK     265  199   4.85
## 3 ALB     439  143  14.4 
## 4 ANC       8 3370  -2.5 
## 5 ATL   17215  757. 11.3 
## 6 AUS    2439 1514.  6.02

Step 3: Filter the Data

Remove destinations with 20 or fewer flights and exclude Honolulu.

delay <- filter(
  delay,
  count > 20,
  dest != "HNL"
)

head(delay)
## # A tibble: 6 × 4
##   dest  count  dist delay
##   <chr> <int> <dbl> <dbl>
## 1 ABQ     254 1826   4.38
## 2 ACK     265  199   4.85
## 3 ALB     439  143  14.4 
## 4 ATL   17215  757. 11.3 
## 5 AUS    2439 1514.  6.02
## 6 AVL     275  584.  8.00

Step 4: Plot the Relationship

ggplot(
  data = delay,
  mapping = aes(x = dist, y = delay)
) +
  geom_point(
    aes(size = count),
    alpha = 1/3
  ) +
  geom_smooth(se = FALSE) +
  labs(
    title = "Distance vs. Average Arrival Delay",
    x = "Average Flight Distance (Miles)",
    y = "Average Arrival Delay (Minutes)"
  ) +
  theme_minimal()

Question 9: Alternative Code Using Pipes

The following code performs the same data manipulation as Question 8, excluding the plotting step.

delays <- flights %>%
  group_by(dest) %>%
  summarise(
    count = n(),
    dist = mean(distance, na.rm = TRUE),
    delay = mean(arr_delay, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  filter(
    count > 20,
    dest != "HNL"
  )

head(delays)
## # A tibble: 6 × 4
##   dest  count  dist delay
##   <chr> <int> <dbl> <dbl>
## 1 ABQ     254 1826   4.38
## 2 ACK     265  199   4.85
## 3 ALB     439  143  14.4 
## 4 ATL   17215  757. 11.3 
## 5 AUS    2439 1514.  6.02
## 6 AVL     275  584.  8.00
# Confirm both methods produce the same result
all.equal(
  as.data.frame(delay),
  as.data.frame(delays)
)
## [1] TRUE

Question 10: Review and Verification

The R for Data Science textbook can be used to verify the code and answers.

# Final verification of dataset dimensions
dim(flights)
## [1] 336776     19
# Verify monthly departure delays
monthly_delays
## # A tibble: 12 × 5
##    month total_flights delayed_over_hour proportion percentage
##    <int>         <int>             <int>      <dbl>      <dbl>
##  1     7         29425              3820     0.134       13.4 
##  2     6         28243              3494     0.128       12.8 
##  3    12         28135              2553     0.0942       9.42
##  4     4         28330              2535     0.0916       9.16
##  5     3         28834              2340     0.0837       8.37
##  6     5         28796              2309     0.0818       8.18
##  7     8         29327              2295     0.0796       7.96
##  8     2         24951              1654     0.0698       6.98
##  9     1         27004              1821     0.0688       6.88
## 10     9         27574              1330     0.0490       4.90
## 11    10         28889              1344     0.0469       4.69
## 12    11         27268              1086     0.0402       4.02
# Verify destinations with the most carriers
destination_carriers %>%
  filter(carriers == max(carriers))
## # A tibble: 5 × 2
##   dest  carriers
##   <chr>    <int>
## 1 ATL          7
## 2 BOS          7
## 3 CLT          7
## 4 ORD          7
## 5 TPA          7
# Verify tail number with lowest arrival delay
tailnum_delays %>%
  slice_min(avg_arr_delay, n = 1)
## # A tibble: 1 × 2
##   tailnum avg_arr_delay
##   <chr>           <dbl>
## 1 N560AS            -53

Conclusion

This assignment explored the NYC flights dataset using data manipulation and visualization techniques in R.

The analysis examined departure delays, arrival delays, airline carriers, destinations, and flight distances. Functions such as group_by(), summarise(), filter(), arrange(), and ggplot() were used to identify patterns and compare flight performance.