Data Cleaning Fundamentals: Detecting and Removing Duplicate Records

A Practical Guide to Identifying, Understanding, and Eliminating Duplicate Data

Duplicate records are among the most common data quality issues found in real-world datasets.

They can arise from data entry mistakes, system integrations, repeated imports, or inconsistencies across multiple data sources.

While duplicates may appear harmless at first glance, they can significantly distort analysis results.

For example:

• Customer counts may be inflated.
• Revenue calculations may be overstated.
• Marketing campaigns may target the same customer multiple times.
• Machine learning models may learn biased patterns.

Before performing analysis, it is essential to identify and address duplicate records to ensure the reliability of your data.

In this article, we’ll explore what duplicates are, why they occur, how to detect them, and the safest strategies for removing them.

What Are Duplicate Records?

A duplicate record occurs when the same entity appears multiple times in a dataset.

Consider the following example:

Customer ID     Name          Email
1001            Alice Smith   alice@email.com
1002            Bob Jones     bob@email.com
1002            Bob Jones     bob@email.com

The third row is a duplicate of the second row.

If left untreated, duplicate records can lead to inaccurate reporting and analysis.

Why Duplicates Matter

Imagine a company calculating the total number of customers.

Without duplicates:

Total Customers = 10,000

With duplicates:

Total Customers = 11,250

The company now believes it has 1,250 more customers than it actually does.

Similar problems occur when calculating:

• Revenue
• Orders
• Conversion rates
• Customer retention
• Inventory counts

Poor decisions often follow poor data quality.

Common Causes of Duplicate Records

Understanding why duplicates occur helps prevent them from reappearing.

Manual Data Entry

Users may enter the same information multiple times.

Example:

Name
John Smith
John Smith

System Migrations

When moving data between systems, records may be imported multiple times.

Example:

CRM System
↓
Data Warehouse
↓
Duplicate Import

Multiple Data Sources

The same customer may exist in several systems.

Example:

Marketing Database
Email
alice@email.com

Sales Database
Email
alice@email.com

Combining these datasets without proper matching may create duplicates.

Lack of Unique Constraints

Databases without primary key constraints allow duplicate entries.

Example:

INSERT INTO customers

can be executed multiple times for the same customer.

Types of Duplicates

Not all duplicates are identical.

Exact Duplicates

Every field matches.

Example:

ID     Name     City
1      Alice    Toronto
1      Alice    Toronto

These are the easiest duplicates to identify.

Partial Duplicates

Some fields match while others differ.

Example:

Name          Email
Alice Smith   alice@email.com
Alice Smith   alice.smith@email.com

Further investigation is required.

Logical Duplicates

Records represent the same entity but contain slight variations.

Example:

Customer Name
Robert Smith
Bob Smith

or

Company
IBM
International Business Machines

These duplicates are harder to detect automatically.

Detecting Duplicate Records

Let’s load a sample dataset.

import pandas as pd
df = pd.read_csv("customers.csv")

Find Exact Duplicates

df.duplicated()

Output:

0    False
1    False
2     True
3    False

Rows marked as True are duplicates.

Count Duplicates

df.duplicated().sum()

Example output:

15

This indicates 15 duplicate rows.

View Duplicate Records

df[df.duplicated()]

This displays all duplicated rows.

Detecting Duplicates Using Specific Columns

Sometimes only certain columns define uniqueness.

Example:

df.duplicated(
    subset=["email"]
)

This identifies customers sharing the same email address.

Count Duplicate Emails

df.duplicated(
    subset=["email"]
).sum()

Example:

28

Understanding the Keep Parameter

Pandas provides flexibility when detecting duplicates.

Keep First Occurrence

df.duplicated(
    keep="first"
)

Default behavior.

Only later occurrences are flagged.

Keep Last Occurrence

df.duplicated(
    keep="last"
)

Only earlier occurrences are flagged.

Mark All Duplicates

df.duplicated(
    keep=False
)

Every duplicate record is marked.

This is often useful for investigations.

Removing Duplicate Records

Once duplicates have been verified, they can be removed.

Remove Exact Duplicates

df.drop_duplicates()

Remove Duplicates Based on Specific Columns

df.drop_duplicates(
    subset=["email"]
)

This keeps only one record per email address.

Keep Most Recent Record

Suppose customer information changes over time.

Example:

Customer ID     Update Date
1001            2024–01–01
1001            2024–03–01

Sort first:

df.sort_values(
    "update_date"
)

Then:

df.drop_duplicates(
    subset=["customer_id"],
    keep="last"
)

This preserves the newest record.

Investigating Before Deleting

Never assume duplicates should be removed immediately.

Consider this dataset:

Order ID     Product     Quantity
5001         Laptop      1
5001         Mouse       1

The Order ID appears twice.

However, these are not duplicates.

Each row represents a different product within the same order.

Deleting one row would remove valid information.

Always understand the business context first.

Finding Potential Logical Duplicates

Logical duplicates often require more advanced techniques.

Example:

Name
John Smith
Jon Smith

A simple duplicate check will not detect these records.

Possible solutions include:

• String similarity matching
• Fuzzy matching
• Record linkage
• Domain-specific rules

Popular libraries:

rapidfuzz
thefuzz
recordlinkage

Practical Duplicate Detection Workflow

When working with a new dataset:

Step 1

Check overall duplicates.

df.duplicated().sum()

Step 2

Inspect duplicate records.

df[df.duplicated()]

Step 3

Identify columns that should be unique.

Examples:

• Customer ID
• Email
• Order ID

Step 4

Check duplicates within those fields.

df.duplicated(
    subset=["customer_id"]
).sum()

Step 5

Investigate before deleting.

Step 6

Remove confirmed duplicates.

Step 7

Validate final record counts.

Common Mistakes

Automatically Removing Duplicates

Not every repeated value is an error.

Understand the business meaning first.

Ignoring Logical Duplicates

Exact duplicate checks miss many real-world issues.

Using the Wrong Unique Identifier

Choosing the wrong column may remove valid records.

Not Documenting Changes

Always record:

• Number of duplicates found
• Removal criteria
• Number of rows removed

Key Takeaways

Duplicate records can significantly distort analytical results and business decisions.

Before removing duplicates:

• Understand how duplicates occur.
• Identify the correct unique identifiers.
• Investigate records carefully.
• Consider business context.

Common techniques include:

• duplicated()
• drop_duplicates()
• Subset-based duplicate detection
• Fuzzy matching for logical duplicates

In the next article of this series, we’ll explore another critical data quality challenge: outliers, including how to identify unusual observations and determine whether they should be removed, corrected, or retained.

Keep in Touch

Thanks for reading! This blog is where I explore data science, machine learning, AI, optimization, simulation, decision-making, and interesting mathematical ideas — sharing projects, experiments, and thoughts I discover along the way.

• 🐙 **GitHub**: projects & notebooks
• ✍️ **Medium**: more posts like this
• 💼 **LinkedIn**: let’s connect!