The log that answers the question before it is asked — Data Cleaning and Validation — Data Analysis

The question you will be asked

It arrives in one of three forms and it always arrives.

A donor’s monitoring visit: “Your caseload fell 4% from the draft to the final report. What changed?”
The programme manager: “Last quarter Gros-Morne was second worst. Now it is fourth. Did something improve?”
Your successor, in an email: “There is a script here that drops rows. Do you know why?”

Every one of them is answerable in thirty seconds with a cleaning log and not at all without one. This lesson is the log.

A log is a table, not a narrative

The instinct is to write a paragraph in the methodology section. A paragraph cannot be counted, cannot be diffed against last quarter, and cannot be checked.

One row per rule applied. Every row carries a count.

rule	severity	rows	action	effect on the indicator	decided by	date
muac-range	error	7	excluded	GAM 8.62% to 8.63%	R. Cassion	2026-07-14
exact-duplicate	error	12	one copy kept	caseload 366 to 360	R. Cassion	2026-07-14
re-registration	warning	6	merged after review	caseload 360 to 354	M. Joseph, supervisor	2026-07-16
oedema-yn-coding	warning	25	recoded to false	GAM unchanged	R. Cassion	2026-07-14
age-missing	note	226	kept, flagged	age-disaggregated table only	R. Cassion	2026-07-14
district-mapping	warning	75	mapped to HT07	Nord-Ouest 14.7% to 15.0%	A. Pierre, field office	2026-07-15

Read down the rows column and you have the whole story of what happened to this file. Read down effect on the indicator and you have the answer to the donor’s question, in advance.

The seven columns, and why each earns its place

rule — the identifier from the rule table in lesson 5, so the log and the code use the same name. If the log says muac-range and the code says implausible_muac, the link is broken the first time someone else looks.
severity — what the check meant, so a reader can skim to the errors.
rows — the count. Not “a few”, not “some outliers”. A number.
action — excluded, corrected, recoded, merged, kept and flagged. Five verbs cover almost everything, and using the same five makes the log scannable.
effect on the indicator — the before and after. This is the column people skip and the only one a donor reads.
decided by — a person. Not “the team”. The rule with no name against it is the rule that turns out to be wrong.
date — when, so the log can be read against the version of the output it produced.

Generate it from the code

A log written afterwards from memory is fiction, and it is always shorter than the truth. Make each cleaning step return its own log row.

LOG = []


def apply_step(df, rule, mask, action, note=""):
    before = indicator(df)
    if action == "exclude":
        out = df[~mask]
    elif action == "flag":
        out = df.assign(**{f"flag_{rule}": mask})
    else:
        raise ValueError(action)
    after = indicator(out)

    LOG.append({
        "rule": rule,
        "rows": int(mask.sum()),
        "action": action,
        "before": before,
        "after": after,
        "effect": round(after - before, 4),
        "note": note,
    })
    return out


muac = apply_step(muac, "muac-range", ~muac["muac_mm"].between(80, 220), "exclude")
muac = apply_step(muac, "age-missing", muac["age_months"].isna(), "flag",
                  "kept; age-disaggregated tables only")

log = pd.DataFrame(LOG)
log.to_csv("outputs/cleaning-log.csv", index=False)

LOG <- list()

apply_step <- function(df, rule, mask, action, note = "") {
  before <- indicator(df)
  out <- switch(action,
    exclude = df[!mask, ],
    flag    = dplyr::mutate(df, "flag_{rule}" := mask),
    stop("unknown action: ", action)
  )
  after <- indicator(out)

  LOG[[length(LOG) + 1]] <<- tibble::tibble(
    rule = rule, rows = sum(mask, na.rm = TRUE), action = action,
    before = before, after = after, effect = round(after - before, 4), note = note
  )
  out
}

muac <- apply_step(muac, "muac-range", !dplyr::between(muac$muac_mm, 80, 220), "exclude")
muac <- apply_step(muac, "age-missing", is.na(muac$age_months), "flag",
                   "kept; age-disaggregated tables only")

readr::write_csv(dplyr::bind_rows(LOG), here::here("outputs", "cleaning-log.csv"))

Two things this arrangement guarantees that discipline alone does not.

A step cannot happen without being logged, because logging is what the function does. The tempting one-line df = df[df.muac_mm > 80] inserted at 6 p.m. on a deadline is exactly the change that never reaches a written log; here there is no shorter way to do it than the logged way.

The effect is measured, not estimated. before and after come from the same indicator() function, evaluated on the same data a moment apart. Nobody has to remember what the number was.

The before-and-after table is the deliverable

Aggregate the log into three lines and it becomes something a non-technical reader can act on:

print(f"Records received:  {len(raw):>6,}")
print(f"Excluded:          {len(raw) - len(analysis):>6,}")
print(f"Analysed:          {len(analysis):>6,}")
print(f"GAM, raw:          {indicator(raw):>6.1%}")
print(f"GAM, cleaned:      {indicator(analysis):>6.1%}")

cat(sprintf("Records received: %6d\n", nrow(raw)))
cat(sprintf("Excluded:         %6d\n", nrow(raw) - nrow(analysis)))
cat(sprintf("Analysed:         %6d\n", nrow(analysis)))
cat(sprintf("GAM, raw:         %6.1f%%\n", 100 * indicator(raw)))
cat(sprintf("GAM, cleaned:     %6.1f%%\n", 100 * indicator(analysis)))

If the raw and cleaned figures are close, say so — it is the strongest sentence available to you, because it means the finding does not depend on your judgement. If they are far apart, say that too, and expect the conversation. A cleaning process that moves an indicator by three points is not a defect in the analysis; it is a finding about data collection, and hiding it is the only version of this that is misconduct.

What must not be in the log

The log is an annex. It gets emailed, attached to reports and forwarded, which means it travels further than the dataset does.

No direct identifiers. Not names, not phone numbers, not GPS coordinates. A log row reading merged CH03315 into CH09241 is fine because those are pseudonyms; merged Jean B. (Ti Rivyè) into… is a protection incident.
No small cells in a sensitive category. “3 rows recoded, GBV case category rape, Marmelade” identifies people in a small commune. Aggregate the rule to the level you would publish, or say 3 rows, one commune.
No free-text detail copied from a case note. The rule is what you applied, not what the record said.

The habit is worth learning on nutrition data, where it costs nothing, so that it is automatic on the day you are handed a protection caseload — where the principles are not advice.

Where the log lives

Beside the output, not in a document.

outputs/
  profiles/muac-2024-q4.json        arrival profile      (lesson 1)
  validation/muac-2024-q4.json      validation findings  (lesson 7)
  cleaning-log.csv                  decisions            (this lesson)
  review/duplicate-candidates.csv   reviewed and signed  (lesson 4)
  tables/gam_by_commune.csv         the result
  tables/definitions.csv            numerator, denominator, source

Those six files are a complete account of one quarter. Someone with the raw export and this folder can reconstruct everything you did and check every number, and that is a much stronger claim than “the analysis was careful”.

Note what is not there: the cleaned dataset is not the deliverable. The raw file stays read-only and the cleaned file is regenerated by running the script. A cleaned CSV emailed around detaches from its log within a week and then nobody can tell which version anyone is holding.

The annex the report needs

Three short sections, and they belong in every analysis this sector produces.

Data source and completeness. Where the file came from, how many records, what share of expected reporting units it covers, over what period.

Cleaning and exclusions. The log table, or a summary of it with the full table attached. Every exclusion with its count.

Limitations. What the cleaning could not fix. The missing-not-at-random you suspect but cannot demonstrate. The six merged records a supervisor confirmed and the two they were unsure about. The district whose rate rests on thirty-three households.

The annex is not a confession. It is the reason the number in the executive summary can be defended, and the professional difference between a figure and a claim.

Where this course leaves you

You can take a raw programme export, describe honestly what arrived, find what is wrong with it in four different ways, decide what to do about each fault against a published standard, make those decisions run again next quarter without you, and hand the whole thing to an auditor with the working attached.

The next course in this module, Joining and Reshaping Programme Data, takes the clean table and puts it together with the others — household to member, register to population denominator, facility register to DHIS2 aggregate — where the failure mode is not a wrong value but a row count that doubled while you were looking at it.