Removing Duplicate Rows From a CSV and Choosing the Keeper

Every deduplication guide tells you how to remove the duplicates. The part that deserves the page is the part they skip: when two rows say they are the same customer and disagree about the details, which one is right? Keep the wrong one and a balance, an address or a signup date is silently wrong in every report that follows. This article builds the key correctly, finds the duplicates in a small file and a 200,000 row one, compares five ways of picking the keeper with numbers, and ends with the cases where the answer is to merge the rows instead. The shell version, for files without quoting, is in removing duplicate rows with shell tools, and the whole cleaning order is in the hub.

Duplicate of what?

There are three kinds of duplicate, and they need three different checks.

• Exact duplicates: every column identical. df.duplicated() finds them, and removing them is safe because nothing is lost. The small file has none: exact duplicate rows (all columns identical): 0.

• Key duplicates: the same customer, order or product with different details. This is what usually causes trouble, and it needs a key and a rule.

• Near duplicates: the same entity with a typo, a nickname or a different spelling. No exact key finds them; that is fuzzy matching, and I leave it out.

The small file has one key duplicate:

import sys, re
sys.path.insert(0, "/batches/csv-0001-0200/lab/clean")
import pandas as pd
from clean import *

def prepare(path):
    df, _, _ = drop_blank_and_footer(read_raw(path))
    df = df.apply(lambda c: c.str.replace(r"[​‌‍⁠]", "", regex=True).str.strip())
    df["key"] = df["email"].str.lower()
    df["signup"] = df["signup_date"].map(parse_date)
    df["amount"] = df["balance"].map(parse_money)
    return df

df = prepare("/batches/csv-0001-0200/lab/data/clean/customers-messy.csv")
dups = df[df.duplicated("key", keep=False)]
print("rows sharing an email once case and padding are ignored:")
print(dups[["id", "name", "email", "signup", "amount", "units"]].to_string(index=False))
print("exact duplicate rows (all columns identical):", int(df.drop(columns=["key", "signup", "amount"]).duplicated().sum()))
print("duplicates found by comparing the raw email text:", int(drop_blank_and_footer(read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy.csv"))[0]["email"].duplicated().sum()))

ordered = df.sort_values("signup", na_position="first")
strategies = {
    "first in file": df.drop_duplicates("key", keep="first"),
    "last in file": df.drop_duplicates("key", keep="last"),
    "latest signup": ordered.drop_duplicates("key", keep="last"),
    "earliest signup": df.sort_values("signup").drop_duplicates("key", keep="first"),
    "highest balance": df.assign(a=df["amount"].map(lambda v: float(v) if v is not None else float("-inf"))).sort_values("a").drop_duplicates("key", keep="last").drop(columns="a"),
}
print()
for name, kept in strategies.items():
    row = kept[kept["key"] == "[email protected]"].iloc[0]
    print(f"{name:16s} keeps id {row['id']} (signup {row['signup']}, balance {row['amount']}), {len(kept)} rows remain")

python /batches/csv-0001-0200/lab/clean/t0112.py

rows sharing an email once case and padding are ignored:
id      name            email     signup  amount units
 1 Ana Silva [email protected] 2025-03-14 1234.50 1,200
 5 Ana Silva [email protected] 2025-03-20 1300.00 1,200
exact duplicate rows (all columns identical): 0
duplicates found by comparing the raw email text: 0

first in file    keeps id 1 (signup 2025-03-14, balance 1234.50), 8 rows remain
last in file     keeps id 5 (signup 2025-03-20, balance 1300.00), 8 rows remain
latest signup    keeps id 5 (signup 2025-03-20, balance 1300.00), 8 rows remain
earliest signup  keeps id 1 (signup 2025-03-14, balance 1234.50), 8 rows remain
highest balance  keeps id 5 (signup 2025-03-20, balance 1300.00), 8 rows remain

Ids 1 and 5 are the same person: [email protected] and [email protected]. They signed up on 14 and 20 March, and their balances are 1234.50 and 1300.00. Comparing the raw text of the email finds nothing (duplicates found by comparing the raw email text: 0), because the case differs. The key has to be normalized before it is compared: trim, remove zero-width characters, lowercase. The trimming article covers why strip() alone is not enough.

Why the key matters more than the method

On the generated file, I compared two keys:

import sys, time
sys.path.insert(0, "/batches/csv-0001-0200/lab/clean")
import pandas as pd
from clean import *

df, _, _ = drop_blank_and_footer(read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv"))
df = df.apply(lambda c: c.str.strip())
df["key"] = df["email"].str.lower()
df["signup"] = df["signup_date"].map(parse_date)
df["amount"] = df["balance"].map(parse_money)
raw_dups = int(df["email"].duplicated().sum())
norm_dups = int(df["key"].duplicated().sum())
groups = df[df.duplicated("key", keep=False)].groupby("key").size()
print(f"rows {len(df)}, duplicates by raw email {raw_dups}, by lowercased email {norm_dups}")
print(f"duplicate groups {len(groups)}, sizes: {groups.value_counts().sort_index().to_dict()}")
def total(kept): return sum(float(v) for v in kept["amount"] if v is not None)
ordered = df.assign(s=pd.to_datetime(df["signup"])).sort_values("s", na_position="first")
res = {
    "no dedupe": df,
    "first in file": df.drop_duplicates("key", keep="first"),
    "last in file": df.drop_duplicates("key", keep="last"),
    "latest signup": ordered.drop_duplicates("key", keep="last"),
}
for name, kept in res.items():
    print(f"{name:14s} rows {len(kept):6d}  total balance {total(kept):>16,.2f}")
t = time.perf_counter()
_ = df.drop_duplicates("key", keep="first")
print(f"drop_duplicates time: {time.perf_counter() - t:.3f}s")

python /batches/csv-0001-0200/lab/clean/t0112b.py

rows 199778, duplicates by raw email 3038, by lowercased email 6070
duplicate groups 5907, sizes: {2: 5746, 3: 159, 4: 2}
no dedupe      rows 199778  total balance 1,309,379,177.89
first in file  rows 193708  total balance 1,270,213,946.61
last in file   rows 193708  total balance 1,269,635,208.07
latest signup  rows 193708  total balance 1,269,880,108.76
drop_duplicates time: 0.129s

With the raw (trimmed) email as the key, 3,038 rows were duplicates of an earlier row. With the lowercased email, 6,070. Half the duplicates were hiding behind a capital letter. Those 5,907 groups were 5,746 pairs, 159 triples and 2 groups of four. A dedupe that skips the normalization step reports success on 3,038 and leaves 3,032 behind, each of which double-counts a customer. After this, every count of "customers" in the data is too high by 1.5%, and the error would be invisible unless somebody compared with the source system.

Choosing the keeper

Once the key is right, drop_duplicates(subset="key", keep=...) (pandas reference) keeps one row per key. The keep argument is the decision, and the choices behave very differently:

On the small file, "first in file" kept id 1 (signup 2025-03-14, balance 1234.50), and "last in file", "latest signup" and "highest balance" kept id 5 (signup 2025-03-20, balance 1300.00). All five rules left 8 rows. So the same person appears with two different balances depending on a rule nobody wrote down. On the large file the effect scales:

• no dedupe: 199,778 rows, total balance 1,309,379,177.89

• first in file: 193,708 rows, 1,270,213,946.61

• last in file: 193,708 rows, 1,269,635,208.07

• latest signup: 193,708 rows, 1,269,880,108.76

All three rules removed the same 6,070 rows, and the totals differ by up to 578,738.54. The gap is small against a billion-dollar balance (0.05%), and a total that moves with an unstated tie-break is a total you cannot reconcile. Notice also that removing duplicates changed the total by 39.2 million: duplicates in a financial table are not a cosmetic issue.

Two details of the pandas code are worth getting right. drop_duplicates keeps the first or last row in the current order, so to keep "the latest" you must sort first, by the parsed date and not by the text of the date (strings like 15 Mar 2025 do not sort chronologically). And the sort has to handle missing values deliberately: I used na_position="first" so that a row with an unparseable date loses to one with a date.

The same rule in SQL

When the data is in DuckDB or a database, a window function expresses the keeper in the query:

bash /batches/csv-0001-0200/lab/clean/t0112c.sh

┌────┬───────────┬─────────────┬───────────┐
│ id │   name    │ signup_date │  balance  │
├────┼───────────┼─────────────┼───────────┤
│ 5  │ Ana Silva │ 2025-03-20  │ $1,300.00 │
└────┴───────────┴─────────────┴───────────┘

row_number() OVER (PARTITION BY k ORDER BY id DESC) = 1 picks the highest id in each group of equal normalized emails, here id 5, and count(*) OVER (...) > 1 shows only groups that had duplicates. Change the ORDER BY to change the rule: ORDER BY signup DESC for the latest, ORDER BY balance DESC for the highest. The rule is written next to the query, which is where it should live. QUALIFY (DuckDB reference) is the DuckDB, Snowflake and BigQuery clause for filtering on a window function; PostgreSQL needs a subquery.

Keep what you drop

A deduplication that throws rows away is a decision. Make it reversible. Write the dropped rows to a second file with the key and the id of the row that was kept, so a person can see why id 1 disappeared. In pandas, df[df.duplicated("key", keep="first")] is the dropped set for "keep first"; add a column with the kept row's id. A few lines, and the first time somebody asks "what happened to customer 1" you have the answer.

When the right answer is to merge

Sometimes neither row is right. Row 1 has a phone number and no email confirmation, row 5 has the email and no phone. Keeping either loses information. For those cases, group by key and combine: take the latest non-empty value for each column (groupby("key").last() in pandas takes the last non-null value per column, which is the pandas version of "latest non-empty"), and keep the earliest signup date. This is more code and a more honest result. It is also riskier: merging rows from two different people that happen to share a mailbox (a family address, a shared inbox such as info@) produces a person who never existed. Check how many groups have more than one distinct name before you merge.

How to tell it worked

Count the key duplicates before and after: after should be zero. Count the rows removed and compare with the number of duplicate groups' extra rows (199,778 − 193,708 = 6,070). Compare a total before and after and understand the difference. Check a few groups by hand: pick three keys with three or four rows and read all of them. Re-run the dedupe on its output; it should remove nothing. And confirm the dropped-rows file exists and has a reason.

When it is not a dedupe job

If the key is a name, it is not a key: two people called John Smith are not duplicates. If the duplicates are the product of a join that fanned out, fix the join. If the duplicate is an event (two orders on the same day for the same customer), it is data. And if you cannot say which row should win, ask the owner of the data, and write the answer in the cleaning script as a comment. The next steps in the series are fixing mixed date formats, which you need for a "latest" rule, and finding duplicate keys before an import.