The district spelled four ways

The problem

You group by district and get twenty-five of them. The country has six in this survey. Your map has holes, your totals do not add up, and the smallest "district" has one household in it.

What you will have at the end

Six districts, a record of exactly what you changed, and a clear rule for when a match is safe to make automatically and when it is not.

The steps

See the damage

import pandas as pd

df = pd.read_csv("household-survey-messy.csv", dtype=str, keep_default_na=False)

print(df["district"].nunique())
print(df["district"].value_counts().head(8))

25
Kasese            38
kasese            36
Hoima             35
Kasese            34
Mbale District    32
HOIMA             31
Hoima District    30
Soroti            29

Look at rows one and four: Kasese appears twice with different counts. One of them has a trailing space. You cannot see it, and neither could the person who checked this by eye.

Normalise the mechanical differences

Three rules clear most of it: strip whitespace, lowercase, drop a trailing "district".

clean = (
    df["district"]
    .str.strip()
    .str.lower()
    .str.replace(r"\s+", " ", regex=True)        # collapse double spaces
    .str.replace(r"\s+district$", "", regex=True)
)

print(clean.nunique())
print(sorted(clean.unique()))

9
['gulu', 'guru', 'hoima', 'kasese', 'lira', 'liraa', 'mbale', 'sorot', 'soroti']

From 25 to 9 with four lines and no judgement calls. Every change so far is reversible and obviously correct.

The remaining three — guru, liraa, sorot — are typos. No amount of normalising fixes a typo, because it is not a formatting difference. It is a different word.

Fix the rest with an explicit lookup

corrections = {
    "guru": "gulu",
    "liraa": "lira",
    "sorot": "soroti",
}

clean = clean.replace(corrections)
print(clean.nunique())    # 6

Write the corrections down in a dictionary like this, in the code, where a reviewer can read them. That dictionary is your audit trail. When someone asks in six months why four households moved from Guru to Gulu, the answer is on screen.

Put it back in title case and check your work

df["district_clean"] = clean.str.title()

changed = df.loc[df["district"].str.strip() != df["district_clean"],
                 ["district", "district_clean"]]
print(f"{len(changed)} rows changed")
print(changed.drop_duplicates().sort_values("district_clean").to_string(index=False))

Always print what you changed. A cleaning step you did not look at is a cleaning step you do not know the effect of.

Where it breaks

Fuzzy matching, used without looking

The temptation is to let an algorithm do the last three:

import difflib

valid = ["Gulu", "Lira", "Mbale", "Soroti", "Hoima", "Kasese"]
difflib.get_close_matches("Sorot", valid, n=1, cutoff=0.8)     # ['Soroti'] - right

That works. The danger is what happens on names it has never seen:

difflib.get_close_matches("Kaseke", valid, n=1, cutoff=0.8)    # ['Kasese']

Kaseke might be a typo for Kasese. It might also be a real place the lookup list is missing. Fuzzy matching cannot tell the difference, and at a cutoff loose enough to catch real typos it will also "fix" genuine values into the wrong bucket.

Use it to suggest, never to apply:

unmatched = sorted(set(clean) - {v.lower() for v in valid})
for name in unmatched:
    print(name, "->", difflib.get_close_matches(name, valid, n=2, cutoff=0.6))

Read the list, decide each one, write your decisions into the corrections dictionary. Ten minutes, and you can defend every change.

Normalising away a real distinction

Lowercasing is safe for district names. It is not safe everywhere: in some datasets AB and Ab are different codes. Check before you flatten a column.

Fixing the symptom instead of the cause

All of this is downstream of a form that let enumerators type a district name freely. The real fix is a select_one with a fixed list, so next round's data arrives clean. Cleaning is what you do about the data you already have; form design is what stops you doing it again.

Your turn

Run the same treatment on subcounty. Then answer: how many distinct subcounties are there before and after, and how many rows did you change?

Then check water_source_other — the free-text box. Count the distinct values. That column is why free text costs more than it looks.

Next

Dates that arrive in five formats — where the obvious fix silently produces the wrong answer.