Group & Summarise

In Lesson 2 you learned the verbs that act on a table as a whole: filter, select, mutate, arrange. But Maya rarely wants the whole table. She wants answers about parts of it: how many of each loaf did I sell, what is the average revenue per item, which days were busy. That means splitting the table into groups, computing a number for each group, and stacking the results back together. This three-move pattern, plus labelling rows by a rule and dealing with the blanks that real data always has, is the last set of skills that turns a tidy table into an answer.

By the end of this lesson you will be able to:

• Collapse a column to one number with summarise(), and a column per group with group_by()
• Tag every row with a category using case_when()
• Handle missing values honestly: na.rm, dropping the rows, or filling them in

Prerequisites: Lesson 2, so you know the dplyr verbs and the pipe. We define everything new as it appears.

summarise() turns a whole column into one number

Here is Maya's week off the till: eight sales over four days. It is the same tidy table from Lesson 2, now with the last two days added. Each lesson starts in a fresh R session, so we build it right here (run this once). Look at the final row: the revenue is NA, because Maya forgot to log that Bagel sale. Hold that thought; we deal with it at the end of the lesson.

Every verb so far returned a table with many rows. summarise() is the first verb that gives you back one row. You hand it a name and a function that reduces a whole column to a single value, and that value is all you get back:

sum(units) adds all eight counts into one total (209 loaves), and mean(units) averages them. The arithmetic mean is worth stating exactly, because it returns in a moment. For a column of values \(x_1, x_2, \dots, x_n\), where \(x_i\) is the units sold in sale \(i\) and \(n\) is the number of sales,

\[ \bar{x} = \frac{1}{n}\sum_{i=1}^{n} x_i \]

so \(\bar{x} = 209 / 8 = 26.1\). The whole point of summarise() is that many rows go in, one row comes out: the eight-row tibble above became a single summary row. In the next step you will see that collapse happen group by group.

group_by() makes summarise() work per category

One grand total is rarely the answer. Maya does not want "26.1 loaves on average" across everything; she wants it per item, so she can see that Bagels move in big batches and Croissants trickle. That is where group_by() comes in. On its own it changes nothing you can see; it quietly tags the table so the next verb runs once per group instead of once for the whole table.

Read it as the three moves from the cover, the pattern statisticians call split-apply-combine: group_by(item) splits the eight rows into three piles (Bagel, Croissant, Sourdough); summarise() applies its functions to each pile; and the results combine into one tidy row per group. The new verb here is n(), which takes no arguments and just counts the rows in the current group, so Maya sees she rang up three Bagel sales and two Croissant sales.

The eight detail rows did not get edited; they collapsed. Three piles in, three summary rows out, one for each value of item.

How many rows come back?

Maya has 8 sales across 3 items. She runs:

How many rows does the result have?

Average loaves per item

Maya wants the average units per item, not the total. The pipe already groups by item; finish the summarise() so mean_units holds the mean of units for each group. Fill in the blank.

sales %>%
  group_by(item) %>%
  summarise(mean_units = mean(units))

case_when() sorts rows into categories

Summaries answer "how much per group". The other everyday job is the opposite: tag each individual row with a label you can group or filter by later. Maya wants to call each sale quiet, steady or busy by how many loaves it moved, on this scale:

You could nest if statements, but dplyr has a clean verb for it: case_when(), used inside mutate() to build a new column. It is a list of condition ~ value rules, where the ~ reads as "becomes": when the condition on its left is TRUE, the row gets the label on its right. It checks each row against the rules top to bottom and stops at the first one that is TRUE, handing that row the matching label. The final TRUE ~ ... is a catch-all that fires when nothing above matched.

For the 44-unit Bagel sale: is 44 >= 35? Yes, so it is labelled "busy" and the lower rules are never checked. For the 22-unit Sourdough: 22 >= 35 is FALSE, 22 >= 20 is TRUE, so "steady". For the 12-unit Croissant: both fail, the TRUE catch-all gives "quiet". The rule runs once per row, so a whole volume column appears.

Order matters in case_when

Maya rewrites the rules with the broad one first:

case_when(
  units >= 20 ~ "steady",
  units >= 35 ~ "busy",
  TRUE        ~ "quiet"
)

What happens to the 44-unit Bagel sale now?

One missing value poisons a summary

Now back to that NA. Maya forgot to log the revenue on the 44-unit Bagel sale, so one cell of the revenue column is blank, which R writes as NA ("not available"). Watch what it does to a per-item average:

The printout makes the damage plain: the Bagel average alone comes back NA, while the two complete groups are fine. This is not a bug; it is R being careful. NA means "a value exists but I do not know it", so any arithmetic touching it is also unknown: 60 + 57 + NA could be anything, so it is NA, and therefore the mean is NA too. In symbols, if any one \(x_i\) is NA then the sum \(\sum_i x_i\) is NA, so \(\bar{x}\) is NA. R refuses to silently guess.

The fix is to tell the summary function to skip the missing values with the argument na.rm = TRUE (read it as "NA, remove"):

Now the Bagel mean is (60 + 57) / 2 = 58.5: the NA row is dropped from this one calculation and the average is taken over the two values that remain. Note that n() would still count all three Bagel sales; na.rm only changes what sum() and mean() add up, not the data.

na.rm is not the only choice

na.rm = TRUE is a local fix: it ignores the blank for one calculation and changes nothing in the table itself. Often that is exactly right. But sometimes you want to deal with the missing rows once, up front, and there are two honest ways to do it.

Drop the rows when a missing value means the observation is unusable. drop_na() removes any row with an NA in the named columns; plain filter() does the same with a condition:

The widget shows that drop: the one unlogged Bagel sale falls away, seven rows remain.

Fill the gap (impute) when you have a defensible value to use, such as 0 or a typical amount. replace_na() and coalesce() both do this:

Rescue the Bagel average

This grouped summary returns NA for Bagels because of the one unlogged sale. Add the argument that tells mean() to skip missing values, so Maya gets a real number for every item. Fill in the blank.

sales %>%
  group_by(item) %>%
  summarise(mean_revenue = mean(revenue, na.rm = TRUE))

References

A few authoritative places to take this further:

• R for Data Science (2e), Data transformation - the canonical, free chapter, including the groups section on group_by() and summarise().
• dplyr: Grouped data vignette - the package authors on how group_by() reshapes what every later verb does.
• dplyr: case_when() reference - the full rules for the condition ladder, including .default and common pitfalls.
• R for Data Science (2e), Missing values - a clear-eyed chapter on explicit and implicit NAs and how to handle them.

Lesson 3 complete, and the course with it

You can now turn a tidy table into an answer. You collapsed columns with summarise(), made it work per category with group_by() (the split-apply-combine pattern), labelled rows with case_when(), and handled missing values honestly with na.rm, dropping or imputing, always saying which.

That closes Data Wrangling with dplyr. Across three lessons you went from a raw CSV to tidy data, learned the one-table verbs and the pipe, and now grouping, summarising and cleaning. From here, two natural next courses: Joining and reshaping (combining several tables and pivoting between long and wide), and Exploratory Data Analysis (using exactly these skills to interrogate a new dataset). Maya is ready to run her bakery on her numbers, and so are you.