The Groupby That Disagreed With Finance

What breaks

• String dtype means .mean() either raises or, worse, behaves in a way that is silently wrong downstream. Any arithmetic is really string handling.
• A naive astype(float) fails on "$1,204.50" because of the $ and the thousands separator.
• A naive .str.replace(',', '') fixes the US rows and corrupts the European rows: "1.204,50" becomes "1.20450", which parses as about $1.20. That is a 1,000× error hiding inside a step that looks correct.

The parsing step

s = df['amount'].astype('string').str.strip().str.replace('$', '', regex=False)

# Decide the decimal convention per row, not per column
us = s.str.match(r'^\d{1,3}(,\d{3})*(\.\d+)?$')
eu = s.str.match(r'^\d{1,3}(\.\d{3})*(,\d+)?$')

s_us = s.where(us).str.replace(',', '', regex=False)
s_eu = s.where(eu).str.replace('.', '', regex=False).str.replace(',', '.', regex=False)

df['amount_clean'] = pd.to_numeric(s_us.fillna(s_eu), errors='coerce')

# Never let coercion vanish quietly
broken = df.loc[df['amount_clean'].isna() & df['amount'].notna(), 'amount']
print(len(broken), broken.head(20).tolist())

Judgement: keep the original amount column. Overwriting it destroys the only evidence of what the source actually said, and you will need it when someone disputes a total.

#	Candidate cause	Diagnostic
1	Row explosion in the merge with the customer dimension (one-to-many), so each order is counted several times	Compare len(df) before and after the merge; re-run with validate='m:1' and see it fail
2	Duplicate order_ids inflating the total	df['order_id'].duplicated().sum(); re-sum after dedupe and compare the delta
3	Gross vs net - cancelled and refunded orders are included, finance nets them out	df['status'].value_counts(dropna=False); re-sum excluding cancelled/refunded and see whether you land near $7.9M
4	Period mismatch - the notebook covers a different 12 months than finance's window	df['order_ts'].min(), .max(), and a monthly sum compared to finance's monthly figures
5	Mixed currency being summed as if it were one	Group by the currency column (or derive it from locale) and sum per currency

The arithmetic clue: $14.2M ÷ $7.9M ≈ 1.8. That is close to a clean duplication factor, which points at cause 1 before anything else. Being roughly double is a signal, not proof - run the diagnostics in this order: merge → dedupe → status → period.

Two-sentence answer for your manager: "We were double-counting orders because the customer join duplicated rows wherever a customer had more than one record, and we were also summing gross totals that still included cancellations and refunds. After fixing the join and netting out cancelled and refunded orders, our figure reconciles to finance's $7.9M within [X]."

Checks that fail loudly, not quietly:

assert df['order_id'].is_unique                       # after dedupe
assert df['amount_clean'].notna().all()               # coercion surfaced, not swallowed
assert df['customer_id'].notna().sum() == len(df) - 3400
assert (df['amount_clean'] >= 0).all() or df['amount_clean'].lt(0).sum() == EXPECTED_REFUNDS

recon = df.loc[df['status'] == 'completed', 'amount_clean'].sum()
print(f'completed net revenue: {recon:,.2f}  | finance: {FINANCE_TOTAL:,.2f}  | delta: {recon - FINANCE_TOTAL:,.2f}')

Plus:

• Row-count ledger printed at every stage: raw → after dedupe → after merge → after filtering - so any inflation is visible in the output, not buried in a cell nobody re-runs.
• dropna awareness: groupby silently excludes NaN keys by default. With 3,400 null customer_id rows, the per-customer mean is computed on a different population than the total. Decide explicitly whether those rows belong in each output and print the excluded count.
• A stated filter definition for what counts as revenue (which statuses, which window, gross or net) as a named constant at the top of the file.
• A reconciliation cell that must match finance - the check that would have caught this on day one.

Judgement: the goal is not a clean notebook, it is a notebook where a wrong number announces itself instead of being discovered by the CFO.