Keys, cardinality, and merges — and the discipline that stops a join from quietly inventing 9.6 million of losses.
inner, left, right, and outer mergesCompanion notebook: lesson-03-relational-joins.ipynb — worked examples and self-checking exercises.
Why the data arrives in six tables, and how they connect.
| 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 |
policy_id, vehicle_id, coverageclaim_id) or a combination (vehicle_id + coverage)claims.policy_id → policies.policy_idpolicy_drivers is a link table: it turns the many-to-many relationship between policies and people into two one-to-many onesNormalization: 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 7618Run these checks before joining. Keys you assume but haven’t tested are the root of most join bugs.
Four kinds of join, and what each keeps.
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 1how= | 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 |
inner and left silently drop itindicator=True adds _merge, telling you where each row came from| 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.
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 mergevalidate= 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 rowsThree habits — validate=, indicator=, and a control total — catch essentially every join bug before it reaches a report.
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"]]on=, get _x / _y suffixesTreat any _x/_y column as a question: “should this have been part of the key?”
One row per unit of exposure; everything you need, on that row.
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).
# 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 topicpivot_table spreads coverages across columns: n_coll, inc_coll, …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.
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.
exposure on policiesvehicles with validate= on every stepdata/processed/abt.parquetvehicle: 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.
Merge claims straight onto policy_drivers. Reproduce the 34.3 million, then explain it to a colleague in two sentences.
Merge coverages on policy_id only. Which control total catches it first?
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.
how= deliberately and inspect _mergevalidate=_x/_y columns as a warning signSplit–apply–combine: frequency, severity, pure premium, and loss ratios by segment.