Transforming Data

Transforming Cheatsheet

Filters are definitions

• Every filter asserts a business definition — state it next to the number: "Active users (logged in within 30 days): 41,200"
• Two dashboards disagree? Compare their WHERE clauses before blaming the data
• Every literal — a threshold, date, status, or country list — is a decision with an author. If you can't name the author, it's unaudited. (AI-generated code invents plausible literals fluently.)
• Mutable attributes (status, plan, segment) filtered at current value answer a question about today, not the past — historical questions need as-of snapshots

Unit traps

Row-count audit trail

Log COUNT(*) after every step; every drop is a checkable claim:

load: 84,210 → Q1 filter: 22,530 → join customers: 22,530 (no fan-out)
→ US: 15,771 (70%, matches mix) → active: 9,006 (−43%?! investigate)

Aggregation

• Group-by changes the grain — restate what one output row means
• Pick the function by the question: SUM = magnitude, COUNT(*) = volume, COUNT(DISTINCT) = reach, AVG = intensity
• AVG/COUNT(col) skip NULLs — each group's mean may have a different denominator than its row count
• Missing groups produce no rows, not zero rows — outer-join onto a complete group list when completeness matters; check the group count (24 months expected, 23 returned?)
• Reconcile: group totals must sum to the ungrouped total (gap = NULL keys)

The average-of-averages rule

Never average rates or averages. Aggregate ratios as sum(numerator) / sum(denominator).

• Wrong: (5% + 10%) / 2 = 7.5%
• Right: 600 / 11,000 = 5.45%
• Summary tables must store numerators AND denominators — bare rates cannot be rolled up
• Equal-weighting groups ("the average store") is a different question — legitimate only if stated

Joins

• Join type = which rows survive: inner drops unmatched (and your guest checkouts); default to left-join from the table that defines the metric
• One-to-many = fan-out: each left row duplicated once per match; sums inflate by exactly the duplication
• Fix: pre-aggregate the many side to the join-key grain before joining
• pandas guard: pd.merge(..., validate='many_to_one') — declare your cardinality assumption
• Anti-join ("who didn't"): LEFT JOIN ... WHERE right.key IS NULL, or NOT EXISTS (avoid NOT IN with NULLs)

Join checks (non-negotiable)

1. Row count before vs after — any increase after a left join = fan-out
2. Right-side key uniqueness: GROUP BY key HAVING COUNT(*) > 1
3. Known total reconciles: revenue in = revenue out
4. Match rate sane — a low match rate is usually a key-format bug ('CUST-001' vs 'cust-001', int vs zero-padded string, timestamp vs date), not a finding