Reshaping with Pivot and Melt

Introduction

The same facts can be laid out in different shapes. Some questions are easy in one shape and awkward in another. Knowing how to convert between them is a core data skill.

Long and wide

Long data has one row per observation, with columns saying what each is:

Wide data spreads values across columns:

Wide tables are easy for people to read, such as reports and spreadsheets. Long tables are usually best for grouping, filtering and plotting with code, because every variable has its own column.

pivot_table: long to wide, with summaries

import pandas as pd

sales = pd.DataFrame({
    "month": ["Jan", "Jan", "Feb", "Feb"],
    "region": ["N", "S", "N", "S"],
    "revenue": [100, 80, 120, 90],
})

print(sales.pivot_table(index="month", columns="region",
                        values="revenue", aggfunc="sum"))

region    N   S
month          
Feb     120  90
Jan     100  80

Notice that the months came out in alphabetical order, with Feb before Jan. pandas sorted the text labels. For real analyses, store months as real dates or ordered categories so the order is correct (the next lesson shows how).

pivot_table handles duplicates by aggregating them. If two rows had the same month and region, aggfunc="sum" would add them. Other choices include "mean", "count" and "max". Add margins=True to include totals, and fill_value=0 to replace gaps.

melt: wide to long

wide = pd.DataFrame({
    "product": ["A", "B"],
    "Jan": [10, 20],
    "Feb": [15, 25],
})

print(wide.melt(id_vars="product", var_name="month", value_name="units"))

  product month  units
0       A   Jan     10
1       B   Jan     20
2       A   Feb     15
3       B   Feb     25

• id_vars are the columns to keep as they are.
• The other columns are "melted" into two: one holding the old column names (var_name) and one holding the values (value_name).

Once in long form, you can group by month or product easily, or pass it to plotting tools that expect one row per data point.

Related tools

• df.pivot(...) reshapes without aggregating, and fails if there are duplicates.
• df.stack() and df.unstack() move index levels between rows and columns.
• pd.crosstab(a, b) counts how often combinations of two categories occur.
• groupby(...).agg(...) is often simpler than a pivot when you just want summary numbers.

Choosing the shape

1. Decide what you want to do with the table.
2. For analysis and plotting by code, aim for long data.
3. For a final report or spreadsheet for people, make a wide pivot at the end.
4. Keep your raw data untouched, and do the reshaping in code so it is repeatable.

Try it yourself

Take a table of exam scores with one column per subject. Melt it to long form, then pivot it back to wide with the average score per class and subject.

Summary

• Long data has one row per observation, and wide data spreads values across columns.
• pivot_table turns long into wide and aggregates duplicates, and melt does the reverse.
• Text labels sort alphabetically, so use real dates or ordered categories for correct ordering.