All lessons

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.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

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.

A missing value is a question before it is a problem: find out why it is missing, then drop it, fill it, or keep it and say so.

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

Pythonorders_messy.csv

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

Pythonorders_messy.csv

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

Python
  • 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:

The data.
orderstoreqty
1S012
2S01NaN
3S024
4S02NaN
5S026
Count. df['qty'].isna().sum() → 2.
Why? The till export dropped qty on two lines; the orders did happen.
Fill from the store's median. df['qty'].fillna(df.groupby('store')['qty'].transform('median'))
Result.
orderstoreqty
1S012
2S012
3S024
4S025
5S026
Flag the filled rows in a column so readers know.

5. Your turn

Your turnorders_messy.csv
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 (n−k)/n, so standard errors and tests become too optimistic.

6. Practice

  1. Count missing values in one column, df['email'].
  2. Which is safer for a missing price: overall mean or that product’s last known price?
  3. Write a line that keeps only rows with no missing values in date and amount.
Answers
  1. df['email'].isna().sum().
  2. The product’s last known price (a group-wise forward fill): prices persist and differ by product.
  3. 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

1How do you count missing values in every column?
2A missing discount most likely means "no discount". What should you do?
3Why is filling missing incomes with the overall mean risky?

Sources

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.