Reshaping with tidyr
Lesson 6 — R Programming
Learning objectives
- State the three rules of tidy data and recognise violations
- Pivot between long and wide with full control over names and types
- Separate and unite columns, including with regex
- Fill, complete and replace missing values deliberately
- Nest and unnest list-columns to run models by group
- Choose long or wide format for BDS analysis datasets
Tidy data
Three rules:
- Each variable is a column
- Each observation is a row
- Each type of observational unit is a table
Most R functions expect this. When something feels awkward — a for loop over columns, a function that takes column names as strings — the data is usually untidy.
# Untidy: values in column names
# USUBJID WEEK0 WEEK4 WEEK8
# 001 120 118 115
# Tidy
# USUBJID WEEK SBP
# 001 0 120
# 001 4 118
# 001 8 115Clinical BDS datasets (ADLB, ADVS, ADEG) are long by design: one row per subject, parameter and timepoint. That is not an accident — it is what makes a single program able to handle any number of parameters.
Long to wide and back
pivot_longer()
library(tidyr)
library(dplyr)
wide <- tibble::tribble(
~USUBJID, ~WEEK0, ~WEEK4, ~WEEK8,
"001", 120, 118, 115,
"002", 135, 132, 130
)
long <- wide |>
pivot_longer(
cols = starts_with("WEEK"),
names_to = "AVISIT",
values_to = "AVAL"
)
long
#> # A tibble: 6 x 3
#> USUBJID AVISIT AVAL
#> <chr> <chr> <dbl>
#> 1 001 WEEK0 120
#> 2 001 WEEK4 118Extract a number from the name while pivoting:
wide |>
pivot_longer(
cols = starts_with("WEEK"),
names_to = "AVISITN",
names_prefix = "WEEK",
names_transform = list(AVISITN = as.integer),
values_to = "AVAL"
)
#> USUBJID AVISITN AVAL
#> <chr> <int> <dbl>
#> 1 001 0 120Split a compound name into several columns:
# Columns named like SBP_WEEK0, DBP_WEEK0, SBP_WEEK4, ...
vitals_wide |>
pivot_longer(
cols = -USUBJID,
names_to = c("PARAMCD", "AVISIT"),
names_sep = "_",
values_to = "AVAL"
)
# Or with a regex
vitals_wide |>
pivot_longer(
cols = -USUBJID,
names_to = c("PARAMCD", "AVISITN"),
names_pattern = "([A-Z]+)_WEEK(\\d+)",
values_to = "AVAL"
)Useful extras: values_drop_na = TRUE removes rows that would be all missing; cols_vary = "slowest" controls the interleaving order.
pivot_wider()
long |>
pivot_wider(
id_cols = USUBJID,
names_from = AVISIT,
values_from = AVAL
)
# Multiple value columns
adlb |>
pivot_wider(
id_cols = c(USUBJID, PARAMCD),
names_from = AVISIT,
values_from = c(AVAL, CHG),
names_glue = "{AVISIT}_{.value}"
)adlb |> pivot_wider(names_from = AVISIT, values_from = AVAL)
#> Warning: Values from `AVAL` are not uniquely identified;
#> output will contain list-cols.This means the combination of id_cols and names_from is not unique — there are two records for the same subject, parameter and visit. That is a data problem, and the warning is telling you about it. Do not silence it with values_fn = mean. Investigate:
adlb |>
count(USUBJID, PARAMCD, AVISIT) |>
filter(n > 1)Unscheduled repeats, re-tests and duplicate loads all show up this way. Decide explicitly which record to keep, in a documented derivation.
Separating and uniting
# One column into several
adae |>
separate_wider_delim(
AEDECOD_SOC, delim = "|",
names = c("AEDECOD", "AESOC")
)
# By position
subjects |>
separate_wider_position(
USUBJID,
widths = c(STUDYID = 8, SITEID = 3, SUBJID = 4)
)
# By regex
subjects |>
separate_wider_regex(
USUBJID,
patterns = c(STUDYID = "[A-Z0-9]+", "-", SITEID = "\\d{3}", "-", SUBJID = "\\d+")
)
# One row into many
adae |> separate_longer_delim(AEDECOD, delim = ";")
# Join columns together
adsl |> unite("SITE_SUBJ", SITEID, SUBJID, sep = "-", remove = FALSE)The older separate() and extract() are superseded but still everywhere in existing code. The separate_wider_* family gives much better error messages when a value does not match the expected shape:
separate_wider_delim(df, x, delim = "-", names = c("a", "b"))
#> Error: Expected 2 pieces in each element of `x`.
#> ! Problems at rows 14, 27.
#> i Use `too_few` / `too_many` to control this.Missing values
Two kinds of missingness:
- Explicit — an
NAsitting in a cell - Implicit — a row that simply is not there
adlb <- tribble(
~USUBJID, ~AVISITN, ~AVAL,
"001", 0, 3.4,
"001", 4, 3.6,
"002", 0, 4.1
)
# Subject 002 has no week 4 row — implicit missingcomplete() makes it explicit:
adlb |> complete(USUBJID, AVISITN)
#> USUBJID AVISITN AVAL
#> 1 001 0 3.4
#> 2 001 4 3.6
#> 3 002 0 4.1
#> 4 002 4 NA <- now visible
adlb |> complete(USUBJID, AVISITN = c(0, 4, 8, 12),
fill = list(AVAL = NA_real_))This matters for a shift table or a completers analysis: a subject who missed a visit must appear as missing, not vanish.
Other tools:
df |> drop_na() # any NA
df |> drop_na(AVAL) # NA in a specific column
df |> replace_na(list(AVAL = 0, CAT = "Unknown"))
df |> fill(TRT01A, .direction = "down") # LOCF-style carry forward
df |> fill(TRT01A, .direction = "downup")
df |> expand(USUBJID, AVISITN) # all combinations, no data
df |> nesting(USUBJID, ARM) # only observed combinationsfill() is not LOCF
fill(.direction = "down") copies the previous non-missing value regardless of how far back it is or which group it came from. For an analysis LOCF you need, at minimum, to group by subject and parameter and to have sorted by date:
adlb |>
arrange(USUBJID, PARAMCD, ADT) |>
group_by(USUBJID, PARAMCD) |>
fill(AVAL, .direction = "down") |>
ungroup()And in a regulated setting, LOCF should be a documented, tested derivation — see admiral::derive_var_extreme_flag() and the imputation functions in ADaM programming with admiral.
Nesting and list-columns
A list-column holds an arbitrary object per row. Combined with purrr, it lets you run a model per group without a loop.
library(purrr)
library(broom)
by_param <- adlb |>
nest(data = -PARAMCD)
by_param
#> # A tibble: 4 x 2
#> PARAMCD data
#> <chr> <list>
#> 1 ALT <tibble [612 x 8]>
#> 2 AST <tibble [612 x 8]>
results <- by_param |>
mutate(
model = map(data, ~ lm(CHG ~ TRT01AN + BASE, data = .x)),
tidied = map(model, tidy),
glanced = map(model, glance)
) |>
select(PARAMCD, tidied) |>
unnest(tidied) |>
filter(term == "TRT01AN")
results
#> # A tibble: 4 x 6
#> PARAMCD term estimate std.error statistic p.value
#> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 ALT TRT01AN -2.31 0.884 -2.61 0.00925Everything stays in one tibble, so the parameter label never gets separated from its result — which is exactly the failure mode of a for loop that accumulates into a list.
unnest_wider() and unnest_longer() handle JSON-shaped data:
api_result |>
unnest_wider(response) |>
unnest_longer(measurements) |>
unnest_wider(measurements)Long or wide for analysis datasets?
| Use long (BDS) when | Use wide (OCCDS/ADSL) when |
|---|---|
| Repeated measures over visits | One row per subject |
| Many parameters, same structure | Attributes, not measurements |
Feeding ggplot2 or a mixed model |
Feeding a wide-format procedure |
| The set of parameters may grow | The variable set is fixed |
CDISC ADaM formalises this: ADSL is one row per subject; BDS datasets (ADLB, ADVS) are long with PARAMCD/AVISIT/AVAL; OCCDS datasets (ADAE) are one row per occurrence. Reshaping is usually the last step before a specific output, not something you do to the analysis dataset itself.
Common mistakes
| Mistake | Consequence | Fix |
|---|---|---|
| Ignoring the list-col warning | Hidden duplicate records | Count the key, investigate |
values_fn = mean to silence it |
Averages away a data issue | Resolve the duplicates properly |
fill() without grouping |
Values leak across subjects | group_by() first |
Losing rows in pivot_wider() |
Missing visits vanish | complete() before pivoting |
separate() on ragged data |
Silent truncation | separate_wider_delim() with too_many |
Nesting without .by awareness |
Grouping carried into the model | nest(data = -key) |
Exercise 6.1 — Wide vitals to BDS
Convert this wide dataset into BDS long format with USUBJID, PARAMCD, AVISITN, AVAL.
vitals <- tibble::tribble(
~USUBJID, ~SBP_W0, ~SBP_W4, ~DBP_W0, ~DBP_W4,
"001", 120, 118, 80, 78,
"002", 135, 132, 88, 85
)Show solution
library(tidyr)
bds <- vitals |>
pivot_longer(
cols = -USUBJID,
names_to = c("PARAMCD", "AVISITN"),
names_pattern = "([A-Z]+)_W(\\d+)",
names_transform = list(AVISITN = as.integer),
values_to = "AVAL"
) |>
arrange(USUBJID, PARAMCD, AVISITN)
bds
#> # A tibble: 8 x 4
#> USUBJID PARAMCD AVISITN AVAL
#> <chr> <chr> <int> <dbl>
#> 1 001 DBP 0 80
#> 2 001 DBP 4 78
#> 3 001 SBP 0 120
#> 4 001 SBP 4 118([A-Z]+)_W(\\d+) has two capture groups matching the two entries in names_to. names_transform converts the captured visit number from character to integer during the pivot rather than in a separate mutate().
Exercise 6.2 — Complete a visit grid
Given bds from the previous exercise, ensure every subject has a row for every combination of parameter and visits 0, 4, 8 and 12, with AVAL missing where no measurement exists. Then add a MISSFL flag.
Show solution
full_grid <- bds |>
complete(
USUBJID,
PARAMCD,
AVISITN = c(0L, 4L, 8L, 12L)
) |>
mutate(MISSFL = if_else(is.na(AVAL), "Y", "N")) |>
arrange(USUBJID, PARAMCD, AVISITN)
full_grid |> count(MISSFL)
#> # A tibble: 2 x 2
#> MISSFL n
#> <chr> <int>
#> 1 N 8
#> 2 Y 24If a subject should only be completed for parameters they actually have, use nesting() to restrict the expansion to observed combinations:
bds |> complete(nesting(USUBJID, PARAMCD), AVISITN = c(0L, 4L, 8L, 12L))complete() on the bare variables gives the full Cartesian product, which can invent subject/parameter pairs that never existed.
Exercise 6.3 — Model per parameter
Using nest() and purrr, fit lm(CHG ~ TRT01AN) separately for each PARAMCD in adlb, and return a tibble with PARAMCD, n, estimate, conf.low, conf.high and p.value for the treatment term.
Show solution
library(dplyr); library(tidyr); library(purrr); library(broom)
results <- adlb |>
filter(!is.na(CHG), AVISITN == 12) |>
nest(data = -PARAMCD) |>
mutate(
n = map_int(data, nrow),
model = map(data, ~ lm(CHG ~ TRT01AN, data = .x)),
coefs = map(model, ~ tidy(.x, conf.int = TRUE))
) |>
select(PARAMCD, n, coefs) |>
unnest(coefs) |>
filter(term == "TRT01AN") |>
select(PARAMCD, n, estimate, conf.low, conf.high, p.value) |>
arrange(p.value)
results
#> # A tibble: 4 x 6
#> PARAMCD n estimate conf.low conf.high p.value
#> <chr> <int> <dbl> <dbl> <dbl> <dbl>
#> 1 ALT 204 -2.31 -4.05 -0.573 0.00925Two robustness notes for real use:
Wrap the fit in
possibly()so one parameter with too few observations does not kill the whole pipeline:safe_lm <- possibly(~ lm(CHG ~ TRT01AN, data = .x), otherwise = NULL)Multiplicity: four models means four p-values. Any inferential claim needs an adjustment (
p.adjust(p.value, method = "BH")) or a pre-specified hierarchy. The code makes it easy to fit many models, which makes it easy to fit too many.
Recap
- Tidy data: one column per variable, one row per observation, one table per unit
pivot_longer()/pivot_wider(), withnames_patternfor compound names- The list-column warning from
pivot_wider()means duplicate keys — investigate, do not aggregate away complete()turns implicit missingness into explicit rowsfill()is not a validated LOCF; group and sort first, or useadmiralnest()+purrrruns a model per group and keeps the labels attached
Next: Dates, strings and factors.