Keep the Latest Record per Key When Deduplicating with pandas

Exports from CRMs, HR systems and order platforms often contain history rather than current state: every time a customer's email or phone changed, a new row was written. Pivot or merge that sheet as it is and each customer is counted several times, with several emails. The fix is to keep one row per key — the most recent — and the textbook one-liner does it. What the one-liner does not do is protect you from ties on the timestamp, dates stored as text, rows with no date at all, or the tempting groupby().last(), which returns something that looks right and is not. This page, part of Cleaning Excel Data With Pandas, handles each case.

Prerequisites

pip install pandas openpyxl

customers.xlsx has seven rows for three customers. C1 has three versions, one of them undated; C2's two versions share a timestamp and differ only in a seq column; C3's second date is written day-first as text, 15/03/2026. Some versions have missing emails or phones.

Step 1 — Make the timestamp a real date

Sorting only works on real dates. A column that mixes ISO dates and day-first text sorts as strings, where "15/03/2026" comes before "2026-01-05" because 1 sorts before 2. Parse it first, stating the formats you expect:

import pandas as pd

hist = pd.read_excel("customers.xlsx")
print(hist["updated"].tolist())

iso = pd.to_datetime(hist["updated"], format="%Y-%m-%d", errors="coerce")
dayfirst = pd.to_datetime(hist["updated"], format="%d/%m/%Y", errors="coerce")
hist["updated"] = iso.fillna(dayfirst)
print(hist[["customer_id", "updated"]].to_string(index=False))

Trying each known format and combining the results is stricter than format="mixed", which would guess — and could read 03/04/2026 as either March or April. The one row with no date stays NaT. Parse Excel Dates into Python datetimes with pandas covers date parsing in depth.

Step 2 — Sort, then keep the last row per key

With real dates, the standard approach is to sort by key and date and keep the last row of each key. Decide where undated rows go: sort_values puts NaT last by default, which would make C1's undated typo the "latest" version:

latest_wrong = (hist.sort_values(["customer_id", "updated"])
                    .drop_duplicates("customer_id", keep="last"))
print(latest_wrong[["customer_id", "updated", "email"]].to_string(index=False))

latest = (hist.sort_values(["customer_id", "updated", "seq"], na_position="first")
              .drop_duplicates("customer_id", keep="last"))
print(latest[["customer_id", "updated", "seq", "email", "phone"]].to_string(index=False))

The first result keeps ada@typo for C1. With na_position="first", undated rows count as the oldest, and C1's March row wins. Adding seq to the sort is the tie-breaker for C2, whose two rows share a date: without it, which one survives depends on the order the rows happened to arrive in. With it, the higher sequence number wins every time.

Step 3 — Do not use groupby().last() for this

groupby("customer_id").last() looks like a shorter way to write the same thing. It is not: it returns the last non-missing value of each column separately, so a record's fields can come from different rows:

mixed = hist.sort_values(["customer_id", "updated", "seq"], na_position="first").groupby("customer_id").last()
print(mixed[["email", "phone"]])

C2's newest row has phone 555-0202, and so does the result — but C1's newest row has no phone, so last() reaches back and takes 555-0101 from January, pairing it with March's email. C3 gets a phone from its February row. None of these combinations existed as a record. If you want row semantics — the latest version of each customer as it was recorded — use drop_duplicates or idxmax. If you want field semantics — the most recent known value of each field — last() is right, but that is a different, deliberate operation, and the result should be labelled as such.

Step 4 — Or select rows with idxmax

An equivalent formulation picks the index of the maximum date per key and selects those rows. It avoids sorting the whole frame and reads as "the row with the latest date":

dated = hist.dropna(subset=["updated"])
idx = dated.sort_values("seq").groupby("customer_id")["updated"].idxmax()
print(hist.loc[idx, ["customer_id", "updated", "seq", "email"]].to_string(index=False))

idxmax returns the first index of the maximum, so sorting by seq beforehand makes the tie-break deterministic in the other direction — here it keeps C2's seq 1 row. That difference is exactly why a tie-breaker has to be chosen, not left to whichever method you happened to use. Undated rows are dropped before idxmax, which cannot compare NaT.

Take a snapshot as of a past date

The same technique answers "what did the customer list look like at the end of February?" — useful when a report for a past period must use the details that were current then, not today's. Filter to versions recorded on or before the date, then deduplicate:

def as_of(history, when):
    cutoff = pd.Timestamp(when)
    known = history[history["updated"].notna() & (history["updated"] <= cutoff)]
    return (known.sort_values(["customer_id", "updated", "seq"])
                 .drop_duplicates("customer_id", keep="last"))

print(as_of(hist, "2026-02-28")[["customer_id", "updated", "email"]].to_string(index=False))

At the end of February, C1's current email was still ada@old.example and C3's was missing — their later versions did not yet exist. Customers with no version before the cutoff simply do not appear, which is correct: they were not customers yet. Undated rows are excluded from snapshots entirely, since there is no way to know when they applied.

Step 5 — Report ties you cannot break

When there is no sequence number or other tie-breaker, surface the ties instead of letting an arbitrary row win:

ties = hist[hist.duplicated(["customer_id", "updated"], keep=False) & hist["updated"].notna()]
print(ties[["customer_id", "updated", "email"]].to_string(index=False))

C2's two rows share a timestamp. With seq available the tie is resolved; without it, list such keys for review. They often point at a real problem in the source — two systems writing the same record at the same moment, or a timestamp stored only to the day.

Step 6 — Keep the history you discarded

Deduplication throws information away, and someone will ask for it. Write the latest records and the superseded ones to separate sheets, so the result is auditable:

superseded = hist.drop(index=latest.index)
with pd.ExcelWriter("customers_latest.xlsx") as writer:
    latest.sort_values("customer_id").to_excel(writer, sheet_name="Current", index=False)
    superseded.sort_values(["customer_id", "updated"], na_position="first").to_excel(writer, sheet_name="History", index=False)
print(pd.read_excel("customers_latest.xlsx", sheet_name=None)["Current"].shape,
      pd.read_excel("customers_latest.xlsx", sheet_name=None)["History"].shape)

Three current records and four historical ones: together they account for all seven input rows, which is a quick sanity check to assert in a job: if the two sheets do not add up to the input, a row was lost or duplicated somewhere along the way. Pandas: Drop Duplicates From an Excel Column covers duplicates that are true copies rather than versions.

Common pitfalls

• Sorting text dates. They sort alphabetically; parse first.
• Undated rows winning. NaT sorts last by default; use na_position="first" or exclude them.
• No tie-breaker. Equal timestamps leave the winner to chance.
• groupby().last() for records. It builds rows that never existed.
• Deduplicating before filtering. When a report covers one region or one period, apply those filters to the history first if the latest version depends on them — the newest row overall may belong to a different region than the newest row within it.
• Forgetting the key's own cleanliness. " C1" and "C1" are different keys; strip and normalise IDs first.

Performance notes

Sorting and drop_duplicates are both vectorised and handle millions of rows in seconds. idxmax avoids sorting the whole frame, which helps a little on very large histories. Parsing text dates with explicit formats is much faster than format="mixed", which tries each value individually.

Conclusion

Parse the timestamp into real dates with explicit formats, sort by key, timestamp and a tie-breaker with undated rows placed first, and keep the last row per key with drop_duplicates(keep="last") — or select rows with idxmax and an explicit tie rule. Avoid groupby().last() for records, because it assembles fields from different rows. Report ties you cannot break, and keep the superseded rows on their own sheet.

Frequently asked questions

What is the quickest way to keep the latest row per ID? Sort by the ID and the timestamp, then call drop_duplicates(subset="id", keep="last"). After the sort, the last row of each ID is the most recent one.

Why not use groupby("id").last()? Because it returns the last non-missing value of each column separately. If the newest row has no phone number, you get the phone from an older row next to the email from the newest one — a record that never existed.

What if two rows have the same timestamp? Add a tie-breaker to the sort, such as a sequence number or the source row order, so the result does not depend on how pandas happens to order equal values. Report ties if there is no sensible tie-breaker.

How are rows with no date treated? sort_values puts missing values last by default, so an undated row would be kept as the "latest". Decide explicitly: pass na_position="first" to rank undated rows as oldest, or exclude and report them.

Related

• Up one level: Cleaning Excel Data With Pandas — the full cleaning workflow.
• Find Duplicate Rows in Excel with Python — finding duplicates before deciding what to keep.
• Update One Excel File from Another with pandas — applying newer values to a master list.
• Match Rows by Nearest Date with pandas merge_asof — picking the right version at a point in time.