Your groupby produced a perfect result — 40 rows of (region, month, revenue). Your manager wants 'regions down the side, months across the top'. Same numbers, different shape. Half of practical data work is exactly this: the data is right, the SHAPE is wrong. pivot and melt are the two verbs that fix it.
What you'll learn
What
Reshaping converts between long format (each row = one observation: entity, variable, value) and wide format (each row = one entity, each column = one variable). pivot and pivot_table go long→wide; melt goes wide→long; stack and unstack do the same moves between index levels and columns.
Why
Tools disagree about shape. groupby output, plotting libraries, and databases prefer long; humans, spreadsheets, and correlation matrices prefer wide. If you can't reshape fluently you either fight your tools or hand-build tables cell by cell — both slow, both error-prone.
Where it's used
Building report cross-tabs from grouped data, preparing survey exports (one column per question) for analysis, turning sensor logs into per-sensor columns, and un-pivoting spreadsheet-shaped data that arrives wide.
Where this runs in production
Survey platforms export one column per question (wide); statistical analysis wants one row per response (long). melt is the first line of nearly every survey-analysis script.
Consultants turn transaction extracts into region-by-quarter revenue grids with pivot_table — the exact table shape that lands in the client deck.
Device telemetry arrives long (timestamp, metric, value); building a per-user day view — steps, heart rate, sleep as columns — is a pivot over the metric column.
Long format: one row per observation — (store, month, sales), 12 rows per store. Wide format: one row per entity, one column per variable — each store's 12 months spread across 12 columns. Long is easier to filter, group, and plot programmatically; wide is easier to read and compare side by side. Neither is 'correct' — they're the same information organised for different consumers.
# LONG -> WIDE wide = df.pivot(index='store', columns='month', values='sales') # month: Jan Feb Mar # store A 10 12 15 # store B 8 9 11
pivot takes three roles: which column becomes the row index, which column's values become the new column names, and which column fills the cells. It's a pure rearrangement — every cell in the output is one cell from the input.
pivot demands exactly one row per (index, columns) pair; a duplicate raises ValueError. Real data has duplicates — three sales for the same store in the same month. pivot_table handles them by aggregating: aggfunc='sum' (or 'mean', default) collapses the duplicates. Rule of thumb: pivot for pure reshaping of already-unique data, pivot_table when any aggregation may be involved — it's groupby and reshape in one step.
wide = df.pivot_table(
index='region', columns='product', values='revenue',
aggfunc='sum', fill_value=0, margins=True,
)pivot_table's extras: fill_value replaces the NaN that appears when a combination never occurred (South sold no TVs), and margins=True adds an 'All' row/column of totals — the spreadsheet pivot-table experience.
# WIDE -> LONG
long = df.melt(
id_vars=['store'], # columns that stay as identifiers
value_vars=['Jan','Feb','Mar'], # columns to unpivot
var_name='month', value_name='sales',
)
# store month sales <- one row per (store, month)melt is pivot's inverse: the listed value columns collapse into two — one holding the old column NAMES, one holding the VALUES. id_vars repeat on every melted row so each observation stays self-describing.
df.groupby(['region','product'])['revenue'].sum().unstack(fill_value=0) # product Phone TV <- inner key level became columns # region # North 30 90 # South 70 20
groupby → unstack ≈ pivot_table. Both paths produce the same grid; use whichever reads more clearly in context. unstack moves the INNERMOST level by default; pass a level name to move a different one.
An accountant's ledger can be bound chronologically — every transaction on its own line, easy to append and audit (long) — or rebound as a summary book with one page per client and columns per month, easy to read in a meeting (wide). Rebinding changes no numbers; it changes what's easy. pivot and melt are the rebinding machines, and knowing which binding each task wants is the actual skill.
Click each operation — two directions, two levels (columns vs index).
Select a type to see its full definition, operations, and data science usage.
Turn (store, month, sales) rows into a store-by-month grid.
Long format: four observations, one per row. Every (store, month) pair appears exactly once — pivot's requirement.
Your task
A support team logs tickets long-form. Build a weekday-by-priority count grid with pivot_table, then melt a wide budget table back to long form.
priority high low day Mon 1 2 Tue 2 0 team year amount Eng 2025 300 Eng 2026 340 Sales 2025 100 Sales 2026 120
Write your solution in the editor on the right, then hit Run.
In df.pivot(index='store', columns='month', values='sales'), what does the 'month' column's data become?
What does melt's id_vars parameter specify?
df.pivot(...) raises 'Index contains duplicate entries, cannot reshape'. The standard fix?
What's the right first move?
Using groupby and unstack, build a city-by-category count grid (fill missing cells with 0) and print it. df = pd.DataFrame({'city':['Pune','Pune','Delhi','Delhi','Pune'], 'category':['food','cab','food','food','cab']})
Why do analysis pipelines generally keep data LONG until the final presentation step?
Explain long vs wide data and when you'd use each.
Long format has one row per observation — (store, month, sales) — with variables as data in columns. Wide format has one row per entity with one column per variable — each store's months spread across columns. They hold identical information; they differ in what's easy. Long wins for machine consumption: filters (month == 'Jan'), groupbys, merges, and most plotting libraries treat it uniformly, and a new month is just new rows, not a schema change. Wide wins for human consumption: side-by-side comparison, correlation matrices, and anything headed for a slide or spreadsheet. My working rule is 'long through the pipeline, wide at the border': keep data tidy while transforming, pivot to wide as the final presentation step, and melt anything that arrives wide from spreadsheets or survey tools before analysing it.
pivot vs pivot_table — when does each apply and why?
pivot is a pure rearrangement: every output cell is exactly one input cell, so it requires each (index, columns) pair to appear at most once and raises on duplicates. That strictness is valuable when I believe the data is already one-row-per-pair — a failure tells me my assumption was wrong, which is a data-quality signal, not a nuisance. pivot_table tolerates duplicates by aggregating them with aggfunc — sum, mean, size — making it groupby plus reshape in one call, with conveniences like fill_value for never-occurred combinations and margins for total rows. So: pivot when reshaping unique data and wanting duplicates to error loudly; pivot_table when aggregation is part of the intent. The equivalent idiom groupby(keys).agg().unstack() produces the same grids — I use whichever reads better, but in review I check the same two things: what happened to duplicates, and what filled the missing combinations.
A stakeholder's spreadsheet has years as columns and wants analysis by year. Walk me through your handling.
That's wide data where the year — a variable — is trapped in the column headers, so my first move is melt: id_vars for the entity columns, the year columns as value_vars, producing (entity, year, amount) long form. I'd immediately fix the type wrinkle melt creates: the year column holds strings like '2025' because they were headers, so to_numeric before any sorting or filtering — otherwise '2026' > '2025' works but '9' > '10' style bugs lurk if formats vary. From long form, every request becomes routine: growth by year is a groupby, filtering to a range is a mask, and joining to other tables works on the year key. When results go back to the stakeholder, I pivot_table back to years-as-columns, because that's the shape they read. The round trip — melt, analyse long, pivot for presentation — is the standard pattern for spreadsheet-origin data, and it means the analysis code survives next year's new column without edits.
Common Mistakes to Avoid
1) Using pivot on data with duplicate (index, columns) pairs — it raises; decide the aggregation and use pivot_table. 2) Forgetting pivot_table's DEFAULT aggfunc is 'mean' — summing was your intent? Say aggfunc='sum'. 3) Ignoring the NaNs that appear for never-occurred combinations — fill_value=0 when zero is the true meaning, but only then. 4) Melting without id_vars and losing track of which row is which entity. 5) Not converting melted headers to proper types — years arrive as strings. 6) Hand-building cross-tabs with loops when groupby+unstack or pivot_table is one line. 7) Keeping data wide through a whole pipeline and paying for it in every subsequent filter and merge.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: long vs wide with the ledger-binding analogy.' • 'Show me the same cross-tab three ways: pivot_table, groupby+unstack, and crosstab.' • 'Quiz me: pivot or pivot_table for each scenario you invent.' • 'Melt this 30-question survey export and compute per-question stats.' • 'Interview mode: challenge me on why pipelines stay long until presentation.'
Glossary
Long format — one row per observation; variables live in columns as data. Wide format — one row per entity; one column per variable. pivot — pure long→wide rearrangement; errors on duplicates. pivot_table — long→wide with aggregation (aggfunc), fill_value, margins. melt — wide→long; id_vars stay, value_vars collapse to var_name/value_name. stack — rotate columns down into an index level. unstack — rotate an index level up into columns (innermost by default). Cross-tab — a two-dimensional count/aggregate grid. Tidy data — the long-format discipline: each variable a column, each observation a row.
Recommended Resources
• Docs: pandas user guide 'Reshaping and pivot tables' — pivot, melt, stack/unstack, and crosstab in one page. • Read: Hadley Wickham's 'Tidy Data' paper — the why behind keeping pipelines long. • Practice: find any wide spreadsheet (years or months as columns), melt it, compute a per-period metric, and pivot back for presentation — the full round trip. • Next in DSM: reshaping rearranges one table; Merging & Joining combines several — the relational step.
Recap
✓ Long = one row per observation (machine-friendly); wide = one row per entity (human-friendly); same data, different shape. ✓ pivot assigns index/columns/values roles and errors on duplicates; pivot_table aggregates them (default aggfunc is MEAN). ✓ fill_value handles never-occurred combinations; margins adds total rows/columns. ✓ melt is the inverse: id_vars stay put, value_vars collapse into var_name/value_name pairs. ✓ groupby + unstack turns a two-key aggregation into a cross-tab — the everyday report idiom. ✓ Keep pipelines long; widen at the last step for human eyes. Next up: Merging & Joining DataFrames. You can reshape one table — next you'll combine several, matching rows across tables by key like SQL joins.
Run your code to see the output here.