Level 7 · Conversion rate by device and source

Rates in a grid

A rate is an average of a yes/no outcome. Python treats True as 1 and False as 0, so the mean of a boolean column is the share of rows that are True. That makes a conversion-rate grid one pivot_table with aggfunc="mean":

web_sessions.pivot_table(index="device", columns="source",
                         values="converted", aggfunc="mean").round(3)
# source   direct  email  organic   paid  social
# device
# desktop   0.057  0.128    0.023  0.022   0.010
# mobile    0.045  0.072    0.015  0.011   0.004
# tablet    0.050  0.092    0.016  0.018   0.003

Email visitors on desktop convert at 12.8%, social visitors on tablets at 0.3%. Each cell is computed from the raw sessions in it, so the rates are exact, not averages of averages.

margins: totals for the row and column

margins=True adds one extra column and one extra row, by default called All. Each margin is the same aggregate computed over all the raw rows of that row or column. For counts that’s a total; for a mean it’s the overall rate:

web_sessions.pivot_table(index="device", columns="source", values="session_id",
                         aggfunc="count", margins=True)
# source   direct  email  organic  paid  social    All
# desktop    2290   1738     4933  3277    2303  14541
# ...
# All        6410   4770    13531  8887    6402  40000

margins_name="all" renames the margin row and column. With aggfunc="mean", the margin of the desktop row is desktop’s overall conversion rate (0.039), and the corner cell is the site-wide rate: 1,193 conversions in 40,000 sessions, 0.030.

Note what the margin is not: the average of the cells in its row. Averaging the three device rates gives 0.0303, slightly off the true 0.0298, because mobile has far more sessions than tablet. The margin weights every session equally, which is what you want.

pivot versus pivot_table

pandas also has DataFrame.pivot. It only moves values into a grid and never aggregates, so it works when each row and column pair appears exactly once. With 40,000 sessions spread over 15 cells, it fails:

web_sessions.pivot(index="device", columns="source", values="converted")
# ValueError: Index contains duplicate entries, cannot reshape

Use pivot on data that’s already one row per cell (for example a long table you just built with groupby), and pivot_table whenever rows need combining.

Common mistakes

• Reaching for pivot when many rows share a cell, and hitting the duplicate-entries error.
• Computing the overall rate as the average of the cell rates, which ignores group sizes.
• Rounding converted or casting it to text before the pivot. Leave it boolean and let mean count the True values.
• Forgetting margins_name, so the margins are labelled All instead of the name asked for.