Where pandas Lies to You

Cheatsheet: Where pandas Lies to You

One page for Topic 2 — the three silent bugs: vanishing groups, edits that never landed, and merges that multiply revenue. All run cleanly.

GroupBy: the grain question

df.groupby('region')['revenue'].sum()

Read in three pieces: group the rows → take a column → aggregate. One output row per unique key. Group keys move into the index; .reset_index() moves them back to ordinary columns (changes no numbers, matters for merges/charts).

Always name the grain — one row per what? — before and after the groupby, and check it matches the question's grain, not just its keywords. "Revenue per customer by region" run on an order-grain table gives revenue per order.

Aggregation reference

The average-of-averages rule

.mean() on any column that is itself a ratio (rate, %, CPA, margin) deserves immediate scrutiny:

Five-fold gap from code that runs perfectly and sounds right. Blended rates = sum numerator / sum denominator. The gap grows with how uneven the traffic is.

NaN group keys vanish

groupby silently drops rows whose group key is missing (dropna=True default) — no warning, no "Unknown" bucket. $500 of real revenue with an unrecorded region is simply gone; a systematic gap (new channel that doesn't set region) makes an entire business line invisible.

The cheap tell: grouped total vs ungrouped total. df.groupby('region')['revenue'].sum().sum() should equal df['revenue'].sum(). If not, rows fell out. Fix to ask for: groupby('region', dropna=False).

The view-vs-copy bug

SettingWithCopyWarning translates to: "your edit probably didn't land." It's a warning, not an error — easy to scroll past, and some AI notebooks suppress warnings entirely ("for cleaner output"), which removes your smoke detector.

Copy-on-write note: under pandas 2.x with copy_on_write = True, and in pandas 3.0 where it's the default, chained assignment never modifies the original (raises ChainedAssignmentError warning). Version change makes the pattern reliably broken, not correct — the reading rule is the same everywhere: chained indexing + assignment means the data was almost certainly not modified.

One-line verification after every cleaning step — check the condition, not the print statement:

(df['revenue'] > 10000).sum()   # should be 0 after capping

"Cleaning complete" prints whether or not anything happened. Standing request for AI: "after every cleaning step, print a check showing the condition you just fixed no longer occurs."

Merges

merged = orders.merge(customers, on='customer_id', how='left')

how= table

If the business question says "all orders" and the code says inner (or omits how=), rows and their revenue are quietly gone.

The row-explosion check

A merge changes row count exactly two ways, both diagnostic:

len(orders), len(merged)   # (1240, 1240) good. (1240, 1113) or (1240, 1892) — explain yourself.

• Shrinkage = inner merge dropped non-matching rows.
• Growth = duplicate keys multiplied rows. One duplicate customer record turned $1,000 of revenue into $1,400 (each of C1's orders matched twice) — no error, no warning, every downstream number inherits the inflation.

validate= — the free safety check

orders.merge(customers, on='customer_id', how='left', validate='many_to_one')

Asserts "right table has at most one row per key" — raises MergeError immediately instead of silently inflating. Options: 'one_to_one', 'one_to_many', 'many_to_one'. Absent from nearly all AI-generated merges; one of the highest-value standing instructions: "add validate= to every merge, stating the relationship you expect."

Suffix collisions

Same-named non-key columns come out as _x (left) and _y (right). amount_x vs amount_y — nothing says which is order vs refund; downstream code grabbing "the amount column" has a coin-flip chance of summing refunds as revenue. Good code renames before merging or passes explicit suffixes=('_order', '_refund').

Traps

• Trusting a grouped report to be complete — NaN keys vanish; reconcile grouped total vs ungrouped total first.
• .mean() on a ratio column — "average conversion rate by channel" literally describes the wrong calculation for a blended rate.
• Grain drift — grouping keys match the question's keywords but not its grain.
• Reading .agg count as row count — it counts non-missing values; use .size() for rows.
• df[mask]['col'] = value — the edit hit a throwaway copy; the safe form is df.loc[mask, 'col'] = value.
• "It printed success, so it worked" — prints prove a line ran, not that it did anything; re-inspect the data.
• warnings.filterwarnings('ignore') at the top of a notebook — the smoke detector is off.
• Merge with no how= — that's an inner join nobody chose.
• Assuming a lookup table is deduplicated because it should be — prove it with validate= or a duplicate check.
• Verifying a merge with merged.head() — five tidy rows show whether or not 10% of orders vanished; only row counts and a reconciled total verify a merge.