Pivot & Pivot Table
Python · Reshaping & Workflow
Theory
Pivoting turns the values of one column into column headers, giving a wide cross-tab such as cities down the side and months across the top. df.pivot(index='city', columns='month', values='amount') only rearranges data: it needs each (city, month) pair to appear once and raises an error on duplicates. df.pivot_table(...) also aggregates, so duplicates are fine: aggfunc='sum' (default 'mean'), several functions as a list, fill_value=0 for empty cells and margins=True to add an 'All' row and column with grand totals.
pd.crosstab(df['city'], df['month']) is a shortcut that counts how often each pair occurs; add values= and aggfunc= to aggregate instead, and normalize='index' to get row percentages. The result's row labels are the index; call reset_index() to make them a column again. melt() reverses a pivot.
Why we use it
Wide tables are what people read in reports and Excel: one row per entity, one column per period or category. pivot_table produces them in one call, with totals, and without writing a CASE WHEN per column as SQL needs.
When to use it
Use pivot_table for summary cross-tabs (it handles duplicates and totals), pivot only when each pair is unique and you just want to reshape, and crosstab for frequency tables. Keep data long (tidy) for further groupby and joins; pivot at the end for display.
How it works
- Long data: city, month, amount
- Is each (city, month) pair unique? Yes: pivot() can reshape it. No: Use pivot_table with aggfunc.
- Turn month values into columns, one row per city
- Fill empty cells (fill_value=0) and add totals (margins=True)
- Wide cross-tab report
Syntax
df.pivot(index='r', columns='c', values='v')
df.pivot_table(index='r', columns='c', values='v', aggfunc='sum', fill_value=0, margins=True)
pd.crosstab(df['r'], df['c'], normalize='index')Example
report = df.pivot_table(index='city', columns='month', values='amount',
aggfunc='sum', fill_value=0, margins=True)Key points
- pivot reshapes only and fails on duplicate pairs; pivot_table aggregates
- pivot_table's default aggfunc is 'mean' - pass aggfunc='sum' for totals
- fill_value=0 replaces empty cells; margins=True adds 'All' totals
- crosstab counts pairs; normalize='index' gives row shares
- SQL equivalent: SUM(CASE WHEN month = 'Jan' THEN amount END) AS Jan ... GROUP BY city