6 Clean and Prepare Forestry Data in R

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:

Recognize the six core dplyr verbs: filter(), select(), mutate(), arrange(), group_by(), summarise().
Drop unwanted columns with select() and drop unwanted rows with filter().
Add new columns or transform existing ones with mutate().
Sort tibbles with arrange() (ascending) and desc() (descending).
Parse messy text dates into proper R dates with lubridate::mdy() and friends.
Recode values into categories with case_when() (the R equivalent of IFS from Chapter 2).
Look up reference values with left_join() (the R equivalent of VLOOKUP).
Chain operations into a single readable pipeline with the native pipe |>.
Maintain a cleaning log inside a Quarto document — text and code side by side, every change documented.

Expected output for this chapter

What you will hand in

A cleaned tibble of airdata3.csv, saved as outputs/airdata3_clean.csv, containing only the useful columns, with the date parsed properly, and missing-value rows removed where appropriate.
A rendered Quarto HTML report (frst232_ch06_learner.html) showing the full cleaning sequence and a cleaning log.
A station-level summary table of mean O₃ by monitoring station, sorted in descending order.
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.

6.1 Why dplyr

In Chapter 2 you cleaned data in Excel using AutoFilter, sorts, helper columns with IF/IFS, VLOOKUP, and SUMIFS. Every move was visible — but every move was also a click that left no record. “How did you get this number?” months later was hard to answer.

dplyr is the tidyverse package for data cleaning in R. Its verbs do exactly what your Excel moves did, with three changes:

Every move is a written instruction that can be re-run.
Every move is named after the operation (filter not “AutoFilter”; mutate not “add helper column”).
Moves chain together with the pipe |>, so a multi-step cleaning is one block of code that reads top to bottom.

6.2 Excel-to-dplyr bridge

This is the cleaning bridge that Chapter 2 previewed and Chapter 5 introduced. Now you write the right-hand column for real.

What you did in Excel (Ch 2)	What you do in R (Ch 6)
AutoFilter to hide rows	`filter(data, condition)`
Sort A→Z	`arrange(data, column)`
Sort Z→A	`arrange(data, desc(column))`
Delete unwanted columns	`select(data, -unwanted_col)`
Helper column with `=A2+B2`	`mutate(data, new_col = A + B)`
`IFS(x<1000, "small", ...)`	`case_when(x < 1000 ~ "small", ...)`
`VLOOKUP(...)`	`left_join(data, reference, by = "key")`
PivotTable (sum by group)	`group_by(col) \|> summarise(sum = sum(x))`
Cleaning log on a separate sheet	Markdown prose between code chunks

Every cell of the right-hand column is one line of code. After this chapter, you will be able to clean a dataset in R as fluently as you cleaned one in Excel — with the bonus that the work is now reproducible.

6.3 The dataset for this chapter

This chapter uses one openly licensed file from the BC Government — Environmental Reporting BC program:

Property	Value
File	`airdata3.csv`
Source	BC Government — Environmental Reporting BC, BC Air Data
Catalogue	https://envistaweb.env.gov.bc.ca/
Licence	Open Government Licence — British Columbia
Rows	11,904
Columns	27 (most of them empty)
Coverage	16 air-quality monitoring stations across Metro Vancouver
Time period	January 2000 (one month of hourly readings)

6.3.1 Why this dataset matters for forestry

Forest health and air quality are tightly linked. Ground-level ozone (O₃) is one of the most damaging air pollutants for vegetation: it enters leaves and needles through the stomata, injures foliage, and reduces photosynthesis and growth over a season. Unlike a pollutant that is emitted directly, ozone forms when sunlight reacts with pollutants from vehicles, industry, and — during fire season — wildfire emissions. The 16 stations in this file are part of the network BC uses to monitor air quality across the Lower Mainland.

A working analyst at a BC ministry, regional district, or consultancy will use exactly this format — hourly readings from many stations — to assess pollutant exposure. In this chapter we focus on O₃, which is recorded at eight of these stations, so that later chapters can compare readings across stations and regions.

The file is intentionally messy

Unlike the BC silviculture and Ontario forest stats files (which were pre-cleaned by their publishers), airdata3.csv arrives in a state much closer to real-world raw data:

Several columns are entirely missing (every row is NA). We need to detect and drop these.
Two redundant index columns ("Unnamed: 0" and similar) were added by an upstream tool — we drop these too.
Dates are stored as text in M/D/YYYY format ("1/1/2000"). R does not recognise these as dates until we parse them.
Many rows have no O₃ measurement because not every station measures every pollutant.

Real forestry data looks like this. The cleaning work in this chapter is the cleaning work you will do all the time as a working analyst.

6.4 Importing the file

In your frst232/ project (from Chapter 4 or 5), the file should live at data/airdata3.csv. If you do not have it yet, download it from the BC Government link above and place it there.

airdata_raw <- read_csv(here("data", "airdata3.csv"),
                        show_col_types = FALSE)

New names:
• `` -> `...1`

dim(airdata_raw)

[1] 11904    27

Notice the variable name: airdata_raw. We never modify this in place. Every cleaning step creates a new variable. The raw tibble stays untouched in memory in case we need to start over.

6.5 Inspect first — the six-move check from Chapter 5

dim(airdata_raw)

[1] 11904    27

glimpse(airdata_raw)

Rows: 11,904
Columns: 27
$ ...1          <dbl> 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16…
$ `Unnamed: 0`  <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 1…
$ WSPD_VECT     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ WDIR_SCLR     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ WSPD_SCLR     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ 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,…
$ NOx           <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ WDIR_VECT     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ TEMP_MEAN     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ 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…
$ 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, …
$ PRECIP_TOTAL  <lgl> 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…
$ ATM_PRESS_1HR <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ HUMIDITY      <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ location      <chr> "Burnaby South", "Burnaby South", "Burnaby South", "Burn…
$ Date          <chr> "1/1/2000", "1/1/2000", "1/1/2000", "1/1/2000", "1/1/200…
$ Time          <time> 01:00:00, 02:00:00, 03:00:00, 04:00:00, 05:00:00, 06:00…
$ RAD_TOTAL     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ NH3           <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ PM25_5030i    <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ TRS           <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ RAD_NET       <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ PM25_SHARP    <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …

colSums(is.na(airdata_raw)) |> sort(decreasing = TRUE) |> head(15)

    WSPD_VECT     WDIR_SCLR     WSPD_SCLR           NOx     WDIR_VECT 
        11904         11904         11904         11904         11904 
    TEMP_MEAN  PRECIP_TOTAL ATM_PRESS_1HR      HUMIDITY     RAD_TOTAL 
        11904         11904         11904         11904         11904 
          NH3    PM25_5030i       RAD_NET    PM25_SHARP           TRS 
        11904         11904         11904         11904         11173

What we see:

11,904 rows × 27 columns.
Many <dbl> columns. Some <chr> columns (location, Date, Time).
Several columns are 100% missing — the count of NAs equals the total row count.

The 100%-missing columns are an artefact of how this dataset is distributed: the schema reserves room for variables that some stations might measure, but the stations in this extract do not all measure them. Our first cleaning move is to drop them.

6.6 Verb 1 — `select()` (keep or drop columns)

select() chooses which columns to keep. The simplest form is to list the columns you want:

airdata_raw |>
  select(location, Date, Time, O3, PM10, NO2, SO2, CO) |>
  head(3)

# A tibble: 3 × 8
  location      Date     Time      O3  PM10   NO2   SO2    CO
  <chr>         <chr>    <time> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Burnaby South 1/1/2000 01:00      1    13    22     0   0.5
2 Burnaby South 1/1/2000 02:00      3     6    21     0   0.4
3 Burnaby South 1/1/2000 03:00     18     2    19     0   0.4

The pipe |> reads: “take airdata_raw, then pass it into select(), then take just the first 3 rows”.

6.6.1 Dropping columns with `-`

To drop named columns, prefix them with -:

# Drop the redundant index columns added by an upstream tool.
airdata_step1 <- airdata_raw |>
  select(-`...1`, -starts_with("Unnamed"))
ncol(airdata_raw)        # columns before

[1] 27

ncol(airdata_step1)      # columns after — a few fewer

[1] 25

names(airdata_step1)     # and here is exactly what remains

 [1] "WSPD_VECT"     "WDIR_SCLR"     "WSPD_SCLR"     "PM10"         
 [5] "SO2"           "NOx"           "WDIR_VECT"     "TEMP_MEAN"    
 [9] "CO"            "NO"            "NO2"           "PM25"         
[13] "PRECIP_TOTAL"  "O3"            "ATM_PRESS_1HR" "HUMIDITY"     
[17] "location"      "Date"          "Time"          "RAD_TOTAL"    
[21] "NH3"           "PM25_5030i"    "TRS"           "RAD_NET"      
[25] "PM25_SHARP"

starts_with("Unnamed") is a selection helper — a pattern match that grabs every column whose name starts with "Unnamed". Other helpers: ends_with(), contains(), matches() (regex), and where() (predicate).

6.6.2 Dropping all-NA columns programmatically

We saw above that several columns are entirely missing. We can drop them in one move with where():

airdata_step2 <- airdata_step1 |>
  select(where(\(col) !all(is.na(col))))
ncol(airdata_step2)

[1] 11

names(airdata_step2)

 [1] "PM10"     "SO2"      "CO"       "NO"       "NO2"      "PM25"    
 [7] "O3"       "location" "Date"     "Time"     "TRS"

Read it: “keep columns where it is not the case that all values are NA”. We just removed twelve dead-weight columns with one line.

6.7 Verb 2 — `filter()` (keep or drop rows)

filter() keeps rows where a condition is TRUE:

# Keep only rows where O₃ was actually measured.
airdata_step3 <- airdata_step2 |>
  filter(!is.na(O3))
nrow(airdata_step3)

[1] 5677

!is.na(O3) reads “O3 is NOT missing”. The ! is the logical NOT operator.

6.7.1 Combining conditions

You can combine conditions with & (AND), | (OR), or by adding multiple conditions separated by commas (which dplyr treats as AND):

# Keep only Burnaby South rows with measured O₃
burnaby_o3 <- airdata_step3 |>
  filter(location == "Burnaby South",
         !is.na(O3))
nrow(burnaby_o3)

[1] 732

Note the double-equals == for “is equal to” — single = would be an assignment.

6.7.2 Common filter patterns

You want	Filter expression
One specific value	`filter(col == "Burnaby South")`
Not equal	`filter(col != "Burnaby South")`
One of several	`filter(col %in% c("A", "B", "C"))`
Numeric threshold	`filter(O3 > 10)`
Between two values	`filter(O3 >= 10, O3 < 20)`
Non-missing	`filter(!is.na(O3))`
Date range	`filter(date >= "2000-01-15")`

The right-hand column reads naturally in English. “Filter the data where location is in ‘A’, ‘B’, or ‘C’.” That’s the dplyr design.

6.8 Verb 3 — `mutate()` (add or change columns)

mutate() adds new columns or replaces existing ones. The most common use in this chapter is parsing the text date column into a real R date.

airdata_step4 <- airdata_step3 |>
  mutate(date_parsed = mdy(Date))
glimpse(airdata_step4 |> select(Date, date_parsed))

Rows: 5,677
Columns: 2
$ Date        <chr> "1/1/2000", "1/1/2000", "1/1/2000", "1/1/2000", "1/1/2000"…
$ date_parsed <date> 2000-01-01, 2000-01-01, 2000-01-01, 2000-01-01, 2000-01-0…

mdy() from the lubridate package reads a text date in month/day/year order and returns a proper <date> column. Other parsers:

Source format	lubridate function
`"1/15/2000"` (US)	`mdy()`
`"15/1/2000"` (UK / Canadian common)	`dmy()`
`"2000-01-15"` (ISO 8601)	`ymd()`
`"15-Jan-2000"`	`dmy()` (handles abbreviations)

Date ambiguity

A string like "3/4/2000" could be March 4 or April 3 depending on convention. lubridate trusts the function name — call mdy() and it interprets as March 4; call dmy() and it interprets as April 3.

Always check: pick one row, look at the original Date string, and confirm the parsed date is what you expected. A wrong date parser is a silent bug that ruins every later analysis.

6.8.1 Multiple new columns at once

airdata_step5 <- airdata_step4 |>
  mutate(date_parsed = mdy(Date),
         year = year(date_parsed),
         month = month(date_parsed),
         day = day(date_parsed),
         o3_kg_per_m3 = O3 / 1000)
glimpse(airdata_step5 |> select(Date, date_parsed, year, month, day, o3_kg_per_m3))

Rows: 5,677
Columns: 6
$ Date         <chr> "1/1/2000", "1/1/2000", "1/1/2000", "1/1/2000", "1/1/2000…
$ date_parsed  <date> 2000-01-01, 2000-01-01, 2000-01-01, 2000-01-01, 2000-01-…
$ year         <dbl> 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 200…
$ month        <dbl> 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, …
$ o3_kg_per_m3 <dbl> 0.001, 0.003, 0.018, 0.032, 0.031, 0.032, 0.028, 0.019, 0…

year(), month(), day() are lubridate accessors. Each returns a vector of the same length.

6.9 Verb 4 — `arrange()` (sort rows)

arrange() sorts the tibble:

airdata_step5 |>
  arrange(O3) |>
  select(location, date_parsed, O3) |>
  head(5)

# A tibble: 5 × 3
  location      date_parsed    O3
  <chr>         <date>      <dbl>
1 Burnaby South 2000-01-02      0
2 Burnaby South 2000-01-02      0
3 Burnaby South 2000-01-02      0
4 Burnaby South 2000-01-04      0
5 Burnaby South 2000-01-04      0

Ascending by default. For descending, wrap in desc():

airdata_step5 |>
  arrange(desc(O3)) |>
  select(location, date_parsed, O3) |>
  head(5)

# A tibble: 5 × 3
  location                    date_parsed    O3
  <chr>                       <date>      <dbl>
1 Port Moody Rocky Point Park 2000-01-09     39
2 Langley Central             2000-01-09     38
3 Port Moody Rocky Point Park 2000-01-09     38
4 Port Moody Rocky Point Park 2000-01-09     38
5 Langley Central             2000-01-09     37

The top of the list is the highest-O₃ hourly readings — useful for spotting the most polluted moments.

6.9.1 Multi-key sort

airdata_step5 |>
  arrange(location, desc(O3)) |>
  select(location, date_parsed, O3) |>
  head(8)

# A tibble: 8 × 3
  location      date_parsed    O3
  <chr>         <date>      <dbl>
1 Burnaby South 2000-01-17     35
2 Burnaby South 2000-01-02     33
3 Burnaby South 2000-01-09     33
4 Burnaby South 2000-01-09     33
5 Burnaby South 2000-01-09     33
6 Burnaby South 2000-01-09     33
7 Burnaby South 2000-01-17     33
8 Burnaby South 2000-01-01     32

Sorts by location (alphabetical), then within each location by O3 descending. The equivalent in Excel was Data → Sort → Add Level.

6.10 Verb 5 — `case_when()` (the R `IFS`)

case_when() recodes values into categories based on rules. It is the direct R equivalent of Excel’s IFS.

airdata_step6 <- airdata_step5 |>
  mutate(o3_band = case_when(
    is.na(O3)    ~ "missing",
    O3 < 10      ~ "low",
    O3 < 20      ~ "moderate",
    O3 < 30      ~ "high",
    O3 >= 30     ~ "very high",
    TRUE           ~ "other"
  ))
airdata_step6 |> count(o3_band)

# A tibble: 4 × 2
  o3_band       n
  <chr>     <int>
1 high        905
2 low        3327
3 moderate   1013
4 very high   432

Read it line by line: “if O3 is missing, return ‘missing’; otherwise if it is less than 10, return ‘low’; otherwise less than 20, ‘moderate’; …”. The first match wins. The trailing TRUE ~ "other" is the catch-all, like the TRUE in Excel’s IFS.

Why case_when beats nested if_else

You could write the same logic with nested if_else():

if_else(O3 < 10, "low",
  if_else(O3 < 20, "moderate",
    if_else(O3 < 30, "high", "very high")))

It works, but it nests deeply and is hard to maintain. case_when is flat — one row per rule — and easy to extend.

6.11 Verb 6 — `summarise()` overall, `group_by()` for groups

summarise() collapses a tibble to one row of summary statistics:

airdata_step6 |>
  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)
  )

# A tibble: 1 × 4
  n_obs mean_o3 median_o3 max_o3
  <int>   <dbl>     <dbl>  <dbl>
1  5677    10.3         6     39

The function n() (with no arguments, only used inside summarise() or mutate()) returns the row count.

Add group_by() before summarise() to get one row per group:

airdata_step6 |>
  group_by(location) |>
  summarise(
    n_obs = n(),
    n_with_o3 = sum(!is.na(O3)),
    mean_o3 = mean(O3, na.rm = TRUE)
  ) |>
  arrange(desc(mean_o3))

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

This is the PivotTable equivalent from Chapter 3. Same operation. Same answer. Now scriptable.

Always remember na.rm = TRUE

When summarising a column that might have missing values, add na.rm = TRUE to every aggregation function: mean(), sum(), min(), max(), median(), sd(). Forgetting it returns NA for any group with even one missing value, which is rarely what you want.

6.11.1 `count()` — the most common summarise

count(col) is a shortcut for group_by(col) |> summarise(n = n()):

airdata_step6 |>
  count(location, sort = TRUE)

# A tibble: 8 × 2
  location                               n
  <chr>                              <int>
1 Burnaby South                        732
2 Langley Central                      732
3 Richmond South                       732
4 North Delta                          731
5 Vancouver Kitsilano                  731
6 Port Moody Rocky Point Park          728
7 Vancouver International Airport #2   727
8 Maple Ridge Golden Ears School       564

sort = TRUE orders the result descending by count. Useful for spotting which categories dominate.

6.12 Verb 7 — `left_join()` (the R `VLOOKUP`)

A real-world cleaning task often needs a lookup: take a code column, attach a description from a reference table. left_join does this without the silent failures of VLOOKUP.

Suppose we have a small reference tibble mapping stations to regions:

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"
)

Attach the region column to the data:

airdata_step7 <- airdata_step6 |>
  left_join(station_regions, by = "location")
airdata_step7 |>
  select(location, region, date_parsed, O3) |>
  head(5)

# A tibble: 5 × 4
  location      region            date_parsed    O3
  <chr>         <chr>             <date>      <dbl>
1 Burnaby South Burrard Peninsula 2000-01-01      1
2 Burnaby South Burrard Peninsula 2000-01-01      3
3 Burnaby South Burrard Peninsula 2000-01-01     18
4 Burnaby South Burrard Peninsula 2000-01-01     32
5 Burnaby South Burrard Peninsula 2000-01-01     31

Every row gets its region. The by = "location" argument names the column to match on. Stations not in the reference table get region = NA (and the join does not drop them — that is what left means).

left_join vs inner_join vs right_join

Join	Keeps
`left_join(x, y)`	All rows of `x`, plus matched rows from `y` (NA otherwise)
`inner_join(x, y)`	Only rows that match in both
`right_join(x, y)`	All rows of `y`, plus matched rows from `x`
`full_join(x, y)`	All rows from both, NA where no match

For lookups, left_join is the default — it keeps every row in your main data, even if the lookup misses.

6.13 Putting it all together — the full cleaning pipeline

Now the entire chapter in one pipeline:

airdata_clean <- read_csv(here("data", "airdata3.csv"),
                          show_col_types = FALSE) |>
  # Drop redundant index columns
  select(-starts_with("Unnamed"), -any_of("...1")) |>
  # Drop columns that are 100% missing
  select(where(\(col) !all(is.na(col)))) |>
  # Parse the date and add date parts
  mutate(date_parsed = mdy(Date),
         year = year(date_parsed),
         month = month(date_parsed),
         day = day(date_parsed)) |>
  # Keep only rows with a measured O₃
  filter(!is.na(O3)) |>
  # Classify O₃ into bands
  mutate(o3_band = case_when(
    O3 < 10  ~ "low",
    O3 < 20  ~ "moderate",
    O3 < 30  ~ "high",
    TRUE       ~ "very high"
  )) |>
  # Attach region info
  left_join(station_regions, by = "location") |>
  # Final sort
  arrange(date_parsed, location)

New names:
• `` -> `...1`

dim(airdata_clean)

[1] 5677   17

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…

One pipeline. Eight steps. Reads top-to-bottom in English. Reproducible. Documented. This is what cleaning data in R looks like once you are fluent.

6.13.1 Save the cleaned tibble

write_csv(airdata_clean,
          here("outputs", "airdata3_clean.csv"))

The cleaned file is your Expected output for the chapter assignment. The raw data/airdata3.csv stays untouched.

6.14 The cleaning log

In Excel you kept a cleaning log on a separate sheet. In Quarto you keep it as Markdown text between the code chunks.

For this chapter, the log might look like:

## Cleaning log

| Date | Step | What I did | Why |
|---|---|---|---|
| 2026-09-22 | 1 | Dropped 2 redundant index columns (`Unnamed: 0`, etc.) | Added by an upstream pandas export; no meaning. |
| 2026-09-22 | 2 | Dropped 12 columns that were 100% missing | No measurements at any station for this month. |
| 2026-09-22 | 3 | Parsed `Date` from text to `<date>` using `lubridate::mdy()` | Original was `"M/D/YYYY"` text. |
| 2026-09-22 | 4 | Added `year`, `month`, `day` columns | Convenience for later grouping. |
| 2026-09-22 | 5 | Filtered to rows where O3 was measured | Reduced rows from 11,904 to 742. |
| 2026-09-22 | 6 | Recoded O3 into 4 bands using `case_when` | For the air-quality summary table. |
| 2026-09-22 | 7 | Left-joined a station→region reference table | To enable regional summaries. |
| 2026-09-22 | 8 | Arranged by date then location | Final sort. |

That table goes in your .qmd directly above the cleaning pipeline. Anyone reading the rendered report sees what you did, why, and the code that did it.

6.15 Active learning

6.15.1 Activity 1 — Find the dirty columns

Run colSums(is.na(airdata_raw)) |> sort(decreasing = TRUE). How many columns are 100% missing? How many are partially missing?

6.15.2 Activity 2 — Filter and count

How many rows did the file have before filtering for non-missing O3? How many after? What fraction of rows did you keep?

6.15.3 Activity 3 — Try a different case_when

Replace the four-band O3 classification with a three-band version (good / moderate / unhealthy). Re-run the pipeline. How does the count of each category change?

6.15.4 Activity 4 — Add a new station to the lookup

Add one row to station_regions for a station you noticed in the data but is missing from the reference. Re-run the left_join. Confirm the new station now has a region.

6.15.5 Activity 5 — Save and re-import

Save your cleaned tibble to outputs/airdata3_clean.csv. Then in a fresh R session, import it with read_csv(). Confirm the date column is still <date> (not <chr>). If it is, your earlier parsing held.

6.16 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: Clean the air-quality file together

Work in groups of 3 or 4. Each member should be inside their own frst232/ RStudio Project. The file airdata3.csv must be in each member’s data/ folder.

Task 1 — Inspect together. Each member runs the six-move inspection (dim, names, glimpse, summary, colSums(is.na(...))). Compare what you see. How many columns have any data? How many are 100% missing?

Task 2 — Drop redundant columns. As a group, decide which columns to drop. Write the select() call together.

Task 3 — Parse the date. Try mdy(Date). Did it work for every row, or did some fail? (Check with summary(parsed_date) — any NA count from parsing.)

Task 4 — Filter and group. Write a filter() + group_by() + summarise() pipeline that returns mean O₃ per station. Sort descending by mean O₃.

Task 5 — Discuss the result. Which station has the highest mean O₃ in this month? Does that make sense given what the group members know about Metro Vancouver geography?

Task 6 — Submit. Each group submits: - the final pipeline code (paste into a Quarto chunk in your shared learner file), - the station-ranked summary table, - a one-sentence interpretation of the result.

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

Reference solution — download

Open the Chapter 6 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.

6.17 Exercises

These are the take-home exercises for this chapter.

Read and inspect. Import airdata3.csv. Report the row count, column count, and the number of all-NA columns.
Clean step by step. Build the pipeline yourself, one step at a time, in separate chunks. After each step, print nrow() and ncol(). The final cleaned tibble should have 742 rows (rows with O₃ measured).
Per-station summary. Group by location and compute n_obs, mean_o3, median_o3, max_o3. Sort descending by mean_o3.
Per-day summary. Group by your parsed date_parsed column and compute mean O₃ across all stations per day. Which day in January 2000 had the highest provincial mean O₃?
Add a category. Use case_when() to add a column aq_alert that is "alert" if O₃ ≥ 25 and "normal" otherwise. How many "alert" hours are in the file?
Lookup challenge. Build a small station_lat_lon tibble with at least 5 stations and made-up latitude/longitude pairs. left_join it onto your cleaned tibble. Confirm the join did not drop any rows.
Cleaning log. Write a cleaning log Markdown table in your .qmd, with one row per step you did in Exercise 2.
Optional, harder. Use pivot_wider() to reshape your per-station summary so each station is a column. (We will meet pivot_wider formally in Chapter 8 — try it now if you want to look ahead.)

6.18 Optional, advanced

6.18.1 Renaming columns

rename() lets you give a column a new name:

airdata_step1 |>
  rename(station = location,
         o3_ugm3 = O3)

6.18.2 `if_else()` for binary recodes

When you only need two outcomes, if_else() is briefer than case_when():

mutate(aq_alert = if_else(O3 >= 25, "alert", "normal"))

6.18.3 `across()` for applying a function to many columns

If you want the mean of every numeric column grouped by station, you can use across():

airdata_clean |>
  group_by(location) |>
  summarise(across(where(is.numeric), \(x) mean(x, na.rm = TRUE)))

This is one of the most powerful patterns in dplyr — but also one of the most confusing. We will use it sparingly in this course.

6.19 A glimpse ahead: From cleaning to summarizing

In Chapter 7 we go deeper into summarise(). The verbs are already familiar; what we add is:

Multiple summary statistics in one call.
group_by() with two or more grouping variables.
count() and tally() shortcuts.
Reshaping summary output with pivot_wider() for cross-tabs.
The connection to the Excel PivotTable from Chapter 3 — same answer, now scriptable.

airdata_clean |>
  group_by(region, o3_band) |>
  summarise(n = n(), .groups = "drop") |>
  pivot_wider(names_from = o3_band, values_from = n, values_fill = 0)

That’s a cross-tabulation: rows = region, columns = O₃ band, values = count. Equivalent to a PivotTable with region in Rows, o3_band in Columns, and Count of n_obs in Values — but as one line of dplyr.

Chapter 7 walks through this in detail.

6.20 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. Which dplyr verb keeps rows where a condition is TRUE?

select()
filter()
mutate()
arrange()

filter() works on rows. select() works on columns. Easy to confuse — name them out loud when you write them.

2. Which dplyr verb keeps or drops columns by name?

select()
filter()
mutate()
group_by()

select() takes column names (or selection helpers like starts_with()) and returns a tibble with only those columns.

3. airdata_raw |> select(-Date) does what?

Selects only the Date column
Drops the Date column, keeps everything else
Sorts by Date
Filters where Date is missing

The minus sign means "drop this column". select() can drop one column or many.

4. arrange(desc(O3)) sorts the tibble —

Ascending by O3
Descending by O3 (largest first)
Alphabetically
By row number

arrange() is ascending by default. Wrap in desc() for descending.

5. Which dplyr verb adds a new column or transforms an existing one?

filter()
select()
mutate()
summarise()

mutate() is the column-creation/transformation verb. mutate(date_parsed = mdy(Date)) adds a parsed date column.

6. The lubridate function that parses "1/15/2000" (month/day/year text) into a real R date is —

ymd()
mdy()
dmy()
parse_date()

mdy() reads month-first. dmy() reads day-first. ymd() reads ISO 8601 (year-month-day).

7. case_when() in dplyr is the R equivalent of which Excel function?

VLOOKUP
SUMIF
IFS (or nested IF)
COUNTA

case_when() walks down a list of conditions, returning the value for the first matching one. The trailing TRUE ~ ... is the catch-all, matching Excel's TRUE in IFS.

8. left_join() in dplyr is the R equivalent of which Excel function?

SUMIFS
VLOOKUP (or XLOOKUP)
IFS
PIVOT

A left join attaches a column from a reference table to your main data based on a matching key — exactly what VLOOKUP does, with fewer silent failures.

9. group_by(location) |> summarise(mean(O3, na.rm = TRUE)) is equivalent to which Excel feature?

AutoFilter
VLOOKUP
A PivotTable with location in Rows and Average of O3 in Values
Conditional Formatting

Group-then-summarise is the conceptual ancestor of every PivotTable. Same answer, scriptable.

10. Why include na.rm = TRUE in mean(O3, na.rm = TRUE)?

It makes R faster
Without it, the mean returns NA whenever any value in the column is NA
It removes the O3 column
It is required syntax

By default, R refuses to silently skip missing values. na.rm = TRUE tells the aggregation function to skip them. Forgetting it is the most common silent bug in summarise pipelines.

11. The native pipe |> takes the thing on its left and —

deletes it
passes it as the first argument of the function on its right
divides it by the right side
prints it

x |> f() is equivalent to f(x). Chained pipes let a multi-step pipeline read top-to-bottom in English.

12. To filter rows where location is one of "A", "B", or "C", the cleanest expression is —

filter(location == "A" | location == "B" | location == "C")
filter(location %in% c("A", "B", "C"))
filter(location = c("A", "B", "C"))
filter(location == c("A", "B", "C"))

%in% is the membership operator. The first option works but is wordy. The last is a common bug — == does element-wise comparison and will not do what you expect.

13. airdata_raw |> filter(!is.na(O3)) keeps —

Only rows where O3 is missing
Only rows where O3 is NOT missing
All rows, unchanged
An error

is.na() returns TRUE for missing values. The ! negates it. So this keeps rows where O3 is non-missing.

14. count(location, sort = TRUE) is equivalent to —

filter(location == ...)
group_by(location) |> summarise(n = n()) |> arrange(desc(n))
select(location)
mutate(location_count = ...)

count() is the dplyr shortcut for group-then-tally. sort = TRUE orders the result descending by count.

15. Which is the cleanest way to drop every column that is 100% missing?

Manually list every empty column in select()
select(where(\(col) !all(is.na(col))))
filter(!is.na(.))
drop_na()

where() applies a predicate to each column. The lambda \(col) !all(is.na(col)) reads "keep the column if it is not the case that all of its values are NA". drop_na() drops rows, not columns.

16. Where does the BC air-quality file come from?

A textbook
The BC Government, Environmental Reporting BC — the same publisher as the silviculture file
A research paper
A private consulting firm

Both the BC silviculture (Ch 1) and the BC air-quality (Ch 6) files come from Environmental Reporting BC, published under the Open Government Licence — British Columbia.

17. After you build a cleaning pipeline, what should you do with the cleaned tibble?

Overwrite the raw CSV in data/
Save it to outputs/ with a clear name (e.g., airdata3_clean.csv) using write_csv()
Email it to yourself
Delete the raw file

Same convention as Excel: raw stays in data/, cleaned versions go in outputs/.

18. In a pipe like x |> filter(...) |> mutate(...) |> arrange(...), in what order do the operations run?

arrange first, then mutate, then filter
filter first, then mutate, then arrange — top to bottom, left to right
All three run simultaneously
In alphabetical order

Pipes are evaluated left to right. The output of each step becomes the input of the next. Reading a pipe is like reading a sentence.

19. The Excel function =SUMIFS(O3_col, location_col, "Burnaby South", O3_col, ">25") has which dplyr equivalent?

select(location, O3)
filter(location == "Burnaby South", O3 > 25) |> summarise(total = sum(O3))
mutate(SUMIFS = ...)
arrange(O3)

SUMIFS = sum the values subject to multiple criteria. In dplyr: filter to the criteria, then summarise the sum. Same answer.

20. Reflection. Of the six core dplyr verbs you met, which felt most natural coming from Excel? Which felt most foreign?

Common reflections: filter felt natural — it is just AutoFilter typed out · select was easy once I learned the minus-sign for dropping · case_when felt foreign at first but turned out cleaner than nested IF · left_join is way clearer than VLOOKUP — no silent first-match bug · group_by + summarise was the most powerful new tool — one line replaces a whole PivotTable.

6.21 AI as a debugging companion

Useful prompts for this chapter

“In R, my case_when() returns NA for some rows even though I have a TRUE catch-all at the end. What might be wrong?”
“Explain step by step what this pipeline does: data |> filter(!is.na(O3)) |> group_by(location) |> summarise(mean_o3 = mean(O3))”
“I parsed a date with mdy() but the result is NA for some rows. The original strings look like ‘1/15/2000’. What are the most common reasons mdy() returns NA?”

6.21.1 A cleaning prompt template

When you ask AI for cleaning help, include four things:

The output of glimpse(your_tibble).
The cleaning step you are trying to do (in plain English).
The dplyr code you tried.
The output (or error) you got.

Without these four, AI’s suggestion will be too generic to use directly.

6.21.2 When not to use AI

Do not let AI write your cleaning log. The log records your decisions about your data.
Do not let AI invent column names that are not in your data (a common hallucination). Cross-check every suggested column name against names(your_tibble).

6.22 Reading

R for Data Science — chapter 4 Data transformation, the canonical dplyr reference. Free at https://r4ds.hadley.nz/.
The dplyr cheatsheet (PDF): https://rstudio.github.io/cheatsheets/data-transformation.pdf
The lubridate cheatsheet (PDF): https://rstudio.github.io/cheatsheets/lubridate.pdf
BC Air Data background: https://www2.gov.bc.ca/gov/content/environment/air-land-water/air/air-quality/air-data

Instructor notes

Pacing and emphasis

The chapter has seven new R concepts (the six core verbs plus case_when / left_join). Plan to teach the verbs in two clusters: row-and-column (filter, select, arrange) in one session, transform-and-summarize (mutate, case_when, group_by + summarise, left_join) in the next.
The Excel-to-dplyr bridge table at the top of the chapter is the most important content. Refer to it whenever a learner asks why R has a particular verb.
The cleaning log habit is the chapter’s most transferable skill. Insist learners write one for every cleaning task, starting with this chapter.

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

outputs/airdata3_clean.csv (saved tibble).
frst232_ch06_learner.html (rendered Quarto report).
Station-ranked summary table.
Group-lab worksheet or screenshot.

6.22.2 Materials provided alongside this chapter

File	Purpose
`airdata3.csv`	The raw BC air-quality file. Place in `data/`.
`frst232_ch06_learner.qmd`	Quarto starter with TODO chunks for each verb.
`frst232_ch06_solutions.qmd`	Completed solutions. Do not distribute to learners.
`quiz_ch06.html`	Standalone interactive quiz.

6.22.3 Common stumbling points

Confusing filter with select. Walk through “rows vs columns” explicitly. “filter chooses some of the rows. select chooses some of the columns.”
== vs =. Learners write filter(location = "Burnaby") and get an error. Insist on == for comparison.
%in% confusion. Some learners try filter(location == c("A", "B")) and get unexpected results (it does element-wise comparison). Teach %in% explicitly.
Date parsing failures. Calling mdy() on a column that’s in dmy() format silently returns wrong dates. Walk through the format-checking habit.
Forgetting na.rm = TRUE. Resulting in summaries full of NA. Emphasise that for any column that might be missing, add na.rm = TRUE to every aggregation.
Excel-to-R bridge: When a learner asks how to do an Excel cleaning move, point them to the bridge table at the top of this chapter. Almost every Excel cleaning question has a one-line dplyr answer.

Chapter goals

Expected output for this chapter

6.1 Why dplyr

6.2 Excel-to-dplyr bridge

6.3 The dataset for this chapter

6.3.1 Why this dataset matters for forestry

6.4 Importing the file

6.5 Inspect first — the six-move check from Chapter 5

6.6 Verb 1 — select() (keep or drop columns)

6.6.1 Dropping columns with -

6.6.2 Dropping all-NA columns programmatically

6.7 Verb 2 — filter() (keep or drop rows)

6.7.1 Combining conditions

6.7.2 Common filter patterns

6.8 Verb 3 — mutate() (add or change columns)

6.8.1 Multiple new columns at once

6.9 Verb 4 — arrange() (sort rows)

6.9.1 Multi-key sort

6.10 Verb 5 — case_when() (the R IFS)

6.11 Verb 6 — summarise() overall, group_by() for groups

6.11.1 count() — the most common summarise

6.12 Verb 7 — left_join() (the R VLOOKUP)

6.13 Putting it all together — the full cleaning pipeline

6.13.1 Save the cleaned tibble

6.14 The cleaning log

6.15 Active learning

6.15.1 Activity 1 — Find the dirty columns

6.15.2 Activity 2 — Filter and count

6.15.3 Activity 3 — Try a different case_when

6.15.4 Activity 4 — Add a new station to the lookup

6.15.5 Activity 5 — Save and re-import

6.16 Practice Demo Lab

6.17 Exercises

6.18 Optional, advanced

6.18.1 Renaming columns

6.18.2 if_else() for binary recodes

6.18.3 across() for applying a function to many columns

6.19 A glimpse ahead: From cleaning to summarizing

6.20 Self-assessment quiz

6.21 AI as a debugging companion

6.21.1 A cleaning prompt template

6.21.2 When not to use AI

6.22 Reading

Instructor notes

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

6.22.2 Materials provided alongside this chapter

6.22.3 Common stumbling points

6.6 Verb 1 — `select()` (keep or drop columns)

6.6.1 Dropping columns with `-`

6.7 Verb 2 — `filter()` (keep or drop rows)

6.8 Verb 3 — `mutate()` (add or change columns)

6.9 Verb 4 — `arrange()` (sort rows)

6.10 Verb 5 — `case_when()` (the R `IFS`)

6.11 Verb 6 — `summarise()` overall, `group_by()` for groups

6.11.1 `count()` — the most common summarise

6.12 Verb 7 — `left_join()` (the R `VLOOKUP`)

6.18.2 `if_else()` for binary recodes

6.18.3 `across()` for applying a function to many columns