Pandas: Reshaping and Pivoting

Q: How do I reverse a melt operation?

Use pivot or pivot_table to restore the original shape: long.pivot(index='product', columns='month', values='sales') . melt and pivot are inverse operations. Note that data types may change after melting (numeric columns may become object), so you might need astype conversion before restoring.

Last updated: 2026-08-26

The same data can be stored as a "wide table" for human readability or a "long table" for programmatic analysis. Pandas provides four essential tools for converting between these formats: pivot / melt / stack / unstack. This lesson uses Mermaid diagrams to illustrate the direction of each reshape operation, giving you an intuitive understanding of what "melting" and "pivoting" really mean — a visualization that most competing tutorials lack.

⚠️ Note: The code below requires a local Python environment to run.

1. What You Will Learn

❶ pivot — basic pivoting
❷ pivot_table — aggregation pivoting
❸ melt — wide to long
❹ stack / unstack
❺ Wide vs. long tables and Tidy Data

2. Alice's Sales Report Transformation

(1) The Pain Point: Report Format ≠ Analysis Format

Alice's monthly sales report is in wide format (months spread across columns), but analysis requires long format (months as a single column):

PYTHON

import pandas as pd

# Wide format — easy to read, hard to analyze
wide = pd.DataFrame({
    'product': ['Latte', 'Mocha', 'Americano'],
    'Jan': [320, 280, 200],
    'Feb': [350, 300, 220],
    'Mar': [380, 310, 240]
})
print(wide)
#     product  Jan  Feb  Mar

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) The Solution: melt to Long Format

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: melt wide to long (Difficulty ⭐)

PYTHON

import pandas as pd

wide = pd.DataFrame({
    'product': ['Latte', 'Mocha', 'Americano'],
    'Jan': [320, 280, 200],
    'Feb': [350, 300, 220],
    'Mar': [380, 310, 240]
})

# Melt: month columns become rows
long = wide.melt(id_vars='product', var_name='month', value_name='sales')
print(long)
#     product month  sales
# 0     Latte   Jan    320
# 1     Mocha   Jan    280
# 2 Americano   Jan    200
# 3     Latte   Feb    350
# ...

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

3. Wide Tables vs. Long Tables

(1) Mermaid Diagram: Wide ⇄ Long

graph LR
    subgraph Wide["Wide Table — Human-Friendly"]
        W["product | Jan | Feb | Mar<br>Latte   | 320 | 350 | 380<br>Mocha  | 280 | 300 | 310"]
    end
    subgraph Long["Long Table — Machine-Analysis-Friendly"]
        L["product | month | sales<br>Latte   | Jan   | 320<br>Latte   | Feb   | 350<br>Latte   | Mar   | 380<br>Mocha  | Jan   | 280<br>..."]
    end
    Wide -->|"melt()"| Long
    Long -->|"pivot()"| Wide

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) Wide vs. Long Comparison

Feature	Wide Table	Long Table
Where values live	In column names	In rows
Best for	Human reading / reports	Machine analysis / ggplot
Adding a new month	Add a column (structure changes)	Add rows (structure unchanged)
groupby	Inconvenient	Naturally supported
Typical use	Excel reports	Databases / Tidy Data

4. pivot — Basic Pivoting

(1) Basic Pivot

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: pivot long to wide (Difficulty ⭐)

PYTHON

import pandas as pd

long = pd.DataFrame({
    'product': ['Latte', 'Latte', 'Latte', 'Mocha', 'Mocha', 'Mocha'],
    'month': ['Jan', 'Feb', 'Mar', 'Jan', 'Feb', 'Mar'],
    'sales': [320, 350, 380, 280, 300, 310]
})

# Pivot: unique row=product, unique column=month, values=sales
wide = long.pivot(index='product', columns='month', values='sales')
print(wide)
# month     Feb   Jan   Mar
# product
# Americano  220   200   240
# Latte      350   320   380
# Mocha      300   280   310

# Reset index to make product a regular column
wide = wide.reset_index()
print(wide.columns)  # MultiIndex → flatten

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

⚠️ Note: pivot requires the index+columns combination to be unique. If there are duplicates (e.g., multiple records for the same product in the same month), it will raise an error. In that case, use pivot_table (which aggregates automatically).

5. pivot_table — Aggregation Pivoting

(1) Aggregation Pivot

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: pivot_table aggregation (Difficulty ⭐⭐)

PYTHON

import pandas as pd

# Multiple entries per product+month (need aggregation)
df = pd.DataFrame({
    'product': ['Latte', 'Latte', 'Latte', 'Latte', 'Mocha', 'Mocha'],
    'month': ['Jan', 'Jan', 'Feb', 'Feb', 'Jan', 'Feb'],
    'sales': [150, 170, 180, 170, 140, 160],
    'quantity': [30, 34, 36, 34, 28, 32]
})

# pivot_table with aggregation function
result = pd.pivot_table(
    df,
    values='sales',
    index='product',
    columns='month',
    aggfunc='sum'   # default is 'mean'
)
print(result)
# month     Feb   Jan
# product
# Latte     350   320
# Mocha     160   140

# Multiple aggregation functions
result2 = pd.pivot_table(
    df,
    values=['sales', 'quantity'],
    index='product',
    columns='month',
    aggfunc={'sales': 'sum', 'quantity': 'mean'}
)

# Margins — add row/column totals
result3 = pd.pivot_table(
    df, values='sales', index='product', columns='month',
    aggfunc='sum', margins=True, margins_name='Total'
)
print(result3)

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) pivot vs. pivot_table

Feature	pivot	pivot_table
Duplicate values	Raises error	Aggregates
Aggregation function	None	sum/mean/count, etc.
margins	Not supported	✅ Row/column totals
Multiple value columns	Single column	Multiple columns
Best for	Clean data with no duplicates	Data with duplicates needing aggregation

6. melt — In Depth

(1) melt Parameters

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: melt parameters explained (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'product': ['Latte', 'Mocha'],
    'region': ['North', 'South'],
    'Jan_sales': [320, 280],
    'Feb_sales': [350, 300],
    'Jan_quantity': [64, 56],
    'Feb_quantity': [70, 60]
})

# Basic melt — all non-id columns become rows
result1 = df.melt(id_vars=['product', 'region'])
print(result1)

# Specify value_vars — only melt certain columns
result2 = df.melt(
    id_vars=['product', 'region'],
    value_vars=['Jan_sales', 'Feb_sales'],
    var_name='month',
    value_name='sales'
)
print(result2)

# Multiple id_vars
result3 = df.melt(
    id_vars=['product'],
    value_vars=['Jan_sales', 'Feb_sales'],
    var_name='month_sales',
    value_name='amount'
)

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

7. stack / unstack

(1) stack — Columns to Rows

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: stack/unstack with MultiIndex (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'Jan': [320, 280],
    'Feb': [350, 300],
    'Mar': [380, 310]
}, index=['Latte', 'Mocha'])
df.index.name = 'product'

# stack: columns → row level (wide → long)
stacked = df.stack()
print(stacked)
# product  month
# Latte    Jan      320
#          Feb      350
#          Mar      380
# Mocha    Jan      280
# ...

# unstack: row level → columns (long → wide)
unstacked = stacked.unstack()
print(unstacked)
#          Jan   Feb   Mar
# product
# Latte    320   350   380
# Mocha    280   300   310

# With MultiIndex: choose which level to unstack
multi = pd.DataFrame({
    'sales': [320, 350, 280, 300],
    'region': ['North', 'North', 'South', 'South'],
    'month': ['Jan', 'Feb', 'Jan', 'Feb']
})
pivot = multi.set_index(['region', 'month'])['sales']
print(pivot.unstack(level='month'))

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) Comparison of Four Reshaping Methods

Method	Direction	Input → Output	Best For
pivot	Long → Wide	Specify index/columns/values	Long tables with no duplicates
pivot_table	Long → Wide	+ aggfunc aggregation	Long tables with duplicates
melt	Wide → Long	Specify id_vars	Column names become row values
stack	Columns → Rows	columns → index level	MultiIndex operations
unstack	Rows → Columns	index level → columns	Inverse of stack

8. Complete Example: Full Wide-Long Conversion Pipeline

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: Full wide-long conversion pipeline (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd
import numpy as np

# ============================================
# Comprehensive example: Wide ↔ Long
# Sales data reshape pipeline
# ============================================

# 1. Start with wide format (report-style)
np.random.seed(42)
wide = pd.DataFrame({
    'product': ['Latte', 'Mocha', 'Americano', 'Espresso', 'Cappuccino'],
    'region': ['North', 'South', 'East', 'West', 'North'],
    'Jan': np.random.randint(200, 500, 5),
    'Feb': np.random.randint(220, 520, 5),
    'Mar': np.random.randint(240, 540, 5),
    'Apr': np.random.randint(250, 550, 5)
})

print("=== Wide Format (5 products × 4 months) ===")
print(wide)

# 2. Melt to long format
long = wide.melt(
    id_vars=['product', 'region'],
    var_name='month',
    value_name='sales'
)
print(f"\n=== Long Format: {len(long)} rows ===")
print(long.head(8))

# 3. Analyze in long format (groupby is natural)
monthly_total = long.groupby('month')['sales'].sum()
print(f"\n=== Monthly Totals ===\n{monthly_total}")

region_avg = long.groupby('region')['sales'].mean().round(0)
print(f"\n=== Region Avg ===\n{region_avg}")

# 4. Pivot back to wide (product × month summary)
summary_wide = pd.pivot_table(
    long, values='sales', index='product', columns='month',
    aggfunc='sum', margins=True, margins_name='Total'
)
print(f"\n=== Pivot Table Summary ===")
print(summary_wide)

# 5. Stack/unstack with MultiIndex
multi = long.set_index(['region', 'product', 'month'])['sales']
by_region = multi.unstack(level='month')
print(f"\n=== Unstack by Month (Region×Product) ===")
print(by_region.head(8))

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

❓ FAQ

Q What is the difference between pivot and pivot_table?

A pivot requires the index+columns combination to be unique — it raises an error if duplicates exist. pivot_table handles duplicates by aggregating them (default is mean, but you can specify sum/count, etc.) and also supports margins (row/column totals). Use pivot for simple data with no duplicates; use pivot_table when duplicates exist or aggregation is needed. In practice, pivot_table is the safer choice for 80% of everyday scenarios.

Q What are id_vars in melt?

A id_vars are the columns that do NOT get melted — they remain as row identifiers. All other columns have their names collected into a variable column (var_name) and their values collected into a value column (value_name). For example, after melting month columns Jan/Feb/Mar, the strings 'Jan'/'Feb'/'Mar' become values in the month column, and the corresponding numbers become values in the sales column.

Q Are stack and unstack inverses of each other?

A Yes — stack pushes columns into rows (wide → long), and unstack spreads rows into columns (long → wide). df.stack().unstack() restores the original table. They operate on MultiIndex levels — stack adds a row level, and unstack adds a column level. The level parameter controls which level is affected.

Q When should I use wide vs. long tables?

A Wide tables are ideal for human reading (reports, presentations, Excel). Long tables are ideal for programmatic analysis (groupby, merge, visualization). The Tidy Data principle recommends long format: one variable per column, one observation per row. Store data in long format (stable structure) and display it in wide format (pivot output).

Q What do I do when pivot reports duplicate values?

A This means the index+columns combination has duplicate rows (e.g., multiple records for the same product in the same month). You have two options: ① Use pivot_table with aggfunc='sum'/'mean' to aggregate automatically; ② Aggregate with groupby first, then pivot. pivot_table is more concise and generally preferred.

Q How do I reverse a melt operation?

A Use pivot or pivot_table to restore the original shape: long.pivot(index='product', columns='month', values='sales'). melt and pivot are inverse operations. Note that data types may change after melting (numeric columns may become object), so you might need astype conversion before restoring.

Q What is Tidy Data?

A Tidy Data is a data organization principle proposed by Hadley Wickham: ① Each variable occupies one column; ② Each observation occupies one row; ③ Each type of observational unit occupies one table. Long tables naturally satisfy Tidy Data; wide tables do not (the month variable is hidden in column names). Pandas' melt exists precisely to "tidy" wide tables into Tidy Data format.

📖 Summary

pivot converts long to wide (when there are no duplicates); pivot_table converts long to wide with aggregation (when duplicates exist)
melt converts wide to long: id_vars are the preserved columns; remaining column names become a variable column, and their values become a value column
stack pushes columns into rows (wide → long); unstack spreads rows into columns (long → wide) — they are inverse operations
Wide tables are human-friendly; long tables are machine-analysis-friendly; Tidy Data recommends long format
Typical workflow: raw data → melt to tidy → groupby to analyze → pivot_table to report
If pivot raises a duplicate error → switch to pivot_table + aggfunc

📝 Exercises

Basic (Difficulty ⭐): Create a wide table (3 products × 3 months), use melt to convert it to long format, then use pivot to convert it back to wide format, and verify the results match.
Intermediate (Difficulty ⭐⭐): Create sales data with duplicate values (multiple records for the same product in the same month), then use pivot_table(aggfunc='sum') + margins=True to generate a pivot table with row and column totals.
Challenge (Difficulty ⭐⭐⭐): Simulate long-format data for 3 regions × 4 products × 3 months, then complete the following: set_index to create a MultiIndex → unstack to spread by month → stack to compress back → melt for an alternative transformation → pivot_table for cross-analysis.

← Previous: Concatenation and Appending · Next: String Operations →

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Pandas: Reshaping and Pivoting

1. What You Will Learn

2. Alice's Sales Report Transformation

(1) The Pain Point: Report Format ≠ Analysis Format

(2) The Solution: melt to Long Format

▶ Example

3. Wide Tables vs. Long Tables

(1) Mermaid Diagram: Wide ⇄ Long

(2) Wide vs. Long Comparison

4. pivot — Basic Pivoting

(1) Basic Pivot

▶ Example

5. pivot_table — Aggregation Pivoting

(1) Aggregation Pivot

▶ Example

(2) pivot vs. pivot_table

6. melt — In Depth

(1) melt Parameters

▶ Example

7. stack / unstack

(1) stack — Columns to Rows

▶ Example

(2) Comparison of Four Reshaping Methods

8. Complete Example: Full Wide-Long Conversion Pipeline

▶ Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好