Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Pandas 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()
(144, 3)
year month passengers
0 1949 January 112
1 1949 February 118
2 1949 March 132
3 1949 April 129
4 1949 May 121
totals = df.pivot_table(index="year", values="passengers",
                        aggfunc="sum")
totals.loc[[1949, 1955, 1960]]
passengers
year
1949 1520
1955 3408
1960 5714

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]
(12, 12)
month April August December February January
year
1949 129 148 118 118 112
1950 135 170 140 126 115
1951 163 199 166 150 145

Quick check: look at the column order. What's wrong with it?

months = df["month"].unique()
wide = wide[months]
wide.iloc[:3, :5]
month January February March April May
year
1949 112 118 132 129 121
1950 115 126 141 135 125
1951 145 150 178 163 172
wide.loc[1958:1960, months[5:8]]
month June July August
year
1958 435 491 505
1959 472 548 559
1960 535 622 606
early = df["year"] < 1955
eras = df.assign(era=early.map({True: "1949-54", False: "1955-60"}))
eras["era"].value_counts()
era
1949-54    72
1955-60    72
Name: count, dtype: int64

Common mistake: pivot when each cell has several values

eras.pivot(index="era", columns="month", values="passengers")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[8], line 1
----> 1 eras.pivot(index="era", columns="month", values="passengers")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:10988, in DataFrame.pivot(self, columns, index, values)
  10981 @Substitution("")
  10982 @Appender(_shared_docs["pivot"])
  10983 def pivot(
  10984     self, *, columns, index=lib.no_default, values=lib.no_default
  10985 ) -> DataFrame:
  10986     from pandas.core.reshape.pivot import pivot
> 10988     return pivot(self, index=index, columns=columns, values=values)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\pivot.py:910, in pivot(data, columns, index, values)
    905         indexed = data._constructor_sliced(data[values]._values, index=multiindex)
    906 # error: Argument 1 to "unstack" of "DataFrame" has incompatible type "Union
    907 # [List[Any], ExtensionArray, ndarray[Any, Any], Index, Series]"; expected
    908 # "Hashable"
    909 # unstack with a MultiIndex returns a DataFrame
--> 910 result = cast("DataFrame", indexed.unstack(columns_listlike))  # type: ignore[arg-type]
    911 result.index.names = [
    912     name if name is not lib.no_default else None for name in result.index.names
    913 ]
    915 return result

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:4501, in Series.unstack(self, level, fill_value, sort)
   4451 """
   4452 Unstack, also known as pivot, Series with MultiIndex to produce DataFrame.
   4453 
   (...)
   4497 b    2    4
   4498 """
   4499 from pandas.core.reshape.reshape import unstack
-> 4501 return unstack(self, level, fill_value, sort)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\reshape.py:596, in unstack(obj, level, fill_value, sort)
    594 if is_1d_only_ea_dtype(obj.dtype):
    595     return _unstack_extension_series(obj, level, fill_value, sort=sort)
--> 596 unstacker = _Unstacker(
    597     obj.index, level=level, constructor=obj._constructor_expanddim, sort=sort
    598 )
    599 return unstacker.get_result(obj, value_columns=None, fill_value=fill_value)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\reshape.py:176, in _Unstacker.__init__(self, index, level, constructor, sort)
    168     if num_cells > np.iinfo(np.int32).max:
    169         warnings.warn(
    170             f"The following operation may generate {num_cells} cells "
    171             f"in the resulting pandas object.",
    172             PerformanceWarning,
    173             stacklevel=find_stack_level(),
    174         )
--> 176 self._make_selectors()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\reshape.py:232, in _Unstacker._make_selectors(self)
    229 mask.put(selector, True)
    231 if mask.sum() < len(self.index):
--> 232     raise ValueError("Index contains duplicate entries, cannot reshape")
    234 self.group_index = comp_index
    235 self.mask = mask

ValueError: Index contains duplicate entries, cannot reshape
by_era = eras.pivot_table(index="era", columns="month",
                         values="passengers", aggfunc="mean")
by_era[months[:4]].round(1)
month January February March April
era
1949-54 157.2 159.7 185.8 178.3
1955-60 326.3 310.3 354.5 355.8

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]
month year January February March
0 1949 112 118 132
1 1950 115 126 141
2 1951 145 150 178
long = wide_df.melt(id_vars="year", var_name="month",
                    value_name="passengers")
print(long.shape)
long.head()
(144, 3)
year month passengers
0 1949 January 112
1 1950 January 115
2 1951 January 145
3 1952 January 171
4 1953 January 196
wide_df.melt(id_vars="year").head(3)
year month value
0 1949 January 112
1 1950 January 115
2 1951 January 145

Common mistake: melting while the key is still in the index

wide.melt(id_vars="year")
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[13], line 1
----> 1 wide.melt(id_vars="year")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:11715, in DataFrame.melt(self, id_vars, value_vars, var_name, value_name, col_level, ignore_index)
  11585 def melt(
  11586     self,
  11587     id_vars=None,
   (...)
  11592     ignore_index: bool = True,
  11593 ) -> DataFrame:
  11594     """
  11595     Unpivot DataFrame from wide to long format, optionally leaving identifiers set.
  11596 
   (...)
  11713     2      c          B          E      5
  11714     """
> 11715     return melt(
  11716         self,
  11717         id_vars=id_vars,
  11718         value_vars=value_vars,
  11719         var_name=var_name,
  11720         value_name=value_name,
  11721         col_level=col_level,
  11722         ignore_index=ignore_index,
  11723     ).__finalize__(self, method="melt")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\melt.py:204, in melt(frame, id_vars, value_vars, var_name, value_name, col_level, ignore_index)
    200 if missing.any():
    201     missing_labels = [
    202         lab for lab, not_found in zip(labels, missing, strict=True) if not_found
    203     ]
--> 204     raise KeyError(
    205         "The following id_vars or value_vars are not present in "
    206         f"the DataFrame: {missing_labels}"
    207     )
    208 if value_vars_was_not_none:
    209     frame = frame.iloc[:, algos.unique(idx)]

KeyError: "The following id_vars or value_vars are not present in the DataFrame: ['year']"
fixed = wide.reset_index().melt(id_vars="year")
print(fixed.shape)
(144, 3)
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)
(36, 3)
month
August    351.1
July      351.3
June      311.7
Name: passengers, dtype: float64
again = long.pivot(index="year", columns="month",
                    values="passengers")[months]
print(again.equals(wide))
True

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)
year month passengers
11 1960 December 432
23 1960 January 417
10 1959 December 405

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)
country year lifeExp gdpPercap
288 China 1952 44.00000 400.448611
289 China 1957 50.54896 575.987001
290 China 1962 44.50136 487.674018
tidy = pair.melt(id_vars=["country", "year"],
                 var_name="measure", value_name="value")
print(tidy.shape)
tidy.sort_values(["country", "year"]).head(4)
(48, 4)
country year measure value
0 China 1952 lifeExp 44.000000
24 China 1952 gdpPercap 400.448611
1 China 1957 lifeExp 50.548960
25 China 1957 gdpPercap 575.987001
first_last = tidy[tidy["year"].isin([1952, 2007])]
first_last.groupby(["country", "measure"])["value"].agg(
    ["first", "last"]).round(1)
first last
country measure
China gdpPercap 400.4 4959.1
lifeExp 44.0 73.0
India gdpPercap 546.6 2452.2
lifeExp 37.4 64.7

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))
  country   variable           value
0   India        pop 1,110,396,331.0
1   India    lifeExp            64.7
2   India  gdpPercap         2,452.2

Common mistake: pivoting back without enough keys

tidy.pivot(index="country", columns="measure", values="value")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[22], line 1
----> 1 tidy.pivot(index="country", columns="measure", values="value")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:10988, in DataFrame.pivot(self, columns, index, values)
  10981 @Substitution("")
  10982 @Appender(_shared_docs["pivot"])
  10983 def pivot(
  10984     self, *, columns, index=lib.no_default, values=lib.no_default
  10985 ) -> DataFrame:
  10986     from pandas.core.reshape.pivot import pivot
> 10988     return pivot(self, index=index, columns=columns, values=values)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\pivot.py:910, in pivot(data, columns, index, values)
    905         indexed = data._constructor_sliced(data[values]._values, index=multiindex)
    906 # error: Argument 1 to "unstack" of "DataFrame" has incompatible type "Union
    907 # [List[Any], ExtensionArray, ndarray[Any, Any], Index, Series]"; expected
    908 # "Hashable"
    909 # unstack with a MultiIndex returns a DataFrame
--> 910 result = cast("DataFrame", indexed.unstack(columns_listlike))  # type: ignore[arg-type]
    911 result.index.names = [
    912     name if name is not lib.no_default else None for name in result.index.names
    913 ]
    915 return result

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:4501, in Series.unstack(self, level, fill_value, sort)
   4451 """
   4452 Unstack, also known as pivot, Series with MultiIndex to produce DataFrame.
   4453 
   (...)
   4497 b    2    4
   4498 """
   4499 from pandas.core.reshape.reshape import unstack
-> 4501 return unstack(self, level, fill_value, sort)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\reshape.py:596, in unstack(obj, level, fill_value, sort)
    594 if is_1d_only_ea_dtype(obj.dtype):
    595     return _unstack_extension_series(obj, level, fill_value, sort=sort)
--> 596 unstacker = _Unstacker(
    597     obj.index, level=level, constructor=obj._constructor_expanddim, sort=sort
    598 )
    599 return unstacker.get_result(obj, value_columns=None, fill_value=fill_value)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\reshape.py:176, in _Unstacker.__init__(self, index, level, constructor, sort)
    168     if num_cells > np.iinfo(np.int32).max:
    169         warnings.warn(
    170             f"The following operation may generate {num_cells} cells "
    171             f"in the resulting pandas object.",
    172             PerformanceWarning,
    173             stacklevel=find_stack_level(),
    174         )
--> 176 self._make_selectors()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\reshape.py:232, in _Unstacker._make_selectors(self)
    229 mask.put(selector, True)
    231 if mask.sum() < len(self.index):
--> 232     raise ValueError("Index contains duplicate entries, cannot reshape")
    234 self.group_index = comp_index
    235 self.mask = mask

ValueError: Index contains duplicate entries, cannot reshape
back = tidy.pivot(index=["country", "year"], columns="measure",
                  values="value")
back.head(3).round(1)
measure gdpPercap lifeExp
country year
China 1952 400.4 44.0
1957 576.0 50.5
1962 487.7 44.5
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()
No description has been provided for this image

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)
Series (144,)
['year', 'month']
year  month   
1949  January     112
      February    118
      March       132
      April       129
dtype: int64
print(stacked.loc[(1960, "July")])
print(stacked.loc[1960].idxmax())
622
July
unstacked = stacked.unstack()
print(unstacked.shape)
print(unstacked.equals(wide))
(12, 12)
True

Quick check: unstack moves the inner level by default. How do we move year?

flipped = stacked.unstack(level="year")
flipped.iloc[:3, -3:]
year 1958 1959 1960
month
January 340 360 417
February 318 342 391
March 362 406 419

Common mistake: a level name with the wrong capital letter

stacked.unstack(level="Year")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:1762, in MultiIndex._get_level_number(self, level)
   1761 try:
-> 1762     level = self.names.index(level)
   1763 except ValueError as err:

ValueError: 'Year' is not in list

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[29], line 1
----> 1 stacked.unstack(level="Year")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:4501, in Series.unstack(self, level, fill_value, sort)
   4451 """
   4452 Unstack, also known as pivot, Series with MultiIndex to produce DataFrame.
   4453 
   (...)
   4497 b    2    4
   4498 """
   4499 from pandas.core.reshape.reshape import unstack
-> 4501 return unstack(self, level, fill_value, sort)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\reshape.py:579, in unstack(obj, level, fill_value, sort)
    575         level = level[0]
    577 if not is_integer(level) and not level == "__placeholder__":
    578     # check if level is valid in case of regular index
--> 579     obj.index._get_level_number(level)
    581 if isinstance(obj, DataFrame):
    582     if isinstance(obj.index, MultiIndex):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:1765, in MultiIndex._get_level_number(self, level)
   1763 except ValueError as err:
   1764     if not is_integer(level):
-> 1765         raise KeyError(f"Level {level} not found") from err
   1766     if level < 0:
   1767         level += self.nlevels

KeyError: 'Level Year not found'
print(stacked.index.names)
stacked.unstack(level="year").shape
['year', 'month']
(12, 12)

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)
278 432 154

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)
(60,)
continent  year
Africa     1952    39.1
           1957    41.3
           1962    43.3
Name: lifeExp, dtype: float64
grid = life.unstack()
grid[[1952, 1982, 2007]].round(1)
year 1952 1982 2007
continent
Africa 39.1 51.6 54.8
Americas 53.3 66.2 73.6
Asia 46.3 62.6 70.7
Europe 64.4 72.8 77.6
Oceania 69.3 74.3 80.7
gain = grid[2007] - grid[1952]
gain.sort_values(ascending=False).round(1)
continent
Asia        24.4
Americas    20.3
Africa      15.7
Europe      13.2
Oceania     11.5
dtype: float64

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)
continent Africa Americas Asia Europe Oceania
year
1952 39.1 53.3 46.3 64.4 69.3
2007 54.8 73.6 70.7 77.6 80.7
long_again = grid.stack()
print(long_again.shape)
long_again.reset_index(name="lifeExp").head(3).round(1)
(60,)
continent year lifeExp
0 Africa 1952 39.1
1 Africa 1957 41.3
2 Africa 1962 43.3

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)
month June July August
year
1949 0.089 0.097 0.097
1960 0.094 0.109 0.106
avg_share = shares.mean().sort_values(ascending=False)
avg_share.round(3).iloc[[0, 1, -2, -1]]
month
August      0.103
July        0.103
February    0.071
November    0.069
dtype: float64
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()
No description has been provided for this image
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()
No description has been provided for this image
peak = wide.max(axis=1)
low = wide.min(axis=1)
swing = (peak / low).round(2)
swing.loc[[1949, 1955, 1960]]
year
1949    1.42
1955    1.56
1960    1.59
dtype: float64

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#

  1. Melt the by_era table back to long, with era, month and passengers.
  2. Melt gapminder's pop, lifeExp and gdpPercap for Brazil into one column.
  3. 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.