Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Reshaping Data with Pandas: pivot, pivot_table, and melt

Learn how to reshape DataFrames with pandas pivot, pivot_table, and melt. Understand when to use each method and how they handle duplicate values.

~4 min readPython
#pandas#data reshaping#pivot table#melt
Illustration of a pandas DataFrame being reshaped from wide to long format using pivot and melt.

When working with tabular data, you often need to change the layout of a DataFrame to make analysis or visualization easier. The pandas library provides three core methods for this: pivot, pivot_table, and melt. Use pivot when each index-column pair is unique, pivot_table when duplicates need aggregation, and melt to unpivot wide data into long format. This article explains each method, when to use it, and how the operations compare.

Why Reshape Data?

Data comes in many shapes. A DataFrame might have one row per subject with multiple measurement columns (wide format), or one row per subject-measurement pair (long format). Many statistical and plotting libraries expect long format, while some reporting tasks favor wide format. Reshaping is the process of converting between these layouts without changing the underlying information.

Pandas offers pivot and pivot_table to go from long to wide, and melt to go from wide to long. Choosing the right method depends on the structure of your data and whether duplicate entries exist.

pivot: Simple Index-Column Reshaping

The pivot method rearranges a DataFrame by specifying which column becomes the index, which becomes the columns, and which provides the values. It works best when each combination of index and column is unique.

Consider a DataFrame recording sales per product and region:

python
import pandas as pd df = pd.DataFrame({ 'product': ['A', 'A', 'B', 'B'], 'region': ['North', 'South', 'North', 'South'], 'sales': [100, 150, 200, 120] }) pivoted = df.pivot(index='product', columns='region', values='sales') print(pivoted)

This produces a table with products as rows and regions as columns. The pivot method does not aggregate; it simply reindexes. If the same index-column pair appears more than once, pandas raises a ValueError because there is no unique value for that cell.

pivot_table: Aggregation for Duplicate Entries

When your data contains duplicate index-column combinations, pivot_table is the appropriate tool. It accepts an aggfunc parameter (defaulting to mean) to combine duplicate entries.

Suppose the same product-region pair appears multiple times:

python
df_dup = pd.DataFrame({ 'product': ['A', 'A', 'A', 'B', 'B'], 'region': ['North', 'North', 'South', 'North', 'South'], 'sales': [100, 110, 150, 200, 120] }) pivot_table = df_dup.pivot_table(index='product', columns='region', values='sales', aggfunc='sum') print(pivot_table)

Here, the two North entries for product A are summed. You can also use aggfunc='mean', 'max', or pass a dictionary to apply different aggregations to different value columns. pivot_table also supports fill_value to replace missing cells with a default, and margins to add row/column totals.

melt: Unpivoting Wide Data

The melt method converts a wide DataFrame into a long format by unpivoting specified columns into two new columns: one for variable names and one for values. This is often described as the inverse of pivot.

For example, if you have quarterly sales columns:

python
wide_df = pd.DataFrame({ 'product': ['A', 'B'], 'Q1': [100, 200], 'Q2': [150, 120] }) long_df = wide_df.melt(id_vars=['product'], var_name='quarter', value_name='sales') print(long_df)

The resulting DataFrame has one row per product-quarter pair. When id_vars is provided, melt unpivots all columns not listed in it by default. You can restrict which columns to unpivot using value_vars.

Combining pivot and melt for Complex Transformations

Real-world reshaping often requires more than a single call. For instance, you might need to melt multiple value columns, then pivot to create a summary table. The key is to understand the intermediate format.

Suppose you have a DataFrame with separate columns for actual and forecast sales across regions. You can melt both value columns into a long format, then pivot to get a wide table with region as columns and metric as rows:

python
df = pd.DataFrame({ 'region': ['North', 'South'], 'actual': [100, 150], 'forecast': [110, 160] }) long = df.melt(id_vars=['region'], var_name='metric', value_name='value') wide = long.pivot(index='metric', columns='region', values='value') print(wide)

This two-step approach gives you full control over the final layout. Without melt, you would need to manually stack columns.

Performance and Memory Considerations

pivot_table performs aggregation, which adds computational overhead compared to pivot. For large datasets, the difference can be noticeable. If your data is already unique, use pivot to avoid unnecessary grouping. Conversely, melt is generally efficient because it simply rearranges the data structure, but it increases the number of rows, which can affect memory usage.

When working with very large DataFrames, consider whether you need the reshaped result in memory at all. Sometimes you can use groupby and agg directly to compute summaries without creating a wide intermediate. The choice between pivot_table and groupby often comes down to whether you need the result in a pivot-like layout.

Common Pitfalls and Edge Cases

One frequent mistake is using pivot on data with duplicate index-column pairs, which raises an error. Check for duplicates on the combination of columns that will become the new index and columns, or switch to pivot_table.

Another issue is handling missing values. pivot leaves missing cells as NaN, while pivot_table can fill them with fill_value. When using melt, missing values in the original wide data become rows with NaN in the value column, which may need to be dropped or imputed.

Also note that pivot and pivot_table require the values used as the new index and column labels to be hashable. If a column contains non-hashable values such as lists, convert them before pivoting.

Finally, if you need a specific order in the variable column produced by melt, pass value_vars explicitly. Otherwise the remaining non-id_vars columns are used in their existing column order.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.