Skip to content
Bamboo Weekly

pandas dropna

Find the missing values first. Then decide what removing them costs.

How many rows does .dropna() remove from a year of New York City traffic crashes? All of them. Ninety-one thousand rows go in, an empty data frame comes out, and nothing warns you. That is the shortest argument I know for treating .dropna() as a decision rather than a cleanup step.

The three methods work together. .isna() returns a boolean of the same shape as what you gave it: True wherever a value is missing. .notna() is its opposite. .dropna() removes the rows — or columns — that those booleans point at. So the first two are how you look, and the third is how you act, and the order matters more than people expect:

df.isna().sum()               # how much is missing, per column
df.loc[pd.col('borough').isna()]   # the rows that are missing it
df.dropna(subset='borough')        # ...and now, deliberately, without them

Every row you drop is a row that will not be in your mean, your count or your chart. That is fine when the rows you dropped resemble the rows you kept. On government data they very often do not, and then you have quietly changed the question, from "what happened" to "what happened among the cases somebody finished filling in."

Official documentation: DataFrame.dropna, Series.dropna, DataFrame.isna and DataFrame.notna. You will also see .isnull() and .notnull() in older code; they are aliases, and return exactly the same thing.

The arguments that earn their keep

.isna() and .notna() take no arguments at all. Everything below belongs to .dropna():

df.dropna()                            # drop rows with ANY missing value
df.dropna(how='all')                   # drop only rows that are entirely missing
df.dropna(subset='borough')            # judge rows on this column alone
df.dropna(subset=['lat', 'lon'])       # ...or on these columns
df.dropna(thresh=4)                    # keep rows with at least 4 real values
df.dropna(axis='columns')              # drop bad COLUMNS instead of rows
df.dropna(axis='columns', how='all')   # ...only the completely empty ones
df.dropna(ignore_index=True)           # renumber the survivors 0..n-1

subset is the one that saves you. Without it, .dropna() looks at every column in the frame, so a row you needed disappears because of a column your analysis never touches. Name the columns your question actually depends on and the rest stop voting. Misspell a name and you get a loud KeyError: ['Borough'], which is a mercy.

how takes 'any' (the default) or 'all'. Anything else raises ValueError: invalid how option: both. thresh replaces how with a count — keep a row if it has at least this many non-missing values — and you cannot pass both: TypeError: You cannot set both the how and thresh arguments at the same time.

axis='columns' turns the operation sideways: instead of which rows to keep, it asks which columns, which is the right question for a wide file where half the fields were never populated.

On a series there is no subset and no thresh — a series has one column of values — but .dropna(), how and ignore_index work.

A worked example, on real data

New York City publishes every police-reported motor vehicle collision, with no key and no signup. Here is 2024:

import pandas as pd

url = ('https://data.cityofnewyork.us/resource/h9gi-nx95.csv'
       '?$limit=200000&$where=date_extract_y(crash_date)=2024')

df = pd.read_csv(url, parse_dates=['crash_date'])

df.shape

(91316, 29)

Before touching anything, the audit. .isna() gives a frame of True and False; summing it counts the True values column by column, because True is 1 and False is 0:

df.isna().sum().sort_values(ascending=False).head(8)

vehicle_type_code_5              90657
contributing_factor_vehicle_5    90632
vehicle_type_code_4              89263
contributing_factor_vehicle_4    89132
vehicle_type_code_3              83758
contributing_factor_vehicle_3    83177
cross_street_name                65428
off_street_name                  44166
dtype: int64

That one line is the most useful thing on this page. It tells you which columns are usable, and it explains what happens next:

df.dropna().shape

(0, 29)

Every row is gone. The file has columns for a fifth vehicle in the collision, and 99 percent of crashes involve fewer than five, so every row has at least one missing value and how='any' disqualifies all of them. Nothing errors: you get a data frame with the right column names, and every number computed from it afterward is empty or NaN.

subset, and the cost of using it

Say the question is injuries by borough. Then borough is the column that matters:

df.dropna(subset='borough').shape

(65231, 29)

From 91,316 rows to 65,231. That is not a rounding error — it is 28.6 percent of the year's crashes, and before I use it I want to know who those 26,085 crashes belong to. .notna() gives me a label to group on:

(
    df
    .assign(has_borough=pd.col('borough').notna())
    .groupby('has_borough')
    .agg(crashes=('collision_id', 'count'),
         mean_injured=('number_of_persons_injured', 'mean'),
         killed=('number_of_persons_killed', 'sum'))
    .round(3)
)

             crashes  mean_injured  killed
has_borough
False          26085         0.684     108
True           65231         0.555     160

The rows with no borough are more dangerous than the rows with one. They are 29 percent of the crashes and 40 percent of the deaths — 108 of the 268 people killed on New York City streets in 2024. Drop them and the mean injuries per crash falls from 0.592 to 0.555, and 40 percent of the year's fatalities are not in the report at all.

Why are they missing? Look at where they happened:

df.loc[pd.col('borough').isna(), 'on_street_name'].value_counts().head(5)

on_street_name
BELT PARKWAY                  1255
LONG ISLAND EXPRESSWAY         862
BROOKLYN QUEENS EXPRESSWAY     805
GRAND CENTRAL PKWY             630
FDR DRIVE                      596

Highways. The borough field is blank when the crash happened on a limited-access road, because those are policed differently and the report does not carry a borough. So .dropna(subset='borough') is not removing bad data. It is removing the highways, and highway crashes are faster and deadlier than street crashes. The number that comes out the other end is still true; it is just an answer about surface streets, and the page it appears on had better say so.

Sometimes the better move is not to drop at all. groupby has its own dropna argument, and turning it off keeps the missing group as a group:

df.groupby('borough', dropna=False)['number_of_persons_killed'].sum()

borough
BRONX             33
BROOKLYN          53
MANHATTAN         32
QUEENS            31
STATEN ISLAND     11
NaN              108
Name: number_of_persons_killed, dtype: int64

One extra row, and the finding of the day is in it.

how and thresh: how bad is bad enough

The five contributing_factor_vehicle_* columns are a natural place to see the difference, because a crash fills in as many of them as there were vehicles:

factors = ['contributing_factor_vehicle_1', 'contributing_factor_vehicle_2',
           'contributing_factor_vehicle_3', 'contributing_factor_vehicle_4',
           'contributing_factor_vehicle_5']

df[factors].dropna().shape              # (684, 5)
df[factors].dropna(how='all').shape     # (90669, 5)
df[factors].dropna(thresh=2).shape      # (69484, 5)
df[factors].dropna(thresh=3).shape      # (8139, 5)

Four numbers, four different populations. The default keeps 684 rows: five-car pileups. how='all' keeps 90,669: every crash where somebody wrote down at least one cause. thresh=2 keeps 69,484, which is a reasonable definition of "multi-vehicle collision" — and notice that I only get to call it that because I chose the threshold on purpose.

how='all' is the one to reach for when a file arrives with junk in it — blank rows at the bottom of a spreadsheet, separator rows in a PDF table. Those rows are entirely missing and nothing else is, so removing them costs you nothing.

Dropping columns instead of rows

Six of these 29 columns are more than 90 percent empty. Rather than let them destroy rows, throw the columns away:

df.dropna(axis='columns').columns.tolist()

['crash_date', 'crash_time', 'number_of_persons_injured',
 'number_of_persons_killed', 'number_of_pedestrians_injured',
 'number_of_pedestrians_killed', 'number_of_cyclist_injured',
 'number_of_cyclist_killed', 'number_of_motorist_injured',
 'number_of_motorist_killed', 'collision_id']

Eleven columns with no gaps at all, and all 91,316 rows still there. That is a strict rule; a gentler one is to keep any column that is at least 90 percent populated:

df.dropna(axis='columns', thresh=int(len(df) * 0.9)).shape

(91316, 16)

Sixteen columns, which adds back latitude, longitude, location, the first contributing factor and the first vehicle type — the fields you would actually map and count with. thresh on columns takes a row count, not a fraction, which is why the int(len(df) * 0.9) is there.

Five mistakes people make

Letting the default decide which rows your question needs. how='any' inspects all 29 columns, so a row vanishes because of a field nothing in your analysis reads. On this file that took 91,316 rows to zero; on tidier data it takes 20 percent and you never notice. If you can name the columns your question depends on, put them in subset; nearly half of the Bamboo Weekly solutions that call .dropna() pass one, and each of those is a place where the default would have removed the wrong rows.

Dropping before you select your columns. Same data, same two operations, opposite order:

cols = ['crash_date', 'borough', 'number_of_persons_injured']

df.dropna()[cols].shape     # (0, 3)
df[cols].dropna().shape     # (65231, 3)

Select first, then drop, and the 26 columns you are not using stop having an opinion. It is the same idea as subset, and it bites more often, because the cleaning tends to get pasted at the top of the chain and the column selection happens further down.

Comparing to np.nan or None instead of calling .isna(). A missing value is not equal to anything, including itself, so == np.nan matches nothing and reports zero rather than raising. That trap, and the audit that avoids it, are on the isna page.

Assuming the three flavors of missing are one thing. None is Python's, np.nan is a float, NaT is the datetime version, and Pandas has its own pd.NA for its nullable dtypes. .isna() and .dropna() treat all of them as missing, which is the point of having those methods:

mixed = pd.DataFrame({'agency': ['NYPD', None, '', 'FDNY'],
                      'budget': [1.0, np.nan, 2.0, 3.0],
                      'filed': pd.to_datetime(['2024-01-05', None,
                                               '2024-03-01', '2024-04-01'])})
mixed

  agency  budget      filed
0   NYPD     1.0 2024-01-05
1    NaN     NaN        NaT
2            2.0 2024-03-01
3   FDNY     3.0 2024-04-01

Row 1 holds a None, a NaN and a NaT — the None prints as NaN, which is Pandas quietly agreeing that they mean the same thing here — and mixed.isna().sum() reports 1 for each of the three columns, as it should. Where they stop being interchangeable is dtypes: NaN is a float, so a column of integers with one gap in it becomes a float column, and 1 prints as 1.0. That is the usual reason a table of counts comes out with decimal points.

Treating an empty string as missing. An empty string, a whitespace-only string, and a placeholder spelled Unspecified are all perfectly good values that dropna will never remove. Finding those is a different job from dropping them, and it belongs to isna and describe.

Where it shows up in Bamboo Weekly

Bamboo Weekly #45: Netflix is where I build the .isna().sum() audit from scratch, then divide by len(df.index) to get a proportion. On Netflix's engagement report the answer is 0.733: three quarters of the titles have no release date, which changes what questions you are willing to ask. It is also where NaT turns up.

Bamboo Weekly #33: Fracking does the audit on six million rows of FracFocus well records, as a percentage: (df.isna().sum() / len(df.index)) * 100. Only then does it drop, and only on the column the question needs — .dropna(subset=['JobStartDate']) — having established that this costs about 0.011 percent of the rows. Look at the number before you delete, then delete narrowly: that is the whole method.

Bamboo Weekly #68: Dangerously hot weather is the thresh case in the wild. Reading a table out of a PDF produced one row of nothing but NaN, and the solution says out loud why the default will not do: "By default, dropna removes any row containing even one NaN value. That's too strict for our purposes, so I used the thresh keyword argument to say that as long as we have at least 4 good values, we should keep the row."

Bamboo Weekly #20: World inflation uses .dropna(subset=['Country', 'Series Name']) on a World Bank export, where the last few rows are footnotes rather than countries. A compound subset is the usual way to strip the trailing junk off a spreadsheet without touching the gaps inside the data itself.

Practice it

Work through a .dropna() exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/dropna/

Go deeper

Removing missing values is one of three choices, and the loudest. The other two are filling them — fillna for a constant or a per-column mapping, interpolate for values that lie on a line between their neighbors — and keeping them, which several methods will do if you ask: value_counts(dropna=False) counts them as their own entry, groupby(dropna=False) gives them their own group, and the aggregations skip them without being told.

Nearby: loc is what turns an .isna() mask into rows, value_counts is how you find the sentinel values that .isna() cannot see, and read_csv takes na_values= so you can declare them missing at the moment the file loads. The Pandas missing data user guide covers pd.NA and the nullable dtypes, which is the modern way to keep an integer column integer when it has gaps.

More Pandas videos on Python and Pandas with Reuven Lerner.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

See it on real data

Below are the 57 Bamboo Weekly exercises that use dropna on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.