7 Into the Tidyverse: summarize, group_by, and drop_na
By the end of this chapter you should be able to:
- Collapse a data frame into summary statistics with
summarize(). - Choose a summary function, such as
mean(),median(),max(), orn(), for the question you are asking. - Calculate a statistic for each group of rows with
group_by()andsummarize(). - Handle missing values with
na.rm = TRUEordrop_na(), and explain how the two differ. - Explain why the average of averages can mislead, and calculate a weighted average instead.
So far, every verb you have used gives back one row for each flight, or fewer flights, or the same flights in a new order. But many questions are about many flights at once. What was the average departure delay in 2013? Which month had the worst delays? Which plane was late most often? This chapter introduces the verbs that answer them: summarize(), which collapses many rows into a few numbers, and group_by(), which makes summarize() give one answer for each group. Along the way you will meet drop_na(), a third way to deal with the missing values you met two chapters ago.
The examples still use the nycflights13 data, and the chapter ends with a question that looks simple but has a trap in it: how fast did planes fly each month?
This code is meant to find the fastest flights in October. Read the error message, then trace the code one line at a time.
R says object 'distance' not found. The filter() line has no pipe at the end, so the chain stops there: flights_new is just the October flights. R then runs mutate() as a separate command, with no data frame, so it can’t find a column called distance. The fix is to add |> at the end of the filter() line. The error message names the symptom (a missing object), not the cause (a missing pipe), so when a column “can’t be found,” check that every line but the last ends with |>.
7.1 Tidy data and the verbs so far
7.1.1 Tidy data
dplyr’s verbs expect tidy data, which follows three rules (Wickham et al. 2023):
- Each variable is a column.
- Each observation is a row.
- Each value is a cell.
flights is tidy. Each row is one flight, each column is one thing recorded about it, such as dep_delay or carrier, and each cell holds one value. Because every month is in the same column, and every delay in the same column, a single line of code can work on all of them at once. That is what makes this chapter’s verbs possible.
7.1.2 Three new verbs
Table 7.1 adds this chapter’s verbs to the ones you already know.
| Works on | Verb | What it does |
|---|---|---|
| Rows | filter() |
Keeps the rows that meet a condition |
| Rows | arrange() |
Sorts the rows |
| Rows | distinct() |
Keeps one row for each unique value |
| Rows | group_by() |
Groups the rows that share a value (new) |
| Rows | drop_na() |
Removes the rows with missing values (new) |
| Columns | select() |
Keeps the columns you name |
| Columns | mutate() |
Adds new columns |
| Columns | summarize() |
Collapses many rows into summary columns (new) |
drop_na() comes from the tidyr package rather than dplyr, but library(tidyverse) loads both, so you don’t need to do anything extra. The live code boxes in this chapter load dplyr and tidyr separately, because that is faster in a web browser.
7.2 Summarizing with summarize()
7.2.1 One number from many rows
How late, on average, did flights leave New York in 2013? summarize() collapses a whole data frame into a single row of summary statistics. Inside it, name the new column and say how to calculate it:
The answer is one row and one column: across all 336,776 flights, the average departure delay was about 12.6 minutes. delay is the name we chose for the result; you can call it anything. mean(dep_delay) is the calculation.
na.rm = TRUE (“remove NAs”) tells mean() to skip the missing values. Without it, the answer is NA, because the 8,255 cancelled flights have no departure delay, and any calculation that includes an unknown number gives an unknown answer. Try it: delete , na.rm = TRUE and run the box again.
summarize() is also spelled summarise(). The two are the same function; use whichever spelling you like.
7.2.2 Summary functions
The calculation inside summarize() can be any function that takes a column and returns one number (Table 7.2).
summarize(), with their results for departure delays in 2013. All but n() need na.rm = TRUE for dep_delay.
| Function | What it returns | dep_delay in 2013 |
|---|---|---|
mean() |
The average | 12.6 minutes |
median() |
The middle value: half are above, half below | −2 minutes |
min(), max() |
The smallest and largest values | −43 and 1,301 minutes |
quantile(x, 0.25) |
The value below which a given share falls; here the 25th percentile | −5 minutes |
sd() |
The standard deviation: how spread out the values are | 40.2 minutes |
var() |
The variance, the standard deviation squared | 1,617 |
sum() |
The total | |
n() |
The number of rows | 336,776 |
n() is different from the others: it takes no arguments, and it counts rows rather than looking at a column. It only works inside dplyr verbs such as summarize().
Look at the first two rows of the table. The mean delay is 12.6 minutes, but the median is −2 minutes: more than half of all flights left early. How can both be true? Delays are skewed. Most flights leave a few minutes early or late, but a few leave hours late, and those few pull the mean up a long way. The median only cares about the middle flight, so it ignores them. When a variable has a long tail like this, report the median as well as the mean, or instead of it.
7.2.3 Several statistics at once
Separate several statistics with commas. Each becomes its own column, still in a single row:
The longest delay was 1,301 minutes, almost 22 hours. The shortest is negative: one flight left 43 minutes before its scheduled time. One maximum for the whole year is a start, but it would be more useful to know the longest delay in each month. That is the job of group_by().
7.3 One result per group with group_by()
7.3.1 Grouping, then summarizing
group_by() sorts the rows into groups that share a value. On its own it changes nothing you can see. But every verb after it then works on each group separately. Put group_by(month) in front of the summarize() from the last section:
The summarize() code is exactly the same, but now there are 12 rows, one per month, instead of one. group_by(month) split the 336,776 flights into 12 groups, and summarize() ran once for each. The grouping column, month, stays in the result so you can tell the rows apart. The longest delay of the year was in January, and the longest in June was 1,137 minutes.
Which month had the worst delays on average? Group by month, take the mean, and sort:
July (21.7 minutes) and June (20.8) were the worst; October and November, at around 6 minutes, were the best. Summer thunderstorms are a likely suspect. n() counts the flights in each group, here between about 25,000 and 29,000 a month.
You can group by any column, and by more than one: group_by(origin) gives one row per airport, and group_by(origin, month) gives one row for each airport in each month, 36 rows in all.
7.3.2 group_by() on its own
What does group_by() do without summarize()?
Nothing seems to change: there are still 336,776 rows and 19 columns, with the same values. The only difference is a new line at the top, Groups: month [12]. The data frame is now a grouped data frame: it carries a hidden note saying that its rows belong to 12 groups by month.
That note matters, because every verb that comes later works within each group, until you remove it with ungroup(). That is what you want right before summarize(), but it can surprise you later in a chain. For example, mutate(mean_delay = mean(dep_delay, na.rm = TRUE)) on a grouped data frame gives each flight the mean delay for its month, not for the year. When you have finished working by group, add ungroup():
flights |>
group_by(month) |>
mutate(month_mean = mean(dep_delay, na.rm = TRUE)) |>
ungroup()summarize() removes one level of grouping for you, so after group_by(month) |> summarize(...) the result is no longer grouped. After group_by(origin, month), it is still grouped by origin, and dplyr prints a message to say so.
7.4 Removing missing values with drop_na()
7.4.1 Two ways to skip NAs
You have now seen na.rm = TRUE many times. drop_na(), from tidyr, is another way to deal with missing values: it removes every row where a column you name is NA. These two chains give exactly the same 12 rows:
In the second, drop_na(dep_delay) removes the 8,255 flights with no departure delay before summarize() sees them, so max() and min() never meet an NA and don’t need na.rm.
7.4.2 They are not the same thing
Here the results match, but the two approaches do different things (Table 7.3).
na.rm = TRUE and drop_na() differ.
na.rm = TRUE |
drop_na() |
|
|---|---|---|
| Where it goes | Inside one summary function | As its own step in the pipe chain |
| What it changes | Only that one calculation | The data: whole rows are removed for every later step |
| Columns affected | The one being summarized | All columns, because whole rows go |
The difference shows up when you count. n() counts rows, whether or not they have missing values. With na.rm = TRUE, n() still counts the cancelled flights; after drop_na(dep_delay), it doesn’t. Neither is wrong, but they answer different questions (“how many flights were scheduled?” against “how many flights have a delay recorded?”), so choose on purpose.
7.4.3 Name your columns
drop_na() with no columns named removes every row that has an NA in any column. That can remove more than you meant to:
drop_na(dep_delay) keeps 328,521 flights. drop_na() keeps only 327,346, because it also removes flights that departed but have no arrival delay or air time recorded. Always name the columns your question needs.
7.5 Practice: how fast did planes fly each month?
7.5.1 The obvious way
Here is a question that combines everything so far: what was the average air speed of flights from New York in each month? A first attempt might calculate the speed of every flight with mutate(), then take the mean for each month:
That runs without errors and gives one speed per month: about 370 miles per hour in January. But is it the right answer?
7.5.2 The average of averages
The problem isn’t the code; it is the statistics. mean(speed) gives every flight an equal say. A half-hour hop to Philadelphia counts as much as a 6-hour flight to San Francisco. Short flights spend most of their time climbing and descending, so they are slow, and there are a lot of them, so they drag the average down.
What does “the average speed in January” really ask for? A good definition is the total distance flown in January, divided by the total time spent in the air:
\[ \text{speed}_{\text{month}} = \frac{\sum \text{distance}_{\text{month}}}{\sum \text{air\_time}_{\text{month}}} \times 60 \]
This is a weighted average of the flights’ speeds. Each flight counts in proportion to its air time, so a 6-hour flight counts twelve times as much as a 30-minute one.
7.5.3 Sum first, then divide
To calculate it, add up the distances and air times in each month with summarize(), and then divide the totals with mutate():
The order matters: summarize() runs first to get one total per month, and mutate() then works on those 12 rows. In January the speed is 394 miles per hour, against 370 from averaging the flights’ speeds, and the weighted speed is higher in every month. Long flights, which spend most of their time at cruising speed, now count in proportion to the time they spend in the air.
Here drop_na(distance, air_time) does more than clean up. Every cancelled flight still has a distance, because the route is known, but no air_time. If you used sum(air_time, na.rm = TRUE) without dropping those rows, the total distance would include miles that were never flown, and January’s speed would come out at about 401 miles per hour, too fast. Dropping the rows keeps the distances and the times about the same flights.
7.6 Practice: the least punctual plane
These steps follow the in-class exercise. Every plane has a tail number, in the tailnum column. Which plane had the worst on-time record, meaning it left late the most times?
Step 1: set up. Start a new R Markdown file, and load the packages and look at the data:
library(tidyverse)
library(nycflights13)
glimpse(flights)Step 2: decide what “late” means. This is a choice, not a fact in the data. Is a flight late if it leaves one minute after its scheduled time (dep_delay > 0)? Or if it arrives more than 15 minutes late (arr_delay > 15)? The US Department of Transportation counts a flight as late if it arrives 15 minutes or more after its scheduled time (arr_delay >= 15). Pick one and write it down.
Step 3: count the late flights for each plane. Keep the late flights with filter(), group them by plane, count each group with n(), and sort. Try it first:
flights |>
filter(dep_delay > 0) |>
group_by(tailnum) |>
summarize(n_late = n()) |>
arrange(desc(n_late))N258JB, a JetBlue plane, left late 186 times in 2013. If you define late as arr_delay > 15 instead, N258JB still comes first, with 121 late arrivals. But with the Department of Transportation’s rule, arr_delay >= 15, a different plane wins: N725MQ, with 126 late arrivals to N258JB’s 123. Changing > to >= changed the answer, which is why step 2 matters.
Step 4: be fair to busy planes. N258JB flew 427 times in 2013, so of course it was late a lot: it flew a lot. A fairer measure is the share of each plane’s flights that left late. Count all the flights and the late ones for each plane, then divide:
flights |>
group_by(tailnum) |>
summarize(
n_flights = n(),
n_late = sum(dep_delay > 0, na.rm = TRUE)
) |>
mutate(share_late = n_late / n_flights) |>
arrange(desc(share_late))sum() of a logical vector counts the TRUEs, because R treats TRUE as 1 and FALSE as 0. Look at the top of the result. Planes that flew once or twice, and were late once, have a share of 1, which tells you nothing. Add filter(n_flights >= 100) before the arrange() to keep the planes with a decent record. Also look for a row where tailnum is NA: 2,512 flights, all of them cancelled, have no tail number, and group_by() puts them together in one group. drop_na(tailnum) removes it.
7.7 Check your understanding
flights |> summarize(delay = mean(dep_delay, na.rm = TRUE))returns how many rows?Without
na.rm = TRUE,mean(dep_delay)returnsNAfor theflightsdata.Which function counts the rows in each group, and takes no arguments?
The mean departure delay is 12.6 minutes but the median is −2 minutes. Why?
flights |> group_by(month)on its own returns 12 rows.flights |> group_by(origin) |> summarize(n = n())returns how many rows?drop_na()with no columns named removes the rows that have anNAin:To find the average speed for a month, it is better to divide the total distance by the total air time than to take the mean of the flights’ speeds.
group_by() doesn’t change the rows. It only notes which group each row is in. It takes summarize() to collapse each group into one row.
7.8 Summary
- dplyr’s verbs work on tidy data: each variable a column, each observation a row, each value a cell.
summarize()collapses a data frame into one row of summary statistics, withname = function(column). Separate several statistics with commas.- Useful summary functions include
mean(),median(),min(),max(),quantile(),sd(),var(),sum(), andn(). Skewed data, like flight delays, can have a mean far from the median. group_by()splits the rows into groups. On its own it changes nothing you can see, butsummarize()after it gives one row per group. Useungroup()when you have finished working by group.na.rm = TRUEskips missing values in one calculation.drop_na()removes whole rows with missing values from the data. Always name the columns indrop_na().- The average of averages gives every row an equal say. To find a rate such as speed for a group, sum first, then divide: that is a weighted average.
7.9 Exercises
Use the flights data from nycflights13 for these exercises, and answer each with a pipe chain.
Busiest airport. Use
group_by()andsummarize()withn()to count the flights from each of the three New York airports. Then tryflights |> count(origin). What doescount()do?Most popular destinations. Which three destinations had the most flights from New York in 2013?
Delays by airport. For each
origin, calculate the mean and median departure delay. Which airport had the worst delays? Do the mean and the median agree?Arrivals. Calculate the mean arrival delay (
arr_delay) for each airline (carrier), and sort from worst to best. How many flights does each airline have? Would you trust the average for an airline with very few flights?Two groups. Group by
originandmonthand count the flights. Which airport was busiest in which month? Is the result still grouped?Counting NAs. Without using
drop_na(), count how many flights in each month have a missingdep_delay. (Hint:sum(is.na(dep_delay)).) Which month had the most cancelled flights?Fix the code. Each chain below has one mistake. Find and fix it.
flights |> summarize(mean(dep_delay)) # result is NA flights |> group_by(month) summarize(n = n()) flights |> summarize(n = n(month)) flights |> group_by(month) |> summarize(delay = mean(dep_delay, na.rm = TRUE) |> arrange(delay)
7.10 Further reading
- R for Data Science (2nd ed.) (Wickham et al. 2023), free at https://r4ds.hadley.nz. The “Data transformation” chapter covers
group_by()andsummarize()with the flights data, and the “Data tidying” chapter explains tidy data. - The tidyr website, https://tidyr.tidyverse.org, has the reference page for
drop_na(). - Posit’s Data transformation with dplyr cheatsheet, https://posit.co/resources/cheatsheets/, shows
summarize(),group_by(), and the summary functions under Summarize Cases and Group Cases.
summarize() calculates them.
Code that takes inputs (arguments), does a job, and returns an output,
such as mean(temps).
A value that is unknown, written NA (“not available”) in R.
Most calculations with NA give NA; test for it
with is.na().
An average in which some values count more than others. Total distance
divided by total air time weights each flight’s speed by its air time.
A dplyr function named for the job it does to a data frame, such as
filter(), select(), arrange(),
and mutate().
An R package of data on every flight that left New York City’s three
airports in 2013, including the flights data frame.
The operator |>, which passes the result on its left as
the first argument of the function on its right. Read it as “and then.”
The tidyverse package for transforming data frames: filtering rows,
choosing columns, adding new columns, joining tables, and summarizing.
Data arranged so that each variable is a column, each observation is a
row, and each value has its own cell.
In R, a named piece of memory that holds data, such as
months <- 12. In a dataset, a measured characteristic,
such as median income; each Census variable has a code.
One thing that was measured or recorded, such as a student, a county, or
a flight. In a data frame, each row is an observation.
A value counted once, however many times it repeats.
distinct() keeps one row for each unique value, or each
unique combination of values.
A bundle of code, written by other users, that adds new functions to R.
Install it once with install.packages(), then load it in
each session with library().
The value below which a given share of the values fall: a quarter of
values are below the 25th percentile. quantile() in R.
A measure of how spread out values are around their mean, in the same
units as the values. sd() in R.
A measure of spread: the standard deviation squared, so its units are
squared too. var() in R.
An input to a function, written inside its parentheses.
The average: the sum of the values divided by how many there are. A few
very large or small values can pull it a long way.
The middle value when values are sorted, so half are above and half
below. Unlike counts, medians can’t be added across areas.
Lopsided: most values are bunched at one end, with a long tail of
unusual values at the other, which pulls the mean away from the median.
A data frame made by group_by(). It looks the same, but
later verbs work on each group separately until you call
ungroup().
Largest to smallest for numbers, Z to A for text. Ask
arrange() for it with desc(), as in
arrange(desc(dep_delay)).
A document format (.Rmd) that mixes formatted text with
chunks of R code. Knitting it runs the code and builds a finished
report.
A vector of TRUE and FALSE values, such as the
result of vect > 10. Inside square brackets it keeps the
TRUE positions.
A one-page summary of a package or tool. Posit publishes them for dplyr,
ggplot2, RStudio, and more.