Duplicates, keys and the latest record

Two rows are duplicates when they record the same real thing more than once: a payment loaded twice, one customer under two spellings, the same update sent by two systems. Duplicates inflate counts and totals, and a join on a repeated key multiplies rows without any warning. pandas finds rows that are copies of an earlier row with duplicated(), rows that repeat a key, the columns that should identify one row, with duplicated(subset=key), and keeps one row per group with drop_duplicates().

Deleting is the easy part. The decisions that change your answer come first: which columns form the key, which row of a group to keep when the rows differ, and whether a repeat is an error at all, because two customers can buy the same thing on the same day. This lesson takes those decisions one at a time and ends with the checks that keep a key honest before every join.

Exact duplicates and duplicates by key

import pandas as pd

payments = pd.DataFrame({
    "payment_id": ["P1", "P2", "P2", "P3", "P4", "P4", "P4"],
    "student": ["Asha", "Ben", "Ben", "Chen", "Dev", "Dev", "Dev"],
    "amount": [1200, 950, 950, 600, 700, 700, 750],
})

# Exact duplicates: every column equal to an earlier row.
print("Exact copies:", payments.duplicated().tolist())

# Duplicates by key: payment_id should identify one payment.
key = ["payment_id"]
print("Repeated keys:", payments.duplicated(subset=key).tolist())

# keep=False marks every member of a duplicate group, so you can review whole groups.
print(payments[payments.duplicated(subset=key, keep=False)].to_string())

# How big are the groups?
sizes = payments.groupby(key).size().value_counts().sort_index()
print(sizes.rename_axis("rows per key").rename("keys").to_string())
print("Rows after drop_duplicates():", len(payments.drop_duplicates()), "of", len(payments))

Exact copies: [False, False, True, False, False, True, False]
Repeated keys: [False, False, True, False, False, True, True]
  payment_id student  amount
1         P2     Ben     950
2         P2     Ben     950
4         P4     Dev     700
5         P4     Dev     700
6         P4     Dev     750
rows per key
1    2
2    1
3    1
Rows after drop_duplicates(): 5 of 7

Row 2 is a full copy of row 1, and row 5 of row 4, so duplicated() marks those two. Row 6 has the same payment_id as rows 4 and 5 but a different amount: only the key test catches it. With the default keep="first", the first row of each group counts as the original and later rows are marked; keep="last" marks all but the last, and keep=False marks every member of a group, which is what you want when you review groups before deciding.

drop_duplicates() with no subset removes the two exact copies and keeps P4 twice, with 700 and with 750. That is the right first step only if exact copies are known to be mistakes. Whether P4 was paid once or twice, and how much, is a question for whoever owns the payments, and the table of group sizes tells you how many such questions you have.

Keep the latest record per key

Logs of changes, such as address updates, hold many rows per customer, and the analysis usually wants the current one. pandas gives two common ways to keep the latest row per key:

import pandas as pd

log = pd.read_csv("address_log.csv", parse_dates=["updated_at"])
log["row"] = range(len(log))  # the order rows arrived in: the tie-breaker

# Way 1: sort, then keep the last row of each customer.
by_sort = log.sort_values(["customer_id", "updated_at"]).drop_duplicates("customer_id", keep="last")

# Way 2: the position of each customer's latest timestamp.
by_max = log.loc[log.groupby("customer_id")["updated_at"].idxmax()]

print(by_sort[["customer_id", "updated_at", "city", "row"]].to_string(index=False))
print(by_max[["customer_id", "updated_at", "city", "row"]].to_string(index=False))

# C101 has two updates at the same minute: each way picks a different one. Make the rule explicit.
latest = log.sort_values(["customer_id", "updated_at", "row"]).drop_duplicates("customer_id", keep="last")
dropped = log.drop(latest.index)
print("Kept:", latest["city"].tolist())
print("Dropped rows:", sorted(dropped["row"].tolist()))

customer_id          updated_at    city  row
       C101 2026-04-18 16:05:00  Nashik    5
       C102 2026-05-01 12:00:00 Udaipur    4
       C103 2026-03-09 11:20:00   Kochi    6
customer_id          updated_at    city  row
       C101 2026-04-18 16:05:00  Nagpur    2
       C102 2026-05-01 12:00:00 Udaipur    4
       C103 2026-03-09 11:20:00   Kochi    3
Kept: ['Nashik', 'Udaipur', 'Kochi']
Dropped rows: [0, 1, 2, 3]

customer_id,updated_at,city,source
C101,2026-03-02 09:15,Pune,app
C102,2026-03-02 10:40,Jaipur,branch
C101,2026-04-18 16:05,Nagpur,app
C103,2026-03-09 11:20,Kochi,app
C102,2026-05-01 12:00,Udaipur,branch
C101,2026-04-18 16:05,Nashik,call centre
C103,2026-03-09 11:20,Kochi,app

Sorting by key and time and then keeping the last row of each key, and asking groupby(...)["updated_at"].idxmax() for the position of each key’s latest time, agree for C102, and for C103 they pick different copies of the same row. For C101 they disagree: two updates arrived in the same minute, Nagpur from the app and Nashik from the call centre, and idxmax() returns the first row that holds the maximum while the sort keeps the later one. Neither is wrong; each applies a tie rule nobody wrote down, and the customer’s city depends on which method a colleague happens to use.

The cure is to make the tie rule part of the sort. Here a row column records the order the rows arrived in, and sorting by ["customer_id", "updated_at", "row"] makes “the most recently received update wins” explicit; the same input now gives the same answer every time. Other rules are just as valid, such as preferring one source over another, as long as the rule is written down. Print what you drop: four rows went, including C103’s exact copy.

Normalise the key before you compare

People and systems type the same value in different ways, so equal keys can look different:

import pandas as pd

contacts = pd.DataFrame({
    "name": [" Ravi Kumar", "ravi  kumar", "Meera Iyer", "MEERA IYER ", "Meera Iyer"],
    "email": ["Ravi.K@Example.com", "ravi.k@example.com ", "meera@example.org", "meera@example.org", "m.iyer@example.org"],
})
print("Exact duplicates:", int(contacts.duplicated().sum()))

# Build a comparison key in a new column; keep the original text untouched.
contacts["email_key"] = contacts["email"].str.strip().str.casefold()
contacts["name_key"] = contacts["name"].str.strip().str.casefold().str.replace(r"\s+", " ", regex=True)

print("Same email:", int(contacts.duplicated(subset=["email_key"]).sum()))
print("Same name:", int(contacts.duplicated(subset=["name_key"]).sum()))
print(contacts.to_string())

Exact duplicates: 0
Same email: 2
Same name: 3
          name                email           email_key    name_key
0   Ravi Kumar   Ravi.K@Example.com  ravi.k@example.com  ravi kumar
1  ravi  kumar  ravi.k@example.com   ravi.k@example.com  ravi kumar
2   Meera Iyer    meera@example.org   meera@example.org  meera iyer
3  MEERA IYER     meera@example.org   meera@example.org  meera iyer
4   Meera Iyer   m.iyer@example.org  m.iyer@example.org  meera iyer

No two rows are exact duplicates, yet after stripping spaces and case-folding, two emails repeat. Build the comparison key in a new column and leave the original text alone, so that the data shows what was received and the key shows how you compared it. str.casefold() exists for caseless matching and removes more case differences than lower(); collapsing runs of spaces with a regular expression handles “ravi kumar”.

The name key finds three repeats where the email key finds two, and the difference is the warning: row 4 is a Meera Iyer with a different address, who may well be a different person. Names make poor keys on their own because people share them. Match on identifiers people do not share, such as an email or an account number, and send the cases that match only on a name to a person for review.

Test the key before you join

A key is only a key if it is unique, and a join is the place where a repeated key does the most damage:

import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3], "product": ["tea", "rice", "tea"], "qty": [2, 1, 5]})
# The price list got tea's new price appended instead of replacing the old one.
prices = pd.DataFrame({"product": ["tea", "rice", "tea"], "price": [40, 60, 42]})

# Three ways to test a key before trusting it.
print("is_unique:", prices["product"].is_unique)
print("repeated keys:", int(prices.duplicated(subset=["product"]).sum()))
print(prices[prices.duplicated(subset=["product"], keep=False)].to_string(index=False))

# A join on a repeated key multiplies rows: 3 orders become 5 rows.
joined = orders.merge(prices, on="product")
print("rows before:", len(orders), "| after the join:", len(joined))
current = prices.drop_duplicates("product", keep="last")  # one price per product: the newest
right = orders.merge(current, on="product", validate="many_to_one")
print("revenue with one price per product:", int((right["qty"] * right["price"]).sum()))
print("revenue after the bad join:", int((joined["qty"] * joined["price"]).sum()))

# validate= makes pandas check the key and refuse the join.
try:
    orders.merge(prices, on="product", validate="many_to_one")
except pd.errors.MergeError as e:
    print("MergeError:", str(e).splitlines()[0])

is_unique: False
repeated keys: 1
product  price
    tea     40
    tea     42
rows before: 3 | after the join: 5
revenue with one price per product: 354
revenue after the bad join: 634
MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

is_unique, duplicated(subset=...).sum() and the rows marked with keep=False all say the same thing: tea is listed twice, because a new price was appended instead of replacing the old one. Joining 3 orders to that list returns 5 rows, and revenue jumps from 354 to 634 although nobody bought anything more. validate="many_to_one" asks pandas to check that the right-hand key is unique and to raise MergeError instead of joining; put it on every merge where you know the relationship. The other values are "one_to_one", "one_to_many" and "many_to_many" (which checks nothing).

Missing keys need their own check, because pandas and SQL treat them differently:

import duckdb
import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3, 4], "customer_id": ["C1", None, "C2", None]})
customers = pd.DataFrame({"customer_id": ["C1", "C2", None], "segment": ["retail", "trade", "unknown"]})

# pandas matches a missing key with a missing key: both orders without a customer get the "unknown" row.
print(orders.merge(customers, on="customer_id", how="left").to_string(index=False))

# SQL never matches NULL to NULL, so DuckDB leaves those two orders without a segment.
print(duckdb.sql("SELECT * FROM orders LEFT JOIN customers USING (customer_id) ORDER BY order_id").df().to_string(index=False))

# A key with gaps is not a key: count them before the join.
print("orders without a customer:", int(orders["customer_id"].isna().sum()))
print("customers without an id:", int(customers["customer_id"].isna().sum()))

 order_id customer_id segment
        1          C1  retail
        2         NaN unknown
        3          C2   trade
        4         NaN unknown
 order_id customer_id segment
        1          C1  retail
        2         NaN     NaN
        3          C2   trade
        4         NaN     NaN
orders without a customer: 2
customers without an id: 1

pandas matches a missing key with a missing key, as its merge documentation warns, so both orders without a customer pick up the “unknown” customer’s segment. In DuckDB any comparison with NULL, NULL = NULL included, gives NULL rather than true, so the join leaves those orders without a segment. Count the missing keys on both sides before a join, and decide what they should match.

When the key is the index, df.index.is_unique tests it, and df.set_flags(allows_duplicate_labels=False) makes pandas raise as soon as an operation would create a repeated label. The pandas documentation calls that flag experimental, because not every method passes it on yet, so keep an explicit test as well.

True duplicates or real repeats?

Two identical rows are not always an error. In a sales table, two identical lines can be one line scanned twice or a customer who bought a second pack; in sensor data, two equal readings can be a stuck sensor or a stable room. Before removing repeats, check what would make them legitimate:

• The grain. If a table has one row per payment and a source-issued payment ID, two rows with that ID are errors. If it has one row per item scanned, identical rows can be real.
• The time. Copies loaded in the same second, or in two loads of the same file, point to a pipeline fault. Copies minutes apart are more likely to be real events.
• The owner. Whoever runs the source system knows whether it can emit the same record twice.

Real data makes this a judgement call. In the UCI Online Retail II data, 34,335 of the 1,067,371 rows are exact copies of an earlier row (67,242 rows belong to a group of identical rows), with the same invoice, product, quantity, minute and price. The data cannot say whether they are double scans or repeated purchases on one invoice. Record the choice in the data log with the count, report the result both ways if it matters, and ask the data owner. (Measured with pandas 3.0.6 on the full dataset.)

Exercise

FIELDS = ("name", "email", "phone")  # the fields that count for completeness


def email_key(email):
    """Return email without surrounding spaces, case-folded."""
    # Replace this line with your code.
    return email


def latest_contacts(rows):
    """Return one row per email_key: latest date, then most complete, then last in rows; in first-seen order."""
    # Replace these lines with your code.
    return list(rows)

import copy

from contacts import email_key, latest_contacts


def row(name, email, phone, updated):
    return {"name": name, "email": email, "phone": phone, "updated": updated}


def test_email_key():
    """strips spaces and case-folds the address"""
    assert email_key("  Ravi@Example.COM ") == "ravi@example.com"
    assert email_key("meera@example.org") == "meera@example.org"


def test_latest_date_wins():
    """keeps the row with the latest date for each person"""
    rows = [row("Asha", "asha@example.com", "91", "2026-01-05"), row("Asha R", "ASHA@example.com", "", "2026-02-01")]
    assert latest_contacts(rows) == [rows[1]]


def test_more_complete_wins_a_tie():
    """on the same date, keeps the row with more non-empty fields"""
    rows = [row("Ravi K", " ravi@example.com", "97", "2026-03-01"), row("Ravi", "Ravi@Example.com", "", "2026-03-01")]
    assert latest_contacts(rows) == [rows[0]]


def test_last_row_wins_a_full_tie():
    """when date and completeness tie, keeps the row that comes last"""
    rows = [row("Dev", "dev@example.com", "11", "2026-04-02"), row("Devraj", "dev@example.com", "12", "2026-04-02")]
    assert latest_contacts(rows) == [rows[1]]


def test_first_seen_order():
    """returns one row per person, in the order the people first appear"""
    rows = [
        row("Ravi", "Ravi@Example.com", "", "2026-03-01"),
        row("Meera", "meera@example.org", "98", "2026-02-11"),
        row("Ravi K", " ravi@example.com", "97", "2026-03-01"),
        row("Chen", "chen@example.net", "", "2026-01-20"),
        row("Meera Iyer", "Meera@example.org ", "98", "2026-01-30"),
    ]
    assert latest_contacts(rows) == [rows[2], rows[1], rows[3]]


def test_input_unchanged():
    """does not change the list or the dictionaries it is given"""
    rows = [row("Asha", "asha@example.com", "91", "2026-01-05"), row("Asha R", "ASHA@example.com", "", "2026-02-01")]
    before = copy.deepcopy(rows)
    latest_contacts(rows)
    assert rows == before


def test_empty():
    """returns an empty list for no rows"""
    assert latest_contacts([]) == []

Interview questions

What does validate="many_to_one" check in a pandas merge, and when should you use it?

It checks the relationship you expect before pandas joins: with "many_to_one", the key must be unique in the right-hand table, so each left row finds at most one partner. If the right table repeats a key, merge raises MergeError instead of quietly multiplying rows. "one_to_one" requires unique keys on both sides, "one_to_many" on the left, and "many_to_many" checks nothing. Use it on every join whose shape you know, typically facts joined to a lookup table such as orders to products or customers. A lookup table with one product listed twice would otherwise duplicate every order of that product and inflate revenue, with the row count as the only sign. Combine it with a count of missing keys, because pandas matches missing keys with each other.

Key takeaways

• duplicated() marks copies of earlier rows; duplicated(subset=key) marks repeats of a key; keep=False marks whole groups for review; drop_duplicates() keeps one row per group.
• Keeping the latest row per key needs a tie rule. Sorting with keep="last" and idxmax() disagree on ties; add a tie-breaker column to the sort and print what you drop.
• Compare on a normalised key column (strip, casefold(), collapse spaces) and keep the original text.
• Test keys before joins: is_unique, duplicated(subset=...), and validate= on merge. A repeated key on the lookup side multiplies rows and totals.
• pandas matches missing keys with each other in a merge; SQL does not. Count missing keys first.
• Identical rows can be real repeats. Decide with the grain, the timing and the data owner, and log the count.