pandas / Intermediate: grouping, joins and temporal quality / Lesson 12

Duplicates, quality rules and reconciliation

Distinguish repeated observations, repeated identities and duplicate labels before choosing a correction.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Define business uniqueness independently of row and index equality.
Preserve conflicts and reconcile accepted/rejected populations.

Repeated rows, keys and labels are different

Two equal measurement rows can describe legitimate separate occurrences if they have different event identities. Two rows with the same immutable event identifier can instead be retransmission or a conflicting payload. Duplicate index labels are another representation issue that can affect selection or reindexing without proving business duplication. Define the uniqueness fields at the source grain before using duplicated or drop_duplicates. An item identifier alone is insufficient when each item can have many movements. A universal keep-first cleaning step can erase valid data, hide an upstream ingestion bug or choose the wrong version of a record.

Flag every member of a repeated-identity group

The illustrative DataFrame has E1 twice with the same units and E2 once. duplicated on event_id with keep=False marks both E1 rows for review, and the declared repeated-identity list is E1. This does not remove anything or decide that the first copy is authoritative. A subset defines the comparison key, while keep controls which members are flagged; changing either can alter the meaning of the check. Expected results are derived rather than pandas-tested. Preserve all conflicting rows when one identity carries different attributes, including source version and arrival evidence needed to resolve the inconsistency.

python Illustrative repeated-identity review mask

import pandas as pd

frame = pd.DataFrame({'event_id': ['E1', 'E1', 'E2'], 'units': [2, 2, 3]})
flagged = frame.duplicated(subset=['event_id'], keep=False)
assert flagged.tolist() == [True, True, False]
repeated_ids = frame.loc[flagged, 'event_id'].unique().tolist()
assert repeated_ids == ['E1']
print(repeated_ids)

Expected output

['E1']

Both E1 rows are flagged and no row is removed; pandas was not executed.

Quality is a set of named business checks

Useful checks can include allowed schema, nonmissing identity, declared key uniqueness, nonnegative units, valid status membership and interval coverage. They are not all reducible to removing nulls or duplicates. Negative units can be valid returns under one contract and invalid stock under another. Record accepted rows, rejected rows and reasons so totals remain explainable. A passing count check does not prove correct membership, and a passing sum can hide an omitted value offset by a duplicate. Combine independent identity and measure invariants rather than adding many tests that all restate the same row count.

Apply one explicit immutable-event rule locally

The standard-library model treats equal E1 payloads as retransmission under a declared immutable-event contract. It verifies equality before retaining one unique event, then checks unique units total five. If the repeated identifier carried another units value, the assertion would fail rather than silently overwrite it. This is a single-threaded in-memory identity rule, not a pandas duplicate detector or a durable ingestion transaction. A source that permits event revisions needs a different versioned policy. State whether correction events amend prior facts or replace records, and do not infer chronology from input order when selecting a supposedly latest version.

python Offline immutable-event reconciliation rule

events = [('E1', 2), ('E1', 2), ('E2', 3)]
unique = {}
for event_id, units in events:
    if event_id in unique:
        assert unique[event_id] == units
    unique[event_id] = units
assert sum(unique.values()) == 5
print(len(unique), sum(unique.values()))

Expected output

2 5

Three deliveries represent two consistent unique events totaling 5 units under the declared rule.

Publish a result with its coverage explanation

A quality report should connect source identity, rule version, accepted population, terminal conflicts and output invariants. Keep original synthetic rows available for review instead of overwriting the only evidence during cleaning. Duplicate-label restrictions can be enforced deliberately in a reviewed pipeline, but changing index flags does not replace business-key validation. This lesson executes only the plain-event model and performs no database write or production-data cleanup. pandas duplicate detection, index-label restrictions and correction behavior remain unexecuted illustrations. The acceptance claim is limited to the supplied identity and payload rule, not a universal definition of clean data.

Common errors and fixes

All repeated item IDs are dropped from a movement table.

Why it happens: Entity identity was confused with observation identity.
How to fix it: Define the actual event or composite observation key before correction.
Prevent it next time: Keep legitimate repeated-entity fixtures in tests.

keep=last is treated as a chronological latest-record rule.

Why it happens: Current row order was mistaken for validated event time or version.
How to fix it: Specify version precedence and deterministic conflict handling explicitly.
Prevent it next time: Test shuffled input and equal-version conflicts.

A duplicate identifier with conflicting units is silently overwritten.

Why it happens: Deduplication ignored immutable payload consistency.
How to fix it: Preserve the conflict and reconcile it against the authoritative source contract.
Prevent it next time: Check payload agreement as well as identity equality.

Check your understanding

1. Can two rows with equal units and depot be distinct legitimate events?

Show worked solution

Yes. Equal measurement values do not define occurrence identity. Use the source event key or a reviewed composite key to decide uniqueness. A content-only deduplication rule can erase repeated but real movements, while retransmission of one immutable event should preserve its original identity and consistent payload.

2. Why should a cleaning report retain rejected and conflicted identities?