Melt and Pivot Reshaping

Wide vs Long Format

Data comes in two fundamental shapes, and knowing which one you need is half the battle. Wide format stores each variable in its own column. Long format (also called tidy or stacked format) stores variable names in one column and values in another.

Consider monthly sales data. In wide format, each month is a column:

import pandas as pd

wide = pd.DataFrame({
    'product': ['A', 'B', 'C'],
    'jan': [100, 150, 200],
    'feb': [120, 160, 210],
    'mar': [110, 170, 220]
})
print(wide)
#   product  jan  feb  mar
# 0       A  100  120  110
# 1       B  150  160  170
# 2       C  200  210  220

In long format, month names and values are stacked into two columns:

long = pd.DataFrame({
    'product': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
    'month': ['jan', 'feb', 'mar', 'jan', 'feb', 'mar', 'jan', 'feb', 'mar'],
    'sales': [100, 120, 110, 150, 160, 170, 200, 210, 220]
})
print(long)
#   product month  sales
# 0       A   jan    100
# 1       A   feb    120
# 2       A   mar    110
# 3       B   jan    150
# 4       B   feb    160
# 5       B   mar    170
# 6       C   jan    200
# 7       C   feb    210
# 8       C   mar    220

Neither format is universally better. Wide format is compact and human-readable — it is what you would put in a report or a slide. Long format is structured for computation — it is what groupby, merge, and most plotting libraries expect.

The principle that guides the choice: long format is the default for analysis. When each row is an observation and each column is a variable, operations like filtering, grouping, and joining become straightforward. Wide format is a presentation concern — convert to it only when you need to display results.

This distinction matters because most real-world data cleaning is a series of conversions between these two shapes. Data arrives in wide format from Excel exports, database dumps, and API responses. You melt it to long format for analysis. You pivot it back to wide format for reporting. Fluency in both directions is a core data manipulation skill.

Melting: Wide to Long

pd.melt() converts wide-format data to long-format by unpivoting columns into rows. The function takes identifier columns (which stay as columns) and value columns (which get stacked into a variable-value pair).

import pandas as pd

wide = pd.DataFrame({
    'product': ['A', 'B', 'C'],
    'jan': [100, 150, 200],
    'feb': [120, 160, 210],
    'mar': [110, 170, 220]
})

long = pd.melt(
    wide,
    id_vars=['product'],
    value_vars=['jan', 'feb', 'mar'],
    var_name='month',
    value_name='sales'
)
print(long)
#   product month  sales
# 0       A   jan    100
# 1       B   jan    150
# 2       C   jan    200
# 3       A   feb    120
# 4       B   feb    160
# 5       C   feb    210
# 6       A   mar    110
# 7       B   mar    170
# 8       C   mar    220

The id_vars parameter lists columns that identify each observation and should remain as columns. The value_vars parameter lists columns to unpivot. If you omit value_vars, all columns not in id_vars are melted. The var_name and value_name parameters let you name the resulting variable and value columns instead of using the defaults variable and value.

A practical example: survey data where each respondent's answers to multiple questions are stored in separate columns.

survey = pd.DataFrame({
    'respondent_id': [1, 2, 3],
    'age': [25, 34, 45],
    'q1_satisfaction': [4, 5, 3],
    'q2_recommend': [3, 4, 2],
    'q3_return': [5, 4, 3]
})

long_survey = pd.melt(
    survey,
    id_vars=['respondent_id', 'age'],
    value_vars=['q1_satisfaction', 'q2_recommend', 'q3_return'],
    var_name='question',
    value_name='score'
)
print(long_survey)

After melting, you can group by question to compute average scores per question, or group by age to see how responses vary by demographic. These operations are awkward or impossible in the wide format.

One thing to watch: melting increases row count. A DataFrame with 1000 rows and 12 month columns becomes 12,000 rows after melting. This is expected, but be aware of it when working with large datasets. The long format trades row count for structural clarity.

Pivoting: Long to Wide

pivot() is the inverse of melt(). It converts long-format data to wide-format by spreading the values of one column into multiple columns. The key difference from melt() is that pivot() requires a unique combination of index and column values — it cannot handle duplicates.

import pandas as pd

long = pd.DataFrame({
    'product': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
    'month': ['jan', 'feb', 'mar', 'jan', 'feb', 'mar', 'jan', 'feb', 'mar'],
    'sales': [100, 120, 110, 150, 160, 170, 200, 210, 220]
})

wide = long.pivot(index='product', columns='month', values='sales')
print(wide)
# month  jan  feb  mar
# product
# A      100  120  110
# B      150  160  170
# C      200  210  220

The index parameter specifies which column becomes the row index. The columns parameter specifies whose values become column headers. The values parameter specifies which column provides the cell values.

The result has a MultiIndex on the columns if you pass a list to columns. Call .reset_index() to flatten the index back into a column.

wide = long.pivot(index='product', columns='month', values='sales').reset_index()
print(wide)
# month product  jan  feb  mar
# 0           A  100  120  110
# 1           B  150  160  170
# 2           C  200  210  220

pivot() raises a ValueError if there are duplicate entries for the same (index, columns) combination. This is a feature, not a bug — it prevents silent data loss. If you have duplicates, you need pivot_table() instead.

# This raises ValueError if (product, month) is not unique
# ValueError: Index contains duplicate entries, cannot reshape

When to use pivot(): when you have a clean long-format dataset with unique keys and you want to reshape it for display or for feeding into an algorithm that expects wide input. When to use pivot_table(): when you need to aggregate duplicates during the reshape.

Pivot Table: Aggregation During Reshape

pivot_table() is pivot() with a built-in aggregation step. It handles duplicate (index, column) combinations by applying an aggregation function, defaulting to the mean.

import pandas as pd

# Long-format sales data with duplicate (product, month) entries
long = pd.DataFrame({
    'product': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
    'month': ['jan', 'jan', 'feb', 'feb', 'jan', 'jan', 'feb', 'feb'],
    'store': ['X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y'],
    'sales': [100, 120, 110, 130, 150, 160, 170, 180]
})

# pivot_table aggregates duplicates (mean by default)
wide = long.pivot_table(
    index='product',
    columns='month',
    values='sales',
    aggfunc='mean'
)
print(wide)
# month   jan  feb
# product
# A       110  120
# B       155  175

The aggfunc parameter accepts any aggregation function: 'sum', 'mean', 'count', 'min', 'max', or a custom function. You can also pass a list of functions to compute multiple aggregations at once.

wide = long.pivot_table(
    index='product',
    columns='month',
    values='sales',
    aggfunc=['mean', 'sum', 'count']
)
print(wide)

pivot_table() also supports multiple index and column levels, creating a MultiIndex on both axes. This is useful for cross-tabulations.

# Cross-tabulation: product vs month, broken down by store
wide = long.pivot_table(
    index=['product', 'store'],
    columns='month',
    values='sales',
    aggfunc='sum'
)
print(wide)

The margins parameter adds row and column totals, which is useful for summary reports.

wide = long.pivot_table(
    index='product',
    columns='month',
    values='sales',
    aggfunc='sum',
    margins=True,
    margins_name='Total'
)
print(wide)
# month   jan  feb  Total
# product
# A       220  240    460
# B       310  350    660
# Total   530  590   1120

Choosing between pivot() and pivot_table() is simple: if your data has unique keys and you want a pure reshape, use pivot(). If you need to aggregate during the reshape, use pivot_table(). Using pivot_table() when you expect unique keys is also fine — it will just apply the aggregation function to single values, which is a no-op.