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.

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:
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:
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:
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:
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 toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.