Pandas Duplicate Data Processing

Duplicate data is a common problem in data analysis that can affect the accuracy of statistical results. Pandas provides comprehensive functionality for detecting and handling duplicate data.

Detecting Duplicate Data

duplicated method

Example

import pandas as pd

# Create a DataFrame containing duplicate rows
df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhang San", "Li Si", "Zhao Liu"],
    "Age": [25, 30, 28, 25, 30, 35],
    "City": ["Beijing", "Shanghai", "Guangzhou", "Beijing", "Shenzhen", "Beijing"]
})

print("Original data:")
print(df)
print()

# Detect duplicate rows (default marks from the first occurrence, keep='first')
print("Detected duplicate rows:")
print(df.duplicated())
print()

# Count the number of duplicate rows
print(f"Number of duplicate rows: {df.duplicated().sum()}")
print()

# Detect duplicates in a specific column
print("Detect duplicates by the 'Name' column:")
print(df.duplicated(subset=["Name"]))

keep parameter

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Zhang San", "Wang Wu", "Li Si"],
    "Age": [25, 30, 25, 28, 30]
})

print("Original data:")
print(df)
print()

# keep='first': keep the first occurrence (default)
print("keep='first' (default):")
print(df.duplicated(keep="first"))
print()

# keep='last': keep the last occurrence
print("keep='last'：")
print(df.duplicated(keep="last"))
print()

# keep=False: mark all duplicates (keep none)
print("keep=False (mark all duplicates):")
print(df.duplicated(keep=False))

Removing Duplicate Data

Basic usage of drop_duplicates

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Zhang San", "Wang Wu", "Li Si"],
    "Age": [25, 30, 25, 28, 30],
    "City": ["Beijing", "Shanghai", "Beijing", "Guangzhou", "Shenzhen"]
})

print("Original data:")
print(df)
print()

# Remove duplicate rows (keep the first by default)
print("Remove duplicate rows (keep the first):")
print(df.drop_duplicates())
print()

# Keep the last one
print("Remove duplicate rows (keep the last one):")
print(df.drop_duplicates(keep="last"))
print()

# Do not keep any duplicate rows
print("Remove all duplicate rows:")
print(df.drop_duplicates(keep=False))

Removing Duplicates by Column

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Zhang San", "Wang Wu"],
    "Age": [25, 30, 35, 28],
    "City": ["Beijing", "Shanghai", "Beijing", "Guangzhou"]
})

print("Original data:")
print(df)
print()

# Remove duplicates by a single column
print("Remove duplicates by the 'Name' column:")
print(df.drop_duplicates(subset=["Name"]))
print()

# Remove duplicates by a combination of multiple columns
print("Remove duplicates by the 'Name'+'City' combination:")
print(df.drop_duplicates(subset=["Name", "City"]))
print()

# Keep the row with the maximum value in a specific column
print("Group by 'Name', keep the one with the largest age:")
print(df.sort_values("Age", ascending=False).drop_duplicates(subset=["Name"], keep="first"))

Practical: Data Cleaning

Comprehensive Case

Example

import pandas as pd
import numpy as np

# Create simulated order data (containing duplicates)
orders = pd.DataFrame({
    "Order ID": ["O001", "O002", "O001", "O003", "O002", "O004"],
    "Customer ID": ["C001", "C002", "C001", "C003", "C002", "C004"],
    "Product": ["iPhone", "MacBook", "iPhone", "iPad", "AirPods", "Apple Watch"],
    "Amount": [8000, 12000, 8000, 5000, 1500, 3000],
    "Date": ["2024-01-01", "2024-01-02", "2024-01-01", "2024-01-03", "2024-01-02", "2024-01-04"]
})

print("Original order data:")
print(orders)
print()

# 1. Detect duplicate orders
print("Duplicate order detection:")
duplicates = orders[orders.duplicated(subset=["Order ID"], keep=False)]
print(duplicates)
print()

# 2. Remove completely duplicate orders
orders_clean = orders.drop_duplicates()
print("After removing complete duplicates:")
print(f"Original {len(orders)} rows -> {len(orders_clean)} rows")
print()

# 3. Remove duplicates by order ID, keep the newest
# Assume the last record is the newest
orders_clean = orders.sort_values("Date").drop_duplicates(
    subset=["Order ID"],
    keep="last"
).sort_index()

print("Remove duplicates by order ID (keep the newest):")
print(orders_clean)

Advanced Usage

Keeping Specific Rows by Group

Example

import pandas as pd

df = pd.DataFrame({
    "Customer": ["A", "A", "A", "B", "B", "B"],
    "Month": ["01", "02", "03", "01", "02", "03"],
    "Spending": [100, 200, 150, 300, 250, 280]
})

print("Original data:")
print(df)
print()

# Keep the month with the highest spending for each customer
print("The month with the highest spending for each customer:")
result = df.sort_values("Spending", ascending=False).drop_duplicates(
    subset=["Customer"],
    keep="first"
).sort_values("Customer")
print(result)
print()

# Keep the first record for each customer (by month)
print("The first record for each customer:")
result = df.sort_values("Month").drop_duplicates(
    subset=["Customer"],
    keep="first"
)
print(result)

Marking Duplicates Instead of Removing

Example

import pandas as pd

df = pd.DataFrame({
    "Name": ["Zhang San", "Li Si", "Zhang San", "Wang Wu", "Li Si"],
    "City": ["Beijing", "Shanghai", "Beijing", "Guangzhou", "Shenzhen"]
})

# Add a duplicate flag column
df["Is Duplicate"] = df.duplicated(subset=["Name"], keep=False)

print("Mark duplicate rows:")
print(df)
print()

# Or mark using groupby
df["Duplicate Count"] = df.groupby("Name")["Name"].transform("count")
df["Is First"] = ~df.duplicated(subset=["Name"], keep="first")

print("Group-wise duplicate count:")
print(df)

Notes

1. Distinguish between "complete duplicates" and "partial duplicates"

Complete duplicates mean all column values are the same, while partial duplicates mean specific columns are the same. Use thesubsetparameter to specify the columns for duplicate detection.

2. Choosing the retention strategy

Choose to keep the first, last, or none based on business requirements. Which one you keep may affect subsequent analysis results.

3. Data type impact

Data types are considered when detecting duplicates. For example, the integer 1 and the float 1.0 are treated as different.

Before removing duplicate data, it is recommended to first analyze the cause and distribution of duplicates to ensure that the removal operation does not lose important information.

Other Extensions