Data manipulation with dplyr

Lesson 5 — R Programming

Lesson 5 of 12 Intermediate ~120 min

Learning objectives

Use the core verbs fluently and compose them with the pipe
Group and summarise, and understand what .by changed
Apply across() to operate on many columns at once
Choose the right join and verify it did what you expected
Use window functions: lag, lead, row_number, cumsum, first, last
Recognise the dplyr idioms that map onto SAS PROC SQL and DATA steps

The grammar

Six verbs cover most of data manipulation:

Verb	Does	SAS equivalent
`filter()`	Keeps rows	`WHERE` / `IF`
`select()`	Keeps columns	`KEEP` / `DROP`
`mutate()`	Adds or changes columns	assignment in a `DATA` step
`arrange()`	Sorts rows	`PROC SORT`
`summarise()`	Collapses to one row per group	`PROC MEANS` / `PROC SQL`
`group_by()`	Sets the grouping	`BY` statement

They all take a data frame first and return a data frame, so they compose:

library(dplyr)

adsl |>
  filter(SAFFL == "Y") |>
  group_by(TRT01A) |>
  summarise(
    n        = n(),
    mean_age = mean(AGE, na.rm = TRUE),
    sd_age   = sd(AGE, na.rm = TRUE),
    .groups  = "drop"
  ) |>
  arrange(TRT01A)

|> or %>%?

|> is base R (4.1+), needs no package, and is slightly faster. %>% is magrittr, allows . placeholders (x %>% f(y, .)) and works with anonymous formulas. Use |> for new code; you will read plenty of %>% in existing code. They are interchangeable for the common case.

`filter()`

adsl |> filter(AGE >= 65)
adsl |> filter(AGE >= 65, SEX == "F")            # comma = AND
adsl |> filter(AGE >= 65 | SEX == "F")           # explicit OR
adsl |> filter(ARM %in% c("Placebo", "Drug A"))
adsl |> filter(!is.na(AGE))
adsl |> filter(between(AGE, 18, 64))
adsl |> filter(if_all(ends_with("FL"), ~ .x == "Y"))
adsl |> filter(if_any(c(AEFL, SAEFL), ~ .x == "Y"))

filter() keeps only rows where the condition is TRUE — NA rows are dropped. If you want them, say so:

adsl |> filter(AGE >= 65 | is.na(AGE))

`select()` and `rename()`

adsl |> select(USUBJID, AGE, SEX)
adsl |> select(-STUDYID)
adsl |> select(USUBJID:AGE)                 # range of columns
adsl |> select(starts_with("TRT"))
adsl |> select(ends_with("FL"))
adsl |> select(contains("DT"))
adsl |> select(matches("^A[GE|VAL]"))       # regex
adsl |> select(where(is.numeric))
adsl |> select(USUBJID, everything())       # move to front
adsl |> select(subject = USUBJID, age = AGE) # select + rename
adsl |> rename(subject = USUBJID)            # rename, keep all
adsl |> relocate(TRT01P, .after = USUBJID)

`mutate()`

adsl |>
  mutate(
    AGEGR1 = case_when(
      AGE < 18  ~ "<18",
      AGE < 65  ~ "18-64",
      AGE >= 65 ~ ">=65",
      .default  = NA_character_
    ),
    BMI      = WEIGHTBL / (HEIGHTBL / 100)^2,
    BMI_R    = round(BMI, 1),                    # can use BMI immediately
    SAFFL    = if_else(!is.na(TRTSDT), "Y", "N")
  )

Columns are created in order and later expressions can reference earlier ones. Control placement with .before / .after, and keep only what you made with .keep:

adsl |> mutate(BMI = WEIGHTBL / (HEIGHTBL/100)^2, .after = WEIGHTBL)
adsl |> mutate(BMI = WEIGHTBL / (HEIGHTBL/100)^2, .keep = "used")

`case_when()` details

case_when(
  AGE < 18            ~ "child",
  AGE < 65            ~ "adult",
  AGE >= 65           ~ "elderly",
  .default            = "unknown"
)

Rules: evaluated top to bottom, first match wins; all right-hand sides must be the same type; unmatched rows get .default (or NA if omitted). The old TRUE ~ value catch-all still works but .default is clearer.

A common error:

case_when(
  AGE < 18 ~ "child",
  AGE < 65 ~ 1          # Error: can't combine <character> and <double>
)

`group_by()` and `summarise()`

adae |>
  group_by(USUBJID, AEDECOD) |>
  summarise(
    n_events  = n(),
    max_sev   = max(AESEVN, na.rm = TRUE),
    any_ser   = any(AESER == "Y"),
    .groups   = "drop"
  )

summarise() peels off one grouping level by default, which is a frequent source of confusion. Be explicit with .groups:

"drop" — return ungrouped (usually what you want)
"drop_last" — remove the last grouping variable (the default)
"keep" — retain all groups

The `.by` argument

Since dplyr 1.1 you can group inline, per-verb, with no lingering state:

# Old
adae |>
  group_by(USUBJID) |>
  summarise(n = n()) |>
  ungroup()

# New
adae |> summarise(n = n(), .by = USUBJID)

# Works in mutate/filter/slice too
adlb |> mutate(baseline = AVAL[AVISITN == 0], .by = c(USUBJID, PARAMCD))
adae |> filter(AESTDT == min(AESTDT), .by = USUBJID)

.by always returns ungrouped output. Prefer it for new code — forgetting to ungroup() is one of the most common dplyr bugs, and .by makes it impossible.

Counting

adsl |> count(ARM)
adsl |> count(ARM, SEX)
adsl |> count(ARM, sort = TRUE)
adsl |> count(ARM, wt = N_EVENTS)              # weighted
adsl |> add_count(ARM)                          # keeps all rows, adds n
adae |> distinct(USUBJID, AEDECOD)
adae |> n_distinct(adae$USUBJID)

`across()`

Apply the same operation to many columns:

# Summarise several columns
adsl |>
  summarise(across(c(AGE, WEIGHTBL, HEIGHTBL),
                   ~ mean(.x, na.rm = TRUE)),
            .by = ARM)

# Multiple functions, controlled names
adsl |>
  summarise(
    across(c(AGE, WEIGHTBL),
           list(n    = ~ sum(!is.na(.x)),
                mean = ~ mean(.x, na.rm = TRUE),
                sd   = ~ sd(.x, na.rm = TRUE)),
           .names = "{.col}_{.fn}"),
    .by = ARM
  )
#> # A tibble: 2 x 7
#>   ARM     AGE_n AGE_mean AGE_sd WEIGHTBL_n WEIGHTBL_mean WEIGHTBL_sd

# Transform in place
adsl |> mutate(across(where(is.character), ~ na_if(.x, "")))
adsl |> mutate(across(ends_with("FL"), ~ .x == "Y"))
adsl |> mutate(across(where(is.numeric), ~ round(.x, 2)))

if_all() and if_any() are the filter() counterparts:

adsl |> filter(if_all(c(SAFFL, ITTFL), ~ .x == "Y"))
adsl |> filter(if_any(everything(), is.na))       # rows with any missing

Joins

# Mutating joins — add columns
left_join(adsl, adae, by = "USUBJID")     # all of adsl
inner_join(adsl, adae, by = "USUBJID")    # only matches
right_join(adsl, adae, by = "USUBJID")    # all of adae
full_join(adsl, adae, by = "USUBJID")     # everything

# Filtering joins — keep rows, add nothing
semi_join(adsl, adae, by = "USUBJID")     # subjects WITH an AE
anti_join(adsl, adae, by = "USUBJID")     # subjects WITHOUT an AE

# Different key names
left_join(adsl, sites, by = c("SITEID" = "site_number"))

# Multiple keys
left_join(adlb, ranges, by = c("PARAMCD", "SEX"))

# Inequality and rolling joins (dplyr >= 1.1)
left_join(ae, periods,
          by = join_by(USUBJID, between(AESTDT, APERSDT, APEREDT)))

left_join(ae, dose,
          by = join_by(USUBJID, closest(AESTDT >= EXSTDT)))

Always check the row count after a join

A left_join() on a non-unique key silently multiplies rows. This is the most common way a study analysis silently produces wrong numbers.

nrow(adsl)                          #> 306
result <- left_join(adsl, adae, by = "USUBJID")
nrow(result)                        #> 1847      <- expected, one row per AE

# If you EXPECT one-to-one, enforce it:
left_join(adsl, sites, by = "SITEID",
          relationship = "many-to-one",
          unmatched = "error")

relationship errors if the join is not the cardinality you declared; unmatched = "error" errors if any left row finds no match. Use both. They turn a silent data error into an immediate, located failure.

Diagnose an unexpected fan-out:

adae |> count(USUBJID) |> filter(n > 1)          # the culprits
adsl |> anti_join(adae, by = "USUBJID")          # unmatched left rows
adae |> anti_join(adsl, by = "USUBJID")          # unmatched right rows

Window functions

Functions that return a vector the same length as the input, computed within groups.

adlb |>
  arrange(USUBJID, PARAMCD, ADT) |>
  mutate(
    visit_n     = row_number(),
    prev_value  = lag(AVAL),
    next_value  = lead(AVAL),
    change      = AVAL - lag(AVAL),
    baseline    = first(AVAL),
    chg_from_bl = AVAL - first(AVAL),
    pct_chg     = 100 * (AVAL - first(AVAL)) / first(AVAL),
    cum_max     = cummax(AVAL),
    rank_desc   = min_rank(desc(AVAL)),
    .by = c(USUBJID, PARAMCD)
  )

Window functions depend on row order

lag(), first() and cumsum() operate on the data in its current order. Always arrange() immediately before using them, and include enough keys to make the order deterministic — ties broken arbitrarily give different answers on different machines.

Ranking functions differ in how they handle ties:

x <- c(10, 20, 20, 30)
row_number(x)   #> 1 2 3 4     arbitrary tie-break
min_rank(x)     #> 1 2 2 4     ties get the lowest rank
dense_rank(x)   #> 1 2 2 3     no gaps

`slice` family

adae |> slice_head(n = 5, by = USUBJID)          # first 5 AEs per subject
adae |> slice_max(AESEVN, n = 1, by = USUBJID)   # worst AE per subject
adae |> slice_min(AESTDT, n = 1, by = USUBJID)   # earliest AE
adae |> slice_sample(n = 10)                     # random rows

slice_max(..., with_ties = FALSE) guarantees exactly one row per group. Without it, ties return several — which is usually a bug in a “worst event per subject” derivation.

Coming from SAS

SAS	dplyr
`data b; set a; where age > 65; run;`	`b <- a \|> filter(age > 65)`
`data b; set a; keep usubjid age; run;`	`b <- a \|> select(usubjid, age)`
`data b; set a; bmi = wt/(ht/100)**2; run;`	`b <- a \|> mutate(bmi = wt/(ht/100)^2)`
`proc sort data=a; by usubjid; run;`	`a \|> arrange(usubjid)`
`proc means; class arm; var age; run;`	`a \|> summarise(mean(age), .by = arm)`
`proc sql; select * from a left join b on a.id=b.id;`	`left_join(a, b, by = "id")`
`if first.usubjid;`	`slice_head(n = 1, by = usubjid)`
`retain` + `lag`	`mutate(prev = lag(x), .by = grp)`
`proc freq; tables arm*sex;`	`count(a, arm, sex)`

The important conceptual difference: SAS DATA steps process one row at a time with implicit retain; dplyr operates on whole columns. Anything you would have done with first./last. and retain maps to a window function plus .by.

Common mistakes

Mistake	Consequence	Fix
Forgetting `ungroup()`	Later verbs silently grouped	Use `.by` instead
`mean(x)` without `na.rm`	`NA` result	`mean(x, na.rm = TRUE)`
Join on a non-unique key	Row multiplication	`relationship = "many-to-one"`
Window function without `arrange()`	Non-deterministic results	Sort first, with full keys
`filter()` dropping `NA` unexpectedly	Missing subjects	`\| is.na(x)` if intended
`case_when()` with mixed types	Error	Make all RHS the same type
`n()` when you meant `n_distinct()`	Counts rows, not subjects	`n_distinct(USUBJID)`

Exercise 5.1 — Demographics summary

From adsl, produce a table with one row per treatment arm containing: N, mean and SD of age, count and percentage female, and count and percentage aged 65 or over. Restrict to the safety population (SAFFL == "Y").

Show solution

library(dplyr)

demo_summary <- adsl |>
  filter(SAFFL == "Y") |>
  summarise(
    n          = n(),
    age_mean   = mean(AGE, na.rm = TRUE),
    age_sd     = sd(AGE, na.rm = TRUE),
    n_female   = sum(SEX == "F", na.rm = TRUE),
    n_elderly  = sum(AGE >= 65, na.rm = TRUE),
    .by = TRT01A
  ) |>
  mutate(
    pct_female  = 100 * n_female  / n,
    pct_elderly = 100 * n_elderly / n
  ) |>
  arrange(TRT01A)

demo_summary
#> # A tibble: 3 x 8
#>   TRT01A      n age_mean age_sd n_female n_elderly pct_female pct_elderly
#>   <chr>   <int>    <dbl>  <dbl>    <int>     <int>      <dbl>       <dbl>
#> 1 Placebo    86     75.2   8.59       53        73       61.6        84.9

sum(SEX == "F", na.rm = TRUE) works because logicals coerce to 0/1 — the technique from Lesson 2. Computing percentages in a separate mutate() after the summarise() avoids recomputing n().

Exercise 5.2 — Worst severity per subject per term

From adae, produce one row per subject and preferred term giving the worst severity (AESEVN, higher is worse), the date of the first occurrence, and the number of occurrences. Guarantee exactly one row per subject/term.

Show solution

Two valid approaches.

Summarise — safest, since it cannot produce extra rows:

worst_ae <- adae |>
  summarise(
    n_events  = n(),
    worst_sev = max(AESEVN, na.rm = TRUE),
    first_dt  = min(AESTDT, na.rm = TRUE),
    any_ser   = any(AESER == "Y", na.rm = TRUE),
    .by = c(USUBJID, AEDECOD)
  )

Slice — use when you need other columns from the winning row:

worst_ae <- adae |>
  arrange(USUBJID, AEDECOD, desc(AESEVN), AESTDT) |>
  slice_head(n = 1, by = c(USUBJID, AEDECOD))

The arrange() here is doing the work: sorting by descending severity then ascending date means the first row per group is the worst event, with ties broken by earliest onset. slice_max(AESEVN, n = 1, by = ...) would return all tied rows unless you add with_ties = FALSE.

Verify:

stopifnot(nrow(worst_ae) == nrow(distinct(adae, USUBJID, AEDECOD)))

Exercise 5.3 — Debug a join

This code should add each subject’s treatment arm to the AE dataset. It returns more rows than adae has. Diagnose and fix.

result <- adae |> left_join(adsl, by = "USUBJID")
nrow(adae)     #> 1847
nrow(result)   #> 2103

Show solution

A left_join() returns more rows than the left table only when the right table has duplicate keys. Find them:

adsl |> count(USUBJID) |> filter(n > 1)
#> # A tibble: 12 x 2
#>   USUBJID         n
#>   <chr>       <int>
#> 1 STUDY-001-0042  2
#> ...

adsl should be one row per subject; something upstream duplicated it — typically a screen failure re-enrolled under the same ID, or an earlier join that itself fanned out.

Immediate fix, joining only what you need and enforcing cardinality:

result <- adae |>
  left_join(
    adsl |> distinct(USUBJID, TRT01A, SAFFL),
    by = "USUBJID",
    relationship = "many-to-one"
  )

If adsl genuinely has conflicting values per subject, distinct() will not collapse them and relationship = "many-to-one" will error — which is correct behaviour. You then have to go and fix the source data.

Prevention. Assert the key at the point adsl is created:

stopifnot(!anyDuplicated(adsl$USUBJID))

Selecting only the three columns you need also avoids .x/.y suffix collisions on the dozens of columns the two datasets share.

Recap

Six verbs, all taking and returning a data frame, composed with |>
Prefer .by = over group_by() — it cannot leak grouping state
across() for many columns; if_all()/if_any() for filtering on many
Always check row counts after a join; declare relationship and unmatched
Window functions need an explicit, deterministic arrange() first
slice_max(with_ties = FALSE) when you need exactly one row per group

Next: Reshaping with tidyr.

The grammar

filter()

select() and rename()

mutate()

case_when() details

group_by() and summarise()

The .by argument

Counting

across()

Joins

Window functions

slice family

Coming from SAS

Common mistakes

`filter()`

`select()` and `rename()`

`mutate()`

`case_when()` details

`group_by()` and `summarise()`

The `.by` argument

`across()`

`slice` family