cassionData Analysis

Back to the lessonLesson 2 of 8A project that reopens

Reading an export with readr and readxl

The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.

Slides · PDFSlides · PowerPoint

  1. Slide 1 / 31

    What this lesson covers

    • readr guesses, and tells you what it guessed
    • The guess uses the first 1,000 rows
    • Declare the columns
    • mean() returns NA, and that is a feature
    • na is file-wide, not per column
    • problems() is a habit, not a rescue
    • Verify the read before doing anything with it
    • Separators, encodings and thousands
    • Excel
    • The read function, assembled
    • What comes next
    Speaker notes
    Column specifications instead of guesses, problems() as a habit rather than a rescue, and the Excel traps — multiple sheets, a header that is not row one, merged cells and serial dates.

  2. Slide 2 / 31

    readr guesses, and tells you what it guessed — In R

    library(readr)
library(here)

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

  3. Slide 3 / 31

    readr guesses, and tells you what it guessed — Example

    child_id       character
commune        character
screening_date date
age_months     numeric
sex            character
muac_mm        numeric
oedema         character
outcome        character
    Speaker notes
    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: Most of that is right. Two parts are worth taking control of.

  4. Slide 4 / 31

    The guess uses the first 1,000 rows — In R

    # Deliberate: read everything as text, look, then convert.
peek <- read_csv(PATH, col_types = cols(.default = col_character()), n_max = 200)
print(peek)
    Speaker notes
    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. Two hundred rows is enough to see the shape and cheap on a large file.

  5. Slide 5 / 31

    Declare the columns — In R

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

  6. Slide 6 / 31

    Declare the columns

    • Leading zeros survive — col_character() on an identifier is the whole lesson: a facility code 007 read as a number…
    • The date format is stated — An inferred format can change between files as the mix of values changes
    • The sentinel is declared — This register codes an unmeasured MUAC as -99
    Speaker notes
    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:

  7. Slide 7 / 31

    Declare the columns — In R

    mean(muac$muac_mm)
#> [1] NA
    Speaker notes
    Which brings us to the most important difference from pandas.

  8. Slide 8 / 31

    mean() returns NA, and that is a feature — In R

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

mean(muac$muac_mm, na.rm = TRUE)
#> [1] 139.92
    Speaker notes
    pandas skips missing values by default; R does not.

  9. Slide 9 / 31

    mean() returns NA, and that is a feature

    • R is loud where pandas is silent — The NA is a question: *do you know there are missing values here, and have you…
    Speaker notes
    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:

  10. Slide 10 / 31

    mean() returns NA, and that is a feature — In R

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

  11. Slide 11 / 31

    na is file-wide, not per column — In R

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

  12. Slide 12 / 31

    na is file-wide, not per column — In R

    muac <- read_csv(PATH, na = c("", "NA")) |>
  dplyr::mutate(muac_mm = dplyr::na_if(muac_mm, -99L))
    Speaker notes
    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: na_if() is the scoped form and is worth preferring whenever more than one column is numeric.

  13. Slide 13 / 31

    problems() is a habit, not a rescue — In R

    issues <- problems(muac)
nrow(issues)
#> [1] 0
    Speaker notes
    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.

  14. Slide 14 / 31

    problems() is a habit, not a rescue — In R

    if (nrow(problems(muac)) > 0) {
  print(problems(muac))
  stop("Input did not match its column specification.")
}
    Speaker notes
    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. In a script that produces a reported figure, that stop() is correct. Refusing to run beats producing a table with a silently empty column.

  15. Slide 15 / 31

    Verify the read before doing anything with it — In R

    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)
    Speaker notes
    Four checks, one minute, and they catch nearly everything: 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.

  16. Slide 16 / 31

    Separators, encodings and thousands — In R

    epi <- read_csv2(PATH)                       # ; separator, , decimal
epi <- read_delim(PATH, delim = "\t")        # tabs
    Speaker notes
    A file produced on a French or Spanish Windows machine is frequently semicolon-separated, because the comma is the decimal mark there.

  17. Slide 17 / 31

    Separators, encodings and thousands — In R

    muac <- read_csv(PATH, locale = locale(encoding = "UTF-8"))
muac <- read_csv(PATH, locale = locale(encoding = "latin1"))   # what Excel often writes
    Speaker notes
    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.

  18. Slide 18 / 31

    Separators, encodings and thousands — In R

    epi <- read_csv(PATH, locale = locale(grouping_mark = ","))
    Speaker notes
    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: Without it, target_population arrives as "1,480" — a character column on which every arithmetic operation fails or concatenates.

  19. Slide 19 / 31

    Excel — In R

    library(readxl)

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

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

  20. Slide 20 / 31

    Excel

    • The header is not row one
    Speaker notes
    read_excel() without sheet returns the first sheet, which in a programme workbook is usually a cover page. Always list the sheets first.

  21. Slide 21 / 31

    Excel — In R

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

  22. Slide 22 / 31

    Excel

    • Merged cells
    Speaker notes
    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. 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:

  23. Slide 23 / 31

    Excel — In R

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

  24. Slide 24 / 31

    Excel

    • Serial dates
    Speaker notes
    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". 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.

  25. Slide 25 / 31

    Excel — In R

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

  26. Slide 26 / 31

    Excel

    • Types in Excel
    Speaker notes
    If a date column arrives as five-digit numbers, this is why.

  27. Slide 27 / 31

    Excel — In R

    data <- read_excel(
  path,
  sheet = "Data",
  col_types = c("text", "text", "date", "numeric", "text", "numeric", "text", "text")
)
    Speaker notes
    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.

  28. Slide 28 / 31

    The read function, assembled — In R (cont.)

    # 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")
  )
    Speaker notes
    Everything above belongs in one function the scripts share:

  29. Slide 29 / 31

    The read function, assembled — In R (cont.)

    
  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
}
    Speaker notes
    One function, one place to fix when the next export changes.

  30. Slide 30 / 31

    What comes next

    • The CSV is in and its types are right.
    Speaker notes
    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.

  31. Slide 31 / 31

    Where this goes next

    Read the full lesson, with runnable code Back to the lesson