Guide

Advanced Data Transformation And CleaningDeep dive

Find Duplicate Rows in Excel with Python

Q: How do I see every copy rather than just the repeats?

Pass keep=False to duplicated(), which marks all members of each duplicate group including the first.

Detect exact and near-duplicate rows in a spreadsheet with pandas — key-based duplicates, whitespace and case traps, duplicate groups with counts, and a report you can send back.

Duplicates are the validation problem Excel cannot solve for you: its own validation rules see one cell at a time, so nothing in the workbook can notice that order 2001 appears twice. pandas does it in a line — and then the interesting work starts, because "duplicate" turns out to mean at least three different things. This guide, part of Validating Excel Data with Python, covers all of them.

Prerequisites

Bash

pip install pandas openpyxl

A sample with all three kinds

Python

import pandas as pd

pd.DataFrame([
    {"Order_ID": 2001, "SKU": "A-100", "Region": "North", "Quantity": 4},
    {"Order_ID": 2002, "SKU": "B-200", "Region": "South", "Quantity": 2},
    {"Order_ID": 2001, "SKU": "A-100", "Region": "North", "Quantity": 4},   # exact copy
    {"Order_ID": 2003, "SKU": "C-300", "Region": "West", "Quantity": 7},
    {"Order_ID": 2003, "SKU": "C-300", "Region": "West", "Quantity": 9},    # key clash
    {"Order_ID": 2004, "SKU": "a-100 ", "Region": "north", "Quantity": 4},  # near duplicate
]).to_excel("orders.xlsx", index=False, sheet_name="Orders")

Exact duplicates

duplicated() compares whole rows. keep=False is the important argument — the default marks only the second and later copies, which is what you want for dropping and not what you want for reporting:

Python

import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders")

repeats_only = df[df.duplicated()]                 # the copies
every_copy = df[df.duplicated(keep=False)]         # copies and originals

print(f"{len(repeats_only)} redundant row(s), {len(every_copy)} row(s) involved")
print(every_copy)

Reporting with keep=False lets a reader see both rows and confirm they really are identical. Reporting with the default shows one row of a pair, which invariably prompts the question "duplicate of what?".

Duplicate keys — the important case

An identifier that repeats while other values differ means two records claim to describe the same thing. This is a genuine data conflict, and it is the check most reporting pipelines are missing:

Python

import pandas as pd

KEY = ["Order_ID"]

def key_conflicts(frame, key=KEY):
    dupes = frame[frame.duplicated(subset=key, keep=False)].copy()
    if dupes.empty:
        return dupes.assign(Conflict=None)

    other = [c for c in frame.columns if c not in key]
    # A conflict is a repeated key whose remaining columns are not all identical
    grouped = dupes.groupby(key)[other].nunique()
    conflicted = grouped[(grouped > 1).any(axis=1)].index

    dupes["Conflict"] = dupes[key[0]].isin(conflicted)
    return dupes.sort_values(key)

conflicts = key_conflicts(df)
print(conflicts[["Order_ID", "Region", "Quantity", "Conflict"]])

The nunique step is what separates a harmless double import from a real disagreement: if every non-key column has a single distinct value within the group, the rows are copies. If any column has two, the two rows say different things about order 2003, and no automatic rule can decide which is right.

Normalise before comparing

Exact comparison misses the third kind entirely. Build a comparison key with the same normalisation you would apply anywhere else — trim, case-fold, and coerce numbers — but keep the original values for the report:

Python

import pandas as pd

def normalised_key(frame, columns):
    key = pd.Series("", index=frame.index)
    for column in columns:
        values = frame[column]
        if pd.api.types.is_numeric_dtype(values):
            part = values.round(2).astype(str)
        else:
            part = (values.astype(str).str.strip().str.casefold()
                    .str.replace(r"\s+", " ", regex=True))
        key = key + "␟" + part            # a separator no value will contain
    return key

df["_key"] = normalised_key(df, ["SKU", "Region", "Quantity"])
near = df[df.duplicated(subset="_key", keep=False)].sort_values("_key")
print(near[["Order_ID", "SKU", "Region", "Quantity"]])

casefold rather than lower is deliberate — it handles non-English text correctly, and validation data has a habit of containing a German street name eventually. Rounding numerics before comparison stops 4.0 and 4.000000001 counting as different, which happens whenever a value has been through a floating-point calculation.

Group the duplicates into a report

A flat list of duplicate rows is hard to act on. Grouping them, with counts and the spreadsheet rows involved, is not:

Python

import pandas as pd

def duplicate_report(frame, subset):
    marked = frame[frame.duplicated(subset=subset, keep=False)].copy()
    marked["Sheet_Row"] = marked.index + 2

    report = (
        marked.groupby(subset, dropna=False)
        .agg(Copies=("Sheet_Row", "size"), Rows=("Sheet_Row", lambda s: ", ".join(map(str, s))))
        .reset_index()
        .sort_values("Copies", ascending=False)
    )
    return report

report = duplicate_report(df, ["Order_ID"])
print(report.to_string(index=False))

with pd.ExcelWriter("duplicates.xlsx", engine="openpyxl") as writer:
    df.drop(columns="_key").to_excel(writer, sheet_name="Orders", index=False)
    report.to_excel(writer, sheet_name="Duplicates", index=False)

Rows holding "4, 6" tells the reader exactly where to look, and Copies sorted descending puts the worst offenders at the top. Pair the sheet with highlighting the duplicated cells and the fix becomes obvious without any explanation.

Deciding which copy wins

When a rule genuinely exists, apply it — and log what was dropped:

Python

import pandas as pd

before = len(df)
resolved = (
    df.sort_values(["Order_ID", "Quantity"], ascending=[True, False])
      .drop_duplicates(subset=["Order_ID"], keep="first")     # keep the largest quantity
)
dropped = before - len(resolved)
print(f"kept {len(resolved)} row(s), dropped {dropped} duplicate(s)")

Sorting before drop_duplicates is what turns "keep the first" into a real rule — here, keep the largest quantity per order. Without the sort, "first" means "whichever row happened to be higher in the file", which is not a rule at all. Whenever the choice is arbitrary, resist the temptation: report the group and let the data owner decide, because a silently discarded row is impossible to notice later. The drop duplicates from an Excel column guide goes through the resolution strategies in more detail.

Where duplicates come from

Knowing the source usually decides the fix, and there are only a few common origins:

The bottom-left box is the only one that cannot be solved in code alone. When two systems disagree about the same order, choosing a winner is a business decision — and encoding that decision as "keep the row from system A" is fine, provided it is written down somewhere other than a sort key buried in a script.

Common pitfalls and gotchas

Symptom	Cause	Fix
Obvious duplicates not detected	Whitespace, case or float differences	Compare on a normalised key
Only half of each pair reported	Default `keep="first"`	Use `keep=False` for reporting
Every row flagged as duplicate	Compared on too few columns	Include the columns that make a row unique
Duplicates reappear next run	Source system re-sends the same rows	Deduplicate on a stable business key, not on load order
Wrong copy kept	`drop_duplicates` without a deliberate sort	Sort by the tie-breaking column first
Blank keys grouped together	`NaN` treated as a value with `dropna=False`	Validate that the key is present before grouping

Check for duplicates before you join

The most expensive duplicate is the one nobody looked for before a merge. Joining a 500-row orders table to a customer table whose key repeats three times produces 1,500 rows and a revenue figure three times too large — and the merge itself raises nothing. Assert uniqueness on the side that is supposed to be unique, and let pandas do the checking:

Python

import pandas as pd

orders = pd.read_excel("orders.xlsx", sheet_name="Orders")
customers = pd.read_excel("customers.xlsx")

# validate="m:1" raises if the right-hand key is not unique
enriched = orders.merge(customers, on="Customer_ID", how="left", validate="m:1")
print(len(orders), "->", len(enriched))

validate="m:1" turns a silent row explosion into an immediate MergeError, which is exactly the trade you want: a job that fails on Monday morning costs an hour, and a report that overstates revenue by a factor of three costs considerably more. The same argument accepts "1:1" and "1:m" for the other shapes, and it is worth adding to every merge in a reporting pipeline rather than only to the ones that have already burned you.

Performance and scale notes

duplicated() hashes rows, so it is roughly linear and fast even on hundreds of thousands of rows. Building a normalised key with string operations is the expensive part — several passes over the column — so only normalise the columns that form the key rather than the whole frame. The groupby report is cheap by comparison. On very large sheets, deduplicate on a narrow key first and only then compare the full rows within the small set of groups that survived, which avoids building a long concatenated key for every row in the file.

Conclusion

"Duplicate" means three different things, and only one of them is safe to fix automatically. Use duplicated(keep=False) to report, compare on a normalised key so near-duplicates cannot hide, separate repeated keys with conflicting data from honest copies with nunique, and group the results with counts and sheet-row numbers. Delete only where a deliberate sort expresses a real rule about which copy wins.

Frequently asked questions

What is the difference between a duplicate row and a duplicate key? A duplicate row repeats every column, and is usually a copy-paste or a double import. A duplicate key repeats an identifier while other columns differ, which means two records disagree about the same thing — a data problem, not a copy.

Why does duplicated() miss obvious duplicates? It compares values exactly. Trailing spaces, different capitalisation, a number stored as text and a float rounding difference all make two visually identical rows unequal.

How do I see every copy rather than just the repeats? Pass keep=False to duplicated(), which marks all members of each duplicate group including the first.

Should I delete duplicates automatically? Only when the rule for which copy wins is unambiguous. Otherwise report the groups and let the data owner decide, because deleting the wrong copy is invisible afterwards.

Up to the parent guide:

Validating Excel Data with Python — the cross-row checks Excel cannot perform.

Related guides:

Drop Duplicates from an Excel Column with pandas — the resolution side, in depth.
Compare Two Excel Files for Differences with Python — duplicates across files rather than within one.
Highlight Invalid Cells in Excel with Python — showing the duplicate rows in the workbook.
Merging and Joining Excel DataFrames — where an undetected duplicate key multiplies rows.