Dropping or Filling Missing Values
Once you know where a table's gaps are, dropna removes rows (all of them with any gap, or only those missing the columns you name in subset), fillna puts a value, a median or 'Unknown' in each gap, and a flag column records which values were filled so none is passed off as measured.
Apprentice · Python · Updated
After this lesson you can
- Drop rows with missing values using dropna with subset and how, and count the rows before and after
- Fill missing values with fillna, using a value, the median or 'Unknown', and flag the filled values in their own column
Key takeaways
- dropna() removes every row with any missing value, which can throw away far more than you meant; subset= drops only the rows missing the columns you name.
- how="all" drops a row only when every column you named is missing.
- fillna() puts a stand-in into each gap, such as 'Unknown' for a missing name or the median for a missing number.
- A filled value is a guess. Record which rows were filled in a True or False flag column before you fill, so nobody mistakes a guess for a measurement.
- Count the rows before and after every drop, so you know what each step cost.
Finding the gaps in a table is half the job; the other half is deciding what to do about each one. Leave it, drop the row, or fill in a stand-in? Each choice changes your answers, and a report that drops a third of its rows or invents values without saying so isn't one people should trust. You'll make this decision on every dataset you clean, for a class, a lab or a job.
The example is Happy Tails Animal Shelter's intake log, intake.csv: 800 rows, one for each time an animal arrived, typed in by volunteers. It's loaded with unknown and - read as missing, as Finding Missing Values: NaN and isna shows, so age_years is a column of numbers with 117 gaps and name has 70.
Dropping rows: dropna
The quickest fix is to throw away every row with a gap. dropna() does exactly that, and counting the rows before and after shows what it costs:
import pandas as pd
intake = pd.read_csv("intake.csv", na_values=["unknown", "-"])
print("rows:", len(intake))
print("rows with nothing missing:", len(intake.dropna()))
rows: 800
rows with nothing missing: 469
Over 40% of the log is gone. An animal with no name, or no answer yet in adopted, lost its whole row, even though its age and weight were fine. Like fillna below, dropna hands back a new table and leaves intake as it was.
Most questions need only a few columns. subset= names the columns that matter, and only rows missing one of those are dropped. For the average age, that's age_years:
with_age = intake.dropna(subset=["age_years"])
print("rows with an age:", len(with_age))
rows with an age: 683
With more than one column in subset, pandas drops a row when any of them is missing. how="all" changes that to "drop it only when all of them are missing", which keeps every row that has at least one of the two:
some_size = intake.dropna(subset=["age_years", "weight_lbs"], how="all")
print("rows with an age or a weight:", len(some_size))
rows with an age or a weight: 797
Filling with a value: fillna
Dropping isn't the only choice. fillna() puts a stand-in into every gap. A stray with no name still needs a name on its kennel card, and "Unknown" is honest:
intake["name"] = intake["name"].fillna("Unknown")
intake["name"].head(3)
0 Unknown
1 Biscuit
2 Rosie
Name: name, dtype: str
fillna hands back a new column, so the line stores it back in intake["name"].
Filling a number with the median
A missing age is harder. Writing 0 would make the animal a newborn and drag the average down. A better stand-in is the median, the middle value when the ages are sorted, which one very old animal can't pull up the way it pulls the average. But a rabbit isn't a dog, so each species' median is fairer than one median for all:
intake.groupby("species")["age_years"].median()
species
cat 5.0
dog 4.0
rabbit 3.0
Name: age_years, dtype: float64
Read the line as "group the rows by species, and give the median age of each group". To fill each gap with its own species' median, transform("median") lines that median up with every row; Grouping with groupby explains groupby in full.
A filled age is a guess, so first record which ages are about to be filled. isna() gives exactly that list of True and False, saved as a flag column:
species_median = intake.groupby("species")["age_years"].transform("median")
intake["age_estimated"] = intake["age_years"].isna()
intake["age_years"] = intake["age_years"].fillna(species_median)
intake[["animal_id", "species", "age_years", "age_estimated"]].head(8)
animal_id species age_years age_estimated
0 HT-1001 cat 3.0 False
1 HT-1002 dog 2.0 False
2 HT-1003 dog 4.0 True
3 HT-1004 cat 5.0 False
4 HT-1005 cat 5.0 True
5 HT-1006 dog 4.0 True
6 HT-1007 dog 4.0 True
7 HT-1008 dog 0.4 False
The order matters: the flag is made before the fill, while the gaps are still there to see.
print("ages missing:", intake["age_years"].isna().sum())
print("ages estimated:", intake["age_estimated"].sum())
ages missing: 0
ages estimated: 117
No gaps are left, and the flag still says which 117 ages are estimates. Filling in values like this is called imputation in statistics classes.
Which choice is honest
- Leave the gap when the question doesn't need that column, or when "we don't know" is the true answer (an adoption that hasn't happened yet).
- Drop the row when the row can't answer your question without the value, and say how many rows you dropped.
- Fill the gap when a reasonable stand-in exists, and flag every filled value so nobody mistakes a guess for a measurement.
Whichever you choose, count the rows before and after, and write the numbers down.
Check yourself
2 questions to try on the lesson page.
So what
Dropping and filling both change your answers, so the honest analyst picks one on purpose for each column, counts what it cost, and flags every value that was filled in.