Dplyr Practice: 10 Exercises with Solutions

Setup

We will use the flights dataset from the nycflights13 package.

library(dplyr)
library(nycflights13)

Question 1: Unique Destinations by Origin

Using distinct(), group_by(), and summarise(), find the number of unique destination airports served by each origin airport (JFK, LGA, and EWR).

Your output should contain exactly three rows and two columns: origin and n_destinations.

Solution

flights %>%
  distinct(origin, dest) %>%
  group_by(origin) %>%
  summarise(
    n_destinations = n(),
    .groups = "drop"
  )

## # A tibble: 3 × 2
##   origin n_destinations
##   <chr>           <int>
## 1 EWR                86
## 2 JFK                70
## 3 LGA                68

Explanation: distinct(origin, dest) keeps unique origin-destination pairs. After grouping by origin, n() counts the number of destinations for each airport.

Question 2: Airport Names and Departure Status

Using mutate() and case_when(), create two new columns:

origin_name: “John F. Kennedy” for JFK, “LaGuardia” for LGA, and “Newark Liberty” for EWR.
delay_status: “Early” for negative departure delays, “On time” for zero, “Delayed” for positive, and NA for missing delays.

Display only origin, origin_name, dep_delay, and delay_status.

Solution

flights %>%
  mutate(
    origin_name = case_when(
      origin == "JFK" ~ "John F. Kennedy",
      origin == "LGA" ~ "LaGuardia",
      origin == "EWR" ~ "Newark Liberty",
      TRUE ~ NA_character_
    ),
    delay_status = case_when(
      is.na(dep_delay) ~ NA_character_,
      dep_delay < 0 ~ "Early",
      dep_delay == 0 ~ "On time",
      dep_delay > 0 ~ "Delayed"
    )
  ) %>%
  select(origin, origin_name, dep_delay, delay_status)

## # A tibble: 336,776 × 4
##    origin origin_name     dep_delay delay_status
##    <chr>  <chr>               <dbl> <chr>       
##  1 EWR    Newark Liberty          2 Delayed     
##  2 LGA    LaGuardia               4 Delayed     
##  3 JFK    John F. Kennedy         2 Delayed     
##  4 JFK    John F. Kennedy        -1 Early       
##  5 LGA    LaGuardia              -6 Early       
##  6 EWR    Newark Liberty         -4 Early       
##  7 EWR    Newark Liberty         -5 Early       
##  8 LGA    LaGuardia              -3 Early       
##  9 JFK    John F. Kennedy        -3 Early       
## 10 LGA    LaGuardia              -2 Early       
## # ℹ 336,766 more rows

Explanation: case_when() evaluates conditions in order. NA_character_ explicitly represents a missing character value.

Question 3: Filtering and Sorting Flights

Find flights satisfying all the following conditions:

Departed from JFK or LGA.
Flew to LAX, SFO, or MIA.
Had a departure delay greater than 30 minutes.
Took place in July or August.

Display month, day, origin, dest, carrier, and dep_delay, sorted by departure delay from highest to lowest.

Solution

flights %>%
  filter(
    origin %in% c("JFK", "LGA"),
    dest %in% c("LAX", "SFO", "MIA"),
    dep_delay > 30,
    month %in% c(7, 8)
  ) %>%
  select(month, day, origin, dest, carrier, dep_delay) %>%
  arrange(desc(dep_delay))

## # A tibble: 689 × 6
##    month   day origin dest  carrier dep_delay
##    <int> <int> <chr>  <chr> <chr>       <dbl>
##  1     7    10 JFK    LAX   VX            634
##  2     7     7 JFK    SFO   VX            629
##  3     7     6 JFK    SFO   DL            589
##  4     7    10 JFK    SFO   B6            453
##  5     8    28 JFK    MIA   DL            436
##  6     7    10 JFK    SFO   VX            432
##  7     7    10 JFK    LAX   VX            413
##  8     7     7 JFK    SFO   VX            396
##  9     8    18 JFK    LAX   VX            392
## 10     7     9 JFK    SFO   AA            364
## # ℹ 679 more rows

Explanation: %in% tests whether a value belongs to a specified set. Multiple conditions inside filter() are combined using AND.

Question 4: Airline Performance Summary

For each airline (carrier), calculate:

Total number of flights.
Number of distinct destinations served.
Average departure delay, excluding missing values.
Percentage of flights departing more than 15 minutes late, excluding missing departure delays from the denominator.

Sort by average departure delay from highest to lowest.

Solution

flights %>%
  group_by(carrier) %>%
  summarise(
    n_flights = n(),
    n_destinations = n_distinct(dest),
    avg_dep_delay = mean(dep_delay, na.rm = TRUE),
    pct_late = 100 * mean(dep_delay > 15, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  arrange(desc(avg_dep_delay))

## # A tibble: 16 × 5
##    carrier n_flights n_destinations avg_dep_delay pct_late
##    <chr>       <int>          <int>         <dbl>    <dbl>
##  1 F9            685              1         20.2     28.2 
##  2 EV          54173             61         20.0     30.5 
##  3 YV            601              3         19.0     28.6 
##  4 FL           3260              3         18.7     26.7 
##  5 WN          12275             11         17.7     26.9 
##  6 9E          18460             49         16.7     25.7 
##  7 B6          54635             42         13.0     22.8 
##  8 VX           5162              5         12.9     17.4 
##  9 OO             32              5         12.6     20.7 
## 10 UA          58665             47         12.1     21.0 
## 11 MQ          26397             20         10.6     21.1 
## 12 DL          48110             40          9.26    16.3 
## 13 AA          32729             19          8.59    15.9 
## 14 AS            714              1          5.80    13.2 
## 15 HA            342              1          4.90     7.02
## 16 US          20536              6          3.78    12.2

Explanation: n_distinct() counts unique values directly. The logical expression dep_delay > 15 produces TRUE/FALSE values, treated as 1/0 by mean(). Using na.rm = TRUE excludes missing delays.

Question 5: Arrival Delay Categories

Create a column called arrival_category using case_when():

Arrival delay	Category
Less than 0	Early
Exactly 0	On time
1–30 minutes	Minor delay
31–60 minutes	Moderate delay
More than 60 minutes	Severe delay
Missing	Unknown

Then count the number of flights in each category, sorted from most frequent to least frequent.

Solution

flights %>%
  mutate(
    arrival_category = case_when(
      is.na(arr_delay) ~ "Unknown",
      arr_delay < 0 ~ "Early",
      arr_delay == 0 ~ "On time",
      arr_delay <= 30 ~ "Minor delay",
      arr_delay <= 60 ~ "Moderate delay",
      TRUE ~ "Severe delay"
    )
  ) %>%
  count(arrival_category, sort = TRUE)

## # A tibble: 6 × 2
##   arrival_category      n
##   <chr>             <int>
## 1 Early            188933
## 2 Minor delay       81505
## 3 Severe delay      27789
## 4 Moderate delay    23710
## 5 Unknown            9430
## 6 On time            5409

Explanation: Conditions in case_when() are checked from top to bottom. Because earlier conditions already exclude delays of 30 minutes or less, arr_delay <= 60 correctly identifies moderate delays.

Question 6: Destinations Served by Multiple Airlines

Find destinations served by at least three different airlines.

For each qualifying destination, display:

Destination airport.
Number of distinct airlines.
Total number of flights.
Average arrival delay, excluding missing values.

Only include destinations with at least 500 flights. Sort by average arrival delay from highest to lowest.

Solution

flights %>%
  group_by(dest) %>%
  summarise(
    n_carriers = n_distinct(carrier),
    n_flights = n(),
    avg_arr_delay = mean(arr_delay, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  filter(
    n_carriers >= 3,
    n_flights >= 500
  ) %>%
  arrange(desc(avg_arr_delay))

## # A tibble: 52 × 4
##    dest  n_carriers n_flights avg_arr_delay
##    <chr>      <int>     <int>         <dbl>
##  1 CVG            4      3941          15.4
##  2 OMA            3       849          14.7
##  3 MCI            3      2008          14.5
##  4 MKE            4      2802          14.2
##  5 IAD            6      5700          13.9
##  6 SDF            3      1157          12.7
##  7 JAX            4      2720          11.8
##  8 BNA            5      6333          11.8
##  9 PWM            3      2352          11.7
## 10 ROC            3      2416          11.6
## # ℹ 42 more rows

Explanation: summarise() creates one row per destination. We then use filter() to keep only destinations meeting both conditions.

Question 7: Using across() for Multiple Statistics

For each origin airport, calculate the mean and median of:

dep_delay
arr_delay
air_time

Use a single across() statement with a list of functions. Ignore missing values.

Solution

flights %>%
  group_by(origin) %>%
  summarise(
    across(
      c(dep_delay, arr_delay, air_time),
      .fns = list(
        mean = ~ mean(.x, na.rm = TRUE),
        median = ~ median(.x, na.rm = TRUE)
      ),
      .names = "{.col}_{.fn}"
    ),
    .groups = "drop"
  )

## # A tibble: 3 × 7
##   origin dep_delay_mean dep_delay_median arr_delay_mean arr_delay_median
##   <chr>           <dbl>            <dbl>          <dbl>            <dbl>
## 1 EWR              15.1               -1           9.11               -4
## 2 JFK              12.1               -1           5.55               -6
## 3 LGA              10.3               -3           5.78               -5
## # ℹ 2 more variables: air_time_mean <dbl>, air_time_median <dbl>

Explanation: across() applies multiple functions to multiple columns. The .names argument determines the names of the output columns.

Question 8: Performance Relative to Airline Average

For each airline, create:

carrier_avg_delay: the airline’s average departure delay, excluding missing values.
relative_status:
- “Better than average” if a flight’s delay is below its airline’s average.
- “Worse than average” if above.
- “Exactly average” if equal.
- NA if the flight delay or airline average is missing.

Display carrier, dep_delay, carrier_avg_delay, and relative_status.

Solution

flights %>%
  group_by(carrier) %>%
  mutate(
    carrier_avg_delay = mean(dep_delay, na.rm = TRUE),
    relative_status = case_when(
      is.na(dep_delay) | is.na(carrier_avg_delay) ~ NA_character_,
      dep_delay < carrier_avg_delay ~ "Better than average",
      dep_delay > carrier_avg_delay ~ "Worse than average",
      TRUE ~ "Exactly average"
    )
  ) %>%
  ungroup() %>%
  select(carrier, dep_delay, carrier_avg_delay, relative_status)

## # A tibble: 336,776 × 4
##    carrier dep_delay carrier_avg_delay relative_status    
##    <chr>       <dbl>             <dbl> <chr>              
##  1 UA              2             12.1  Better than average
##  2 UA              4             12.1  Better than average
##  3 AA              2              8.59 Better than average
##  4 B6             -1             13.0  Better than average
##  5 DL             -6              9.26 Better than average
##  6 UA             -4             12.1  Better than average
##  7 B6             -5             13.0  Better than average
##  8 EV             -3             20.0  Better than average
##  9 B6             -3             13.0  Better than average
## 10 AA             -2              8.59 Better than average
## # ℹ 336,766 more rows

Explanation: Unlike summarise(), mutate() preserves the original rows. Because the data is grouped by carrier, the mean is calculated separately for each airline and assigned to all of its flights.

Question 9: Three Worst Days for Each Airline

Find the three worst days for each airline based on average arrival delay.

Define a day using year, month, and day.

Only consider airline-day combinations with at least 10 flights having nonmissing arrival delays.

Return carrier, year, month, day, n_flights, and avg_arr_delay.

Use slice_max() and return no more than three rows per airline.

Solution

flights %>%
  filter(!is.na(arr_delay)) %>%
  group_by(carrier, year, month, day) %>%
  summarise(
    n_flights = n(),
    avg_arr_delay = mean(arr_delay),
    .groups = "drop"
  ) %>%
  filter(n_flights >= 10) %>%
  group_by(carrier) %>%
  slice_max(
    order_by = avg_arr_delay,
    n = 3,
    with_ties = FALSE
  ) %>%
  ungroup() %>%
  arrange(carrier, desc(avg_arr_delay)) %>%
  select(carrier, year, month, day, n_flights, avg_arr_delay)

## # A tibble: 33 × 6
##    carrier  year month   day n_flights avg_arr_delay
##    <chr>   <int> <int> <int>     <int>         <dbl>
##  1 9E       2013     7    22        31         105. 
##  2 9E       2013    12    14        35         101. 
##  3 9E       2013     1    16        32         101. 
##  4 AA       2013     3     8        91          78.4
##  5 AA       2013     6    13        94          61.1
##  6 AA       2013     5    23        86          60.3
##  7 B6       2013     3     8       154          92.7
##  8 B6       2013     7     1       144          90.0
##  9 B6       2013     7    10       159          71.7
## 10 DL       2013     3     8       131          69.1
## # ℹ 23 more rows

Explanation: We first remove missing arrival delays, calculate daily airline averages, and then use slice_max() within each airline. with_ties = FALSE ensures at most three rows per airline.

Question 10: Comprehensive Destination Analysis

Among destinations with at least 1,000 scheduled flights, identify the five destinations with the worst departure-delay performance.

For each destination, calculate:

n_flights: total scheduled flights.
n_delayed: flights with dep_delay > 0.
pct_delayed: percentage of flights delayed, excluding missing departure delays from the denominator.
avg_delay: average departure delay among delayed flights only.
n_carriers: number of distinct airlines serving the destination.

Rank by pct_delayed (highest first), breaking ties by avg_delay (highest first), then destination code alphabetically.

Return exactly five destinations.

Solution

flights %>%
  group_by(dest) %>%
  summarise(
    n_flights = n(),
    n_delayed = sum(dep_delay > 0, na.rm = TRUE),
    pct_delayed = 100 * mean(dep_delay > 0, na.rm = TRUE),
    avg_delay = mean(dep_delay[dep_delay > 0 & !is.na(dep_delay)]),
    n_carriers = n_distinct(carrier),
    .groups = "drop"
  ) %>%
  filter(n_flights >= 1000) %>%
  arrange(
    desc(pct_delayed),
    desc(avg_delay),
    dest
  ) %>%
  slice_head(n = 5)

## # A tibble: 5 × 6
##   dest  n_flights n_delayed pct_delayed avg_delay n_carriers
##   <chr>     <int>     <int>       <dbl>     <dbl>      <int>
## 1 MDW        4113      2251        55.7      35.5          1
## 2 PDX        1354       681        50.5      35.4          3
## 3 DEN        7266      3624        50.3      33.7          5
## 4 MHT        1009       458        49.1      47.8          2
## 5 MKE        2802      1312        48.3      43.1          4

Explanation: We calculate statistics for each destination. The percentage excludes missing departure delays. The average delay includes only flights that actually departed late. Finally, we filter, sort, and select the five worst destinations.

Summary of Functions Practiced

Function	Questions
`distinct()`	1
`n_distinct()`	4, 6, 10
`filter()`	3, 6, 9, 10
`mutate()`	2, 5, 8
`case_when()`	2, 5, 8
`group_by()`	1, 4, 6, 7, 8, 9, 10
`summarise()`	1, 4, 6, 7, 9, 10
`across()`	7
`arrange()`	3, 4, 6, 9, 10
`count()`	5
`slice_max()`	9
`slice_head()`	10
`is.na()`	2, 5, 8, 9
`%in%`	3

Ahmad Mousavi

Setup

Question 1: Unique Destinations by Origin

Solution

Question 2: Airport Names and Departure Status

Solution

Question 3: Filtering and Sorting Flights

Solution

Question 4: Airline Performance Summary

Solution

Question 5: Arrival Delay Categories

Solution

Question 6: Destinations Served by Multiple Airlines

Solution

Question 7: Using across() for Multiple Statistics

Solution

Question 8: Performance Relative to Airline Average

Solution

Question 9: Three Worst Days for Each Airline

Solution

Question 10: Comprehensive Destination Analysis

Solution

Summary of Functions Practiced