Finding repeated rows, deciding which one to keep, and the subset argument that does the real work.
Overview
duplicated marks the copies
Duplicate rows appear for many reasons. A data source sends the same record twice. A join multiplies rows because of a many-to-many key nobody expected. A concat stacks two exports that overlap in time. An append inside a loop adds the same batch on every iteration.
Finding duplicates is easy. The harder part is deciding what counts as a duplicate and which copy to keep. Two rows might match on every column, or they might match only on an id while differing on a timestamp. The first case is a true copy. The second might be an update history where you want the latest version. The subset parameter controls which columns matter.
Ignoring duplicates biases every aggregate. A duplicated sale doubles the revenue for that product. A duplicated survey response shifts the distribution. The effect is proportional to how many copies exist and how different they are from the rest. In small datasets the effect is obvious. In large ones it hides inside plausible-looking totals.
Checking for duplicates belongs in the inspection routine, right after shape and dtypes. A quick df.duplicated().sum() tells you whether the problem exists. If it does, the next step is to look at the actual rows, decide on a subset and a keep rule, and document the choice.
This module covers duplicated for detection, drop_duplicates for removal, and the subset and keep parameters that control both. It also covers the case where duplicates are expected and meaningful -- for example, a log where the same event can fire many times.
The examples show what happens to aggregates before and after deduplication. That makes the cost of ignoring the problem concrete. A single duplicated row in the right place can shift an average, inflate a total, or change which product ranks first.
df.duplicated() returns a boolean Series: True for a row that has been seen before, False for its first case.
That skew is deliberate. It means df[~df.duplicated()] keeps just one of each. df[df.duplicated()] shows you what would be removed.
keep controls which case is treated as the first:
keep="first" (the default) flags each copy after the first.
keep="last" flags each copy before the last.
keep=False flags all of them, including the first. This is the one to use when inspecting. It shows you the full groups rather than half of each.
Worth knowing
duplicated() flags repeats but not the first occurrence, which is what makes it usable as a drop mask.
keep="last" flags the earlier copies; keep=False flags every copy, which is what you want for inspection.
subset= carries the meaning — the real question is usually whether the key is duplicated, not the whole row.
drop_duplicates keeps the first row in the frame's current order, so sort first if which one survives matters.
Normalise case and whitespace before deduplicating, or the same value counts as several.
A duplicated key silently multiplies rows in a later merge — is_unique is worth asserting where uniqueness is assumed.
Duplicates
Finding repeated rows and deciding which one to keep.
duplicated flags the repeats, not the original
The first occurrence is False by default, which is what makes it a drop mask.
example_01.pypandas
import pandas as pd
df = pd.DataFrame({
"name": ["ana", "raj", "ana", "kim", "raj"],
"city": ["pune", "delhi", "pune", "goa", "delhi"],
})
print(df.assign(dup=df.duplicated()))
print()
print("total duplicated rows:", int(df.duplicated().sum()))
print()
print("keep='last' flags the earlier ones instead:")
print(list(df.duplicated(keep="last")))
print("keep=False flags EVERY copy, including the first:")
print(list(df.duplicated(keep=False)))
Output
subset is where the meaning lives
Rows are rarely duplicated in every column - usually just in the key.
example_02.pypandas
import pandas as pd
df = pd.DataFrame({
"id": [1, 2, 1, 3],
"name": ["ana", "raj", "ana", "kim"],
"score": [10, 20, 99, 30],
})
print(df)
print()
print("whole-row duplicates:", int(df.duplicated().sum()),
"<- none, the scores differ")
print("duplicate ids :", int(df.duplicated(subset=["id"]).sum()))
print()
print("That is the real question: is 'id' supposed to be unique?")
print(df[df.duplicated(subset=["id"], keep=False)])
Output
drop_duplicates keeps the first by default
So the row you keep depends on the order the frame happens to be in.
example_03.pypandas
import pandas as pd
df = pd.DataFrame({
"id": [1, 1, 2],
"updated": ["2024-01-01", "2024-06-01", "2024-03-01"],
"score": [10, 99, 20],
})
print("as loaded:")
print(df.drop_duplicates(subset=["id"]))
print()
print("that kept score 10 - the FIRST row, not the newest.")
print()
best = df.sort_values("updated").drop_duplicates(subset=["id"], keep="last")
print("sort first, then keep='last':")
print(best)
print()
print("Deduplication without an explicit sort is a coin flip.")
Output
Duplicates that are not identical
Whitespace and case make two spellings of the same thing.
example_04.pypandas
import pandas as pd
s = pd.Series(["Pune", "pune ", "PUNE", "delhi"])
print("raw value_counts:")
print(s.value_counts())
print()
clean = s.str.strip().str.lower()
print("after strip().lower():")
print(clean.value_counts())
print()
print("duplicates before:", int(s.duplicated().sum()))
print("duplicates after :", int(clean.duplicated().sum()))
print()
print("Normalise before deduplicating, or you keep four cities.")
Output
Counting instead of dropping
Sometimes the repeat is the data, and you want it aggregated.
example_05.pypandas
import pandas as pd
orders = pd.DataFrame({
"customer": ["ana", "raj", "ana", "ana", "kim"],
"amount": [100, 200, 50, 75, 300],
})
print("how many rows per customer:")
print(orders["customer"].value_counts())
print()
print("collapse them into one row each:")
print(orders.groupby("customer", as_index=False)
.agg(orders=("amount", "size"), total=("amount", "sum")))
print()
print("Dropping duplicates here would have thrown away real orders.")
Output
Checking uniqueness as an assertion
Cheaper to state the assumption than to debug the join that breaks later.
example_06.pypandas
import pandas as pd
df = pd.DataFrame({"id": [1, 2, 3], "v": [10, 20, 30]})
dupe = pd.DataFrame({"id": [1, 2, 2], "v": [10, 20, 30]})
for name, frame in [("clean", df), ("with a repeat", dupe)]:
unique = frame["id"].is_unique
print("%-14s id unique: %s" % (name, unique))
print()
print("as a hard check:")
try:
assert dupe["id"].is_unique, "id must be unique"
except AssertionError as e:
print(" AssertionError:", e)
print()
print("A duplicated key silently multiplies rows in a merge, which is")
print("the subject of a later module and a very common surprise.")
Output
subset is the important argument
Whole-row copies — each column same — are the easy case and often not the real one.
The question that often matters is whether a key is repeated. Two rows with the same customer id and other scores aren't same rows. They may well be a data problem.
df.duplicated(subset=["id"]) asks that question. Mixed with keep=False, it shows you each conflicting group:
That is the first thing to run when a key is supposed to be unique and a thing downstream suggests it isn't.
Order decides what survives
drop_duplicates keeps the first matching row in the frame's current order.
That order is what the data happened to arrive in. If the rows represent versions of a record and you want the newest, keeping the first is any. It will be right at times and wrong at times. Nothing will tell you which.
Now "the newest per id" is what the code says. It doesn't depend on how the file was written.
This is one of the most common silent errors in data cleaning. The result always looks plausible. You asked for one row per id and you got one row per id.
Near-duplicates
Exact matching misses the copies that really occur in real data.
"Pune", "pune " and "PUNE" are three distinct values to pandas and one city to all else. So are "Ltd" and "Ltd.", or a name with a double space.
Norm before deduplicating:
df["city"] = df["city"].str.strip().str.lower()
value_counts() on the column before and after is the quickest way to see how much gap it made. To spot the variants you had not thought of.
For truly fuzzy matching — misspellings, transpositions — pandas has nothing built in. The answer is a dedicated library. But stripping and lowercasing catches the large majority of real cases and costs one line.
Sometimes the repeat is the data
Not each repeated value is an error. Five orders from the same customer are five orders.
The tell is whether the rows carry split data. If the copy rows differ in a real column, dropping them destroys data. The step you want is an aggregation:
That merges each customer to one row and keeps what the repeats were telling you.
Reaching for drop_duplicates when you meant groupby is a quiet way to lose most of a dataset.
Assert uniqueness where you assume it
s.is_unique is a cheap check, and df.index.is_unique covers the index.
Where a key is supposed to be unique. Because it's a main key, or because a later merge depends on it. An clear assertion is worth the line:
assert df["id"].is_unique, "id must be unique"
The reason is exact: a duplicated key on either side of a merge silently times rows. Ten thousand rows join to eleven thousand, no one notices. Each later sum is inflated. merge(..., validate="one_to_one") catches it at the join. Asserting earlier catches it closer to the cause.
Duplicates as a data-quality signal
A copy is rarely just a copy. It often means one of a small number of things. Finding which changes what you should do.
A repeated load. The same file processed twice, or an append that ran again. Whole-row copies, exact. Safe to drop.
A join that times'd rows. copies in some columns and not others, appearing only after a merge. The fix is at the merge, not here.
Genuine repeated events. Two orders from the same customer. Not copies at all. Aggregate rather than drop.
Multiple versions of a record. Same key, other timestamps. Keep one. *which* one matters — sort first.
Near-copies from inconsistent entry. Same entity, other spelling. Norm first, or you'll keep both.
Running df.duplicated().sum() and df.duplicated(subset=[key]).sum() and checking the two numbers often tells you which case you're in.
Finding what differs
When a key is duplicated but the rows aren't same, the handy question is which columns disagree:
"Merge the rows" — taking the first non-missing value of each column — is a group-by:
df.groupby("id", as_index=False).first()
first() skips missing values, so it mixes partial records rather than picking one. That is often what people really want when they reach for drop_duplicates.
Duplicated columns and index labels
copy column names are legal and cause real confusion: df["a"] returns a DataFrame rather than a Series when two columns are called a.
df.columns.duplicated() finds them, and they often arrive from a merge with shared names or a bad header row.
df.loc[:, ~df.columns.duplicated()] keeps the first of each.
copy index labels have the same problem for row pick. reset_index(drop=True) is the usual fix.
Before a join, always
The single highest-value use of all in this module is the check before a merge:
assert df["id"].is_unique
on whichever side is supposed to be unique.
A copy there times rows silently, inflates each later total. Is invisible in the output. Checking costs one line. merge(..., validate="many_to_one") makes the check part of the step itself.
Duplicates and the index
Row copies and index copies are other problems with other symptoms.
A duplicated index makes .loc[label] return a few rows where code expects one, breaks reindex, and makes some joins raise.
df.index.is_unique checks it. df[df.index.duplicated(keep=False)] shows the offenders. reset_index(drop=True) fixes it when the labels carry no meaning.
Duplicated index labels often arrive from concat without ignore_index=True, or from explode, or from a group-by result that was reshaped. None of them warns.
Fuzzy duplicates
Exact matching finds only the easy cases. Real copy records differ in ways that need judgement:
Whitespace and case — fixed by norming, and worth doing always.
Punctuation — "Ltd" and "Ltd.".
Abbreviations — "Street" and "St".
Transpositions and typos — truly fuzzy.
other formats for the same value. Phone numbers, dates as text.
pandas handles the first two well and the rest not at all. For the rest, the honest options are a scaling function encoding the domain's rules, or a dedicated record-linkage library. What doesn't work is hoping drop_duplicates will catch them.
The hands-on approach: norm aggressively into a split key column, deduplicate on that. Keep the first values. That way the matching is clear and the data isn't damaged.
Counting rather than removing
Before dropping anything, it's worth knowing what the copies represent:
If most keys appear once and a few appear twice, that is probably a data-entry issue. If each key appears just three times, the data has a structure you haven't accounted for. Three records per entity, perhaps one per year. And deduplicating would destroy it.
That gap isn't visible from the copy count alone. It changes the correct action fully.
A summary
duplicated() flags repeats but not the first. keep=False flags each copy.
subset= asks the question that often matters: is the key duplicated?
drop_duplicates keeps the first in current order. Sort first if which lives matters.
Norm case and whitespace before deduplicating.
Repeated rows carrying split data want groupby, not drop_duplicates.
groupby(...).first() merges partial records rather than picking one.
And assert uniqueness before any merge that depends on it. A duplicated key there times rows and inflates each total downstream.
A closing note on Duplicates
The word "copy" hides a few other situations. Choosing the right action depends on which one you've.
An exact repeated row from a double load can be dropped without thought. Two versions of a record need a sort and a rule about which lives. Repeated events carrying split data need aggregating, not dropping. Near-copies from inconsistent text need norming first, or they won't be found at all.
The diagnostic that splits them is checking whole-row copies against key copies. If the key repeats but the row doesn't, a thing differs between the copies. That gap is the thing to look at before deciding anything.
And the highest-value use of all here is the check before a join. A duplicated key times rows, inflates each total downstream. Makes output that looks fully fair. One assertion blocks it.
Two more things worth knowing
drop_duplicates accepts ignore_index=True, which renumbers the result rather than leaving gaps in the index where rows were removed. Without it the surviving labels are the first ones, which is correct and at times surprising when the result is printed.
df.duplicated() checks whole rows including NaN. Two rows with a missing value in the same column do count as copies of each other. Unlike almost all over else in pandas. Here NaN never equals NaN. That inconsistency is deliberate and handy here. Two the same way incomplete rows often are copies.
And for finding copies across a subset of columns while keeping the rest, df.groupby(keys).filter(lambda g. Len(g) > 1) returns each row belonging to a repeated key. This Is the inspection view that keep=False gives more right.
Deduplicating across sources
The hardest version of this problem isn't copies within one table but the same entity appearing in two.
The steps are always the same, and only the third is hard.
Norm both sides into a close key. Case, whitespace, punctuation, and any domain-exact standardisation.
Match on the key, with merge or isin.
Decide what to do with near-matches that the key doesn't unify.
pandas handles the first two well. The third is record linkage. It's a truly open problem for messy data. The same person may appear with a other spelling, a maiden name, a typo'd date of birth.
The pragmatic middle ground is to match just on a normed key, count how many records fail to match. Inspect a sample of those. Often a small number of scaling rules. Drop punctuation, standardise abbreviations. Resolves most of them. The rest are truly ambiguous and better flagged for a human than resolved by code.
Questions people ask about Duplicates
How do I find duplicate rows? df.duplicated() returns True for each repeat after the first. Pass subset=['a', 'b'] to compare only some columns, and keep=False to flag every copy.
How do I remove duplicates? Use df.drop_duplicates(). By default it keeps the first occurrence, and keep='last' keeps the last.
Which duplicate does drop_duplicates keep? The first one in the current row order. Sort first if you want to keep, for example, the most recent record.
Why are rows that look identical not detected as duplicates? Trailing spaces, different capitalisation, or float rounding make values differ. Normalise with str.strip() and str.lower() before checking.
Can I check that a column is unique? Yes. Use df['id'].is_unique, or assert not df.duplicated('id').any() to stop a pipeline when the assumption breaks.
Are duplicates always errors? No. Repeated rows can be real events, like two identical purchases, so decide from what the data means before deleting.
Duplicates, in summary
duplicated flags each copy but the first. This Is what makes it a drop mask. keep=False flags them all. This Is what you want for inspection.
The question that matters is often about the key rather than the whole row, and subset= asks it.
Sort before dropping, or which row lives is any. Norm text first, or the same value counts as a few. And if the repeated rows carry split data, the step you want is an aggregation, not a deletion.
Check yourself
0 of 4
Answer without scrolling back up.
What does `duplicated()` return for the first occurrence of a repeated row?
That asymmetry is what makes df[~df.duplicated()] keep exactly one of each. Use keep=False to flag every copy when inspecting.
Why sort before `drop_duplicates(subset=['id'])`?
A silent error: you asked for one row per id and got one row per id, but which one depends on how the file happened to be written.
Five rows share a customer name but have different amounts. What is usually the right operation?
The rows carry independent information. Reaching for drop_duplicates when you meant groupby is a quiet way to lose most of a dataset.
Why assert `df['id'].is_unique` before a merge?
Ten thousand rows join to eleven thousand and nobody notices. merge(validate='one_to_one') catches it at the join.
Cheat sheet
Duplicates
Find, count and remove duplicate rows in a pandas DataFrame. Choose which copy to keep and verify the cleaned result matches expectations.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.