16  Factors, Strings, and Dates

Problems with numeric columns are usually easy to see. A value that should be a number and is not will stop the analysis almost at once. Text, categories, and dates tend to fail silently. To illustrate, take the first number in each HF-HOME discharge medication string as its dose in milligrams. Then 3,701 of the 15,530 doses written in milligrams or micrograms are wrong, and no error is raised. Nitroglycerin 0.4 mg becomes 0 mg, and digoxin 125 mcg becomes 125 mg, a thousand times the prescribed dose. In the same way, a date parsed under the wrong convention is still a date, and a factor with an unintended reference level still fits a model. Each case produces a result rather than an error, and the result looks normal. In this chapter we show how to make each of these failures visible.

There are only two hard things in Computer Science: cache invalidation and naming things.

Phil Karlton, attributed (c. 1996)

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 16.1: Where this chapter sits: giving text, categories, and dates their types is part of the Wrangle stage, between the raw export and the derived analytic data.
NoteIn this chapter

Reading time. About 30 minutes.

You’ll need. Chapter 13 for reading the HF-HOME files, and Chapter 15 for the per-site recodes this chapter builds on. The code uses:

library(tidyverse)   # includes forcats, stringr, and lubridate

You’ll be able to.

  • Turn a variable coded several ways into a factor with a declared level set and reference level, and verify the recode.
  • Extract drug, dose, unit, and frequency from free-text dosing strings, and count what a careless pattern gets wrong.
  • Parse dates written in several formats, and detect the values that a multi-format parser may silently misread.
  • Choose between a period and a duration, and between with_tz() and force_tz(), for a given question.

Outline.

If these answers come quickly, skip to the next chapter. The answers are in Section 16.9.

  1. Why has stringsAsFactors = FALSE been the default since R 4.0, and what problems did the old default cause?
  2. What does stringr::str_detect() return, and how does it differ from stringr::str_extract()?
  3. Given c('2026-04-23', '04/23/2026', 'April 23, 2026'), write one lubridate call that parses all three into dates, and say what could go wrong if a fourth value were '03/04/2026'.

16.1 Factors: categories with a declared level set

A character vector is flexible. It accepts any value, in any order, and new values can be added later. A factor is constrained. It has a fixed set of levels, a defined order, and a first level that a model treats as the reference. For analysis the constraints are correct, because the model needs the level set and the reference. For cleaning and joining they are a hazard, because a new or misspelled value has nowhere to go. The question is therefore when to convert. Figure 16.2 gives the answer we recommend in this chapter (Habit 7).

flowchart LR
  R["Read<br/><i>character</i>"] --> C["Clean and recode<br/><i>character</i>"]
  C --> J["Join<br/><i>character</i>"]
  J --> X["<b>Convert here</b><br/><i>factor, reference level<br/>set deliberately</i>"]
  X --> M["Model<br/><i>factor</i>"]
  X --> P["Plot<br/><i>factor, ordered<br/>by fct_reorder</i>"]
Figure 16.2: Where to convert. Character is the safe type through reading, cleaning, and joining, because a new or misspelled value is merely a new string rather than an invalid level. Factor is the correct type at modeling and plotting, because the model needs a fixed level set and a declared reference.

The generic tour of forcats is in R for Data Science (Wickham et al., 2023, ch. 16), and the full verb list is in the cheat sheet at the end of this chapter. Each section below starts from a problem in the HF-HOME export and then introduces the verb that solves it.

16.1.1 A variable coded four ways

The case report form (CRF) export records sex as F/M at two sites, Female/Male at one, f/m at another, and 2/1 at Site D. Site D’s codebook maps 1 to male and 2 to female. Chapter 15 recoded these with case_when(). forcats::fct_collapse() does the same thing in terms of levels:

library(tidyverse)
hf_dir <- file.path("..", "data", "raw_data", "hfhome")

crf_raw <- read_csv(file.path(hf_dir, "crf_export.csv"),
                    col_types = cols(.default = col_character()),
                    na = character()) |>
  mutate(site_raw = site,
         site = str_to_title(str_squish(site)),
         patient_id = str_pad(patient_id, width = 4, pad = "0"))

crf_typed <- crf_raw |>
  mutate(sex = fct_collapse(sex,
                            F = c("F", "Female", "f", "2"),
                            M = c("M", "Male", "m", "1")) |>
           fct_relevel("F", "M"))
levels(crf_typed$sex)
#> [1] "F" "M"
crf_typed |> count(site, raw = crf_raw$sex, sex)
#> # A tibble: 10 × 4
#>    site   raw    sex       n
#>    <chr>  <chr>  <fct> <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 collapsed levels come out in the order of the raw codes they absorbed, so fct_relevel() states the order you want. More important, fct_collapse() keeps any code you did not list as a level of its own, and it gives no warning. For this reason we print the levels, and the verification below asserts them. An unexpected code, such as U arriving next quarter, will then stop the script. It will not become a third sex.

TipVerification

The running study ships the clean demographics.csv, so the recode can be checked against an answer key:

demographics <- read_csv(file.path(hf_dir, "demographics.csv"),
                         col_types = cols(patient_id = col_character()),
                         show_col_types = FALSE)
key <- match(crf_typed$patient_id, demographics$patient_id)

stopifnot(
  identical(levels(crf_typed$sex), c("F", "M")),
  !anyNA(key),
  all(as.character(crf_typed$sex) == demographics$sex[key])
)

A real export has no answer key. In that case the check is the cross-tabulation above, read line by line against each site’s codebook.

16.1.2 Declaring the level set

The site labels arrive in fifteen spellings of five sites. A factor built with factor() and no levels accepts all fifteen, in alphabetical order. A factor built with the levels the protocol names turns every other spelling into NA, which an assertion can catch:

site_levels <- paste("Site", LETTERS[1:5])

nlevels(factor(crf_raw$site_raw))
#> [1] 15

crf_typed <- crf_typed |>
  mutate(site = factor(site, levels = site_levels))
stopifnot(!anyNA(crf_typed$site))
fct_count(crf_typed$site)
#> # A tibble: 5 × 2
#>   f          n
#>   <fct>  <int>
#> 1 Site A   700
#> 2 Site B   571
#> 3 Site C   498
#> 4 Site D   373
#> 5 Site E   290

Declared levels also fix the order of every table and figure. Use fct_infreq() or fct_reorder() when the order should follow the data, as in a bar chart sorted by count; keep the protocol’s order when the reader will look for a site by name.

16.1.3 The reference level

randomization.csv records the arm as Usual care or Home health. Left to factor(), the levels are alphabetical, so Home health becomes the reference and the model reports the effect of usual care:

randomization <- read_csv(file.path(hf_dir, "randomization.csv"),
                          col_types = cols(.default = col_character()))
outcomes <- read_csv(file.path(hf_dir, "outcomes.csv"),
                     col_types = cols(patient_id = col_character()),
                     show_col_types = FALSE)
trial <- randomization |>
  inner_join(outcomes, by = "patient_id",
             relationship = "one-to-one")

levels(factor(trial$arm))
#> [1] "Home health" "Usual care"

fit_default <- glm(event_30d ~ arm, family = binomial,
                   data = trial |> mutate(arm = factor(arm)))
fit_declared <- glm(event_30d ~ arm, family = binomial,
                    data = trial |> mutate(arm = factor(
                      arm, levels = c("Usual care", "Home health"))))
coef(fit_default)[2]
#> armUsual care 
#>      0.145559
coef(fit_declared)[2]
#> armHome health 
#>      -0.145559

The two coefficients have the same size and opposite signs. Neither is wrong. However, a reader who assumes that usual care is the comparison arm will read the first one’s direction backwards. Declare the reference in the cleaning script, in writing, where a reviewer can see it. Whether the estimate is distinguishable from zero is a question for the analysis plan (Chapter 26), not for the factor.

ImportantLLM prompt

Context. My model is glm(event_30d ~ arm + site + sex, family = binomial). The levels of each factor, as printed by levels(), are below, with one sentence from the protocol naming the control arm.

Constraints. Do not refit or change the formula. Set levels with factor(..., levels = ) or forcats::fct_relevel() in the data-cleaning step, not inside the model call.

Criteria. For each factor, say which level is the reference now, which it should be and why, and write the code that sets it. Then state in one sentence what the arm coefficient will mean after the change, including its sign convention.

Failure mode. Models assert that ‘the first level is the reference’ without checking which level is first, or set the reference inside the formula with relevel() where the next model in the script does not inherit it. Check. Run levels() on each factor after the change, refit, and confirm that the coefficient’s name (armHome health) names the treated arm. Send level names, never rows (Section 12.3).

16.1.4 Factors in joins and arithmetic

A dplyr join on factor keys compares the labels, not the integer codes behind them, so the rows match correctly. However, two less obvious things happen, and neither produces a warning:

a <- tibble(site = factor(c("Site A", "Site B")), x = 1:2)
b <- tibble(site = factor(c("Site B", "Site C")), y = 3:4)
joined <- full_join(a, b, by = "site")
levels(joined$site)
#> [1] "Site A" "Site B" "Site C"
as.integer(a$site[a$site == "Site B"])
#> [1] 2
as.integer(b$site[b$site == "Site B"])
#> [1] 1

site_number <- factor(c("10", "2", "3"))
as.numeric(site_number)
#> [1] 1 2 3
as.numeric(as.character(site_number))
#> [1] 10  2  3

The joined key acquires the union of both level sets, in an order neither table chose, and Site B is code 2 in one table and code 1 in the other.

WarningPitfall

as.numeric() on a factor returns its codes, not its labels. A site number stored as a factor, 10, 2, 3, becomes 1, 2, 3, because the codes follow the alphabetical order of the labels. Any join or calculation on those codes pairs rows by position in the level set, with the wrong labels and no error. Convert through as.character(), or better, keep identifiers and numbers out of factors altogether.

16.1.5 Exercises

  1. Build site as a factor with the five protocol levels from the raw labels after str_squish() only, without str_to_title(). How many patients become NA, and under which raw spellings?
  2. Fit lm(event_30d ~ arm, data = trial) with the default levels and with fct_relevel(arm, "Usual care"). Verify that the two arm coefficients are equal in size and opposite in sign, and say which one is the risk difference for home health against usual care.
squished <- crf_raw |>
  mutate(site_f = factor(str_squish(site_raw), levels = site_levels))
sum(is.na(squished$site_f))
#> [1] 143
squished |>
  filter(is.na(site_f)) |>
  count(spelling = str_squish(site_raw))
#> # A tibble: 10 × 2
#>    spelling     n
#>    <chr>    <int>
#>  1 SITE A      16
#>  2 SITE B      15
#>  3 SITE C      16
#>  4 SITE D      15
#>  5 SITE E       6
#>  6 site a      19
#>  7 site b      20
#>  8 site c      16
#>  9 site d      10
#> 10 site e      10

Every upper- and lower-case variant is refused. That is the point of declaring the levels. The NAs are a list of what the cleaning step still has to handle. With factor() and no levels, each of these spellings would have become a site of its own.

lm_default <- lm(event_30d ~ arm, data = trial)
lm_declared <- lm(event_30d ~ arm,
                  data = trial |> mutate(arm = fct_relevel(arm,
                                                           "Usual care")))
stopifnot(all.equal(unname(coef(lm_default)[2]),
                    -unname(coef(lm_declared)[2])))
coef(lm_declared)[2]
#> armHome health 
#>    -0.02527255

With Usual care as the reference, the coefficient armHome health is the difference in the proportion with an event, home health minus usual care, among ascertained patients. A negative value favors home health. The default model reports the same number with the sign reversed, under the name armUsual care.

Question. This chapter keeps categorical variables as character through reading, cleaning, and joining, and converts to factor only for modeling and plotting. What goes wrong if you convert at read time instead?

Answer. Recoding becomes harder. Assigning a value that is not already a level yields NA with a warning, not the value you assigned. Also, str_replace() on a factor silently returns character. Joining becomes harder to reason about. Two factors built independently rarely share a level set, and the joined key acquires the union of both. The codes behind the labels also differ between tables, which causes errors as soon as anyone calls as.integer() on a key. In short, the fixed set of categories and the reference level that a model needs are the same properties that make a factor brittle while the data are still changing.

16.2 Strings: structure from free text

medications.csv has one row per discharge medication. Its med_text column records each medication as the prescriber typed it. The 16,927 rows use 57 distinct strings. They mix brand and generic names, mg with and without a space, upper and lower case, combination doses, units other than milligrams, and a dozen ways to say how often. The generic tour of stringr and regular expressions is in R for Data Science (Wickham et al., 2023, chs. 14 and 15). In this section we answer three questions a statistician asks of this column.

meds <- read_csv(file.path(hf_dir, "medications.csv"),
                 col_types = cols(patient_id = col_character(),
                                  med_seq = col_integer(),
                                  med_text = col_character()))
meds |> filter(patient_id == "0001") |> pull(med_text)
#> [1] "Entresto 24-26 mg twice daily"         
#> [2] "metformin 1000mg bid with meals"       
#> [3] "Nitroglycerin 0.4 mg SL PRN chest pain"
#> [4] "atorvastatin 80mg at bedtime"          
#> [5] "Digoxin 0.125 mg daily"                
#> [6] "Amiodarone 200 mg daily"               
#> [7] "Empagliflozin 10 mg daily"             
#> [8] "K-Dur 20 meq qd"                       
#> [9] "Farxiga 10 mg QAM"

16.2.1 Which drug?

How many discharge prescriptions are for furosemide? The obvious pattern undercounts twice, once for case and once for the brand name:

c(
  case_sensitive = sum(str_detect(meds$med_text, "furosemide")),
  ignore_case = sum(str_detect(meds$med_text,
                               regex("furosemide", ignore_case = TRUE))),
  with_brand = sum(str_detect(meds$med_text,
                              regex("furosemide|lasix",
                                    ignore_case = TRUE)))
)
#> case_sensitive    ignore_case     with_brand 
#>            142            590            735

Each count is a plausible number of prescriptions, and only the last is right. A question about a drug therefore starts with a lookup. The lookup names every spelling, brand and generic, for each class the analysis needs. For a heart-failure trial, three classes matter first: sodium-glucose cotransporter 2 (SGLT2) inhibitors, the angiotensin receptor-neprilysin inhibitor (ARNI) sacubitril-valsartan, and loop diuretics.

meds <- meds |>
  mutate(drug_class = case_when(
    str_detect(med_text,
               regex("dapagliflozin|farxiga|empagliflozin|jardiance",
                     ignore_case = TRUE)) ~ "SGLT2 inhibitor",
    str_detect(med_text, regex("sacubitril|entresto",
                               ignore_case = TRUE)) ~ "ARNI",
    str_detect(med_text, regex("furosemide|lasix|torsemide",
                               ignore_case = TRUE)) ~ "Loop diuretic"
  ))
meds |>
  filter(!is.na(drug_class)) |>
  count(drug_class, med_text)
#> # A tibble: 14 × 3
#>    drug_class      med_text                              n
#>    <chr>           <chr>                             <int>
#>  1 ARNI            Entresto 24-26 mg twice daily       254
#>  2 ARNI            Sacubitril/valsartan 49/51 mg BID   238
#>  3 ARNI            sacubitril-valsartan 97/103mg bid   254
#>  4 Loop diuretic   FUROSEMIDE 80 MG QAM                153
#>  5 Loop diuretic   Furosemide 20 mg daily              140
#>  6 Loop diuretic   Furosemide 40 mg BID                155
#>  7 Loop diuretic   Lasix 40 mg twice daily             145
#>  8 Loop diuretic   Torsemide 20 mg daily               361
#>  9 Loop diuretic   furosemide 40mg bid                 142
#> 10 Loop diuretic   torsemide 10mg qd                   381
#> 11 SGLT2 inhibitor Dapagliflozin 10 mg daily           384
#> 12 SGLT2 inhibitor Empagliflozin 10 mg daily           369
#> 13 SGLT2 inhibitor Farxiga 10 mg QAM                   374
#> 14 SGLT2 inhibitor Jardiance 10mg qd                   349

The cross-tabulation serves as the verification. Every spelling that matched is listed beside its class, so a reader can see that Farxiga was counted and that nothing else was.

16.2.2 What dose?

The dose is a number followed by a unit. The number may have a decimal point, and it may be a pair for a combination tablet (49/51, 24-26). The pattern below captures each part with a group, and str_match() returns one column per group:

dose_pattern <- regex(
  paste0("(\\d+(?:\\.\\d+)?)",
         "(?:\\s*[/-]\\s*(\\d+(?:\\.\\d+)?))?",
         "\\s*(mg|mcg|meq|units)\\b"),
  ignore_case = TRUE
)
dose_parts <- str_match(meds$med_text, dose_pattern)

meds <- meds |>
  mutate(
    dose    = as.numeric(dose_parts[, 2]),
    dose_2  = as.numeric(dose_parts[, 3]),
    unit    = str_to_lower(dose_parts[, 4]),
    dose_mg = case_when(unit == "mg" ~ dose,
                        unit == "mcg" ~ dose / 1000)
  )
meds |> count(unit)
#> # A tibble: 4 × 2
#>   unit      n
#>   <chr> <int>
#> 1 mcg    1087
#> 2 meq     697
#> 3 mg    14443
#> 4 units   700
meds |> filter(!is.na(dose_2)) |> count(med_text, dose, dose_2)
#> # A tibble: 3 × 4
#>   med_text                           dose dose_2     n
#>   <chr>                             <dbl>  <dbl> <int>
#> 1 Entresto 24-26 mg twice daily        24     26   254
#> 2 Sacubitril/valsartan 49/51 mg BID    49     51   238
#> 3 sacubitril-valsartan 97/103mg bid    97    103   254

Table 16.1 reads the pattern piece by piece.

Table 16.1: The constructs in the dose pattern. In an R string each regular expression backslash is written twice.
Piece Matches Why it is there
\\d+(?:\\.\\d+)? 40, 0.4, 12.5 A dose with or without a decimal part; (?: ) groups without capturing
(?:\\s*[/-]\\s*(...))? /51, -26, or nothing The second component of a combination tablet, captured separately
\\s* Zero or more spaces 40mg and 40 mg alike
(mg\|mcg\|meq\|units) The unit Requiring a unit stops q5min from being read as a dose
\\b A word boundary mg but not the start of a longer word
ignore_case = TRUE MG, mEq Prescribers’ capitalization varies
WarningPitfall

A pattern that takes the first number misreads decimals, units, and combination doses, and returns a number every time. str_extract(x, "\\d+") reads 0.4 mg as 0, 12.5 mg as 12, and digoxin 125 mcg as 125, which a later step treats as milligrams. 24-26 mg is a combination tablet, 24 mg of sacubitril and 26 mg of valsartan, not a range and not 24 mg. Require the unit in the pattern, capture the decimal part, and convert units explicitly.

TipVerification

Every row should yield a dose and a unit from a known set, and two spellings of the same prescription should agree once units are converted:

digoxin <- meds |>
  filter(str_detect(med_text, regex("digoxin", ignore_case = TRUE)))
stopifnot(
  !anyNA(meds$dose),
  setequal(unique(meds$unit), c("mg", "mcg", "meq", "units")),
  n_distinct(digoxin$dose_mg) == 1
)
digoxin |> count(med_text, dose_mg)
#> # A tibble: 2 × 3
#>   med_text               dose_mg     n
#>   <chr>                    <dbl> <int>
#> 1 Digoxin 0.125 mg daily   0.125   376
#> 2 digoxin 125 mcg QD       0.125   367

0.125 mg and 125 mcg are the same dose, and the assertion says so. A first-number parse fails this check by a factor of a thousand.

16.2.3 How often?

The frequency appears in three forms. It is written as Latin abbreviations with and without periods (BID, b.i.d.), as English (twice daily, once a day), and as timing (QAM, qhs, at bedtime). We remove the periods and the case, then map each phrase to doses per day. PRN (as needed) has no fixed daily count and stays NA.

freq_pattern <- paste(
  "\\bprn\\b", "\\bbid\\b", "twice daily", "\\btid\\b", "\\bqd\\b",
  "once daily", "once a day", "every day", "daily", "\\bqam\\b",
  "\\bqhs\\b", "at bedtime", sep = "|"
)

meds <- meds |>
  mutate(
    freq_text = med_text |> str_to_lower() |> str_remove_all("\\."),
    freq_code = str_extract(freq_text, freq_pattern),
    per_day = case_when(
      freq_code == "prn" ~ NA_real_,
      freq_code %in% c("bid", "twice daily") ~ 2,
      freq_code == "tid" ~ 3,
      freq_code %in% c("qd", "once daily", "once a day", "every day",
                       "daily", "qam", "qhs", "at bedtime") ~ 1
    )
  )
meds |> count(per_day, freq_code)
#> # A tibble: 12 × 3
#>    per_day freq_code       n
#>      <dbl> <chr>       <int>
#>  1       1 at bedtime    587
#>  2       1 daily        4562
#>  3       1 every day     224
#>  4       1 once a day    263
#>  5       1 once daily    517
#>  6       1 qam           884
#>  7       1 qd           3428
#>  8       1 qhs           595
#>  9       2 bid          3225
#> 10       2 twice daily   399
#> 11       3 tid          1516
#> 12      NA prn           727

Two details make the mapping work. First, str_extract() returns the leftmost match, so twice daily is found before the daily inside it. A case_when() that tested str_detect(x, "daily") first would call every twice-daily prescription once daily. Second, the word boundaries in \\bqd\\b and \\bbid\\b keep the codes from matching inside longer words.

TipVerification

Every row has a frequency code, and the only rows without a daily count are the as-needed ones:

stopifnot(
  !anyNA(meds$freq_code),
  all(is.na(meds$per_day) == (meds$freq_code == "prn"))
)

16.2.4 Exercises

  1. Count patients, not prescriptions, discharged on an SGLT2 inhibitor in each arm. Join to randomization.csv with a declared relationship.
  2. Compute the daily furosemide dose (dose_mg * per_day) for every furosemide prescription, in any spelling. Then recompute per_day with a case_when() that tests for daily before twice daily, and count the prescriptions, across all drugs, whose daily count changes.
sglt2_patients <- meds |>
  filter(drug_class == "SGLT2 inhibitor") |>
  distinct(patient_id)

randomization |>
  left_join(sglt2_patients |> mutate(on_sglt2 = TRUE),
            by = "patient_id", relationship = "one-to-one") |>
  mutate(on_sglt2 = coalesce(on_sglt2, FALSE)) |>
  count(arm, on_sglt2) |>
  mutate(share = n / sum(n), .by = arm)
#> # A tibble: 4 × 4
#>   arm         on_sglt2     n share
#>   <chr>       <lgl>    <int> <dbl>
#> 1 Home health FALSE      622 0.512
#> 2 Home health TRUE       594 0.488
#> 3 Usual care  FALSE      583 0.479
#> 4 Usual care  TRUE       633 0.521

distinct() comes first because a patient could hold two SGLT2 prescriptions. Without it the join would be one-to-many, and the declared relationship would refuse it. The shares in the two arms are within a few percentage points of each other. This is what randomization leads us to expect for a medication prescribed before the arm could influence it. A large difference would be worth a data query.

meds |>
  filter(str_detect(med_text, regex("furosemide|lasix",
                                    ignore_case = TRUE))) |>
  mutate(daily_mg = dose_mg * per_day) |>
  count(med_text, daily_mg)
#> # A tibble: 5 × 3
#>   med_text                daily_mg     n
#>   <chr>                      <dbl> <int>
#> 1 FUROSEMIDE 80 MG QAM          80   153
#> 2 Furosemide 20 mg daily        20   140
#> 3 Furosemide 40 mg BID          80   155
#> 4 Lasix 40 mg twice daily       80   145
#> 5 furosemide 40mg bid           80   142

wrong_order <- case_when(
  str_detect(meds$freq_text, "\\bprn\\b") ~ NA_real_,
  str_detect(meds$freq_text, "daily") ~ 1,
  str_detect(meds$freq_text, "\\bbid\\b|twice daily") ~ 2,
  str_detect(meds$freq_text, "\\btid\\b") ~ 3,
  str_detect(meds$freq_text,
             "\\bqd\\b|once a day|every day|\\bqam\\b|\\bqhs\\b|at bedtime") ~ 1
)
sum(wrong_order != meds$per_day, na.rm = TRUE)
#> [1] 399
meds |>
  filter(wrong_order != per_day) |>
  count(med_text)
#> # A tibble: 2 × 2
#>   med_text                          n
#>   <chr>                         <int>
#> 1 Entresto 24-26 mg twice daily   254
#> 2 Lasix 40 mg twice daily         145

Every twice daily prescription is halved, including the Lasix 40 mg twice daily rows, whose daily furosemide dose drops from 80 mg to 40 mg. No value is NA, and no assertion on types fails. Only the cross-tabulation of codes against counts shows the error.

Question. A colleague flags beta-blockers with str_detect(med_text, "olol"), because their generic names end that way. What does the flag count in this file, and what does it miss?

Answer. Tabulate it against the spellings you know to be beta-blockers:

meds |>
  filter(str_detect(med_text, regex("olol|coreg|toprol",
                                    ignore_case = TRUE))) |>
  count(med_text, flagged = str_detect(med_text, "olol"))
#> # A tibble: 4 × 3
#>   med_text                         flagged     n
#>   <chr>                            <lgl>   <int>
#> 1 Coreg 25 mg b.i.d.               FALSE     242
#> 2 Metoprolol succinate 50 mg daily TRUE      263
#> 3 Toprol XL 100 mg once daily      FALSE     255
#> 4 metoprolol XL 25mg QD            TRUE      227

The flag counts metoprolol written generically. It misses carvedilol, whose name ends in -dilol, and the brands Coreg and Toprol XL. The suffix is shared by some names in the class, but not by all of them. Build class flags from a lookup that names each drug, brand and generic, with ignore_case = TRUE. Then print the matched spellings beside the class, as in types-classes, so a reader can see what was counted.

16.3 Dates: formats, ambiguity, and time

The CRF export writes dates in four formats: YYYY-MM-DD at Sites A and B, MM/DD/YYYY at Site C, DD-MON-YYYY at Site D, and YYYYMMDD at Site E. Chapter 15 parsed them site by site. It also noted that a single call to lubridate::parse_date_time() with a list of formats returns a date for every row, and that some of those dates are wrong. In this section we show why, and how to find the rows at risk. The generic tour is in R for Data Science (Wickham et al., 2023, ch. 17).

16.3.1 One parser, several formats

parse_date_time() accepts several orders and returns, for each value, a date under one of them. The per-site parse uses one order per site, as each site’s codebook states:

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)
  )
}

dates <- crf_raw |>
  transmute(
    site, discharge_raw = discharge_date,
    by_site = parse_site_date(discharge_date, site),
    one_call = as_date(parse_date_time(discharge_date,
                                       orders = c("ymd", "mdy", "dmy"),
                                       quiet = TRUE))
  )

dates |> count(site, wrong = by_site != one_call)
#> # A tibble: 6 × 3
#>   site   wrong     n
#>   <chr>  <lgl> <int>
#> 1 Site A FALSE   700
#> 2 Site B FALSE   571
#> 3 Site C FALSE   498
#> 4 Site D FALSE   223
#> 5 Site D TRUE    150
#> 6 Site E FALSE   290
dates |> filter(by_site != one_call) |> distinct() |> head(4)
#> # A tibble: 4 × 4
#>   site   discharge_raw by_site    one_call  
#>   <chr>  <chr>         <date>     <date>    
#> 1 Site D 03-FEB-2024   2024-02-03 2024-03-20
#> 2 Site D 05-FEB-2024   2024-02-05 2024-05-20
#> 3 Site D 10-FEB-2024   2024-02-10 2024-10-20
#> 4 Site D 01-MAR-2024   2024-03-01 2024-01-20

misread <- dates |>
  filter(by_site != one_call) |>
  summarize(n = n(),
            in_period = sum(between(one_call, min(dates$by_site),
                                    max(dates$by_site))))
misread
#> # A tibble: 1 × 2
#>       n in_period
#>   <int>     <int>
#> 1   150       141

The single call parses every value and returns no NA. Every disagreement is at Site D. The rows shown are consistent with 03-FEB-2024 being read as month 03. That is, FEB is skipped, and 2024 is split into a day, 20, and a two-digit year, 24. Of the 150 misread discharge dates, 141 fall inside the trial’s enrollment period, so a range check on discharge dates catches only 9 of them.

16.3.2 Ambiguous values

Site D’s failure comes from a format the orders did not describe well. Site C’s risk is harder to see. The value 03/04/2026 is a valid date under both mdy and dmy. parse_date_time() chooses an order by training on the whole vector. As a result, the reading of one value depends on the order you list and on its neighbors:

parse_date_time("03/04/2026", orders = c("mdy", "dmy"))
#> [1] "2026-03-04 UTC"
parse_date_time("03/04/2026", orders = c("dmy", "mdy"))
#> [1] "2026-04-03 UTC"
parse_date_time(c("03/04/2026", "25/04/2026"), orders = c("mdy", "dmy"))
#> [1] "2026-04-03 UTC" "2026-04-25 UTC"

Alone, with mdy listed first, the value is March 4. Beside a value that only dmy can read, the same value becomes April 3. The call is the same, and there is no warning. A column’s dates can therefore change when rows are added to it. The defense is to count the values that a second convention would read differently:

ambiguous <- function(x) {
  as_mdy <- mdy(x, quiet = TRUE)
  as_dmy <- dmy(x, quiet = TRUE)
  !is.na(as_mdy) & !is.na(as_dmy) & as_mdy != as_dmy
}

crf_raw |>
  filter(site == "Site C") |>
  summarize(n = n(), ambiguous = sum(ambiguous(discharge_date)))
#> # A tibble: 1 × 2
#>       n ambiguous
#>   <int>     <int>
#> 1   498       207

Only Site C’s codebook can resolve those values. For this reason the parse is done per site, and the order is stated rather than guessed.

WarningPitfall

A multi-format parser never says it guessed. parse_date_time() with several orders returns a date for every value it can read under any of them. Day-month swaps produce valid dates in the same month range, and a misread such as Site D’s produces a plausible date in the same year. Parse each source with its single documented order, and count the ambiguous values before trusting any multi-order parse.

ImportantLLM prompt

Context. A date column from three sources. Below are twenty synthetic values in each source’s format, and each source’s codebook line stating its format.

Constraints. Parse each source with its single documented order (ymd(), mdy(), dmy()), not parse_date_time() with several orders and not nested ifelse(). Use quiet = TRUE and count failures explicitly.

Criteria. Return a function parse_source_date(x, source) and a check that reports, per source, the number of NAs and the number of values that mdy() and dmy() would read as different dates.

Failure mode. The model’s default answer is parse_date_time(x, orders = c("ymd", "mdy", "dmy")), often called the robust choice. It returns a date for every value, so an NA count finds nothing. It also misreads exactly the values this section counts. Check. Compare the model’s parse with the per-site parse row by row, as in types-dates. Any disagreement is a misread by one of them. Send synthetic values in the source’s format, not real dates of birth (Section 12.3).

16.3.3 Excel serial dates

A date column from a spreadsheet may arrive as five-digit numbers, Excel’s count of days from its own origin. Converting with as.Date() counts from R’s origin instead:

as.Date(45405)
#> [1] "2094-04-25"
as.Date(45405, origin = "1899-12-30")
#> [1] "2024-04-23"
janitor::excel_numeric_to_date(45405)
#> [1] "2024-04-23"

as.Date(45405) is a valid date seventy years from now, and nothing about it is NA.1 Treat any column whose name says date and whose values are numbers as a bug until shown otherwise. The assertion is a range check against the study period.

1 Since R 4.3.0, as.Date() on a number uses 1970-01-01 as the origin when none is given; earlier versions required an origin. The Excel origin of 1899-12-30, rather than 1900-01-01, absorbs Excel’s treatment of 1900 as a leap year, which holds for dates after February 1900 in the default Windows date system.

16.3.4 Time zones and daylight saving

HF-HOME records dates without times, which is the safe case for daily-resolution analyses. Device data, electronic health record audit logs, and infusion pumps record date-times. A date-time without a time zone is ambiguous. Is it Coordinated Universal Time (UTC), the site’s zone, or the zone of the server that wrote it? lubridate parses a date-time without a zone as UTC, while base R uses the session’s zone. State the zone explicitly:

t <- ymd_hms("2026-04-23 14:30:00", tz = "America/Los_Angeles")
t
#> [1] "2026-04-23 14:30:00 PDT"
with_tz(t, "UTC")
#> [1] "2026-04-23 21:30:00 UTC"
force_tz(t, "UTC")
#> [1] "2026-04-23 14:30:00 UTC"

with_tz() changes the displayed zone without changing the instant. force_tz() changes the instant, by declaring that the clock reading was always in the new zone. Confusing them shifts times by the offset between the zones, here seven hours.

Daylight-saving transitions add a second trap. In most US time zones, daylight-saving time began at 2:00 a.m. on March 8, 2026, so an interval that starts at noon the day before crosses it:

start <- ymd_hms("2026-03-07 12:00:00", tz = "America/Los_Angeles")
start + days(1)
#> [1] "2026-03-08 12:00:00 PDT"
start + hours(24)
#> [1] "2026-03-08 12:00:00 PDT"
start + dhours(24)
#> [1] "2026-03-08 13:00:00 PDT"

days(1) and hours(24) are both periods. They advance the clock, and the result is noon on March 8. dhours(24) is a duration of exactly 86,400 seconds. Its result is 1:00 p.m., because the night of the transition was only 23 hours long. Use a period for a calendar day and a duration for elapsed time, whichever the analysis requires.

16.3.5 Exercises

  1. Parse dob per site and verify it against demographics.csv. Then parse it with the single parse_date_time() call of types-dates, and count the dates of birth it gets wrong and how many of those fall after the patient’s discharge date.
  2. Count the ambiguous dates of birth at Site C. For one of them, show the date mdy() and dmy() each return.
  3. Create a date-time in America/Los_Angeles and convert it to UTC with with_tz() and with force_tz(). Explain the difference, and say when each is the right call.
dob <- crf_raw |>
  transmute(
    patient_id, site,
    by_site = parse_site_date(dob, site),
    one_call = as_date(parse_date_time(dob,
                                       orders = c("ymd", "mdy", "dmy"),
                                       quiet = TRUE)),
    discharge = parse_site_date(discharge_date, site)
  )
stopifnot(identical(dob$by_site,
                    demographics$dob[match(dob$patient_id,
                                           demographics$patient_id)]))

dob_wrong <- dob |>
  filter(by_site != one_call) |>
  summarize(wrong = n(), after_discharge = sum(one_call > discharge),
            .by = site)
dob_wrong
#> # A tibble: 1 × 3
#>   site   wrong after_discharge
#>   <chr>  <int>           <int>
#> 1 Site D   125             112

The per-site parse agrees with the answer key for every patient. The single call misreads only Site D’s dates of birth. Of its 125 misreads, 112 place the birth after the discharge, which a range check catches. The other 13 are plausible ages. Only the comparison catches them.

site_c_dob <- crf_raw |> filter(site == "Site C") |> pull(dob)
sum(ambiguous(site_c_dob))
#> [1] 176
example <- site_c_dob[ambiguous(site_c_dob)][1]
c(raw = example, mdy = format(mdy(example)),
  dmy = format(dmy(example)))
#>          raw          mdy          dmy 
#> "03/07/1952" "1952-03-07" "1952-07-03"

Site C’s codebook says MM/DD/YYYY, so mdy() is right. Nothing in the values says so: for each ambiguous date, both readings are real dates.

t <- ymd_hms("2026-04-23 14:30:00", tz = "America/Los_Angeles")

with_tz(t, "UTC")
#> [1] "2026-04-23 21:30:00 UTC"
force_tz(t, "UTC")
#> [1] "2026-04-23 14:30:00 UTC"

with_tz preserves the moment and relabels it. force_tz preserves the digits and moves the moment. Use with_tz when combining data recorded in different zones, and force_tz only to repair a timestamp that was stamped with the wrong zone to begin with.

Question. Three things go wrong with dates in this chapter: a multi-format column parsed with one parse_date_time() call, an Excel serial number read with as.Date(), and days(1) used where a duration was meant. Which will your code tell you about?

Answer. None of them. The single parse returns a date for every Site D value and is wrong for some, so an NA count finds nothing. The Excel number becomes a valid date seventy years out. The daylight saving case returns a correct answer to a different question. days(1) gives the same clock time the next day, and dhours(24) gives the instant 24 hours later. R cannot know which you meant. A bug that produces NA is cheap to find. A bug that produces a plausible date is expensive. The assertion that exposes it is a comparison against a per-source parse, or a range check (Habit 8).

16.4 Worked example: a typed patient table

The script below combines the chapter’s steps into one patient-level table. The table holds site and sex as factors with declared levels, the discharge date parsed per site, the arm with its reference set, and three medication summaries. It reuses crf_typed, parse_site_date(), and the parsed meds from the sections above.

med_summary <- meds |>
  summarize(
    n_meds    = n(),
    on_sglt2  = any(drug_class == "SGLT2 inhibitor", na.rm = TRUE),
    on_arni   = any(drug_class == "ARNI", na.rm = TRUE),
    loop_mg_day = sum(
      if_else(str_detect(med_text, regex("furosemide|lasix",
                                         ignore_case = TRUE)),
              dose_mg * per_day, 0)
    ),
    .by = patient_id
  )

patients <- crf_typed |>
  transmute(
    patient_id, site, sex,
    discharge_date = parse_site_date(discharge_date,
                                     as.character(site))
  ) |>
  left_join(randomization |> select(patient_id, arm),
            by = "patient_id", relationship = "one-to-one",
            unmatched = "error") |>
  left_join(med_summary, by = "patient_id",
            relationship = "one-to-one") |>
  mutate(
    arm = factor(arm, levels = c("Usual care", "Home health")),
    across(c(n_meds, loop_mg_day), \(x) coalesce(x, 0)),
    across(c(on_sglt2, on_arni), \(x) coalesce(x, FALSE))
  )

glimpse(patients)
#> Rows: 2,432
#> Columns: 9
#> $ patient_id     <chr> "0001", "0002", "0003", "0004", "0005", "0006", "0007",…
#> $ site           <fct> Site B, Site E, Site E, Site B, Site A, Site A, Site B,…
#> $ sex            <fct> F, F, F, M, F, M, M, M, M, M, F, M, M, F, M, M, F, M, M…
#> $ discharge_date <date> 2024-02-01, 2024-02-01, 2024-02-01, 2024-02-01, 2024-0…
#> $ arm            <fct> Home health, Home health, Usual care, Usual care, Usual…
#> $ n_meds         <dbl> 9, 8, 10, 5, 5, 3, 5, 7, 9, 10, 5, 5, 9, 8, 5, 6, 4, 6,…
#> $ on_sglt2       <lgl> TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRU…
#> $ on_arni        <lgl> TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FA…
#> $ loop_mg_day    <dbl> 0, 0, 80, 0, 80, 0, 80, 0, 20, 0, 0, 0, 80, 0, 0, 80, 0…
patients |>
  summarize(n = n(), sglt2 = mean(on_sglt2), arni = mean(on_arni),
            furosemide = mean(loop_mg_day > 0), .by = arm) |>
  arrange(arm)
#> # A tibble: 2 × 5
#>   arm             n sglt2  arni furosemide
#>   <fct>       <int> <dbl> <dbl>      <dbl>
#> 1 Usual care   1216 0.521 0.318      0.295
#> 2 Home health  1216 0.488 0.295      0.309

Two judgments are written into the code. First, patients with no medication rows get n_meds = 0 and FALSE flags. That is the codebook’s meaning of an absent row. In a file where absence could mean ‘not recorded’, the right value would be NA. Second, loop_mg_day counts furosemide only. Torsemide is about twice as potent per milligram, so adding the two would mix units of effect. A dose-equivalence rule belongs in the analysis plan, not in a cleaning script’s arithmetic.

TipVerification

Confirm the types and levels, and check the medication count against the clean baseline.csv, which records each patient’s number of discharge medications:

baseline <- read_csv(file.path(hf_dir, "baseline.csv"),
                     col_types = cols(patient_id = col_character()),
                     show_col_types = FALSE)

stopifnot(
  nrow(patients) == nrow(randomization),
  identical(levels(patients$arm), c("Usual care", "Home health")),
  identical(levels(patients$site), site_levels),
  inherits(patients$discharge_date, "Date"),
  !anyNA(patients$discharge_date),
  all(patients$n_meds ==
        baseline$discharge_med_count[match(patients$patient_id,
                                           baseline$patient_id)])
)
sum(patients$n_meds == 0)
#> [1] 2

The last assertion checks the string work end to end: a patient whose medication rows were lost or duplicated would fail it.

Question. The worked example converts arm to a factor at the end of the script, after both joins. Why not at the start, when randomization.csv is read?

Answer. Here the arm is not a join key, so converting early would do no harm. The reason is where the declaration sits. Converting at the end keeps every column that a join, filter, or recode touches in its plain type. It also puts the reference level beside the analysis that depends on it, where a reviewer looks for it. Suppose arm had been a key, or had been recoded after the read. An early factor would then have needed its levels managed at every step, and a join would have given it the union of two level sets.

16.5 Rules of thumb

  1. Habit 7: convert late, validate early. Keep categories as character until the model, then declare the levels and the reference in writing, and assert them.
  2. Habit 8: a parser that always returns a value cannot tell you it guessed. A first-number regex, a multi-order date parse, and an Excel serial date all produce plausible values. Compare against a second, stricter reading and count the disagreements.
  3. Habit 12: every model output is a hypothesis until verified. A model’s regular expression or date parser is checked on the full column, with a cross-tabulation of what it matched.

16.6 Cumulative practice

  1. Cumulative (uses Chapter 18). Add each patient’s discharge potassium from labs.csv (visit -01, test K) to patients, with a declared relationship, after handling the duplicated discharge potassium that Chapter 15 found. Flag the patients discharged on a potassium supplement (K-Dur, KCl, or potassium chloride, in any case) and compare their median discharge potassium with everyone else’s.
  2. On your own work (no key). Write a function that takes any character column and returns each value with its count and proportion, and use it to audit the categorical variables in a recent analysis of yours. Report every level you did not expect.
labs <- read_csv(file.path(hf_dir, "labs.csv"),
                 col_types = cols(patient_id = col_character()),
                 show_col_types = FALSE)
discharge_k <- labs |>
  filter(str_ends(visit_id, "-01"), test_code == "K") |>
  distinct() |>
  filter(n() == 1, .by = patient_id) |>
  select(patient_id, k_discharge = value)

k_supplement <- meds |>
  filter(str_detect(med_text, regex("k-dur|\\bkcl\\b|potassium chloride",
                                    ignore_case = TRUE))) |>
  distinct(patient_id) |>
  mutate(on_k = TRUE)

with_k <- patients |>
  left_join(discharge_k, by = "patient_id",
            relationship = "one-to-one") |>
  left_join(k_supplement, by = "patient_id",
            relationship = "one-to-one") |>
  mutate(on_k = coalesce(on_k, FALSE))

stopifnot(nrow(with_k) == nrow(patients))
with_k |>
  summarize(n = n(), missing_k = sum(is.na(k_discharge)),
            median_k = median(k_discharge, na.rm = TRUE), .by = on_k)
#> # A tibble: 2 × 4
#>   on_k      n missing_k median_k
#>   <lgl> <int>     <int>    <dbl>
#> 1 TRUE    697         0      4.3
#> 2 FALSE  1735         1      4.3

distinct() removes the exact duplicate. The n() == 1 filter sets aside the potassium that was re-run with a different value, as Chapter 15 did. That patient’s potassium is therefore missing rather than invented, and the missing_k column shows it. The orphan patient in labs.csv never reaches patients, because the left join keeps only randomized patients. Any comparison of supplemented patients here is descriptive. Potassium supplements are prescribed in response to low potassium, so any difference is confounded by indication.

16.7 Cheat sheet

Table 16.2: Factors, strings, and dates at a glance.
Task Code Guards against
Collapse spellings to levels fct_collapse(x, F = c("F", "f", "2"), M = c(...)) Several codes for one category
Rename levels fct_recode(x, Female = "F") Codes in tables and figures
Declare the level set factor(x, levels = site_levels) Variant spellings becoming categories
Set the reference fct_relevel(arm, "Usual care") Effects reported in the wrong direction
Order by frequency or value fct_infreq(x), fct_reorder(x, y, median) Alphabetical order in plots
Lump rare levels fct_lump_min(x, min = 10) Unstable estimates for tiny groups
Count levels, including empty fct_count(x) Dropped or unexpected levels
Detect, ignoring case str_detect(x, regex("lasix", ignore_case = TRUE)) Undercounts from capitalization
Capture parts str_match(x, "(\\d+(?:\\.\\d+)?)\\s*(mg\|mcg)") First-number parsing
Normalize text str_to_lower(), str_squish(), str_remove_all(x, "\\.") Spellings that differ only in form
Parse one known format ymd(), mdy(), dmy() Guessed conventions
Find ambiguous dates !is.na(mdy(x)) & !is.na(dmy(x)) & mdy(x) != dmy(x) Silent day-month swaps
Excel serial dates janitor::excel_numeric_to_date(x) Dates seventy years off
Change displayed zone with_tz(t, "UTC") Mixing zones across sources
Repair a wrong zone force_tz(t, "UTC") Timestamps stamped in the wrong zone
Calendar day versus elapsed time days(1) versus ddays(1) One-hour errors at daylight-saving transitions
Add months safely d %m+% months(3) Invalid dates such as February 30

16.8 Further reading

16.9 Prerequisites answers

  1. Before R 4.0, read.csv() and data.frame() converted character columns to factors by default. This caused three problems. Levels appeared where a character string was expected, for example as merge keys. New values could not be assigned without first adding a level. And as.numeric() on a numeric-looking column returned codes rather than values. R 4.0 made FALSE the default, and the old default now matters only when reading older code.
  2. str_detect(string, pattern) returns a logical vector of the same length as string: TRUE where the pattern matched, FALSE elsewhere. str_extract() returns a character vector, the first matching substring per input, or NA where there was no match. str_detect() is for filtering and flagging, and str_extract() is for pulling out parts.
  3. parse_date_time(x, orders = c('ymd', 'mdy', 'Bdy')) parses all three. A fourth value '03/04/2026' is valid as March 4 and as April 3. With only mdy among the slash orders it is read as March 4. If dmy were also listed, however, the reading would depend on the order of the list and on the other values in the vector, with no warning. Parse each source with its single documented order, and count the values that a second convention would read differently.