Flag Duplicate Transactions

When to use this skill

Use this when duplicates may be inflating counts or totals and you need to investigate before deleting anything. Warning signs:

• Two people run “how many active members?” and get different numbers
• A vendor payment run shows the same invoice number twice
• A merged dataset suddenly has more rows than either source
• Member/employee counts don’t tie to HR or the census

The professional rule: flag first, investigate, then remove. A duplicate invoice might be a data error — or a legitimate second payment. Deleting on autopilot destroys the evidence. The key tool is duplicated(keep=False), which flags every member of a duplicate group (including the first occurrence) so the reviewer sees the whole group side by side.

Inputs it expects

• A pandas DataFrame of transactions or member records
• The business key that defines “the same record” — e.g., vendor_id + invoice_num, or member_id. Choosing this key is the judgment call; all-columns-identical is a different (stricter) test worth running too.
• If keeping “most recent,” a reliable date column to sort by
• Control totals from the source (record count, amount total) to tie against

Steps

1. Snapshot: row count and amount total before anything else.
2. Test for fully identical rows first (df.duplicated() with no subset) — these are almost always safe-to-remove load errors, but count them separately.
3. Flag business-key duplicates with keep=False so every row in each duplicate group is marked — this is the review file, sorted by key so groups sit together.
4. Investigate: are the “duplicates” truly the same event, or legitimate multiples (a member with two accounts; two invoices with the same number from different vendors)?
5. Decide the resolution per pattern: keep-first, keep-most-recent (sort by date first), or aggregate (groupby().sum()) when multiples are legitimate and you need one row per entity.
6. Dedupe with an audit trail — keep the removed rows in a separate file with a reason and date.
7. Reconcile: rows kept + rows removed = rows in; explain the amount impact.

Code

import pandas as pd
from datetime import datetime

# Load AP transaction detail
txns = pd.read_excel('ap_transactions.xlsx')
rows_in = len(txns)
amount_in = txns['invoice_amount'].sum()

# --- Step 1: fully identical rows (usually double-loads) ---
exact_dupes = txns.duplicated()          # all columns match
print(f"Fully identical rows: {exact_dupes.sum()}")

# --- Step 2: flag EVERY member of each business-key duplicate group ---
key_cols = ['vendor_id', 'invoice_num']
txns['in_dupe_group'] = txns.duplicated(subset=key_cols, keep=False)

# Review file: whole groups together, so a human sees all versions side by side
review = (txns[txns['in_dupe_group']]
          .sort_values(key_cols + ['invoice_date']))
review.to_excel('duplicates_for_review.xlsx', index=False)
print(f"Rows in duplicate groups (for review): {len(review)}")
print(f"Distinct duplicated keys: {review.drop_duplicates(subset=key_cols).shape[0]}")

# --- Step 3: after review, dedupe with an audit trail ---
def deduplicate_with_audit(df, subset_cols, sort_col=None, keep='first'):
    """Remove duplicates, returning (kept, removed-with-audit-info)."""
    df = df.copy()
    if sort_col:                          # keep most recent: sort, then keep last
        df = df.sort_values(sort_col)
    is_dupe = df.duplicated(subset=subset_cols, keep=keep)
    kept = df[~is_dupe].copy()
    removed = df[is_dupe].copy()
    removed['removal_date'] = datetime.now()
    removed['removal_reason'] = f'Duplicate on {subset_cols} (kept {keep})'
    return kept, removed

txns_clean, txns_removed = deduplicate_with_audit(
    txns, subset_cols=key_cols, sort_col='invoice_date', keep='last'
)
txns_removed.to_excel('removed_duplicates_audit.xlsx', index=False)

# --- Control totals ---
print(f"Rows in: {rows_in} | kept: {len(txns_clean)} | removed: {len(txns_removed)}")
assert len(txns_clean) + len(txns_removed) == rows_in
print(f"Amount in:      {amount_in:,.2f}")
print(f"Amount kept:    {txns_clean['invoice_amount'].sum():,.2f}")
print(f"Amount removed: {txns_removed['invoice_amount'].sum():,.2f}")
print(f"Unique vendor+invoice keys: {txns_clean.drop_duplicates(subset=key_cols).shape[0]}")

Validation (control totals)

• Row reconciliation. Rows kept + rows removed = rows in. Assert it in code; never let rows vanish unaccounted.
• Amount reconciliation. Amount in = amount kept + amount removed. The “amount removed” figure is your quantified duplicate exposure — report it explicitly (e.g., “removed 14 rows totaling $42,318.55”).
• Key uniqueness after dedupe. txns_clean.duplicated(subset=key_cols).sum() must be 0.
• Unique-count cross-check. df['member_id'].nunique() before dedupe should equal len(df) after keep-first dedupe on member_id — two roads to the same number.
• If aggregating instead of removing: the grouped total (groupby(key).sum()) must equal the pre-aggregation total exactly — aggregation moves no dollars.

Exceptions to surface

The review file — every member of every duplicate group — goes to a human. Highlight in particular:

• Same key, different amounts — same vendor + invoice number but $5,000 vs $5,500. Could be a keying error, a partial payment, or a corrected reissue; only a person (or the vendor) can say.
• Same key, different dates far apart — a possible genuine duplicate payment (recovery opportunity) rather than a data glitch.
• Fully identical rows — likely double-loaded files; confirm the load history before removing, and note which batch caused it so it doesn’t recur.
• Legitimate-multiple patterns — one customer with checking + savings + retirement accounts is not a duplicate person; these should be aggregated or counted with nunique(), never deleted.
• Near-duplicates the exact match missed — names or vendors differing by a typo ("Jon Smith" / "John Smith"); list high-similarity pairs separately for manual confirmation rather than merging automatically.