Pivot and Melt in Python

Ever stared at a DataFrame that has one row per product per store and wished you could see it as a matrix with stores as columns and products as rows? Or maybe you've had the opposite problem: wide summary tables of monthly sales that you need to melt into a tall, tidy format before you can plot them with Matplotlib or feed them to a machine learning model. That friction — reshaping data between wide and long formats — is one of the most common data wrangling pains in pandas, and it's exactly what pivot and melt are built to solve. By the end of this lesson, you'll be able to reshape data with pivot and melt confidently, turning messy multi-column tables into analysis-ready structures and back again.

The problem this lesson solves

Real-world data rarely arrives in the shape your analysis requires. You might export sales figures where each month is a separate column, but your scatter plot needs one row per sale. Or you might have survey responses stacked long (one row per respondent per question) when you need a wide matrix for correlation analysis. Manually reshaping with loops or groupby hacks is slow, error-prone, and unreadable. The core pain is a mismatch between data shape and analytical need. Without a systematic way to reshape, you'll burn hours writing brittle code that breaks when your data changes. Pandas provides two elegant, high-level tools — pivot and melt — that flip between wide and long formats with a single call. Learning to reshape data with pivot and melt is not just a convenience; it's a fundamental skill in the data science workflow, because most visualization and modeling libraries expect data in a tidy, long format, while many raw exports come wide.

Core concept / mental model

Think of a DataFrame as a grid with two dimensions. Wide format has many columns, each representing a variable (e.g., sales for Jan, Feb, Mar). Long format has fewer columns but many rows, with a key column that identifies the variable (e.g., month) and a value column holding the numbers. The mental model for pivot and melt is a hinge:

• Melt rotates columns down into rows. It's like grabbing the column headers and collapsing them into a single column of category labels, with corresponding values in another column. This is often called 'unpivoting'.
• Pivot rotates rows up into columns. It spreads unique values from a column across new column headers, using another column's values to fill the grid. This is 'pivoting'.

Imagine a physical flexagon: pivot expands the rows into columns (wide), melt compresses the columns into rows (long). They are inverse operations, though not perfectly symmetrical in all cases (duplicate entries cause pivot to fail, but pivot_table handles them). The key mental shift is: you are choosing which column becomes the row index, which becomes the column index, and which provides the values.

Definitions you'll use daily

• DataFrame.pivot(index=..., columns=..., values=...) — reshapes by specifying which column to use as the new row index, which to use as column headers, and which to fill the cells.
• pandas.melt(frame, id_vars=..., value_vars=..., var_name=..., value_name=...) — converts wide to long, keeping identifier columns fixed and stacking value columns.

These are the two pillars of data reshaping in pandas. Once internalized, you'll see them everywhere in data cleaning pipelines.

How it works step by step

Step 1: Identify your current format

Look at your DataFrame. If columns represent categories (months, products, conditions), you're wide. If you have a column that lists those categories with values in another column, you're long.

Step 2: Decide your target format

• For plotting with Matplotlib/Seaborn, long format is usually required for hue, col, etc.
• For correlation matrices or lookup tables, wide format is convenient.
• For database-like operations (filtering, pivoting per group), long is often easier.

Step 3: Use melt to go wide → long

Call melt on the wide DataFrame, specifying which columns are identifiers (e.g., id_vars='customer_id'). All other columns get stacked into a variable column and a value column.

import pandas as pd

# Wide sales: each month a column
df_wide = pd.DataFrame({
    'customer_id': [101, 102],
    'Jan': [250, 180],
    'Feb': [300, 220],
    'Mar': [270, 210]
})

# Melt into long format
df_long = df_wide.melt(id_vars='customer_id', var_name='month', value_name='sales')
print(df_long)

Output:

   customer_id month  sales
0          101   Jan    250
1          102   Jan    180
2          101   Feb    300
3          102   Feb    220
4          101   Mar    270
5          102   Mar    210

Step 4: Use pivot to go long → wide

From the long frame, pivot by setting the index to a unique identifier, columns to the variable column, and values to the value column.

# Pivot back to wide
df_wide_restored = df_long.pivot(index='customer_id', columns='month', values='sales')
print(df_wide_restored)

Output:

month         Feb   Jan   Mar
customer_id
101           300   250   270
102           220   180   210

Notice that pivot doesn't reset the index by default — you may call .reset_index() if you want a flat column.

Hands-on walkthrough

Let's apply these tools to a realistic scenario: a dataset of student exam scores across subjects.

Setting up the data

import pandas as pd

# Raw export: one row per student, subjects as columns (wide)
students = pd.DataFrame({
    'student_id': [1, 2, 3],
    'math': [85, 90, 78],
    'science': [92, 88, 95],
    'english': [79, 84, 92]
})
print('Original (wide):')
print(students)

Exercise 1: Melt to long for boxplots

# Melt to long format for plotting
students_long = students.melt(id_vars='student_id', var_name='subject', value_name='score')
print('\nMelted (long):')
print(students_long)

# Expected output
#    student_id subject  score
# 0           1    math     85
# 1           2    math     90
# 2           3    math     78
# 3           1  science     92
# ...

Exercise 2: Pivot to compare subjects per student

# Pivot long back to wide for a comparison matrix
pivoted = students_long.pivot(index='student_id', columns='subject', values='score')
print('\nPivoted (wide):')
print(pivoted)
# Expected output
# subject    english  math  science
# student_id
# 1               79    85       92
# 2               84    90       88
# 3               92    78       95

Exercise 3: Handle duplicates with pivot_table

If your data has duplicate combinations, pivot raises ValueError: Index contains duplicate entries. Use pivot_table with an aggregation function.

# Add a duplicate student entry
dup = students_long._append({'student_id': 2, 'subject': 'math', 'score': 100}, ignore_index=True)

# pivot will fail, pivot_table aggregates (mean by default)
try:
    dup.pivot(index='student_id', columns='subject', values='score')
except ValueError as e:
    print('Error:', e)

pivot_table_result = dup.pivot_table(index='student_id', columns='subject', values='score', aggfunc='mean')
print('\nPivot table (averaged):')
print(pivot_table_result)

Pro tip: pivot_table is a more robust sibling that allows aggregation, fill_value, and margins — use it when your data has duplicates or missing combinations.

Compare options / when to choose what

When to choose what: - melt is your first choice for making data tidy before plotting or modeling. - pivot is ideal for constructing summary tables, e.g., a matrix of values for a heatmap. - pivot_table is essential when aggregating duplicates — think total sales per product per region. - stack/unstack shine when you're working with pivot-ed multi-index data and need fine control.

Troubleshooting & edge cases

1. ValueError: Index contains duplicate entries

This happens when the combination of index and columns is not unique. Fix: either drop duplicates first, or switch to pivot_table with an aggfunc.

df.drop_duplicates(subset=['id', 'var']).pivot(...)

2. Pivot returns a MultiIndex column

When you set columns to a column with multiple levels or after a groupby, you might get a MultiIndex. Flatten it with df.columns = [f'{i}{j}' for i,j in df.columns] or use .droplevel().

3. Melt has missing values (NaN) for missing combos

If your original wide data has NaN, melted output will include those NaN rows. Decide whether to dropna or fillna based on your analysis.

4. Column type becomes object after pivot

If values column has mixed dtypes, pivot converts to object. Check with df.dtypes and convert to numeric with pd.to_numeric().

5. Setting id_vars in melt is optional but dangerous

If you omit id_vars, all columns are melted, losing your identifiers. Always specify which columns to keep fixed.

Quick checklist: - Before pivot: verify unique combinations, consider pivot_table. - After melt: check column names and dtypes. - Always test with a small sample before running on large data.

What you learned & what's next

You've now mastered the essential skill of reshaping data with pivot and melt in pandas. You can: - Explain the core idea behind reshaping data with pivot and melt — the hinge between wide and long formats. - Apply melt to transform wide datasets into tidy long format for visualization or machine learning. - Apply pivot (and pivot_table for duplicates) to reshape long data into wide matrices for correlation or summary tables. - Troubleshoot common errors like duplicate indexes and missing values.

These operations are foundational for the rest of the data science workflow. Next up in the track, you'll build on this by learning how to combine and join multiple DataFrames — merging separate tables on keys, which is the natural follow-up to reshaping. With pivot and melt under your belt, you'll be ready to tackle complex data pipelines with confidence.