Python for Actuarial Students · Course 2

Lesson 3

Relational Data Modeling & Multi-Table Joins

Keys, cardinality, and merges — and the discipline that stops a join from quietly inventing 9.6 million of losses.

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

Identify primary and foreign keys, and check they hold
Explain why operational data is normalized across tables
Choose between inner, left, right, and outer merges
Predict row counts from cardinality: one-to-one, one-to-many, many-to-many
Resolve column collisions and validate every merge
Build an analytical base table from six source tables

75 min

Concepts & worked examples

90 min

Lab: the motor ABT

Companion notebook: lesson-03-relational-joins.ipynb — worked examples and self-checking exercises.

Part 1

Relational Concepts

Why the data arrives in six tables, and how they connect.

SchemaThe motor schema

Table	Rows	Primary key
`policies`	22,185	`policy_id`
`vehicles`	23,993	`vehicle_id`
`persons`	12,351	`person_id`
`policy_drivers`	29,803	(`policy_id`, `person_id`)
`coverages`	63,817	(`vehicle_id`, `coverage`)
`claims`	3,354	`claim_id`

persons

1 : n

policy_driverslink table

n : 1

policies

1 : n

vehicles

1 : n

coverages

vehicles

1 : n

claimspolicy_id, vehicle_id, coverage

KeysPrimary keys and foreign keys

A primary key identifies each row uniquely — one value (claim_id) or a combination (vehicle_id + coverage)
A foreign key points at another table’s primary key: claims.policy_id → policies.policy_id
policy_drivers is a link table: it turns the many-to-many relationship between policies and people into two one-to-many ones

Normalization: each fact is stored once, in the table it belongs to. A driver’s date of birth lives in persons — not copied onto every policy they’ve ever been on.

# Check a primary key really is unique
policies["policy_id"].duplicated().sum()          # 0
coverages.duplicated(["vehicle_id", "coverage"]).sum()   # 0

# Check a foreign key points at something real
(~claims["policy_id"].isin(policies["policy_id"])).sum()  # 0

# How many vehicles per policy? drivers per policy?
vehicles.groupby("policy_id").size().value_counts()
# 1    20377
# 2     1808
policy_drivers.groupby("policy_id").size().value_counts()
# 1    14567
# 2     7618

Run these checks before joining. Keys you assume but haven’t tested are the root of most join bugs.

Part 2

The Mechanics of Merging

Four kinds of join, and what each keeps.

Merge typesinner, left, right, outer

L = pd.DataFrame({"policy_id": ["P1", "P2", "P3"],
                  "channel": ["Agent", "Direct", "Agent"]})
R = pd.DataFrame({"policy_id": ["P1", "P1", "P3", "P9"],
                  "paid": [500, 1200, 300, 800]})

L.merge(R, on="policy_id", how="left")
# policy_id channel    paid
#        P1   Agent   500.0
#        P1   Agent  1200.0
#        P2  Direct     NaN   ← kept, no match
#        P3   Agent   300.0

m = L.merge(R, on="policy_id", how="outer", indicator=True)
m["_merge"].value_counts()
# both 3 · left_only 1 · right_only 1

`how=`	Keeps	Rows here
`inner`	only keys in both	3
`left`	every row of L	4
`right`	every row of R	4
`outer`	every row of both	5

P1 appears twice in R, so it appears twice in every result
P9 is an orphan claim — no matching policy. inner and left silently drop it
indicator=True adds _merge, telling you where each row came from

CardinalityPredict the row count before you merge

Merge	Relationship	Rows in → out	Why
claims ⟶ policies	many-to-one	3,354 → 3,354	each claim finds exactly one policy
policies ⟶ claims (left)	one-to-many	22,185 → 22,535	policies with 2+ claims repeat
vehicles ⟶ coverages on `policy_id` only	many-to-many	23,993 → 73,423	every car paired with every cover on the policy
vehicles ⟶ coverages on both keys	one-to-many	23,993 → 63,817	each cover joins its own car
claims ⟶ policy_drivers	many-to-many	3,354 → 4,613	a claim repeats once per listed driver

That last join makes total incurred jump from 24.7 million to 34.3 million. Nothing errors, nothing warns — the numbers are just wrong. Joining at the wrong grain is the most expensive bug in actuarial data work.

ValidationMake pandas check your assumptions

abt = claims.merge(policies, on="policy_id", how="left",
                   validate="many_to_one", indicator=True)
abt["_merge"].value_counts()
# both 3354 · left_only 0 · right_only 0

policies.merge(claims, on="policy_id", validate="one_to_one")
# MergeError: Merge keys are not unique in right dataset;
#             not a one-to-one merge

validate= raises MergeError if the keys don’t have the cardinality you claimed: "one_to_one", "one_to_many", "many_to_one"
indicator=True + value_counts() shows unmatched rows
After merging, check a control total: row count, claim count, incurred, exposure

Three habits — validate=, indicator=, and a control total — catch essentially every join bug before it reaches a report.

CollisionsSame column name, two tables

x = vehicles.merge(coverages, on="policy_id")
[c for c in x.columns if c.endswith(("_x", "_y"))]
# ['vehicle_id_x', 'vehicle_id_y']     ← and 73,423 rows

# Fix 1: join on ALL the shared keys
vehicles.merge(coverages, on=["policy_id", "vehicle_id"])

# Fix 2: name the suffixes meaningfully
a.merge(b, on="policy_id", suffixes=("_policy", "_claim"))

# Fix 3: keep only the columns you need
b[["policy_id", "channel"]]

Columns present in both inputs, but not in on=, get _x / _y suffixes
Unexpected suffixes are a symptom: usually a key you forgot to join on
Select columns before merging — narrower tables, fewer collisions, faster joins

Treat any _x/_y column as a question: “should this have been part of the key?”

Part 3

Building an Analytical Base Table

One row per unit of exposure; everything you need, on that row.

ABTChoose the grain first

An ABT is a single wide table for analysis and modeling. Decide its grain — what one row represents — before writing any merge.

For motor pricing: one row per vehicle per policy term (23,993 rows).

Many-to-one parents (policy) → merge straight on
One-to-many children (coverages, claims, drivers) → aggregate to the grain first, then merge

vehiclesthe grain · 23,993

+ policies (many-to-one)

drivers → per policycount, youngest age, years licensed

coverages → per vehiclepremium by cover, has COLL / COMP

claims → per vehiclecount & incurred by cover

ABTAggregate children to the grain

# Drivers → one row per policy
d = (policy_drivers
     .merge(persons, on="person_id", validate="many_to_one")
     .merge(policies[["policy_id", "inception_date"]],
            on="policy_id", validate="many_to_one"))
d["age"] = (d["inception_date"] - d["date_of_birth"]).dt.days / 365.25
drivers = d.groupby("policy_id").agg(
    n_drivers=("person_id", "size"),
    youngest_driver_age=("age", "min"))

# Claims → one row per vehicle, a column per coverage
claims["incurred"] = claims["paid"] + claims["outstanding"]
clm = claims.pivot_table(index="vehicle_id", columns="coverage",
                         values="incurred",
                         aggfunc=["count", "sum"], fill_value=0)
clm.columns = [f"{'n' if a == 'count' else 'inc'}_{c.lower()}"
               for a, c in clm.columns]

groupby().agg(new_name=(column, function)) — named aggregation, the next lesson’s main topic
pivot_table spreads coverages across columns: n_coll, inc_coll, …
The pivot gives two-level column names; the comprehension flattens them

Rating on the youngest listed driver is a common, conservative convention: a policy is as risky as its riskiest driver. It’s an assumption — document it.

ABTAssemble and reconcile

abt = (vehicles
  .merge(policies[["policy_id", "inception_date", "channel",
                   "latitude", "longitude", "exposure"]],
         on="policy_id", how="left", validate="many_to_one")
  .merge(drivers, on="policy_id", how="left", validate="many_to_one")
  .merge(cov_wide, on="vehicle_id", how="left", validate="one_to_one")
  .merge(clm, on="vehicle_id", how="left", validate="one_to_one"))

claim_cols = [c for c in abt.columns if c.startswith(("n_", "inc_"))]
abt[claim_cols] = abt[claim_cols].fillna(0)
abt.shape          # (23993, 27)

Control total	Source	ABT
Rows	23,993 vehicles	23,993 ✓
Claims	3,354	3,354 ✓
Incurred	24,669,861.81	24,669,861.81 ✓
Exposure	18,579.58 policy yrs	20,100.95 car yrs

Exposure should grow: each vehicle on a two-car policy carries the policy’s full exposure. That’s the switch from policy years to car years.

left merges from the grain table, and fillna(0) for claim columns: a vehicle with no claims has zero claims, not unknown claims.

Actuarial Lab · 90 minBuild the motor ABT

Load all six tables; test every primary key and every foreign key
Recompute Lesson 2’s calendar-split exposure on policies
Aggregate drivers, coverages, and claims to their target grain
Merge onto vehicles with validate= on every step
Reconcile the four control totals
Save as data/processed/abt.parquet

Target columns (27)

vehicle: vehicle_style, model_year, vehicle_value, … · policy: channel, latitude, longitude, exposure · drivers: n_drivers, youngest_driver_age, min_years_licensed · coverages: prem_liab, prem_coll, prem_comp, has_coll, has_comp · claims: n_liab, n_coll, n_comp, inc_liab, inc_coll, inc_comp

Parquet keeps dtypes (dates stay dates, categories stay categories) and loads far faster than CSV. It needs pyarrow, which you installed in Lesson 1.

Lab · StretchDeliberately break it

Wrong grain

Merge claims straight onto policy_drivers. Reproduce the 34.3 million, then explain it to a colleague in two sentences.

Missing key

Merge coverages on policy_id only. Which control total catches it first?

Package it

Write build_abt(tables: dict) -> pd.DataFrame in src/wrangling/abt.py, with a test asserting the four control totals.

Keep the ABT build as code, not as a saved file you edit by hand. Next quarter’s extract should rebuild it with one command — that’s the Lesson 8 pipeline.

RecapLesson 3 checklist

I test primary and foreign keys before joining
I can explain normalization and link tables
I pick how= deliberately and inspect _merge
I predict row counts from cardinality

Every merge I write has validate=
I treat _x/_y columns as a warning sign
I aggregate one-to-many tables to the grain before merging
My ABT reconciles: 23,993 rows, 3,354 claims, 24,669,861.81 incurred

Up next

Lesson 4

Aggregations, Grouping & Classical Actuarial Metrics

Split–apply–combine: frequency, severity, pure premium, and loss ratios by segment.

← Course home Next: Lesson 4 →