Data Cleaning & Deduplication Data Integrity Pandas 2.x Verified Live Interactive Lab

Python Data Cleaning: Finding & Removing Duplicates

Master professional duplicate record detection, evaluation, and deduplication. Distinguish true duplicate records from innocent repeated values, inspect duplicate rows with boolean indexing, control retention using keep, and enforce business keys with subset.

Level: Beginner to Intermediate

Environment: Python 3 / Pandas 2.x

Est. Time: 50–65 Minutes

1. What are Duplicate Records?

The analytical problem of repeated rows in business datasets

Consider this customer database extract:

Index	CustomerID	Name	City	Status
0	101	Amit	Mumbai	Original
1	102	Priya	Delhi	Original
2	101	Amit	Mumbai	Duplicate
3	103	Rahul	Pune	Original

Row 2 is an exact duplicate of Row 0. Duplicate records frequently infiltrate datasets due to:

Common Causes of Duplication:

Multiple File Ingestions: Combining weekly CSV exports where overlapping dates were imported twice.
API Re-tries: Network glitches prompting webhooks or mobile clients to submit forms multiple times.
Database Join Multiplications: Faulty SQL joins generating cartesian duplicates across transactions.
Manual Data Entry: Human operators re-entering customer profiles.

2. Finding Duplicates: df.duplicated()

Detecting duplicate rows and calculating total counts

Pandas provides df.duplicated() to detect duplicate records. It returns a boolean Series:

False

First occurrence of a unique row.

True

Subsequent repeated duplicate.

df.duplicated().sum()

Total number of duplicate rows.

df[df.duplicated()]

Isolates duplicate rows for review.

Boolean Indexing: Writing df[df.duplicated()] filters the table to show only the rows marked as True, letting you inspect the duplicates before deleting them!

3. 🔥 Live Duplicate Detection

Inspect duplicate masks and filter duplicate records

Given the customer table below, practice identifying, counting, and viewing duplicate rows:

duplicate_detection.py

# Task 1: Identify duplicate rows across the dataset

# Task 2: Count how many duplicate rows exist
# df.duplicated().sum()

# Task 3: Display ONLY the duplicate rows
# df[df.duplicated()]

Click "Run Python" to test your code...

4. 🔥 Duplicates vs. Repeated Values

A critical distinction every junior analyst must grasp

Repeated Value ≠ Duplicate Record:
In the sample table above, the city "Mumbai" appears 3 times. Does this mean we have 3 duplicate rows? Absolutely not! Amit (101) lives in Mumbai, and Neha (104) also lives in Mumbai. They are two completely distinct human beings who happen to reside in the same city!

• Repeated Value: An attribute occurs multiple times across independent records (normal).
• Duplicate Record: Two or more rows represent the exact same real-world entity (data flaw).

5. Removing Duplicates: df.drop_duplicates()

Safely generating clean, deduplicated DataFrames

Once duplicates are identified and confirmed, use drop_duplicates() to remove them:

Deduplication Syntax

# Generate a new DataFrame with duplicate rows removed
df_clean = df.drop_duplicates()

Best Practice: Always assign the result to a new variable (df_clean = df.drop_duplicates()). Avoid modifying datasets in-place so you can trace your cleaning steps.

6. 🔥 The keep Parameter: "first", "last", or False

Controlling which occurrence of a duplicate group survives

When identical records exist, which one should be kept? The keep argument gives you control:

keep="first" (Default)

df.drop_duplicates(keep="first")

Keeps the first occurrence encountered. Marks and drops all subsequent occurrences.

keep="last"

df.drop_duplicates(keep="last")

Keeps the last occurrence encountered. Marks and drops all earlier occurrences.

keep=False

df.drop_duplicates(keep=False)

Drops all occurrences in the duplicate group. Retains only truly unique records.

7. 🔥 Live keep Practice

Execute each retention strategy and compare the output

keep_strategy_lab.py

# Keep first occurrence (default: marks later duplicates)
df.drop_duplicates(keep="first")

Click "Run Python" to test...

8. 🔥 Duplicates by Specific Columns: The subset Parameter

Enforcing primary key uniqueness when secondary columns differ

In real enterprise data, two records representing the exact same customer may have different secondary fields (e.g. an address update):

CustomerID	Name	City	Analysis
101	Amit	Mumbai	Older address record
101	Amit	Pune	Newer address record

If you run df.drop_duplicates() without parameters, neither row is dropped because the cities differ! To tell Pandas that CustomerID defines a unique entity, use subset:

Subset Blueprint

# Deduplicate by primary key only
df.drop_duplicates(subset=["CustomerID"])

# Deduplicate by combined business keys
df.drop_duplicates(subset=["CustomerID", "Email"])

9. 🔥 Live subset Practice

Observe the difference between full-row and key-based deduplication

subset_lab.py

# Notice: Full rows are NOT identical because Amit & Priya have different cities!
# Task: Remove duplicate records based on the business key "CustomerID"

Click "Run Python" to test subset...

10. 🔥 Which Record Should Be Kept?

The myth of "keep='last' always means newest"

Critical Analyst Warning:
keep="last" does NOT magically know which record is the newest! It merely looks at row position in the DataFrame. If your DataFrame is NOT sorted by timestamp or update date, keep="last" could discard your newest update and preserve stale data!

Professional Deduplication Pattern:
1. Sort first by date: df = df.sort_values("UpdatedAt")
2. Then deduplicate keeping the latest: df_clean = df.drop_duplicates(subset=["CustomerID"], keep="last")

11. The 6-Step Deduplication Workflow

A disciplined framework for data cleaning

Detect

df.duplicated().sum()

Inspect

df[df.duplicated(keep=False)]

Key

Identify subset columns

Decide

keep="first" or "last"

Deduplicate

drop_duplicates(subset=...)

Verify

sum() == 0

12. 🔥 Practical Data Analytics Scenario

Diagnosing customer CRM records

Consider this realistic marketing CRM export:

CustomerID	Name	Email	City
101	Amit	amit@example.com	Mumbai
102	Priya	priya@example.com	Delhi
103	Rahul	rahul@example.com	Pune
101	Amit	amit@example.com	Mumbai
104	Neha	neha@example.com	Mumbai
102	Priya	priya@example.com	Delhi

Analyst Assessment:

Complete duplicate rows: 2 rows (Amit row 3 and Priya row 5).
Duplicate CustomerIDs: Exactly 2 (IDs 101 and 102).
Duplicate Emails: Exactly 2 (amit@ and priya@).
Action: df.drop_duplicates(subset=["CustomerID"], keep="first") cleans the dataset safely!

13. 🔥 Final Live Challenge: End-to-End Deduplication

Audit, clean, and verify customer identities

In the code editor below, detect duplicates using the customer key "CustomerID", remove them keeping the first record, and verify that .duplicated().sum() equals 0:

final_deduplication.py

# Final Enterprise Deduplication Challenge
# Step 1: Detect duplicates based on unique CustomerID


# Step 2: Remove duplicate customer records keeping the first entry


# Step 3: Verify that 0 duplicates remain

Click "Run Python" to test your pipeline...

14. Common Mistakes to Avoid

Pitfalls encountered when deduplicating records

1. Assuming repeated values mean duplicate rows

Just because 50 customers live in Mumbai does not mean they are duplicates. Always distinguish between repeated attributes and duplicate entity identities.

2. Dropping duplicates without specifying subset

If records have minor timestamp or address discrepancies, drop_duplicates() without subset will fail to catch duplicates of the same customer!

3. Assuming keep="last" always keeps the latest update

keep="last" only preserves the newest record if your DataFrame was sorted by timestamp beforehand.

4. Skipping post-cleaning verification

Always run df.duplicated(subset=...).sum() immediately after deduplication to confirm that duplicate counts are down to zero.

15. Knowledge Check Quiz

Test your understanding of duplicate detection and deduplication

TEST YOUR KNOWLEDGE

Python Duplicate Records Knowledge Check

Validate your understanding of duplicated(), drop_duplicates(), subset, and keep strategies.

Question 1 of 4Current Score: 0 / 0

Why does a repeated value in one column (e.g. City="Mumbai") NOT automatically mean the entire row is a duplicate?

16. Key Takeaways Checklist

What you can now do with Python Deduplication

Distinguish duplicate records from valid repeated column values.

Detect duplicates and count them using df.duplicated().sum().

Isolate duplicate rows using boolean indexing df[df.duplicated()].

Control occurrence retention with keep="first", "last", and False.

Target primary business keys using the subset parameter.

Follow the 6-step deduplication workflow and verify cleaned datasets.