How to remove duplicates using a defined key in Pandas (with Code)

The Short Answer (TL;DR)

Pass the columns that define a record to drop_duplicates(subset=…), and choose the retained occurrence deliberately. When the policy is to keep the latest update, sort by a valid timestamp and an explicit tie-breaker first, and retain an audit of discarded and unresolved records.

import pandas as pd
df = pd.DataFrame({"account":["A","A","B"],"revision":[1,2,1],"value":[10,12,20]})
print(df.sort_values("revision").drop_duplicates(subset=["account"],keep="last"))

Detailed Implementation

This example uses synthetic data and does not need a network connection. Install the recorded package versions in a separate Python environment before running it.

python -m pip install numpy==2.5.4 pandas==3.0.6

from pathlib import Path
import numpy as np
import pandas as pd

def select_latest(frame):
    keys = ["account", "period"]
    required = set(keys + ["updated", "ingest_seq"])
    if not frame.columns.is_unique or not required.issubset(frame.columns):
        raise ValueError("Require unique columns and all record-selection fields.")
    if "_updated" in frame.columns:
        raise ValueError("The temporary name _updated is already in use.")
    sequence = frame["ingest_seq"]
    if (not pd.api.types.is_integer_dtype(sequence) or pd.api.types.is_bool_dtype(sequence)
            or sequence.isna().any() or not sequence.is_unique):
        raise ValueError("ingest_seq must contain unique, nonmissing integers.")
    for key in keys:
        values = frame[key].dropna()
        if not all(isinstance(x, str) and x.strip() for x in values):
            raise ValueError("Nonmissing record keys must be nonblank strings.")
    updated = pd.to_datetime(frame["updated"],format="%Y-%m-%d %H:%M",utc=True,errors="raise")
    if updated.isna().any():
        raise ValueError("Every update needs a known UTC timestamp.")
    unresolved = frame[keys].isna().any(axis=1)
    quarantine = frame.loc[unresolved].copy()
    ranked = frame.loc[~unresolved].assign(_updated=updated.loc[~unresolved])
    # A unique ingestion sequence makes equal timestamps explicitly ordered.
    ranked = ranked.sort_values(["_updated", "ingest_seq"])
    kept = ranked.drop_duplicates(subset=keys,keep="last").drop(columns="_updated")
    kept = kept.sort_values("ingest_seq")
    removed = frame.loc[~unresolved & ~sequence.isin(kept["ingest_seq"])].copy()
    return kept, removed, quarantine

# Synthetic account-period revisions. Index labels deliberately repeat.
data = pd.DataFrame({
    "account":["A","A","B","B","C",None,"A","C"],
    "period":["2026-01"]*6+["2026-02","2026-01"],
    "updated":["2026-11-01 09:00","2026-11-02 09:00","2026-11-01 09:00",
               "2026-11-01 09:00","2026-11-03 09:00","2026-11-04 09:00",
               "2026-11-01 09:00","2026-11-02 09:00"],
    "ingest_seq":np.arange(8), "amount":[10,12,20,22,30,99,40,31],
},index=[7,7,2,4,4,9,1,3])
kept, removed, quarantine = select_latest(data)
report = ("KEPT\n"+kept.to_string(index=True)+"\n\nREMOVED\n"+
          removed.to_string(index=True)+"\n\nUNRESOLVED KEY\n"+
          quarantine.to_string(index=True)+
          f"\ninput={len(data)}; kept={len(kept)}; removed={len(removed)}; unresolved={len(quarantine)}\n")
Path("defined-key-deduplication.txt").write_text(report,encoding="utf-8")
print(report)

The selected ingestion sequences are 1, 3, 4 and 6, with amounts 12, 22, 30 and 40. Three older or lower-ranked revisions are removed and one missing-key record is quarantined; 8=4+3+1.

Which columns define a duplicate?

The example defines one record by the pair account and period. Two rows for account A in different periods are separate records. Amount, update time and index label do not define identity here. The same key can have conflicting amounts because the rows represent revisions. Confirm that contract with the source system before discarding records; identical-looking business keys can also describe legitimate repeated events.

Which occurrence wins when values conflict?

The rule is latest UTC update first, with the larger ingestion sequence winning an equal timestamp. The full example sorts in ascending order and keeps the last occurrence. Its unique, nonmissing integer sequence supplies a final tie-breaker, so shuffling the input does not change the selected records. A timestamp tie for B keeps amount 22. For C, amount 30 wins over 31 because its update is later. This is an explicit policy, not evidence that the chosen amount is factually correct.

What happens to missing keys and duplicate index labels?

A missing account or period is quarantined because this example cannot establish that two unidentified rows belong to the same record. Nonmissing keys must be nonblank strings; the helper does not silently trim or change their case. Original index labels are preserved, including the repeated label 4 among retained rows. DataFrame duplicate removal uses the named key columns, so a repeated index label alone is not a reason to delete a record.

How can the removal be audited?

The returned tables separate four selected records, three removed revisions and one unresolved key. The unique ingestion sequence connects every output row to its original source record. The source frame is unchanged, and the partition accounts for all eight input rows. Keep the removed and quarantined tables with the selection policy when a downstream report needs a trace of exclusions. The text file contains the complete synthetic audit, not a count without supporting rows.

Why can keep=False or arbitrary first-row selection change the answer?

Keeping the first row without ordering makes file order part of the policy. That can retain an older revision after exports are concatenated or reordered. keep=False removes every row belonging to a repeated key, including a valid latest revision, so it answers a different question. Neither option resolves conflicting source values automatically. In a lookup table used for joining, verify that the selected result has unique join keys and audit unmatched records separately.

Which validation and scale limits remain?

Every update must parse to a nonmissing UTC timestamp using the stated format, and ingestion sequences must be unique integers. The helper rejects ambiguous output-name collisions and incomplete schemas. Keys are simple strings, while nested unhashable key objects need a different representation. Sorting the full eligible table requires memory for that table and intermediate results. Running the same rule separately on CSV chunks would miss revisions that occur in different chunks.

Mathematical Context

Partition the input into eligible rows with complete keys and quarantined rows. If an eligible key k has m_k revisions, the rule retains one and removes m_k-1. Here four eligible keys contain seven rows, so three revisions are removed; the eighth row is quarantined. Thus N=K+D+Q=4+3+1=8. Selection changes the analysis population: the retained amount sum 104 is a sum of selected revisions, not a correction justified by a statistical estimator. Sorting commonly costs on the order of N log N comparisons, while exact runtime and memory depend on the data and implementation.

\[ N=K+D+Q,\qquad D=\sum_k(m_k-1) \]

Reproducibility

Executed with Python 3.12.14; numpy 2.5.4, pandas 3.0.6. Automated checks confirm the stated output properties.

Primary documentation

• Pandas drop_duplicates: subset and retention policy
• Pandas sort_values: explicit ordering columns
• Pandas duplicated: audit duplicate keys

Related guides

• Validate the cardinality of a lookup merge
• Audit observed counts with named aggregations

For files that do not fit comfortably in memory, read a large CSV in chunks while keeping the same key and duplicate-resolution policy across chunk boundaries.