Reading an export without corrupting it — Data Analysis Foundations for M&E — Data Analysis

The damage happens before you look

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.

Missing-value codes become numbers

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.

import pandas as pd

naive = pd.read_csv("data/raw/muac-screening-artibonite-2024.v1.csv")
print(naive["muac_mm"].mean())

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:

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

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

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.

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.

Identifiers are not numbers

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.

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

muac <- read_csv(
  path,
  col_types = cols(
    child_id = col_character(),
    commune  = col_character()
  ),
  na = c("", "NA", "-99")
)

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.

Dates

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.

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

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

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

Two habits are worth forming:

State the format explicitly rather than letting the parser infer it. An inferred format can change between files as the mix of values changes.
Use errors="raise". The alternative, errors="coerce", converts every unparseable date to missing — which means a file in the wrong date format imports “successfully” with an entirely empty date column.

Categories with a fixed vocabulary

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.

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

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

sum(is.na(muac$outcome))

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.

A defensive read, end to end

Putting it together — and asserting what you expect, so the script fails on a bad file instead of producing a bad number.

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)}"
assert muac["child_id"].notna().all(), "every row needs an identifier"

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

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()
  ),
  na = c("", "NA", "-99")
)

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

glimpse(muac)
colSums(is.na(muac))

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.

Reading the other formats

The same principles apply, with different function names.

Source	Python	R
CSV	`pd.read_csv`	`readr::read_csv`
Excel	`pd.read_excel`	`readxl::read_excel`
Stata	`pd.read_stata`	`haven::read_dta`
SPSS	`pd.read_spss`	`haven::read_sav`
Parquet	`pd.read_parquet`	`arrow::read_parquet`

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.

What comes next

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.