Finding & Removing Duplicates

Duplicate rows creep in when files are merged, when a scraper runs twice, or when someone copy-pastes in Excel. If the same IPL match appears twice, every "matches won" count and every average is wrong. Removing duplicates is a standard step in every cleaning workflow — but you must first decide what counts as a duplicate and which copy to keep.

Definition

A duplicate is a row whose values are identical to an earlier row — either in every column (an exact duplicate) or only in a chosen set of key columns (a key duplicate, e.g. the same match_id). duplicated() marks duplicates with True, and drop_duplicates() returns a copy with them removed.

• Identical to an earlier row — by default the first occurrence is the "original" (False) and later copies are duplicates (True).
• Every column vs key columns — subset chooses which columns define "same".
• Marks / removes — duplicated() gives a boolean Series you can inspect; drop_duplicates() actually removes.

Syntax

df.duplicated(subset=None, keep="first")
df.drop_duplicates(subset=None, keep="first", inplace=False, ignore_index=False)
series.duplicated(keep="first")
series.drop_duplicates()

Example — exact duplicates

results = pd.DataFrame({
    "match_id": [101, 102, 102, 103, 104, 104],
    "winner":   ["MI", "CSK", "CSK", "RCB", "KKR", "DC"],
    "margin":   [10, 5, 5, 22, 7, 7],
})
print(results)
print(results.duplicated())
print("Exact duplicates:", results.duplicated().sum())

   match_id winner  margin
0       101     MI      10
1       102    CSK       5
2       102    CSK       5
3       103    RCB      22
4       104    KKR       7
5       104     DC       7
0    False
1    False
2     True
3    False
4    False
5    False
dtype: bool
Exact duplicates: 1

How it works: row 2 is identical to row 1 in all three columns, so it is marked True. Rows 4 and 5 share match_id 104 but have different winners, so they are not exact duplicates.

Example — key duplicates with subset

A match ID must be unique. Two rows with ID 104 means a data-entry error even if the other columns differ:

print(results.duplicated(subset=["match_id"]))
print(results[results.duplicated(subset=["match_id"], keep=False)])

0    False
1    False
2     True
3    False
4    False
5     True
dtype: bool
   match_id winner  margin
1       102    CSK       5
2       102    CSK       5
4       104    KKR       7
5       104     DC       7

How it works: subset=["match_id"] compares only that column. keep=False marks every row that has a repeat — perfect for showing all conflicting copies side by side before deciding what to do.

Example — the three keep options

print(pd.DataFrame({
    "match_id": results["match_id"],
    "first": results.duplicated(subset="match_id", keep="first"),
    "last":  results.duplicated(subset="match_id", keep="last"),
    "False": results.duplicated(subset="match_id", keep=False),
}))

   match_id  first   last  False
0       101  False  False  False
1       102  False   True   True
2       102   True  False   True
3       103  False  False  False
4       104  False   True   True
5       104   True  False   True

Example — removing duplicates

print(results.drop_duplicates())
print()
print(results.drop_duplicates(subset="match_id", keep="last", ignore_index=True))

   match_id winner  margin
0       101     MI      10
1       102    CSK       5
3       103    RCB      22
4       104    KKR       7
5       104     DC       7

   match_id winner  margin
0       101     MI      10
1       102    CSK       5
2       103    RCB      22
3       104     DC       7

How it works: the first call removes only the exact copy (row 2). The second treats match_id as the key and keeps the last entry for each ID — useful when later rows are corrections. ignore_index=True renumbers the result.

Duplicates in a single column

teams = pd.Series(["MI", "CSK", "MI", "RCB", "CSK"])
print(teams.duplicated().tolist())
print(teams.drop_duplicates().tolist())
print(list(teams.unique()), teams.nunique())

[False, False, True, False, True]
['MI', 'CSK', 'RCB']
['MI', 'CSK', 'RCB'] 3

drop_duplicates() keeps a Series (with its index); unique() returns an array of the distinct values (wrapped in list() here for clean printing); nunique() counts them.

Text duplicates that hide

"Mumbai" and "mumbai " look the same to a human but not to pandas. Clean text first (strip spaces, fix case), then drop duplicates:

cities = pd.DataFrame({"city": ["Mumbai", "mumbai ", "Delhi", "MUMBAI"]})
print("before:", cities.duplicated().sum())
cities["city"] = cities["city"].str.strip().str.title()
print("after cleaning:", cities.duplicated().sum())
print(cities.drop_duplicates())

before: 0
after cleaning: 2
     city
0  Mumbai
2   Delhi

Common mistakes

• ❌ df.drop_duplicates() without reassigning. ✅ df = df.drop_duplicates().
• ❌ Checking only exact duplicates when a key must be unique. ✅ Also check duplicated(subset=["match_id"]).
• ❌ Dropping duplicates before fixing case/spaces. ✅ Standardise text first, then de-duplicate.
• ❌ Using keep=False in drop_duplicates by accident — it removes all copies, including the original.

Comparison — duplicated vs drop_duplicates vs unique

Interview tip

Q: How do you keep the most recent record for each player? A: Sort by the date column, then df.drop_duplicates(subset="player", keep="last").