Compare Each Row to Its Group with Transform

When to use this skill

• You need each transaction compared against its own group: “what percent of this vendor’s annual spend is this invoice?” or “what is the running balance for this member?”
• A regular groupby().sum() won’t work because it collapses the detail — you need the group figure next to every row so a reviewer can scan line by line.
• You’re replacing slow, fragile SUMIFS / cumulative SUMIFS formulas that break when rows are re-sorted.

The key distinction: agg gives you one row per group; transform gives you a value for every original row, aligned to the detail. Detail stays intact for tie-outs and workpapers.

Inputs it expects

• A DataFrame at transaction/detail level — one row per payment, invoice, or journal line.
• A grouping column (Vendor_ID, Member_ID, Account, Department).
• A numeric amount column to aggregate.
• For running totals: a date or period column, and the data sorted by group then date before computing cumulative figures.

Steps

1. Sort the DataFrame by group and date — cumulative sums follow row order, so this matters.
2. Use transform('sum') to put each group’s total on every row of that group.
3. Use transform('cumsum') for a running balance within each group.
4. Derive comparison columns: percent of group total, difference from group average, rank within group.
5. Keep the detail rows — this enriched table is the deliverable, ready for filtering and reviewer sampling.

Code

import pandas as pd

# Detail-level payment data
df = pd.DataFrame({
    'Vendor_ID': ['V-1001', 'V-1001', 'V-1001', 'V-2002', 'V-2002', 'V-3003'],
    'Invoice_Date': ['2025-01-15', '2025-02-10', '2025-03-05',
                     '2025-01-20', '2025-02-25', '2025-01-30'],
    'Invoice_Amount': [12500.00, 8750.00, 15300.00, 42000.00, 38500.00, 9900.00],
})
df['Invoice_Date'] = pd.to_datetime(df['Invoice_Date'])

# Sort first — running totals follow row order
df = df.sort_values(['Vendor_ID', 'Invoice_Date']).reset_index(drop=True)

# Group total repeated on every detail row
df['Vendor_Total'] = df.groupby('Vendor_ID')['Invoice_Amount'].transform('sum')

# Running balance within each vendor
df['Running_Total'] = df.groupby('Vendor_ID')['Invoice_Amount'].transform('cumsum')

# Each invoice as a share of its vendor's total spend
df['Pct_of_Vendor_Total'] = (df['Invoice_Amount'] / df['Vendor_Total'] * 100).round(1)

# Variance from the vendor's average invoice
df['Vendor_Avg'] = df.groupby('Vendor_ID')['Invoice_Amount'].transform('mean')
df['Diff_vs_Avg'] = df['Invoice_Amount'] - df['Vendor_Avg']

# Rank invoices within each vendor (largest = 1)
df['Rank_in_Vendor'] = df.groupby('Vendor_ID')['Invoice_Amount'] \
                         .rank(method='dense', ascending=False).astype(int)

print(df)

Validation (control totals)

• Row count unchanged: transform never adds or removes rows — len(df) must match the source detail exactly.
• Grand total unchanged: df['Invoice_Amount'].sum() is identical before and after — you only added columns.
• Group totals tie to an independent aggregation: df.groupby('Vendor_ID')['Invoice_Amount'].sum() must equal df.groupby('Vendor_ID')['Vendor_Total'].first() for every group.
• Running total lands on the group total: the last Running_Total row in each group must equal that group’s Vendor_Total.
• Percentages sum to 100: df.groupby('Vendor_ID')['Pct_of_Vendor_Total'].sum() should be ~100 per group (allow small rounding drift).

Exceptions to surface

• Rows where Pct_of_Vendor_Total is unusually high (e.g., one invoice is more than half the vendor’s total) — concentration worth a look.
• Groups with only one row — group average and rank are trivially meaningless there; flag them rather than letting them blend in.
• Rows with a null or zero group total — a percent-of-total divides by zero and produces inf/NaN; the underlying amounts need review.
• Negative amounts (credits/reversals) mixed into a group — they distort running totals and percentages; confirm they belong in the same population.
• Rows whose Diff_vs_Avg exceeds a set threshold (e.g., 3x the group’s typical invoice) — classic outlier candidates for the reviewer’s sample.