Capstone: clean a messy dataset
Thirteen rows, four defects, one pipeline.
drop_duplicates.strto_datetimetransformfillnaassertWatch it happen
Play it through, or step back and forth yourself.
shape · dtypes · isna().sum().drop_duplicates().str.strip().str.lower()to_datetime · astype("category")transform("mean") · fillnaassert · isna().sum()orders has been quietly broken this whole time: an exact duplicate row, three missing values across two columns, and ginger appearing twice under two spellings. Six stages to fix it — and every one is a lesson you've already done.
The idea
The orders table you've been using was broken on purpose, and you've met every defect in it separately. Now fix them all, in order, in one pipeline.
orders.shape # (13, 6)
orders.duplicated().sum() # 1 — an exact repeat
orders.isna().sum() # rating 2, revenue 1
orders["item"].unique() # "ginger" AND "Ginger"1. Inspect before you touch anything
shape, dtypes, isna().sum(), describe(), and value_counts() on the text columns. Write down what you find — you'll check against it at the end.
2. Deduplicate first
Before anything else, because every count you compute afterwards depends on it. And decide deliberately whether you mean identical rows or a repeated key — subset=["date", "city"] gives a different answer here, and neither is automatically right.
3. Normalise text before you group on it
.str.strip().str.lower() on item and city. Order matters: normalise before converting to category, or you'll store the bug more efficiently.
4. Fix the types
date to datetime64 so it sorts and resamples; city and item to category now that they're clean.
5. Handle the gaps, deliberately
Two missing ratings and one missing revenue, and they deserve different treatment.
A rating is an opinion about a specific order — the best guess is the city's average, not a global one:
df["rating"].fillna(df.groupby("city")["rating"].transform("mean"))A missing revenue is different: it's a fact that wasn't recorded. Inventing one would put a made-up number into every total you report. Leaving it NaN means the sums skip it and stay honest — so leave it, and say so in a comment.
That distinction is the whole point of the capstone. Filling is a claim. Make it only where you can defend it.
6. Verify
assert clean.duplicated().sum() == 0
assert clean["rating"].isna().sum() == 0
assert clean["item"].nunique() == 3
assert len(clean) == 12Four lines that turn "I think I cleaned it" into something that fails loudly when the input changes. This is the habit that separates a script that worked once from one you can run next month.
Put it in one chain
Once each step works on its own, assemble them with lesson 25. The finished pipeline should read as the six headings above.
See it run
The lesson's code, ready to run and to fiddle with.
Putting the kettle on…
Starting up…
Worked example
not gradedAlready written and ready to go — press Run to see what it does, then change a number, a column name, anything, and run it again.
tryremoving the .str.lower() and watching nunique go back to 4.
Your turn
5 exercises. Write the code yourself, then press Check — a nudge and the answer are there if you want them.
Start with an audit: return how many values are missing in each column of orders.
Return orders with the exact duplicate removed. It should have 12 rows.
After deduplicating, normalise item to lower case and return the counts — ginger should appear once, not twice.
Fill the missing rating values with the mean rating for that city, and return the rating column. The starter uses one global mean — fix it.
Build the whole thing as one chain and return its shape: deduplicate, lower-case item, fill rating per city, and reset the index. It should be (12, 6).