15 Data Wrangling Essentials
The HF-HOME case report form (CRF) export arrives from five hospitals, and each wrote its own conventions into the file. Read it with the defaults and summarize systolic blood pressure at discharge by site. Site D’s mean is 115.8 mmHg, against 123.7 to 124.3 at the other four. That site difference is large enough to earn a sentence in the methods section and a covariate in the model. Nothing in the patients supports it. Site D writes a missing measurement as -99, the default read accepted it as a pressure, and the mean included it. Every decision a cleaning script makes is a decision about what the analysis is of. That includes what a code means, which rows are excluded, and which of two conflicting values survives. These decisions are made before any model and are rarely questioned afterward. This chapter makes them in code, where a reader can see them and count what each one did.
Like families, tidy datasets are all alike but every messy dataset is messy in its own way.
Hadley Wickham, Tidy Data, Journal of Statistical Software (2014)
Reading time. About 25 minutes.
You’ll need. Chapter 13 for reading files deliberately and for the raw-data rule. The code uses:
You’ll be able to.
- Read a raw export so that every missing-value code and site convention stays visible until you decide what it means.
- Recode per-site conventions (sex codes, date formats, missing codes) and verify each recode with a cross-tabulation.
- Choose the long or wide shape for the operation at hand, and find the duplicates that make a pivot fail.
- Report how many rows each filter removes, and save derived data without touching the raw file.
Outline.
If these answers come quickly, skip to the next chapter. The answers are in Section 15.9.
- What is ‘tidy data’ as defined by Wickham (2014) (Wickham, 2014), and how does it differ from a typical ‘wide’ clinical data layout?
- Write the
dplyrpipeline that keeps the patients with an ejection fraction (ef_pct) below 40 and returns their mean discharge systolic blood pressure by site. - What does
tidyr::pivot_longer()do, and when should you reach forpivot_wider()instead?
15.1 Reading the export deliberately
The dplyr and tidyr verbs take an afternoon to learn, and this chapter does not re-teach them.1 What takes longer is deciding what the values mean, and that decision starts at the read. The raw-data rule of Chapter 13 applies throughout: crf_export.csv is evidence, never edited, and everything the script produces is written somewhere else (Habit 2).
1 The generic tour of filter(), select(), mutate(), summarize(), and arrange() is R for Data Science (Wickham et al., 2023, chs. 3 and 5). Readers coming from pandas will find that the verbs map closely onto query(), assign(), and groupby().agg().
15.1.1 What the defaults guessed
readr guesses a type for every column from its first rows. The guesses are usually right about the type and silent about the meaning.
library(tidyverse)
hf_dir <- file.path("..", "data", "raw_data", "hfhome")
crf_default <- read_csv(file.path(hf_dir, "crf_export.csv"),
show_col_types = FALSE)
spec(crf_default)
#> cols(
#> patient_id = col_character(),
#> site = col_character(),
#> sex = col_character(),
#> dob = col_character(),
#> discharge_date = col_character(),
#> ef_pct = col_double(),
#> sbp_discharge = col_double(),
#> weight_kg = col_double()
#> )
crf_default |>
summarize(mean_sbp = mean(sbp_discharge, na.rm = TRUE),
n = n(), .by = site) |>
arrange(site)
#> # A tibble: 15 × 3
#> site mean_sbp n
#> <chr> <dbl> <int>
#> 1 SITE A 114. 16
#> 2 SITE B 127. 15
#> 3 SITE C 122. 16
#> 4 SITE D 107. 15
#> 5 SITE E 134. 6
#> 6 Site A 125. 665
#> 7 Site B 124. 536
#> 8 Site C 124. 466
#> 9 Site D 117. 348
#> 10 Site E 124. 274
#> 11 site a 120. 19
#> 12 site b 124. 20
#> 13 site c 128. 16
#> 14 site d 72.5 10
#> 15 site e 120 10The summary has two problems before it has any results: fifteen spellings of five sites, and a Site D mean several points below the rest. Neither raised a message.
15.1.2 Read everything as text first
A first read for discovery keeps every value as text. It also turns off readr’s own idea of what is missing. The empty strings, the NA strings, and the -99 then all arrive as written and can be counted.
The missing-value codes are a finding, so tabulate them by site. Pivoting the three numeric columns into one makes that a single count():
crf_raw |>
mutate(site = str_to_title(str_squish(site))) |>
pivot_longer(c(ef_pct, sbp_discharge, weight_kg),
names_to = "variable", values_to = "value") |>
filter(value %in% c("", "NA", "-99")) |>
count(site, value)
#> # A tibble: 5 × 3
#> site value n
#> <chr> <chr> <int>
#> 1 Site A "" 23
#> 2 Site B "NA" 26
#> 3 Site C "" 21
#> 4 Site D "-99" 21
#> 5 Site E "NA" 9Each site uses one convention: an empty field at A and C, the string NA at B and E, and -99 at D. Reading as text is Habit 7 in practice: nothing is converted until you have decided what each value means.
A missing-value code that parses as a number becomes data. -99 is a valid double, so the default read stored it as one, and na.rm = TRUE removed only the true NAs. No type check knows that a systolic pressure cannot be negative. Name the codes when you convert (parse_double(x, na = c("", "NA", "-99"))) and then assert the plausible range, so that the next unannounced code stops the script.
15.1.3 Exercises
- For each site, count the missing-value codes in
sbp_dischargeand inweight_kgseparately. Then confirm that the totals equal the number of missing values in the cleanbaseline.csv, which the running study supplies as an answer key.
missing_codes <- crf_raw |>
mutate(site = str_to_title(str_squish(site))) |>
pivot_longer(c(sbp_discharge, weight_kg),
names_to = "variable", values_to = "value") |>
filter(value %in% c("", "NA", "-99")) |>
count(variable, site, value)
missing_codes
#> # A tibble: 10 × 4
#> variable site value n
#> <chr> <chr> <chr> <int>
#> 1 sbp_discharge Site A "" 18
#> 2 sbp_discharge Site B "NA" 23
#> 3 sbp_discharge Site C "" 17
#> 4 sbp_discharge Site D "-99" 15
#> 5 sbp_discharge Site E "NA" 5
#> 6 weight_kg Site A "" 5
#> 7 weight_kg Site B "NA" 3
#> 8 weight_kg Site C "" 4
#> 9 weight_kg Site D "-99" 6
#> 10 weight_kg Site E "NA" 4
baseline <- read_csv(file.path(hf_dir, "baseline.csv"),
col_types = cols(patient_id = col_character()),
show_col_types = FALSE)
totals <- missing_codes |> summarize(n = sum(n), .by = variable)
stopifnot(
totals$n[totals$variable == "sbp_discharge"] ==
sum(is.na(baseline$sbp_discharge)),
totals$n[totals$variable == "weight_kg"] ==
sum(is.na(baseline$weight_kg))
)
totals
#> # A tibble: 2 × 2
#> variable n
#> <chr> <int>
#> 1 sbp_discharge 78
#> 2 weight_kg 22The two totals match the clean table, so the three codes are the only ones in use. A real export has no answer key; the equivalent check is the plausible-range assertion after conversion.
Question. Why read every column as character, with na = character(), instead of passing the right na codes to read_csv() from the start?
Answer. Because you do not know the codes until you have looked, and an assumed list hides the ones you did not assume. The text read lets you count every code by site, which is how you learn that only Site D uses -99. Once the codes are known, naming them in the conversion step records the decision in code. A single na = list passed to read_csv() also applies to every column, including identifiers and free text, where a string such as NA might be a value.
15.2 Recoding site conventions
Each site’s convention is reasonable on its own. The script’s job is to map all five onto one, and to prove that the mapping did what you meant.
15.2.1 Labels and identifiers
Site labels vary in case and spacing; str_squish() removes the stray spaces and str_to_title() fixes the case. Site E’s system dropped the leading zeros of 117 patient IDs, which str_pad() restores. Chapter 13 explains how the zeros were lost, and Chapter 18 what they cost at the join.
crf_labeled <- crf_raw |>
mutate(
site = str_to_title(str_squish(site)),
patient_id = str_pad(patient_id, width = 4, pad = "0")
)
crf_labeled |> count(site)
#> # A tibble: 5 × 2
#> site n
#> <chr> <int>
#> 1 Site A 700
#> 2 Site B 571
#> 3 Site C 498
#> 4 Site D 373
#> 5 Site E 290This export’s header is already in snake_case. Most are not, and janitor::clean_names() is the one-line fix for headers such as Patient ID or SBP at Baseline (mmHg).
15.2.2 Sex: a code means what the codebook says
Four sites write a letter or a word; Site D writes a number. The recode below names every accepted spelling and leaves anything else as NA, so that an unexpected code is caught by the assertion rather than absorbed.
crf_sex <- crf_labeled |>
mutate(
sex_raw = sex,
sex = case_when(
sex %in% c("F", "Female", "f") ~ "F",
sex %in% c("M", "Male", "m") ~ "M",
site == "Site D" & sex == "2" ~ "F",
site == "Site D" & sex == "1" ~ "M"
)
)
stopifnot(!anyNA(crf_sex$sex))
crf_sex |> count(site, sex_raw, sex)
#> # A tibble: 10 × 4
#> site sex_raw sex n
#> <chr> <chr> <chr> <int>
#> 1 Site A F F 308
#> 2 Site A M M 392
#> 3 Site B Female F 268
#> 4 Site B Male M 303
#> 5 Site C f F 225
#> 6 Site C m M 273
#> 7 Site D 1 M 230
#> 8 Site D 2 F 143
#> 9 Site E F F 122
#> 10 Site E M M 168The cross-tabulation is the verification: every raw code appears once, beside the value it became, and a reader can check each row against the site’s codebook.
A numeric sex code recoded from memory produces no NA and no warning. Site D’s codebook says 1 is male and 2 is female, the convention of ISO/IEC 5218; other systems number them the other way. A recode written from habit rather than from the codebook swaps the sex of all 373 Site D patients, and every sex-specific result downstream is plausible and wrong. Write the mapping from the codebook, and keep the raw column until the cross-tabulation has been checked.
15.2.3 Dates: one format per site
The five sites write dates in four formats. Because each site is internally consistent, the safe parse is per site:
parse_site_date <- function(x, site) {
case_when(
site %in% c("Site A", "Site B", "Site E") ~ ymd(x, quiet = TRUE),
site == "Site C" ~ mdy(x, quiet = TRUE),
site == "Site D" ~ dmy(x, quiet = TRUE)
)
}
crf_dates <- crf_sex |>
mutate(across(c(dob, discharge_date),
\(x) parse_site_date(x, site)))
stopifnot(!anyNA(crf_dates$dob), !anyNA(crf_dates$discharge_date),
all(crf_dates$dob < crf_dates$discharge_date))
range(crf_dates$discharge_date)
#> [1] "2024-02-01" "2025-11-30"The tempting alternative is one call to lubridate::parse_date_time() with a list of orders. On this file it returns a date for every row, and 150 of them are wrong: Site D’s 03-FEB-2024 becomes 2024-03-20. Chapter 16 takes up why ambiguous formats misparse and how to detect it.
15.2.4 Numbers: name the codes, then check the range
crf_clean <- crf_dates |>
mutate(across(c(ef_pct, sbp_discharge, weight_kg),
\(x) parse_double(x, na = c("", "NA", "-99"))))
stopifnot(
all(between(crf_clean$ef_pct, 5, 90)),
all(between(crf_clean$sbp_discharge, 50, 250), na.rm = TRUE),
all(between(crf_clean$weight_kg, 30, 250), na.rm = TRUE)
)
crf_clean |>
summarize(mean_sbp = mean(sbp_discharge, na.rm = TRUE), .by = site)
#> # A tibble: 5 × 2
#> site mean_sbp
#> <chr> <dbl>
#> 1 Site B 124.
#> 2 Site E 124.
#> 3 Site A 124.
#> 4 Site D 125.
#> 5 Site C 124.Site D’s mean now sits with the others. The range check is an assertion, not a filter. When it fails, query the site and record the answer, rather than dropping the row. Some protocols do exclude values. In that case ‘removed implausible values’ is not a documented rule. A documented rule reads ‘removed SBP above 250 or below 50’, with the number and percentage of patients computed by the script beside it.
The running study ships the clean tables the export was written from, so you can check the recodes against an answer key that a real export would not have.
demographics <- read_csv(file.path(hf_dir, "demographics.csv"),
col_types = cols(patient_id = col_character()),
show_col_types = FALSE)
key <- match(crf_clean$patient_id, demographics$patient_id)
stopifnot(
!anyNA(key),
identical(crf_clean$sex, demographics$sex[key]),
identical(crf_clean$dob, demographics$dob[key])
)The chunk prints nothing when every patient’s sex and date of birth agree with the clean table.
15.2.5 Exercises
- Reverse the Site D mapping (1 as female, 2 as male), and count how many patients then disagree with
demographics.csv. Which check in the chapter would have caught the error without the answer key? - Parse
discharge_datewithparse_date_time(orders = c("mdy", "dmy", "ymd"))instead of per site. How many dates differ from the per-site parse, and at which sites?
Every Site D patient disagrees, and nothing else in the script notices. There is no NA, so the stopifnot() passes. Only the cross-tabulation read against the codebook catches it. That is why the codebook, not memory, is the source of the mapping.
other_order <- as.Date(parse_date_time(
crf_raw$discharge_date, orders = c("mdy", "dmy", "ymd"),
quiet = TRUE
))
differs <- other_order != crf_dates$discharge_date
sum(is.na(other_order))
#> [1] 0
crf_dates |> filter(differs) |> count(site)
#> # A tibble: 1 × 2
#> site n
#> <chr> <int>
#> 1 Site D 150Changing the order of the formats does not help: every date parses, none is NA, and the same Site D dates land on the wrong day. A parser that always returns a date cannot tell you it guessed.
Question. Next month Site B begins exporting Unknown for sex. What does the recode in this section do, and what would a recode written as if_else(sex %in% c("M", "Male", "m"), "M", "F") do?
Answer. The case_when() above has no .default, so Unknown becomes NA and stopifnot(!anyNA(sex)) halts the script. That is the loud failure you want. The if_else() version assigns F to every value that is not a spelling of male, so Unknown silently becomes female. Listing what each value means, and refusing everything else, is what makes a new code visible.
15.3 Shapes: tidy, long, and wide

Wickham’s three rules (Wickham, 2014) define tidy data, and the HF-HOME files break each of them somewhere (Table 15.1).
| Rule | Broken when | HF-HOME example |
|---|---|---|
| Each variable forms a column | A value is stored in a column name | A lab table with ntprobnp_d0 and ntprobnp_d30: visit is a variable hidden in the names |
| Each observation forms a row | One observation spans rows, or one row holds several |
labs.csv records one discharge potassium twice, with different values |
| Each type of observational unit forms a table | Properties of different units share a table |
labs.csv repeats unit on every row, though the unit belongs to the test, not to the measurement |
Tidiness serves the analysis. The long form usually suits it, but the operation decides the shape.
15.3.1 Long to wide, and the duplicate that stops it
labs.csv is long: one row per visit and test. A per-patient table of discharge labs needs it wide, one row per patient and one column per test. Visit -01 is always the discharge visit.
labs <- read_csv(file.path(hf_dir, "labs.csv"),
col_types = cols(patient_id = col_character()),
show_col_types = FALSE)
discharge_labs <- labs |> filter(str_ends(visit_id, "-01"))
discharge_labs |>
select(patient_id, test_code, value) |>
pivot_wider(names_from = test_code, values_from = value) |>
filter(patient_id %in% c("0373", "2113"))
#> Warning: Values from `value` are not uniquely identified; output will contain list-cols.
#> • Use `values_fn = list` to suppress this warning.
#> • Use `values_fn = {summary_fun}` to summarise duplicates.
#> • Use the following dplyr code to identify duplicates.
#> {data} |>
#> dplyr::summarise(n = dplyr::n(), .by = c(patient_id, test_code)) |>
#> dplyr::filter(n > 1L)
#> # A tibble: 2 × 6
#> patient_id CREAT HGB K NTPROBNP SODIUM
#> <chr> <list> <list> <list> <list> <list>
#> 1 0373 <dbl [1]> <dbl [1]> <dbl [1]> <dbl [2]> <dbl [1]>
#> 2 2113 <dbl [1]> <dbl [1]> <dbl [2]> <dbl [1]> <dbl [1]>pivot_wider() warns and returns list-columns, because two patient-test pairs have two values each. Count the keys before the pivot, as Chapter 18 counts them before a join:
dup_keys <- discharge_labs |>
count(patient_id, test_code) |>
filter(n > 1)
discharge_labs |> semi_join(dup_keys, by = c("patient_id", "test_code"))
#> # A tibble: 4 × 5
#> patient_id visit_id test_code value unit
#> <chr> <chr> <chr> <dbl> <chr>
#> 1 0373 0373-01 NTPROBNP 2502 pg/mL
#> 2 0373 0373-01 NTPROBNP 2502 pg/mL
#> 3 2113 2113-01 K 3.8 mmol/L
#> 4 2113 2113-01 K 4.1 mmol/LOne is an exact duplicate, which distinct() removes without loss. The other is a potassium re-run with a different value, and no rule in the data says which result stands. Supplying values_fn = mean would silence the warning by inventing a value nobody measured. Set the pair aside as a data query, and let the patient’s potassium be missing until the site answers:
labs_distinct <- discharge_labs |> distinct()
lab_queries <- labs_distinct |>
filter(n() > 1, .by = c(patient_id, test_code))
discharge_wide <- labs_distinct |>
filter(n() == 1, .by = c(patient_id, test_code)) |>
select(patient_id, test_code, value) |>
pivot_wider(names_from = test_code, values_from = value)
stopifnot(!anyDuplicated(discharge_wide$patient_id))
lab_queries
#> # A tibble: 2 × 5
#> patient_id visit_id test_code value unit
#> <chr> <chr> <chr> <dbl> <chr>
#> 1 2113 2113-01 K 3.8 mmol/L
#> 2 2113 2113-01 K 4.1 mmol/L
nrow(discharge_wide)
#> [1] 2433The wide table has 2,433 rows for 2,432 randomized patients. The extra one is an orphan lab record that Chapter 18 catches with an anti-join.
15.3.2 The operation decides the shape
A per-test summary wants the long form, where test is a column to group by:
labs_distinct |>
summarize(median = median(value), q1 = quantile(value, 0.25),
q3 = quantile(value, 0.75), n = n(), .by = test_code)
#> # A tibble: 5 × 5
#> test_code median q1 q3 n
#> <chr> <dbl> <dbl> <dbl> <int>
#> 1 CREAT 1.29 1.02 1.63 2433
#> 2 HGB 12.1 11 13.3 2433
#> 3 K 4.3 4 4.6 2434
#> 4 NTPROBNP 3439 1777 6311 2433
#> 5 SODIUM 137 134 139 2433A condition that combines tests within a patient (low sodium and abnormal potassium) wants the wide form, where both values sit in one row. A trajectory plot wants long again, with visit as a column to map to the x-axis (Chapter 20). Pivot only when the operation requires it. On a large file, a long table is many times the size of the wide one.
15.3.3 Writing your own verbs
A helper that takes a column name needs one of two idioms. Embrace it with { } when the caller types the name bare, and use .data[[ ]] when the caller passes a string:
count_by <- function(df, var) {
df |> count({{ var }})
}
count_by_name <- function(df, var_name) {
df |> count(.data[[var_name]])
}
count_by(crf_clean, site)
#> # A tibble: 5 × 2
#> site n
#> <chr> <int>
#> 1 Site A 700
#> 2 Site B 571
#> 3 Site C 498
#> 4 Site D 373
#> 5 Site E 290
count_by_name(crf_clean, "sex")
#> # A tibble: 2 × 2
#> sex n
#> <chr> <int>
#> 1 F 1066
#> 2 M 1366Question 1. labs.csv repeats unit on every row. Which tidy rule does that bend, and is it worth fixing?
Answer. Rule 3. The unit is a property of the test. For example, N-terminal pro-B-type natriuretic peptide (NT-proBNP) is in pg/mL for every patient. The unit therefore belongs in a lookup table keyed by test_code. Repeating it on every row is duplication that can drift. Many analysts leave it in place and accept the risk. That is a defensible choice when it is made deliberately and checked (count(labs, test_code, unit) should give one row per test).
Question 2. What does count_by(crf_clean, "site"), with the name passed as a string, return?
Answer. One row, with the literal string "site" as its value and every patient counted in it. { } embraces whatever it is given, and a string is a constant, so the function counts by a constant. There is no error, and the result has the right shape with a meaningless number in it. count_by_name(crf_clean, "site") is the string version. The rule is that { } takes what the caller types, and .data[[ ]] takes a name your code computed.
15.3.4 Exercises
- Using
discharge_wide, count the patients whose discharge potassium is outside 3.5 to 5.0 mmol/L, the patients whose sodium is below 135 mmol/L, and the patients with both. Why does this question need the wide shape?
discharge_wide |>
summarize(
potassium_abnormal = sum(K < 3.5 | K > 5.0, na.rm = TRUE),
sodium_low = sum(SODIUM < 135, na.rm = TRUE),
both = sum((K < 3.5 | K > 5.0) & SODIUM < 135, na.rm = TRUE),
potassium_missing = sum(is.na(K))
)
#> # A tibble: 1 × 4
#> potassium_abnormal sodium_low both potassium_missing
#> <int> <int> <int> <int>
#> 1 196 624 47 1The condition compares two tests for the same patient, so both values must sit in one row. In the long form it needs a grouped filter across rows, which is harder to read and easier to get wrong. The one patient with a missing potassium is the queried re-run. na.rm = TRUE leaves that patient out of the counts. The potassium_missing column makes the omission visible rather than silent.
15.4 Worked example: from CRF export to derived baseline
The script below is the chapter in one place. It reads the export as text, recodes each site’s conventions, and asserts the result. It then adds derived variables, counts what each filter removes, and writes the derived data to its own directory. It reuses parse_site_date() from Section 15.2.
raw_path <- file.path(hf_dir, "crf_export.csv")
raw_md5 <- unname(tools::md5sum(raw_path))
baseline_derived <- read_csv(
raw_path, col_types = cols(.default = col_character()),
na = character()
) |>
mutate(
site = str_to_title(str_squish(site)),
patient_id = str_pad(patient_id, width = 4, pad = "0"),
sex = case_when(
sex %in% c("F", "Female", "f") ~ "F",
sex %in% c("M", "Male", "m") ~ "M",
site == "Site D" & sex == "2" ~ "F",
site == "Site D" & sex == "1" ~ "M"
),
across(c(dob, discharge_date), \(x) parse_site_date(x, site)),
across(c(ef_pct, sbp_discharge, weight_kg),
\(x) parse_double(x, na = c("", "NA", "-99"))),
age = as.integer(floor(time_length(
interval(dob, discharge_date), "years"
))),
age_group = cut(age, breaks = c(-Inf, 65, 80, Inf), right = FALSE,
labels = c("under 65", "65 to 79", "80 or older"))
)
randomized <- read_csv(file.path(hf_dir, "randomization.csv"),
col_types = cols(.default = col_character()),
show_col_types = FALSE)
stopifnot(
!anyDuplicated(baseline_derived$patient_id),
!anyNA(baseline_derived$sex),
!anyNA(baseline_derived$discharge_date),
all(between(baseline_derived$ef_pct, 5, 90))
)
# one rule per step, so that each step's exclusions can be counted
on_list <- baseline_derived |>
filter(patient_id %in% randomized$patient_id)
with_sbp <- on_list |> filter(!is.na(sbp_discharge))
with_vitals <- with_sbp |> filter(!is.na(weight_kg))
flow <- tibble(
step = c("rows in the CRF export", "on the randomization list",
"discharge SBP recorded", "discharge weight recorded"),
n = c(nrow(baseline_derived), nrow(on_list), nrow(with_sbp),
nrow(with_vitals))
) |>
mutate(removed = lag(n) - n)
flow
#> # A tibble: 4 × 3
#> step n removed
#> <chr> <int> <int>
#> 1 rows in the CRF export 2432 NA
#> 2 on the randomization list 2432 0
#> 3 discharge SBP recorded 2354 78
#> 4 discharge weight recorded 2332 22
derived_dir <- file.path(tempdir(), "derived_data")
dir.create(derived_dir, showWarnings = FALSE)
write_rds(baseline_derived, file.path(derived_dir, "crf_baseline.rds"))
write_csv(flow, file.path(derived_dir, "crf_flow.csv"))The flow table answers a reviewer’s first question about any subset, which is how many patients each rule removed and why. In a compendium, derived_dir is analysis/data/derived_data/ (Section 11.1). Here the chunk writes to a temporary directory, so that rendering the book leaves the repository unchanged.
Two judgments in the script belong to the analysis rather than to the code. First, the flow does not define the analysis population. HF-HOME is analyzed as randomized, so the primary analysis includes all 2,432 patients on the list. The last two steps describe the subset with complete vital signs that a covariate-adjusted sensitivity analysis would use. Reporting its 100 exclusions is what lets a reader judge it. Chapter 21 explains why dropping them is not a neutral act.
Second, age_group is derived beside age, not in place of it. Grouping age is a legitimate display choice for a table of baseline characteristics. It is rarely a legitimate modeling choice: categorizing a continuous predictor discards the variation within each group, assumes a step function where the biology is smooth, and makes the estimate depend on cutpoints that were chosen rather than measured (Harrell, 2025, ch. 18; Harrell, 2015). Deriving age_group is cheap; recovering age after you have overwritten it is not.
Confirm that the derived file round-trips exactly, that the raw file is byte-for-byte what it was before the script ran, and that the derived ages agree with the clean table.
An .rds file keeps types, dates, and factor levels that a derived CSV would lose. The checksum turns Habit 2 into a check: it confirms that the script read the evidence without changing it.
An agentic version: a coding agent writes and runs the recode, and you review the evidence it reports. HF-HOME is synthetic, so the agent may run against it. On a real export it would see the codebook and synthetic rows only.
Context.
analysis/data/raw_data/hfhome/crf_export.csvis a raw CRF export from five sites;README.mdin the same directory is the codebook and lists each site’s sex coding, date format, and missing-value codes. WriteR/derive_baseline.R.Constraints. Use
readr,dplyr,stringr, andlubridate; add no packages. Read every column as character withna = character(). Never write toraw_data/. Map each code exactly as the codebook states, with no.defaultin any recode. Do not useparse_date_time()with several orders, and do not drop rows.Criteria. Before writing code, list each mapping you will apply and the codebook line it comes from. After running, report
count(site, sex_raw, sex), the missing codes found per site and column, the number ofNAdates (which must be zero), and the row count in and out (which must be equal). If any assertion fails, stop and report it instead of fixing it.
Failure mode. The agent maps Site D’s 1 and 2 from a convention it has seen elsewhere. Or it converts the numeric columns with as.numeric(), so that -99 survives as a value. Both errors pass every structural check. Check. Read the cross-tabulation against the codebook line by line, and compare per-site means of sbp_discharge. Search the diff for as.numeric(, .default, and parse_date_time(. The governance rules are in Section 12.3.
Question. A reviewer asks why the primary analysis includes 2,432 patients when the flow table ends at 2,332. What do you answer?
Answer. The flow table describes a subset, not the analysis population. The trial is analyzed as randomized. Every randomized patient is therefore in the primary analysis, whether or not a discharge vital sign was recorded. Excluding patients by a post-randomization measurement would break the comparison that randomization created. The subset with complete vital signs serves a sensitivity analysis, and the table reports what it lost.
15.5 Rules of thumb
- Habit 2: raw data are evidence; derived data are a build product. Read the export, never write to it, and save what you derive to its own directory, with a checksum to prove it.
- Habit 7: convert late, validate early. Read as text, count the codes, then convert with the codes named and the ranges asserted.
-
Habit 8: a bug that produces a plausible number is the expensive kind. A swapped code or a parsed
-99raises nothing; a cross-tabulation and a range check make it loud.
15.6 Cumulative practice
-
Cumulative (uses Chapter 13). Turn the worked example into a function,
derive_baseline(raw_path, out_dir), that writes the derived.rdsand the flow table and returns the raw file’s checksum. Call it twice into two directories and verify that the two derived files are identical and that the raw checksum did not change. - On your own work (no key). Take a raw export from your own work, or one in a public repository, and write the missing-code table and the recode cross-tabulations of this chapter for it. Record every decision you made that the codebook did not determine.
derive_baseline <- function(raw_path, out_dir) {
dir.create(out_dir, showWarnings = FALSE, recursive = TRUE)
derived <- read_csv(
raw_path, col_types = cols(.default = col_character()),
na = character()
) |>
mutate(
site = str_to_title(str_squish(site)),
patient_id = str_pad(patient_id, width = 4, pad = "0"),
across(c(dob, discharge_date), \(x) parse_site_date(x, site)),
across(c(ef_pct, sbp_discharge, weight_kg),
\(x) parse_double(x, na = c("", "NA", "-99")))
)
flow <- tibble(
step = c("rows in the CRF export", "discharge SBP recorded"),
n = c(nrow(derived), sum(!is.na(derived$sbp_discharge)))
)
write_rds(derived, file.path(out_dir, "crf_baseline.rds"))
write_csv(flow, file.path(out_dir, "crf_flow.csv"))
unname(tools::md5sum(raw_path))
}
run_1 <- file.path(tempdir(), "run-1")
run_2 <- file.path(tempdir(), "run-2")
md5_1 <- derive_baseline(raw_path, run_1)
md5_2 <- derive_baseline(raw_path, run_2)
stopifnot(
md5_1 == md5_2,
unname(tools::md5sum(file.path(run_1, "crf_baseline.rds"))) ==
unname(tools::md5sum(file.path(run_2, "crf_baseline.rds")))
)The sex recode is left out to keep the key short; add it, with its assertion, in your own version. Comparing the derived files by checksum is stricter than comparing the data frames. It fails if anything about the serialization changes. That is what you want before you claim that a rerun reproduced a file.
15.7 Cheat sheet
| Task | Code | Guards against |
|---|---|---|
| Read for discovery | read_csv(f, col_types = cols(.default = col_character()), na = character()) |
Codes converted before you have seen them |
| Tabulate codes | count(df, site, sex) |
Unseen spellings and conventions |
| Normalize labels | str_to_title(str_squish(site)) |
Fifteen spellings of five sites |
| Restore leading zeros | str_pad(id, width = 4, pad = "0") |
Keys that match nothing |
| Clean a header | janitor::clean_names(df) |
Spaces and units in column names |
| Recode by codebook |
case_when(x %in% c("F", "f") ~ "F", ...) with no .default
|
New codes absorbed silently |
| Name missing codes | parse_double(x, na = c("", "NA", "-99")) |
Missing codes treated as data |
| Assert ranges | stopifnot(all(between(x, lo, hi), na.rm = TRUE)) |
Implausible values passing through |
| Long to wide | pivot_wider(names_from = test_code, values_from = value) |
Per-patient conditions computed across rows |
| Wide to long | pivot_longer(cols, names_to = "variable", values_to = "value") |
Variables hidden in column names |
| Find pivot duplicates | count(df, id, key) \|> filter(n > 1) |
List-columns and invented values |
Group without group_by()
|
summarize(m = median(x), .by = site) |
Groupings left on by accident |
| Count each exclusion | one filter() per rule, nrow() after each |
Unreported subsets |
| Save derived data | write_rds(df, file.path(derived_dir, "x.rds")) |
Overwritten raw data; lost types |
15.8 Further reading
- (Wickham et al., 2023) Chapters 3, 5, and 7, the single-table verbs, tidying, and data import.
- (Wickham, 2014), the original Tidy Data paper and the three rules.
-
(Harrell, 2025) Chapter 18, ‘Information Loss’, on why the
cut()in the worked example belongs to the table and not to the model.
15.9 Prerequisites answers
- Tidy data satisfy three rules: each variable forms a column, each observation forms a row, and each type of observational unit forms a table. A typical wide clinical layout has one row per patient and one column per variable and time point (
sbp_v1,sbp_v2), which breaks the first two: visit is a variable hidden in the column names, and one row holds several observations. -
crf |> filter(ef_pct < 40) |> summarize(mean_sbp = mean(sbp_discharge, na.rm = TRUE), .by = site). On the raw export this is only correct after the missing-value codes have been converted toNA, which is the point of this chapter’s first section. -
pivot_longer()reshapes wide to long: column names become values of a new column. Use it when columns encode a variable, such as visit, that should be a column of its own, for example to plot a trajectory. Usepivot_wider()when a calculation needs several values for the same unit in one row, such as a change from discharge to day 30 or a condition that combines two lab tests.