13 Getting Data In: Files
Every data chapter after this one begins with a data frame already in memory, as though it had arrived there by itself. It did not. Read the HF-HOME case report form (CRF) export with base R’s read.csv(), and 882 of 2,432 patient identifiers lose their leading zeros. Read it with readr, and the zeros survive everywhere except in 117 identifiers. One site’s system had already stripped those before the file was written. Neither reader warns. A join to a randomization list that kept its zeros then drops every one of those patients. No downstream test will catch a value misread at import. This chapter therefore treats getting data in as the first step of the analysis.
It is a capital mistake to theorize before one has data.
Arthur Conan Doyle, A Scandal in Bohemia (1891)
Reading time. About 25 minutes.
You’ll need. Nothing beyond R. The chapter looks at a file’s bytes with readBin(); the optional Chapter 5 shows the same check at the terminal. The code uses:
You’ll be able to.
- Read a delimited file with a full column specification, and compute what the default read got wrong.
- Detect the file pathologies of a clinical export (byte-order mark, line endings, lost leading zeros, missing-value codes, per-site formats) and write an assertion for each.
- Keep the data as received read-only, and turn the checks on an import into assertions that run on every render.
Outline.
If these answers come quickly, skip to the next chapter. The answers are in Section 13.7.
-
read_csv()guesses the type of every column from the first thousand rows. Name two ways this guess can be wrong in a manner that no error message will reveal, and say what you should do instead. - A collaborator corrects three cells of the raw export in a spreadsheet and sends it back. The corrections are right. Why should you not use the corrected file?
- A script that reads
x$patient_idworks on your laptop and returnsNULLin a container. The file begins with a byte-order mark. What is happening?
13.1 Three ways data arrive
Data arrive as a file, through an interface, or off a web page. The three differ in how much control you have over what arrives (Table 13.1). A file is a snapshot someone made for you. Its pathologies are all present the moment you receive it. An interface, such as the application programming interface (API) of REDCap or of an institutional data warehouse, is a query against a living system, so the code is deterministic and the data source is not. A scrape is what you resort to when the data exist but no interface does. This chapter reads files; Chapter 14 takes up interfaces and pages.
| Source | What can go wrong | What you control | How to freeze it |
|---|---|---|---|
| File | Encoding, line endings, delimiter, guessed column types, missing-value codes | Only how you read it | Keep the file as received, read-only, with a checksum |
| Interface | Authentication, pagination, rate limits, a response shape that changes without notice | The query | Write each response to a dated raw file with a provenance note |
| Web page | Terms of use, page structure that is not a contract, personal data | The selector, and whether to scrape at all | Save the page or the parsed table, dated, once |
The routes differ in almost everything except their final step. Figure 13.2 puts them side by side. We do so because the checking step they share is the step most often skipped.
flowchart TD F["<b>A file</b><br/><i>delimiter, encoding,<br/>column types</i>"] A["<b>An interface</b><br/><i>authentication, pagination,<br/>rate limits, JSON shape</i>"] W["<b>A web page</b><br/><i>terms of use, selectors,<br/>silent structure changes</i>"] F --> C["<b>Checked analysis file</b><br/><i>types asserted, levels counted,<br/>missingness described</i>"] A --> C W --> C C --> D["Everything downstream<br/><i>wrangling, joining, modeling</i>"]
13.1.1 The raw-data rule
The three routes converge on a single discipline, the raw-data rule. The data as received are read-only, and every change to them lives in a script rather than in the file (Habit 2). At the acquisition step the rule has a second clause. Whatever you acquire, write it down and date it. Never acquire it again in the same step that analyzes it. Chapter 15 applies the same rule to cleaning.
The rule follows from treating acquisition as a step rather than a preamble. The instinct is to regard ‘getting the data’ as something that happens before the analysis starts, and therefore something that need not be scripted to the same standard. This is backwards. The acquisition determines which rows exist, and no amount of care downstream can recover a row that was silently dropped at import. The script that acquires is the first script in the pipeline and is held to the same standard as the last.
Question. A collaborator opens the CRF export in a spreadsheet, fixes three obviously wrong site labels by hand, saves it, and sends it back. The fixes are correct. What has been lost?
Answer. The evidence. The file you hold is no longer the file the sites produced. Nothing records which three cells changed or why. The next export from the sites will arrive with the same three errors and no script to correct them. The spreadsheet may also have rewritten other cells on save, for example by stripping leading zeros from identifiers. Under the raw-data rule the collaborator’s three corrections become three lines in the cleaning script, run against the untouched export.
Before assembling data from several routes, ask whether the people in it would be surprised to find their records joined. Linking sources that are each harmless can create a profile none of them disclosed. Section 32.3 works through the argument, and Chapter 14 applies it to scraping.
13.2 Reading files deliberately
The tidyverse import story is one function per format: readr::read_csv() and its relatives for delimited text, readxl::read_excel() for spreadsheets, haven::read_sas() and haven::read_xpt() for the SAS world of Chapter 29. They share an interface and a philosophy: they parse, they report what they did, and they fail loudly if you tell them what to expect. The generic tour is in R for Data Science (Wickham et al., 2023, ch. 7); this section reads one real export.
13.2.1 The default read
The HF-HOME CRF export, crf_export.csv, holds one row per patient from five hospitals, each of which wrote its own conventions into the file (the codebook is README.md beside it). Read it twice with the defaults, once with base R and once with readr:
library(tidyverse)
hf_dir <- file.path("..", "data", "raw_data", "hfhome")
crf_path <- file.path(hf_dir, "crf_export.csv")
naive_base <- read.csv(crf_path)
naive_readr <- read_csv(crf_path, show_col_types = FALSE)
str(naive_base[, c("patient_id", "dob", "sbp_discharge")])
#> 'data.frame': 2432 obs. of 3 variables:
#> $ patient_id : int 1 2 3 4 5 6 7 8 9 10 ...
#> $ dob : chr "1946-10-11" "19460328" "19610907" "1955-06-27" ...
#> $ sbp_discharge: int 154 159 110 105 117 125 150 135 115 107 ...
spec(naive_readr)
#> 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()
#> )Both reads succeed in silence, and both are wrong in ways the output does not show. read.csv() made the identifier an integer, so 0007 is now 7. readr kept it as text. That is a property of readr’s guesser and not of R, and relying on it is a mistake. The day a collaborator uses read.csv(), or the file arrives through readxl, the zeros are lost. Both readers left the dates as text and read Site D’s missing-value code, -99, as a blood pressure.
13.2.2 The specified read
The remedy is to say what you expect. A full column specification names every column’s type, and the na argument names every string that means ‘missing’:
crf_spec <- 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 <- read_csv(crf_path, col_types = crf_spec,
na = c("", "NA", "-99"))
problems(crf)
#> # A tibble: 0 × 5
#> # ℹ 5 variables: row <int>, col <int>, expected <chr>, actual <chr>, file <chr>The dates stay as text on purpose. The sites wrote them in four formats, and a col_date() here would be a promise the file cannot keep. Chapter 15 parses them site by site. One na list applies to every column. That is safe here only because no identifier or label in this file can legitimately read -99. The assertions below check that assumption.
Compute the difference between the reads instead of judging it by eye:
iso_date <- "^\\d{4}-\\d{2}-\\d{2}$"
read_diffs <- tibble(
column = c("patient_id", "patient_id", "sbp_discharge",
"weight_kg", "discharge_date", "site"),
problem = c("leading zeros dropped by read.csv()",
"leading zeros already missing in the file",
"-99 read as a pressure by both default reads",
"-99 read as a weight by both default reads",
"not in ISO 8601 form",
"distinct spellings of five sites"),
rows = c(
sum(as.character(naive_base$patient_id) != crf$patient_id),
sum(nchar(crf$patient_id) < 4),
sum(naive_readr$sbp_discharge == -99, na.rm = TRUE),
sum(naive_readr$weight_kg == -99, na.rm = TRUE),
sum(!str_detect(crf$discharge_date, iso_date)),
n_distinct(crf$site)
)
)
read_diffs
#> # A tibble: 6 × 3
#> column problem rows
#> <chr> <chr> <int>
#> 1 patient_id leading zeros dropped by read.csv() 882
#> 2 patient_id leading zeros already missing in the file 117
#> 3 sbp_discharge -99 read as a pressure by both default reads 15
#> 4 weight_kg -99 read as a weight by both default reads 6
#> 5 discharge_date not in ISO 8601 form 1161
#> 6 site distinct spellings of five sites 15The specification repairs what a read can repair (rows 1, 3, and 4). That is, the identifier stays text and the missing-value code becomes NA. Rows 2, 5, and 6 are facts about the file. No reader can fix them, so a cleaning script must. The 117 short identifiers are the ones Site E’s system stripped before export. Restoring them with str_pad() is safe only because the codebook says every identifier has four digits (Chapter 15).
A specification is also an assertion. Declare the discharge date as an ISO 8601 date, which the codebook promises for the clean tables but not for this export, and readr says so, row by row:
strict_spec <- crf_spec
strict_spec$cols$discharge_date <- col_date()
strict <- read_csv(crf_path, col_types = strict_spec,
na = c("", "NA", "-99"))
#> Warning: One or more parsing issues, call `problems()` on your data frame for details,
#> e.g.:
#> dat <- vroom(...)
#> problems(dat)
nrow(problems(strict))
#> [1] 1161
strict |>
count(site = str_to_title(str_squish(site)),
unparsed = is.na(discharge_date))
#> # A tibble: 5 × 3
#> site unparsed n
#> <chr> <lgl> <int>
#> 1 Site A FALSE 700
#> 2 Site B FALSE 571
#> 3 Site C TRUE 498
#> 4 Site D TRUE 373
#> 5 Site E TRUE 290Every failure is at Sites C, D, and E, the three that do not write ISO dates. Suppose next month’s extract changes. A full specification turns the change into a warning and a problems() table naming the row, the column, and what was expected. Without it you get a data frame that is quietly different from last month’s. An empty problems() table is a passed assertion; inspect it after every read.
Whether an identifier survives depends on which reader you call. readr guesses patient_id as text here only because some values have leading zeros in its sample. read.csv(), data.table::fread(), and a spreadsheet opened and re-saved all make it a number. Two files read by coercing readers then join without error on integers, and the row count is right. However, every identifier that carried a leading zero no longer matches the source system. Write col_character() for every identifier, on every read of a file you did not create.
Use a model to draft the specification, from the codebook rather than from the data.
Context. I am reading
crf_export.csv, a raw CRF export from five sites. Its header and the codebook entries for each column are below, followed by three synthetic rows I made up in the file’s format. The identifier is four digits with leading zeros.Constraints. Use
readr::read_csv()with a completecols()specification, every column named, no.default. Identifiers arecol_character(). Keep any column whose format varies by site as character. Declare the missing-value codes the codebook lists inna.Criteria. Justify each column’s type in one line, citing the codebook. List every assumption you made that the codebook does not state. Write a
stopifnot()that fails if the identifier is not character or if any numeric column holds a value outside a range you state.
Failure mode. A model infers types from the rows it sees, exactly as read_csv() does. It therefore inherits the guesser’s blindness. Shown rows without leading zeros, it makes the identifier an integer. Shown no -99, it omits the code. Check. Run the read, then problems(), then the checks in Section 13.2.4. Compare the model’s na list with the missing-value table in Chapter 15. Send the codebook and synthetic rows, never the export (Section 12.3).
13.2.3 Pathologies that do not announce themselves
Two more properties of this file are invisible on screen. Its first three bytes are a UTF-8 byte-order mark (BOM), and every line ends in a carriage return and a line feed (CRLF), the Windows convention. Chapter 5 shows both at the byte level. What they do depends on the reader and on the machine, and the claims commonly made about them are too strong. Compare three reads:
readBin(crf_path, "raw", n = 3)
#> [1] ef bb bf
c(
`read_csv()` = names(naive_readr)[1],
`read.csv(), UTF-8 locale` = names(naive_base)[1],
`read.csv(), C locale` = withr::with_locale(
c(LC_CTYPE = "C"), names(read.csv(crf_path))[1]
)
)
#> read_csv() read.csv(), UTF-8 locale read.csv(), C locale
#> "patient_id" "patient_id" "X...patient_id"readr removes the mark. So does base R’s read.csv() in a UTF-8 locale, the default on current macOS, Windows, and most Linux desktops. R falls back to the C (POSIX) locale in a container or on a server where no locale has been set. In that locale read.csv() keeps the three bytes, and check.names turns them into dots. The first column is then X...patient_id, and x$patient_id returns NULL.1 The same script works on your laptop and fails in the container. Worse, it may return NULL into a check.
1 As run for this edition (R 4.6.1, readr 2.2.0, data.table 1.18.4, macOS): data.table::fread() also removes the mark. Python’s csv module keeps it when the file is opened with encoding='utf-8', so the first name is '\ufeffpatient_id', and removes it with encoding='utf-8-sig'. Shell tools keep it (Chapter 5).
The carriage returns are harmless to every R reader shown here, which accept either line ending. They matter at the shell, where cut and grep see a \r at the end of every last field, and in any parser you write yourself.
Table 13.2 collects the file pathologies of this export and three that it happens not to have, with how each shows itself and the assertion that makes it loud.
| Pathology | Symptom | Detection | Fix | Assertion |
|---|---|---|---|---|
| Byte-order mark | First column name changes with the reader and locale |
readBin(f, "raw", 3) is ef bb bf
|
read_csv(), or fileEncoding = "UTF-8-BOM"
|
names(x)[1] == "patient_id" |
| CRLF line endings |
\r on the last field, at the shell |
od -c, cat -e (Chapter 5) |
None needed in R; never edit the raw file | Last numeric column parses with no problems()
|
| Leading zeros lost |
7 where the key is 0007; joins drop patients |
count(x, nchar(patient_id)) |
col_character(); str_pad() only by the codebook |
all(nchar(x$patient_id) == 4) |
| Missing-value codes |
-99 averaged as a pressure |
Count codes by site (Chapter 15) | na = c("", "NA", "-99") |
all(between(x$sbp, 50, 250), na.rm = TRUE) |
| Formats by site | Dates stay text; a single parse misreads some | count(x, site, str_detect(d, iso)) |
Character at read; parse per site (Chapter 16) |
!anyNA(date) after parsing |
| Label variants | Fifteen spellings of five sites | count(x, site) |
str_squish(), str_to_title()
|
all(site %in% expected) |
| Wrong delimiter | One wide column | ncol(x) |
read_tsv() or delim =
|
ncol(x) == 8 |
| Wrong encoding |
Z\xfcrich for Zürich |
stringi::stri_enc_isutf8() |
locale = locale(encoding = "latin1") |
All strings valid UTF-8 |
| Excel serial dates | Five-digit numbers in a date column | A date-named column that is numeric |
janitor::excel_numeric_to_date() (Chapter 16) |
Dates within the study period |
13.2.4 Checks that run on every render
Every analyst checks data. However, a check run at the console leaves nothing behind. It is not re-run when the extract is refreshed, and a reviewer cannot tell which pathologies were looked for. Harrell’s R Workflow (Harrell, 2025) makes data checking and data overview numbered chapters of the workflow, with their output a section of the rendered report. A check that lives in the document executes on every render and fails loudly when the next extract violates an assumption the first one satisfied.
In the tidyverse idiom, the artifact is a function that returns one result per row of the checklist, applied to each read:
randomization <- read_csv(file.path(hf_dir, "randomization.csv"),
col_types = cols(.default = col_character()))
check_import <- function(x) {
c(
header = identical(names(x)[1], "patient_id"),
rows = nrow(x) == nrow(randomization),
id_is_text = is.character(x$patient_id),
id_width_4 = all(nchar(as.character(x$patient_id)) == 4),
id_unique = !anyDuplicated(x$patient_id),
sbp_range = all(between(x$sbp_discharge, 50, 250),
na.rm = TRUE),
weight_range = all(between(x$weight_kg, 30, 250), na.rm = TRUE),
site_labels = all(x$site %in% paste("Site", LETTERS[1:5]))
)
}
tibble(
check = names(check_import(crf)),
read.csv = check_import(naive_base),
readr = check_import(naive_readr),
specified = check_import(crf)
)
#> # A tibble: 8 × 4
#> check read.csv readr specified
#> <chr> <lgl> <lgl> <lgl>
#> 1 header TRUE TRUE TRUE
#> 2 rows TRUE TRUE TRUE
#> 3 id_is_text FALSE TRUE TRUE
#> 4 id_width_4 FALSE FALSE FALSE
#> 5 id_unique TRUE TRUE TRUE
#> 6 sbp_range FALSE FALSE TRUE
#> 7 weight_range FALSE FALSE TRUE
#> 8 site_labels FALSE FALSE FALSEThe specified read passes every check a read can pass. The two failures it keeps, the identifier width and the site labels, are the repairs Chapter 15 makes. The import script records them. It also asserts that they are confined to where the codebook says they are, so that a new kind of failure stops the build.
The build-time form of the checks is a stopifnot() that prints nothing when the file is as expected:
crf_checks <- check_import(crf)
short_id_sites <- crf |>
filter(nchar(patient_id) < 4) |>
distinct(site = str_to_title(str_squish(site)))
stopifnot(
all(crf_checks[c("header", "rows", "id_is_text", "id_unique",
"sbp_range", "weight_range")]),
identical(short_id_sites$site, "Site E"),
nrow(problems(crf)) == 0
)If Site B’s system begins dropping zeros next quarter, the second assertion fails and names the change, instead of the join in Chapter 18 quietly losing Site B’s patients.
skimr::skim() and count() belong beside the assertions: skim() reports what each column looks like, and count() exposes the level the codebook did not mention. The first two are for the reader; the stopifnot() is for the build. Chapter 27 develops the assertion side into a test suite.
13.2.5 Exercises
- Using
crffrom the specified read, count by site the patients whosepatient_idmatches no row ofrandomization.csv. Pad the identifiers to four digits and show that the anti-join is then empty. - Add two checks to
check_import(): one that fails when the columns are not exactly those in the codebook, in order, and one that fails when any numeric column holds the value-99. Report the result for all three reads.
crf |>
anti_join(randomization, by = "patient_id") |>
count(site = str_to_title(str_squish(site)))
#> # A tibble: 1 × 2
#> site n
#> <chr> <int>
#> 1 Site E 117
padded <- crf |>
mutate(patient_id = str_pad(patient_id, width = 4, pad = "0"))
stopifnot(nrow(anti_join(padded, randomization,
by = "patient_id")) == 0)Every unmatched patient is at Site E, which is the codebook’s account of the file. A join on the unpadded key would have dropped them all without an error (Chapter 18).
codebook_cols <- c("patient_id", "site", "sex", "dob",
"discharge_date", "ef_pct", "sbp_discharge",
"weight_kg")
check_import_2 <- function(x) {
numeric_cols <- c("ef_pct", "sbp_discharge", "weight_kg")
c(
check_import(x),
columns = identical(names(x), codebook_cols),
no_code = !any(vapply(numeric_cols,
\(v) any(x[[v]] == -99, na.rm = TRUE),
logical(1)))
)
}
tibble(
check = names(check_import_2(crf)),
read.csv = check_import_2(naive_base),
readr = check_import_2(naive_readr),
specified = check_import_2(crf)
) |>
tail(2)
#> # A tibble: 2 × 4
#> check read.csv readr specified
#> <chr> <lgl> <lgl> <lgl>
#> 1 columns TRUE TRUE TRUE
#> 2 no_code FALSE FALSE TRUEAll three reads have the codebook’s columns in this locale. Under the C locale, read.csv() fails the columns check, because its first name is X...patient_id. Only the specified read passes no_code. The -99 check is narrower than the range checks, so when it fails it names the cause as well as the symptom.
Question. Your cohort has 1,000 patients. You read the demographics file and the visits file, both without a column specification, join them on patient_id, and get 1,000 rows, which is what you expected. What might still be wrong?
Answer. If one file was read by a coercing reader and the other by readr, the join fails with a type error, which is the good case. If both were read by coercing readers, both identifiers are integers. The join succeeds and the row count is right. However, every identifier that carried a leading zero no longer matches the source system. The analysis is internally consistent and externally wrong. The day someone asks you to re-identify a case for a data query, you cannot. The row count was never sensitive to the failure. Read identifiers as character, always, and assert it.
13.3 Rules of thumb
- Habit 2: raw data are evidence. Keep what you received read-only and unedited, and make every change in a script run against the untouched file.
- Habit 7: convert late, validate early. Specify every column, read identifiers as character, and keep site-dependent formats as text until a script parses them by rule.
-
Habit 8: a bug that produces a plausible number is the expensive kind. A
-99averaged as a pressure raises nothing; write the assertion for each row of the import checklist.
13.4 Cumulative practice
- On your own work (no key). Take a delimited file you did not create. Read it once with no arguments and once with a full column specification, compute the differences as in Section 13.2, and say for each disagreement which read is correct.
13.5 Cheat sheet
| Task | Code | Guards against |
|---|---|---|
| Read with a contract | read_csv(f, col_types = cols(...), na = c("", "NA", "-99")) |
Guessed types; missing codes as data |
| Keep identifiers | patient_id = col_character() |
Lost leading zeros |
| Read everything as text | cols(.default = col_character()) |
Conversions before you have looked |
| See parse failures | problems(x) |
A changed extract read quietly |
| Look at the first bytes | readBin(f, "raw", n = 3) |
Byte-order marks |
| Declare an encoding | locale = locale(encoding = "latin1") |
Mangled accented names |
| Assert the import | stopifnot(names(x)[1] == "patient_id", ...) |
Reader and locale differences |
13.6 Further reading
-
(Wickham et al., 2023), the data-import and spreadsheet chapters, the canonical applied treatment of
readrandreadxl. -
(Harrell, 2025), Chapters 7 and 8, on data checking and data overview as reported steps of the workflow. The code is
data.tableandHmiscrather than tidyverse (Appendix A covers the dialect), but the structural argument transfers intact.
13.7 Prerequisites answers
- The guess is made from a sample (the first thousand rows by default), so any pathology that does not appear in the sample is invisible to it. Two consequences produce no error. First, a code used upstream to mean ‘missing’, such as -99, is guessed as a number and enters the analysis as a measurement, dragging every mean it touches. Second, a column of dates in an ambiguous or site-specific format is guessed as character and sorts as text. Or it is parsed under the wrong convention, so that 03/04/2026 is March in one file and April in another. A third hazard is commonly misdescribed: whether an identifier with leading zeros survives depends on which reader you call.
readrkeeps0007as text when its sample shows the zeros; base R’sread.csv()and most spreadsheet exports make it the integer 7. The remedy in every case is a fullcol_typesspecification and an explicitnaargument, treated as assertions, withproblems()checked after every read. - Because the file is evidence and the corrections are not recorded. Nothing says which cells changed or why. The next export will arrive with the same errors and no script to fix them. The spreadsheet may also have rewritten other cells on save, for example by stripping leading zeros. Keep the export as received and make the three corrections lines of the cleaning script.
- Base R’s
read.csv()removes the mark in a UTF-8 locale but keeps it in the C (POSIX) locale that a container or server often falls back to. Therecheck.namesturns the three bytes into dots, the first column is namedX...patient_id, andx$patient_idreturnsNULL. Read withread_csv(), which removes the mark in either locale, and assertnames(x)[1] == "patient_id".