Python for Actuarial Students · Course 2

Lesson 4

Aggregations, Grouping & Classical Actuarial Metrics

Split–apply–combine, reshaping, and the four numbers every pricing actuary lives by: frequency, severity, pure premium, and loss ratio.

Lesson 4 · OverviewBy the end of this lesson you can…

Explain split–apply–combine and use groupby with named aggregations
Reshape between wide and long with pivot_table and melt
Flatten multi-level column indexes
Compute frequency, severity, pure premium, and loss ratio correctly — as ratios of sums
Segment a portfolio across several rating dimensions at once
Recognize when a segment is too thin to trust

75 min

Concepts & worked examples

75 min

Lab: style × territory × age band

Companion notebook: lesson-04-aggregation-metrics.ipynb — worked examples and self-checking exercises.

Part 1

Split–Apply–Combine

The pattern behind every pivot table you’ve ever built.

groupbySplit, apply, combine

Splitrows into groups by a key: vehicle_style

→

Applya function to each group: sum, mean, count…

→

Combineresults into one table, indexed by the key

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       320

The group keys become the result’s index
Group by several keys with a list: groupby(["vehicle_style", "region"])
With category keys, pass observed=True to skip empty combinations

Every 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.

aggNamed aggregations

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.

transformGroup results back on every row

# 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 group
transform broadcasts: same shape as the input, so the result drops straight into a new column
Use it for shares, group means, and relativities without a merge

Instead of “groupby → agg → merge back”, reach for transform. One line, no join to validate.

Part 2

Reshaping

Wide ⇄ long, and taming multi-level columns.

pivot_tableTwo-way tables

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 → cells
Several values give MultiIndex columns: (measure, region)
Select a whole block with its first level: 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.

meltWide to long

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 → long
pivot / pivot_table: long → wide
stack / unstack: move a level between the index and the columns

Rule of thumb: store and compute long; present wide.

Part 3

Classical Pricing Metrics

Frequency, severity, pure premium, loss ratio.

MetricsThe four numbers

$$\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.

PitfallRatio of sums, not mean of ratios

# ❌ 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.2670

Averaging 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.

By coverageMetrics need the right exposure

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.

Actuarial Lab · 75 minMulti-dimensional risk segmentation

Load the Lesson 3 ABT; add age_band with pd.cut on youngest_driver_age (17–24, 25–34, 35–49, 50–64, 65–74, 75+)
Add region from longitude (cuts at −81.2 and −78.6)
Write metrics(df, by) returning exposure, claims, frequency, severity, pure premium, and loss ratio for any grouping
One-way tables: style, region, age band
Two-way pivot: style × region pure premium
Three-way: style × region × age band — then judge credibility

Age band answer key

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.

Lab · CredibilityWhen is a segment too small to trust?

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).

Tasks

Add a claims column to every table and highlight cells under 30
Rank the ten largest positive and negative loss-ratio deviations with at least 100 car years
Write three sentences for an underwriting manager: where are we underpriced?

RecapLesson 4 checklist

I use groupby + named agg for segment tables
I use transform to put group results on every row
I reshape with pivot_table and melt, and flatten MultiIndex columns
I compute every ratio as a ratio of sums

I use coverage-specific exposure for coverage metrics
Pure premium = frequency × severity, and I check it
I found the U-shaped age curve and the coastal comprehensive gap
I flag segments too thin to trust

Up next

Lesson 5

Exploratory Data Analysis & Statistical Visualization

matplotlib and seaborn: loss distributions, risk curves, and charts fit for an executive deck.

← Course home Next: Lesson 5 →