Handling Duplicates

import pandas as pd
import numpy as np

# Merged orders dataset — contains all three types of duplicate
df = pd.DataFrame({
    'order_id':    [1001, 1002, 1003, 1004, 1005, 1001, 1006, 1007, 1007, 1008, 1009, 1010],
    'customer_id': [201,  202,  203,  201,  204,  201,  205,  206,  206,  202,  207,  202 ],
    'product':     ['Laptop','Phone','Tablet','Laptop','Monitor','Laptop',
                    'Keyboard','Headphones','Headphones','Phone','Mouse','Phone'],
    'amount':      [999,  349,  529,  999,  449,  999,  89,   149,  149,  349,  39,  350],
    #                                                    ^^^                           ^^^
    # order_id 1001 appears at rows 0 and 5 — exact duplicate
    # order_id 1007 appears at rows 6 and 7 — exact duplicate
    # customer 202 appears in rows 1, 9, 11 — this is fine (3 separate orders)
    # row 11: amount 350 vs row 1: amount 349 — near-duplicate for phone order?
    'status':      ['shipped','delivered','processing','shipped','delivered',
                    'shipped','processing','delivered','delivered','delivered',
                    'shipped','delivered'],
    'date':        ['2024-03-01','2024-03-02','2024-03-03','2024-03-01','2024-03-04',
                    '2024-03-01','2024-03-05','2024-03-06','2024-03-06','2024-03-07',
                    '2024-03-08','2024-03-09']
})

print(f"Rows loaded: {len(df)}")
print(df.to_string(index=True))

What just happened?

Even eyeballing the table you can spot things: rows 0 and 5 are identical (order 1001), rows 7 and 8 are identical (order 1007). Customer 202 appears three times — but rows 1, 9, and 11 have different order IDs and dates, so those are legitimate repeat customers. Row 11 has amount 350 while row 1 has 349 — same customer, same product, one day apart. Is that a near-duplicate or a genuine second purchase? That's the judgment call we'll work through.

# .duplicated() returns True for every row that is an exact copy of an earlier row
# keep='first' means: mark all copies EXCEPT the first occurrence as duplicates
exact_dupes = df[df.duplicated(keep='first')]

print(f"Exact duplicate rows found: {len(exact_dupes)}\n")
print(exact_dupes)
print()

# Remove exact duplicates — keep the first occurrence of each
df_clean = df.drop_duplicates(keep='first')
print(f"Rows after removing exact duplicates: {len(df_clean)}  (removed {len(df) - len(df_clean)})")

What just happened?

pandas' .duplicated(keep='first') scans every row and returns True for any row that is an exact match of a previous one. keep='first' means the first occurrence is considered the "real" row — all later copies are flagged. .drop_duplicates(keep='first') removes all the flagged copies in one call.

Two exact duplicates found and removed: the second copy of order 1001 (row 5) and the second copy of order 1007 (row 8). We're now at 10 rows. But we're not done — we still need to check for key duplicates and near-duplicates.

# Check whether any order_id appears more than once
# subset='order_id' means: only look at the order_id column when checking for duplicates
key_dupes = df_clean[df_clean.duplicated(subset='order_id', keep=False)]
# keep=False marks ALL occurrences — not just the copies — so we can see both rows

print(f"Rows with duplicate order_id: {len(key_dupes)}")
if len(key_dupes) > 0:
    print("\nAll rows with conflicting order IDs:")
    print(key_dupes.sort_values('order_id'))
else:
    print("No key duplicates found — order_id is unique after exact duplicate removal.")
print()

# Also check customer_id — a customer appearing twice is NOT a problem
# (they can place multiple orders), so we just want to know the count
cust_order_counts = df_clean.groupby('customer_id')['order_id'].count()
repeat_customers  = cust_order_counts[cust_order_counts > 1]
print(f"Customers with multiple orders: {len(repeat_customers)}")
print(repeat_customers)

What just happened?

pandas' .duplicated(subset='order_id') checks for duplicates only within the specified column — ignoring all other columns. keep=False is the key difference from Step 1: it marks every occurrence of a duplicate (including the first), so you can see all conflicting rows side by side and decide which one to keep.

Good news: after removing exact duplicates, every order_id is now unique — no conflicting records. Customers 201 and 202 each appear multiple times, but with different order IDs — those are legitimate repeat purchases. This is an important distinction: a duplicated customer ID is expected and desirable. A duplicated order ID is a problem.

# Near-duplicate rule for this dataset:
# "Same customer + same product + amount within 5% + within 7 days = suspicious"
df_clean['date'] = pd.to_datetime(df_clean['date'])   # make sure date is datetime not string

near_dupes = []

rows = df_clean.reset_index(drop=True)   # reset index after dropping rows

for i in range(len(rows)):
    for j in range(i + 1, len(rows)):
        row_a = rows.iloc[i]
        row_b = rows.iloc[j]

        # Condition 1: same customer and same product
        same_customer_product = (row_a['customer_id'] == row_b['customer_id'] and
                                  row_a['product']     == row_b['product'])

        # Condition 2: amount within 5% of each other
        amount_diff_pct = abs(row_a['amount'] - row_b['amount']) / row_a['amount']
        similar_amount  = amount_diff_pct < 0.05

        # Condition 3: dates within 7 days of each other
        date_diff = abs((row_a['date'] - row_b['date']).days)
        close_dates = date_diff <= 7

        if same_customer_product and similar_amount and close_dates:
            near_dupes.append({
                'row_a_idx':    i,
                'row_b_idx':    j,
                'order_a':      row_a['order_id'],
                'order_b':      row_b['order_id'],
                'customer':     row_a['customer_id'],
                'product':      row_a['product'],
                'amount_a':     row_a['amount'],
                'amount_b':     row_b['amount'],
                'amount_diff%': round(amount_diff_pct * 100, 1),
                'days_apart':   date_diff
            })

if near_dupes:
    print(f"Near-duplicate pairs found: {len(near_dupes)}\n")
    print(pd.DataFrame(near_dupes).to_string(index=False))
else:
    print("No near-duplicates found.")

What just happened?

pandas' .iloc[] accesses rows by integer position so we can compare each pair. pd.to_datetime() converts the date strings so we can do arithmetic (subtract two dates to get the gap in days). The loop checks every combination of two rows against our three near-duplicate criteria.

Customer 202 has three phone orders. Orders 1002 and 1008 are 5 days apart with identical amounts — that's suspicious. Orders 1002 and 1010 are 7 days apart with a £1 difference (0.3%) — also suspicious. But here's where analysis stops and judgment begins. Are these copy-paste errors, or is customer 202 genuinely buying phones repeatedly? You need to escalate this to the business, not silently delete it.

def duplicate_audit(original_df, cleaned_df, near_dupe_pairs):
    """Produces a plain-English duplicate audit report."""
    exact_removed  = len(original_df) - len(cleaned_df)
    near_flagged   = len(near_dupe_pairs)

    print("=" * 52)
    print("  DUPLICATE AUDIT REPORT")
    print("=" * 52)
    print(f"\n  Original row count:    {len(original_df)}")
    print(f"  After exact removal:   {len(cleaned_df)}")
    print(f"  Exact duplicates found and removed: {exact_removed}")
    print(f"  → Action taken: auto-dropped (pipeline error confirmed)\n")

    print(f"  Key duplicate check (order_id):  PASSED — all IDs unique")
    print(f"  Key duplicate check (customer):  {len(cleaned_df['customer_id'].unique())} unique customers, "
          f"{len(cleaned_df) - len(cleaned_df['customer_id'].unique())} repeat-buyer rows (expected)\n")

    print(f"  Near-duplicate pairs flagged: {near_flagged}")
    if near_flagged > 0:
        print(f"  → Action required: HUMAN REVIEW before deletion\n")
        for pair in near_dupe_pairs:
            print(f"    Orders {pair['order_a']} & {pair['order_b']} — "
                  f"Customer {pair['customer']}, {pair['product']}, "
                  f"£{pair['amount_a']} vs £{pair['amount_b']}, "
                  f"{pair['days_apart']} days apart")
    print(f"\n  Final row count ready for modelling: {len(cleaned_df)}")
    print("=" * 52)

duplicate_audit(df, df_clean, near_dupes)

What just happened?

pandas' .unique() counts distinct values in a column. Subtracting the unique customer count from the total row count tells us how many rows are "repeat appearances" — and whether that's expected (customers with multiple orders) or suspicious.

The audit report clearly separates what was done automatically (exact duplicates removed), what passed cleanly (key duplicate check on order_id), and what needs a human decision (near-duplicate pairs). This is the output you forward to the data owner — not a notebook with 50 cells of code. One clear document, three sections, explicit next actions.

Teacher's Note

The most dangerous duplicate is the one you don't drop. If you silently keep two copies of the same transaction, your revenue totals are overstated, your model trains on duplicate examples and becomes overconfident, and any analysis you run is wrong. This is why the duplicate check comes before everything else in the data cleaning workflow.

But the second most dangerous thing is deleting legitimate rows because they looked suspicious. Customer 202 buying three phones in a week might seem odd — but maybe they're a reseller. The rule is: auto-drop what you can prove is an error (exact duplicates from a known pipeline bug), and escalate everything else. Documentation of every decision is what protects you when someone asks "why are there only 10 rows in this dataset?"

Up Next · Lesson 33

Feature Extraction Basics

Turn raw columns into smarter features — extract day of week from dates, parse text into numbers, and create ratio features that a model couldn't see on its own.