Pandas DataFrame.duplicated(): Detect and Review Repeated Records
Use df.duplicated(subset=None, keep="first") to identify repeated rows. Choose the fields that define a duplicate, inspect every member when necessary and review conflicting or missing keys before removing records.
Create the student practice table 🔝
Rows at positions five and six repeat earlier complete records. Different students sharing a class are not automatically duplicate people. Keep an unchanged source table for independent checks.
import pandas as pd
def student_data():
return pd.DataFrame({
"id": [1, 2, 3, 4, 5, 4, 2],
"name": ["John", "Max", "Arnold", "Krish", "John", "Krish", "Max"],
"class1": ["Four", "Three", "Three", "Four", "Four", "Four", "Three"],
"mark": [75, 85, 55, 60, 60, 60, 85],
"gender": ["female", "male", "male", "female", "female", "female", "male"]
})
df = student_data()
print(df)Expected output
id name class1 mark gender
0 1 John Four 75 female
1 2 Max Three 85 male
2 3 Arnold Three 55 male
3 4 Krish Four 60 female
4 5 John Four 60 female
5 4 Krish Four 60 female
6 2 Max Three 85 maleDetect repeated full rows 🔝
By default, all columns are compared and the first occurrence is marked False. The result is a Boolean Series; it does not remove rows.
df = student_data()
mask = df.duplicated()
print(mask)
print("Repeated rows after the first:", int(mask.sum()))
assert mask.tolist() == [False, False, False, False, False, True, True]Expected output
0 False
1 False
2 False
3 False
4 False
5 True
6 True
dtype: bool
Repeated rows after the first: 2Add a status column without changing the comparison 🔝
Compute the mask before adding an audit column. Later comparisons across all columns could include that new column and change the definition of an identical row. Keep an explicit subset for repeatable checks.
df = student_data()
source_columns = df.columns.tolist()
df["status"] = df.duplicated(subset=source_columns)
print(df)
assert df.duplicated(subset=source_columns).sum() == 2Expected output
id name class1 mark gender status
0 1 John Four 75 female False
1 2 Max Three 85 male False
2 3 Arnold Three 55 male False
3 4 Krish Four 60 female False
4 5 John Four 60 female False
5 4 Krish Four 60 female True
6 2 Max Three 85 male TrueCompare first, last and False 🔝
keep="first" marks repetitions after the first; keep="last" marks repetitions before the last; keep=False marks every member of a repeated group. Use the Boolean False, not the string "False". First and last refer to current row order.
df = student_data()
for policy in ["first", "last", False]:
print("keep =", policy)
print(df.loc[df.duplicated(keep=policy)])
assert df.index[df.duplicated(keep="last")].tolist() == [1, 3]
assert df.index[df.duplicated(keep=False)].tolist() == [1, 3, 5, 6]Expected output
keep = first
id name class1 mark gender
5 4 Krish Four 60 female
6 2 Max Three 85 male
keep = last
id name class1 mark gender
1 2 Max Three 85 male
3 4 Krish Four 60 female
keep = False
id name class1 mark gender
1 2 Max Three 85 male
3 4 Krish Four 60 female
5 4 Krish Four 60 female
6 2 Max Three 85 maleRetain one representative or find singleton rows 🔝
Negating the default mask retains one representative per group, including repeated groups. Negating keep=False selects rows whose values occur only once. These are different meanings of unique.
df = student_data()
representatives = df.loc[~df.duplicated()].copy()
singletons = df.loc[~df.duplicated(keep=False)].copy()
print("One representative per group:")
print(representatives)
print("Rows occurring exactly once:")
print(singletons)
assert len(representatives) == 5 and len(singletons) == 3Expected output
One representative per group:
id name class1 mark gender
0 1 John Four 75 female
1 2 Max Three 85 male
2 3 Arnold Three 55 male
3 4 Krish Four 60 female
4 5 John Four 60 female
Rows occurring exactly once:
id name class1 mark gender
0 1 John Four 75 female
2 3 Arnold Three 55 male
4 5 John Four 60 femaleCompare one column with subset 🔝
subset changes the fields used for comparison. Repeated class values are useful for category inspection, but not a sufficient rule for deleting student records.
df = student_data()
print(df.loc[df.duplicated(subset=["class1"], keep="last")])
print("First row for each class:")
print(df.loc[~df["class1"].duplicated(keep="first")])
assert df.index[df.duplicated(subset=["class1"], keep="last")].tolist() == [0, 1, 2, 3, 4]Expected output
id name class1 mark gender
0 1 John Four 75 female
1 2 Max Three 85 male
2 3 Arnold Three 55 male
3 4 Krish Four 60 female
4 5 John Four 60 female
First row for each class:
id name class1 mark gender
0 1 John Four 75 female
1 2 Max Three 85 maleCompare several columns 🔝
Choose fields that express the intended equivalence. The first example compares class and gender; the second compares class and mark. A matching class and mark still does not prove that two students are the same person.
df = student_data()
print("Repeated class and gender, keeping last:")
print(df.loc[df.duplicated(subset=["class1", "gender"], keep="last")])
print("Repeated class and mark, keeping first:")
print(df.loc[df[["class1", "mark"]].duplicated()])
assert df.index[df.duplicated(subset=["class1", "mark"])].tolist() == [4, 5, 6]Expected output
Repeated class and gender, keeping last:
id name class1 mark gender
0 1 John Four 75 female
1 2 Max Three 85 male
2 3 Arnold Three 55 male
3 4 Krish Four 60 female
4 5 John Four 60 female
Repeated class and mark, keeping first:
id name class1 mark gender
4 5 John Four 60 female
5 4 Krish Four 60 female
6 2 Max Three 85 maleCheck repeated index labels separately 🔝
DataFrame.duplicated() compares column values and ignores index labels. Use index.duplicated() to inspect repeated labels. See row selection when choosing between labels and positions.
table = pd.DataFrame({"value": ["A", "B", "A"]}, index=[10, 10, 20])
print("Repeated row values:", table.duplicated().tolist())
print("Repeated index labels:", table.index.duplicated().tolist())
assert table.duplicated().tolist() == [False, False, True]
assert table.index.duplicated().tolist() == [False, True, False]Expected output
Repeated row values: [False, False, True]
Repeated index labels: [False, True, False]Review missing keys before deduplication 🔝
Repeated missing values can be flagged as duplicates. Two unknown IDs do not establish that the rows belong to the same entity. Separate incomplete keys for review instead of automatically removing them.
table = pd.DataFrame({"id": pd.Series(["A", "A", None, None], dtype="string"), "amount": [5, 5, 7, 8]})
print(table.duplicated(subset=["id"], keep=False))
missing_key = table["id"].isna()
print("Missing-key rows:")
print(table.loc[missing_key])
assert missing_key.sum() == 2Expected output
0 True
1 True
2 True
3 True
dtype: bool
Missing-key rows:
id amount
2 <NA> 7
3 <NA> 8Practical workflow: audit duplicate order IDs 🔝
This synthetic extract has one exact repeated order, one conflicting repeated ID and one missing ID. Use the order ID as the business key, inspect all repeated members, and check conflicting product or amount values before removal.
orders = pd.DataFrame({"order_id": pd.Series(["001", "001", "002", "002", "003", None], dtype="string"), "product": ["Pen", "Pen", "Book", "Book", "Mug", "Pen"], "amount": [5, 5, 10, 12, 8, 3]})
known = orders.loc[orders["order_id"].notna()].copy()
repeated = known.duplicated(subset=["order_id"], keep=False)
print("All repeated-key rows:")
print(known.loc[repeated])
conflict_flags = known.groupby("order_id")[["product", "amount"]].nunique(dropna=False).gt(1).any(axis=1)
conflicting_ids = conflict_flags.index[conflict_flags]
print("Conflicting IDs:", conflicting_ids.tolist())
assert conflicting_ids.tolist() == ["002"]Expected output
All repeated-key rows:
order_id product amount
0 001 Pen 5
1 001 Pen 5
2 002 Book 10
3 002 Book 12
Conflicting IDs: ['002']Separate safe repetitions from unresolved records 🔝
Remove repetitions only from groups with complete keys and consistent payloads. Retain missing-key and conflicting records in a review table. This policy assumes identical repeated orders are duplicate imports; another application may use a different rule. See drop_duplicates().
needs_review = orders["order_id"].isna() | orders["order_id"].isin(conflicting_ids)
review = orders.loc[needs_review].copy()
safe = orders.loc[~needs_review].copy()
removed = safe.loc[safe.duplicated(subset=["order_id"], keep="first")].copy()
clean = safe.drop_duplicates(subset=["order_id"], keep="first").copy()
print("Clean records:")
print(clean)
print("Review records:")
print(review)
print("Safe repetitions removed:", len(removed))
assert clean["order_id"].tolist() == ["001", "003"]
assert len(review) == 3 and len(removed) == 1Expected output
Clean records:
order_id product amount
0 001 Pen 5
4 003 Mug 8
Review records:
order_id product amount
2 002 Book 10
3 002 Book 12
5 <NA> Pen 3
Safe repetitions removed: 1Validate counts and retain the audit trail 🔝
Check that every input row is accounted for as retained, removed or requiring review. Preserve identifiers as text for leading zeros. See identifier formatting and Excel export.
assert len(orders) == len(clean) + len(removed) + len(review)
assert clean["order_id"].notna().all()
assert not clean.duplicated(subset=["order_id"]).any()
print("Input:", len(orders), "Clean:", len(clean), "Removed:", len(removed), "Review:", len(review))
print(clean.to_csv(index=False).rstrip())
# Optional Colab exports:
# clean.to_csv("clean_orders.csv", index=False)
# review.to_csv("orders_for_review.csv", index=False)Expected output
Input: 6 Clean: 2 Removed: 1 Review: 3
order_id,product,amount
001,Pen,5
003,Mug,8Normalise text only when the key specification allows it 🔝
Whitespace or case variants can hide repeated text keys. Keep the raw text and compare a normalised helper column only when the application considers these variants equivalent. Normalisation may create collisions; inspect them. See case conversion.
customers = pd.DataFrame({"code": pd.Series([" AB12 ", "ab12", "CD34"], dtype="string")})
customers["comparison_key"] = customers["code"].str.strip().str.casefold()
print(customers.loc[customers.duplicated(subset=["comparison_key"], keep=False)])
assert customers.duplicated(subset=["comparison_key"]).sum() == 1Expected output
code comparison_key
0 AB12 ab12
1 ab12 ab12Common questions 🔝
Does duplicated() delete rows? No; it returns a mask. Why does keep=False flag the first row too? It marks every member of each repeated group. Does keep="last" mean the newest record? Only if the row order reflects that policy; sort and define tie handling first. Why did my duplicate flags change after adding status? All-column comparison now includes status; specify the original fields. Can list-valued fields be compared directly? The subset must contain hashable values; normalise complex structures deliberately before checking.
Use the same masks in a desktop display 🔝
To display all, repeated or representative rows in a desktop interface, apply the masks before populating the widget. Related tutorials: dynamic Treeview columns and radio-button selection. The Pandas notebook runs in Colab; a native Tkinter window requires a desktop Python environment.
Exercise: compare representatives and singletons 🔝
Create keys A, A, B and C with values 5, 5, 7 and 8. Count repetitions after the first, all members of repeated groups, one representative per full row, and rows occurring only once. Expect 1, 2, 3 and 2 respectively.
Exercise solution 🔝
Use the same unchanged table for each mask so audit columns do not alter the comparison.
practice = pd.DataFrame({"key": ["A", "A", "B", "C"], "value": [5, 5, 7, 8]})
print("Repetitions after first:", int(practice.duplicated().sum()))
print("Members of repeated groups:", int(practice.duplicated(keep=False).sum()))
print("Representatives:", int((~practice.duplicated()).sum()))
print("Singletons:", int((~practice.duplicated(keep=False)).sum()))
assert practice.duplicated().sum() == 1
assert (~practice.duplicated(keep=False)).sum() == 2Expected output
Repetitions after first: 1
Members of repeated groups: 2
Representatives: 3
Singletons: 2Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change comparison columns, keep policies and conflicting order values, then inspect each mask. Save your own copy to keep edits. All sample tables are included in the notebook.
Continue with the Data Cleaning hub, DataFrame.drop_duplicates(), Series.duplicated() and Series.drop_duplicates().
Reference: Pandas DataFrame.duplicated documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials