Resolve Duplicate Rows and Conflicting Business Keys

Module 3: Cleaning Real-World Data · Lecture 10 of 32

Deduplication is a business rule, not a button. Distinguish exact redelivery from two different records claiming the same order, and build a cleaner that refuses to choose an unsupported winner.

What you will learn

Detect full-row duplicates separately from repeated keys.
Compare conflicting versions before deciding a resolution policy.
Test that duplicated delivery does not change a metric.

Understand the problem

Exact redelivery

An export job may send the same row twice. Under a one-row-per-order contract, identical rows can be removed without changing the represented entity. Keep the count of removed rows in the audit. If the source actually contains multiple identical line items, full-row deduplication would be wrong unless a line identifier establishes that they are redeliveries.

Conflicting keys

Two rows can share order_id but differ in refund, status, or quantity. One might be a later valid snapshot, an out-of-order event, or an erroneous record. A keep='last' choice follows physical row order, which is not automatically event time or authority. Require an explicit version number, update timestamp, or documented source precedence before resolving the conflict.

Choose the right pipeline behavior

A pipeline can stop, quarantine the entire conflicting key group, or resolve according to a trusted rule. This course stops because choosing a row could change the revenue total. A high-availability report might publish unaffected segments with a visible incompleteness flag, but that is a separate product decision, not an excuse to conceal conflicts.

Idempotence and reconciliation

A useful cleaning property is that repeated delivery of an identical observation does not change the accepted entity count or revenue. Test that property directly. Keep raw records immutable so a reviewer can reconstruct what was received. Do not overwrite the source file with the deduplicated result and lose evidence about the delivery defect.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 10 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

raw = load_raw()
exact = raw.loc[raw.duplicated(keep=False)]
print('Rows involved in exact repeats:', len(exact))
original = load_orders()
redelivered = pd.concat([raw, raw.iloc[[0]]], ignore_index=True)
cleaned, audit = clean_orders(redelivered)
pd.testing.assert_frame_equal(original, cleaned)
conflict = raw.iloc[[2]].copy()
conflict.loc[:, 'discount_paise'] = '1'
try:
    clean_orders(pd.concat([raw, conflict], ignore_index=True))
except ValueError as error:
    print('Blocked conflict:', str(error))
else:
    raise AssertionError('A conflicting order key was accepted')
assert audit['exact_duplicates'] == 3

Verified output

Rows involved in exact repeats: 4
Blocked conflict: Conflicting rows share an order_id; investigate before reporting

Walk through the result

Four displayed raw rows participate in two duplicate pairs, but only two extra rows are removed in the original audit. The redelivery test adds another identical copy and confirms that the clean table remains unchanged. The conflict changes a valid order's discount without changing its key; the cleaner raises rather than guessing which version is authoritative. This is a business-contract test that protects the calculation from a realistic source defect.

Common mistakes and diagnosis

duplicated(keep=False) counts all members of duplicate groups, not just extra copies. deduplicating only on customer_id destroys legitimate repeat purchases. Sorting by an untrusted timestamp does not establish authority. Conflicts involving missing timestamps or equal version numbers need an explicit tie policy. Do not fix a failed uniqueness assertion by resetting the row index: that only changes labels.

Practice assignment

Produce a review table of all raw rows whose order_id is repeated. Compare its row count with the count of removable exact duplicates. Explain why the same quantity cannot be used for both measures.

Worked solution and explanation

Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.

key_review = raw.loc[raw['order_id'].duplicated(keep=False)].sort_values('order_id')
extra_copies = int(raw.duplicated().sum())
assert len(key_review) == 4
assert extra_copies == 2
key_review.to_csv(OUT / 'duplicate-review.csv', index=False)

Check your understanding

When is keeping the latest row a defensible resolution?

Answer guide

When the source contract supplies a reliable ordering field or version and explicitly defines later records as authoritative snapshots for that entity. You also need a policy for equal versions, missing timestamps, and late delivery. File row order alone is not such a contract.

Reference and next step

pandas duplicate removal

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next