Pandas duplicated: choose the business key before the method

Define what one row represents and which fields identify that entity before using duplicated or drop_duplicates. Repeated customers are normal in an order table. Repeated order IDs may represent an identical replay, an update or a conflict, depending on the source contract.

The pandas method can identify repeated values. It cannot decide which business event should exist.

Inspect the repeated population

The pandas quality lab has nine raw rows and eight unique order IDs. P03 appears twice with identical field values.

import pandas as pd
from build_and_verify import ROOT, load_orders

raw = pd.read_csv(ROOT / 'orders_raw.csv', dtype='string', keep_default_na=False)
repeated_keys = raw.loc[raw.duplicated('order_id', keep=False)]
assert len(raw) == 9
assert repeated_keys['order_id'].tolist() == ['P03', 'P03']
assert raw.duplicated().sum() == 1
clean = load_orders()
assert len(clean) == 8 and clean['order_id'].is_unique
assert clean.loc[clean['customer_id'].eq('C01'), 'order_id'].tolist() == ['P01', 'P02']
print('One identical replay removed; legitimate repeat customers retained')

keep=False marks every member of the repeated-key group, which is useful for investigation. The full-row duplicate check identifies the extra identical replay under this fixture's schema.

The duplicated reference explains subset and keep. The drop_duplicates reference describes removal behavior; neither supplies a conflict-resolution policy.

Separate identical replays from conflicts

The lab first removes identical full rows, then checks whether order_id remains duplicated. If it does, different versions share the same business key and the loader fails.

import pandas as pd
from pathlib import Path
from tempfile import TemporaryDirectory
from build_and_verify import ROOT, load_orders

raw = pd.read_csv(ROOT / 'orders_raw.csv', dtype='string', keep_default_na=False)
changed = raw.iloc[[0]].copy()
changed.loc[:, 'amount_paise'] = '99999'
conflict = pd.concat([raw, changed], ignore_index=True)
with TemporaryDirectory() as directory:
    path = Path(directory) / 'conflicting_orders.csv'
    conflict.to_csv(path, index=False)
    try:
        load_orders(path)
    except ValueError as error:
        assert str(error) == 'conflicting_order_key'
    else:
        raise AssertionError('Conflicting order version accepted')
print('A changed amount under the same order key is rejected')

Selecting keep="last" would make input order choose the winner. That can be legitimate for an explicitly ordered change stream, but this fixture has no version or effective-time field authorizing that choice.

Include the grain in the key

If a file contains order lines, order_id alone is usually not unique. A composite order_id plus line_id may be the correct key. Deduplicating by order_id would delete legitimate products from the same order.

Likewise, a daily customer snapshot might use customer_id plus snapshot_date. The correct key follows the record definition, not whichever column happens to produce fewer duplicates.

Null keys need their own policy. A group of missing IDs should not be treated as one confidently identified entity. Reject or separately investigate missing required keys before deduplication.

Reconcile the effect of removal

Track raw rows, identical replays, accepted unique entities and conflicts. Compare measures before and after deduplication, but do not infer correctness from a smaller total alone.

In this fixture, the repeated P03 adds 20,000 paise to a naive raw known-value sum. Removing the replay restores the all-status known subtotal to 65,000 paise. One accepted amount remains missing, so that is still a known subtotal rather than a complete total.

Exercise: add an explicit version column to a source copy and design a latest-version rule. Test tied versions with different amounts, out-of-order arrivals and exact replays. Explain when the program must still refuse to choose a winner.

NeuraPath's Data Analytics with Generative AI course connects pandas cleaning with entity identity. A defensible deduplication step explains which records were repeated and why each removal was allowed.

Continue learning

This article is part of the Pandas wrangling and data checks sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Convert mixed date formats without silent data loss.
• Continue with Handle missing values without inventing customer behaviour.