Six districts, three districts — Data Cleaning and Validation — Data Analysis

The table with six rows and three districts

Group the WASH household survey by district and this comes back:

district	Households	Below 15 L/person/day
NORD-OUEST	33	21.2%
nord-ouest	20	20.0%
Sud-Est	803	16.4%
Nord-Ouest	727	14.7%
Nord Ouest	22	9.1%
Centre	798	8.9%

Six rows. There are three districts. One enumerator team wrote Nord-Ouest four different ways, and because the variants sort apart, that district’s 802 households are split into pieces of 727, 33, 22 and 20.

Now read the table the way a coordination meeting would. Sorted worst first, NORD-OUEST is the priority district at 21.2% — a figure computed on thirty-three households, which is the smallest cell in the table and the one most easily moved by a handful of interviews. The real Nord-Ouest, all 802 households of it, sits at 15.0% and is second behind Sud-Est.

Nobody will tell you this happened. Nothing errors. The table looks fine, it just answers a different question from the one asked.

The administrative list is the authority

The first decision is whose names are correct, and the answer is never “the ones in the data”. Every country has an official administrative hierarchy — admin 1, admin 2, admin 3 — published with codes. In humanitarian operations these are p-codes, distributed through the Humanitarian Data Exchange and used by every cluster, and the whole point of them is that a code does not have spelling variants.

admin = pd.DataFrame([
    {"admin1_pcode": "HT07", "admin1_name": "Nord-Ouest"},
    {"admin1_pcode": "HT05", "admin1_name": "Centre"},
    {"admin1_pcode": "HT09", "admin1_name": "Sud-Est"},
])

admin <- tibble::tribble(
  ~admin1_pcode, ~admin1_name,
  "HT07",        "Nord-Ouest",
  "HT05",        "Centre",
  "HT09",        "Sud-Est"
)

Three consequences follow from treating this list as the authority, and they are the shape of the rest of the lesson.

A value in the data that does not resolve to the list is unmatched, not wrong. It may be a spelling variant, a new administrative unit, or a real place the list does not cover.
The output of matching is a code, and everything downstream joins on the code.
The list, not the data, decides how many districts there are. A table with six rows fails a check rather than being reported.

Normalise, then match exactly

Most of the variants are not really different names. Strip what does not carry meaning and they collapse.

def match_key(series):
    return (
        series.fillna("")
        .str.normalize("NFKD")
        .str.encode("ascii", "ignore").str.decode("ascii")
        .str.lower()
        .str.replace(r"[^a-z0-9]+", " ", regex=True)
        .str.strip()
    )


wash["district_key"] = match_key(wash["district"])
admin["key"] = match_key(admin["admin1_name"])

matched = wash.merge(
    admin[["key", "admin1_pcode", "admin1_name"]],
    left_on="district_key", right_on="key", how="left",
)
print(matched["admin1_pcode"].isna().sum(), "rows unmatched")

match_key <- function(x) {
  x |>
    tidyr::replace_na("") |>
    stringi::stri_trans_general("Latin-ASCII") |>
    tolower() |>
    stringr::str_replace_all("[^a-z0-9]+", " ") |>
    stringr::str_squish()
}

wash  <- wash  |> mutate(district_key = match_key(district))
admin <- admin |> mutate(key = match_key(admin1_name))

matched <- wash |> left_join(admin, by = c("district_key" = "key"))
sum(is.na(matched$admin1_pcode))

Case, accents and the hyphen-versus-space distinction account for every one of the four Nord-Ouest variants here. NORD-OUEST, nord-ouest, Nord Ouest and Nord-Ouest all normalise to nord ouest, and the merge is exact. Zero rows unmatched.

That is the common case and it is worth internalising: reach for fuzzy matching after normalisation, not instead of it. A great deal of what looks like a difficult linkage problem is punctuation.

What is left over is the actual work

When the exact match does not clear everything — and on village names it never does — the residue is what you work through.

unmatched = (
    matched[matched["admin1_pcode"].isna()]
    .groupby("district")
    .size()
    .sort_values(ascending=False)
)
print(unmatched)

matched |>
  filter(is.na(admin1_pcode)) |>
  count(district, sort = TRUE)

Work the list by row count, not alphabetically. The unmatched values are almost always a very short head and a long tail: two or three variants covering most of the rows, then singletons. Resolving the head takes ten minutes and recovers most of the data.

For the tail, generate candidates rather than deciding:

from rapidfuzz import process, fuzz

for value in unmatched.index:
    best = process.extract(
        match_key(pd.Series([value]))[0],
        admin["key"].tolist(),
        scorer=fuzz.ratio,
        limit=3,
    )
    print(value, "->", best)

for (value in unique(unmatched$district)) {
  scores <- stringdist::stringsim(match_key(value), admin$key, method = "jw")
  cat(value, "->", admin$admin1_name[order(-scores)][1:3], "\n")
}

The matches you must refuse

Fuzzy matching on place names is more dangerous than it looks, because administrative units are frequently named after each other and after the same saints, rivers and colonial officials. Two real, distinct communes can be one character apart.

Three habits that prevent the expensive mistake.

Constrain by the parent unit. A village only needs to be matched against villages in its own commune, and a commune against communes in its own department. This removes most false candidates for free and is why you match the hierarchy top down.
Refuse to auto-accept below a high threshold. A near-match on a two-syllable name is not evidence. Where the top two candidates score within a few points of each other, the answer is “review”, not “the first one”.
Leave unmatched values unmatched. A row with no district is honest and shows up in the completeness table. A row assigned to the wrong district is invisible and moves two numbers.

The failure mode is not that the script cannot match. It is that the script matches something, and the household ends up counted in a district it is not in.

The mapping is a file, not a chain of replacements

The instinct is a replace or a case_when. Resist it.

mapping = pd.read_csv("reference/district-mapping.csv")   # raw_value, admin1_pcode, decided_by, decided_on
wash = wash.merge(mapping, left_on="district", right_on="raw_value", how="left")

mapping <- readr::read_csv(here::here("reference", "district-mapping.csv"))
wash <- wash |> left_join(mapping, by = c("district" = "raw_value"))

A mapping file beats a chain of replacements on four counts, and the last one is what actually matters.

It is reviewable by someone who does not read code — usually the person who knows the districts.
It is reusable across the notebook, the dashboard and next round’s script.
It is diffable, so adding a variant shows up in review as one line.
It carries who decided. decided_by and decided_on turn a mapping from a technical artefact into a record, and when someone asks in October why Nord Ouest was folded into HT07, the file answers.

Match once, join on the code forever

After matching, drop the free-text name from everything downstream and carry the p-code.

wash = wash.drop(columns=["district", "district_key"]).rename(
    columns={"admin1_pcode": "admin1"}
)

wash <- wash |> select(-district, -district_key) |> rename(admin1 = admin1_pcode)

This is the step that makes the problem stay solved. Population denominators, last round’s survey, the cluster’s 4W matrix and the geodata for the map all join on the code without ever comparing a name again. Two datasets that both carry p-codes join correctly the first time, which is the entire reason the codes exist.

The assertion that catches next round’s fifth spelling

Everything above cleans the file you have. One line stops the same defect arriving unnoticed next quarter.

EXPECTED_DISTRICTS = {"HT05", "HT07", "HT09"}

seen = set(wash["admin1"].dropna())
assert seen <= EXPECTED_DISTRICTS, f"unexpected districts: {seen - EXPECTED_DISTRICTS}"
assert wash["admin1"].isna().sum() == 0, "rows with no district after mapping"

EXPECTED_DISTRICTS <- c("HT05", "HT07", "HT09")

stopifnot(
  all(wash$admin1 %in% EXPECTED_DISTRICTS),
  !any(is.na(wash$admin1))
)

Note what this does when the programme genuinely expands into a fourth district: it fails. That is correct. A new district is a thing someone should tell you about, and a script that silently absorbs it will silently compute a coverage figure against a denominator that does not include it.

What comes next

You now have rules for values and a mapping for names, and both were applied by hand this quarter. The next unit makes them run by themselves — a validation suite that executes on every read, reports what failed with counts, and stops the pipeline before a bad export reaches a dashboard.