Split–apply–combine, reshaping, and the four numbers every pricing actuary lives by: frequency, severity, pure premium, and loss ratio.
groupby with named aggregationspivot_table and meltCompanion notebook: lesson-04-aggregation-metrics.ipynb — worked examples and self-checking exercises.
The pattern behind every pivot table you’ve ever built.
vehicle_stylesum, mean, count…abt = pd.read_parquet("data/processed/abt.parquet")
abt.groupby("vehicle_style")["n_claims"].sum()
# Hatchback 508
# Minivan 221
# Pickup 374
# SUV 948
# Sedan 983
# Sports 320groupby(["vehicle_style", "region"])category keys, pass observed=True to skip empty combinationsEvery Excel pivot table is a groupby. The difference: this one is code, reruns on next quarter’s data unchanged, and can be reviewed line by line.
seg = abt.groupby("vehicle_style", observed=True).agg(
exposure=("exposure", "sum"),
claims=("n_claims", "sum"),
incurred=("incurred", "sum"),
premium=("earned_premium", "sum"),
vehicles=("vehicle_id", "count"),
)
seg["frequency"] = seg["claims"] / seg["exposure"]
seg["severity"] = seg["incurred"] / seg["claims"]
seg["pure_premium"] = seg["incurred"] / seg["exposure"]
seg["loss_ratio"] = seg["incurred"] / seg["premium"]new_column=(source_column, function) — one clearly named output per line. Functions can be names ("sum", "mean", "max", "nunique") or your own.
| Style | Exposure | Freq. | Severity | Pure prem. | LR |
|---|---|---|---|---|---|
| Sports | 1,198.4 | 0.2670 | 7,924 | 2,115.96 | 73.8% |
| Hatchback | 2,856.8 | 0.1778 | 6,891 | 1,225.32 | 76.0% |
| SUV | 5,580.4 | 0.1699 | 7,049 | 1,197.57 | 71.6% |
| Sedan | 6,318.3 | 0.1556 | 7,454 | 1,159.74 | 77.1% |
| Minivan | 1,486.9 | 0.1486 | 7,621 | 1,132.73 | 75.4% |
| Pickup | 2,660.2 | 0.1406 | 7,858 | 1,104.77 | 74.0% |
Aggregate the ingredients (sums), then divide.
# agg: one row per group
abt.groupby("region", observed=True)["exposure"].sum()
# transform: one value per ORIGINAL row, aligned to the index
by_region = abt.groupby("region", observed=True)
abt["region_share"] = (abt["exposure"]
/ by_region["exposure"].transform("sum"))
abt["region_share"].sum() # 3.0 (one per region)
# Relativity of each vehicle's segment to the portfolio
by_style = abt.groupby("vehicle_style")
abt["style_pp"] = (by_style["incurred"].transform("sum")
/ by_style["exposure"].transform("sum"))agg reduces: one row per grouptransform broadcasts: same shape as the input, so the result drops straight into a new columnInstead of “groupby → agg → merge back”, reach for transform. One line, no join to validate.
Wide ⇄ long, and taming multi-level columns.
pt = abt.pivot_table(index="vehicle_style", columns="region",
values=["incurred", "exposure"],
aggfunc="sum", observed=True)
pt.columns[:2].tolist()
# [('exposure', 'Mountains'), ('exposure', 'Piedmont')]
pure_premium = pt["incurred"] / pt["exposure"]| Pure premium | Mountains | Piedmont | Coastal Plain |
|---|---|---|---|
| Sports | 2,482 | 1,806 | 2,292 |
| SUV | 1,300 | 1,185 | 1,162 |
| Sedan | 916 | 1,053 | 1,384 |
| Hatchback | 1,219 | 1,137 | 1,322 |
| Pickup | 1,110 | 1,016 | 1,203 |
| Minivan | 898 | 1,419 | 932 |
index → rows, columns → columns, values + aggfunc → cellsvalues give MultiIndex columns: (measure, region)pt["incurred"]Flatten for export or plotting:
pt.columns = [f"{m}_{r}" for m, r in pt.columns]
→ 'exposure_Mountains', …
“Region” here is a quick longitude-based split (Mountains / Piedmont / Coastal Plain). In Lesson 6 we replace it with real territories.
long = abt.melt(
id_vars=["vehicle_id", "exposure", "vehicle_style",
"region", "age_band"],
value_vars=["n_liab", "n_coll", "n_comp"],
var_name="coverage", value_name="n")
long.shape # (71979, 7) = 23,993 vehicles × 3
long.head(3)
# vehicle_id exposure vehicle_style region age_band coverage n
# 0 VEH000001 1.000000 SUV Piedmont 35-49 n_liab 0.0
# 1 VEH000002 1.000786 SUV Piedmont 35-49 n_liab 0.0
# 2 VEH000003 0.999214 SUV Piedmont 35-49 n_liab 0.0
long.groupby("coverage")["n"].sum()| Wide | Long |
|---|---|
| one column per coverage | a coverage column + one value column |
| good for reading, Excel | good for groupby, seaborn, databases |
melt: wide → longpivot / pivot_table: long → widestack / unstack: move a level between the index and the columnsRule of thumb: store and compute long; present wide.
Frequency, severity, pure premium, loss ratio.
$$\text{Frequency} = \frac{\text{Claim count}}{\text{Exposure}} \qquad \text{Severity} = \frac{\text{Losses}}{\text{Claim count}}$$
$$\text{Pure premium} = \frac{\text{Losses}}{\text{Exposure}} = \text{Freq.} \times \text{Sev.} \qquad \text{Loss ratio} = \frac{\text{Losses}}{\text{Earned premium}}$$
| Whole portfolio | |
|---|---|
| Exposure | 20,100.95 car years |
| Claims · incurred | 3,354 · 24,669,862 |
| Frequency | 0.1669 |
| Severity | 7,355.36 |
| Pure premium | 1,227.30 = 0.1669 × 7,355.36 ✓ |
| Earned premium | 33,086,631 |
| Loss ratio | 74.6% |
Pure premium is the expected loss cost per unit of exposure — what the rate must cover before expenses and profit. Loss ratio asks whether the premium actually charged covers it.
“Losses” here are incurred: paid + case outstanding. Real work adds IBNR and development — Course 3.
# ❌ average each vehicle's own frequency
abt["freq_row"] = abt["n_claims"] / abt["exposure"]
abt.loc[abt.vehicle_style == "Sports", "freq_row"].mean()
# 0.2618
# ✅ total claims ÷ total exposure
s = abt[abt.vehicle_style == "Sports"]
s["n_claims"].sum() / s["exposure"].sum()
# 0.2670Averaging per-row ratios gives a vehicle insured for one week the same weight as one insured all year — and blows up when exposure is tiny.
The same trap appears with loss ratios by segment, average premium, and average severity. Always sum the numerator and denominator separately, then divide.
Equivalent: an exposure-weighted average of row frequencies.
| Cover | Exposure | Freq. | Severity | Pure prem. | LR |
|---|---|---|---|---|---|
| LIAB | 20,100.9 | 0.0648 | 12,683 | 821.49 | 66.5% |
| COLL | 16,388.8 | 0.0878 | 4,442 | 390.03 | 95.6% |
| COMP | 16,986.6 | 0.0361 | 2,879 | 103.90 | 113.4% |
Only vehicles that bought collision cover can have collision claims — so collision frequency uses collision exposure.
| Region | COMP freq. | COMP LR | LIAB LR |
|---|---|---|---|
| Mountains | 0.033 | 119.6% | 62.8% |
| Piedmont | 0.025 | 68.8% | 64.3% |
| Coastal Plain | 0.049 | 159.1% | 70.5% |
Comprehensive frequency near the coast is double the Piedmont’s — hail, flood, and storm damage — yet everyone pays the same territory load. That’s a pricing gap you can now measure. Lesson 6 finds where exactly it is.
age_band with pd.cut on youngest_driver_age (17–24, 25–34, 35–49, 50–64, 65–74, 75+)region from longitude (cuts at −81.2 and −78.6)metrics(df, by) returning exposure, claims, frequency, severity, pure premium, and loss ratio for any grouping| Band | Freq. | PP | LR |
|---|---|---|---|
| 17–24 | 0.2248 | 1,699 | 78.2% |
| 25–34 | 0.1811 | 1,317 | 78.2% |
| 35–49 | 0.1484 | 1,102 | 73.4% |
| 50–64 | 0.1430 | 1,113 | 75.0% |
| 65–74 | 0.1633 | 933 | 60.8% |
| 75+ | 0.2418 | 1,634 | 71.9% |
A U-shaped frequency curve — young and elderly drivers claim more.
Style × region × age band = 108 cells. Median exposure 118 car years — but:
| Smallest cells | Exposure | Claims |
|---|---|---|
| Sports · Mountains · 75+ | 5.55 | 2 |
| Sports · Coastal Plain · 75+ | 5.89 | 3 |
| Minivan · Mountains · 75+ | 6.13 | 1 |
25 of 108 cells have fewer than 10 claims. A frequency built on 2 claims could easily be half or double its true value.
The classical full-credibility standard for frequency is about 1,082 claims (90% probability of being within 5%). Few cells get there — which is why pricing moves from one-way tables to models (GLMs, Course 3).
claims column to every table and highlight cells under 30groupby + named agg for segment tablestransform to put group results on every rowpivot_table and melt, and flatten MultiIndex columnsmatplotlib and seaborn: loss distributions, risk curves, and charts fit for an executive deck.