Mathew K Analytics

Lesson 29 · Pandas Full Course

Pivot Tables in Pandas: Build Excel-Style Reports in One Line | Pandas Course #29

In this lesson you'll be able to: Build a summary grid in one call with pivottable Choose the statistic, or several, with aggfunc Add totals and fill gaps…

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 29: Pivot Tables and Cross-tabulations#

In this lesson you'll be able to:

  • Build a summary grid in one call with pivot_table
  • Choose the statistic, or several, with aggfunc
  • Add totals and fill gaps with margins and fill_value
  • Count and compare categories with crosstab
  • Chart a pivot table to tell its story Data: 8,399 Superstore orders and the Titanic passenger list

Warm-up: a grid the long way#

Lesson 28 built grids with groupby and unstack. Here's one more.

import pandas as pd
orders = pd.read_csv("data/pandas/superstore.csv")
short = {"Product Category": "Category",
         "Customer Segment": "Segment",
         "Order Priority": "Priority", "Ship Mode": "Ship"}
orders = orders.rename(columns=short)
grp = orders.groupby(["Region", "Category"])["Sales"].sum()
grp.unstack().round(0)
Category Furniture Office Supplies Technology
Region
Atlantic 708727.0 478464.0 827057.0
Northwest Territories 282838.0 216849.0 301160.0
Nunavut 43546.0 38615.0 34215.0
Ontario 1109618.0 927431.0 1026164.0
Prarie 919191.0 720090.0 1198023.0
Quebec 605784.0 351823.0 552588.0
West 1172989.0 797511.0 1627049.0
Yukon 335898.0 221978.0 417991.0

Build a grid in one call with pivot_table#

Problem: say what goes down the side, across the top, and in the cells.

grid = orders.pivot_table(
    index="Region",
    columns="Category",
    values="Sales",
    aggfunc="sum",
)
grid.round(0)
Category Furniture Office Supplies Technology
Region
Atlantic 708727.0 478464.0 827057.0
Northwest Territories 282838.0 216849.0 301160.0
Nunavut 43546.0 38615.0 34215.0
Ontario 1109618.0 927431.0 1026164.0
Prarie 919191.0 720090.0 1198023.0
Quebec 605784.0 351823.0 552588.0
West 1172989.0 797511.0 1627049.0
Yukon 335898.0 221978.0 417991.0
print(grid.equals(grp.unstack()))
True
grid.idxmax(axis=1)
Region
Atlantic                 Technology
Northwest Territories    Technology
Nunavut                   Furniture
Ontario                   Furniture
Prarie                   Technology
Quebec                    Furniture
West                     Technology
Yukon                    Technology
dtype: str

Quick check: what does pivot_table calculate if you leave out aggfunc?

avg = orders.pivot_table(index="Region", values="Sales")
avg.round(2).head(3)
Sales
Region
Atlantic 1865.04
Northwest Territories 2032.61
Nunavut 1473.12

Common mistake: using pivot on data with repeats

orders.pivot(index="Region", columns="Category", values="Sales")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[7], line 1
----> 1 orders.pivot(index="Region", columns="Category", values="Sales")

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
orders.pivot_table(index="Region", columns="Category",
                   values="Sales", aggfunc="max").round(0)
Category Furniture Office Supplies Technology
Region
Atlantic 28180.0 20175.0 89061.0
Northwest Territories 26133.0 23793.0 22079.0
Nunavut 14224.0 10123.0 12028.0
Ontario 21426.0 23106.0 24051.0
Prarie 29345.0 17854.0 41343.0
Quebec 24701.0 25410.0 45924.0
West 28389.0 23516.0 29885.0
Yukon 17248.0 10223.0 23950.0

Your turn#

Total Profit by Region (rows) and Segment (columns).

orders.pivot_table(index="Region", columns="Segment",
                   values="Profit", aggfunc="sum").round(0)
Segment Consumer Corporate Home Office Small Business
Region
Atlantic 85085.0 44102.0 63196.0 46578.0
Northwest Territories 11220.0 43577.0 10525.0 35331.0
Nunavut 4041.0 -1353.0 1058.0 -905.0
Ontario 25480.0 136340.0 129180.0 55869.0
Prarie 60583.0 169012.0 55206.0 36360.0
Quebec 31704.0 52913.0 24803.0 31006.0
West 67987.0 139845.0 7665.0 81512.0
Yukon 1861.0 15311.0 26720.0 29958.0

Choose the statistic with aggfunc#

Problem: one question needs counts, another totals, another several at once.

orders.pivot_table(index="Segment", columns="Category",
                   values="Order ID", aggfunc="count")
Category Furniture Office Supplies Technology
Segment
Consumer 358 865 426
Corporate 633 1680 763
Home Office 406 1145 481
Small Business 327 920 395
orders.pivot_table(index="Segment", columns="Category",
                   values="Customer Name", aggfunc="nunique")
Category Furniture Office Supplies Technology
Segment
Consumer 168 184 168
Corporate 269 328 285
Home Office 189 231 199
Small Business 154 181 156
orders.pivot_table(index="Category", values=["Sales", "Profit"],
                   aggfunc={"Sales": "sum",
                            "Profit": "median"}).round(2)
Profit Sales
Category
Furniture -14.25 5178590.54
Office Supplies -3.84 3752762.10
Technology 66.22 5984248.18
stats = orders.pivot_table(index="Category", values="Profit",
                           aggfunc=["sum", "mean", "min"])
stats.round(0)
sum mean min
Profit Profit Profit
Category
Furniture 117433.0 68.0 -11054.0
Office Supplies 518021.0 112.0 -2175.0
Technology 886314.0 429.0 -14141.0
reg = orders.pivot_table(index="Region", values=["Sales", "Profit"],
                         aggfunc="sum")
reg["margin"] = reg["Profit"] / reg["Sales"]
reg.sort_values("margin").round(3).head(3)
Profit Sales margin
Region
Nunavut 2841.11 116376.484 0.024
Yukon 73849.21 975867.371 0.076
West 297008.61 3597549.276 0.083
def loss_share(profits):
    return (profits < 0).mean()

orders.pivot_table(index="Region", columns="Category",
                   values="Profit", aggfunc=loss_share).round(2)
Category Furniture Office Supplies Technology
Region
Atlantic 0.55 0.52 0.40
Northwest Territories 0.55 0.51 0.47
Nunavut 0.46 0.52 0.56
Ontario 0.53 0.52 0.42
Prarie 0.49 0.53 0.44
Quebec 0.60 0.56 0.43
West 0.54 0.55 0.41
Yukon 0.52 0.54 0.47

Your turn#

Median Sales by Segment (rows) and Category (columns).

orders.pivot_table(index="Segment", columns="Category",
                   values="Sales", aggfunc="median").round(2)
Category Furniture Office Supplies Technology
Segment
Consumer 1366.37 207.19 1074.06
Corporate 1323.67 205.48 1313.64
Home Office 1571.98 223.64 1298.29
Small Business 1217.62 202.48 1383.92

Add totals and fill the gaps#

Problem: reports need totals, and empty cells should read as zero.

tot = orders.pivot_table(index="Region", columns="Category",
                         values="Sales", aggfunc="sum",
                         margins=True, margins_name="Total")
tot.round(0)
Category Furniture Office Supplies Technology Total
Region
Atlantic 708727.0 478464.0 827057.0 2014248.0
Northwest Territories 282838.0 216849.0 301160.0 800847.0
Nunavut 43546.0 38615.0 34215.0 116376.0
Ontario 1109618.0 927431.0 1026164.0 3063212.0
Prarie 919191.0 720090.0 1198023.0 2837305.0
Quebec 605784.0 351823.0 552588.0 1510195.0
West 1172989.0 797511.0 1627049.0 3597549.0
Yukon 335898.0 221978.0 417991.0 975867.0
Total 5178591.0 3752762.0 5984248.0 14915601.0
tot.drop(index="Total").sort_values("Total", ascending=False).round(0)
Category Furniture Office Supplies Technology Total
Region
West 1172989.0 797511.0 1627049.0 3597549.0
Ontario 1109618.0 927431.0 1026164.0 3063212.0
Prarie 919191.0 720090.0 1198023.0 2837305.0
Atlantic 708727.0 478464.0 827057.0 2014248.0
Quebec 605784.0 351823.0 552588.0 1510195.0
Yukon 335898.0 221978.0 417991.0 975867.0
Northwest Territories 282838.0 216849.0 301160.0 800847.0
Nunavut 43546.0 38615.0 34215.0 116376.0
box = orders.pivot_table(index="Product Container",
                         columns="Category", values="Sales",
                         aggfunc="sum")
box.round(0)
Category Furniture Office Supplies Technology
Product Container
Jumbo Box 2448408.0 NaN 372887.0
Jumbo Drum 1709024.0 338966.0 1358080.0
Large Box 488914.0 336671.0 1247345.0
Medium Box 229814.0 114480.0 350581.0
Small Box 138866.0 2695506.0 2254595.0
Small Pack 95712.0 122289.0 328797.0
Wrap Bag 67853.0 144850.0 71964.0
box = orders.pivot_table(index="Product Container",
                         columns="Category", values="Sales",
                         aggfunc="sum", fill_value=0)
box.round(0)
Category Furniture Office Supplies Technology
Product Container
Jumbo Box 2448408.0 0.0 372887.0
Jumbo Drum 1709024.0 338966.0 1358080.0
Large Box 488914.0 336671.0 1247345.0
Medium Box 229814.0 114480.0 350581.0
Small Box 138866.0 2695506.0 2254595.0
Small Pack 95712.0 122289.0 328797.0
Wrap Bag 67853.0 144850.0 71964.0
orders.pivot_table(index="Category", values="Sales",
                   aggfunc="mean", margins=True).round(2)
Sales
Category
Furniture 3003.82
Office Supplies 814.05
Technology 2897.94
All 1775.88

Common mistake: inventing a name for the statistic

orders.pivot_table(index="Region", values="Sales", aggfunc="total")
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[22], line 1
----> 1 orders.pivot_table(index="Region", values="Sales", aggfunc="total")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:11161, in DataFrame.pivot_table(self, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name, observed, sort, **kwargs)
  11143 @Substitution("")
  11144 @Appender(_shared_docs["pivot_table"])
  11145 def pivot_table(
   (...)
  11157     **kwargs,
  11158 ) -> DataFrame:
  11159     from pandas.core.reshape.pivot import pivot_table
> 11161     return pivot_table(
  11162         self,
  11163         values=values,
  11164         index=index,
  11165         columns=columns,
  11166         aggfunc=aggfunc,
  11167         fill_value=fill_value,
  11168         margins=margins,
  11169         dropna=dropna,
  11170         margins_name=margins_name,
  11171         observed=observed,
  11172         sort=sort,
  11173         **kwargs,
  11174     )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\pivot.py:267, in pivot_table(data, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name, observed, sort, **kwargs)
    264     table = concat(pieces, keys=keys, axis=1)
    265     return table.__finalize__(data, method="pivot_table")
--> 267 table = __internal_pivot_table(
    268     data,
    269     values,
    270     index,
    271     columns,
    272     aggfunc,
    273     fill_value,
    274     margins,
    275     dropna,
    276     margins_name,
    277     observed,
    278     sort,
    279     kwargs,
    280 )
    281 return table.__finalize__(data, method="pivot_table")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\pivot.py:344, in __internal_pivot_table(data, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name, observed, sort, kwargs)
    339 if values_passed:
    340     # GH#57876 and GH#61292
    341     # mypy is not aware `grouped[values]` will always be a DataFrameGroupBy
    342     grouped = grouped[values]  # type: ignore[assignment]
--> 344 agged = grouped.agg(aggfunc, **kwargs)
    346 if dropna and isinstance(agged, ABCDataFrame) and len(agged.columns):
    347     agged = agged.dropna(how="all")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\generic.py:2291, in DataFrameGroupBy.aggregate(self, func, engine, engine_kwargs, *args, **kwargs)
   2288     kwargs["engine_kwargs"] = engine_kwargs
   2290 op = GroupByApply(self, func, args=args, kwargs=kwargs)
-> 2291 result = op.agg()
   2292 if not is_dict_like(func) and result is not None:
   2293     # GH #52849
   2294     if not self.as_index and is_list_like(func):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\apply.py:291, in Apply.agg(self)
    288 func = self.func
    290 if isinstance(func, str):
--> 291     return self.apply_str()
    293 if is_dict_like(func):
    294     return self.agg_dict_like()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\apply.py:701, in Apply.apply_str(self)
    697     if "axis" in arg_names and not isinstance(
    698         obj, (SeriesGroupBy, DataFrameGroupBy)
    699     ):
    700         self.kwargs["axis"] = self.axis
--> 701 return self._apply_str(obj, func, *self.args, **self.kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\apply.py:805, in Apply._apply_str(self, obj, func, *args, **kwargs)
    803 else:
    804     msg = f"'{func}' is not a valid function for '{type(obj).__name__}' object"
--> 805     raise AttributeError(msg)

AttributeError: 'total' is not a valid function for 'DataFrameGroupBy' object
orders.pivot_table(index="Region", values="Sales",
                   aggfunc="sum").round(0).head(3)
Sales
Region
Atlantic 2014248.0
Northwest Territories 800847.0
Nunavut 116376.0

Your turn#

Count orders by Product Container and Ship mode, zeros filled, with totals.

orders.pivot_table(index="Product Container", columns="Ship",
                   values="Order ID", aggfunc="count",
                   fill_value=0, margins=True)
Ship Delivery Truck Express Air Regular Air All
Product Container
Jumbo Box 527 0 5 532
Jumbo Drum 619 0 5 624
Large Box 0 59 347 406
Medium Box 0 54 312 366
Small Box 0 602 3745 4347
Small Pack 0 124 832 956
Wrap Bag 0 144 1024 1168
All 1146 983 6270 8399

Count combinations with crosstab#

Problem: how often does each pair of categories occur, and in what share?

df = pd.read_csv("data/pandas/titanic.csv")
names = {1: "First", 2: "Second", 3: "Third"}
df["Class"] = df["Pclass"].map(names)
pd.crosstab(df["Class"], df["Sex"])
Sex female male
Class
First 94 122
Second 76 108
Third 144 347
pd.crosstab(df["Class"], df["Sex"], margins=True)
Sex female male All
Class
First 94 122 216
Second 76 108 184
Third 144 347 491
All 314 577 891

Common mistake: passing column names instead of columns

pd.crosstab("Class", "Sex")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[27], line 1
----> 1 pd.crosstab("Class", "Sex")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\pivot.py:1099, in crosstab(index, columns, values, rownames, colnames, aggfunc, margins, margins_name, dropna, normalize)
   1093 from pandas import DataFrame
   1095 data = {
   1096     **dict(zip(unique_rownames, index, strict=True)),
   1097     **dict(zip(unique_colnames, columns, strict=True)),
   1098 }
-> 1099 df = DataFrame(data, index=common_idx)
   1101 if values is None:
   1102     df["__dummy__"] = 0

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:769, in DataFrame.__init__(self, data, index, columns, dtype, copy)
    763     mgr = self._init_mgr(
    764         data, axes={"index": index, "columns": columns}, dtype=dtype, copy=copy
    765     )
    767 elif isinstance(data, dict):
    768     # GH#38939 de facto copy defaults to False only in non-dict cases
--> 769     mgr = dict_to_mgr(data, index, columns, dtype=dtype, copy=copy)
    770 elif isinstance(data, ma.MaskedArray):
    771     from numpy.ma import mrecords

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\construction.py:460, in dict_to_mgr(data, index, columns, dtype, copy)
    441 if copy:
    442     # We only need to copy arrays that will not get consolidated, i.e.
    443     #  only EA arrays
    444     arrays = [
    445         (
    446             x.copy()
   (...)
    457         for x in arrays
    458     ]
--> 460 return arrays_to_mgr(arrays, columns, index, dtype=dtype, consolidate=copy)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\construction.py:113, in arrays_to_mgr(arrays, columns, index, dtype, verify_integrity, consolidate)
    110 if verify_integrity:
    111     # figure out the index, if necessary
    112     if index is None:
--> 113         index = _extract_index(arrays)
    114     else:
    115         index = ensure_index(index)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\construction.py:634, in _extract_index(data)
    631         raise ValueError("Per-column arrays must each be 1-dimensional")
    633 if not indexes and not raw_lengths:
--> 634     raise ValueError("If using all scalar values, you must pass an index")
    636 if have_series:
    637     index = union_indexes(indexes)

ValueError: If using all scalar values, you must pass an index
pd.crosstab(df["Class"], df["Sex"]).loc["Third"]
Sex
female    144
male      347
Name: Third, dtype: int64
pd.crosstab(df["Class"], df["Sex"], normalize="index").round(2)
Sex female male
Class
First 0.44 0.56
Second 0.41 0.59
Third 0.29 0.71

Quick check: what share of all women were in third class?

pd.crosstab(df["Class"], df["Sex"], normalize="columns").round(2)
Sex female male
Class
First 0.30 0.21
Second 0.24 0.19
Third 0.46 0.60
pd.crosstab(df["Class"], df["Sex"], normalize="all").round(2)
Sex female male
Class
First 0.11 0.14
Second 0.09 0.12
Third 0.16 0.39
pd.crosstab(df["Class"], df["Sex"], values=df["Survived"],
            aggfunc="mean").round(2)
Sex female male
Class
First 0.97 0.37
Second 0.92 0.16
Third 0.50 0.14
pd.crosstab([df["Class"], df["Sex"]], df["Survived"])
Survived 0 1
Class Sex
First female 3 91
male 77 45
Second female 6 70
male 91 17
Third female 72 72
male 300 47
pack = pd.crosstab(orders["Product Container"], orders["Category"],
                   normalize="columns")
pack.round(2)
Category Furniture Office Supplies Technology
Product Container
Jumbo Box 0.28 0.00 0.02
Jumbo Drum 0.22 0.02 0.08
Large Box 0.10 0.03 0.04
Medium Box 0.07 0.02 0.08
Small Box 0.14 0.66 0.50
Small Pack 0.12 0.05 0.24
Wrap Bag 0.07 0.21 0.04

Your turn#

Crosstab Class against Survived, as shares of each class.

pd.crosstab(df["Class"], df["Survived"],
            normalize="index").round(2)
Survived 0 1
Class
First 0.37 0.63
Second 0.53 0.47
Third 0.76 0.24

Multi-level pivots and charts#

Problem: a third factor, and a picture that shows the pattern.

rates = df.pivot_table(index=["Sex", "Class"], columns="Embarked",
                       values="Survived", aggfunc="mean")
rates.round(2)
Embarked C Q S
Sex Class
female First 0.98 1.00 0.96
Second 1.00 1.00 0.91
Third 0.65 0.73 0.38
male First 0.40 0.00 0.35
Second 0.20 0.00 0.15
Third 0.23 0.08 0.13
rates.loc["male"].round(2)
Embarked C Q S
Class
First 0.40 0.00 0.35
Second 0.20 0.00 0.15
Third 0.23 0.08 0.13

Quick check: for third class women, which port had the best survival rate?

third_women = rates.loc[("female", "Third")]
print(third_women.idxmax(), round(third_women.max(), 2))
Q 0.73
import matplotlib.pyplot as plt
ax = rates.loc["female"].plot(kind="bar", rot=0, figsize=(7, 4))
ax.set_title("Titanic: women's survival by class and port")
ax.set_xlabel("Passenger class")
ax.set_ylabel("Share who survived")
ax.legend(title="Port")
plt.show()
No description has been provided for this image
mix = pd.crosstab(df["Class"], df["Sex"], normalize="index")
ax = mix.plot(kind="bar", stacked=True, rot=0, figsize=(7, 4))
ax.set_title("Titanic: share of women and men in each class")
ax.set_xlabel("Passenger class")
ax.set_ylabel("Share of passengers")
plt.show()
No description has been provided for this image

Your turn#

Median Fare by Class (rows) and Embarked (columns), with totals.

df.pivot_table(index="Class", columns="Embarked", values="Fare",
               aggfunc="median", margins=True).round(2)
Embarked C Q S All
Class
First 78.27 90.00 52.00 58.69
Second 24.00 12.35 13.50 14.25
Third 7.90 7.75 8.05 8.05
All 29.70 7.75 13.00 14.45

Mini-project: do urgent orders ship faster?#

Plan: ship mode by priority, shipping cost, then profit by ship mode.

order = ["Critical", "High", "Medium", "Low", "Not Specified"]
ship = pd.crosstab(orders["Priority"], orders["Ship"],
                   normalize="index").loc[order]
ship.round(2)
Ship Delivery Truck Express Air Regular Air
Priority
Critical 0.14 0.12 0.73
High 0.14 0.12 0.74
Medium 0.13 0.12 0.75
Low 0.15 0.11 0.74
Not Specified 0.13 0.11 0.76
cost = orders.pivot_table(index="Priority", columns="Ship",
                          values="Shipping Cost", aggfunc="mean")
cost.loc[order].round(2)
Ship Delivery Truck Express Air Regular Air
Priority
Critical 47.30 8.71 7.28
High 45.19 6.86 7.65
Medium 46.15 8.13 7.69
Low 44.53 8.17 8.02
Not Specified 43.67 8.17 7.62
fmt = "%m/%d/%Y"
shipped = pd.to_datetime(orders["Ship Date"], format=fmt)
placed = pd.to_datetime(orders["Order Date"], format=fmt)
orders["days"] = (shipped - placed).dt.days
days = orders.pivot_table(index="Priority", columns="Ship",
                          values="days", aggfunc="mean")
days.loc[order].round(1)
Ship Delivery Truck Express Air Regular Air
Priority
Critical 1.5 1.5 1.5
High 1.5 1.4 1.4
Medium 1.6 1.5 1.5
Low 4.0 4.3 4.3
Not Specified 1.5 1.5 1.5
wait = orders.pivot_table(index="Priority", values="days")
wait = wait["days"].loc[order]
ax = wait.plot(kind="bar", rot=0, figsize=(8, 4))
ax.set_title("Average days from order to shipping, by priority")
ax.set_xlabel("Order priority")
ax.set_ylabel("Days to ship")
plt.show()
No description has been provided for this image
crit = ship.loc["Critical", "Express Air"]
low = ship.loc["Low", "Express Air"]
print(f"Express Air: critical {crit:.0%}, low {low:.0%}")
print(f"Days: critical {wait['Critical']:.1f}, low {wait['Low']:.1f}")
Express Air: critical 12%, low 11%
Days: critical 1.5, low 4.2

Recap#

  • pivot_table: index down the side, columns across, values in the cells
  • aggfunc defaults to mean; pass sum, count, a list, a dict or a function
  • pivot fails on repeats; pivot_table combines them
  • margins adds totals; fill_value fills empty cells
  • crosstab counts pairs; normalize turns counts into shares

Challenges#

  1. Superstore: total Profit by Region and Ship mode, with totals.
  2. Titanic: crosstab of Embarked and Class, as shares of each port.
  3. Superstore: loss_share by Segment and Ship mode. Which is worst?

What's next#

Lesson 30: Reshaping Data using melt, stack, and unstack Turn wide grids into long tables, and back again.

Found this useful?

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