Pandas

How to Pivot a Dataframe in Pandas — With Examples

Answer

Use df.pivot() to reshape data from long to wide format, specifying the index, columns, and values. Use df.pivot_table() when you have duplicate entries and need to aggregate them.

Why This Happens

Data often comes in long format (one row per observation) but analysis or visualization needs wide format (one row per entity, columns for each category). Pivoting transforms between these shapes — essential for creating summary tables, heatmaps, or preparing data for certain models.

Solution

The rule: use pivot() for simple reshaping, pivot_table() when you need aggregation or have duplicates.

import pandas as pd

df = pd.DataFrame({
    'date': ['2024-01', '2024-01', '2024-02', '2024-02'],
    'product': ['A', 'B', 'A', 'B'],
    'sales': [100, 150, 200, 175]
})

# ✅ Basic pivot (no duplicate entries)
df.pivot(index='date', columns='product', values='sales')
# Result: dates as rows, products as columns, sales as values

# ❌ Problematic: pivot fails with duplicate entries
df_dupes = pd.DataFrame({
    'date': ['2024-01', '2024-01', '2024-01'],
    'product': ['A', 'A', 'B'],  # two entries for A
    'sales': [100, 50, 150]
})
df_dupes.pivot(index='date', columns='product', values='sales')
# ValueError: Index contains duplicate entries

# ✅ Fixed: use pivot_table with aggregation
df_dupes.pivot_table(index='date', columns='product', values='sales', aggfunc='sum')

# ✅ Multiple aggregations
df.pivot_table(index='date', columns='product', values='sales', aggfunc=['sum', 'mean'])

# ✅ Multiple value columns
df.pivot_table(index='date', columns='product', values=['sales', 'quantity'], aggfunc='sum')

# ✅ Reverse: wide to long with melt
wide_df.melt(id_vars=['date'], var_name='product', value_name='sales')

Better Workflow

Zerve's persistent state lets you experiment with different pivot configurations without losing your original dataframe. Try one shape, see if it works for your analysis, try another — no need to re-run your data loading.