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

By

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.

Key terms

Related