Mathew K Analytics

Lesson 31 · Pandas Full Course

Pandas MultiIndex Explained: Hierarchical Rows and Columns Made Simple | Pandas Course #31

In this lesson you'll be able to: Build a MultiIndex and select rows with loc and xs Slice ranges safely with sortindex and IndexSlice Read groupby results…

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 31: Working with MultiIndex and Hierarchical Columns#

In this lesson you'll be able to:

  • Build a MultiIndex and select rows with loc and xs
  • Slice ranges safely with sort_index and IndexSlice
  • Read groupby results that have several index levels
  • Select from, and flatten, two-level column headers
  • Answer a real question by grouping on index levels Dataset: gapminder, 142 countries from 1952 to 2007 (gapminder.csv)

Warm-up: unstack from lesson 30#

Lesson 30 reshaped data with stack and unstack. One more time:

import pandas as pd
import matplotlib.pyplot as plt
gap = pd.read_csv("data/pandas/gapminder.csv")
print(gap.shape)
gap.head(3)
(1704, 6)
country year pop continent lifeExp gdpPercap
0 Afghanistan 1952 8425333.0 Asia 28.801 779.445314
1 Afghanistan 1957 9240934.0 Asia 30.332 820.853030
2 Afghanistan 1962 10267083.0 Asia 31.997 853.100710
life = gap.groupby(["continent", "year"])["lifeExp"].mean()
life.unstack().iloc[:, [0, -1]].round(1)
year 1952 2007
continent
Africa 39.1 54.8
Americas 53.3 73.6
Asia 46.3 70.7
Europe 64.4 77.6
Oceania 69.3 80.7
print(type(life.index).__name__)
print(life.index.names)
MultiIndex
['continent', 'year']

Build a MultiIndex and select from it#

Problem: each row is identified by two labels, not one.

g = gap.set_index(["country", "year"])
print(g.index.nlevels, g.index.names)
g.head(3)
2 ['country', 'year']
pop continent lifeExp gdpPercap
country year
Afghanistan 1952 8425333.0 Asia 28.801 779.445314
1957 9240934.0 Asia 30.332 820.853030
1962 10267083.0 Asia 31.997 853.100710
years = g.index.get_level_values("year")
print(years.min(), years.max())
print(g.index.get_level_values("country").nunique())
1952 2007
142
g.index[:3]
MultiIndex([('Afghanistan', 1952),
            ('Afghanistan', 1957),
            ('Afghanistan', 1962)],
           names=['country', 'year'])
g.loc["Japan"].head(3)
pop continent lifeExp gdpPercap
year
1952 86459025.0 Asia 63.03 3216.956347
1957 91563009.0 Asia 65.50 4317.694365
1962 95831757.0 Asia 68.73 6576.649461
row = g.loc[("Japan", 2007)]
print(row)
print(g.loc[("Japan", 2007), "lifeExp"])
pop          127467972.0
continent           Asia
lifeExp           82.603
gdpPercap    31656.06806
Name: (Japan, 2007), dtype: object
82.603

Quick check: what does g.loc["Japan", "lifeExp"] return?

g.loc["Japan", "lifeExp"].head(3)
year
1952    63.03
1957    65.50
1962    68.73
Name: lifeExp, dtype: float64
pairs = pd.MultiIndex.from_product(
    [["Japan", "Kenya"], [1952, 2007]], names=["country", "year"])
g.loc[pairs, "lifeExp"]
country  year
Japan    1952    63.030
         2007    82.603
Kenya    1952    42.270
         2007    54.110
Name: lifeExp, dtype: float64

Common mistake: selecting an inner level with plain loc

g.loc[2007]
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3641, in Index.get_loc(self, key)
   3640 try:
-> 3641     return self._engine.get_loc(casted_key)
   3642 except KeyError as err:

File pandas/_libs/index.pyx:168, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/index.pyx:176, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/index.pyx:583, in pandas._libs.index.StringObjectEngine._check_type()

KeyError: 2007

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

KeyError                                  Traceback (most recent call last)
Cell In[12], line 1
----> 1 g.loc[2007]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1207, in _LocationIndexer.__getitem__(self, key)
   1205 maybe_callable = com.apply_if_callable(key, self.obj)
   1206 maybe_callable = self._raise_callable_usage(key, maybe_callable)
-> 1207 return self._getitem_axis(maybe_callable, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1449, in _LocIndexer._getitem_axis(self, key, axis)
   1447 # fall thru to straight lookup
   1448 self._validate_key(key, axis)
-> 1449 return self._get_label(key, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1399, in _LocIndexer._get_label(self, label, axis)
   1397 def _get_label(self, label, axis: AxisInt):
   1398     # GH#5567 this will fail if the label is not present in the axis.
-> 1399     return self.obj.xs(label, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:4233, in NDFrame.xs(self, key, axis, level, drop_level)
   4230     index = self.index
   4232 if isinstance(index, MultiIndex):
-> 4233     loc, new_index = index._get_loc_level(key, level=0)
   4234     if not drop_level:
   4235         if lib.is_integer(loc):
   4236             # Slice index must be an integer or None

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:3784, in MultiIndex._get_loc_level(self, key, level)
   3782         return indexer, maybe_mi_droplevels(indexer, ilevels)
   3783 else:
-> 3784     indexer = self._get_level_indexer(key, level=level)
   3785     if (
   3786         isinstance(key, str)
   3787         and self.levels[level]._supports_partial_string_indexing
   3788     ):
   3789         # check to see if we did an exact lookup vs sliced
   3790         check = self.levels[level].get_loc(key)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:3885, in MultiIndex._get_level_indexer(self, key, level, indexer)
   3882         return slice(i, j, step)
   3884 else:
-> 3885     idx = self._get_loc_single_level_index(level_index, key)
   3887     if level > 0 or self._lexsort_depth == 0:
   3888         # Desired level is not sorted
   3889         if isinstance(idx, slice):
   3890             # test_get_loc_partial_timestamp_multiindex

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:3458, in MultiIndex._get_loc_single_level_index(self, level_index, key)
   3456     return -1
   3457 else:
-> 3458     return level_index.get_loc(key)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3648, in Index.get_loc(self, key)
   3643     if isinstance(casted_key, slice) or (
   3644         isinstance(casted_key, abc.Iterable)
   3645         and any(isinstance(x, slice) for x in casted_key)
   3646     ):
   3647         raise InvalidIndexError(key) from err
-> 3648     raise KeyError(key) from err
   3649 except TypeError:
   3650     # If we have a listlike key, _check_indexing_error will raise
   3651     #  InvalidIndexError. Otherwise we fall through and re-raise
   3652     #  the TypeError.
   3653     self._check_indexing_error(key)

KeyError: 2007
y2007 = g.xs(2007, level="year")
print(y2007.shape)
y2007.sort_values("lifeExp").head(3)
(142, 4)
pop continent lifeExp gdpPercap
country
Swaziland 1133066.0 Africa 39.613 4513.480643
Mozambique 19951656.0 Africa 42.082 823.685621
Zambia 11746035.0 Africa 42.384 1271.211593

Slice ranges with sort_index and IndexSlice#

Problem: pick several countries and a span of years in one go.

print(g.index.is_monotonic_increasing)
g.loc["Canada":"Chile", "lifeExp"].iloc[[0, -1]]
True
country  year
Canada   1952    68.750
Chile    2007    78.553
Name: lifeExp, dtype: float64

Common mistake: slicing an unsorted MultiIndex

mixed = g.sample(frac=1, random_state=42)
mixed.loc["Canada":"Chile"]
---------------------------------------------------------------------------
UnsortedIndexError                        Traceback (most recent call last)
Cell In[15], line 2
      1 mixed = g.sample(frac=1, random_state=42)
----> 2 mixed.loc["Canada":"Chile"]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1207, in _LocationIndexer.__getitem__(self, key)
   1205 maybe_callable = com.apply_if_callable(key, self.obj)
   1206 maybe_callable = self._raise_callable_usage(key, maybe_callable)
-> 1207 return self._getitem_axis(maybe_callable, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1429, in _LocIndexer._getitem_axis(self, key, axis)
   1427 if isinstance(key, slice):
   1428     self._validate_key(key, axis)
-> 1429     return self._get_slice_axis(key, axis=axis)
   1430 elif com.is_bool_indexer(key):
   1431     return self._getbool_axis(key, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1461, in _LocIndexer._get_slice_axis(self, slice_obj, axis)
   1458     return obj.copy(deep=False)
   1460 labels = obj._get_axis(axis)
-> 1461 indexer = labels.slice_indexer(slice_obj.start, slice_obj.stop, slice_obj.step)
   1463 if isinstance(indexer, slice):
   1464     return self.obj._slice(indexer, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:6804, in Index.slice_indexer(self, start, end, step)
   6753 def slice_indexer(
   6754     self,
   6755     start: Hashable | None = None,
   6756     end: Hashable | None = None,
   6757     step: int | None = None,
   6758 ) -> slice:
   6759     """
   6760     Compute the slice indexer for input labels and step.
   6761 
   (...)
   6802     slice(1, 3, None)
   6803     """
-> 6804     start_slice, end_slice = self.slice_locs(start, end, step=step)
   6806     # return a slice
   6807     if not is_scalar(start_slice):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:3382, in MultiIndex.slice_locs(self, start, end, step)
   3329 """
   3330 For an ordered MultiIndex, compute the slice locations for input
   3331 labels.
   (...)
   3378                       sequence of such.
   3379 """
   3380 # This function adds nothing to its parent implementation (the magic
   3381 # happens in get_slice_bound method), but it adds meaningful doc.
-> 3382 return super().slice_locs(start, end, step)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:7062, in Index.slice_locs(self, start, end, step)
   7060 start_slice = None
   7061 if start is not None:
-> 7062     start_slice = self.get_slice_bound(start, "left")
   7063 if start_slice is None:
   7064     start_slice = 0

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:3324, in MultiIndex.get_slice_bound(self, label, side)
   3322 if not isinstance(label, tuple):
   3323     label = (label,)
-> 3324 result = self._partial_tup_index(label, side=side)
   3325 result = maybe_unbox_numpy_scalar(result)
   3326 return result

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:3386, in MultiIndex._partial_tup_index(self, tup, side)
   3384 def _partial_tup_index(self, tup: tuple, side: Literal["left", "right"] = "left"):
   3385     if len(tup) > self._lexsort_depth:
-> 3386         raise UnsortedIndexError(
   3387             f"Key length ({len(tup)}) was greater than MultiIndex lexsort depth "
   3388             f"({self._lexsort_depth})"
   3389         )
   3391     n = len(tup)
   3392     start, end = 0, len(self)

UnsortedIndexError: 'Key length (1) was greater than MultiIndex lexsort depth (0)'
mixed = mixed.sort_index()
mixed.loc["Canada":"Chile"].shape
(48, 4)
idx = pd.IndexSlice
pick = idx[["China", "India"], 1997:2007]
g.loc[pick, ["lifeExp", "gdpPercap"]].round(1)
lifeExp gdpPercap
country year
China 1997 70.4 2289.2
2002 72.0 3119.3
2007 73.0 4959.1
India 1997 61.8 1458.8
2002 62.9 1746.8
2007 64.7 2452.2
in_2007 = g.loc[idx[:, 2007], "lifeExp"]
in_2007.nlargest(3)
country           year
Japan             2007    82.603
Hong Kong, China  2007    82.208
Iceland           2007    81.757
Name: lifeExp, dtype: float64

Your turn#

Show life expectancy for France, Germany and the United Kingdom, in 1952 and 2007 only, with years as columns.

trio = ["France", "Germany", "United Kingdom"]
sel = g.loc[idx[trio, [1952, 2007]], "lifeExp"]
sel.unstack()
year 1952 2007
country
France 67.41 80.657
Germany 67.50 79.406
United Kingdom 69.18 79.425
by_year = g.swaplevel().sort_index()
by_year.loc[2007].head(3)
pop continent lifeExp gdpPercap
country
Afghanistan 31889923.0 Asia 43.828 974.580338
Albania 3600523.0 Europe 76.423 5937.029526
Algeria 33333216.0 Africa 72.301 6223.367465

Read and summarise grouped results#

Problem: groupby on two keys returns a MultiIndex. Use it.

cols = ["lifeExp", "gdpPercap"]
cont = gap.groupby(["continent", "year"])[cols].mean()
cont.round(1).head(3)
lifeExp gdpPercap
continent year
Africa 1952 39.1 1252.6
1957 41.3 1385.2
1962 43.3 1598.1
cont.loc["Europe"].tail(3).round(1)
lifeExp gdpPercap
year
1997 75.5 19076.8
2002 76.7 21711.7
2007 77.6 25054.5
cont.xs(2007, level="year").round(1)
lifeExp gdpPercap
continent
Africa 54.8 3089.0
Americas 73.6 11003.0
Asia 70.7 12473.0
Europe 77.6 25054.5
Oceania 80.7 29810.2
world = g.groupby(level="year")["pop"].sum() / 1e9
world.iloc[[0, 5, -1]].round(2)
year
1952    2.41
1977    3.93
2007    6.25
Name: pop, dtype: float64
per_country = g.groupby(level="country")["lifeExp"].max()
per_country.nlargest(3)
country
Japan               82.603
Hong Kong, China    82.208
Iceland             81.757
Name: lifeExp, dtype: float64
year_avg = g.groupby(level="year")["lifeExp"].transform("mean")
above = (g["lifeExp"] - year_avg).round(1)
above.loc[["Japan", "Kenya"], 2007]
country  year
Japan    2007    15.6
Kenya    2007   -12.9
Name: lifeExp, dtype: float64

Your turn#

Using y2007, name the richest country in each continent.

rich = y2007.groupby("continent")["gdpPercap"].idxmax()
rich
continent
Africa              Gabon
Americas    United States
Asia               Kuwait
Europe             Norway
Oceania         Australia
Name: gdpPercap, dtype: str

Quick check: how many continents and years does cont cover?

print(cont.index.get_level_values("continent").nunique())
print(cont.index.levels[1].tolist())
5
[1952, 1957, 1962, 1967, 1972, 1977, 1982, 1987, 1992, 1997, 2002, 2007]

Hierarchical columns#

Problem: several statistics for several columns give two-level headers.

recent = gap[gap["year"] == 2007]
stats = recent.groupby("continent")[cols].agg(["mean", "max"])
stats.round(1)
lifeExp gdpPercap
mean max mean max
continent
Africa 54.8 76.4 3089.0 13206.5
Americas 73.6 80.7 11003.0 42951.7
Asia 70.7 82.6 12473.0 47307.0
Europe 77.6 81.8 25054.5 49357.2
Oceania 80.7 81.2 29810.2 34435.4
print(stats.columns.nlevels)
print(stats.columns.tolist()[:2])
2
[('lifeExp', 'mean'), ('lifeExp', 'max')]
stats["lifeExp"].round(1)
mean max
continent
Africa 54.8 76.4
Americas 73.6 80.7
Asia 70.7 82.6
Europe 77.6 81.8
Oceania 80.7 81.2
stats[("gdpPercap", "max")].round(0)
continent
Africa      13206.0
Americas    42952.0
Asia        47307.0
Europe      49357.0
Oceania     34435.0
Name: (gdpPercap, max), dtype: float64

Common mistake: asking for a lower column level directly

stats["mean"]
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3641, in Index.get_loc(self, key)
   3640 try:
-> 3641     return self._engine.get_loc(casted_key)
   3642 except KeyError as err:

File pandas/_libs/index.pyx:168, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/index.pyx:197, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:7668, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:7676, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'mean'

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

KeyError                                  Traceback (most recent call last)
Cell In[33], line 1
----> 1 stats["mean"]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:4377, in DataFrame.__getitem__(self, key)
   4375 if is_single_key:
   4376     if self.columns.nlevels > 1:
-> 4377         return self._getitem_multilevel(key)
   4378     indexer = self.columns.get_loc(key)
   4379     if is_integer(indexer):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:4435, in DataFrame._getitem_multilevel(self, key)
   4433 def _getitem_multilevel(self, key):
   4434     # self.columns is a MultiIndex
-> 4435     loc = self.columns.get_loc(key)
   4436     if isinstance(loc, (slice, np.ndarray)):
   4437         new_columns = self.columns[loc]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:3523, in MultiIndex.get_loc(self, key)
   3520     return mask
   3522 if not isinstance(key, tuple):
-> 3523     loc = self._get_level_indexer(key, level=0)
   3524     return _maybe_to_slice(loc)
   3526 keylen = len(key)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:3885, in MultiIndex._get_level_indexer(self, key, level, indexer)
   3882         return slice(i, j, step)
   3884 else:
-> 3885     idx = self._get_loc_single_level_index(level_index, key)
   3887     if level > 0 or self._lexsort_depth == 0:
   3888         # Desired level is not sorted
   3889         if isinstance(idx, slice):
   3890             # test_get_loc_partial_timestamp_multiindex

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\multi.py:3458, in MultiIndex._get_loc_single_level_index(self, level_index, key)
   3456     return -1
   3457 else:
-> 3458     return level_index.get_loc(key)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3648, in Index.get_loc(self, key)
   3643     if isinstance(casted_key, slice) or (
   3644         isinstance(casted_key, abc.Iterable)
   3645         and any(isinstance(x, slice) for x in casted_key)
   3646     ):
   3647         raise InvalidIndexError(key) from err
-> 3648     raise KeyError(key) from err
   3649 except TypeError:
   3650     # If we have a listlike key, _check_indexing_error will raise
   3651     #  InvalidIndexError. Otherwise we fall through and re-raise
   3652     #  the TypeError.
   3653     self._check_indexing_error(key)

KeyError: 'mean'
stats.xs("mean", axis=1, level=1).round(1)
lifeExp gdpPercap
continent
Africa 54.8 3089.0
Americas 73.6 11003.0
Asia 70.7 12473.0
Europe 77.6 25054.5
Oceania 80.7 29810.2
by_stat = stats.swaplevel(axis=1).sort_index(axis=1)
by_stat.round(1).head(3)
max mean
gdpPercap lifeExp gdpPercap lifeExp
continent
Africa 13206.5 76.4 3089.0 54.8
Americas 42951.7 80.7 11003.0 73.6
Asia 47307.0 82.6 12473.0 70.7

Your turn#

From stats, show only the max columns, then find the continent with the highest single life expectancy.

maxes = stats.xs("max", axis=1, level=1)
print(maxes.round(1))
print(maxes["lifeExp"].idxmax())
           lifeExp  gdpPercap
continent                    
Africa        76.4    13206.5
Americas      80.7    42951.7
Asia          82.6    47307.0
Europe        81.8    49357.2
Oceania       81.2    34435.4
Asia
flat = stats.copy()
flat.columns = flat.columns.map("_".join)
flat.round(1).head(3)
lifeExp_mean lifeExp_max gdpPercap_mean gdpPercap_max
continent
Africa 54.8 76.4 3089.0 13206.5
Americas 73.6 80.7 11003.0 42951.7
Asia 70.7 82.6 12473.0 47307.0
named = recent.groupby("continent").agg(
    life_mean=("lifeExp", "mean"),
    gdp_max=("gdpPercap", "max"),
)
named.round(1).head(3)
life_mean gdp_max
continent
Africa 54.8 13206.5
Americas 73.6 42951.7
Asia 70.7 47307.0

Move levels between rows and columns#

Problem: put a level where it's easiest to compare.

wide = cont.unstack("continent")
print(wide.columns.nlevels)
wide.loc[[1952, 2007], "lifeExp"].round(1)
2
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
back = wide.stack("continent")
print(back.shape, cont.shape)
(60, 2) (60, 2)
order = cont.reorder_levels(["year", "continent"]).sort_index()
order.loc[2007, "lifeExp"].round(1)
continent
Africa      54.8
Americas    73.6
Asia        70.7
Europe      77.6
Oceania     80.7
Name: lifeExp, dtype: float64
tidy = cont.reset_index()
print(tidy.columns.tolist())
tidy.round(1).head(3)
['continent', 'year', 'lifeExp', 'gdpPercap']
continent year lifeExp gdpPercap
0 Africa 1952 39.1 1252.6
1 Africa 1957 41.3 1385.2
2 Africa 1962 43.3 1598.1
only_year = cont.droplevel("continent")
print(only_year.index.is_unique)
False

Your turn#

Show GDP per person for the last three years, continents as columns.

gdp = cont["gdpPercap"].unstack("continent")
gdp.tail(3).round(0)
continent Africa Americas Asia Europe Oceania
year
1997 2379.0 8889.0 9834.0 19077.0 24024.0
2002 2599.0 9288.0 10174.0 21712.0 26939.0
2007 3089.0 11003.0 12473.0 25054.0 29810.0

Mini-project: biggest gains in each continent#

Question: which country added the most years of life, 1952-2007?

keys = ["continent", "country", "year"]
t = gap.set_index(keys)["lifeExp"].unstack("year")
print(t.shape)
t.iloc[:3, [0, -1]]
(142, 12)
year 1952 2007
continent country
Africa Algeria 43.077 72.301
Angola 30.015 42.731
Benin 38.223 56.728
gain = (t[2007] - t[1952]).round(1)
top = gain.groupby(level="continent").idxmax()
gain.loc[top.values]
continent  country  
Africa     Libya        31.2
Americas   Nicaragua    30.6
Asia       Oman         38.1
Europe     Turkey       28.2
Oceania    Australia    12.1
dtype: float64
gain.groupby(level="continent").median()
continent
Africa      16.05
Americas    21.60
Asia        24.70
Europe      12.60
Oceania     11.45
dtype: float64
big = gain > 30
big.groupby(level="continent").sum()
continent
Africa      1
Americas    1
Asia        7
Europe      0
Oceania     0
dtype: int64
share = (gain > 20).groupby(level="continent").mean()
share.round(2).sort_values(ascending=False)
continent
Asia        0.70
Americas    0.60
Africa      0.31
Europe      0.10
Oceania     0.00
dtype: float64
gain.nsmallest(3)
continent  country  
Africa     Zimbabwe    -5.0
           Swaziland   -1.8
           Zambia       0.3
dtype: float64
winners = gain.loc[top.values].droplevel("continent")
ax = winners.sort_values().plot(kind="barh", figsize=(7, 4))
ax.set_title("Biggest life expectancy gain per continent, 1952-2007")
ax.set_xlabel("Years of life gained")
ax.set_ylabel("Country")
plt.show()
No description has been provided for this image

Recap#

  • set_index with a list builds a MultiIndex; each label is a tuple
  • loc selects from the outer level; xs selects from any level
  • Sort the index before slicing, and use IndexSlice for ranges
  • Two-level columns: select with tuples, flatten with map
  • groupby(level=...) summarises one level of the index

Challenges#

  1. Use xs to compare GDP per person in all Asian countries in 1977.
  2. Find each continent's most populous country in 2007 with idxmax.
  3. Build min, mean and max of pop per continent, then flatten the columns.

What's next#

Lesson 32: Concatenating DataFrames Vertically and Horizontally Combine several tables into one with concat.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.