Mathew K Analytics

Lesson 28 · Pandas Full Course

Advanced groupby in Pandas: Multi-level Groups and Custom Aggregations | Pandas Course #28

In this lesson you'll be able to: Group by three keys and read the multi-level result Select, roll up and rank across index levels Write your own…

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 28: Multi-level Grouping and Custom Aggregations#

In this lesson you'll be able to:

  • Group by three keys and read the multi-level result
  • Select, roll up and rank across index levels
  • Write your own aggregation functions
  • Flatten two-level column names into tidy labels
  • Calculate whole-group ratios, like profit margin, correctly Data: the Titanic passenger list and 8,399 Superstore orders

Warm-up: class and sex on the Titanic#

Lesson 27 grouped by two keys. Same data, same move.

import pandas as pd
df = pd.read_csv("data/pandas/titanic.csv")
names = {1: "First", 2: "Second", 3: "Third"}
df["Class"] = df["Pclass"].map(names)
rates = df.groupby(["Class", "Sex"])["Survived"].mean()
rates.unstack().round(2)
Sex female male
Class
First 0.97 0.37
Second 0.92 0.16
Third 0.50 0.14

Group by three keys and read the MultiIndex#

Problem: two keys can hide a third factor, like age.

bands = [0, 15, 80]
df["age_group"] = pd.cut(df["Age"], bins=bands,
                         labels=["Child", "Adult"])
df["age_group"].value_counts(dropna=False)
age_group
Adult    631
NaN      177
Child     83
Name: count, dtype: int64
keys = ["Class", "Sex", "age_group"]
grp = df.groupby(keys, observed=True)
table = grp["Survived"].agg(["size", "mean"]).round(2)
table
size mean
Class Sex age_group
First female Child 3 0.67
Adult 82 0.98
male Child 3 1.00
Adult 98 0.38
Second female Child 10 1.00
Adult 64 0.91
male Child 9 1.00
Adult 90 0.07
Third female Child 30 0.53
Adult 72 0.43
male Child 28 0.32
Adult 225 0.13
print(table.index.nlevels)
print(table.index.names)
3
['Class', 'Sex', 'age_group']
table["mean"].unstack("age_group")
age_group Child Adult
Class Sex
First female 0.67 0.98
male 1.00 0.38
Second female 1.00 0.91
male 1.00 0.07
Third female 0.53 0.43
male 0.32 0.13
table.loc[("Third", "male")]
size mean
age_group
Child 28 0.32
Adult 225 0.13
table.loc[("First", "female", "Adult")]
size    82.00
mean     0.98
Name: (First, female, Adult), dtype: float64
table.sort_values("mean").head(3)
size mean
Class Sex age_group
Second male Adult 90 0.07
Third male Adult 225 0.13
Child 28 0.32

Common mistake: using loc for a label on an inner level

table.loc["Child"]
---------------------------------------------------------------------------
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: 'Child'

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

KeyError                                  Traceback (most recent call last)
Cell In[10], line 1
----> 1 table.loc["Child"]

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: 'Child'
table.xs("Child", level="age_group")
size mean
Class Sex
First female 3 0.67
male 3 1.00
Second female 10 1.00
male 9 1.00
Third female 30 0.53
male 28 0.32

Quick check: were boys in third class saved as often as boys in first?

boys = table.xs(("male", "Child"), level=["Sex", "age_group"])
boys
size mean
Class
First 3 1.00
Second 9 1.00
Third 28 0.32

Your turn#

Show adult survival rates only, as a Class by Sex grid.

adults = table.xs("Adult", level="age_group")
adults["mean"].unstack()
Sex female male
Class
First 0.98 0.38
Second 0.91 0.07
Third 0.43 0.13

Roll up, reshape and rank across levels#

Problem: a detailed table must be rolled up and ranked for a report.

orders = pd.read_csv("data/pandas/superstore.csv")
short = {"Product Category": "Category",
         "Product Sub-Category": "Sub",
         "Customer Segment": "Segment"}
orders = orders.rename(columns=short)
cols = ["Region", "Segment", "Category", "Sales", "Profit"]
print(orders.shape)
orders[cols].head(3)
(8399, 21)
Region Segment Category Sales Profit
0 Nunavut Small Business Office Supplies 261.54 -213.25
1 Nunavut Consumer Office Supplies 10123.02 457.81
2 Nunavut Consumer Office Supplies 244.57 46.71
sales = orders.groupby(["Region", "Category"])["Sales"].sum()
print(sales.shape)
sales.head(6).round(0)
(24,)
Region                 Category       
Atlantic               Furniture          708727.0
                       Office Supplies    478464.0
                       Technology         827057.0
Northwest Territories  Furniture          282838.0
                       Office Supplies    216849.0
                       Technology         301160.0
Name: Sales, dtype: float64
by_region = sales.groupby(level="Region").sum()
by_region.sort_values(ascending=False).round(0)
Region
West                     3597549.0
Ontario                  3063212.0
Prarie                   2837305.0
Atlantic                 2014248.0
Quebec                   1510195.0
Yukon                     975867.0
Northwest Territories     800847.0
Nunavut                   116376.0
Name: Sales, dtype: float64
by_cat = sales.swaplevel().sort_index()
by_cat.head(4).round(0)
Category   Region               
Furniture  Atlantic                  708727.0
           Northwest Territories     282838.0
           Nunavut                    43546.0
           Ontario                  1109618.0
Name: Sales, dtype: float64
region_total = sales.groupby(level="Region").transform("sum")
share = (sales / region_total).round(2)
share.unstack()
Category Furniture Office Supplies Technology
Region
Atlantic 0.35 0.24 0.41
Northwest Territories 0.35 0.27 0.38
Nunavut 0.37 0.33 0.29
Ontario 0.36 0.30 0.33
Prarie 0.32 0.25 0.42
Quebec 0.40 0.23 0.37
West 0.33 0.22 0.45
Yukon 0.34 0.23 0.43
ranked = sales.sort_values(ascending=False)
ranked.groupby(level="Region").head(1).round(0)
Region                 Category  
West                   Technology    1627049.0
Prarie                 Technology    1198023.0
Ontario                Furniture     1109618.0
Atlantic               Technology     827057.0
Quebec                 Furniture      605784.0
Yukon                  Technology     417991.0
Northwest Territories  Technology     301160.0
Nunavut                Furniture       43546.0
Name: Sales, dtype: float64
flat = sales.reset_index(name="total_sales")
flat.head(3).round(0)
Region Category total_sales
0 Atlantic Furniture 708727.0
1 Atlantic Office Supplies 478464.0
2 Atlantic Technology 827057.0

Your turn#

Which customer segment brings in the most sales in each category?

seg = orders.groupby(["Category", "Segment"])["Sales"].sum()
seg = seg.sort_values(ascending=False)
seg.groupby(level="Category").head(1).round(0)
Category         Segment  
Technology       Corporate    2294749.0
Furniture        Corporate    1862841.0
Office Supplies  Corporate    1341316.0
Name: Sales, dtype: float64

Write your own aggregation functions#

Problem: the question needs a statistic pandas doesn't have built in.

print(orders["Profit"].median())
print(round((orders["Profit"] < 0).mean(), 3))
-1.5
0.508
def loss_share(profits):
    return (profits < 0).mean()

by_cat = orders.groupby("Category")["Profit"]
by_cat.agg(loss_share).round(3)
Category
Furniture          0.535
Office Supplies    0.534
Technology         0.427
Name: Profit, dtype: float64
spread = orders.groupby("Category")["Discount"]
spread.agg(lambda d: d.max() - d.min())
Category
Furniture          0.25
Office Supplies    0.10
Technology         0.10
Name: Discount, dtype: float64
by_cat.agg(["median", "mean", loss_share]).round(2)
median mean loss_share
Category
Furniture -14.25 68.12 0.53
Office Supplies -3.84 112.37 0.53
Technology 66.22 429.21 0.43

Common mistake: a custom function that returns many values

by_cat.agg(lambda p: p[p < 0])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[26], line 1
----> 1 by_cat.agg(lambda p: p[p < 0])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\generic.py:493, in SeriesGroupBy.aggregate(self, func, engine, engine_kwargs, *args, **kwargs)
    484     obj = self._obj_with_exclusions
    485     return self._wrap_aggregated_output(
    486         self.obj._constructor(
    487             [],
   (...)
    491         )
    492     )
--> 493 return self._python_agg_general(func, *args, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\generic.py:501, in SeriesGroupBy._python_agg_general(self, func, *args, **kwargs)
    498 f = lambda x: func(x, *args, **kwargs)
    500 obj = self._obj_with_exclusions
--> 501 result = self._grouper.agg_series(obj, f)
    502 res = obj._constructor(result, name=obj.name)
    503 return self._wrap_aggregated_output(res)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\ops.py:988, in BaseGrouper.agg_series(self, obj, func, preserve_dtype)
    972 @final
    973 def agg_series(
    974     self, obj: Series, func: Callable, preserve_dtype: bool = False
    975 ) -> ArrayLike:
    976     """
    977     Parameters
    978     ----------
   (...)
    986     np.ndarray or ExtensionArray
    987     """
--> 988     result = self._aggregate_series_pure_python(obj, func)
    989     return obj.array._cast_pointwise_result(result)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\ops.py:1006, in BaseGrouper._aggregate_series_pure_python(self, obj, func)
   1002 res = extract_result(res)
   1004 if not initialized:
   1005     # We only do this validation on the first iteration
-> 1006     check_result_array(res, group.dtype)
   1007     initialized = True
   1009 result[i] = res

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\ops.py:89, in check_result_array(obj, dtype)
     85 if isinstance(obj, np.ndarray):
     86     if dtype != object:
     87         # If it is object dtype, the function can be a reduction/aggregation
     88         #  and still return an ndarray e.g. test_agg_over_numpy_arrays
---> 89         raise ValueError("Must produce aggregated value")

ValueError: Must produce aggregated value
by_cat.agg(lambda p: p[p < 0].sum()).round(0)
Category
Furniture         -435589.0
Office Supplies   -284519.0
Technology        -382395.0
Name: Profit, dtype: float64
summary = orders.groupby("Category").agg(
    orders=("Order ID", "nunique"),
    loss_rate=("Profit", loss_share),
    biggest=("Sales", "max"),
    lost=("Profit", lambda p: p[p < 0].sum()),
)
summary.round(2)
orders loss_rate biggest lost
Category
Furniture 1561 0.53 29345.27 -435589.36
Office Supplies 3632 0.53 25409.63 -284519.05
Technology 1858 0.43 89061.05 -382394.60

Your turn#

Write big_share: the share of orders over 1,000 dollars, per Category.

def big_share(sales):
    return (sales > 1000).mean()

orders.groupby("Category")["Sales"].agg(big_share).round(3)
Category
Furniture          0.567
Office Supplies    0.168
Technology         0.574
Name: Sales, dtype: float64

Quick check: what share of Technology orders in Nunavut lost money?

rk = orders.groupby(["Region", "Category"])["Profit"]
loss = rk.agg(loss_share).round(2)
print(loss.loc[("Nunavut", "Technology")])
0.56

Flatten two-level column names#

Problem: several statistics per column give awkward two-level headers.

wide = orders.groupby("Region").agg({
    "Sales": ["sum", "mean"],
    "Profit": ["sum", "min"],
})
print(wide.columns.tolist())
[('Sales', 'sum'), ('Sales', 'mean'), ('Profit', 'sum'), ('Profit', 'min')]
wide.sort_values(("Profit", "sum")).round(0).head(3)
Sales Profit
sum mean sum min
Region
Nunavut 116376.0 1473.0 2841.0 -1766.0
Yukon 975867.0 1800.0 73849.0 -9079.0
Northwest Territories 800847.0 2033.0 100653.0 -11054.0

Common mistake: asking for a joined name that doesn't exist

wide["Profit_sum"]
---------------------------------------------------------------------------
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: 'Profit_sum'

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

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

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: 'Profit_sum'
wide.columns = ["_".join(pair) for pair in wide.columns]
wide["Profit_sum"].sort_values().round(0).head(3)
Region
Nunavut                    2841.0
Yukon                     73849.0
Northwest Territories    100653.0
Name: Profit_sum, dtype: float64
named = orders.groupby("Region").agg(
    sales_sum=("Sales", "sum"),
    profit_min=("Profit", "min"),
)
print(named.columns.tolist())
['sales_sum', 'profit_min']

Your turn#

Build median and max Shipping Cost per Category, flattened.

ship = orders.groupby("Category").agg(
    {"Shipping Cost": ["median", "max"]})
ship.columns = ["_".join(pair) for pair in ship.columns]
ship
Shipping Cost_median Shipping Cost_max
Category
Furniture 26.0 164.73
Office Supplies 5.4 99.00
Technology 5.5 69.30

Whole-group ratios with apply#

Problem: a group's margin is total profit over total sales, not an average.

orders["margin"] = orders["Profit"] / orders["Sales"]
naive = orders.groupby("Category")["margin"].mean()
naive.round(3)
Category
Furniture         -0.076
Office Supplies   -0.164
Technology        -0.110
Name: margin, dtype: float64
def margin(g):
    return g["Profit"].sum() / g["Sales"].sum()

two = orders.groupby("Category")[["Sales", "Profit"]]
two.apply(margin).round(3)
Category
Furniture          0.023
Office Supplies    0.138
Technology         0.148
dtype: float64

Quick check: why do the two answers disagree so much?

small = orders["Sales"] < 100
print(round(orders.loc[small, "margin"].mean(), 3))
print(round(orders.loc[~small, "margin"].mean(), 3))
-0.438
-0.069
totals = orders.groupby(["Segment", "Category"])[["Sales", "Profit"]]
totals = totals.sum()
ratio = totals["Profit"] / totals["Sales"]
ratio.unstack().round(3)
Category Furniture Office Supplies Technology
Segment
Consumer 0.038 0.128 0.126
Corporate 0.012 0.151 0.163
Home Office 0.019 0.126 0.131
Small Business 0.032 0.139 0.161

Your turn#

Rank the 8 regions by true margin, lowest first.

reg = orders.groupby("Region")[["Sales", "Profit"]].sum()
(reg["Profit"] / reg["Sales"]).sort_values().round(3)
Region
Nunavut                  0.024
Yukon                    0.076
West                     0.083
Quebec                   0.093
Prarie                   0.113
Ontario                  0.113
Atlantic                 0.119
Northwest Territories    0.126
dtype: float64

Mini-project: where is the store losing money?#

Plan: totals by category and sub-category, margins, then the losers.

keys = ["Category", "Sub"]
subs = orders.groupby(keys).agg(
    sales=("Sales", "sum"),
    profit=("Profit", "sum"),
    loss_rate=("Profit", loss_share),
)
subs["margin"] = subs["profit"] / subs["sales"]
print(len(subs))
17

losers = subs[subs["profit"] < 0].sort_values("profit")
show = ["sales", "profit", "margin"]
losers.droplevel("Category")[show].round(3)
sales profit margin
Sub
Tables 1896008.142 -99062.50 -0.052
Bookcases 822652.040 -33582.13 -0.041
Scissors, Rulers and Trimmers 80996.310 -7799.25 -0.096
Rubber Bands 15006.630 -102.67 -0.007
worst = subs["margin"].sort_values().head(8)
labels = worst.index.get_level_values("Sub")
import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(8, 4))
ax.barh(labels, worst.values)
ax.set_title("Superstore: lowest profit margins")
ax.set_xlabel("Profit margin (profit / sales)")
ax.set_ylabel("Sub-category")
plt.show()
No description has been provided for this image
tables = orders[orders["Sub"] == "Tables"]
t = tables.groupby("Region")[["Sales", "Profit"]].sum()
t["margin"] = t["Profit"] / t["Sales"]
t.sort_values("Profit").round(2).head(3)
Sales Profit margin
Region
Ontario 406439.93 -42422.71 -0.10
Quebec 275689.43 -28300.74 -0.10
West 391504.12 -16963.81 -0.04
loss = subs.loc[("Furniture", "Tables"), "profit"]
total = subs["profit"].sum()
print(f"Tables lost {-loss:,.0f}; store profit {total:,.0f}")
Tables lost 99,062; store profit 1,521,768

Recap#

  • Pass a list of keys to get a MultiIndex, outermost first
  • loc drills down with tuples; xs picks labels on any level
  • groupby(level=...) rolls up; transform gives shares of a level
  • A custom aggregation returns ONE value per group
  • Flatten column pairs with join; ratios come from totals

Challenges#

  1. Titanic: share of missing ages per Class and Sex, with a custom function.
  2. Superstore: true margin by Region and Ship Mode, unstacked.
  3. Find the most profitable sub-category in each region.

What's next#

Lesson 29: Pivot Tables and Cross-tabulations Build these grids in one call, with totals, using pivot_table and crosstab.

Found this useful?

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