Data Analyst & Analytics Interview · Python / pandas for Analysis · Lesson 2 of 2
Merges & reshaping
Real analysis spans multiple tables and often needs reshaping between long and wide form. merge is pandas' JOIN; pivot_table and melt reshape.
# Merge = SQL JOIN. how = 'inner' | 'left' | 'right' | 'outer' orders_cust = orders.merge( customers, on="customer_id", how="left", validate="many_to_one" ) # Long -> wide: revenue by month per customer wide = orders_cust.pivot_table( index="customer_id", columns="month", values="amount", aggfunc="sum", fill_value=0 ) # Wide -> long again long = wide.reset_index().melt( id_vars="customer_id", var_name="month", value_name="revenue" )
- merge: set
on(orleft_on/right_on) andhow; thevalidateargument catches unexpected many-to-many blowups. - pivot_table: long → wide, one row per index, categories become columns (like SQL conditional aggregation).
- melt: wide → long, columns become rows — the tidy format most analysis and charting wants.
- concat: stack DataFrames vertically (UNION) or horizontally.
Duplicate keys explode rowsA
merge on a key that isn't unique on the 'one' side does a many-to-many join and multiplies rows — inflating every downstream sum. Pass validate='many_to_one' so pandas raises instead of silently duplicating.Tidy data'Tidy' data means one variable per column, one observation per row. Most pandas and plotting tools assume it — reshaping messy wide exports into long form with
melt is often step one of an analysis.◆ Lock it in
mergeis JOIN — sethowand usevalidateto catch key blowups.pivot_tablegoes long→wide;meltgoes wide→long ('tidy').- Duplicate merge keys silently multiply rows and corrupt aggregates.
Feynman drill — say it out loudExplain what goes wrong when you merge on a non-unique key, and how pivot_table and melt are inverses of each other.
Step 1 rate your confidence · Step 2 pick your answer
You merge orders to customers and your total revenue suddenly doubles. Most likely cause?
Step 1 — how sure are you?