Lesson 30 · Pandas Full Course
Reshape Data in Pandas: melt, pivot, stack and unstack (Long vs Wide) | Pandas Course #30
In this lesson you'll be able to: Tell long data from wide data, and pick the right one Spread long data into a wide table with pivot Turn wide tables back…
- CoursePandas Full Course
- Lesson30 of 65
- FormatJupyter notebook · 40 code cells
- Data2 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- flights.csv preview2.3 KB
- gapminder.csv preview80.2 KB
📓 Full notebook
Download .ipynbPandas 30: Reshaping Data using melt, stack, and unstack#
In this lesson you'll be able to:
- Tell long data from wide data, and pick the right one
- Spread long data into a wide table with pivot
- Turn wide tables back into long ones with melt
- Move labels between rows and columns with stack and unstack
- Use reshaping to answer a real question about seasons Datasets: airline passengers 1949-1960 (flights.csv) and gapminder.csv
Warm-up: a pivot table recap#
Lesson 29 built pivot tables. Here's one on the flights data.
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_csv("data/pandas/flights.csv")
print(df.shape)
df.head()
totals = df.pivot_table(index="year", values="passengers",
aggfunc="sum")
totals.loc[[1949, 1955, 1960]]
Long versus wide data#
Problem: the same numbers can be one long list or a grid.
- Long: one row per measurement; great for groupby and charts
- Wide: one row per item, one column per category; easy to read
wide = df.pivot(index="year", columns="month",
values="passengers")
print(wide.shape)
wide.iloc[:3, :5]
Quick check: look at the column order. What's wrong with it?
months = df["month"].unique()
wide = wide[months]
wide.iloc[:3, :5]
wide.loc[1958:1960, months[5:8]]
early = df["year"] < 1955
eras = df.assign(era=early.map({True: "1949-54", False: "1955-60"}))
eras["era"].value_counts()
Common mistake: pivot when each cell has several values
eras.pivot(index="era", columns="month", values="passengers")
by_era = eras.pivot_table(index="era", columns="month",
values="passengers", aggfunc="mean")
by_era[months[:4]].round(1)
Fold a wide table back with melt#
Problem: wide data arrives, but your tools want it long.
wide_df = wide.reset_index()
wide_df.iloc[:3, :4]
long = wide_df.melt(id_vars="year", var_name="month",
value_name="passengers")
print(long.shape)
long.head()
wide_df.melt(id_vars="year").head(3)
Common mistake: melting while the key is still in the index
wide.melt(id_vars="year")
fixed = wide.reset_index().melt(id_vars="year")
print(fixed.shape)
summer = ["June", "July", "August"]
hot = wide_df.melt(id_vars="year", value_vars=summer,
var_name="month", value_name="passengers")
print(hot.shape)
hot.groupby("month")["passengers"].mean().round(1)
again = long.pivot(index="year", columns="month",
values="passengers")[months]
print(again.equals(wide))
Your turn#
Melt only the winter months (December, January, February). Then find the single busiest winter month on record.
winter = ["December", "January", "February"]
cold = wide_df.melt(id_vars="year", value_vars=winter,
value_name="passengers")
cold.sort_values("passengers", ascending=False).head(3)
Melt several measures at once#
Problem: compare different measures side by side in one chart.
gap = pd.read_csv("data/pandas/gapminder.csv")
pair = gap[gap["country"].isin(["China", "India"])]
cols = ["country", "year", "lifeExp", "gdpPercap"]
pair = pair[cols]
pair.head(3)
tidy = pair.melt(id_vars=["country", "year"],
var_name="measure", value_name="value")
print(tidy.shape)
tidy.sort_values(["country", "year"]).head(4)
first_last = tidy[tidy["year"].isin([1952, 2007])]
first_last.groupby(["country", "measure"])["value"].agg(
["first", "last"]).round(1)
Your turn#
Melt pop, lifeExp and gdpPercap for India in 2007 into one column.
india = gap[(gap["country"] == "India") & (gap["year"] == 2007)]
measures = ["pop", "lifeExp", "gdpPercap"]
flat = india.melt(id_vars="country", value_vars=measures)
print(flat.to_string(float_format="{:,.1f}".format))
Common mistake: pivoting back without enough keys
tidy.pivot(index="country", columns="measure", values="value")
back = tidy.pivot(index=["country", "year"], columns="measure",
values="value")
back.head(3).round(1)
import seaborn as sns
grid = sns.relplot(data=tidy, x="year", y="value", hue="country",
col="measure", kind="line", height=3.5,
facet_kws={"sharey": False})
grid.set_axis_labels("Year", "Value")
grid.figure.suptitle("China and India, 1952-2007", y=1.03)
plt.show()
Move labels with stack and unstack#
Problem: move a label between the columns and the row index.
- stack: column labels move down into the row index
- unstack: an index level moves up into the columns
stacked = wide.stack()
print(type(stacked).__name__, stacked.shape)
print(stacked.index.names)
stacked.head(4)
print(stacked.loc[(1960, "July")])
print(stacked.loc[1960].idxmax())
unstacked = stacked.unstack()
print(unstacked.shape)
print(unstacked.equals(wide))
Quick check: unstack moves the inner level by default. How do we move year?
flipped = stacked.unstack(level="year")
flipped.iloc[:3, -3:]
Common mistake: a level name with the wrong capital letter
stacked.unstack(level="Year")
print(stacked.index.names)
stacked.unstack(level="year").shape
Your turn#
Using stacked, find the passengers for December 1955, then the change from December 1955 to December 1960.
dec55 = stacked.loc[(1955, "December")]
dec60 = stacked.loc[(1960, "December")]
print(dec55, dec60, dec60 - dec55)
Unstack a groupby result#
Problem: a two-key groupby gives a long list that's hard to scan.
life = gap.groupby(["continent", "year"])["lifeExp"].mean()
print(life.shape)
life.head(3).round(1)
grid = life.unstack()
grid[[1952, 1982, 2007]].round(1)
gain = grid[2007] - grid[1952]
gain.sort_values(ascending=False).round(1)
Quick check: how would you put years down the rows and continents across?
by_year = life.unstack(level="continent")
by_year.loc[[1952, 2007]].round(1)
long_again = grid.stack()
print(long_again.shape)
long_again.reset_index(name="lifeExp").head(3).round(1)
Mini-project: when do people fly?#
Question: which months are busiest, and did the pattern change?
shares = wide.div(wide.sum(axis=1), axis=0)
shares.loc[[1949, 1960], months[5:8]].round(3)
avg_share = shares.mean().sort_values(ascending=False)
avg_share.round(3).iloc[[0, 1, -2, -1]]
ax = shares.mean()[months].plot(kind="bar", figsize=(8, 4))
ax.set_title("Average share of yearly passengers by month")
ax.set_xlabel("Month")
ax.set_ylabel("Share of the year")
plt.show()
profile = wide.loc[[1949, 1955, 1960]].T
ax = profile.plot(marker="o", figsize=(8, 4))
ax.set_title("Monthly airline passengers, three years")
ax.set_xlabel("Month")
ax.set_ylabel("Passengers (thousands)")
plt.show()
peak = wide.max(axis=1)
low = wide.min(axis=1)
swing = (peak / low).round(2)
swing.loc[[1949, 1955, 1960]]
Recap#
- Long data: one row per value. Wide data: a readable grid
- pivot spreads long to wide; its keys must be unique
- melt folds wide to long; reset_index first if the key is in the index
- stack moves columns into the index; unstack moves a level back up
- Name the level in unstack to choose which one moves
Challenges#
- Melt the by_era table back to long, with era, month and passengers.
- Melt gapminder's pop, lifeExp and gdpPercap for Brazil into one column.
- Group gapminder by continent and year, then unstack continent into columns.
What's next#
Lesson 31: Working with MultiIndex and Hierarchical Columns Select, slice and tidy the multi-level labels you met today.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



