cassionData Analysis

Back to the lessonLesson 3 of 8Getting the data in

Reading an export without corrupting it

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 / 20

    What this lesson covers

    • The damage happens before you look
    • Missing-value codes become numbers
    • Identifiers are not numbers
    • Dates
    • Categories with a fixed vocabulary
    • A defensive read, end to end
    • Reading the other formats
    • What comes next
    Speaker notes
    Type inference, leading zeros, dates, and the missing-value code that becomes a number. The damage happens at import, before you have looked at anything.

  2. Slide 2 / 20

    The damage happens before you look

    • read_csv(path) is one line and it makes half a dozen decisions on your behalf.
    Speaker notes
    read_csv(path) is one line and it makes half a dozen decisions on your behalf. Most are right. The ones that are wrong are wrong silently, and by the time you notice, the corrupted value has been in three summaries and a chart. This lesson is about taking those decisions back.

  3. Slide 3 / 20

    Missing-value codes become numbers — In Python

    import pandas as pd

naive = pd.read_csv("data/raw/muac-screening-artibonite-2024.v1.csv")
print(naive["muac_mm"].mean())
    Speaker notes
    The MUAC register codes missing measurements as -99, not as a blank cell. There is a reason for it — a blank on a paper register is ambiguous between "not measured" and "the enumerator skipped the page", and a sentinel is unambiguous — but a CSV reader has no way to know that -99 is not a measurement.

  4. Slide 4 / 20

    Missing-value codes become numbers — In Python

    muac = pd.read_csv(
    "data/raw/muac-screening-artibonite-2024.v1.csv",
    na_values={"muac_mm": ["-99"]},
)

print(muac["muac_mm"].mean())
print(muac["muac_mm"].isna().sum())
    Speaker notes
    That number is several millimetres below the truth. Worse, it is plausible: nothing about it looks like an error, and a MUAC mean is not a figure most readers can sanity-check by eye. Declare the sentinel at read time and it never enters a calculation:

  5. Slide 5 / 20

    Missing-value codes become numbers — In R

    library(readr)

muac <- read_csv(
  "data/raw/muac-screening-artibonite-2024.v1.csv",
  na = c("", "NA", "-99")
)

mean(muac$muac_mm, na.rm = TRUE)
sum(is.na(muac$muac_mm))

  6. Slide 6 / 20

    Missing-value codes become numbers

    Handling a sentinel is not the same as deciding what to do about the missing value. That decision is lesson 6. Here you are only making sure the code stops pretending to be a measurement.
    Speaker notes
    Note the difference. pandas lets you scope the sentinel to one column, which is what you want: -99 is a missing MUAC, but if a column ever legitimately held a negative value, blanket treatment would destroy it. R's read_csv applies na across the file, so scope it afterwards when that matters.

  7. Slide 7 / 20

    Identifiers are not numbers — In Python

    muac = pd.read_csv(
    path,
    dtype={"child_id": "string", "commune": "string"},
    na_values={"muac_mm": ["-99"]},
)
    Speaker notes
    child_id in this register looks like CH00854 and survives import intact. Many real registers use bare numeric identifiers instead — 00854 — and a reader will helpfully turn that into the integer 854. The leading zero is gone, the join to the household file fails for exactly the identifiers that had one, and the failure looks like missing households rather than like a type error.

  8. Slide 8 / 20

    Identifiers are not numbers — In R

    muac <- read_csv(
  path,
  col_types = cols(
    child_id = col_character(),
    commune  = col_character()
  ),
  na = c("", "NA", "-99")
)
    Speaker notes
    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 all text that happens to be written with digits.

  9. Slide 9 / 20

    Dates — In Python

    muac["screening_date"] = pd.to_datetime(
    muac["screening_date"], format="%Y-%m-%d", errors="raise"
)
    Speaker notes
    screening_date arrives as 2024-01-15. That is ISO 8601, it is unambiguous, and both readers will parse it correctly. Be aware that you are lucky. Real exports produce 15/01/2024, 01/15/2024, 15-Jan-24 and Excel serial numbers like 45306, sometimes in the same column, because three people entered data on three differently-configured machines. 01/02/2024 is either 1 February or 2 January and the file will not tell you which.

  10. Slide 10 / 20

    Dates — In R

    muac <- muac |>
  dplyr::mutate(
    screening_date = as.Date(screening_date, format = "%Y-%m-%d")
  )

stopifnot(!any(is.na(muac$screening_date)))

  11. Slide 11 / 20

    Dates

    • State the format explicitly rather than letting the parser infer it. An inferred format can change between files as…
    • Use errors="raise". The alternative, errors="coerce", converts every unparseable date to missing — which means…
    Speaker notes
    Two habits are worth forming:

  12. Slide 12 / 20

    Categories with a fixed vocabulary — In Python

    outcomes = pd.CategoricalDtype(
    ["no-action", "referred-tsfp", "referred-otp", "referred-sc"], ordered=False
)

muac["outcome"] = muac["outcome"].astype(outcomes)

# Anything outside the vocabulary is now NaN — count it before moving on.
print(muac["outcome"].isna().sum())
    Speaker notes
    outcome takes four values and sex takes two. Declaring them as categorical gives you two things: a smaller object, and — more useful — an error when a value outside the vocabulary appears.

  13. Slide 13 / 20

    Categories with a fixed vocabulary — In R

    muac <- muac |>
  dplyr::mutate(
    outcome = factor(
      outcome,
      levels = c("no-action", "referred-tsfp", "referred-otp", "referred-sc")
    )
  )

sum(is.na(muac$outcome))
    Speaker notes
    This is a genuine trap in both languages: a value outside the declared levels becomes missing rather than raising. Always count the missing immediately after converting. A jump from zero to nine means nine rows carried a value you did not know about, and you want to see it now rather than discover it as a gap in a table. The oedema column in this register is exactly that case. Ennery and Desdunes recorded it inconsistently in the first quarter, using Y and N rather than true and false. Read it naively and those rows land as missing without comment.

  14. Slide 14 / 20

    A defensive read, end to end — In Python (cont.)

    import pandas as pd

PATH = "data/raw/muac-screening-artibonite-2024.v1.csv"

muac = pd.read_csv(
    PATH,
    dtype={"child_id": "string", "commune": "string", "sex": "string"},
    na_values={"muac_mm": ["-99"]},
    keep_default_na=True,
)

muac["screening_date"] = pd.to_datetime(
    muac["screening_date"], format="%Y-%m-%d", errors="raise"
)

assert len(muac) == 4218, f"expected 4218 rows, got {len(muac)}"
    Speaker notes
    Putting it together — and asserting what you expect, so the script fails on a bad file instead of producing a bad number.

  15. Slide 15 / 20

    A defensive read, end to end — In Python (cont.)

    assert muac["child_id"].notna().all(), "every row needs an identifier"

print(muac.dtypes)
print(muac.isna().sum())

  16. Slide 16 / 20

    A defensive read, end to end — In R (cont.)

    library(readr)
library(dplyr)

PATH <- "data/raw/muac-screening-artibonite-2024.v1.csv"

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

  17. Slide 17 / 20

    A defensive read, end to end — In R (cont.)

      ),
  na = c("", "NA", "-99")
)

stopifnot(nrow(muac) == 4218)
stopifnot(!any(is.na(muac$child_id)))

glimpse(muac)
colSums(is.na(muac))
    Speaker notes
    The assertions are the part people skip and the part that pays. 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.

  18. Slide 18 / 20

    Reading the other formats

    SourcePythonR
    CSVpd.read_csvreadr::read_csv
    Excelpd.read_excelreadxl::read_excel
    Statapd.read_statahaven::read_dta
    SPSSpd.read_spsshaven::read_sav
    Parquetpd.read_parquetarrow::read_parquet
    Speaker notes
    The same principles apply, with different function names. Two notes specific to this sector. Stata and SPSS files carry value labels — 1 = Yes, 2 = No — and haven preserves them while pandas mostly does not; if you are handed a .dta from a survey firm, read it in R even if you will analyse it in Python. And Excel exports frequently carry a merged title row above the header, which both readers will treat as the header unless you tell them to skip it.

  19. Slide 19 / 20

    What comes next

    • The register is now in memory with its types intact.
    Speaker notes
    The register is now in memory with its types intact. The next lesson reshapes it — and reshapes the CommCare-style flattened export it could have arrived as — into one row per observation.

  20. Slide 20 / 20

    Where this goes next

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