Finding Missing Values: NaN and isna
pandas marks a missing value as NaN, isna().sum() counts the missing values in every column, na_values in read_csv turns hidden blanks such as 'unknown' and '-' into NaN, and count and mean quietly leave missing values out.
Apprentice · Python · Updated
After this lesson you can
- Find the missing values in each column with isna().sum(), and explain how missing values change count and mean
- Read hidden blanks such as 'unknown' and '-' as missing with read_csv's na_values
Key takeaways
- pandas marks a missing value as NaN, read "not a number". Python's None and pandas' pd.NA mean the same thing in other places.
- isna() answers True or False for every value, and isna().sum() counts the missing values in each column.
- A file can hide its blanks as words such as 'unknown' or '-'. Listing them in read_csv's na_values reads them as NaN.
- count() counts only the values that are there, and mean() averages only those, so always check how many are missing before you trust a number.
Almost every real table has holes in it: a survey question someone skipped, a sensor that dropped out for an hour, a form field a busy volunteer left blank. Before you average, total or chart anything, you need to know where the holes are and how many there are, because pandas works around them quietly. A class project, a report for a manager and a data job all start with the same check, and this lesson is that check.
The example is Happy Tails Animal Shelter's intake log, intake.csv: one row for each time an animal arrived this year, 800 rows typed in by volunteers. Its columns are animal_id (such as HT-1001), name, species (dog, cat or rabbit), breed, age_years, weight_lbs, intake_date, intake_type (stray, owner surrender or transfer), adopted and adoption_fee. The log is our own made-up data.
What missing looks like
Load the log in a notebook cell and look at a few columns:
import pandas as pd
intake = pd.read_csv("intake.csv")
intake[["animal_id", "name", "breed", "age_years", "adopted"]].head(8)
animal_id name breed age_years adopted
0 HT-1001 NaN Domestic longhair 3 N
1 HT-1002 Biscuit Labrador mix 2 Y
2 HT-1003 Rosie German shepherd mix NaN yes
3 HT-1004 Rocky Domestic shorthair 5 Y
4 HT-1005 Peanut Domestic longhair unknown NaN
5 HT-1006 Clover Labrador mix unknown NaN
6 HT-1007 Maple Husky mix unknown NaN
7 HT-1008 Biscuit NaN 0.4 Y
VS Code draws the table as a grid; this lesson shows it as pandas prints it as text. Where a cell in the file was empty, pandas shows NaN, read "not a number". It's pandas' mark for a missing value: there's no value here. HT-1001 came in as a stray with no name, so its name is NaN.
You'll meet two cousins of NaN. Python's own None means "nothing here" in plain Python code, and pandas also has pd.NA for the same idea in a few column types. Each means missing; NaN is the one this log shows.
Counting the gaps with isna
The shelter's director asks a fair question: how old are the animals we take in, on average?
intake["age_years"].mean()
TypeError: Cannot perform reduction 'mean' with string dtype
That's the last line of the error. The ages aren't numbers to pandas yet, and counting what's in the column shows why:
intake["age_years"].value_counts().head(4)
age_years
2 109
4 86
3 80
unknown 65
Name: count, dtype: int64
Sixty-five rows say unknown. Before fixing anything, find every gap. isna() (read "is n a") asks every value "are you missing?" and answers True or False:
intake["name"].isna().head(3)
0 True
1 False
2 False
Name: name, dtype: bool
Python counts True as 1 and False as 0, so adding up the answers counts the missing values. On the whole table, isna().sum() does it for every column at once:
intake.isna().sum()
animal_id 0
name 70
species 0
breed 34
age_years 52
weight_lbs 20
intake_date 0
intake_type 0
adopted 105
adoption_fee 50
dtype: int64
Seventy animals have no name and 105 have no answer in adopted yet. The 52 missing ages, though, are only the empty cells.
Blanks the file hides
The unknown ages are missing too; they're spelled differently. Volunteers also typed - for 14 weights nobody took. pandas already reads a handful of spellings as missing, n/a, NA and null among them, which is why the breeds a volunteer typed as n/a show as NaN. It can't guess that unknown or - mean the same thing in this file.
Tell it, with read_csv's na_values: a list of the words that mean "missing" in this file.
intake = pd.read_csv("intake.csv", na_values=["unknown", "-"])
intake.isna().sum()
animal_id 0
name 70
species 0
breed 34
age_years 117
weight_lbs 34
intake_date 0
intake_type 0
adopted 105
adoption_fee 50
dtype: int64
Now the counts tell the truth: 117 ages are missing (52 blanks and 65 unknown), and 34 weights (20 blanks and 14 dashes). A quick value_counts() on each text column is how you spot words like these.
What missing values do to count and mean
With unknown read as missing, the director's question has an answer. Look at what each number counts:
ages = intake["age_years"]
print("rows:", len(ages))
print("count:", ages.count())
print("missing:", ages.isna().sum())
print("mean:", round(ages.mean(), 1))
rows: 800
count: 683
missing: 117
mean: 4.9
len() counts every row, 800. count() counts only the values that are there, 683. And mean() adds up those 683 ages and divides by 683, skipping the missing ones without a word. So 4.9 years is the average age of the animals whose age we know. That's a fine answer, as long as the report says it's based on 683 of 800 animals. Checking how many values are missing before you trust a number is the habit this whole chapter builds.
Check yourself
2 questions to try on the lesson page.
So what
Every analysis starts by counting what's missing: isna().sum() shows the gaps, na_values reveals the ones a file hides, and knowing them is what lets you say how much a number can be trusted.