Pandas: Handling Duplicate Data

Last updated: 2026-08-26

Data merges, repeated collection runs, system glitches — duplicate data is everywhere. A single customer record appearing 3 times inflates your statistics by 3x. Pandas' duplicated / drop_duplicates let you detect and clean duplicates precisely, while the subset + keep parameters offer flexible control over granularity. This section covers the complete workflow from detection to removal.

⚠️ Note: The code below must be run in a local Python environment.

1. What You Will Learn

❶ Detecting duplicates with duplicated
❷ Removing duplicates with drop_duplicates
❸ Column-based dedup with subset
❹ Retention strategies with keep
❺ Multi-column dedup and real-world strategies

2. Alice's Duplicate Coffee Orders

(1) The Pain Point: System Glitch Causes Duplicate Orders

Alice's coffee shop system glitched during a rush, and 3 orders were submitted twice:

PYTHON

import pandas as pd

orders = pd.DataFrame({
    'order_id': ['O001', 'O001', 'O002', 'O003', 'O003', 'O003', 'O004'],
    'customer': ['Alice', 'Alice', 'Bob', 'Charlie', 'Charlie', 'Charlie', 'Carol'],
    'drink': ['Latte', 'Latte', 'Americano', 'Mocha', 'Mocha', 'Mocha', 'Espresso'],
    'price': [4.50, 4.50, 3.00, 5.50, 5.50, 5.50, 2.50]
})
print(f"Total rows: {len(orders)}")
print(f"Duplicates: {orders.duplicated().sum()}")
# Total rows: 7, Duplicates: 3

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) The Solution: One-Line Cleanup with drop_duplicates

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Basic dedup (Difficulty ⭐)

PYTHON

import pandas as pd

orders = pd.DataFrame({
    'order_id': ['O001', 'O001', 'O002', 'O003', 'O003', 'O003', 'O004'],
    'customer': ['Alice', 'Alice', 'Bob', 'Charlie', 'Charlie', 'Charlie', 'Carol'],
    'drink': ['Latte', 'Latte', 'Americano', 'Mocha', 'Mocha', 'Mocha', 'Espresso'],
    'price': [4.50, 4.50, 3.00, 5.50, 5.50, 5.50, 2.50]
})

# Remove exact row duplicates
clean = orders.drop_duplicates()
print(clean)
#    order_id customer      drink  price
# 0     O001    Alice      Latte    4.5
# 2     O002      Bob  Americano    3.0
# 3     O003  Charlie      Mocha    5.5
# 6     O004    Carol   Espresso    2.5

print(f"Before: {len(orders)} → After: {len(clean)}")
# Before: 7 → After: 4

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

3. Detecting Duplicates with duplicated

(1) Basic Detection

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Detecting duplicates with duplicated (Difficulty ⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Alice', 'Charlie', 'Bob'],
    'age': [28, 34, 28, 25, 35]
})

# Mark duplicates (first occurrence = False, subsequent = True)
print(df.duplicated())
# 0    False  ← first Alice (keep)
# 1    False  ← first Bob (keep)
# 2     True  ← second Alice (duplicate!)
# 3    False  ← first Charlie (keep)
# 4     True  ← Bob but age=35 (NOT duplicate — row is different)

# Filter to see only duplicate rows
print(df[df.duplicated()])
#    name  age
# 2 Alice   28

# Count duplicates
print(f"Duplicate rows: {df.duplicated().sum()}")  # 1

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) The keep Parameter Controls the Marking Logic

keep Value	Behavior	First Duplicate	Last Duplicate
'first'	Keep the first occurrence	Marked False	Marked True
'last'	Keep the last occurrence	Marked True	Marked False
False	Mark all duplicates	Marked True	Marked True

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Comparing the keep parameter (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Alice', 'Alice', 'Bob'],
    'score': [85, 92, 85, 85, 92]
})

# keep='first' (default) — first occurrence is NOT duplicate
print("keep='first':", df.duplicated(keep='first').tolist())
# [False, False, True, True, True]

# keep='last' — last occurrence is NOT duplicate
print("keep='last':", df.duplicated(keep='last').tolist())
# [True, True, True, False, False]

# keep=False — ALL duplicates are marked True
print("keep=False:", df.duplicated(keep=False).tolist())
# [True, True, True, True, True]

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

4. Removing Duplicates with drop_duplicates

(1) Basic Removal

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Removing duplicates with drop_duplicates (Difficulty ⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Alice', 'Charlie', 'Alice'],
    'age': [28, 34, 28, 25, 28],
    'city': ['NYC', 'LA', 'NYC', 'Chicago', 'Boston']  # different city for last Alice
})

# Drop full-row duplicates
print(df.drop_duplicates())
#    name  age     city
# 0 Alice   28      NYC
# 1   Bob   34       LA
# 3 Charlie   25  Chicago
# 4 Alice   28   Boston  ← kept (city is different from row 0)

# With keep parameter
print(df.drop_duplicates(keep='last'))
#    name  age     city
# 1   Bob   34       LA
# 3 Charlie   25  Chicago
# 4 Alice   28   Boston  ← last Alice kept

# Remove ALL duplicates (keep only unique rows)
print(df.drop_duplicates(keep=False))
#    name  age     city
# 1   Bob   34       LA
# 3 Charlie   25  Chicago
# All Alice rows removed (because there are multiple)

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) inplace and Resetting the Index

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: inplace and reset_index (Difficulty ⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Alice', 'Charlie'],
    'score': [85, 92, 85, 78]
})

# Method 1: assign back (recommended)
df = df.drop_duplicates().reset_index(drop=True)
print(df)
#     name  score
# 0  Alice     85
# 1    Bob     92
# 2 Charlie    78

# Method 2: inplace
# df.drop_duplicates(inplace=True)
# df.reset_index(inplace=True, drop=True)

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

5. Column-Based Dedup with subset

(1) Looking Only at Key Columns

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Column-based dedup with subset (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'student_id': ['S001', 'S001', 'S002', 'S003', 'S002'],
    'name': ['Alice', 'Alice Smith', 'Bob', 'Charlie', 'Robert Bob'],
    'math_score': [85, 85, 92, 78, 92],
    'attempt': [1, 2, 1, 1, 2]
})

# Full-row duplicate: none (name column differs)
print(f"Full-row duplicates: {df.duplicated().sum()}")  # 0

# But student_id should be unique!
print(f"student_id duplicates: {df.duplicated(subset='student_id').sum()}")  # 2

# Drop duplicates based on student_id only
clean = df.drop_duplicates(subset='student_id', keep='first')
print(clean)
#   student_id         name  math_score  attempt
# 0       S001        Alice          85        1
# 2       S002          Bob          92        1
# 3       S003      Charlie          78        1

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) Multi-Column Combination Dedup

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Multi-column combination dedup (Difficulty ⭐⭐)

PYTHON

import pandas as pd

# Same student can have multiple test attempts
# But same student + same subject should be unique
df = pd.DataFrame({
    'student_id': ['S001', 'S001', 'S001', 'S002', 'S002'],
    'subject': ['Math', 'English', 'Math', 'Math', 'English'],
    'score': [85, 90, 88, 92, 87],
    'attempt_date': ['2024-01-10', '2024-01-10', '2024-02-15', '2024-01-12', '2024-01-12']
})

# Duplicate: student_id + subject combination
dupes = df.duplicated(subset=['student_id', 'subject'], keep=False)
print("All rows involved in duplicates:")
print(df[dupes])
# S001 + Math appears twice (row 0 and 2)

# Keep latest attempt per student + subject
clean = df.drop_duplicates(subset=['student_id', 'subject'], keep='last')
print("\nAfter dedup (keep last attempt):")
print(clean)
#   student_id  subject  score attempt_date
# 0       S001   English     90   2024-01-10
# 2       S001      Math     88   2024-02-15  ← latest
# 3       S002      Math     92   2024-01-12
# 4       S002   English     87   2024-01-12

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

6. Strategies for Handling Duplicate Data

(1) Scenario Decision Table

Scenario	Strategy	Code
Fully duplicate rows	drop_duplicates()	`df.drop_duplicates()`
Duplicate key columns	Dedup with subset	`df.drop_duplicates(subset=['id'])`
Keep the latest	keep='last'	`df.drop_duplicates(subset=['id'], keep='last')`
Needs manual review	Mark without deleting	`df[df.duplicated(keep=False)]`
Aggregate instead of delete	groupby + agg	`df.groupby('id').agg({'val': 'mean'})`

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Aggregating instead of deleting (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd

# Multiple readings for same sensor — average instead of drop
df = pd.DataFrame({
    'sensor_id': ['T01', 'T01', 'T01', 'T02', 'T02'],
    'location': ['Lab A', 'Lab A', 'Lab A', 'Lab B', 'Lab B'],
    'temperature': [22.5, 22.7, 22.3, 18.1, 18.3],
    'humidity': [45, 47, 43, 55, 57]
})

# Instead of dropping, aggregate: keep mean
agg = df.groupby('sensor_id').agg({
    'location': 'first',
    'temperature': 'mean',
    'humidity': 'mean'
}).reset_index()
print(agg)
#   sensor_id location  temperature  humidity
# 0       T01    Lab A        22.500      45.0
# 1       T02    Lab B        18.200      56.0

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) Dedup Decision Flow

graph TB
    Q["Found duplicates?"] -->|No| DONE["✅ Data is clean"]
    Q -->|Yes| TYPE{"Full-row or key-column?"}
    TYPE -->|Full-row| SIMPLE["drop_duplicates()"]
    TYPE -->|Key-column| SUBSET["drop_duplicates(subset=['id'])"]
    SIMPLE --> WHICH["keep='first'/'last'/False?"]
    SUBSET --> WHICH
    WHICH -->|Keep first| FIRST["keep='first'"]
    WHICH -->|Keep last| LAST["keep='last'"]
    WHICH -->|Need all for review| REVIEW["duplicated(keep=False) → manual"]
    WHICH -->|Values differ| AGG["groupby().agg() → merge"]

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

7. Complete Example: Full Customer Dedup Workflow

▶ Example

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Full customer dedup workflow (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd

# ============================================
# Comprehensive example: Customer dedup
# with multiple strategies
# ============================================

# 1. Raw data with various duplicate patterns
df = pd.DataFrame({
    'email': ['alice@example.com', 'alice@example.com', 'bob@example.com',
              'charlie@example.com', 'bob@example.com', 'alice@example.com',
              'david@example.com'],
    'name': ['Alice', 'Alice Smith', 'Bob', 'Charlie', 'Bob Jones',
             'Alice', 'David'],
    'phone': ['555-0100', '555-0100', '555-0200', '555-0300',
              '555-0201', '555-0100', '555-0400'],
    'signup_date': ['2024-01-15', '2024-02-01', '2024-01-20',
                    '2024-03-01', '2024-03-15', '2024-01-15',
                    '2024-04-01']
})

print("=== Step 1: Detect Duplicates ===")
print(f"Full-row dupes: {df.duplicated().sum()}")
print(f"Email dupes: {df.duplicated(subset='email').sum()}")
print(f"Email+Phone dupes: {df.duplicated(subset=['email','phone']).sum()}")

# 2. Review all rows involved in email duplicates
print("\n=== Step 2: Review Email Duplicates ===")
email_dupes = df[df.duplicated(subset='email', keep=False)]
print(email_dupes.sort_values('email'))

# 3. Strategy: keep the latest signup per email
df['signup_date'] = pd.to_datetime(df['signup_date'])
clean = df.sort_values('signup_date').drop_duplicates(subset='email', keep='last')
print("\n=== Step 3: After Dedup (keep latest) ===")
print(clean[['email', 'name', 'signup_date']])

# 4. Verify
print(f"\nBefore: {len(df)} rows → After: {len(clean)} rows")
print(f"Unique emails: {clean['email'].nunique()}")

TEXT 📖 Display only

> **Output:** Run this in a local Python environment (pandas 2.x). The Piston server does not have pandas preinstalled — install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

❓ FAQ

Q What is the difference between duplicated and drop_duplicates?

A duplicated() returns a boolean Series that marks duplicate rows (without deleting them) — use it for detection and review. drop_duplicates() directly returns a deduplicated DataFrame (without modifying the original data). Use duplicated when you want to inspect the duplicates, and drop_duplicates when you want to clean them up.

Q How do I choose between keep='first'/'last'/False?

A keep='first' keeps the first occurrence (the default — good for log-style data). keep='last' keeps the last occurrence (good for update-overwrite scenarios). keep=False marks every duplicate row (used for review, keeping none of the duplicates). drop_duplicates(keep=False) keeps only rows that were never duplicated.

Q What is the difference between single-column and multi-column subset?

A subset=['email'] only checks whether the email column is duplicated, ignoring differences in other columns. subset=['email','phone'] requires both columns to match simultaneously to count as a duplicate. Single-column dedup may wrongly delete legitimate data (same email but different contact info), while multi-column combinations are more precise but may miss variant duplicates.

Q What if the index is messed up after dedup?

A After drop_duplicates removes rows, it keeps the original index, leaving gaps. Use reset_index(drop=True) to reset to a continuous index. drop=True discards the old index; otherwise the old index becomes a new column named 'index'. The recommended chained form is: df.drop_duplicates().reset_index(drop=True).

Q Should duplicate data be deleted or aggregated?

A It depends on the business meaning. If the duplicates are errors (system glitch → delete). If the duplicates are repeated measurements → aggregate (mean/max/last). If you are unsure → mark and review first, do not delete directly. Principle: deletion is irreversible, while aggregation preserves information.

Q Does drop_duplicates modify the original DataFrame?

A No. drop_duplicates returns a new DataFrame and leaves the original data unchanged. The inplace=True parameter can modify it in place, but under Pandas 3.x Copy-on-Write mode the assignment form is recommended: df = df.drop_duplicates(), which has clearer semantics.

Q How do I find "near-duplicate" rows?

A Use duplicated for exact duplicates, but "Alice" and "Alice Smith" are not exact duplicates. Strategy: normalize first, then compare — strip() to remove whitespace, lower() to unify case, or match on the email prefix as a substring. For fuzzy matching needs, use the recordlinkage or dedupe library for probabilistic dedup.

📖 Summary

duplicated() marks duplicate rows and returns a boolean Series; drop_duplicates() dedups directly
The keep parameter controls the retention strategy: first (default) / last / False (mark all)
The subset parameter specifies which columns to judge duplicates by, supporting both single columns and multi-column combinations
After dedup, use reset_index(drop=True) to reset to a continuous index
Fully duplicate rows → delete directly; duplicate key columns → dedup with subset; differing values → aggregate rather than delete
Detect and review first (duplicated), then decide on a strategy (delete / aggregate / manual review)

📝 Exercises

Basic (Difficulty ⭐): Create a DataFrame with 3 fully duplicate rows (8 rows total), count them with duplicated().sum(), clean them with drop_duplicates(), and compare the row counts before and after.
Intermediate (Difficulty ⭐⭐): Create a student grades DataFrame (the same student appears multiple times for the same subject), dedup with subset=['student_id','subject'], and use keep='last' to retain the latest grade.
Challenge (Difficulty ⭐⭐⭐): Simulate customer signup data (email/name/phone/signup_date), including cases where the email is duplicated but the name/phone differ. Complete the following: detect email duplicates → review the duplicate rows → sort by signup_date then dedup with keep='last' → verify the count of unique emails.

← Previous Lesson: Handling Missing Values · Next Lesson: Data Transformation →

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Pandas: Handling Duplicate Data

1. What You Will Learn

2. Alice's Duplicate Coffee Orders

(1) The Pain Point: System Glitch Causes Duplicate Orders

(2) The Solution: One-Line Cleanup with drop_duplicates

▶ Example

3. Detecting Duplicates with duplicated

(1) Basic Detection

▶ Example

(2) The keep Parameter Controls the Marking Logic

▶ Example

4. Removing Duplicates with drop_duplicates

(1) Basic Removal

▶ Example

(2) inplace and Resetting the Index

▶ Example

5. Column-Based Dedup with subset

(1) Looking Only at Key Columns

▶ Example

(2) Multi-Column Combination Dedup

▶ Example

6. Strategies for Handling Duplicate Data

(1) Scenario Decision Table

▶ Example

(2) Dedup Decision Flow

7. Complete Example: Full Customer Dedup Workflow

▶ Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好