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)

Diagram of the analysis pipeline: six stages in order (Plan, Acquire, Wrangle, Analyze, Report, Share) above two bands that run under every stage (Version control and Environment). Highlighted for this chapter: Wrangle.
Figure 15.1: Where this chapter sits: wrangling turns the raw export from the Acquire stage into the derived data every later stage reads.
NoteIn this chapter

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.

  1. What is ‘tidy data’ as defined by Wickham (2014) (Wickham, 2014), and how does it differ from a typical ‘wide’ clinical data layout?
  2. Write the dplyr pipeline that keeps the patients with an ejection fraction (ef_pct) below 40 and returns their mean discharge systolic blood pressure by site.
  3. What does tidyr::pivot_longer() do, and when should you reach for pivot_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      10

The 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.

crf_raw <- read_csv(file.path(hf_dir, "crf_export.csv"),
                    col_types = cols(.default = col_character()),
                    na = character())
crf_raw |> count(sex)
#> # A tibble: 8 × 2
#>   sex        n
#>   <chr>  <int>
#> 1 1        230
#> 2 2        143
#> 3 F        430
#> 4 Female   268
#> 5 M        560
#> 6 Male     303
#> 7 f        225
#> 8 m        273

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

Each 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.

WarningPitfall

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

  1. For each site, count the missing-value codes in sbp_discharge and in weight_kg separately. Then confirm that the totals equal the number of missing values in the clean baseline.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        22

The 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   290

This 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       168

The 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.

WarningPitfall

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.

TipVerification

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

  1. 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?
  2. Parse discharge_date with parse_date_time(orders = c("mdy", "dmy", "ymd")) instead of per site. How many dates differ from the per-site parse, and at which sites?
reversed <- crf_labeled |>
  mutate(sex = case_when(
    sex %in% c("F", "Female", "f") ~ "F",
    sex %in% c("M", "Male", "m") ~ "M",
    site == "Site D" & sex == "1" ~ "F",
    site == "Site D" & sex == "2" ~ "M"
  ))
sum(reversed$sex != demographics$sex[key])
#> [1] 373

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   150

Changing 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

Portrait photograph of Hadley Wickham.

Hadley Wickham, author of the tidyverse and of the tidy-data formulation this chapter teaches. Photograph by Hadley Wickham, CC BY-SA 4.0, via Wikimedia Commons.

Wickham’s three rules (Wickham, 2014) define tidy data, and the HF-HOME files break each of them somewhere (Table 15.1).

Table 15.1: Wickham’s tidy-data rules and where the running study breaks them.
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/L

One 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] 2433

The 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     2433

A 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      1366

Question 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

  1. 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                 1

The 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.

TipVerification

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.

stopifnot(
  identical(read_rds(file.path(derived_dir, "crf_baseline.rds")),
            baseline_derived),
  unname(tools::md5sum(raw_path)) == raw_md5,
  identical(baseline_derived$age,
            as.integer(demographics$age[key]))
)

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.

ImportantLLM prompt

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.csv is a raw CRF export from five sites; README.md in the same directory is the codebook and lists each site’s sex coding, date format, and missing-value codes. Write R/derive_baseline.R.

Constraints. Use readr, dplyr, stringr, and lubridate; add no packages. Read every column as character with na = character(). Never write to raw_data/. Map each code exactly as the codebook states, with no .default in any recode. Do not use parse_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 of NA dates (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

  1. 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.
  2. Habit 7: convert late, validate early. Read as text, count the codes, then convert with the codes named and the ranges asserted.
  3. Habit 8: a bug that produces a plausible number is the expensive kind. A swapped code or a parsed -99 raises nothing; a cross-tabulation and a range check make it loud.

15.6 Cumulative practice

  1. Cumulative (uses Chapter 13). Turn the worked example into a function, derive_baseline(raw_path, out_dir), that writes the derived .rds and 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.
  2. 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

Table 15.2: Verbs and shapes, with what each guards against.
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

  1. 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.
  2. 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 to NA, which is the point of this chapter’s first section.
  3. 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. Use pivot_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.