Export a table people can use

The problem

You have cleaned everything in a notebook. Now someone needs the result — in Excel, or in Power BI, or as the input to next month's script. This is the step where a careful clean gets undone by a careless save.

And there is still that 618 against 600.

What you will have at the end

One script that runs start to finish, a clean file, a short data dictionary, and the duplicates resolved on purpose rather than by accident.

The steps

Deal with the duplicates first

import pandas as pd

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

print("rows:", len(df))
print("unique households:", df["_uuid"].nunique())

dupes = df[df.duplicated("_uuid", keep=False)].sort_values("_uuid")
print("rows involved in a duplicate:", len(dupes))

rows: 618
unique households: 600
rows involved in a duplicate: 36

36 rows, which is 18 households that arrived twice — usually a tablet that lost signal, retried, and sent both copies once it reconnected.

Look before you drop. If the two copies differ, one of them was edited and you need to know which to keep:

same = dupes.groupby("_uuid").nunique().max(axis=1).eq(1)
print(f"{same.sum()} of {len(same)} duplicate pairs are byte-identical")

Identical pairs are safe to collapse. Where they differ, keeping the later submission is the usual rule — but say so in the code:

# Keep the last submission for each household: where a tablet sent twice, the
# later copy is the one the enumerator confirmed.
df = df.sort_values("_submission_time").drop_duplicates("_uuid", keep="last")
print("after dedup:", len(df))

Write it as a script, not a notebook

A notebook is for working out what to do. Once you know, move it into a file that runs top to bottom, because a notebook run out of order is how a clean becomes irreproducible.

"""Clean the household survey export. Run: python clean_survey.py"""

import numpy as np
import pandas as pd

RAW = "household-survey-messy.csv"
OUT = "household-survey-clean.csv"

DISTRICT_FIXES = {"guru": "gulu", "liraa": "lira", "sorot": "soroti"}
YES = {"yes", "y", "1", "true"}

def load(path):
    return pd.read_csv(path, dtype=str, keep_default_na=False)

def clean_district(series):
    return (
        series.str.strip().str.lower()
        .str.replace(r"\s+", " ", regex=True)
        .str.replace(r"\s+district$", "", regex=True)
        .replace(DISTRICT_FIXES)
        .str.title()
    )

def clean_income(series):
    numeric = pd.to_numeric(
        series.str.replace(",", "").str.replace(" ", ""), errors="coerce"
    )
    # -99 refused and -88 don't know are codes, not amounts.
    return numeric.where(numeric >= 0)

def main():
    df = load(RAW)
    df = df.sort_values("_submission_time").drop_duplicates("_uuid", keep="last")

    out = pd.DataFrame({
        "household_id": df["_uuid"],
        "enumerator_id": df["enumerator_id"].str.strip(),
        "district": clean_district(df["district"]),
        "consented": df["consent"].str.strip().str.lower().isin(YES),
        "household_size": pd.to_numeric(df["household_size"], errors="coerce"),
        "income_ugx": clean_income(df["monthly_income_ugx"]),
    })

    # A household that declined was never asked, so its blanks are not missing
    # data. Leave them empty and let the analyst exclude them knowingly.
    out.loc[~out["consented"], ["household_size", "income_ugx"]] = np.nan

    out.to_csv(OUT, index=False)
    print(f"wrote {OUT}: {len(out)} households, {out['district'].nunique()} districts")

if __name__ == "__main__":
    main()

Save it so the next tool can read it

out.to_csv("household-survey-clean.csv", index=False)

index=False matters. Without it pandas writes its row numbers as a nameless first column, and whoever opens the file next gets a mystery field called Unnamed: 0.

For Excel:

out.to_excel("household-survey-clean.xlsx", index=False, sheet_name="households")

This needs openpyxl, which you installed in the setup lesson. Excel is the better choice when a person will open the file — types survive, and nothing mangles an ID into a date. CSV is the better choice when a machine reads it.

Write the data dictionary

Four lines per column, saved next to the data:

dictionary = pd.DataFrame([
    ("household_id",  "text",    "Submission UUID, unique after deduplication"),
    ("enumerator_id", "text",    "Enumerator code"),
    ("district",      "text",    "Normalised; see DISTRICT_FIXES for corrections applied"),
    ("consented",     "boolean", "False means the interview did not proceed"),
    ("household_size","integer", "Blank where consent was declined"),
    ("income_ugx",    "number",  "Monthly UGX. Blank = refused, don't know, or not asked"),
], columns=["column", "type", "notes"])

dictionary.to_csv("household-survey-clean-dictionary.csv", index=False)

This takes four minutes and it is the difference between a file someone can use and a file someone emails you about.

Where it breaks

to_csv without index=False

The commonest mistake in this lesson. Check your output by reading it back:

print(pd.read_csv("household-survey-clean.csv").columns.tolist())

Excel turning your IDs into dates

A value like 03-14 becomes a date the moment Excel touches it, and it does not change back. If an ID could look like a date or a number, write .xlsx rather than CSV, or prefix the column at source.

Overwriting your raw file

Never write back to the file you read. Keep the raw export untouched and read-only — it is the only record of what actually arrived, and you will want it the first time someone disputes a number.

Silent loss between steps

Print the row count after every step that can drop rows. A clean that starts with 618 and ends with 400 should tell you where the other 218 went, in the output, as it runs.

Your turn

Run the script end to end, then verify three things against the raw file:

1. Every one of the 600 households appears exactly once.
2. No income_ugx is negative.
3. The number of districts is 6.

Then open the CSV in Excel and check the first column is household_id and not Unnamed: 0.

Next

That is the end of Module 1 — you can take a raw export to a table you can analyse. The full curriculum has what comes next: the roster join, reshaping repeat groups, and the analysis that sits on top.