Reshape Data with Pivot and Melt

Your data is messy. You've got a DataFrame that looks like a spreadsheet from 2004 — dates in rows, products in columns, and values scattered like confetti. Every time you try to plot it, run a statistical test, or feed it into a machine learning model, you hit a wall. The culprit? Your data is in the wrong shape. This lesson is your escape hatch.

Reshaping data with pivot and melt is the single most powerful skill in the pandas toolkit. pivot flips your data from long to wide, and melt does the opposite — turning wide data into tidy, analysis-ready rows. By the end of this lesson, you'll be able to reshape any dataset on command, and you'll understand exactly when to use each tool.

The problem this lesson solves

Real-world data rarely arrives in the shape you need. APIs return nested JSON, spreadsheets have one column per category, and survey exports put every answer in a separate row. Trying to analyze data in the wrong format is like trying to bake a cake with the ingredients still in the pantry — technically possible, but messy and error-prone.

Consider this: you have sales data where each row is a product and each column is a month. To plot a trend line, you need every month in a single column. Or you have a long format — one row per customer per purchase — but you need a pivot table with customers as rows and products as columns for a recommendation engine.

Without pivot and melt, you'd resort to manual loops, groupby gymnastics, or painful copy-paste in Excel. With them, you reshape your data in one clean line of code.

Why it matters now: As you progress in your data science journey, you'll spend 80% of your time cleaning and reshaping data. Master these two functions, and you'll cut your analysis time in half.

Core concept / mental model

Think of your data as a piece of clay. pivot and melt are your two most important sculpting tools.

• pivot — takes long data (many rows, few columns) and makes it wide (fewer rows, more columns).
• melt — takes wide data (many columns, few rows) and makes it long (more rows, fewer columns).

A simple visual:

Long format (tidy)          Wide format (summary)
─────────────────────        ─────────────────────
| Year | Product | Sales |   | Year | Apples | Oranges |
| 2023 | Apples  | 100   |   | 2023 | 100    | 150     |
| 2023 | Oranges | 150   |   | 2024 | 120    | 180     |
| 2024 | Apples  | 120   |   ─────────────────────
| 2024 | Oranges | 180   |
─────────────────────

The tidy data principle: each variable is a column, each observation is a row. Most analysis tools love tidy data. melt gets you there; pivot is for when you need a summary view.

Think of melt as "unpivot" — it takes columns and turns them into rows, with two new columns: one for the old column names (variable) and one for their values (value). pivot is the reverse — it takes values from one column and spreads them out as new columns, using another column as the index.

How it works step by step

Let's break down both functions logically.

melt — from wide to long

1. You have a DataFrame with many columns that represent different categories or time points.
2. You decide which columns are identifier variables — the ones you want to keep as-is (e.g., Year).
3. All other columns become value variables — they get collapsed into a single column of values.
4. melt creates two new columns, variable and value, and repeats the identifier rows accordingly.

Key parameters: id_vars, value_vars, var_name, value_name.

pivot — from long to wide

1. You have a long DataFrame with one column that contains category names (e.g., Product) and another with values (e.g., Sales).
2. You choose which column becomes the index (e.g., Year).
3. You choose which column's values become new column names (e.g., Product).
4. You specify which column holds the values to fill those new columns.
5. pivot returns a new DataFrame with a row per index value, and one column per unique value in the pivot column.

Key parameters: index, columns, values.

Pro tip: pivot is a pure reshape — it does not aggregate data. If you have duplicate index-column pairs, you'll get an error. Use pivot_table if you need aggregation.

Hands-on walkthrough

Let's start with a sample dataset. We'll use a sales table in long format.

import pandas as pd

df_long = pd.DataFrame({
    'Year': [2023, 2023, 2024, 2024],
    'Product': ['Apples', 'Oranges', 'Apples', 'Oranges'],
    'Sales': [100, 150, 120, 180]
})

print(df_long)

Output:

   Year  Product  Sales
0  2023   Apples    100
1  2023   Oranges  150
2  2024   Apples    120
3  2024   Oranges  180

Now let's pivot it to get a summary with years as rows and products as columns.

pivoted = df_long.pivot(index='Year', columns='Product', values='Sales')
print(pivoted)

Output:

Product  Apples  Oranges
Year                    
2023        100      150
2024        120      180

Now, let's melt it back to long format. Suppose you receive a wide CSV file:

# Simulate a wide dataset
df_wide = pd.DataFrame({
    'Year': [2023, 2024],
    'Apples': [100, 120],
    'Oranges': [150, 180]
})

melted = df_wide.melt(id_vars=['Year'], var_name='Product', value_name='Sales')
print(melted)

Output:

   Year Product  Sales
0  2023  Apples    100
1  2024  Apples    120
2  2023  Oranges   150
3  2024  Oranges   180

Notice how melt returns the same long format we started with — the two operations are inverses.

Real-world example: multiple value columns

What if you have both sales and profit? Use pivot_table to aggregate, or melt with value_vars to choose specific columns.

# Higher-dimensional pivot
df_multi = pd.DataFrame({
    'Year': [2023, 2023, 2024, 2024],
    'Product': ['Apples', 'Oranges', 'Apples', 'Oranges'],
    'Sales': [100, 150, 120, 180],
    'Profit': [20, 30, 25, 40]
})

pivot_multi = df_multi.pivot(index='Year', columns='Product', values='Sales')
print(pivot_multi)

Output:

Product  Apples  Oranges
Year                    
2023        100      150
2024        120      180

To keep both sales and profit, you can use pivot_table with aggfunc='mean' or set the index to a MultiIndex.

Compare options / when to choose what

Before you pick pivot or melt, ask yourself: What shape does my analysis need?

Alternatives: - pivot_table — adds aggregation (aggfunc) and handles duplicates. - stack/unstack — reshape the index levels for MultiIndex DataFrames. - groupby + agg — combine with pivot for complex summaries.

Troubleshooting & edge cases

Error: "Index contains duplicate entries"

This happens when your index and columns combination isn't unique.

# Duplicate rows cause this error
df = pd.DataFrame({
    'Year': [2023, 2023],
    'Product': ['Apples', 'Apples'],
    'Sales': [100, 120]
})
# df.pivot(index='Year', columns='Product', values='Sales')  # raises ValueError

Fix: Use pivot_table with aggfunc='sum' (or mean/min) to collapse duplicates.

Melt output columns are named variable and value

If you don't like the defaults, specify var_name and value_name.

Pivot resets index to a column

If you want Year back as a regular column, call reset_index().

pivoted.reset_index(inplace=True)

Missing values after pivot

If some combinations have no data, you'll get NaN. Use fillna(0) for a complete table.

Edge case: pivot_table fills missing values with NaN by default; you can pass fill_value=0 to avoid that.

What you learned & what's next

You've just mastered the two most powerful data reshaping tools in pandas. You can now:

• Explain why and when to use pivot vs melt
• Reshape a long dataset to a wide summary using pivot
• Melt a wide dataset into a tidy long format
• Troubleshoot duplicate-index errors and missing values

What's next? In the next lesson, you'll combine datasets with merge and concat — the natural follow-up when you need to bring multiple DataFrames together after reshaping them.

Review this lesson by going back to the mental model anytime, and remember: every dataset has an ideal shape — you now know how to find it.