Module 3: Cleaning data · Lesson 3.1 · about 20 min
Missing values: find, fill or drop
Find missing values with isna, decide what each gap means, then drop rows, fill with a fixed value, a group average or the previous value, and keep a record of what you changed.
Module 3 · about 20 minutes · uses Lesson 1.6. You’ll be able to find missing values, choose a sensible fix for each column, and apply it.
In everyday terms: a gap in a till receipt could be a smudge (unknown), an item the customer did not buy (zero) or a till fault (needs fixing). Treating all three the same gives wrong totals.
1. Find them
pandas marks missing values as NaN (numbers), NaT (dates) or <NA>. Empty cells in a CSV become missing automatically; other markers (-, n/a, ?) need na_values when reading (Lesson 1.5).
2. Decide what each gap means
Before touching anything, ask why it’s missing:
| Column | Likely meaning | Sensible action |
|---|---|---|
| Discount | no discount entered | fill with 0 |
| Qty | not recorded | drop the line (can’t compute revenue), or ask the source |
| Customer ID | guest checkout | fill with 'GUEST', keep the sale |
3. Drop or fill
dropna(subset=[...]) drops rows missing those columns only; plain dropna() drops a row if anything is missing, which often throws away far too much.
4. Smarter fills
- Forward fill suits values that persist until changed (a price, a status).
- Group mean or median beats the overall mean when groups differ.
- Interpolation suits smooth measurements over time.
- Add a flag column (
df['price_was_missing'] = df['price'].isna()) before filling, so the change is traceable.
- NaN / None / NaT
- pandas markers for "no value" in numbers, objects and dates.
- isna().sum()
- Missing count per column.
- dropna(subset=[...])
- Remove rows missing in those columns.
- fillna(value)
- Replace gaps with a value, or with group values via transform.
- Physical meaning
- A gap is information: "not recorded" is different from "zero". Sums skip NaN, so an unfilled gap quietly lowers totals; a gap filled with 0 states that nothing happened. Pick the one that is true.
“Fill every gap with 0 so the maths works.” If qty was not recorded, 0 claims the customer bought nothing and drags averages down. Fill with 0 only when “nothing” is the true meaning; otherwise drop, fill from a sensible group value, or keep and flag.
Watch it happen, one step at a time:
| order | store | qty |
|---|---|---|
| 1 | S01 | 2 |
| 2 | S01 | NaN |
| 3 | S02 | 4 |
| 4 | S02 | NaN |
| 5 | S02 | 6 |
df['qty'].isna().sum() → 2.df['qty'].fillna(df.groupby('store')['qty'].transform('median'))| order | store | qty |
|---|---|---|
| 1 | S01 | 2 |
| 2 | S01 | 2 |
| 3 | S02 | 4 |
| 4 | S02 | 5 |
| 5 | S02 | 6 |
5. Your turn
How it works
Principle: missing data is only harmless when it’s missing at random. If gaps cluster in one group (one store’s till didn’t record quantities, unhappy customers skip surveys), any fill or drop can bias the result. Always look at who is missing: df[df['x'].isna()]['store'].value_counts().
Effect of mean filling: with k of n values filled at the mean, the sample variance shrinks by roughly the factor , so standard errors and tests become too optimistic.
6. Practice
- Count missing values in one column,
df['email']. - Which is safer for a missing price: overall mean or that product’s last known price?
- Write a line that keeps only rows with no missing values in
dateandamount.
Answers
df['email'].isna().sum().- The product’s last known price (a group-wise forward fill): prices persist and differ by product.
df.dropna(subset=['date', 'amount']).
Try it on your own data: CSV profiler counts missing values per column in any CSV.
New words: NaN, isna, dropna, fillna, forward fill, interpolate, missing at random.
- Find gaps with isna().sum() before analysing.
- Decide what each gap means: unknown, zero, or an error.
- Drop, fill with a fixed value, fill from a group (transform), or keep and flag.
- Sums and means skip NaN, so gaps change results silently.
Check yourself
Sources
- pandas documentation, User Guide: Working with missing data https://pandas.pydata.org/docs/user_guide/missing_data.html
- Little, R. J. A. and Rubin, D. B. (2019), Statistical Analysis with Missing Data, 3rd edition, Wiley
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.