7 Summarize Forestry Data Overall and by Group

Data for this chapter

This chapter uses the BC air-quality file (airdata3.csv). Click the file name to download it directly, then save it in your data/ folder. For the source and details, see the Datasets Used in This Book page.

Chapter goals

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

Write an overall summary of one or more columns with summarise() — counts, means, medians, standard deviations, ranges.
Write a by-group summary by adding group_by() before summarise().
Group by multiple columns at once (e.g., region × day) to build cross-tabulations.
Use the shortcut functions count(), tally(), and n_distinct() for common patterns.
Reshape a summary with pivot_wider() so each group level becomes a column — the closest R has to a PivotTable.
Recognize that a group_by() |> summarise() pipeline is the conceptual equivalent of an Excel PivotTable from Chapter 3 — and produce identical numbers.
Use across() to apply the same summary to many columns at once.
Write summary tables that read cleanly in a rendered Quarto report.

Expected output for this chapter

What you will hand in

A rendered Quarto HTML report (frst232_ch07_learner.html) containing:
- an overall summary of O₃ across the whole cleaned air-quality file,
- a by-station summary table (one row per station),
- a by-region × by-day summary table reshaped with pivot_wider() so each region is a column,
- one short prose paragraph interpreting which station and which day had the highest mean O₃.
The corresponding .qmd source file.
A short group-lab worksheet (or screenshot) submitted to the course site.

This list is the same as the rubric for the chapter assignment. Keep it in view while you work.

7.1 Where we are

In Chapter 6 you cleaned airdata3.csv with the six dplyr verbs. The cleaned tibble (airdata_clean) is the starting point for this chapter. Chapter 7 picks up where Chapter 6 leaves off: now that the data is clean, how do you turn it into numbers your reader can use?

Chapter 6 introduced group_by() |> summarise() briefly. This chapter is that pattern at full depth.

7.2 Excel-to-R bridge — summarising

What you did in Excel	What you do in R
`=AVERAGE(C2:C743)` over a column	`summarise(mean = mean(O3, na.rm = TRUE))`
`=MEDIAN(C2:C743)`	`summarise(median = median(O3, na.rm = TRUE))`
`=COUNT(C2:C743)`	`summarise(n_obs = n())`
`=SUMIF(loc, "Burnaby", o3)`	`filter(location == "Burnaby") \|> summarise(sum = sum(O3))`
`=AVERAGEIF(loc, "Burnaby", o3)`	`filter(...) \|> summarise(mean = mean(O3))`
`=SUMIFS(...)` multiple criteria	`filter(...) \|> summarise(...)` with multiple conditions
PivotTable (rows = station)	`group_by(location) \|> summarise(...)`
PivotTable (rows × columns)	`group_by(region, day) \|> summarise(...) \|> pivot_wider(...)`
PivotTable count by category	`count(category)`
PivotTable refresh after data change	Re-render the document

The right-hand column is the rest of this chapter. Every Excel summary move from Chapters 2 and 3 has a one-line dplyr equivalent.

7.3 The starting point — the cleaned tibble

This chapter continues directly from Chapter 6. Before you run anything else, run the setup block below. It loads the packages this chapter needs — including knitr (for kable()) and scales, which library(tidyverse) does not load — and rebuilds the cleaned, region-joined airdata_clean tibble from the Chapter 6 pipeline. If you skip it, later code fails with “could not find function” or fills the region column with NA.

library(tidyverse)
library(lubridate)
library(here)
library(knitr)
library(scales)

# Rebuild the cleaned tibble from Chapter 6 --------------------------------
airdata_raw <- read_csv(here("data", "airdata3.csv"),
                        show_col_types = FALSE)

# Station-to-region lookup (same table used in Chapter 6)
station_regions <- tribble(
  ~location,                          ~region,
  "Burnaby South",                    "Burrard Peninsula",
  "Coquitlam Douglas College",        "North-East Sector",
  "Langley Central",                  "Fraser Valley West",
  "Maple Ridge Golden Ears School",   "North-East Sector",
  "New Westminster Sapperton Park",   "Burrard Peninsula",
  "North Delta",                      "South of Fraser",
  "North Vancouver Second Narrows",   "North Shore",
  "Pitt Meadows Airport",             "North-East Sector",
  "Port Coquitlam North",             "North-East Sector",
  "Port Moody Rocky Point Park",      "North-East Sector",
  "Richmond South",                   "South of Fraser",
  "Tsawwassen",                       "South of Fraser",
  "Vancouver Clark Drive",            "Vancouver",
  "Vancouver International Airport #2","South of Fraser",
  "Vancouver Kitsilano",              "Vancouver",
  "West Vancouver Lions Gate",        "North Shore"
)

airdata_clean <- airdata_raw |>
  select(-starts_with("Unnamed"), -any_of("...1")) |>
  select(where(\(col) !all(is.na(col)))) |>
  mutate(date_parsed = mdy(Date),
         year = year(date_parsed),
         month = month(date_parsed),
         day = day(date_parsed)) |>
  filter(!is.na(O3)) |>
  mutate(o3_band = case_when(
    O3 < 10  ~ "low",
    O3 < 20  ~ "moderate",
    O3 < 30  ~ "high",
    TRUE       ~ "very high"
  )) |>
  left_join(station_regions, by = "location")

Confirm it is in memory:

glimpse(airdata_clean)

Rows: 5,677
Columns: 17
$ PM10        <dbl> 13, 6, 2, 2, 4, 2, 4, 6, 6, 8, 7, 4, 3, 3, 6, 6, 6, 5, 8, …
$ SO2         <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
$ CO          <dbl> 0.5, 0.4, 0.4, 0.3, 0.3, 0.3, 0.3, 0.4, 0.4, 0.4, 0.4, 0.4…
$ NO          <dbl> 10, 4, 2, 1, 1, 1, 1, 1, 2, 5, 4, 3, 3, 3, 3, 2, 3, 2, 5, …
$ NO2         <dbl> 22, 21, 19, 8, 9, 9, 12, 19, 20, 23, 17, 10, 9, 8, 8, 10, …
$ PM25        <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ O3          <dbl> 1, 3, 18, 32, 31, 32, 28, 19, 17, 13, 15, 20, 19, 21, 21, …
$ location    <chr> "Burnaby South", "Burnaby South", "Burnaby South", "Burnab…
$ Date        <chr> "1/1/2000", "1/1/2000", "1/1/2000", "1/1/2000", "1/1/2000"…
$ Time        <time> 01:00:00, 02:00:00, 03:00:00, 04:00:00, 05:00:00, 06:00:0…
$ TRS         <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ date_parsed <date> 2000-01-01, 2000-01-01, 2000-01-01, 2000-01-01, 2000-01-0…
$ year        <dbl> 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000…
$ month       <dbl> 1, 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, 1…
$ o3_band     <chr> "low", "low", "moderate", "very high", "very high", "very …
$ region      <chr> "Burrard Peninsula", "Burrard Peninsula", "Burrard Peninsu…

5677 rows × 17 columns, all with a measured O₃ value, parsed dates, a O₃ band, and a region.

7.4 Overall summary — one row

The simplest case: collapse the whole tibble to one row of summary statistics:

airdata_clean |>
  summarise(
    n_obs       = n(),
    mean_o3   = mean(O3, na.rm = TRUE),
    median_o3 = median(O3, na.rm = TRUE),
    sd_o3     = sd(O3, na.rm = TRUE),
    min_o3    = min(O3, na.rm = TRUE),
    max_o3    = max(O3, na.rm = TRUE)
  )

# A tibble: 1 × 6
  n_obs mean_o3 median_o3 sd_o3 min_o3 max_o3
  <int>   <dbl>     <dbl> <dbl>  <dbl>  <dbl>
1  5677    10.3         6  10.8      0     39

Every column in the result is a single number. The arguments to summarise() are named: <name> = <expression>.

7.4.1 The functions you will use most

Function	Returns	Notes
`n()`	row count	No arguments. Only inside `summarise()` / `mutate()`.
`sum()`	total	Add `na.rm = TRUE` if missing values are possible.
`mean()`	arithmetic mean	Same.
`median()`	middle value	Robust to outliers.
`min()` / `max()`	extremes	Sensitive to outliers.
`sd()`	standard deviation	Spread around the mean.
`n_distinct()`	unique values	Useful as `n_distinct(location)`.
`quantile()`	any percentile	`quantile(O3, 0.9)` for the 90th.
`IQR()`	interquartile range	Robust spread.

mutate() vs summarise() — don’t mix them up

Chapter 6 used mutate() to add or change columns; this chapter uses summarise() to collapse rows into summary values.

Function	What it does	Rows out
`mutate()`	Adds or changes a column	Same number of rows
`summarise()`	Computes summary values	Fewer rows — one overall, or one per group

7.5 By-group summary — one row per group

AI prompt to check your work

Use this after your own attempt. It should check your reasoning, not hand you the answer:

“I wrote df |> group_by([group]) |> summarise([stat]). Check whether group_by() + summarise() is the efficient choice here, whether I should report n() beside each summary, and how to confirm the group sizes are plausible. Do not write the final pipeline for me.”

Add group_by() before summarise():

station_summary <- airdata_clean |>
  group_by(location) |>
  summarise(
    n_obs       = n(),
    mean_o3   = mean(O3, na.rm = TRUE),
    median_o3 = median(O3, na.rm = TRUE),
    max_o3    = max(O3, na.rm = TRUE),
    .groups = "drop"
  ) |>
  arrange(desc(mean_o3))

station_summary

# A tibble: 8 × 5
  location                           n_obs mean_o3 median_o3 max_o3
  <chr>                              <int>   <dbl>     <dbl>  <dbl>
1 Langley Central                      732   17.7         19     38
2 Maple Ridge Golden Ears School       564   11.3         10     35
3 Richmond South                       732   10.5          5     37
4 North Delta                          731   10.3          6     37
5 Vancouver International Airport #2   727   10.2          4     37
6 Burnaby South                        732    9.05         5     35
7 Vancouver Kitsilano                  731    7.49         2     37
8 Port Moody Rocky Point Park          728    6.13         2     39

This is the dplyr equivalent of an Excel PivotTable with location in Rows and Average of O3 in Values. Same operation. Same answer.

Always add .groups = "drop" (or your code will warn)

dplyr keeps the grouping after summarise() by default for backward compatibility, which causes confusing behaviour in downstream pipelines. Explicitly say .groups = "drop" to clear the grouping after summarise. Treat it as a habit, not a choice.

7.5.1 Multi-key grouping

Group by two or more variables at once. Each unique combination becomes one row:

region_day_summary <- airdata_clean |>
  group_by(region, day) |>
  summarise(
    mean_o3 = mean(O3, na.rm = TRUE),
    .groups = "drop"
  )

region_day_summary |> head(10)

# A tibble: 10 × 3
   region              day mean_o3
   <chr>             <int>   <dbl>
 1 Burrard Peninsula     1   17.2 
 2 Burrard Peninsula     2   16.1 
 3 Burrard Peninsula     3   14.1 
 4 Burrard Peninsula     4   17.4 
 5 Burrard Peninsula     5   12.6 
 6 Burrard Peninsula     6    5.39
 7 Burrard Peninsula     7    4.21
 8 Burrard Peninsula     8   22.2 
 9 Burrard Peninsula     9   23.7 
10 Burrard Peninsula    10    6.59

The result is in long format: one row per (region, day) combination. Useful for plotting (Chapter 9) but hard to scan.

7.6 Reshaping with `pivot_wider()` — the PivotTable equivalent

For human-readable cross-tabulations, reshape the long-format summary into a wide table:

region_day_summary |>
  pivot_wider(
    names_from  = region,
    values_from = mean_o3
  ) |>
  arrange(day)

# A tibble: 31 × 6
     day `Burrard Peninsula` `Fraser Valley West` `North-East Sector`
   <int>               <dbl>                <dbl>               <dbl>
 1     1               17.2                 24.6                15.1 
 2     2               16.1                 21.6                11.9 
 3     3               14.1                 15.7                 9.44
 4     4               17.4                 30.7                18.5 
 5     5               12.6                 18.7                 3.51
 6     6                5.39                 8.96                9.15
 7     7                4.21                15.3                 5.40
 8     8               22.2                 30.8                21.6 
 9     9               23.7                 31.5                21.9 
10    10                6.59                19.1                10.4 
# ℹ 21 more rows
# ℹ 2 more variables: `South of Fraser` <dbl>, Vancouver <dbl>

What we just built is a true cross-tab: one row per day, one column per region, values = mean O₃. This is what an Excel PivotTable produces when you put day in Rows, region in Columns, and Average of O3 in Values.

7.6.1 `pivot_wider()` arguments

Argument	What it does
`names_from`	the column whose values become new column names
`values_from`	the column whose values fill the cells
`values_fill`	what to put in cells where no data exists (default: NA; often `0` for counts)

When to use long vs wide

Long format (one row per combination) is what summarise() and ggplot2 prefer. Use it for analysis.
Wide format (one column per group) is what humans prefer to read. Use it for tables in a report.

Convert between them with pivot_longer() and pivot_wider(). The two are inverses.

7.7 Shortcut: `count()` and `tally()`

The two most common summarise patterns:

airdata_clean |> count(region, sort = TRUE)

# A tibble: 5 × 2
  region                 n
  <chr>              <int>
1 South of Fraser     2190
2 North-East Sector   1292
3 Burrard Peninsula    732
4 Fraser Valley West   732
5 Vancouver            731

airdata_clean |> count(region, o3_band)

# A tibble: 20 × 3
   region             o3_band       n
   <chr>              <chr>     <int>
 1 Burrard Peninsula  high        110
 2 Burrard Peninsula  low         459
 3 Burrard Peninsula  moderate    136
 4 Burrard Peninsula  very high    27
 5 Fraser Valley West high        248
 6 Fraser Valley West low         189
 7 Fraser Valley West moderate    194
 8 Fraser Valley West very high   101
 9 North-East Sector  high        170
10 North-East Sector  low         845
11 North-East Sector  moderate    232
12 North-East Sector  very high    45
13 South of Fraser    high        301
14 South of Fraser    low        1301
15 South of Fraser    moderate    373
16 South of Fraser    very high   215
17 Vancouver          high         76
18 Vancouver          low         533
19 Vancouver          moderate     78
20 Vancouver          very high    44

count(x) is exactly group_by(x) |> summarise(n = n()). Use it for tallies.

count(x, y) cross-tabulates. To pivot it wide:

airdata_clean |>
  count(region, o3_band) |>
  pivot_wider(names_from = o3_band, values_from = n, values_fill = 0)

# A tibble: 5 × 5
  region              high   low moderate `very high`
  <chr>              <int> <int>    <int>       <int>
1 Burrard Peninsula    110   459      136          27
2 Fraser Valley West   248   189      194         101
3 North-East Sector    170   845      232          45
4 South of Fraser      301  1301      373         215
5 Vancouver             76   533       78          44

That’s a complete cross-tab in three lines. The Excel equivalent was a PivotTable with region in Rows, o3_band in Columns, Count of n in Values, plus the “Show items with no data” toggle to fill empty cells with 0. Three clicks in Excel; three lines in R; same answer.

7.8 Counting unique values with `n_distinct()`

airdata_clean |>
  summarise(
    n_stations = n_distinct(location),
    n_regions  = n_distinct(region),
    n_days     = n_distinct(day)
  )

# A tibble: 1 × 3
  n_stations n_regions n_days
       <int>     <int>  <int>
1          8         5     31

Useful when you want “how many unique stations report O₃?” in one line.

To see the actual unique values — not just how many — use unique() (handy when inspecting a column in Chapter 5 too):

unique(airdata_clean$region)

[1] "Burrard Peninsula"  "Fraser Valley West" "North-East Sector" 
[4] "South of Fraser"    "Vancouver"

7.9 Multiple statistics with `across()`

When you want the same summary (e.g., mean) on several columns, across() is the cleaner pattern:

airdata_clean |>
  group_by(region) |>
  summarise(
    across(c(O3, PM10, NO2),
           \(x) mean(x, na.rm = TRUE)),
    .groups = "drop"
  )

# A tibble: 5 × 4
  region                O3  PM10   NO2
  <chr>              <dbl> <dbl> <dbl>
1 Burrard Peninsula   9.05 10.6   26.0
2 Fraser Valley West 17.7   8.15  10.2
3 North-East Sector   8.40 10.9   18.3
4 South of Fraser    10.4  12.4   23.4
5 Vancouver           7.49 12.0   29.8

This summarises three pollutant columns at once, one mean per column per region. Far less repetitive than writing three mean(...) calls manually.

7.9.1 Multiple statistics on the same column

You can also use across() to apply multiple statistics to one column:

airdata_clean |>
  group_by(region) |>
  summarise(
    across(O3,
           list(mean = \(x) mean(x, na.rm = TRUE),
                sd   = \(x) sd(x, na.rm = TRUE),
                max  = \(x) max(x, na.rm = TRUE))),
    .groups = "drop"
  )

# A tibble: 5 × 4
  region             O3_mean O3_sd O3_max
  <chr>                <dbl> <dbl>  <dbl>
1 Burrard Peninsula     9.05  9.54     35
2 Fraser Valley West   17.7  10.3      38
3 North-East Sector     8.40  9.50     39
4 South of Fraser      10.4  11.3      37
5 Vancouver             7.49 10.0      37

The result has columns O3_mean, O3_sd, O3_max.

across() is advanced — use it when it helps

For two or three columns, plain summarise(mean1 = mean(x), mean2 = mean(y), ...) is more readable. For five or more, across() shines. Use whichever is clearer for your reader.

7.10 Putting it together — a worked summary report

Here is what a complete summary section of a forestry data report might look like.

7.10.1 Overall

overall <- airdata_clean |>
  summarise(
    n_obs       = n(),
    n_stations  = n_distinct(location),
    n_regions   = n_distinct(region),
    mean_o3   = round(mean(O3, na.rm = TRUE), 1),
    median_o3 = round(median(O3, na.rm = TRUE), 1),
    max_o3    = round(max(O3, na.rm = TRUE), 1)
  )
overall |> kable()

n_obs	n_stations	n_regions	mean_o3	median_o3	max_o3
5677	8	5	10.3	6	39

7.10.2 By station

airdata_clean |>
  group_by(location) |>
  summarise(
    n_obs     = n(),
    mean_o3 = round(mean(O3, na.rm = TRUE), 1),
    max_o3  = max(O3, na.rm = TRUE),
    .groups = "drop"
  ) |>
  arrange(desc(mean_o3)) |>
  kable()

location	n_obs	mean_o3	max_o3
Langley Central	732	17.7	38
Maple Ridge Golden Ears School	564	11.3	35
Richmond South	732	10.5	37
North Delta	731	10.3	37
Vancouver International Airport #2	727	10.2	37
Burnaby South	732	9.1	35
Vancouver Kitsilano	731	7.5	37
Port Moody Rocky Point Park	728	6.1	39

7.10.3 By region × day (wide)

airdata_clean |>
  group_by(region, day) |>
  summarise(mean_o3 = round(mean(O3, na.rm = TRUE), 1),
            .groups = "drop") |>
  pivot_wider(names_from = region, values_from = mean_o3,
              values_fill = NA) |>
  arrange(day) |>
  head(10) |>
  kable()

day	Burrard Peninsula	Fraser Valley West	North-East Sector	South of Fraser	Vancouver
1	17.2	24.6	15.1	18.9	15.0
2	16.1	21.6	11.9	19.9	19.2
3	14.1	15.7	9.4	11.3	5.1
4	17.4	30.7	18.5	24.4	17.2
5	12.6	18.7	3.5	16.1	17.8
6	5.4	9.0	9.1	2.4	2.5
7	4.2	15.3	5.4	5.7	1.4
8	22.2	30.8	21.6	27.7	22.1
9	23.7	31.5	21.9	28.3	19.7
10	6.6	19.1	10.4	7.4	2.5

7.10.4 By O₃ band — count cross-tab

airdata_clean |>
  count(region, o3_band) |>
  pivot_wider(names_from = o3_band, values_from = n, values_fill = 0) |>
  kable()

region	high	low	moderate	very high
Burrard Peninsula	110	459	136	27
Fraser Valley West	248	189	194	101
North-East Sector	170	845	232	45
South of Fraser	301	1301	373	215
Vancouver	76	533	78	44

That entire summary section — four tables — is fifteen lines of dplyr code and renders to a polished HTML report. The same output in Excel would be three or four PivotTables, each requiring a manual refresh after every data update. The dplyr version updates automatically when you re-render.

7.11 Active learning

7.11.1 Activity 1 — Overall summary

Write a single summarise() call that returns the count, mean, median, sd, min, and max of O₃ across the whole cleaned tibble. Compare with the table in the chapter.

7.11.2 Activity 2 — By-station ranking

Group by location, summarise mean_o3, and arrange descending. Which station has the highest mean O₃? Which has the lowest?

7.11.3 Activity 3 — `count()` shortcut

Use count(region) and count(region, sort = TRUE). What is the difference?

7.11.4 Activity 4 — Cross-tabulation

Build a cross-tab where rows are region, columns are o3_band, and cells are counts. Use pivot_wider() with values_fill = 0 so empty cells show 0 instead of NA.

7.11.5 Activity 5 — `across()` for many means

Use across(c(O3, PM10, NO2), \(x) mean(x, na.rm = TRUE)) inside a group_by(region) |> summarise(...) pipeline. How does the output differ from writing three separate mean calls?

7.11.6 Activity 6 — Interpret one summary

Pick any one row of your by-group summary and write one sentence that names the group, the statistic, the value, and the unit — e.g. “The Fraser Valley West region had the highest mean O₃, at about 39 ppb.” A number is only useful once you can say what it means.

7.12 Practice Demo Lab

Practice demo only

This is a guided practice lab in the OER book, designed to help you learn the workflow before completing the official lab assignment on Canvas. The Canvas lab is the graded assignment. Use this activity to practice, compare your work with the reference solution, and learn how to write an AI verification prompt.

This demo lab has six parts — work them in order:

Your attempt. Work through the tasks below.
Reference solution. Compare against the worked examples earlier in this chapter (your public reference). The graded Canvas lab has its own private answer key — do not copy demo solutions into a Canvas submission.
Compare your work with the reference. Where did it match? Where did it differ? What caused the difference, and how did you fix it?
Write your own AI verification prompt. Ask an AI to check your reasoning, code, and outputs — not to produce the answer for you.
Model AI verification prompt.

“I am doing a FRST 232 practice demo lab. My dataset is [file name] (columns [columns]). I attempted [paste your steps or code] and got [paste the output]. Explain what my work does line by line, tell me whether it answers the task, and list the checks I can run to verify it against the chapter’s worked example. Do not give me the final answer.”
Reflection: what changed after checking? In two or three sentences — did your attempt hold up? what did you fix? what will you check first next time?

Lab: Produce a summary report together

Work in groups of 3 or 4. Each member should already have airdata_clean in memory (from Chapter 6, or by re-running the Chapter 6 pipeline at the top of their frst232_ch07_learner.qmd setup chunk).

Task 1 — Agree on the question. As a group, pick one forestry question your summary report will answer:

Which BC region had the highest mean O₃ in January 2000?
Which day in January 2000 was the worst air-quality day, on average, across all stations?
Which monitoring station had the most “very high” O₃ hours?

Task 2 — Build the summary. Write a single dplyr pipeline that answers your question. Use group_by(), summarise(), and arrange() as needed.

Task 3 — Reshape if useful. If your summary has two grouping variables, use pivot_wider() to produce a cross-tab readable in a single screen. If it has one grouping variable, sort it.

Task 4 — Caption it. Write a one-sentence caption above your table. The caption should state the headline finding (“Region X had the highest mean O₃…”).

Task 5 — Submit. Submit: - the dplyr pipeline (in a Quarto chunk), - the rendered table, - the one-sentence caption, - all group members’ names.

This lab fits comfortably inside a single hands-on session.

Reference solution — download

Open the Chapter 7 reference solution (rendered HTML) — the completed, rendered solution for this demo lab. Open it after your own attempt and use it to check your work.

This is the public practice solution. The graded lab on Canvas has its own private answer key — do not copy this into a Canvas submission.

7.13 Exercises

These are the take-home exercises for this chapter.

Overall summary. Write a single summarise() call that returns n_obs, n_stations, mean_o3, median_o3, sd_o3, and max_o3 for the cleaned air-quality data.
By region. Group by region, summarise mean O₃, arrange descending. Which region has the highest mean? Does the result make sense given Metro Vancouver geography?
By day. Group by day (the day-of-month integer), then summarise n_obs and mean_o3 per day. Which day in January 2000 had the highest provincial mean O₃?
Cross-tabulation. Build the region × o3_band cross-tab from Activity 4. Use pivot_wider() with values_fill = 0. Submit the rendered table.
Top station per region. Group by region, then within each region find the station with the highest mean O₃. Hint: group_by(region) |> slice_max(mean_o3, n = 1) after you have already computed per-station means.
across() for multiple pollutants. Group by region and compute the mean of every pollutant column (O3, PM10, O3, NO2, SO2, CO). Use across().
PivotTable parity. Save your cleaned tibble to outputs/airdata3_clean.csv. Open it in Excel. Build a PivotTable with region in Rows, o3_band in Columns, Count of O3 in Values. Confirm the numbers match your dplyr cross-tab to the last unit.
Optional, harder. Compute the 90th percentile of O₃ per station: summarise(p90 = quantile(O3, 0.9, na.rm = TRUE)). Which station has the highest 90th-percentile O₃?

7.14 Optional, advanced

7.14.1 `slice_max()` and `slice_min()` — top-N per group

airdata_clean |>
  group_by(location) |>
  slice_max(O3, n = 3, with_ties = FALSE)

Returns the three highest-O₃ hours at each station.

7.14.2 `summarise()` with custom functions

You can define a helper function and call it inside summarise():

robust_mean <- function(x) mean(x, na.rm = TRUE, trim = 0.05)

airdata_clean |>
  group_by(region) |>
  summarise(rmean_o3 = robust_mean(O3), .groups = "drop")

The 5% trimmed mean drops the top 5% and bottom 5% before averaging — more robust to outliers than a plain mean.

7.14.3 `summary()` of a tibble vs `summarise()` of a tibble

These two are easily confused:

Function	Returns
`summary(tibble)`	A base-R text dump: five-number summary per column
`summarise(tibble, ...)`	A new tibble with the columns you asked for

Use summary() for quick interactive inspection. Use summarise() for output that goes into a report.

7.15 A glimpse ahead: From summarising to reshaping and combining

Chapter 7 used pivot_wider() for the first time. In Chapter 8 you will meet:

pivot_longer() — the inverse of pivot_wider(). Useful when data arrives in wide format but you need long format for analysis.
inner_join(), right_join(), full_join() — the other three join types, alongside left_join() from Chapter 6.
bind_rows() and bind_cols() for stacking tibbles.
The principles of tidy data — why one row per observation and one column per variable matter so much.

The verbs in Chapter 8 are the last dplyr verbs you need for this course. After Chapter 8, every cleaning, summarising, reshaping, and joining task you face has a one-line dplyr answer.

7.16 Self-assessment quiz

Click the answer you think is correct. The right answer turns green; wrong answers turn red and the correct one is revealed. Explanations appear below each question.

1. summarise() with no group_by() before it returns —

One row per row of the original tibble
One row of overall summary statistics
An error
A chart

summarise() collapses the tibble to one row. With group_by() first, it collapses to one row per group.

2. group_by(location) |> summarise(mean = mean(O3, na.rm = TRUE)) returns —

One row total
One row per unique location
A chart
An error

Each unique value of the grouping column becomes one row of the summary.

3. Inside summarise(), what does n() return?

The number of columns
The row count of the group (or the whole tibble if ungrouped)
The number of NAs
The sum of the values

n() is a special dplyr function — no arguments, only used inside summarise() or mutate(). Returns the row count of the current group.

4. count(region, o3_band) is equivalent to —

filter(region == ..., o3_band == ...)
group_by(region, o3_band) |> summarise(n = n())
select(region, o3_band)
arrange(region, o3_band)

count(...) is the dplyr shortcut for group-then-tally. Multiple grouping variables produce a row per combination.

5. pivot_wider(names_from = region, values_from = mean_o3) does what?

Drops the region column
Turns each unique value of region into a new column, with mean_o3 values inside
Sorts by region
Counts the regions

This produces a cross-tab. The original long-format summary becomes wide-format with one column per region.

6. The closest Excel equivalent of group_by(region, day) |> summarise(mean(O3)) |> pivot_wider(...) is —

A bar chart
An AutoFilter
A PivotTable with day in Rows, region in Columns, and Average of O3 in Values
A VLOOKUP

The dplyr summarise + pivot_wider pipeline produces exactly what a PivotTable produces — same data, same answer, scriptable instead of clickable.

7. summarise(p90 = quantile(O3, 0.9, na.rm = TRUE)) returns —

The 9th percentile
The 90th percentile of O3 (the value below which 90% of observations fall)
A 90-row summary
An error

quantile(x, p) returns the value at the p-th percentile of x. 0.9 is the 90th percentile.

8. n_distinct(location) returns —

The total row count
The number of unique values in the location column
The first location
An error

n_distinct(x) counts unique values. Useful for "how many unique stations are in this dataset?".

9. .groups = "drop" in summarise() —

Drops the grouped columns
Drops the rows
Clears the grouping after summarise, so downstream operations are not grouped
Is mandatory or the code errors

dplyr keeps the grouping by default after summarise (for backward compatibility). .groups = "drop" clears it, which is usually what you want. Treat it as a habit.

10. pivot_wider(values_fill = 0) means —

Fill the entire table with zeros
Where no observation existed for that combination, put 0 instead of NA
Multiply everything by zero
Round to zero decimals

When pivoting count data wider, cells with no matching observations default to NA. values_fill = 0 replaces those NAs with 0, which is usually what you want for counts.

11. across(c(O3, PM10, NO2), \(x) mean(x, na.rm = TRUE)) inside summarise() —

Drops three columns
Applies the mean function to O3, PM10, and NO2 — one column per pollutant in the output
Pivots the table
Counts the three columns

across() applies the same function to multiple columns at once. Saves writing three separate mean(...) calls.

12. filter(region == "Burrard Peninsula") |> summarise(mean = mean(O3, na.rm = TRUE)) is the dplyr equivalent of which Excel formula?

=AVERAGE(O3)
=AVERAGEIF(region, "Burrard Peninsula", O3)
=SUMIFS(...)
=VLOOKUP(...)

AVERAGEIF averages one column subject to a criterion. In dplyr: filter to the criterion, then summarise the mean.

13. A by-station summary returns 6 rows in the result. The cleaned tibble has 742 rows. That means —

736 rows were lost
There is a bug
There are 6 unique stations with measured O₃; the 742 rows were collapsed by group
The summary is wrong

Summarise collapses the rows of each group into one row. 742 rows distributed across 6 stations → 6 summary rows. Nothing was "lost".

14. If you forget na.rm = TRUE in summarise(mean = mean(O3)) and the O3 column has any NA values, what happens?

R uses 0 for missing values
The mean returns NA
R skips the NAs silently
An error is thrown

R refuses to silently ignore NAs by default. Always add na.rm = TRUE to aggregations on columns that might contain NAs.

15. The result of summarise() is —

A vector
A new tibble (smaller than the input)
A list
A chart

summarise() returns a tibble. This is why you can pipe its output into another verb, like arrange() or pivot_wider().

16. To get the standard deviation in dplyr, you use —

stdev()
sd()
stddev()
STDEV()

sd() is the base R standard-deviation function. Like all aggregations, add na.rm = TRUE if needed.

17. Which is the closer R analogue of an Excel PivotTable refresh after editing the underlying Data sheet?

Manually clicking each summarise() call
Re-rendering the Quarto document, which re-runs every chunk in order
There is no analogue
install.packages()

Re-rendering re-runs the cleaning and the summarising. Numbers in the report are always in sync with the current data. No PivotTable refresh bug.

18. The dplyr pipeline data |> count(category) |> pivot_wider(names_from = category, values_from = n, values_fill = 0) produces —

A bar chart
A long-format tibble
A one-row wide tibble with one column per category, holding the count of each
An error

Count produces a long-format table (one row per category). pivot_wider turns it into a single row with one column per category — the simplest cross-tab.

19. group_by(region) |> summarise(across(where(is.numeric), \(x) mean(x, na.rm = TRUE))) does what?

Drops every numeric column
Returns one row per region with the mean of every numeric column
Computes the global mean
Renames the columns

where(is.numeric) is a column-selection predicate that picks every numeric column. across() applies the mean to each. Powerful but use sparingly.

20. Reflection. Now that you can produce summary tables in dplyr, when (if ever) would you still reach for an Excel PivotTable?

Common reflections: for quick one-off exploration where I do not need to share the result · for sharing with non-R colleagues · when the deliverable is itself an Excel workbook · for the slicer interactivity — clicking buttons is sometimes the right answer · almost never anymore — once I am fluent in dplyr, the same logic is faster to type than to click.

7.17 AI as a debugging companion

Useful prompts for this chapter

“In R, my group_by(region) |> summarise(mean(O3)) returns NA for some groups, even though the column has values. Why?”
“Show me how to convert a long-format summary tibble into a wide-format cross-tab with pivot_wider(). Use these column names: …”
“What is the difference between group_by() |> summarise() and group_by() |> mutate()?” (Answer: summarise collapses to one row per group; mutate keeps every row and broadcasts the result across the group.)

7.17.1 When not to use AI

Do not let AI choose what to summarise. The choice of mean vs median vs 90th percentile is an analytical decision — yours to make.
Do not let AI invent column names that are not in your tibble. Always cross-check with names(your_tibble).

7.18 Reading

R for Data Science — chapter 5 Data tidying (covers pivot_wider and pivot_longer), chapter 13 Quantitative exploratory data analysis. Free at https://r4ds.hadley.nz/.
The dplyr cheatsheet (PDF): https://rstudio.github.io/cheatsheets/data-transformation.pdf
The tidyr cheatsheet (PDF): https://rstudio.github.io/cheatsheets/tidyr.pdf

Instructor notes

Pacing and emphasis

This chapter is shorter on new verbs than Chapter 6 — only summarise() (deeper), pivot_wider(), count(), n_distinct(), and across(). Spend the saved time on the PivotTable parity exercise (Exercise 7), which makes the Excel-to-R bridge concrete by computing the same number both ways.
The .groups = "drop" habit is worth repeating. Without it, downstream pipelines silently inherit grouping and produce surprising results.
across() is powerful but overwhelming for first-time R users. Mention it once, demo it once, then let learners use whichever pattern (plain summarise vs across) feels clearer.

7.18.1 Expected output checklist (matches the top-of-chapter callout)

frst232_ch07_learner.html (rendered).
frst232_ch07_learner.qmd (source).
Group-lab worksheet or screenshot.

7.18.2 Materials provided alongside this chapter

File	Purpose
`airdata3.csv`	Same raw file from Ch 6. Already in `data/`.
`frst232_ch07_learner.qmd`	Quarto starter with TODO chunks.
`frst232_ch07_solutions.qmd`	Completed solutions. Do not distribute.
`quiz_ch07.html`	Standalone interactive quiz.

7.18.3 Sample numbers (reference)

Quantity	Approximate value
Overall mean O₃ (cleaned tibble)	~10 ppb
Overall median O₃	~6 ppb
Number of unique stations with O₃	6
Number of unique regions in cleaned tibble	4
Highest single-hour O₃ reading	~39 ppb

7.18.4 Common stumbling points

Confusing summarise() with summary(). Walk through the distinction explicitly in the lecture: one is a verb that returns a new tibble; the other is a base R function that prints a text summary.
Forgetting na.rm = TRUE. Causes group-mean columns full of NA. The fix is one argument; the habit takes a few weeks to form.
pivot_wider() syntax confusion. names_from vs values_from is easy to swap. The mnemonic: names_from is the column whose names become headers; values_from is the column whose values fill the cells.
Excel-to-R bridge: Exercise 7 (PivotTable parity) is the most important exercise. Insist that learners actually open Excel and verify the numbers match. The visceral “they’re identical” moment is the chapter’s pedagogical payoff.

Chapter goals

Expected output for this chapter

7.1 Where we are

7.2 Excel-to-R bridge — summarising

7.3 The starting point — the cleaned tibble

7.4 Overall summary — one row

7.4.1 The functions you will use most

7.5 By-group summary — one row per group

7.5.1 Multi-key grouping

7.6 Reshaping with pivot_wider() — the PivotTable equivalent

7.6.1 pivot_wider() arguments

7.7 Shortcut: count() and tally()

7.8 Counting unique values with n_distinct()

7.9 Multiple statistics with across()

7.9.1 Multiple statistics on the same column

7.10 Putting it together — a worked summary report

7.10.1 Overall

7.10.2 By station

7.10.3 By region × day (wide)

7.10.4 By O₃ band — count cross-tab

7.11 Active learning

7.11.1 Activity 1 — Overall summary

7.11.2 Activity 2 — By-station ranking

7.11.3 Activity 3 — count() shortcut

7.11.4 Activity 4 — Cross-tabulation

7.11.5 Activity 5 — across() for many means

7.11.6 Activity 6 — Interpret one summary

7.12 Practice Demo Lab

7.13 Exercises

7.14 Optional, advanced

7.14.1 slice_max() and slice_min() — top-N per group

7.14.2 summarise() with custom functions

7.14.3 summary() of a tibble vs summarise() of a tibble

7.15 A glimpse ahead: From summarising to reshaping and combining

7.16 Self-assessment quiz

7.17 AI as a debugging companion

7.17.1 When not to use AI

7.18 Reading

Instructor notes

7.18.1 Expected output checklist (matches the top-of-chapter callout)

7.18.2 Materials provided alongside this chapter

7.18.3 Sample numbers (reference)

7.18.4 Common stumbling points

7.6 Reshaping with `pivot_wider()` — the PivotTable equivalent

7.6.1 `pivot_wider()` arguments

7.7 Shortcut: `count()` and `tally()`

7.8 Counting unique values with `n_distinct()`

7.9 Multiple statistics with `across()`

7.11.3 Activity 3 — `count()` shortcut

7.11.5 Activity 5 — `across()` for many means

7.14.1 `slice_max()` and `slice_min()` — top-N per group

7.14.2 `summarise()` with custom functions

7.14.3 `summary()` of a tibble vs `summarise()` of a tibble