Skip to content
Bamboo Weekly

pandas drop_duplicates

Look at the repeated rows before you delete them.

How many rows does .drop_duplicates() remove from a messy government CSV? Very often the answer is zero — and the file is still full of repetition. That gap between what the method does and what people assume it does is why I teach these two together, in this order.

.duplicated() returns a boolean series with one entry per row: True if this row has been seen before, False if it is the first of its kind. .drop_duplicates() hands back the data frame with the True rows removed. One operation, seen from two sides:

df.drop_duplicates()      # is exactly
df.loc[~df.duplicated()]

They take the same arguments, so everything you learn about one applies to the other. The reason to reach for .duplicated() first is that "duplicate" is not a property your data has. It is a claim you are making about what counts as the same thing. Two rows naming the same clinic under two spellings are duplicates; two rows describing the same disaster in two counties may or may not be, depending on the question you asked. Pandas cannot decide that for you, and will not warn you when you decide badly. .duplicated() lets you inspect the rows your definition catches, while they are still there to inspect.

Official documentation: DataFrame.drop_duplicates, DataFrame.duplicated, Series.drop_duplicates and Series.duplicated

The arguments that earn their keep

There are three, and the first two belong to both methods:

df.duplicated()                                # a boolean series, one entry per row
df.drop_duplicates()                           # "same" means every column matches
df.drop_duplicates(subset='disasterNumber')    # "same" means this column matches
df.drop_duplicates(subset=['state', 'name'])   # these columns, taken together
df.drop_duplicates(keep='last')                # keep the last copy, not the first
df.drop_duplicates(keep=False)                 # drop every copy of anything repeated
df.drop_duplicates(ignore_index=True)          # renumber the survivors 0..n-1

subset is the argument that matters, and its default — all columns — is almost never the definition you want on real data. Name one column or a list of them and Pandas compares only those. Misspell a name and you get a loud KeyError, which is a relief after so many Pandas arguments that fail silently.

keep accepts 'first' (the default), 'last', or False. The first two choose which copy survives. False is different in kind: it deletes every copy, including the one you thought of as the original, leaving only rows that had no twin at all.

ignore_index=True renumbers the survivors from 0; without it they keep their original labels, so the index has gaps. On a series there is no subset — a series has one column of values to compare — but keep and ignore_index work.

A worked example, on real data

FEMA publishes every U.S. disaster declaration since 1953 in a single CSV, and Bamboo Weekly #86 built a puzzle around it. FEMA adds to the file constantly, so your numbers will be a little larger than mine:

import pandas as pd

url = 'https://www.fema.gov/api/open/v2/DisasterDeclarationsSummaries.csv'

df = pd.read_csv(url)

df.shape

(70248, 29)

Seventy thousand rows. How many are duplicates? df.duplicated().sum() returns 0. Not one row in the file repeats another.

And yet df['disasterNumber'].nunique() returns 5248. Those 70,248 rows describe 5,248 disasters, because FEMA writes one row per county per declaration. Disaster 4586, the Texas freeze of February 2021, occupies 254 of them:

(
    df
    .loc[pd.col('disasterNumber') == 4586,
         ['disasterNumber', 'state', 'declarationTitle', 'designatedArea']]
    .head(3)
)

      disasterNumber state      declarationTitle  designatedArea
5800            4586    TX  SEVERE WINTER STORMS  Floyd (County)
5801            4586    TX  SEVERE WINTER STORMS  Garza (County)
5802            4586    TX  SEVERE WINTER STORMS  Bowie (County)

The default found nothing to remove because it asks whether all 29 columns match, and every row carries its own hash and a unique UUID in id. Two columns I do not care about were enough to make 254 rows distinct. So I say what I mean, and I say it to .duplicated() first:

df.duplicated(subset='disasterNumber').sum()

That returns 65000. Ninety-two percent of the file is about to disappear — exactly the kind of number worth seeing before rather than after. It is also the right answer here, because counting disasters is what I came to do:

df.drop_duplicates(subset='disasterNumber').shape

(5248, 29)

Which of the 254 Texas rows survived? By default, the first one Pandas met:

(
    df
    .loc[pd.col('disasterNumber') == 4586]
    .drop_duplicates(subset='disasterNumber')
    [['disasterNumber', 'designatedArea']]
)

      disasterNumber  designatedArea
5800            4586  Floyd (County)

Pass keep='last' and Glasscock County survives instead. Neither choice means anything — Floyd and Glasscock are wherever FEMA happened to put them. If the surviving row's other columns matter, sort first, so that "first" becomes a decision: chaining .sort_values('designatedArea') ahead of the same call leaves Anderson County.

keep=False asks a different question altogether — not "one row per disaster" but "which disasters touched exactly one county?"

(
    df
    .drop_duplicates(subset='disasterNumber', keep=False)
    ['incidentType']
    .value_counts()
    .head()
)

incidentType
Fire            1415
Flood            217
Severe Storm     105
Biological        54
Hurricane         49
Name: count, dtype: int64

Of 5,248 disasters, 2,025 were confined to a single county, and most of those were fires. A real finding, out of an argument people usually meet as a mistake.

With one row per disaster, the ordinary counting works:

(
    df
    .drop_duplicates(subset='disasterNumber')
    .loc[pd.col('fyDeclared') >= 2021]
    .pivot_table(index='fyDeclared', columns='incidentType',
                 values='disasterNumber', aggfunc='count')
    [['Fire', 'Flood', 'Hurricane', 'Severe Storm']]
)

incidentType  Fire  Flood  Hurricane  Severe Storm
fyDeclared
2021          47.0    8.0       32.0          14.0
2022          41.0   10.0       10.0          24.0
2023          32.0   23.0       10.0          28.0
2024          56.0   21.0        8.0          43.0
2025          82.0   14.0        4.0          31.0
2026          61.0   12.0        1.0          12.0

Skip the drop_duplicates and every number is county-weighted rather than disaster-weighted: 2021 becomes 771 hurricanes against 88 fires, reversing the answer. Nothing errors, and the chart looks fine.

The survivors keep their original labels, so a deduplicated index has holes in it — 0, 1, 3, 5. Add ignore_index=True and you get 0, 1, 2, 3:

(
    df
    .drop_duplicates(subset='disasterNumber', ignore_index=True)
    [['disasterNumber', 'state', 'designatedArea']]
    .head(4)
)

   disasterNumber state                     designatedArea
0            5672    AK  Southeast Fairbanks (Census Area)
1            5670    OR                   Jackson (County)
2            5669    OK                  Oklahoma (County)
3            5668    NV                    Washoe (County)

Five mistakes people make

Dropping on all columns when you meant a key subset. This is the big one, and the FEMA file above is the whole demonstration: df.drop_duplicates() removed nothing from a data frame in which 92 percent of the rows repeated a disaster. One hash column was enough. Any table with a row ID, a timestamp, a source URL or a record hash behaves this way, and it fails silently — you get a frame back, it has the shape you did not check, and the analysis proceeds. If you can name the thing you want one row of, put that name in subset.

Expecting keep=False to keep one of each. It keeps none of them. 'first' and 'last' choose which copy survives; False disqualifies anything repeated entirely, which on the FEMA data took 5,248 disasters down to 2,025. It is a genuinely useful argument, but it is not a stricter version of the default, and everyone learns that the hard way once.

Whitespace and capitalization make identical-looking rows distinct. The Electronic Frontier Foundation's Atlas of Surveillance records 15,186 agency-technology pairs, and its Vendor column has 345 distinct values — 344 after stripping whitespace, and 322 after folding the case too:

aos = pd.read_csv('https://www.atlasofsurveillance.org/download.csv')

aos['Vendor'].value_counts().loc[['Axon', 'Axon ', 'Idemia', 'IDEMIA']]

Vendor
Axon      899
Axon        1
Idemia    257
IDEMIA      1
Name: count, dtype: int64

Look at the first two rows. They print identically, and one is a different vendor as far as Pandas is concerned, because someone typed a trailing space. .drop_duplicates() compares values, not appearances, so it happily keeps both. Clean the column first — str.strip and str.lower between them fix most of it — and drop duplicates afterward.

Not checking .duplicated().sum() before you drop. .drop_duplicates() never tells you what it did — no count, no warning, and because it returns a new frame rather than modifying yours, nothing looks different until the totals come out wrong. One line, df.duplicated(subset=…).sum(), turns an invisible deletion into a number you can sanity-check, and df.loc[df.duplicated(subset=…, keep=False)] shows you the rows themselves.

A subset so narrow that genuinely different things collapse. The mistake runs both ways. The CDC's 2021 ART report covers 453 distinct fertility clinic names across 454 state-and-name pairs, because "Family Fertility Center" is a clinic in Bethlehem, Pennsylvania and a different clinic in Houston, Texas. Deduplicate on FacilityName alone and you have silently merged two clinics. Names are rarely keys; when you find yourself deduplicating on one, ask what else has to match for two rows to really be the same row.

Where it shows up in Bamboo Weekly

Bamboo Weekly #55: IVF is the clearest example of a compound key chosen on purpose. The CDC file holds one row per clinic per statistic, so counting clinics per state starts with [['LocationAbbr', 'FacilityName']].drop_duplicates() — both columns, for the Bethlehem-and-Houston reason above — and only then value_counts.

Bamboo Weekly #62: Economic report card pulls the G20 membership table off Wikipedia, where 24 rows list 21 members: China, the European Union and the African Union each occupy two rows. .drop_duplicates() on the Member column collapses them, and the next two str.replace calls in that chain fix the spellings so the names match the other data set. That pairing — deduplicate, then reconcile spellings — is the whole job in miniature.

Bamboo Weekly #46: Pedestrians uses the series form on federal crash data, where each row is a person rather than a crash. .drop_duplicates() on the ST_CASE column turns person-rows back into distinct crashes before anything gets counted.

Bamboo Weekly #86: FEMA is the source of the worked example above. Its chains keep returning to .drop_duplicates(subset='disasterNumber') — once before a pivot_table, again before a histogram of declaration months — for the simple reason that no question about disasters can be answered from a table of counties.

Practice it

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

Go deeper

.duplicated() has a cousin: Index.duplicated asks the same question of your index labels rather than your rows, which is how you deal with a repeated index after a join. Bamboo Weekly #91: Roller coasters does exactly that with .loc[lambda df_: ~df_.index.duplicated(keep='first')].

Around these two methods, value_counts is usually how you discover the repetition, sort_values is how you make keep='first' mean something, and groupby is what you want instead whenever the duplicates should be combined rather than discarded.

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 13 Bamboo Weekly exercises that use drop_duplicates on real-world data — try each one, then study the worked solution.

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