pandas drop_duplicates() — one row per key, not one row per event

$ import pandas as pd

crm = pd.DataFrame({
    "contact_id": [7, 3, 7, 12, 3, 21],
    "name":       ["Ada", "Bo", "Ada", "Cy", "Bo", "Dee"],
    "revenue":    [1200, 800, 1350, 400, 800, 2100],
    "updated":    pd.to_datetime(["2026-09-01", "2026-09-02", "2026-10-01",
                                  "2026-09-20", "2026-10-04", "2026-09-11"]),
})

crm = crm.drop_duplicates(subset="contact_id", keep="last", ignore_index=True)
print(crm.to_string())

   contact_id name  revenue    updated
0           7  Ada     1350 2026-10-01
1          12   Cy      400 2026-09-20
2           3   Bo      800 2026-10-04
3          21  Dee     2100 2026-09-11

The everyday shape of the call: dedupe on the business key, keep the newest snapshot, reset the index so nothing downstream trips over the gaps. ignore_index does not sort — contact 12 keeps its arrival position, only the labels are rebuilt. The surviving rows for contacts 7 and 3 are the ones with the higher revenue, because this export appends updates instead of overwriting them.

$ sep = pd.DataFrame({"ticket": ["T-91", "T-92", "T-93"],
                     "region": ["North", "South", "North"],
                     "minutes": [45, 90, 45]})
oct_ = pd.DataFrame({"ticket": ["T-91", "T-93", "T-94"],
                     "region": ["North", "North", "West"],
                     "minutes": [45, 45, 120]})
raw = pd.concat([sep, oct_], ignore_index=True)

print("rows:", len(raw), "| exact duplicates:", int(raw.duplicated().sum()))
print(raw[raw.duplicated(keep=False)].to_string())
print(raw.drop_duplicates(ignore_index=True).to_string())

rows: 6 | exact duplicates: 2
  ticket region  minutes
0   T-91  North       45
2   T-93  North       45
3   T-91  North       45
4   T-93  North       45
  ticket region  minutes
0   T-91  North       45
1   T-92  South       90
2   T-93  North       45
3   T-94   West      120

Two monthly exports stacked with concat, and two rows came back twice. keep=False in duplicated() is the audit: it prints all four rows involved, both copies, so you can see which tickets carried over instead of trusting a count. Only then does drop_duplicates() remove the second occurrence — T-91 and T-93 keep their September position, T-94 is genuinely new.

$ ledger = pd.DataFrame({
    "invoice":  ["INV-01", "INV-01", "INV-02", "INV-03", "INV-03"],
    "customer": ["Nord", "Nord", "Nord", "Ost", "Ost"],
    "net":      [1200.0, 1200.0, 480.0, 950.0, 950.0],
})

clean = ledger.drop_duplicates(subset="invoice")
print(f"naive {ledger['net'].sum():.2f} -> real {clean['net'].sum():.2f}"
      f" (overstated by {ledger['net'].sum() - clean['net'].sum():.2f})")
print(clean.to_string(index=False))

naive 4780.00 -> real 2630.00 (overstated by 2150.00)
invoice customer    net
 INV-01     Nord 1200.0
 INV-02     Nord  480.0
 INV-03      Ost  950.0

Why the first line of a revenue report is worth five seconds of suspicion: two re-submitted invoices inflated the total by 2,150.00. subset='invoice' alone makes INV-01 and INV-03 duplicates even though their other columns agree too — pick the key that defines the entity and drop_duplicates() collapses it in one pass, no groupby-sum dance required.

$ snapshots = pd.DataFrame({
    "account":  ["A-10", "A-10", "A-11", "A-11", "A-12"],
    "plan":     ["basic", "pro", "basic", "basic", "pro"],
    "mrr":      [29, 99, 29, 29, 99],
    "taken_at": pd.to_datetime(["2026-07-01", "2026-09-15", "2026-08-01",
                                "2026-09-20", "2026-06-11"]),
})

current = (snapshots.sort_values("taken_at", ascending=False)
                    .drop_duplicates(subset="account", keep="first"))
print(current.to_string(index=False))

account  plan  mrr   taken_at
   A-11 basic   29 2026-09-20
   A-10   pro   99 2026-09-15
   A-12   pro   99 2026-06-11

The current-state pattern: sort newest-first, then keep='first' — same result as keep='last' on an ascending sort, but it reads as what it is. A-10 upgraded basic to pro 99 and A-11 downgraded pro to basic 29; both rows exist in the log, only the newest survives. Ties on taken_at are broken by row order, because the dedupe is stable and never sorts.

$ import pandas as pd
import numpy as np

print("np.nan == np.nan ->", np.nan == np.nan)
gaps = pd.DataFrame({"site": ["A", "A", "B"], "pm25": [12.0, np.nan, np.nan]})
print("NaN rows counted as duplicates:", gaps.duplicated(subset="pm25").tolist())

lists = pd.DataFrame({"tags": [["a", "b"], ["a", "b"], ["c"]], "n": [1, 1, 2]})
print("one column of lists ->", len(lists[["tags"]].drop_duplicates()), "rows")
try:
    lists.drop_duplicates()
except TypeError as e:
    print("two columns of lists -> TypeError:", e)

cols = pd.DataFrame({"a": [1, 1], "b": [9, 9]})
try:
    cols.drop_duplicates(cols="a")
except TypeError as e:
    print("old keyword -> TypeError:", e)

np.nan == np.nan -> False
NaN rows counted as duplicates: [False, False, True]
one column of lists -> 2 rows
two columns of lists -> TypeError: unhashable type: 'list'
old keyword -> TypeError: DataFrame.drop_duplicates() got an unexpected keyword argument 'cols'

Three failure modes worth knowing before you trust a dedupe: NaN is considered equal to itself here even though Python says otherwise; a frame of list cells is fine on one column and dies on two with TypeError: unhashable type: 'list'; and any tutorial older than May 2014 that taught cols= raises TypeError immediately, because the parameter has been subset since 0.14.0.