Skip to content

Duplicate Records

Duplicates inflate counts, overstate revenue, and ruin aggregations. A customer who appears twice in your CRM gets marketed to twice — wasting budget and annoying the customer. An order counted twice makes your revenue look higher than it is. Deduplication is a critical step before any downstream analysis.

Real-world duplicate sources

Duplicates enter datasets from several directions: ETL pipelines that re-run and re-insert the same data, CRM imports from multiple systems, Excel sheets where someone copied rows, and event streams where the same event fires twice due to a retry logic bug. The source determines the deduplication strategy.

Learning Objectives

  • Detect duplicate rows using .duplicated()
  • Identify duplicates on specific key columns (key-based deduplication)
  • Understand the difference between exact duplicates and logical duplicates
  • Apply the right deduplication strategy: keep first, keep last, manual resolution
  • Measure the revenue impact of duplicates before and after cleaning

Detecting Duplicates

import pandas as pd

df = pd.read_csv("sales_data.csv")

# Check if any rows are fully duplicated across all columns
print(f"Total rows: {len(df):,}")
print(f"Exact duplicate rows: {df.duplicated().sum():,}")
print(f"Unique rows: {df.duplicated().sum() == 0}")

# Which rows are duplicated? (keep=False marks ALL copies, not just the second)
duplicate_rows = df[df.duplicated(keep=False)]
print(f"\nRows involved in duplication: {len(duplicate_rows):,}")
print(duplicate_rows.sort_values("order_id").head(10))

Exact Duplicates — All Columns Match

# View exact duplicates (second and further occurrences only)
exact_dupes = df[df.duplicated()]
print(f"Exact duplicate rows: {len(exact_dupes)}")

# Remove exact duplicates — keep the first occurrence
df_clean = df.drop_duplicates()
print(f"Rows after removing exact dupes: {len(df_clean):,}")

# Verify
assert df_clean.duplicated().sum() == 0, "Still has duplicates!"
print("Verification passed: no exact duplicates remain.")

When to use drop_duplicates() without a subset

Only when every column should be identical for a row to be a duplicate. If you have an updated_at timestamp, two rows with the same order_id but different timestamps will NOT be caught as exact duplicates — you need key-based deduplication.

Key-Based Duplicates — Same ID, Different Data

An order_id should be unique. If two rows share the same order_id, one is a duplicate — even if other columns differ (e.g., someone updated the status and both the old and new version were exported).

# Find rows with duplicate order_id
key_dupes = df[df.duplicated(subset=["order_id"], keep=False)]
print(f"Rows with duplicate order_id: {len(key_dupes):,}")
print(key_dupes.sort_values(["order_id", "order_date"]).head(20))

# How many unique order IDs have more than one row?
dup_counts = df.groupby("order_id").size()
dup_ids = dup_counts[dup_counts > 1]
print(f"\nOrder IDs with >1 row: {len(dup_ids):,}")
print(dup_ids.head(10))

Sample output — two versions of the same order:

order_id customer_id total_amount status order_date
O145 C003 389.00 pending 2024-01-15 09:00
O145 C003 389.00 completed 2024-01-15 14:30

This is a logical duplicate — same order, updated status. Keep the latest version.

# Keep the most recent version of each order
# Assumes an 'order_date' or 'updated_at' column exists
df_clean = (
    df
    .sort_values("order_date", ascending=False)    # most recent first
    .drop_duplicates(subset=["order_id"], keep="first")  # keep first = most recent
    .reset_index(drop=True)
)

print(f"Before: {len(df):,} rows")
print(f"After:  {len(df_clean):,} rows")
print(f"Removed: {len(df) - len(df_clean):,} duplicate rows")

For mid-level analysts

"Keep latest" is not always the right rule. Consider these scenarios:

  • Keep highest-value: for orders where a correction changed the price — keep the one with the correct (higher or lower) total based on business rules
  • Keep by status priority: completed > refunded > cancelled > pending — if the same order has two rows with different statuses, keep the most "final" one
  • Flag for manual review: if the two versions have materially different amounts (not just a status update), flag for a human to resolve
# Keep based on status priority — not just recency
status_priority = {"completed": 4, "refunded": 3, "cancelled": 2, "pending": 1}
df["status_rank"] = df["status"].map(status_priority).fillna(0)

df_clean = (
    df
    .sort_values("status_rank", ascending=False)
    .drop_duplicates(subset=["order_id"], keep="first")
    .drop(columns=["status_rank"])
    .reset_index(drop=True)
)

Fuzzy Duplicates — Same Entity, Different Spelling

Sometimes the same record appears with slight variations in text — different capitalisation, extra spaces, abbreviated names.

# Sample: same customer entered differently
customers = pd.DataFrame({
    "customer_id": ["C001", "C001a", "C001b", "C001c"],
    "customer_name": ["Priya Sharma", "priya sharma", "PRIYA SHARMA", "Priya  Sharma"],
    "email": ["priya@example.com", "priya@example.com", "priya@example.com", "priya@example.com"],
    "region": ["North", "North", "north", "North"],
})

# Step 1: Normalise for comparison
customers["name_clean"] = (
    customers["customer_name"]
    .str.lower()
    .str.strip()
    .str.replace(r"\s+", " ", regex=True)
)
customers["region_clean"] = customers["region"].str.lower().str.strip()

# Step 2: Deduplicate on normalised + email
unique_customers = customers.drop_duplicates(subset=["email", "name_clean"])
print(f"Before: {len(customers)} rows → After: {len(unique_customers)} rows")

# Fuzzy matching with the thefuzz library (formerly fuzzywuzzy)
# pip install thefuzz
from thefuzz import fuzz, process

# Find customer names that are >85% similar to "Priya Sharma"
names = ["Priya Sharma", "priya sharma", "P. Sharma", "Priya S.", "James Brown"]
matches = process.extract("Priya Sharma", names, scorer=fuzz.token_sort_ratio, limit=5)

for name, score in matches:
    if score >= 85:
        print(f"Likely match: '{name}' (score: {score})")

For senior analysts

Fuzzy deduplication at scale requires a blocking strategy — you cannot compare every pair of rows (that is O(n²)). Block on a known-stable field first (email domain, zip code, first letter of last name) to reduce the candidate pairs, then apply fuzzy matching within blocks. Libraries like recordlinkage implement this efficiently.

Deduplication Strategy Reference

Scenario Strategy Pandas Code
Exact row duplicates Keep first occurrence df.drop_duplicates()
Duplicate IDs, keep newest Sort by timestamp DESC, then dedupe df.sort_values("updated_at", ascending=False).drop_duplicates(subset=["order_id"], keep="first")
Duplicate IDs, keep by status priority Sort by priority column, then dedupe Map status to rank, sort DESC, then drop_duplicates
Fuzzy name duplicates Normalise strings, then dedupe .str.lower().str.strip() then drop_duplicates
Flag but do not delete Add is_duplicate column df["is_dup"] = df.duplicated(subset=["order_id"])
Multiple sources, different IDs Build a match table Requires entity resolution — beyond Pandas alone

Auditing After Deduplication

Always verify the result and measure the impact:

# Check for remaining duplicates on the key column
remaining_dupes = df_clean.duplicated(subset=["order_id"]).sum()
print(f"Remaining duplicate order_ids: {remaining_dupes}")
assert remaining_dupes == 0, "Deduplication incomplete!"

# Compare before/after
print(f"\nBefore vs After:")
print(f"  Row count:     {len(df):,} → {len(df_clean):,} (removed {len(df) - len(df_clean):,})")
print(f"  Total revenue: £{df['total_amount'].sum():,.2f} → £{df_clean['total_amount'].sum():,.2f}")
print(f"  Revenue delta: £{df['total_amount'].sum() - df_clean['total_amount'].sum():,.2f}")

# Spot check: are the retained rows the correct versions?
# Pick a known duplicate and verify the right version was kept
sample_id = "O145"
print(f"\nAll versions of order {sample_id} in raw data:")
print(df[df["order_id"] == sample_id][["order_id", "status", "order_date"]])
print(f"\nKept version in clean data:")
print(df_clean[df_clean["order_id"] == sample_id][["order_id", "status", "order_date"]])

Always verify revenue impact

Removing duplicates should reduce your row count but revenue should remain close to the original (duplicates inflate revenue, so deduplication reduces it). If revenue drops dramatically, check whether you accidentally removed legitimate orders — not duplicates.

Practice Exercises

Warm-up

  1. Count the total number of exact duplicate rows in the sales dataset.
  2. Find all rows with a duplicate order_id using duplicated(subset=["order_id"], keep=False). Print the results sorted by order_id.
  3. Remove exact duplicate rows using drop_duplicates(). Verify none remain.

Main

  1. The sales CSV was exported twice — each order can appear up to twice. Find all order_id values with more than one row. Keep the most recently dated version of each. Print the before/after row count and revenue total.
  2. Normalise the customer_name column: lowercase, strip whitespace, collapse multiple spaces. Then find pairs of rows with the same email but different raw names. How many are there?
  3. After deduplication, calculate revenue before and after and express the duplicate inflation as a percentage.

Stretch

  1. Write a function deduplicate(df, key_col, sort_col, sort_ascending=False, keep="first") that:
  2. Sorts df by sort_col in sort_ascending order
  3. Deduplicates on key_col keeping keep
  4. Returns the clean DataFrame and a summary dict with rows_before, rows_after, rows_removed, revenue_before, revenue_after
  5. Implement fuzzy deduplication: find customer pairs where email matches exactly but customer_name differs with a fuzzy similarity score below 90. Output a DataFrame of flagged pairs for manual review.

Interview Questions

[Beginner] How do you detect duplicate rows in a Pandas DataFrame?

Show answer 
# Count exact duplicate rows
df.duplicated().sum()

# See the duplicated rows (keep=False marks all copies)
df[df.duplicated(keep=False)]

# Duplicate rows on a specific key column
df[df.duplicated(subset=["order_id"], keep=False)]

duplicated() returns a boolean Series: True for each row that is a duplicate of a previous row (by default). keep="first" marks only the second+ occurrence; keep=False marks all copies of a duplicated row.

[Beginner] What is the difference between drop_duplicates() and drop_duplicates(subset=["order_id"])?

Show answer

drop_duplicates() with no arguments removes rows where every column is identical.

drop_duplicates(subset=["order_id"]) removes rows where the order_id column is repeated — even if other columns differ (status, timestamp, etc.).

In practice, key-based deduplication is almost always what you want, because real-world duplicates rarely have identical values in every column — they usually differ in at least one field (update timestamp, status change).

[Mid-level] What is the difference between an exact duplicate and a logical duplicate? Give an example of each.

Show answer

Exact duplicate: every column in both rows is identical. This happens when a pipeline re-inserts the exact same record twice. 

order_id | total | status    | order_date
O145     | 389   | completed | 2024-01-15   ← exact copy of next row
O145     | 389   | completed | 2024-01-15

Logical duplicate: the rows represent the same real-world entity but have different values in some columns — because the record was updated between exports. 

order_id | total | status  | order_date
O145     | 389   | pending   | 2024-01-15 09:00
O145     | 389   | completed | 2024-01-15 14:30
Both rows refer to the same order (O145), but at different points in its lifecycle. The second row is the "correct" current state.

Exact duplicates are easy to remove. Logical duplicates require a decision rule: keep newest, keep highest status, or flag for review.

[Mid-level] You have an orders table where the same order can appear multiple times because the ETL process runs multiple times a day. How do you deduplicate it?

Show answer 
# Sort by the timestamp that represents the most current state
df_sorted = df.sort_values("updated_at", ascending=False)

# Keep only the first (most recent) row for each order_id
df_clean = df_sorted.drop_duplicates(subset=["order_id"], keep="first")

# Verify: no duplicate order_ids remain
assert df_clean["order_id"].duplicated().sum() == 0

# Log the impact
rows_removed = len(df) - len(df_clean)
revenue_removed = df["total_amount"].sum() - df_clean["total_amount"].sum()
print(f"Removed {rows_removed:,} duplicate rows (£{revenue_removed:,.2f} revenue inflation corrected)")

Also: discuss the root cause with the data engineering team. If the ETL re-runs are creating duplicates, the pipeline should be idempotent — running it twice should produce the same result as running it once.

[Senior] You are merging customer data from three CRM systems. Each system uses a different customer ID scheme. How do you detect and reconcile duplicate customers?

Show answer

This is an entity resolution problem — one of the hardest problems in data engineering.

Step 1: Standardise fields used for matching 

for df in [crm1, crm2, crm3]:
    df["email_clean"] = df["email"].str.lower().str.strip()
    df["name_clean"] = df["name"].str.lower().str.strip().str.replace(r"\s+", " ", regex=True)
    df["phone_clean"] = df["phone"].str.replace(r"[^\d]", "", regex=True)

Step 2: Create a canonical match key Email is usually the most reliable — if two records share an email, they are almost certainly the same person. 

# Flag matches by email
all_customers = pd.concat([crm1, crm2, crm3])
email_counts = all_customers.groupby("email_clean").size()
all_customers["email_duplicate"] = all_customers["email_clean"].map(email_counts) > 1

Step 3: Apply fuzzy matching where exact keys fail Not everyone has an email in all systems. Use name + phone as a fallback, with fuzzy matching (fuzz.token_sort_ratio) within blocking groups (same first letter of last name, same area code).

Step 4: Create a master customer ID Build a mapping table: original_system_id → master_customer_id. Assign a new ID to each entity cluster. All three CRM IDs that refer to the same person map to the same master ID.

Step 5: Escalate ambiguous cases Any pair where you're not >90% confident is a match should be flagged for manual review — not silently merged. False merges (treating two different people as one) are worse than missed merges for most downstream analyses.

Previous: Missing Values | Next: Outliers