Pandas Pivot Table: Long to Wide DataFrame Guide
Learn pandas pivot and pivot_table to reshape DataFrames from long to wide format. Handle duplicates with aggfunc (mean, sum), fill NaNs, multi-indexes, crosstab counts, and melt reverse. Code examples for real scenarios.
How to Pivot a Pandas DataFrame: Comprehensive Guide from Long to Wide Format
How do I pivot a pandas DataFrame so that values in one column (col) become new columns, values in another column (row) become the index, and aggregated values (e.g., mean of val0) fill the cells?
This covers transforming data from long format to wide format using pivot() and pivot_table(), handling duplicates, custom aggregations (mean, sum), missing values, multi-level indexes, multiple value columns, cross-tabulation, and flattening multi-indexes.
Sample DataFrame
Consider this DataFrame df with columns 'key', 'row', 'item', 'col', 'val0', 'val1':
key row item col val0 val1 0 key0 row3 item1 col3 0.81 0.04 1 key1 row2 item1 col2 0.44 0.07 2 key1 row0 item1 col0 0.77 0.01 3 key0 row4 item0 col2 0.15 0.59 4 key1 row0 item2 col1 0.81 0.64 5 key1 row2 item2 col4 0.13 0.88 6 key2 row4 item1 col3 0.88 0.39 7 key1 row4 item1 col1 0.10 0.07 8 key1 row0 item2 col4 0.65 0.02 9 key1 row2 item0 col2 0.35 0.61 10 key2 row0 item2 col1 0.40 0.85 11 key2 row4 item1 col2 0.64 0.25 12 key0 row2 item2 col3 0.50 0.44 13 key0 row4 item1 col4 0.24 0.46 14 key1 row3 item2 col3 0.28 0.11 15 key0 row3 item1 col1 0.31 0.23 16 key0 row0 item2 col3 0.86 0.01 17 key0 row4 item0 col3 0.64 0.21 18 key2 row2 item2 col0 0.13 0.45 19 key0 row2 item0 col4 0.37 0.70
Setup Code
import numpy as np
import pandas as pd
from numpy.core.defchararray import add
np.random.seed([3,1415])
n = 20
cols = np.array(['key', 'row', 'item', 'col'])
arr1 = (np.random.randint(5, size=(n, 4)) // [2, 1, 2, 1]).astype(str)
df = pd.DataFrame(
add(cols, arr1), columns=cols
).join(
pd.DataFrame(np.random.rand(n, 2).round(2)).add_prefix('val')
)
print(df)
Common Pivoting Scenarios
1. Basic Pivot with Aggregation (Avoid ValueError: Index contains duplicate entries)
Use pivot_table() instead of pivot() for duplicates.
Goal: col → columns, row → index, mean(val0) → values
col col0 col1 col2 col3 col4 row row0 0.77 0.605 NaN 0.860 0.65 row2 0.13 NaN 0.395 0.500 0.25 row3 NaN 0.310 NaN 0.545 NaN row4 NaN 0.100 0.395 0.760 0.24
2. Fill Missing Values with 0
col col0 col1 col2 col3 col4 row row0 0.77 0.605 0.000 0.860 0.65 row2 0.13 0.000 0.395 0.500 0.25 row3 0.00 0.310 0.000 0.545 0.00 row4 0.00 0.100 0.395 0.760 0.24
3. Use Different Aggregation (e.g., sum)
col col0 col1 col2 col3 col4 row row0 0.77 1.21 0.00 0.86 0.65 row2 0.13 0.00 0.79 0.50 0.50 row3 0.00 0.31 0.00 1.09 0.00 row4 0.00 0.10 0.79 1.52 0.24
4. Multiple Aggregations (e.g., sum and mean)
sum mean col col0 col1 col2 col3 col4 col0 col1 col2 col3 col4 row row0 0.77 1.21 0.00 0.86 0.65 0.77 0.605 0.000 0.860 0.65 row2 0.13 0.00 0.79 0.50 0.50 0.13 0.000 0.395 0.500 0.25 row3 0.00 0.31 0.00 1.09 0.00 0.00 0.310 0.000 0.545 0.00 row4 0.00 0.10 0.79 1.52 0.24 0.00 0.100 0.395 0.760 0.24
5. Aggregate Multiple Value Columns (val0, val1)
val0 val1 col col0 col1 col2 col3 col4 col0 col1 col2 col3 col4 row row0 0.77 0.605 0.000 0.860 0.65 0.01 0.745 0.00 0.010 0.02 row2 0.13 0.000 0.395 0.500 0.25 0.45 0.000 0.34 0.440 0.79 row3 0.00 0.310 0.000 0.545 0.00 0.00 0.230 0.00 0.075 0.00 row4 0.00 0.100 0.395 0.760 0.24 0.00 0.070 0.42 0.300 0.46
6. Multi-Level Columns (Subdivide by item)
item item0 item1 item2 col col2 col3 col4 col0 col1 col2 col3 col4 col0 col1 col3 col4 row row0 0.00 0.00 0.00 0.77 0.00 0.00 0.00 0.00 0.00 0.605 0.86 0.65 row2 0.35 0.00 0.37 0.00 0.00 0.44 0.00 0.00 0.13 0.000 0.50 0.13 row3 0.00 0.00 0.00 0.00 0.31 0.00 0.81 0.00 0.00 0.000 0.28 0.00 row4 0.15 0.64 0.00 0.00 0.10 0.64 0.88 0.24 0.00 0.000 0.00 0.00
7. Multi-Level Index (Subdivide by key and row)
(Example output truncated for brevity; full multi-index structure with key and row as index levels.)
8. Cross-Tabulation (Frequency Count)
col col0 col1 col2 col3 col4 row row0 1 2 0 1 1 row2 1 0 2 1 2 row3 0 1 0 2 0 row4 0 1 2 2 1
9. Pivot on Only Two Columns (Long to Wide, Handling Duplicates)
Given:
np.random.seed([3, 1415])
df2 = pd.DataFrame({'A': list('aaaabbbc'), 'B': np.random.choice(15, 8)})
print(df2)
Expected (pivoting B values into columns indexed by implicit row):
a b c 0 0.0 10.0 7.0 1 11.0 10.0 NaN 2 2.0 14.0 NaN 3 11.0 NaN NaN
10. Flatten Multi-Index Columns to Single Level
From:
1 2 1 1 2 a 2 1 1 b 2 1 0 c 1 0 0
To:
1|1 2|1 2|2 a 2 1 1 b 2 1 0 c 1 0 0
What are the pandas pivot() and pivot_table() syntaxes, parameters, and best practices for these scenarios?
To pivot a pandas DataFrame from long to wide format, use pandas pivot_table with col as columns, row as index, and mean of val0 as values—it handles duplicates gracefully where basic pandas pivot fails with a ValueError. For custom needs like sum aggregation, filling NaNs with 0, or multi-level indexes from key or item, tweak parameters like aggfunc, fill_value, and index/columns. This flexible reshaping powers everything from sales summaries to crosstabs, as detailed in the official pandas docs.
Contents
- Introduction to Pandas Pivot and Pandas Pivot Table
- Basic Pandas Pivot Syntax
- Pandas Pivot Table for Aggregations and Handling Duplicates
- Filling Missing Values in Pandas Pivot Table
- Multiple Aggregations and Value Columns
- Multi-Level Indexes and Columns
- Pandas Crosstab for Frequency Counts
- Flattening Multi-Index Columns
- Pandas Melt: The Reverse Operation
- Best Practices for Pandas Pivot Table
- Common Errors and Troubleshooting
- Real-World Examples with Sample Data
- Sources
- Conclusion
Introduction to Pandas Pivot and Pandas Pivot Table
Ever stared at a long, skinny DataFrame and wished it spread out wider for easier analysis? That’s where pandas pivot and pandas pivot_table shine—they reshape data so unique values in one column fan out as new columns, while another sets the rows. Think sales data: stack regions down the side, products across the top, totals in the cells.
Start with your setup code for the sample DataFrame—it’s got key, row, item, col, val0, and val1 columns, perfect for demos:
import numpy as np
import pandas as pd
np.random.seed([3,1415])
n = 20
cols = np.array(['key', 'row', 'item', 'col'])
arr1 = (np.random.randint(5, size=(n, 4)) // [2, 1, 2, 1]).astype(str)
df = pd.DataFrame(add(cols, arr1), columns=cols).join(
pd.DataFrame(np.random.rand(n, 2).round(2)).add_prefix('val')
)
print(df)
Basic idea? pivot() assumes unique index-column pairs (no duplicates). Duplicates? Boom—ValueError. Enter pivot_table: it aggregates them (default mean). Both live in the pandas reshaping guide, but pivot_table’s your daily driver for real data.
Basic Pandas Pivot Syntax
df.pivot(index='row', columns='col', values='val0')—that’s the no-frills pandas pivot. It grabs unique col values as headers, aligns by row index, fills cells with val0. Perfect for tidy data without repeats.
But what if duplicates sneak in? You’ll hit:
ValueError: Index contains duplicate entries, cannot reshape
On our sample? It chokes because multiple rows share row-col combos. Quick fix: drop duplicates first or switch to pivot_table. Here’s a safe basic pivot on a subset without dupes:
pivoted = df.pivot(index='row', columns='col', values='val0')
print(pivoted)
Output matches scenario 1’s shape, but skips aggregation. Fast for clean data. Why bother with pivot at all? It’s lighter—no extra computation if your data’s pristine.
Pandas Pivot Table for Aggregations and Handling Duplicates
Duplicates ruining your day? pandas pivot_table laughs at them. Core syntax: pd.pivot_table(df, values='val0', index='row', columns='col', aggfunc='mean'). It averages (or sums, counts—your call) clashing entries.
For scenario 1’s exact output:
pivoted = pd.pivot_table(df, values='val0', index='row', columns='col', aggfunc='mean')
print(pivoted)
col col0 col1 col2 col3 col4
row
row0 0.770 0.605 NaN 0.860 0.650
row2 0.130 NaN 0.395 0.500 0.250
row3 NaN 0.310 NaN 0.545 NaN
row4 NaN 0.100 0.395 0.760 0.240
Switch to sum? aggfunc='sum'. Counts? 'count'. Custom? aggfunc=lambda x: x.max(). As Practical Business Python explains, this flexibility makes pivot_table king for messy business data.
Filling Missing Values in Pandas Pivot Table
NaNs everywhere post-pivot? No sweat—fill_value=0 zaps them. Scenario 2:
pivoted = pd.pivot_table(df, values='val0', index='row', columns='col',
aggfunc='mean', fill_value=0)
print(pivoted)
Boom—zeros fill the gaps:
col col0 col1 col2 col3 col4
row
row0 0.770 0.605 0.000 0.860 0.650
row2 0.130 0.000 0.395 0.500 0.250
row3 0.000 0.310 0.000 0.545 0.000
row4 0.000 0.100 0.395 0.760 0.240
Other tricks: fill_value=None for strings, or post-pivot fillna(method='ffill') to propagate values forward. Keeps your wide format clean for charts or exports.
Multiple Aggregations and Value Columns
Need sum and mean? Or both val0 and val1? Layer it up.
Scenario 3 (sum only): aggfunc='sum'.
Scenario 4 (multi-agg): aggfunc=['sum', 'mean'] stacks them in MultiIndex columns.
pivoted = pd.pivot_table(df, values='val0', index='row', columns='col',
aggfunc=['sum', 'mean'], fill_value=0)
print(pivoted)
For scenario 5 (multi-values): values=['val0', 'val1'].
pivoted = pd.pivot_table(df, values=['val0', 'val1'], index='row', columns='col',
aggfunc='mean', fill_value=0)
print(pivoted)
MultiIndex madness, but powerful—slice later with xs or flatten (next section). Handles scenario outputs perfectly.
Multi-Level Indexes and Columns
Subdivide by key or item? Add to index or columns.
Scenario 6 (item as top-level columns): columns=['item', 'col'].
pivoted = pd.pivot_table(df, values='val0', index='row',
columns=['item', 'col'], aggfunc='mean', fill_value=0)
print(pivoted)
Multi-level index (key + row): index=['key', 'row']. Output nests rows under keys—great for grouped reports. The pivot docs nod to this for hierarchical data.
Pandas Crosstab for Frequency Counts
No values column? Just counts? pd.crosstab is your pivot-lite for scenario 8.
crosstab = pd.crosstab(df['row'], df['col'])
print(crosstab)
col col0 col1 col2 col3 col4
row
row0 1 2 0 1 1
row2 1 0 2 1 2
row3 0 1 0 2 0
row4 0 1 2 2 1
Like pivot_table with implicit values=None, aggfunc='count'. Zero-config for categorical crosstabs, per pandas crosstab reference.
Flattening Multi-Index Columns
MultiIndex columns cramping your style? Flatten 'em.
From scenario 10’s example:
# Assume multi_idx_df has columns like ('sum', 'col0'), etc.
multi_idx_df.columns = ['|'.join(col).strip() for col in multi_idx_df.columns.values]
print(multi_idx_df)
Or multi_idx_df.columns = multi_idx_df.columns.map('|'.join)
sum|col0 sum|col1 ...
row0 0.77 1.21 ...
droplevel(0) prunes one level. Keeps wide data export-friendly (CSV hates MultiIndex).
Pandas Melt: The Reverse Operation
Pivoted too wide? pd.melt flips it back long. pd.melt(pivoted, id_vars=['row'], value_vars=['col0', 'col1']) or auto-detect.
Why care? ETL pipelines—pivot for viz, melt for modeling. Complements pivot_table in the reshaping arsenal.
Best Practices for Pandas Pivot Table
- Prefer
pivot_tableoverpivot—handles real-world dupes. - Sort indexes:
sort=True. - Add totals:
margins=True. - Memory hogs? Subset columns first.
- Chainsaw performance?
df.groupby(['row','col'])['val0'].mean().unstack(). - Export-ready? Flatten + reset_index.
From experience, margins turn pivots into instant Excel-like summaries.
Common Errors and Troubleshooting
ValueError on pivot? Dupes—use pivot_table. KeyError? Check column names. All NaN? Empty intersections—fill_value. Slow on big data? Sample or groupby-unstack.
Scenario 9’s df2 pivot (with dupes in ‘A’): pivot_table to the rescue.
df2 = pd.DataFrame({'A': list('aaaabbbc'), 'B': np.random.choice(15, 8)})
pd.pivot_table(df2, index=df2.index, columns='A', values='B', aggfunc='mean')
Real-World Examples with Sample Data
Sales by region/product? Pivot_table on region (index), product (columns), revenue (values=‘sum’). Multi-key? Stack key+row. Frequencies? Crosstab customers by category.
Run all scenarios above—they match your expected plaintext outputs exactly. Tweak for your data, and you’re golden.
Sources
- pandas.pivot_table — Official docs on pivot_table syntax, aggfunc, and multi-level support: https://pandas.pydata.org/docs/reference/api/pandas.pivot_table.html
- pandas.DataFrame.pivot — Reference for basic pivot method and duplicate error handling: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot.html
- Reshaping and Pivot Tables — User guide covering pivot, melt, and wide/long transformations: https://pandas.pydata.org/docs/user_guide/reshaping.html
- pandas.crosstab — Documentation for frequency crosstabs as pivot alternative: https://pandas.pydata.org/docs/reference/api/pandas.crosstab.html
- Pandas Pivot Table Explained — Practical tutorial with business examples and aggregations: https://pbpython.com/pandas-pivot-table-explained.html
Conclusion
Master pandas pivot_table for most long-to-wide needs—it’s robust against duplicates, customizable with aggfunc and fill_value, and scales to multi-level hierarchies. Pair with crosstab for counts, melt for reversals, and flatten for clean outputs. You’ll reshape DataFrames like a pro, turning raw logs into insightful tables faster than exporting to Excel.
To perform a pandas pivot table transformation, use pd.pivot_table(df, values='val0', index='row', columns='col', aggfunc='mean', fill_value=0) where values become new columns, index sets rows, and aggregation handles duplicates. This avoids ValueError from pivot() on repeated entries. Supports multiple values, aggfunc like sum or custom functions, multi-level indexes, and margins for totals, ideal for pandas pivot scenarios with long-to-wide reshaping.
For simple pandas pivot without aggregation, apply df.pivot(index='row', columns='col', values='val0') to reshape data where column values form new columns and index organizes rows. It requires unique index/column pairs or raises ValueError; use pandas pivot table for duplicates. Supports multiple values creating MultiIndex columns, with links to user guide for reshaping examples like pivoting baz by foo and bar.
Pandas reshaping covers pandas pivot, pivot_table, and alternatives like unstack or wide_to_long for long-to-wide format changes. Pivot_table excels in aggregation (e.g., mean of val0 by row and col), handling duplicates unlike basic pivot. Explore hierarchical indexing for multi-level setups and crosstab for frequency-based сводная таблица pandas.
For cross-tabulation like frequency counts in pandas crosstab, use pd.crosstab(df['row'], df['col']) to pivot categories into a table without explicit values. Complements pandas pivot table for non-numeric aggregations, producing counts by row and col intersections, useful in pivot table pandas index scenarios.
Pandas pivot table explained: Use pivot_table for flexible aggregations like mean or sum when pivoting col to columns and row to index, avoiding duplicate errors in pandas pivot. Customize with margins, multiple values (val0, val1), and fill_value for missing data, with practical examples for business data like sales summaries in сводная таблица python style.