Reading an export with readr and readxl

readr guesses, and tells you what it guessed

library(readr)
library(here)

PATH <- here("data", "raw", "muac-screening-artibonite-2024.v1.csv")
muac <- read_csv(PATH)

read_csv() prints the specification it inferred. That message is not noise to suppress — it is the only moment the reader tells you what it decided, and on this register it decides eight things:

child_id       character
commune        character
screening_date date
age_months     numeric
sex            character
muac_mm        numeric
oedema         character
outcome        character

Most of that is right. Two parts are worth taking control of.

The guess uses the first 1,000 rows

guess_max defaults to 1,000. A column that is empty for the first thousand rows and numeric afterwards is guessed as logical — because NA is logical — and every later value becomes NA.

This is the readr equivalent of pandas’ mixed-dtype warning, and it fails more quietly: you get a column full of missing values and a problems() table you did not look at.

# Deliberate: read everything as text, look, then convert.
peek <- read_csv(PATH, col_types = cols(.default = col_character()), n_max = 200)
print(peek)

Two hundred rows is enough to see the shape and cheap on a large file.

Declare the columns

muac <- read_csv(
  PATH,
  col_types = cols(
    child_id       = col_character(),
    commune        = col_character(),
    screening_date = col_date(format = "%Y-%m-%d"),
    age_months     = col_integer(),
    sex            = col_character(),
    muac_mm        = col_integer(),
    oedema         = col_character(),
    outcome        = col_character()
  ),
  na = c("", "NA", "-99")
)

Three things that buys you.

Leading zeros survive. col_character() on an identifier is the whole lesson: a facility code 007 read as a number becomes 7, the join to the facility list fails for exactly the codes that had a leading zero, and the failure looks like missing facilities rather than a type error.

The rule generalises: if you will never do arithmetic on it, it is not a number. Facility codes, phone numbers, cluster identifiers, household numbers and administrative codes are text that happens to be written with digits.

The date format is stated. An inferred format can change between files as the mix of values changes. 01/02/2024 is either 1 February or 2 January and the file will not tell you which.

The sentinel is declared. This register codes an unmeasured MUAC as -99. Read without na, that is a number:

mean(muac$muac_mm)
#> [1] NA

Which brings us to the most important difference from pandas.

mean() returns NA, and that is a feature

pandas skips missing values by default; R does not.

mean(muac$muac_mm)
#> [1] NA

mean(muac$muac_mm, na.rm = TRUE)
#> [1] 139.92

R is loud where pandas is silent. The NA is a question: do you know there are missing values here, and have you decided what they mean? Answering it with na.rm = TRUE is fine — answering it without noticing you were asked is how a denominator quietly changes.

na.rm = TRUE drops the missing from the numerator and from the denominator. That is usually right for a mean and usually wrong for a coverage rate, where the unmeasured child is still a child who was screened. Count them:

sum(is.na(muac$muac_mm))
#> [1] 72

na is file-wide, not per column

muac <- read_csv(PATH, na = c("", "NA", "-99"))

readr applies na across every column. pandas can scope a sentinel to one column with na_values = {"muac_mm": ["-99"]}; readr cannot, so if another column legitimately holds -99, handle it afterwards:

muac <- read_csv(PATH, na = c("", "NA")) |>
  dplyr::mutate(muac_mm = dplyr::na_if(muac_mm, -99L))

na_if() is the scoped form and is worth preferring whenever more than one column is numeric.

problems() is a habit, not a rescue

When a value does not fit its declared type, readr does not stop. It records the row, the column, what it expected and what it found.

issues <- problems(muac)
nrow(issues)
#> [1] 0

Zero on this file, and that is the point of checking: the number is only meaningful if you look at it every time. A run that quietly parsed 200 values as NA looks identical to a clean one until you ask.

if (nrow(problems(muac)) > 0) {
  print(problems(muac))
  stop("Input did not match its column specification.")
}

In a script that produces a reported figure, that stop() is correct. Refusing to run beats producing a table with a silently empty column.

Verify the read before doing anything with it

Four checks, one minute, and they catch nearly everything:

library(dplyr)

check <- function(df, expected_rows = NULL) {
  cat("rows/cols ", nrow(df), ncol(df), "\n")
  print(dplyr::glimpse(df))
  print(colSums(is.na(df)))
  if (!is.null(expected_rows)) {
    stopifnot(nrow(df) == expected_rows)
  }
}

check(muac, expected_rows = 4218)

The row-count assertion is the one people skip. A file that arrives with 4,190 rows instead of 4,218 has lost something between the server and you, and the script should say so rather than quietly report a smaller caseload.

Separators, encodings and thousands

A file produced on a French or Spanish Windows machine is frequently semicolon-separated, because the comma is the decimal mark there.

epi <- read_csv2(PATH)                       # ; separator, , decimal
epi <- read_delim(PATH, delim = "\t")        # tabs

read_csv2() is the European variant — semicolon and comma — and it exists precisely because this is common enough to deserve its own function.

Encoding shows up in commune names: Gonaïves, L’Estère, Anse-Rouge.

muac <- read_csv(PATH, locale = locale(encoding = "UTF-8"))
muac <- read_csv(PATH, locale = locale(encoding = "latin1"))   # what Excel often writes

latin1 never errors, because every byte is a valid latin1 character. That makes it a poor diagnostic and a decent fallback: if a file read as latin1 shows Gonaïves, it was UTF-8 all along and you told the reader otherwise.

Thousands separators turn a number into text:

epi <- read_csv(PATH, locale = locale(grouping_mark = ","))

Without it, target_population arrives as "1,480" — a character column on which every arithmetic operation fails or concatenates.

Excel

library(readxl)

excel_sheets(path)
#> [1] "Cover" "Data" "Codebook" "Sheet3"

data <- read_excel(path, sheet = "Data")

read_excel() without sheet returns the first sheet, which in a programme workbook is usually a cover page. Always list the sheets first.

The header is not row one

data <- read_excel(path, sheet = "Data", skip = 3)

Programme workbooks routinely carry a title, a logo row and a blank line above the real header. The symptom is column names like ...1, ...2.

Merged cells

A merged cell holds its value in the top-left position and nothing in the rest. When a district name is merged across its facilities, only the first row of each district has one:

data <- data |> tidyr::fill(district, .direction = "down")

fill() is correct here and dangerous in general: it is only right because the blanks came from merging, not from non-response. Never apply it to a column where a blank might mean “not answered”.

Serial dates

Excel stores dates as a number of days since 1899-12-30. read_excel() usually converts them, but a column typed as text in the workbook comes through as the raw serial.

data$screening_date <- as.Date(as.numeric(data$screening_date), origin = "1899-12-30")

If a date column arrives as five-digit numbers, this is why.

Types in Excel

data <- read_excel(
  path,
  sheet = "Data",
  col_types = c("text", "text", "date", "numeric", "text", "numeric", "text", "text")
)

readxl takes a positional vector rather than a named specification, so a column added to the export shifts everything after it. Check ncol() against the length of your vector, or read as text and convert with dplyr.

The read function, assembled

Everything above belongs in one function the scripts share:

# R/read_register.R
read_register <- function(path) {
  muac <- readr::read_csv(
    path,
    col_types = readr::cols(
      child_id       = readr::col_character(),
      commune        = readr::col_character(),
      screening_date = readr::col_date(format = "%Y-%m-%d"),
      age_months     = readr::col_integer(),
      sex            = readr::col_character(),
      muac_mm        = readr::col_integer(),
      oedema         = readr::col_character(),
      outcome        = readr::col_character()
    ),
    na = c("", "NA", "-99")
  )

  if (nrow(readr::problems(muac)) > 0) {
    print(readr::problems(muac))
    stop("Input did not match its column specification.")
  }
  stopifnot(!any(is.na(muac$child_id)))

  muac
}

One function, one place to fix when the next export changes.

What comes next

The CSV is in and its types are right. The next lesson handles the files that carry more than values — Stata and SPSS exports, where 1 = Yes travels with the data and haven is the reason to read them in R even when you will analyse them somewhere else.