pandas duplicated() — flag and count the rows that repeat

$ import pandas as pd

invoices = pd.DataFrame({
    "invoice_no": ["INV-9001", "INV-9002", "INV-9002", "INV-9003", "INV-9004", "INV-9004"],
    "customer":   ["Northwind", "Acme", "Acme", "Globex", "Initech", "Initech"],
    "amount":     [1200.00, 480.00, 480.00, 950.00, 310.00, 310.00],
})
print(invoices.duplicated())
print("extra copies:", invoices.duplicated().sum())
print(invoices[invoices.duplicated()].to_string(index=False))

0    False
1    False
2     True
3    False
4    False
5     True
dtype: bool
extra copies: 2
invoice_no customer  amount
  INV-9002     Acme   480.0
  INV-9004  Initech   310.0

The audit I run first on any nightly export that suddenly grew: duplicated() with no arguments compares all three columns, and the two rows it flags are the ones a double-loaded batch created. Mind what the count means — .sum() returns 2, the number of superfluous rows, not the number of invoices inside duplicate groups. That 2 is what you subtract from len(invoices) to prove the file is correct again, and it is also what drop_duplicates() would silently have removed. The index labels on the left are your original ones, which is the point: this is a mask over your frame, so df[df.duplicated()] is a printable change record for the ticket and df[~df.duplicated()] dedupes without throwing anything else away.

$ import pandas as pd

customers = pd.DataFrame({
    "customer_id": ["C-01", "C-02", "C-03", "C-04", "C-05"],
    "email":       ["ana@shop.at", "bo@shop.de", "ana@shop.at", "cy@shop.ch", "bo@shop.de"],
    "country":     ["AT", "DE", "AT", "CH", "DE"],
    "updated":     pd.to_datetime(["2026-01-05", "2026-02-11", "2026-03-02", "2026-03-19", "2026-04-01"]),
})
mask = customers.duplicated(subset=["email"], keep=False)
print(customers[mask].sort_values("email").to_string(index=False))
print("rows flagged:", mask.sum(), "| distinct emails involved:", customers.loc[mask, "email"].nunique())

customer_id       email country    updated
       C-01 ana@shop.at      AT 2026-01-05
       C-03 ana@shop.at      AT 2026-03-02
       C-02  bo@shop.de      DE 2026-02-11
       C-05  bo@shop.de      DE 2026-04-01
rows flagged: 4 | distinct emails involved: 2

subset= is what turns row-matching into an identity check: these rows are not identical — different ids, country and updated dates — but two customers share one email, and keep=False brings back both parties so a human can decide which record wins. That keep=False is deliberate. With the default keep='first' the output would have contained only C-03 and C-05, and a list of orphans is not enough evidence to merge anything. The report form I use: duplicated(subset=["email"], keep=False).sum() / 2 is the number of merge decisions waiting for a person, and the same call on the id column tells you whether the CRM's own key is unique — the one assumption every downstream merge is built on.

$ import pandas as pd

orders = pd.DataFrame({"order_id": ["O-1", "O-2", "O-3"], "revenue": [500.0, 250.0, 700.0]})
shipments = pd.DataFrame({"order_id": ["O-1", "O-1", "O-2", "O-3"], "box": ["B1", "B2", "B3", "B4"]})
joined = orders.merge(shipments, on="order_id")
print("orders:", len(orders), "| joined rows:", len(joined), "| revenue sum:", joined["revenue"].sum())
print(joined[joined.duplicated(subset=["order_id"], keep=False)].to_string(index=False))
print("extra copies the join created:", joined.duplicated(subset=["order_id"]).sum())

orders: 3 | joined rows: 4 | revenue sum: 1950.0
order_id  revenue box
     O-1    500.0  B1
     O-1    500.0  B2
extra copies the join created: 1

This is the check I now run on every join whose right-hand table is not proven unique on the key: straight after the merge, duplicated(subset=["order_id"]) reports how many rows the join invented. One extra box for O-1 turned three orders into four rows and produced 1,950.00 of revenue that is really 1,450.00 — a 34% error from a merge that succeeded completely, with no NaN, no missing row and no warning. Run it before the groupby, because once you aggregate, duplication is indistinguishable from real volume. Same trick reconciles a concat: concat the batches, then duplicated(subset=key).sum().

$ import pandas as pd

sync = pd.DataFrame({
    "meter":  ["M-41", "M-42", "M-41", "M-43", "M-42"],
    "kwh":    [311.4, 180.9, 318.2, 92.7, 181.5],
    "synced": pd.to_datetime(["2026-10-10", "2026-10-10", "2026-10-12", "2026-10-11", "2026-10-12"]),
})
superseded = sync.duplicated(subset=["meter"], keep="last")
print(sync[superseded].to_string(index=False))
print("superseded:", superseded.sum(), "| kept:", (~superseded).sum())
print(sync[~superseded].sort_values("meter").to_string(index=False))

meter   kwh     synced
 M-41 311.4 2026-10-10
 M-42 180.9 2026-10-10
superseded: 2 | kept: 3
meter   kwh     synced
 M-41 318.2 2026-10-12
 M-42 181.5 2026-10-12
 M-43  92.7 2026-10-11

Meter readings get re-uploaded with corrected values, so the newest row per meter is the one that counts — and keep='last' is how you find the rows to retire instead of blindly calling drop_duplicates(). The trap is in the name: 'last' means last in ROW order, not last by date. This frame happens to be in sync order; if it were not, you would sort_values("synced") first, and until you did, the mask would be meaningless. So: sort first, then flag. Use df.sort_values("synced").drop_duplicates(subset="meter", keep="last") when you only need the survivors, and the mask above when a reviewer wants to see which 2 of 5 readings were replaced and by how much — 311.4 superseded by 318.2 is a 6.8 kWh correction no other report would surface.