Python Doc & Data Automation

Remove Duplicate Rows in pandas

drop_duplicates() looks like a one-liner and quietly does the wrong thing on real extracted data:

>>> len(frame)
12_480
>>> len(frame.drop_duplicates())
12_480        # nothing removed, yet the report double-counts

The rows are duplicates in every sense a human cares about. They differ by a trailing space in a supplier name, by a timestamp captured at extraction time, or by case — and exact comparison treats each difference as a distinct record.

The opposite failure is worse:

>>> len(frame.drop_duplicates(subset=["invoice_no"]))
9_204         # 3,276 rows gone — including legitimate multi-line invoices

Root Cause

drop_duplicates compares whole rows by exact value across every column, unless told otherwise. Two things break that.

Columns that are not part of a record's identity — an extraction timestamp, a source filename, a row number — differ on every copy, so no two rows are ever equal. And values that are part of the identity carry incidental variation: Acme Ltd, acme ltd and Acme Ltd are three distinct strings and one supplier.

The over-deletion case is the mirror image: choosing a subset that is not actually unique. An invoice number is unique per invoice, not per line item, so deduplicating on it alone collapses every multi-line invoice to its first row.

What counts as a duplicate depends on the comparison Four rows for the same invoice differ in supplier name spacing and case and in an extraction timestamp column. Under exact whole row matching none are duplicates, so nothing is removed. Under normalised matching on the identity columns, three of the four collapse into one. Under matching on the invoice number alone, all four collapse, which wrongly removes a legitimate second line item. Same data, three definitions of "duplicate" invoice_no supplier line extracted_at 100417 Acme Ltd 1 09:14 100417 acme ltd 1 09:22 100417 Acme Ltd 1 09:31 100417 Acme Ltd 2 09:14 exact, all columns: 0 removed extracted_at differs on every row normalised on identity: 2 removed line 1 kept once, line 2 kept invoice_no only: 3 removed line 2 lost — a real record Define identity explicitly: which columns make two rows the same fact? Everything else is metadata and must be excluded from the comparison

Minimal Diagnostic

Count duplicates under several definitions before removing anything.

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

CSV = Path("data/extracted_invoices.csv")

def duplicate_report(frame: pd.DataFrame, identity: list[str]) -> pd.DataFrame:
    """Compare duplicate counts under exact, subset and normalised matching."""
    missing = [column for column in identity if column not in frame.columns]
    if missing:
        raise KeyError(f"identity column(s) not in the frame: {missing}")

    normalised = frame[identity].apply(
        lambda s: s.astype(str).str.strip().str.casefold() if s.dtype == object else s
    )
    return pd.DataFrame([
        {"definition": "exact, all columns", "duplicates": int(frame.duplicated().sum())},
        {"definition": f"subset {identity}", "duplicates": int(frame.duplicated(subset=identity).sum())},
        {"definition": "normalised subset", "duplicates": int(normalised.duplicated().sum())},
    ])

if __name__ == "__main__":
    data = pd.read_csv(CSV)
    print(duplicate_report(data, ["invoice_no", "line_no"]).to_string(index=False))

        definition  duplicates
exact, all columns           0
 subset ['invoice_no', 'line_no']         412
 normalised subset         1,208

The gap between the second and third rows is the whitespace-and-case problem, quantified. The gap between the first and second is the metadata problem.

The Fix: Normalise, Then Deduplicate on Identity

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

IDENTITY = ["invoice_no", "line_no"]
METADATA = ["extracted_at", "source_file", "_row"]

def normalise_keys(frame: pd.DataFrame, columns: list[str]) -> pd.DataFrame:
    """Add normalised comparison keys without altering the original values."""
    out = frame.copy()
    for column in columns:
        if out[column].dtype == object:
            out[f"_key_{column}"] = (
                out[column].astype(str)
                .str.replace(" ", " ", regex=False)   # non-breaking space from Excel
                .str.strip()
                .str.replace(r"\s+", " ", regex=True)
                .str.casefold()
            )
        else:
            out[f"_key_{column}"] = out[column]
    return out

def deduplicate(frame: pd.DataFrame, identity: list[str],
                keep: str = "last", sort_by: str | None = None
                ) -> tuple[pd.DataFrame, pd.DataFrame]:
    """Return (unique rows, removed rows) matching on normalised identity columns."""
    working = normalise_keys(frame, identity)
    if sort_by:
        working = working.sort_values(sort_by)      # decide WHICH copy 'last' means

    keys = [f"_key_{column}" for column in identity]
    is_duplicate = working.duplicated(subset=keys, keep=keep)

    removed = working.loc[is_duplicate].drop(columns=keys)
    kept = working.loc[~is_duplicate].drop(columns=keys)
    return kept.reset_index(drop=True), removed.reset_index(drop=True)

if __name__ == "__main__":
    data = pd.read_csv(Path("data/extracted_invoices.csv"))
    unique, dropped = deduplicate(data, IDENTITY, keep="last", sort_by="extracted_at")
    print(f"{len(unique):,} kept, {len(dropped):,} removed")
    if not dropped.empty:
        dropped.to_csv("out/quarantine/duplicates.csv", index=False)

Three decisions are explicit here rather than implicit. The comparison keys are added rather than applied in place, so the original values — the ones a human will read — keep their real spelling. Sorting before deduplicating makes keep="last" mean something specific: the most recently extracted copy, not whichever the file happened to list last. And the removed rows are returned rather than discarded, so the count can be reconciled and the evidence kept — the same discipline as quarantine invalid rows in a pipeline.

Variant: Keeping the Most Complete Copy

keep="first" and keep="last" are positional. When duplicates differ in completeness — one copy has a supplier reference and the other does not — keep the richest.

# pip install "pandas>=2.2,<3"
import pandas as pd

def keep_most_complete(frame: pd.DataFrame, identity: list[str]) -> pd.DataFrame:
    """Within each duplicate group, keep the row with the fewest missing values."""
    scored = frame.assign(_filled=frame.notna().sum(axis=1))
    return (
        scored.sort_values("_filled", ascending=False)
        .drop_duplicates(subset=identity, keep="first")
        .drop(columns="_filled")
        .sort_index()
    )

For duplicates that are complementary rather than redundant — each copy holding a different subset of the fields — merge them instead of choosing:

# pip install "pandas>=2.2,<3"
def coalesce_duplicates(frame: pd.DataFrame, identity: list[str]) -> pd.DataFrame:
    """Combine duplicate rows field by field, taking the first non-null of each."""
    return (
        frame.groupby(identity, as_index=False, dropna=False)
        .agg(lambda series: series.dropna().iloc[0] if series.notna().any() else pd.NA)
    )

Variant: Near-Duplicates That Normalisation Misses

Acme Ltd against Acme Limited, or Gamma Partners LLP against Gamma Partners L.L.P., survive case-folding. Handle them with a canonical form rather than fuzzy scoring, which is slow and unpredictable at scale.

# pip install "pandas>=2.2,<3"
import re

SUFFIXES = {
    r"\blimited\b": "ltd",
    r"\bincorporated\b": "inc",
    r"\bcompany\b": "co",
    r"\bpublic limited company\b": "plc",
}

def canonical_name(series: pd.Series) -> pd.Series:
    """Reduce a company name to a comparison form: no punctuation, standard suffixes."""
    out = series.astype(str).str.casefold().str.replace(r"[.,'()]", "", regex=True)
    for pattern, replacement in SUFFIXES.items():
        out = out.str.replace(pattern, replacement, regex=True)
    return out.str.replace(r"\s+", " ", regex=True).str.strip()

if __name__ == "__main__":
    names = pd.Series(["Acme Limited", "ACME LTD.", "Acme  Ltd", "Beta Group"])
    print(canonical_name(names).value_counts())
    # acme ltd     3
    # beta group   1

Keep the canonical form as a separate column, never as a replacement. The moment a canonicalisation rule is wrong — and eventually one will be — you need the original to recover from it.

Variant: Duplicates Introduced by a Merge

A row count that grows after a join means the join key is not unique on one side. That is a fan-out, not a duplicate, and deduplicating afterwards hides the real problem.

# pip install "pandas>=2.2,<3"
merged = invoices.merge(suppliers, on="supplier_id", how="left", validate="many_to_one")
# raises MergeError if suppliers has repeated supplier_id values

validate= is the cheapest guard in pandas: it turns a silent row explosion into an exception naming the offending relationship. The related column-name collisions are covered in fix pandas merge overlapping columns.

Choosing the right deduplication Starting from apparent duplicates, the first question is whether the count grew after a join, in which case the cause is a fan-out and the fix belongs upstream with a validate argument. Otherwise, if the copies differ only in whitespace or case, normalised comparison keys are enough. If they differ in spelling such as Limited against Ltd, a canonical form is needed. If they are byte identical, plain drop_duplicates on the identity subset suffices. Apparent duplicates after a join? yes Fan-out, not duplication fix the join with validate= no Whitespace or case only normalised comparison keys Spelling differs canonical form, kept as its own column

Verification

Assert that the identity is genuinely unique afterwards, and that the drop was proportionate.

# pip install "pandas>=2.2,<3"
import pandas as pd

def assert_deduplicated(before: pd.DataFrame, after: pd.DataFrame, removed: pd.DataFrame,
                        identity: list[str], max_removed_pct: float = 20.0) -> None:
    """Fail on residual duplicates, an unaccounted row, or an implausible drop."""
    remaining = after.duplicated(subset=identity).sum()
    assert remaining == 0, f"{remaining} duplicate(s) still present on {identity}"

    assert len(after) + len(removed) == len(before), (
        f"{len(before):,} in, {len(after):,} kept + {len(removed):,} removed "
        f"= {len(after) + len(removed):,}"
    )

    pct = len(removed) / max(len(before), 1) * 100
    assert pct <= max_removed_pct, (
        f"{pct:.1f}% of rows removed as duplicates — the identity subset may be too narrow"
    )

    for column in identity:
        assert after[column].notna().all(), f"{column}: null values in an identity column"
    print(f"{len(after):,} unique row(s), {len(removed):,} removed ({pct:.1f}%)")

if __name__ == "__main__":
    assert_deduplicated(data, unique, dropped, IDENTITY)

The percentage ceiling is what catches the over-deletion this page opens with. A deduplication that removes a quarter of the rows is far more likely to have the wrong identity subset than to have found a quarter-duplicate dataset — and the null check on identity columns catches the related trap, where NaN keys compare unequal and duplicates survive silently.

How the identity subset changes the outcome Four definitions applied to the same twelve thousand four hundred and eighty row frame. All columns exactly removes nothing. Invoice number plus line number removes four hundred and twelve. The same pair normalised for case and whitespace removes one thousand two hundred and eight. Invoice number alone removes three thousand two hundred and seventy six, which is far too many and indicates the wrong identity. Rows removed from 12,480, by identity definition all columns, exact 0 — metadata differs invoice + line 412 + normalised 1,208 — the right answer invoice only 3,276 — multi-line invoices destroyed

FAQ

Does drop_duplicates keep the first occurrence by default? Yes, keep="first". That is positional, so sort deliberately first if "first" is meant to carry meaning such as earliest or most complete.

Why do rows with missing keys never deduplicate?NaN does not equal NaN, so two rows with a null identity column are always distinct. Fill or exclude nulls in the identity columns before comparing.

Is groupby().first() an alternative? It deduplicates and aggregates in one step, but it also reorders and drops the index. drop_duplicates is clearer when you only want to remove rows; use groupby when you genuinely want to combine them.

How slow is deduplication on a large frame?drop_duplicates hashes the subset, so it is roughly linear — a few seconds on ten million rows. Building normalised string keys costs more than the deduplication itself, so restrict normalisation to the identity columns.

Should duplicates be removed before or after validation? After. Validate first so that a row failing on type is quarantined with its reason, then deduplicate the survivors — otherwise a bad row can be the one copy kept. The order is set out in validating document data with schemas.

Deduplicating Across Runs, Not Just Within One

Everything above deduplicates a single frame. A pipeline that ingests a fresh extract every day has a second problem: the same record arriving on consecutive days, which no within-frame check will catch.

The usual answer is a small persisted set of identity hashes with the run that first saw each one. Before appending, hash the identity columns of the incoming rows and drop those already present. That also gives a useful signal for free — a day where ninety per cent of rows are already known usually means the source exported the wrong date range rather than that nothing happened.

Part of Cleaning Messy CSV Data with pandas.