Topic 8.5: Resolving Duplicates

A national postal service merges records from two regional offices. After the merge, many addresses appear twice — some because both offices served the same street, others because an export script ran twice during the migration. For each duplicated address, the registry must decide which copy to keep. In most cases the newer record is more accurate — it reflects recent building renames or postcode corrections. But for some records, the newer one introduced a typo, making the older one correct.

This is the practical reality of deduplication: it is not sufficient to 'just remove duplicates'. You must decide which copy to keep, whether to match on all columns or only the key identifier columns, and how to handle cases where duplicates are not entirely identical. Pandas provides exactly the parameters needed to make each of these decisions with precision.

Before any row is deleted, the analyst should inspect the duplicate records. The keep=False option on .duplicated() marks every occurrence of any duplicated row — including the first — making it easy to compare each pair side-by-side.

import pandas as pd

df = pd.read_csv('../Datasets/8_4_competition_results.csv')

# Detect duplicates — True means the row is a duplicate
df.duplicated()

The boolean mask alone is hard to read at this size. Filtering the DataFrame down to only the duplicated rows makes the actual content of the duplication visible.

# Show only the duplicate rows
df[df.duplicated()]

All 7 duplicates are exact copies across all 7 columns — every field matches between the pair. This is consistent with a system export error (the export script ran twice), not with legitimate repeat entries. The correct action is to remove the second occurrence and keep the first.

The .drop_duplicates() method removes duplicate rows. Its default behaviour — keep='first' — retains the first occurrence of each duplicated set and removes all subsequent copies. This is the most common use case when the original entry is assumed to be the correct one.

# Remove duplicates — keep the FIRST occurrence (default behavior)
df_clean = df.drop_duplicates(keep='first')

print(f"Rows before: {len(df)}")
print(f"Rows after:  {len(df_clean)}")

The DataFrame now has 200 rows — exactly as expected (207 original rows minus 7 duplicate copies). All 7 duplicate rows were removed, and the verification confirms zero duplicates remain. The original df is unchanged; the cleaned result is stored in df_clean.

The keep parameter gives precise control over which copy of each duplicated group is retained. The three options are 'first', 'last', and False. Each serves a different scenario:

# Keep the LAST occurrence instead
df_clean_last = df.drop_duplicates(keep='last')

print(f"Rows after keep=last: {len(df_clean_last)}")

# Remove ALL copies — keep none of the duplicated rows
df_no_dupes = df.drop_duplicates(keep=False)

print(f"Rows remaining: {len(df_no_dupes)}")

With keep='first' or keep='last': 200 rows remain (7 duplicates removed, first or last copy kept). With keep=False: 193 rows remain — all 14 rows involved in duplication (7 originals + 7 copies) are deleted. This leaves only rows that were never duplicated at all. Use keep=False when you want to study only the rows that have no duplicates whatsoever — not the typical deduplication use case.

The subset parameter restricts duplicate detection to a specific set of columns rather than all columns. This is essential when records that should be considered duplicates are not perfectly identical across every field — for example, two rows with the same student name, age, and city but a slightly different timestamp.

In the W8 dataset, a useful subset check is whether any student's core identifying information — name and city — appears twice, regardless of whether other fields match exactly:

# Detect duplicates based on specific columns only
df.duplicated(subset=['name', 'city'])

This flags more rows than the full-row check: 203 students share both a name and a city with another row, even though their score, grade, level, or date may differ. Removing these rows with drop_duplicates on the same subset narrows the dataset down to one row per unique name+city combination:

# Remove duplicates based on student name AND school — keep first
df_clean = df.drop_duplicates(
    subset=['name', 'city'],
    keep='first'
)

print(f"Rows before: {len(df)}")
print(f"Rows after:  {len(df_clean)}")

Note that subset deduplication on name+city removes 23 rows — far more than the 7 full-row duplicates. This is expected: many distinct students happen to share a name and city by coincidence, even though their score, grade, level, and date differ. This is exactly why the subset parameter must be chosen deliberately, matched to what truly constitutes a "duplicate" for the data at hand, rather than applied automatically.

A professional deduplication workflow combines all the methods seen in this topic into a single, verifiable pipeline. The structure: measure before, remove, measure after, verify. This three-step verification ensures no unexpected rows were removed and no duplicates remain.

import pandas as pd

# Full deduplication workflow with verification
df = pd.read_csv('../Datasets/8_4_competition_results.csv')

# Step 1: Measure before
print(f"=== BEFORE DEDUPLICATION ===")
print(f"Total rows:          {len(df)}")
print(f"Duplicate rows:      {df.duplicated().sum()}")
print(f"Unique rows:         {len(df) - df.duplicated().sum()}")

# Step 2: Remove duplicates (keep first occurrence)
df_clean = df.drop_duplicates(keep='first')

# Step 3: Measure after and verify
print(f"\n=== AFTER DEDUPLICATION ===")
print(f"Total rows:          {len(df_clean)}")
print(f"Duplicate rows:      {df_clean.duplicated().sum()}")
print(f"Rows removed:        {len(df) - len(df_clean)}")
print(f"\nVerification: {len(df) - len(df_clean)} duplicates removed — expected 7")

The verification confirms: 7 rows removed, 200 rows remain, 0 duplicates in the result. All three checks pass. The dataset is ready to proceed to missing value handling (Topic 8.6).