Assignment 2: Data Transformation

Due by 11:59 PM on Sunday, April 12, 2026

NoteAssignment Details

Assigned: Wednesday, April 8 (Session 4) Due: Sunday, April 12 at 11:59 PM Submit: R script (.R file) on Canvas

TipGetting started

See the step-by-step guides: Setting Up an R Project | Using R Scripts

Overview

This assignment practices data transformation using dplyr verbs and the pipe operator. You’ll work with the nycflights13 dataset to answer questions about flight delays.

Setup

# Assignment 2: Data Transformation
# Your Name
# Date

library(tidyverse)
library(nycflights13)

# If you haven't installed nycflights13:
# install.packages("nycflights13")

Part 1: Filtering and Arranging (25 points)

Task 1.1

Find all flights that departed from JFK in June and were delayed by more than 1 hour. How many flights meet these criteria?

NoteAnswer

jfk_june_delayed <- flights |>
  filter(origin == "JFK", month == 6, dep_delay > 60)

jfk_june_delayed

# A tibble: 1,185 × 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     6     1     1439           1300        99     1741           1555
 2  2013     6     1     1837           1650       107     2054           1914
 3  2013     6     1     2012           1710       182     2142           1915
 4  2013     6     1     2104           1919       105     2338           2220
 5  2013     6     1     2150           2031        79       37           2348
 6  2013     6     1     2209           1925       164     2345           2137
 7  2013     6     2       24           2245        99      133              1
 8  2013     6     2     1505           1355        70     1745           1643
 9  2013     6     2     1605           1455        70     1746           1640
10  2013     6     2     1718           1600        78     1911           1833
# ℹ 1,175 more rows
# ℹ 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>

nrow(jfk_june_delayed)

[1] 1185

Task 1.2

Of those flights, which one had the longest departure delay? Use arrange() to find out. Report the carrier, destination, and delay time.

NoteAnswer

flights |>
  filter(origin == "JFK", month == 6, dep_delay > 60) |>
  arrange(desc(dep_delay))

# A tibble: 1,185 × 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     6    15     1432           1935      1137     1607           2120
 2  2013     6    27      959           1900       899     1236           2226
 3  2013     6    27      615           1705       790      853           2004
 4  2013     6    24      159           1735       504      432           2108
 5  2013     6     7     2359           1700       419      201           1830
 6  2013     6    23     1833           1200       393       NA           1507
 7  2013     6    13     1627            959       388     1815           1114
 8  2013     6    27      221           2000       381      309           2129
 9  2013     6    25     1421            805       376     1602            950
10  2013     6     7       29           1818       371      336           2120
# ℹ 1,175 more rows
# ℹ 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>

# The first row is the worst offender — look at carrier, dest, and dep_delay.

Task 1.3

Find all flights to Los Angeles (LAX) or San Francisco (SFO) that departed on time or early. How many were there?

NoteAnswer

flights |>
  filter(dest %in% c("LAX", "SFO"), dep_delay <= 0) |>
  nrow()

[1] 17125

dep_delay <= 0 captures both on-time (0) and early (negative) departures. A common mistake here is using dest == "LAX" | dest == "SFO" instead of dest %in% c("LAX", "SFO"). Both work, but %in% is cleaner when checking one variable against multiple values.

Part 2: Selecting and Mutating (35 points)

Task 2.1

Create a new dataset that contains only:

• carrier
• origin
• dest
• dep_delay
• arr_delay

Call this dataset flights_subset.

NoteAnswer

flights_subset <- flights |>
  select(carrier, origin, dest, dep_delay, arr_delay)

flights_subset

# A tibble: 336,776 × 5
   carrier origin dest  dep_delay arr_delay
   <chr>   <chr>  <chr>     <dbl>     <dbl>
 1 UA      EWR    IAH           2        11
 2 UA      LGA    IAH           4        20
 3 AA      JFK    MIA           2        33
 4 B6      JFK    BQN          -1       -18
 5 DL      LGA    ATL          -6       -25
 6 UA      EWR    ORD          -4        12
 7 B6      EWR    FLL          -5        19
 8 EV      LGA    IAD          -3       -14
 9 B6      JFK    MCO          -3        -8
10 AA      LGA    ORD          -2         8
# ℹ 336,766 more rows

Task 2.2

Using flights_subset, create two new variables:

• delay_diff: the difference between arrival delay and departure delay
• dep_delay_hrs: the departure delay in hours (not minutes)

NoteAnswer

Tip

This builds on Task 2.1 — in your own script, make sure you’ve created flights_subset first.

flights_subset <- flights_subset |>
  mutate(
    delay_diff = arr_delay - dep_delay,
    dep_delay_hrs = dep_delay / 60
  )

flights_subset

# A tibble: 336,776 × 7
   carrier origin dest  dep_delay arr_delay delay_diff dep_delay_hrs
   <chr>   <chr>  <chr>     <dbl>     <dbl>      <dbl>         <dbl>
 1 UA      EWR    IAH           2        11          9        0.0333
 2 UA      LGA    IAH           4        20         16        0.0667
 3 AA      JFK    MIA           2        33         31        0.0333
 4 B6      JFK    BQN          -1       -18        -17       -0.0167
 5 DL      LGA    ATL          -6       -25        -19       -0.1   
 6 UA      EWR    ORD          -4        12         16       -0.0667
 7 B6      EWR    FLL          -5        19         24       -0.0833
 8 EV      LGA    IAD          -3       -14        -11       -0.05  
 9 B6      JFK    MCO          -3        -8         -5       -0.05  
10 AA      LGA    ORD          -2         8         10       -0.0333
# ℹ 336,766 more rows

Task 2.3

What does a positive delay_diff mean? What does a negative value mean? Answer in a comment, then find a flight that “made up time” (arrived less delayed than it departed).

NoteAnswer

Tip

This builds on Tasks 2.1 and 2.2 — delay_diff must exist in flights_subset before running this.

# A positive delay_diff means the flight arrived MORE delayed than it departed —
# it lost additional time in the air (e.g., circling, headwinds).
#
# A negative delay_diff means the flight "made up time" — it arrived
# less delayed than it departed (e.g., favorable tailwinds, shorter route).

flights_subset |>
  filter(dep_delay > 0, delay_diff < 0) |>
  arrange(delay_diff)

# A tibble: 83,728 × 7
   carrier origin dest  dep_delay arr_delay delay_diff dep_delay_hrs
   <chr>   <chr>  <chr>     <dbl>     <dbl>      <dbl>         <dbl>
 1 EV      EWR    JAX         235       126       -109         3.92 
 2 HA      JFK    HNL          60       -27        -87         1    
 3 HA      JFK    HNL         206       126        -80         3.43 
 4 DL      JFK    SFO          17       -62        -79         0.283
 5 HA      JFK    HNL          24       -52        -76         0.4  
 6 UA      EWR    SNA          48       -26        -74         0.8  
 7 UA      EWR    SFO          34       -40        -74         0.567
 8 UA      EWR    SFO          31       -42        -73         0.517
 9 DL      JFK    LAX           9       -63        -72         0.15 
10 UA      EWR    LAX          19       -53        -72         0.317
# ℹ 83,718 more rows

Part 3: Grouped Summaries (30 points)

Task 3.1

Calculate the average departure delay for each carrier. Which carrier has the worst average delay?

NoteAnswer

flights |>
  group_by(carrier) |>
  summarize(avg_dep_delay = mean(dep_delay, na.rm = TRUE)) |>
  arrange(desc(avg_dep_delay))

# A tibble: 16 × 2
   carrier avg_dep_delay
   <chr>           <dbl>
 1 F9              20.2 
 2 EV              20.0 
 3 YV              19.0 
 4 FL              18.7 
 5 WN              17.7 
 6 9E              16.7 
 7 B6              13.0 
 8 VX              12.9 
 9 OO              12.6 
10 UA              12.1 
11 MQ              10.6 
12 DL               9.26
13 AA               8.59
14 AS               5.80
15 HA               4.90
16 US               3.78

# Frontier Airlines (F9) has the worst average departure delay.
# na.rm = TRUE is essential — without it, any carrier with even one
# missing value returns NA for the whole mean.

Task 3.2

Calculate the average departure delay for each origin airport, by month. Which month is the worst for delays at each airport?

NoteAnswer

flights |>
  group_by(origin, month) |>
  summarize(avg_dep_delay = mean(dep_delay, na.rm = TRUE)) |>
  arrange(origin, desc(avg_dep_delay))

# A tibble: 36 × 3
# Groups:   origin [3]
   origin month avg_dep_delay
   <chr>  <int>         <dbl>
 1 EWR        6         22.5 
 2 EWR        7         22.0 
 3 EWR       12         21.0 
 4 EWR        3         18.1 
 5 EWR        4         17.4 
 6 EWR        5         15.4 
 7 EWR        1         14.9 
 8 EWR        8         13.5 
 9 EWR        2         13.1 
10 EWR       10          8.64
# ℹ 26 more rows

# Summer months (June/July) tend to be worst across all three airports.

group_by(origin, month) groups by the combination of both variables, giving one row per airport-month pair.

Task 3.3

Create a summary table showing, for each carrier:

• Average departure delay
• Average arrival delay
• Number of flights
• Number of destinations served (n_distinct())

Arrange by number of flights (descending).

NoteAnswer

flights |>
  group_by(carrier) |>
  summarize(
    avg_dep_delay = mean(dep_delay, na.rm = TRUE),
    avg_arr_delay = mean(arr_delay, na.rm = TRUE),
    n_flights = n(),
    n_dest = n_distinct(dest)
  ) |>
  arrange(desc(n_flights))

# A tibble: 16 × 5
   carrier avg_dep_delay avg_arr_delay n_flights n_dest
   <chr>           <dbl>         <dbl>     <int>  <int>
 1 UA              12.1          3.56      58665     47
 2 B6              13.0          9.46      54635     42
 3 EV              20.0         15.8       54173     61
 4 DL               9.26         1.64      48110     40
 5 AA               8.59         0.364     32729     19
 6 MQ              10.6         10.8       26397     20
 7 US               3.78         2.13      20536      6
 8 9E              16.7          7.38      18460     49
 9 WN              17.7          9.65      12275     11
10 VX              12.9          1.76       5162      5
11 FL              18.7         20.1        3260      3
12 AS               5.80        -9.93        714      1
13 F9              20.2         21.9         685      1
14 YV              19.0         15.6         601      3
15 HA               4.90        -6.92        342      1
16 OO              12.6         11.9          32      5

n() counts rows (total flights) while n_distinct(dest) counts unique values — these serve very different purposes.

Putting It Together

Write a single piped sequence that:

1. Filters to United Airlines (UA) flights
2. Removes rows with missing arrival delay
3. Groups by destination
4. Calculates mean arrival delay and number of flights
5. Filters to destinations with at least 100 flights
6. Arranges by mean delay (worst first)

What are the top 3 worst destinations for United delays?

NoteAnswer

flights |>
  filter(carrier == "UA") |>
  filter(!is.na(arr_delay)) |>
  group_by(dest) |>
  summarize(
    mean_arr_delay = mean(arr_delay),
    n_flights = n()
  ) |>
  filter(n_flights >= 100) |>
  arrange(desc(mean_arr_delay))

# A tibble: 29 × 3
   dest  mean_arr_delay n_flights
   <chr>          <dbl>     <int>
 1 BQN            10.9        295
 2 ATL            10.5        102
 3 SAT             7.69       323
 4 MIA             6.66      1548
 5 CLE             6.41      1863
 6 ORD             6.07      6744
 7 SEA             5.83      1101
 8 PBI             5.41      1825
 9 FLL             5.25      2376
10 DEN             5.16      3737
# ℹ 19 more rows

A common mistake is putting filter(n_flights >= 100) before summarize() — at that point, n_flights doesn’t exist yet. The filter on a summary variable always has to come after summarize().

Note: we don’t need na.rm = TRUE inside mean() here because filter(!is.na(arr_delay)) already removed the missing rows. Both approaches work, but the explicit filter step makes the cleaning visible.

Grading Rubric

Component	Points
Part 1: Filtering & Arranging	25
Part 2: Selecting & Mutating	35
Part 3: Grouped Summaries	30
Code runs without errors	10
Total	100

Submission

• Script runs without errors
• All tasks completed with clear comments
• Answers to questions in comments
• Your name at the top

Submit your .R file on Canvas.