cassionData Analysis

Back to the lessonLesson 6 of 8Making it trustworthy

Cleaning decisions and the cleaning log

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

    What this lesson covers

    • Cleaning is a set of decisions, not a script
    • The four options
    • Working through this register
    • Quantify what the decision costs
    • The cleaning log
    • The one thing never to do
    • What comes next
    Speaker notes
    Decide what to do about each defect, quantify what the decision costs the final number, and record it so an auditor — or you in six months — can follow the reasoning.

  2. Slide 2 / 32

    Cleaning is a set of decisions, not a script

    • The profiling in the last lesson produced a list of faults.
    Speaker notes
    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.

  3. Slide 3 / 32

    The four options

    OptionWhen it appliesWhat it costs
    CorrectYou know what the value was meant to beNothing, if you are right
    Set to missingThe value is wrong and unrecoverablePrecision, and possibly bias
    Drop the rowThe row is not a real observationCaseload, if you are wrong
    Keep and flagThe value may be right, and you cannot tellNothing, but the reader must handle it
    Speaker notes
    Every defect gets one of these. "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.

  4. Slide 4 / 32

    Working through this register

    • Unit errors — seven records in centimetres — Recoverable
    Speaker notes
    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.

  5. Slide 5 / 32

    Working through this register — In Python

    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

  6. Slide 6 / 32

    Working through this register — In R

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

  7. Slide 7 / 32

    Working through this register

    • Implausible values that are not unit errors — Not recoverable
    Speaker notes
    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.

  8. Slide 8 / 32

    Working through this register — In Python

    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

  9. Slide 9 / 32

    Working through this register — In R

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

  10. Slide 10 / 32

    Working through this register

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

  11. Slide 11 / 32

    Working through this register — In Python

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

  12. Slide 12 / 32

    Working through this register — In R

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

  13. Slide 13 / 32

    Working through this register

    • Re-registrations under a new identifier — six suspected — Keep and flag
    Speaker notes
    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.

  14. Slide 14 / 32

    Working through this register — In Python

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

  15. Slide 15 / 32

    Working through this register — In R

    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)

  16. Slide 16 / 32

    Working through this register

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

  17. Slide 17 / 32

    Working through this register — In Python

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

  18. Slide 18 / 32

    Working through this register — In R

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

  19. Slide 19 / 32

    Working through this register

    • Missing age clustered in Gros-Morne — This is the one that needs a decision rather than a rule, and it is the subject…
    Speaker notes
    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.

  20. Slide 20 / 32

    Quantify what the decision costs — In Python (cont.)

    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"]
    Speaker notes
    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.

  21. Slide 21 / 32

    Quantify what the decision costs — In Python (cont.)

    )
print(comparison.sort_values("difference", ascending=False).round(4))

  22. Slide 22 / 32

    Quantify what the decision costs — In R

    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

  23. Slide 23 / 32

    Quantify what the decision costs

    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.
    Speaker notes
    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.

  24. Slide 24 / 32

    The cleaning log — In Python (cont.)

    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. "
    Speaker notes
    Everything above becomes rows in a table that ships with the analysis.

  25. Slide 25 / 32

    The cleaning log — In Python (cont.)

                "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.",

  26. Slide 26 / 32

    The cleaning log — In Python (cont.)

            },
        {
            "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.",
        },

  27. Slide 27 / 32

    The cleaning log — In Python (cont.)

        ]
)

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

  28. Slide 28 / 32

    The cleaning log — In R

    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

  29. Slide 29 / 32

    The cleaning log

    • 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…
    Speaker notes
    Four properties make this log useful rather than ceremonial:

  30. Slide 30 / 32

    The one thing never to do

    • Do not edit data/raw/.
    Speaker notes
    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.

  31. Slide 31 / 32

    What comes next

    • The data is clean and the decisions are recorded.
    Speaker notes
    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.

  32. Slide 32 / 32

    Where this goes next

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