Cleaning decisions and the cleaning log — Data Analysis Foundations for M&E — Data Analysis

Cleaning is a set of decisions, not a script

The profiling in the last lesson produced a list of faults. None of them has an objectively correct treatment. Each has a defensible treatment and a rationale, and the rationale is the part that matters — a reviewer will not object to you dropping seven rows, they will object to not being able to find out why.

So: for each fault, three things. What you did, why, and how many rows it touched.

The four options

Every defect gets one of these.

Option	When it applies	What it costs
Correct	You know what the value was meant to be	Nothing, if you are right
Set to missing	The value is wrong and unrecoverable	Precision, and possibly bias
Drop the row	The row is not a real observation	Caseload, if you are wrong
Keep and flag	The value may be right, and you cannot tell	Nothing, but the reader must handle it

“Keep and flag” is under-used. It is the honest option when a value is suspicious but not impossible, and it pushes the judgement to the person reading the analysis instead of hiding it inside it.

Working through this register

Unit errors — seven records in centimetres. Recoverable. A MUAC of 13.3 was measured as 133 mm; the decimal point is the whole story. Correct them, and record that you did.

import numpy as np

unit_error = muac["muac_mm"].notna() & (muac["muac_mm"] < 40)
n_unit_error = int(unit_error.sum())

muac.loc[unit_error, "muac_mm"] = muac.loc[unit_error, "muac_mm"] * 10

unit_error <- !is.na(muac$muac_mm) & muac$muac_mm < 40
n_unit_error <- sum(unit_error)

muac <- muac |>
  mutate(muac_mm = if_else(!is.na(muac_mm) & muac_mm < 40, muac_mm * 10L, muac_mm))

Multiplying by ten is safe here only because the plausible ranges do not overlap: nothing genuinely measured in millimetres is below 40, and nothing genuinely measured in centimetres is above 22. Check that the two ranges are disjoint before applying a rule like this. If they overlap, you cannot tell the cases apart and the values are not recoverable.

Implausible values that are not unit errors. Not recoverable. Set to missing, do not drop the row — the child was screened, and the rest of the record is still evidence.

implausible = muac["muac_mm"].notna() & (
    (muac["muac_mm"] < 80) | (muac["muac_mm"] > 220)
)
n_implausible = int(implausible.sum())

muac.loc[implausible, "muac_mm"] = np.nan

implausible <- !is.na(muac$muac_mm) & (muac$muac_mm < 80 | muac$muac_mm > 220)
n_implausible <- sum(implausible)

muac <- muac |>
  mutate(muac_mm = if_else(implausible, NA_integer_, muac_mm))

Exact duplicates — twelve rows. Drop. A form submitted twice on a poor connection is one screening, not two.

before = len(muac)
muac = muac.drop_duplicates()
n_exact_dupes = before - len(muac)

before <- nrow(muac)
muac <- distinct(muac)
n_exact_dupes <- before - nrow(muac)

Re-registrations under a new identifier — six suspected. Keep and flag. You cannot distinguish these from genuine coincidences without going back to the register, so mark them and say so in the report.

key = ["commune", "screening_date", "age_months", "sex", "muac_mm"]
muac["suspected_duplicate"] = muac.duplicated(subset=key, keep=False) & muac[
    "muac_mm"
].notna()

n_suspected = int(muac["suspected_duplicate"].sum())

muac <- muac |>
  group_by(commune, screening_date, age_months, sex, muac_mm) |>
  mutate(suspected_duplicate = n() > 1 & !is.na(muac_mm)) |>
  ungroup()

n_suspected <- sum(muac$suspected_duplicate)

Inconsistent oedema coding. Recoverable. Y and N mean what they look like they mean; blanks do not, and become missing.

oedema_map = {
    "true": True, "TRUE": True, "Y": True, "y": True, "yes": True,
    "false": False, "FALSE": False, "N": False, "n": False, "no": False,
}
muac["oedema"] = muac["oedema"].astype("string").str.strip().map(oedema_map)
n_oedema_missing = int(muac["oedema"].isna().sum())

muac <- muac |>
  mutate(
    oedema = case_when(
      tolower(trimws(oedema)) %in% c("true", "y", "yes") ~ TRUE,
      tolower(trimws(oedema)) %in% c("false", "n", "no") ~ FALSE,
      TRUE ~ NA
    )
  )

n_oedema_missing <- sum(is.na(muac$oedema))

Missing age clustered in Gros-Morne. This is the one that needs a decision rather than a rule, and it is the subject of the next section.

Quantify what the decision costs

The reason to take missing age seriously is that the obvious treatment — drop rows with missing age — changes the answer. Measure it rather than assuming.

gam_threshold = 125

complete = muac.dropna(subset=["age_months", "muac_mm"])
all_measured = muac.dropna(subset=["muac_mm"])

def gam_rate(df):
    return (df["muac_mm"] < gam_threshold).mean()

comparison = pd.DataFrame(
    {
        "dropping_missing_age": complete.groupby("commune").apply(gam_rate),
        "keeping_missing_age": all_measured.groupby("commune").apply(gam_rate),
    }
)
comparison["difference"] = (
    comparison["dropping_missing_age"] - comparison["keeping_missing_age"]
)
print(comparison.sort_values("difference", ascending=False).round(4))

gam_rate <- function(df) mean(df$muac_mm < 125, na.rm = TRUE)

complete <- muac |> filter(!is.na(age_months), !is.na(muac_mm))
all_measured <- muac |> filter(!is.na(muac_mm))

comparison <- full_join(
  complete |> group_by(commune) |> summarise(dropping = gam_rate(pick(everything()))),
  all_measured |> group_by(commune) |> summarise(keeping = gam_rate(pick(everything()))),
  by = "commune"
) |>
  mutate(difference = dropping - keeping) |>
  arrange(desc(abs(difference)))

comparison

Gros-Morne moves and the other communes barely do. That is the bias, made visible. Since the indicator here does not require age — MUAC thresholds for 6 to 59 months are a single band, unlike weight-for-height z-scores — the right decision is to keep the rows, use them for the MUAC indicator, and exclude them only from any analysis that genuinely needs age.

That is a better decision than dropping, and it is only available because you looked.

The general principle: drop rows for a specific analysis, never from the dataset. A row missing age is still evidence about MUAC. Filtering at the point of use keeps each analysis on the largest sample that supports it.

The cleaning log

Everything above becomes rows in a table that ships with the analysis.

cleaning_log = pd.DataFrame(
    [
        {
            "rule": "MUAC unit error corrected (cm to mm)",
            "column": "muac_mm",
            "action": "corrected",
            "rows": n_unit_error,
            "rationale": "Values below 40 are centimetres left unconverted. "
            "Plausible mm and cm ranges are disjoint, so the correction is unambiguous.",
        },
        {
            "rule": "Implausible MUAC set to missing",
            "column": "muac_mm",
            "action": "set-missing",
            "rows": n_implausible,
            "rationale": "Outside 80-220 mm is not a measurement for 6-59 months. "
            "Row retained; the screening happened.",
        },
        {
            "rule": "Exact duplicate submissions removed",
            "column": "all",
            "action": "dropped",
            "rows": n_exact_dupes,
            "rationale": "Identical rows are one form submitted twice on a poor connection.",
        },
        {
            "rule": "Suspected re-registration flagged",
            "column": "suspected_duplicate",
            "action": "flagged",
            "rows": n_suspected,
            "rationale": "Same commune, date, age, sex and MUAC under different ids. "
            "Cannot be distinguished from coincidence without the paper register.",
        },
        {
            "rule": "Oedema coding harmonised",
            "column": "oedema",
            "action": "corrected",
            "rows": n_oedema_missing,
            "rationale": "Ennery and Desdunes used Y/N in Q1. Blanks remain missing.",
        },
        {
            "rule": "Missing age retained",
            "column": "age_months",
            "action": "kept",
            "rows": int(muac["age_months"].isna().sum()),
            "rationale": "60% missing in the Gros-Morne week of 10-14 June. Dropping "
            "shifts the commune ranking; MUAC thresholds do not require age.",
        },
    ]
)

cleaning_log.to_csv("output/tables/cleaning-log.csv", index=False)
print(cleaning_log[["rule", "action", "rows"]])

cleaning_log <- tibble::tribble(
  ~rule,                                    ~column,               ~action,       ~rows,
  "MUAC unit error corrected (cm to mm)",   "muac_mm",             "corrected",   n_unit_error,
  "Implausible MUAC set to missing",        "muac_mm",             "set-missing", n_implausible,
  "Exact duplicate submissions removed",    "all",                 "dropped",     n_exact_dupes,
  "Suspected re-registration flagged",      "suspected_duplicate", "flagged",     n_suspected,
  "Oedema coding harmonised",               "oedema",              "corrected",   n_oedema_missing,
  "Missing age retained",                   "age_months",          "kept",        sum(is.na(muac$age_months))
)

readr::write_csv(cleaning_log, "output/tables/cleaning-log.csv")
cleaning_log

Four properties make this log useful rather than ceremonial:

The row counts come from the code, not from memory. If the rule changes, the count changes with it.
It ships with the analysis — as an annex to the report, not as a file on someone’s laptop.
The rationale is a sentence, not a category. “Data quality” is not a rationale.
It is written as the cleaning happens, not reconstructed afterwards. Reconstructed logs are wrong in exactly the places that matter.

The one thing never to do

Do not edit data/raw/. Not one cell, not to fix an obvious typo, not “just this once” the night before a deadline.

Every correction above is code. That means it is visible, reversible, applied identically to next quarter’s export, and re-runnable by someone who doubts you. A cell edited by hand in a spreadsheet is none of those things, and it is invisible six months later when the number is queried.

What comes next

The data is clean and the decisions are recorded. The next lesson turns it into an indicator — which means confronting the fact that a rate is a numerator over a denominator, and that almost nobody agrees on the denominator.