Tidyverse Data Wrangling

The tidyverse grammar

The tidyverse is a collection of packages that share a common design philosophy: data in rectangular tables, one row per observation, one column per variable, and functions that do one thing clearly and chain together naturally.

library(tidyverse)   # loads dplyr, tidyr, ggplot2, readr, stringr, forcats, purrr

The six core dplyr verbs

filter()

Keep rows that satisfy a condition

filter(DO_mgl > 6)

select()

Keep or drop columns by name

select(site, date, DO_mgl)

mutate()

Add or overwrite columns

mutate(DO_sat = DO_mgl / 9.1)

summarise()

Collapse rows to a summary

summarise(mean_DO = mean(DO_mgl))

group_by()

Define groups for summarise or mutate

group_by(site, month)

arrange()

Sort rows by one or more columns

arrange(desc(date))

The dataset: stream water quality

We will use a simulated multi-site water quality dataset throughout this module. It is designed to mirror common real-world messiness: missing values, inconsistent site labels, and a mix of numeric and character columns.

set.seed(42)

# ── Simulated raw water quality data ─────────────────────────────
# Twelve sites, daily sampling over two seasons, with realistic noise
n <- 240

wq_raw <- tibble(
  record_id   = 1:n,
  Site        = sample(c("Upper_Madera", "upper_madera",   # inconsistent case
                          "Mid Reach",    "Mid_Reach",      # inconsistent separator
                          "Lower Madera", "Lower_Madera",
                          "Tributary_N",  "Tributary_S"),
                        n, replace = TRUE),
  Date        = sample(seq(as.Date("2023-05-01"),
                            as.Date("2023-10-31"), by = "day"),
                        n, replace = TRUE),
  Season      = if_else(lubridate::month(
                  sample(seq(as.Date("2023-05-01"),
                              as.Date("2023-10-31"), by = "day"),
                          n, replace = TRUE)) <= 7,
                  "Spring", "Summer"),
  DO_mgl      = round(rnorm(n, mean = 8.2, sd = 1.4), 2),
  Temp_C      = round(rnorm(n, mean = 16,  sd = 3.5), 1),
  pH          = round(rnorm(n, mean = 7.6, sd = 0.4), 2),
  Cond_uScm   = round(rnorm(n, mean = 320, sd = 60),  0),
  Turbidity_NTU = round(abs(rnorm(n, mean = 4.5, sd = 6)), 1),
  Sampler     = sample(c("AJ", "BL", "CK", "AJ "),    # trailing space in one label
                        n, replace = TRUE),
  QC_flag     = sample(c(NA, "suspect", "equipment_error"),
                        n, replace = TRUE, prob = c(0.85, 0.1, 0.05))
) %>%
  # Inject some realistic NA patterns
  mutate(
    DO_mgl    = if_else(runif(n) < 0.06, NA_real_, DO_mgl),
    Temp_C    = if_else(runif(n) < 0.03, NA_real_, Temp_C),
    pH        = if_else(runif(n) < 0.04, NA_real_, pH)
  )

glimpse(wq_raw)

## Rows: 240
## Columns: 11
## $ record_id     <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 1…
## $ Site          <chr> "Upper_Madera", "Lower Madera", "Upper_Madera", "Upper_M…
## $ Date          <date> 2023-07-20, 2023-05-27, 2023-05-30, 2023-10-27, 2023-08…
## $ Season        <chr> "Spring", "Spring", "Spring", "Summer", "Summer", "Sprin…
## $ DO_mgl        <dbl> 8.48, 6.65, 11.13, 6.59, 9.70, 7.72, 8.02, 10.51, 10.38,…
## $ Temp_C        <dbl> 5.6, 15.5, 10.3, 14.4, 13.2, 17.6, 15.1, 14.6, 14.1, 15.…
## $ pH            <dbl> 7.22, 7.12, 7.94, NA, 7.85, 7.36, 8.01, 7.94, 7.33, 7.43…
## $ Cond_uScm     <dbl> 368, 342, 314, 210, 267, 270, 396, 307, 310, 277, 319, 2…
## $ Turbidity_NTU <dbl> 10.3, 5.2, 5.5, 9.6, 15.9, 10.5, 13.1, 7.5, 8.7, 1.1, 10…
## $ Sampler       <chr> "CK", "CK", "AJ ", "AJ ", "AJ", "CK", "BL", "AJ ", "AJ",…
## $ QC_flag       <chr> NA, NA, NA, NA, "suspect", "suspect", NA, "equipment_err…

The Pipe Operator

The pipe passes the output of one function directly into the first argument of the next, letting you build a sequence of transformations that reads left-to-right, top-to-bottom — exactly like a sentence.

R 4.1+ ships with a native pipe |>. The tidyverse historically used %>% from the magrittr package; both work the same way for most purposes.

# Without pipe — read inside-out, hard to follow
arrange(filter(select(wq_raw, site, date, DO_mgl), DO_mgl > 6), date)

# With pipe — reads as a sequence of actions
wq_raw |>
  select(site, date, DO_mgl) |>
  filter(DO_mgl > 6)         |>
  arrange(date)

# Each step receives a data frame and returns a data frame — the shape may change but the contract stays the same

⚙️ |> vs %>% — practical differences

Feature	Native `\\|>` (R ≥ 4.1)	magrittr `%>%`
Requires a package	No	Yes (`magrittr` / `tidyverse`)
Placeholder for non-first arg	`_` (R ≥ 4.2)	`.`
Speed	Marginally faster	Slightly slower
RStudio shortcut	`Ctrl`+`Shift`+`M`	Same shortcut

For EA030 purposes they are interchangeable. Use |> in new scripts; you will still see %>% in course resources and online examples.

filter() — Keep Rows

filter() returns rows where the logical condition is TRUE. Any comparison operator works: ==, !=, >, >=, <, <=, %in%, is.na().

# Rows with acceptable DO and no QC flag
clean_do <- wq_raw |>
  filter(
    DO_mgl > 5,           # drop hypoxic readings
    !is.na(DO_mgl),       # drop missing DO
    is.na(QC_flag)        # keep only unflagged records
  )

nrow(wq_raw)    # before

## [1] 240

nrow(clean_do)  # after

## [1] 177

Filtering with %in%

%in% tests membership in a vector — cleaner than multiple == conditions joined by |:

# Keep only the two tributary sites
tributaries <- wq_raw |>
  filter(Site %in% c("Tributary_N", "Tributary_S"))

tributaries |> count(Site)

## # A tibble: 2 × 2
##   Site            n
##   <chr>       <int>
## 1 Tributary_N    22
## 2 Tributary_S    29

Filtering on string patterns

Use str_detect() from stringr for partial-match filtering — useful when site names are inconsistent:

# All records where Site contains "Madera" (any case)
madera <- wq_raw |>
  filter(str_detect(Site, regex("madera", ignore_case = TRUE)))

madera |> count(Site)

## # A tibble: 4 × 2
##   Site             n
##   <chr>        <int>
## 1 Lower Madera    31
## 2 Lower_Madera    33
## 3 Upper_Madera    34
## 4 upper_madera    42

Filtering on dates

library(lubridate)

# Summer records only (July–September)
summer <- wq_raw |>
  filter(between(month(Date), 7, 9))

nrow(summer)

## [1] 119

⚠️ filter() drops rows — make sure that is what you want

If you filter out NA rows to make a plot, do it inside that pipeline only — do not overwrite wq_raw. Keep the raw object intact; filter into a named subset when needed.

select() — Keep Columns

select() subsets or reorders columns. It accepts column names, ranges, and helper functions.

# Keep only the measurement columns plus identifiers
wq_slim <- wq_raw |>
  select(record_id, Site, Date, DO_mgl, Temp_C, pH, Cond_uScm)

names(wq_slim)

## [1] "record_id" "Site"      "Date"      "DO_mgl"    "Temp_C"    "pH"       
## [7] "Cond_uScm"

select() helpers

# starts_with / ends_with / contains / matches
wq_raw |> select(starts_with("Temp")) |> names()

## [1] "Temp_C"

wq_raw |> select(ends_with("_C"))     |> names()

## [1] "Temp_C"

wq_raw |> select(contains("mgl"))     |> names()

## [1] "DO_mgl"

# Drop specific columns with -
wq_raw |> select(-record_id, -Sampler, -QC_flag) |> names()

## [1] "Site"          "Date"          "Season"        "DO_mgl"       
## [5] "Temp_C"        "pH"            "Cond_uScm"     "Turbidity_NTU"

# Reorder: bring Date and Site to front, keep everything else
wq_raw |> select(Date, Site, everything()) |> names() |> head(5)

## [1] "Date"      "Site"      "record_id" "Season"    "DO_mgl"

Rename inside select

# Rename while selecting — new_name = old_name
wq_raw |>
  select(
    site        = Site,
    date        = Date,
    do_mgl      = DO_mgl,
    temp_c      = Temp_C,
    cond_us_cm  = Cond_uScm
  ) |>
  glimpse()

## Rows: 240
## Columns: 5
## $ site       <chr> "Upper_Madera", "Lower Madera", "Upper_Madera", "Upper_Made…
## $ date       <date> 2023-07-20, 2023-05-27, 2023-05-30, 2023-10-27, 2023-08-08…
## $ do_mgl     <dbl> 8.48, 6.65, 11.13, 6.59, 9.70, 7.72, 8.02, 10.51, 10.38, 6.…
## $ temp_c     <dbl> 5.6, 15.5, 10.3, 14.4, 13.2, 17.6, 15.1, 14.6, 14.1, 15.6, …
## $ cond_us_cm <dbl> 368, 342, 314, 210, 267, 270, 396, 307, 310, 277, 319, 207,…

✅ rename() for renaming without dropping

select() drops columns not mentioned. If you only want to rename without dropping, use rename(new = old) instead.

mutate() — Add and Transform Columns

mutate() adds new columns or overwrites existing ones. The new column is available to subsequent expressions in the same mutate() call.

Unit conversions and derived variables

wq <- wq_raw |>
  select(record_id, Site, Date, Season, DO_mgl, Temp_C, pH, Cond_uScm) |>
  mutate(
    # Convert Celsius to Fahrenheit
    Temp_F      = round(Temp_C * 9/5 + 32, 1),

    # Theoretical DO saturation (simplified Benson & Krause eq.)
    DO_sat_mgl  = round(14.62 - 0.3898 * Temp_C + 0.006969 * Temp_C^2 -
                          0.00005896 * Temp_C^3, 2),

    # Percent saturation — uses DO_sat_mgl defined just above
    DO_pct_sat  = round(100 * DO_mgl / DO_sat_mgl, 1),

    # Hypoxia flag
    hypoxic     = DO_mgl < 5
  )

wq |> select(Temp_C, Temp_F, DO_mgl, DO_sat_mgl, DO_pct_sat, hypoxic) |> head(5)

## # A tibble: 5 × 6
##   Temp_C Temp_F DO_mgl DO_sat_mgl DO_pct_sat hypoxic
##    <dbl>  <dbl>  <dbl>      <dbl>      <dbl> <lgl>  
## 1    5.6   42.1   8.48       12.6       67   FALSE  
## 2   15.5   59.9   6.65       10.0       66.3 FALSE  
## 3   10.3   50.5  11.1        11.3       98.7 FALSE  
## 4   14.4   57.9   6.59       10.3       64.1 FALSE  
## 5   13.2   55.8   9.7        10.6       91.9 FALSE

if_else() and case_when() inside mutate

if_else() handles a single TRUE/FALSE condition. case_when() handles multiple ordered conditions — the equivalent of an if–else if–else chain.

wq <- wq |>
  mutate(
    # Two-level classification
    DO_class_2 = if_else(DO_mgl >= 7, "Good", "Impaired"),

    # Multi-level classification (EPA-style)
    DO_class = case_when(
      DO_mgl >= 8             ~ "Excellent",
      DO_mgl >= 6             ~ "Good",
      DO_mgl >= 4             ~ "Fair",
      DO_mgl >= 2             ~ "Poor",
      DO_mgl < 2              ~ "Critically low",
      is.na(DO_mgl)           ~ NA_character_
    )
  )

wq |> count(DO_class)

## # A tibble: 4 × 2
##   DO_class      n
##   <chr>     <int>
## 1 Excellent   124
## 2 Fair         20
## 3 Good         80
## 4 <NA>         16

across() — apply the same transformation to multiple columns

# Round all numeric columns to 2 decimal places in one call
wq |>
  mutate(across(where(is.numeric), \(x) round(x, 2))) |>
  glimpse()

## Rows: 240
## Columns: 14
## $ record_id  <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, …
## $ Site       <chr> "Upper_Madera", "Lower Madera", "Upper_Madera", "Upper_Made…
## $ Date       <date> 2023-07-20, 2023-05-27, 2023-05-30, 2023-10-27, 2023-08-08…
## $ Season     <chr> "Spring", "Spring", "Spring", "Summer", "Summer", "Spring",…
## $ DO_mgl     <dbl> 8.48, 6.65, 11.13, 6.59, 9.70, 7.72, 8.02, 10.51, 10.38, 6.…
## $ Temp_C     <dbl> 5.6, 15.5, 10.3, 14.4, 13.2, 17.6, 15.1, 14.6, 14.1, 15.6, …
## $ pH         <dbl> 7.22, 7.12, 7.94, NA, 7.85, 7.36, 8.01, 7.94, 7.33, 7.43, 7…
## $ Cond_uScm  <dbl> 368, 342, 314, 210, 267, 270, 396, 307, 310, 277, 319, 207,…
## $ Temp_F     <dbl> 42.1, 59.9, 50.5, 57.9, 55.8, 63.7, 59.2, 58.3, 57.4, 60.1,…
## $ DO_sat_mgl <dbl> 12.65, 10.03, 11.28, 10.28, 10.55, 9.60, 10.12, 10.23, 10.3…
## $ DO_pct_sat <dbl> 67.0, 66.3, 98.7, 64.1, 91.9, 80.4, 79.2, 102.7, 100.4, 63.…
## $ hypoxic    <lgl> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FAL…
## $ DO_class_2 <chr> "Good", "Impaired", "Good", "Impaired", "Good", "Good", "Go…
## $ DO_class   <chr> "Excellent", "Good", "Excellent", "Good", "Excellent", "Goo…

# Standardise (z-score) the measurement columns
wq |>
  mutate(across(c(DO_mgl, Temp_C, pH, Cond_uScm),
                \(x) as.numeric(scale(x)),
                .names = "{.col}_z")) |>
  select(ends_with("_z")) |>
  summary()

##     DO_mgl_z           Temp_C_z             pH_z           Cond_uScm_z      
##  Min.   :-2.41760   Min.   :-2.73131   Min.   :-2.56988   Min.   :-2.43995  
##  1st Qu.:-0.65410   1st Qu.:-0.71297   1st Qu.:-0.76299   1st Qu.:-0.65901  
##  Median : 0.06773   Median : 0.06331   Median : 0.06389   Median : 0.03106  
##  Mean   : 0.00000   Mean   : 0.00000   Mean   : 0.00000   Mean   : 0.00000  
##  3rd Qu.: 0.68993   3rd Qu.: 0.63259   3rd Qu.: 0.73765   3rd Qu.: 0.72112  
##  Max.   : 2.55305   Max.   : 2.85794   Max.   : 2.45266   Max.   : 3.06404  
##  NA's   :16         NA's   :5          NA's   :8

summarise() and group_by()

summarise() collapses a data frame to one row per combination of grouping variables. It is almost always paired with group_by().

Basic grouped summary

site_summary <- wq |>
  filter(!is.na(DO_mgl)) |>
  group_by(Site, Season) |>
  summarise(
    n          = n(),
    DO_mean    = mean(DO_mgl),
    DO_sd      = sd(DO_mgl),
    DO_se      = DO_sd / sqrt(n),
    DO_min     = min(DO_mgl),
    DO_max     = max(DO_mgl),
    pct_hypoxic = mean(hypoxic, na.rm = TRUE) * 100,
    .groups    = "drop"   # always include — removes grouping after summarise
  ) |>
  mutate(across(where(is.double), \(x) round(x, 2)))

site_summary

## # A tibble: 16 × 9
##    Site         Season     n DO_mean DO_sd DO_se DO_min DO_max pct_hypoxic
##    <chr>        <chr>  <int>   <dbl> <dbl> <dbl>  <dbl>  <dbl>       <dbl>
##  1 Lower Madera Spring    16    7.72  1.62  0.41   4.89  10.9         6.25
##  2 Lower Madera Summer    14    8.48  1.06  0.28   6.41   9.58        0   
##  3 Lower_Madera Spring    14    8.44  1.81  0.48   5.99  11.8         0   
##  4 Lower_Madera Summer    18    8.23  1.32  0.31   5.99  11.2         0   
##  5 Mid Reach    Spring     9    8.05  0.83  0.28   6.47   9.22        0   
##  6 Mid Reach    Summer    10    7.63  1.67  0.53   5.53  10.1         0   
##  7 Mid_Reach    Spring    13    7.51  1.73  0.48   5.57  10.4         0   
##  8 Mid_Reach    Summer    14    7.71  1.29  0.35   5.01   9.44        0   
##  9 Tributary_N  Spring     8    8.1   0.8   0.28   7.3    9.39        0   
## 10 Tributary_N  Summer    10    7.52  1.38  0.44   4.79   9.17       10   
## 11 Tributary_S  Spring     7    7.52  1.77  0.67   4.66  10.0        14.3 
## 12 Tributary_S  Summer    20    8.48  1.17  0.26   5.39  10.1         0   
## 13 Upper_Madera Spring    12    8.45  1.66  0.48   5.08  11.1         0   
## 14 Upper_Madera Summer    20    8.4   1.44  0.32   5.62  10.5         0   
## 15 upper_madera Spring    17    8.02  1.69  0.41   5.83  11.1         0   
## 16 upper_madera Summer    22    8.52  1.17  0.25   6.29  10.8         0

📌 Always use .groups = “drop”

Without .groups = "drop", summarise() leaves the data partially grouped by all but the last grouping variable. This causes subtle bugs downstream — a subsequent mutate() will operate within those silent groups rather than on the whole table. Dropping explicitly is always safer.

Visualise the summary

site_summary |>
  mutate(Site = str_replace_all(Site, "_", " ") |> str_to_title()) |>
  ggplot(aes(Site, DO_mean, colour = Season, group = Season)) +
  geom_hline(yintercept = 6, linetype = "dashed",
             colour = "#c8832a", linewidth = 0.8) +
  geom_errorbar(aes(ymin = DO_mean - DO_se, ymax = DO_mean + DO_se),
                width = 0.25, position = position_dodge(0.5)) +
  geom_point(size = 3, position = position_dodge(0.5)) +
  scale_colour_manual(values = c("#4a7c5e", "#c8832a")) +
  labs(x = NULL, y = "Dissolved oxygen (mg/L)",
       title = "Mean DO by site and season",
       subtitle = "Dashed line = 6 mg/L aquatic life support threshold") +
  theme_minimal(base_size = 13) +
  theme(
    axis.text.x   = element_text(angle = 30, hjust = 1),
    legend.position = "top",
    plot.title    = element_text(family = "serif", face = "bold", colour = "#1e3a2f"),
    plot.subtitle = element_text(colour = "#7a7a72", size = 10)
  )

Mean dissolved oxygen ± 1 SE by site and season. Error bars represent ±1 standard error. The dashed line marks the 6 mg/L threshold for aquatic life support.

count() and tally() — quick frequency tables

# count() = group_by() + summarise(n = n()) + ungroup()
wq_raw |> count(Site, sort = TRUE)

## # A tibble: 8 × 2
##   Site             n
##   <chr>        <int>
## 1 upper_madera    42
## 2 Upper_Madera    34
## 3 Lower_Madera    33
## 4 Lower Madera    31
## 5 Mid_Reach       29
## 6 Tributary_S     29
## 7 Tributary_N     22
## 8 Mid Reach       20

# count with a condition
wq |>
  count(Site, hypoxic) |>
  filter(hypoxic) |>
  arrange(desc(n))

## # A tibble: 3 × 3
##   Site         hypoxic     n
##   <chr>        <lgl>   <int>
## 1 Lower Madera TRUE        1
## 2 Tributary_N  TRUE        1
## 3 Tributary_S  TRUE        1

Grouped mutate — running statistics within groups

group_by() is not limited to summarise(). A grouped mutate() computes within-group values and attaches them back to every row — useful for anomaly detection and standardisation.

wq <- wq |>
  group_by(Site) |>
  mutate(
    # z-score relative to each site's own mean and SD
    DO_site_z = (DO_mgl - mean(DO_mgl, na.rm = TRUE)) /
                  sd(DO_mgl, na.rm = TRUE),
    # Flag values > 2 SD from the site mean
    DO_outlier = abs(DO_site_z) > 2
  ) |>
  ungroup()

wq |> filter(DO_outlier) |> select(Site, Date, DO_mgl, DO_site_z)

## # A tibble: 6 × 4
##   Site         Date       DO_mgl DO_site_z
##   <chr>        <date>      <dbl>     <dbl>
## 1 Tributary_N  2023-07-23   4.79     -2.56
## 2 Tributary_S  2023-08-10   5.39     -2.06
## 3 Tributary_S  2023-07-28   4.66     -2.59
## 4 Lower Madera 2023-08-28   4.89     -2.24
## 5 Upper_Madera 2023-08-10   5.08     -2.23
## 6 Lower_Madera 2023-10-19  11.8       2.25

Joins — Combining Data Frames

Field data rarely lives in a single table. You might have water quality readings in one file and site metadata (watershed, elevation, land use) in another. Joins merge tables by a shared key column.

Simulate a site metadata table

site_meta <- tribble(
  ~site_clean,     ~watershed,    ~elev_m,  ~land_use,
  "Upper Madera",  "Madera Cr.",  1240,     "Forest",
  "Mid Reach",     "Madera Cr.",   820,     "Mixed",
  "Lower Madera",  "Madera Cr.",   410,     "Agriculture",
  "Tributary N",   "North Fork",   950,     "Chaparral",
  "Tributary S",   "South Fork",   880,     "Urban"
)
site_meta

## # A tibble: 5 × 4
##   site_clean   watershed  elev_m land_use   
##   <chr>        <chr>       <dbl> <chr>      
## 1 Upper Madera Madera Cr.   1240 Forest     
## 2 Mid Reach    Madera Cr.    820 Mixed      
## 3 Lower Madera Madera Cr.    410 Agriculture
## 4 Tributary N  North Fork    950 Chaparral  
## 5 Tributary S  South Fork    880 Urban

Clean site names before joining

Join keys must match exactly. Our Site column has inconsistent casing and separators — fix it first:

wq <- wq |>
  mutate(
    # Standardise: replace underscores with spaces, then title-case
    site_clean = Site |>
      str_replace_all("_", " ") |>
      str_to_title() |>
      str_trim()          # remove any trailing whitespace
  )

wq |> count(site_clean)

## # A tibble: 5 × 2
##   site_clean       n
##   <chr>        <int>
## 1 Lower Madera    64
## 2 Mid Reach       49
## 3 Tributary N     22
## 4 Tributary S     29
## 5 Upper Madera    76

left_join() — keep all left-table rows

wq_joined <- wq |>
  left_join(site_meta, by = "site_clean")

wq_joined |>
  select(site_clean, watershed, elev_m, land_use, DO_mgl) |>
  head(6)

## # A tibble: 6 × 5
##   site_clean   watershed  elev_m land_use    DO_mgl
##   <chr>        <chr>       <dbl> <chr>        <dbl>
## 1 Upper Madera Madera Cr.   1240 Forest        8.48
## 2 Lower Madera Madera Cr.    410 Agriculture   6.65
## 3 Upper Madera Madera Cr.   1240 Forest       11.1 
## 4 Upper Madera Madera Cr.   1240 Forest        6.59
## 5 Upper Madera Madera Cr.   1240 Forest        9.7 
## 6 Mid Reach    Madera Cr.    820 Mixed         7.72

Join types at a glance

dplyr join functions and when to use each.
Function	Rows kept	Typical use
`left_join(x, y)`	All rows of x; matched rows of y	Add metadata to measurements (most common)
`right_join(x, y)`	All rows of y; matched rows of x	Rare — reverse of left_join
`inner_join(x, y)`	Only rows present in both x and y	Strict inner merge, drop unmatched
`full_join(x, y)`	All rows of both x and y	Consolidate two incomplete tables
`anti_join(x, y)`	Rows of x with NO match in y	Find records missing from a reference table
`semi_join(x, y)`	Rows of x WITH a match in y (no y columns added)	Filter x to matched rows without adding columns

⚠️ Check row counts before and after joining

A left_join() can silently multiply rows if the right table has duplicate keys. Always compare nrow() before and after:

nrow(wq)          # before
nrow(wq_joined)   # after — should be the same for a clean left join

If the count increases, your right table has duplicate keys. Investigate with wq_joined |> filter(duplicated(record_id)).

Pivoting with tidyr

Wide vs. tidy (long) format

Environmental data is often delivered in wide format — one row per site, one column per date or parameter. Most R analysis and ggplot2 plotting expects tidy (long) format — one row per observation, one column per variable.

Wide format

Site	DO_May	DO_Jun	DO_Jul
Upper	8.2	7.5	6.8
Mid	7.8	7.1	6.4
Lower	6.9	6.2	5.7

pivot_longer() → pivot_wider() ←

Tidy (long) format

Site	month	DO_mgl
Upper	May	8.2
Upper	Jun	7.5
Upper	Jul	6.8
Mid	May	7.8
Mid	Jun	7.1
Mid	Jul	6.4
Lower	May	6.9
Lower	Jun	6.2
Lower	Jul	5.7

pivot_longer() — wide to long

The most common reshape operation: turn multiple value columns into key–value pairs.

# Simulate a wide format sensor export (common from dataloggers)
wq_wide <- wq |>
  filter(!is.na(DO_mgl), !is.na(Temp_C), !is.na(pH)) |>
  select(record_id, site_clean, Date, DO_mgl, Temp_C, pH) |>
  slice_head(n = 30)          # small example

# Reshape to long — one row per measurement
wq_long <- wq_wide |>
  pivot_longer(
    cols      = c(DO_mgl, Temp_C, pH),  # columns to pivot
    names_to  = "parameter",             # new column for old column names
    values_to = "value"                  # new column for values
  )

head(wq_long, 9)

## # A tibble: 9 × 5
##   record_id site_clean   Date       parameter value
##       <int> <chr>        <date>     <chr>     <dbl>
## 1         1 Upper Madera 2023-07-20 DO_mgl     8.48
## 2         1 Upper Madera 2023-07-20 Temp_C     5.6 
## 3         1 Upper Madera 2023-07-20 pH         7.22
## 4         2 Lower Madera 2023-05-27 DO_mgl     6.65
## 5         2 Lower Madera 2023-05-27 Temp_C    15.5 
## 6         2 Lower Madera 2023-05-27 pH         7.12
## 7         3 Upper Madera 2023-05-30 DO_mgl    11.1 
## 8         3 Upper Madera 2023-05-30 Temp_C    10.3 
## 9         3 Upper Madera 2023-05-30 pH         7.94

Long format enables faceted multi-parameter plots with a single ggplot() call:

wq_long |>
  ggplot(aes(Date, value, colour = site_clean)) +
  geom_point(alpha = 0.5, size = 1.5) +
  geom_smooth(method = "loess", se = FALSE, linewidth = 0.7) +
  facet_wrap(~ parameter, scales = "free_y", ncol = 1,
             labeller = labeller(parameter = c(
               DO_mgl = "DO (mg/L)",
               Temp_C = "Temperature (°C)",
               pH     = "pH"
             ))) +
  scale_colour_brewer(palette = "Dark2", name = "Site") +
  labs(x = NULL, y = NULL,
       title = "Water quality parameters over time") +
  theme_minimal(base_size = 12) +
  theme(
    legend.position = "top",
    strip.text      = element_text(family = "mono", face = "bold",
                                   colour = "#1e3a2f"),
    plot.title      = element_text(family = "serif", face = "bold",
                                   colour = "#1e3a2f")
  )

All three water quality parameters faceted in a single plot — only possible cleanly in long format. Each facet scales independently with scales = 'free_y'.

pivot_wider() — long to wide

The reverse: spread a key–value pair across multiple columns. Common for creating summary tables and for functions that expect wide input (e.g., distance matrices).

# Monthly mean DO per site — from long to wide summary table
monthly_mean <- wq |>
  filter(!is.na(DO_mgl)) |>
  mutate(Month = month(Date, label = TRUE, abbr = TRUE)) |>
  group_by(site_clean, Month) |>
  summarise(mean_DO = round(mean(DO_mgl), 2), .groups = "drop")

# Pivot so months become columns
monthly_wide <- monthly_mean |>
  pivot_wider(
    names_from  = Month,
    values_from = mean_DO
  )

monthly_wide

## # A tibble: 5 × 7
##   site_clean     May   Jun   Jul   Aug   Sep   Oct
##   <chr>        <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Lower Madera  7.98  8.68  8.63  7.63  7.78  8.82
## 2 Mid Reach     8.59  7.2   8.62  7.5   7.23  7.54
## 3 Tributary N   7.34  8.37  6.61  8.2   7.46  8.29
## 4 Tributary S   9.03  8.29  8.07  7.72  7.74  8.45
## 5 Upper Madera  8.77  7.96  9.12  7.87  8.46  7.81

separate() and unite() — splitting and combining columns

Field data often stores multiple pieces of information in one column (e.g., "Site_A_2023-05") or needs them combined for a unique key.

# separate() — split one column into multiple
wq_raw |>
  select(record_id, Sampler, Date) |>
  mutate(
    # Create a combined site-date label for illustration
    site_date = paste0("Madera_", format(Date, "%Y%m%d"))
  ) |>
  separate(site_date,
           into = c("stream", "date_compact"),
           sep  = "_",
           extra = "merge") |>    # merge extra pieces rather than error
  head(4)

## # A tibble: 4 × 5
##   record_id Sampler Date       stream date_compact
##       <int> <chr>   <date>     <chr>  <chr>       
## 1         1 "CK"    2023-07-20 Madera 20230720    
## 2         2 "CK"    2023-05-27 Madera 20230527    
## 3         3 "AJ "   2023-05-30 Madera 20230530    
## 4         4 "AJ "   2023-10-27 Madera 20231027

# unite() — combine columns into one
wq |>
  unite(col    = "site_season",
        site_clean, Season,
        sep    = " — ") |>
  select(site_season, DO_mgl) |>
  head(4)

## # A tibble: 4 × 2
##   site_season           DO_mgl
##   <chr>                  <dbl>
## 1 Upper Madera — Spring   8.48
## 2 Lower Madera — Spring   6.65
## 3 Upper Madera — Spring  11.1 
## 4 Upper Madera — Summer   6.59

String Cleaning with stringr

The stringr package (loaded with tidyverse) provides consistent functions for working with character columns. All functions start with str_.

The sampler name problem

Our dataset has a trailing-space duplicate: "AJ " and "AJ" are treated as different values.

wq_raw |> count(Sampler, sort = TRUE)   # "AJ " and "AJ" appear separately

## # A tibble: 4 × 2
##   Sampler     n
##   <chr>   <int>
## 1 "AJ"       63
## 2 "AJ "      63
## 3 "CK"       59
## 4 "BL"       55

wq_clean_names <- wq_raw |>
  mutate(
    Sampler = Sampler |>
      str_trim()         |>   # strip leading/trailing whitespace
      str_to_upper()           # standardise to upper case
  )

wq_clean_names |> count(Sampler)

## # A tibble: 3 × 2
##   Sampler     n
##   <chr>   <int>
## 1 AJ        126
## 2 BL         55
## 3 CK         59

Key stringr functions for field data cleaning

Frequently used stringr functions for cleaning environmental data.
Function	What it does	Field data example
`str_trim()`	Remove leading and trailing whitespace	`str_trim("AJ ")` → `"AJ"`
`str_squish()`	Trim + collapse internal whitespace runs to single space	`str_squish("San Gabriel")` → `"San Gabriel"`
`str_to_lower()` / `str_to_title()`	Standardise case	`str_to_title("upper_madera")` → `"Upper_Madera"`
`str_replace(string, pattern, replacement)`	Replace first match of pattern	`str_replace("DO_mg_L", "_", ".")` → `"DO.mg_L"`
`str_replace_all()`	Replace ALL matches of pattern	`str_replace_all("DO_mg_L", "_", ".")` → `"DO.mg.L"`
`str_detect(string, pattern)`	Returns TRUE/FALSE — use inside filter()	`str_detect(site, "Madera")` → `TRUE/FALSE`
`str_extract(string, pattern)`	Pull the first match out as a new string	`str_extract(label, "[0-9]+")` → extracts numeric ID
`str_pad(string, width, side)`	Pad to a fixed width (e.g., leading zeros on IDs)	`str_pad("7", 3, "left", "0")` → `"007"`
`str_c(..., sep)`	Paste strings together (like paste())	`str_c("Site", 1:3, sep = "_")` → `"Site_1"` …

A Complete Cleaning Workflow

Putting it all together: a realistic pipeline from messy raw import to analysis-ready data frame and summary plot.

# ── Complete cleaning pipeline for wq_raw ─────────────────────────

wq_clean <- wq_raw |>

  # 1. Standardise column names (snake_case)
  rename_with(tolower) |>                      # all lowercase
  rename(do_mgl    = do_mgl,
         temp_c    = temp_c,
         cond_uscm = cond_uscm,
         turb_ntu  = turbidity_ntu) |>

  # 2. Fix site name inconsistencies
  mutate(
    site = site |>
      str_replace_all("_", " ") |>
      str_squish()              |>
      str_to_title()
  ) |>

  # 3. Fix sampler labels
  mutate(sampler = str_trim(str_to_upper(sampler))) |>

  # 4. Parse date, extract temporal components
  mutate(
    date  = as.Date(date),
    year  = year(date),
    month = month(date, label = TRUE, abbr = TRUE),
    doy   = yday(date)
  ) |>

  # 5. Drop flagged and missing-DO records
  filter(is.na(qc_flag), !is.na(do_mgl)) |>
  select(-qc_flag) |>

  # 6. Physical and derived variables
  mutate(
    do_sat    = round(14.62 - 0.3898 * temp_c + 0.006969 * temp_c^2 -
                        0.00005896 * temp_c^3, 2),
    do_pct    = round(100 * do_mgl / do_sat, 1),
    do_class  = case_when(
      do_mgl >= 8 ~ "Excellent",
      do_mgl >= 6 ~ "Good",
      do_mgl >= 4 ~ "Fair",
      TRUE        ~ "Poor"
    ),
    do_class = factor(do_class,
                      levels = c("Poor","Fair","Good","Excellent"))
  ) |>

  # 7. Within-site anomaly flag
  group_by(site) |>
  mutate(
    do_z      = (do_mgl - mean(do_mgl, na.rm = TRUE)) /
                  sd(do_mgl, na.rm = TRUE),
    outlier   = abs(do_z) > 2.5
  ) |>
  ungroup() |>

  # 8. Select and order final columns
  select(record_id, site, date, year, month, doy, season,
         sampler, do_mgl, do_sat, do_pct, do_class, do_z, outlier,
         temp_c, ph, cond_uscm, turb_ntu)

glimpse(wq_clean)

## Rows: 180
## Columns: 18
## $ record_id <int> 1, 2, 3, 4, 7, 9, 10, 12, 14, 16, 17, 18, 19, 20, 22, 25, 27…
## $ site      <chr> "Upper Madera", "Lower Madera", "Upper Madera", "Upper Mader…
## $ date      <date> 2023-07-20, 2023-05-27, 2023-05-30, 2023-10-27, 2023-09-22,…
## $ year      <dbl> 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, …
## $ month     <ord> Jul, May, May, Oct, Sep, May, Sep, Jul, Jun, May, Sep, Oct, …
## $ doy       <dbl> 201, 147, 150, 300, 265, 133, 246, 183, 181, 121, 265, 281, …
## $ season    <chr> "Spring", "Spring", "Spring", "Summer", "Summer", "Spring", …
## $ sampler   <chr> "CK", "CK", "AJ", "AJ", "BL", "AJ", "CK", "BL", "CK", "BL", …
## $ do_mgl    <dbl> 8.48, 6.65, 11.13, 6.59, 8.02, 10.38, 6.34, 9.34, 7.64, 9.01…
## $ do_sat    <dbl> 12.65, 10.03, 11.28, 10.28, 10.12, 10.34, 10.01, 9.23, 9.38,…
## $ do_pct    <dbl> 67.0, 66.3, 98.7, 64.1, 79.2, 100.4, 63.3, 101.2, 81.4, 84.6…
## $ do_class  <fct> Excellent, Good, Excellent, Good, Excellent, Excellent, Good…
## $ do_z      <dbl> 0.14276682, -1.09650895, 1.93217526, -1.13345279, -0.1678474…
## $ outlier   <lgl> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALS…
## $ temp_c    <dbl> 5.6, 15.5, 10.3, 14.4, 15.1, 14.1, 15.6, 19.5, 18.7, 12.8, 2…
## $ ph        <dbl> 7.22, 7.12, 7.94, NA, 8.01, 7.33, 7.43, 6.83, 7.72, 7.66, 8.…
## $ cond_uscm <dbl> 368, 342, 314, 210, 396, 310, 277, 207, 364, 308, 370, 247, …
## $ turb_ntu  <dbl> 10.3, 5.2, 5.5, 9.6, 13.1, 8.7, 1.1, 9.4, 10.8, 4.1, 12.9, 7…

# Stacked proportion chart by site and DO class
wq_clean |>
  count(site, do_class) |>
  group_by(site) |>
  mutate(pct = n / sum(n) * 100) |>
  ungroup() |>
  ggplot(aes(fct_reorder(site, pct, \(x) x[4]),  # order by Excellent %
             pct, fill = do_class)) +
  geom_col(width = 0.7) +
  scale_fill_manual(
    values = c(Poor = "#c0392b", Fair = "#e8a84a",
               Good = "#4a7c5e", Excellent = "#1e3a2f"),
    name   = "DO class"
  ) +
  coord_flip() +
  labs(x = NULL, y = "Percentage of records (%)",
       title = "DO quality class distribution by site") +
  theme_minimal(base_size = 13) +
  theme(
    legend.position = "top",
    panel.grid.major.y = element_blank(),
    plot.title = element_text(family = "serif", face = "bold", colour = "#1e3a2f")
  )

$Distribution of dissolved oxygen measurements by site and DO quality class after the full cleaning pipeline. The stacked proportion bars reveal which sites have the highest fraction of 'Good' and 'Excellent' readings relative to the less-oxygenated sites.$

Distribution of dissolved oxygen measurements by site and DO quality class after the full cleaning pipeline. The stacked proportion bars reveal which sites have the highest fraction of ‘Good’ and ‘Excellent’ readings relative to the less-oxygenated sites.

🔑 Tidyverse wrangling — key takeaways

Pipes (|> or %>%) chain verbs into readable top-to-bottom sequences. Each step receives a data frame and returns a data frame.
filter() keeps rows. Use %in% for sets, str_detect() for patterns, between() for ranges, is.na() for missingness.
select() keeps or drops columns. Use helper functions (starts_with(), contains(), where()) to avoid typing every column name.
mutate() adds or overwrites columns. case_when() handles multi-condition classification. across() applies one function to many columns at once.
group_by() + summarise() compute grouped statistics. Always include .groups = “drop”. Use grouped mutate() for within-group anomaly detection.
left_join() is the workhorse for attaching metadata. Always check row counts before and after to catch duplicate keys.
pivot_longer() reshapes wide data to tidy long format — required for multi-parameter ggplot2 facets and most statistical models. pivot_wider() goes the other direction for summary tables.
stringr: str_trim(), str_squish(), str_to_title(), and str_replace_all() fix the most common field-data string problems.

Quick-reference cheatsheet

Tidyverse quick-reference: one line per common wrangling task.
Task	Code
Keep rows matching a condition	`filter(col > value)`
Keep rows matching a list of values	`filter(col %in% c(...))`
Drop rows where column is NA	`filter(!is.na(col))`
Keep rows where column matches a string pattern	`filter(str_detect(col, "pattern"))`
Keep specific columns	`select(col1, col2)`
Drop specific columns	`select(-col1, -col2)`
Rename a column	`rename(new = old)`
Add a new derived column	`mutate(new_col = expression)`
Classify into categories	`mutate(class = case_when(...))`
Apply a function to many columns	`mutate(across(cols, fn))`
Count rows per group	`count(group_col)`
Summarise by group	`group_by(col) \|> summarise(..., .groups='drop')`
Sort rows	`arrange(col)` / `arrange(desc(col))`
Add metadata from another table	`left_join(df, meta, by = 'key')`
Reshape wide → long	`pivot_longer(cols, names_to, values_to)`
Reshape long → wide	`pivot_wider(names_from, values_from)`
Split one column into two	`separate(col, into, sep)`
Combine two columns into one	`unite(new_col, col1, col2, sep)`
Remove whitespace	`mutate(col = str_trim(col))`
Fix inconsistent separators	`mutate(col = str_replace_all(col, "_", " "))`

What’s next?

Module 01 — Project Structure — where to save the clean data this pipeline produces
Basic Statistics 02 — Data Visualisation — ggplot2 in depth, using the tidy data frames you have built here
Advanced Stats 11 — Multiple Regression & GLMs — statistical modelling starts with clean, tidy input

Tidyverse Data Wrangling

Marc Los Huertos

EA030 · Compiled 2026-04-23

Tidyverse Data Wrangling

The tidyverse grammar

The six core dplyr verbs

The dataset: stream water quality

The Pipe Operator

filter() — Keep Rows

Filtering with %in%

Filtering on string patterns

Filtering on dates

select() — Keep Columns

select() helpers

Rename inside select

mutate() — Add and Transform Columns

Unit conversions and derived variables

if_else() and case_when() inside mutate

across() — apply the same transformation to multiple columns

summarise() and group_by()

Basic grouped summary

Visualise the summary

count() and tally() — quick frequency tables

Grouped mutate — running statistics within groups

Joins — Combining Data Frames

Simulate a site metadata table

Clean site names before joining

left_join() — keep all left-table rows

Join types at a glance

Pivoting with tidyr

Wide vs. tidy (long) format

pivot_longer() — wide to long

pivot_wider() — long to wide

separate() and unite() — splitting and combining columns

String Cleaning with stringr

The sampler name problem

Key stringr functions for field data cleaning

A Complete Cleaning Workflow

🔑 Tidyverse wrangling — key takeaways

Quick-reference cheatsheet

What’s next?