Python Data Cleaning and Analysis

Before transforming anything, write down what each column must contain. This data contract is what the cleaning code checks:

expected = {"donation_id", "date", "amount", "method"}
missing = expected - set(raw.columns)
if missing:
    raise ValueError(f"Export is missing columns: {sorted(missing)}")

A file loading without errors proves only that it was readable, not that its contents make sense: a CSV with every amount set to 0, or dates shifted by a year, loads just as smoothly. Checking the contract first turns silent problems into explicit ones. Reading everything as text initially (dtype=str) keeps the raw values visible, so conversion problems can be reported rather than guessed at.

Missing values need a policy based on what the gap means:

• A missing amount makes a donation unusable for totals: reject the row and report it.
• A missing optional note is harmless: keep it.
• A missing method might be filled with "unknown" if the analysis can live with that category.

Duplicates need the same care. Two rows that are identical in every column, including donation_id, are almost certainly an export glitch. Two donations of £20 on the same day from different donors are legitimate and must both stay. Decide which columns define "the same record":

dupes = df.duplicated(subset=["donation_id"], keep="first")
print(dupes.sum(), "repeated ids")
df = df[~dupes]

Deduplicating on amount and date alone would wrongly delete real donations. Always count how many rows each rule removes, so the effect is visible in the report rather than hidden in the totals.

Convert each column explicitly, and keep a record of rows that fail, together with the reason. That record is the cleaning step's provenance:

amount = pd.to_numeric(raw["amount"], errors="coerce")
date = pd.to_datetime(raw["date"], format="%Y-%m-%d", errors="coerce")

problems = pd.Series("", index=raw.index)
problems[amount.isna()] += "amount not a number; "
problems[amount <= 0] += "amount not positive; "
problems[date.isna()] += "bad date; "
problems[~raw["method"].isin(["cash", "card", "transfer"])] += "unknown method; "

rejected = raw[problems != ""].assign(reason=problems[problems != ""])

errors="coerce" turns invalid values into NaN or NaT instead of raising, which lets you find them all at once. Coercion alone is dangerous, though: if you then drop or fill the NaNs without recording which inputs they came from, the source problems vanish. Keeping rejected with a reason column lets someone fix the source system, and lets you answer "why isn't donation 1042 in the total?"

Normalise text before validating it: str.strip() and str.lower() turn " Card " into "card", so formatting differences aren't mistaken for invalid values.

Before presenting any result, reconcile:

1. Row counts: accepted + rejected + removed duplicates = original rows.
2. Totals: the cleaned total plus the rejected amounts should explain the raw total where amounts were readable.
3. Ranges: minimum and maximum dates and amounts are plausible.

assert len(clean) + len(rejected) + duplicates_removed == len(raw), "rows unaccounted for"
print(clean["amount"].describe())
print(clean["date"].min(), clean["date"].max())

These checks catch bugs in the cleaning code itself, such as a filter that accidentally drops valid rows, which a chart would never reveal. A clean-looking bar chart built on an incorrect policy is still wrong.

Make the process repeatable: put the cleaning steps in a function or script, keep the raw file unchanged, and write the cleaned data and the rejection report as separate outputs. Running the same code on next month's export then produces comparable results.

Task: clean donation records while reconciling source, accepted and rejected counts and documenting every policy.

import pandas as pd

raw = pd.DataFrame({"id": ["1", "2", "3", "3", "4"],
                    "amount": ["10", "n/a", "25", "25", "0"]})
policies = ["duplicate ids keep the first row",
            "non-numeric or non-positive amounts are rejected"]

dupes = raw.duplicated("id")
data = raw[~dupes]
amount = pd.to_numeric(data["amount"], errors="coerce")
valid = amount > 0                      # NaN compares as False
clean, rejected = data[valid], data[~valid]

assert len(clean) + len(rejected) + dupes.sum() == len(raw)
print(len(clean), len(rejected), int(dupes.sum()))   # 2 2 1
print(amount[valid].sum())                            # 35.0
print(*policies, sep="\n")

For the worked check, rejected row information is retained to explain cleaning decisions: it shows which records were excluded, why, and lets the totals be reconciled.

The pandas user guide covers working with missing data and duplicate labels, and the to_numeric reference explains errors=. Continue with statistics and data visualization. Practise with the Python exercises and Python quiz.