Parsing dates safely, doing arithmetic with them, and turning 22,185 policy terms into exact earned exposure.
pd.to_datetime().dt properties and Timedelta / DateOffset arithmeticCompanion notebook: lesson-02-dates-exposure.ipynb — worked examples and self-checking exercises.
From text to timestamps, safely.
pd.to_datetime() — and its trapspolicies = pd.read_csv("data/motor/policies.csv",
parse_dates=["inception_date", "expiry_date",
"cancellation_date"])
policies["inception_date"].dtype # datetime64[us]
pd.to_datetime("2024-03-04") # 4 March
pd.to_datetime("03/04/2024") # 4 March (US)
pd.to_datetime("03/04/2024", dayfirst=True) # 3 April (UK)
pd.to_datetime(["2024-03-04", "04/03/2024"])
# ValueError: time data "04/03/2024" doesn't match
# format "%Y-%m-%d"
pd.to_datetime(["2024-02-30", "2024-03-01"],
format="%Y-%m-%d", errors="coerce")
# [NaT, Timestamp('2024-03-01')] NaT = "not a time"parse_dates=, or convert laterformat="%Y-%m-%d" makes the rule explicit and fasterrors="coerce" turns impossible dates into NaT — then count them03/04/2024 is 4 March in the US and 3 April in the UK. Ambiguous formats are silent errors waiting to happen. Insist on ISO dates (YYYY-MM-DD) in every extract you control.
.dt accessord = pd.to_datetime(pd.Series(["2024-02-29", "2023-12-31"]))
d.dt.year # [2024, 2023]
d.dt.month # [2, 12]
d.dt.day_name() # ['Thursday', 'Sunday']
d.dt.dayofyear # [60, 365]
d.dt.is_leap_year # [True, False]
d.dt.days_in_month # [29, 31]
claims["accident_year"] = claims["accident_date"].dt.year
claims["report_lag"] = (claims["report_date"]
- claims["accident_date"]).dt.daysJust as .str works on text, .dt works on every timestamp in a column at once.
| Coverage | Median report lag |
|---|---|
| COLL | 8 days |
| COMP | 8 days |
| LIAB | 19 days |
Liability claims are reported more slowly — third parties take time to come forward. Report lags drive IBNR, and you just measured them in one line.
Timedelta vs. DateOffsetstart = pd.Timestamp("2024-01-15")
start + pd.Timedelta(days=365) # 2025-01-14 ← a day short!
start + pd.DateOffset(years=1) # 2025-01-15 ← calendar-aware
pd.Timestamp("2024-02-29") + pd.DateOffset(years=1)
# 2025-02-28 (no 29 Feb in 2025)
gap = pd.Timestamp("2025-01-14") - start
gap # Timedelta('365 days')
gap.days # 365
gap / pd.Timedelta(days=365.25) # 0.999315...| Measures | Use for | |
|---|---|---|
Timedelta | an exact duration | “30 days later”, differences between dates |
DateOffset | calendar steps | “same date next year”, month-ends |
A policy year is not 365 days. Terms that include 29 February run 366. Build expiry dates with DateOffset(years=1), never Timedelta(days=365).
In our data: 16,728 terms of 365 days, 5,457 of 366.
The denominator of every frequency and every rate.
Earned exposure is the time each vehicle was actually insured, measured in years, up to the evaluation date.
EVAL = pd.Timestamp("2025-12-31")
p = policies
p["cover_end"] = p[["expiry_date",
"cancellation_date"]].min(axis=1)
p["earned_end"] = p["cover_end"].clip(upper=EVAL)min(axis=1) skips missing values, so an uncanceled policy simply uses its expiry date. Convention: cover runs through the cancellation date; start and end dates both count.
In this lesson we measure policy years. Most policies insure one car, but 1,808 insure two — joining to vehicles in the next lesson turns 18,579.58 policy years into 20,100.95 car years.
def calendar_exposure(start, end, year):
"""Years of exposure earned in `year` by cover from start to end
(inclusive). Each day weighs 1/365, or 1/366 in a leap year."""
y0 = pd.Timestamp(f"{year}-01-01")
y1 = pd.Timestamp(f"{year}-12-31")
days = (end.clip(upper=y1) - start.clip(lower=y0)).dt.days + 1
return days.clip(lower=0) / (366 if y1.is_leap_year else 365)
cy = pd.DataFrame({
y: calendar_exposure(p["inception_date"], p["earned_end"], y)
for y in range(2022, 2026)
})
p["exposure"] = cy.sum(axis=1).clip(lower=0) handles policies that don’t touch the year at allKeep the per-year table cy: it’s your calendar-year exposure, ready for frequencies by year.
| Policy | Inception | Expiry | Canceled | 2023 | 2024 | 2025 | Total |
|---|---|---|---|---|---|---|---|
| POL000347 — renewal spanning 29 Feb | 2023-07-06 | 2024-07-05 | — | 179/365 = 0.490411 | 187/366 = 0.510929 | — | 1.001340 |
| POL000016 — canceled | 2023-11-04 | 2024-11-03 | 2024-07-30 | 58/365 = 0.1589 | 212/366 = 0.5792 | — | 0.7381 |
| POL000004 — in force at evaluation | 2025-09-18 | 2026-09-17 | — | — | — | 105/365 = 0.2877 | 0.2877 |
POL000347’s term has 366 days, but only 187 fall in the leap year — so it earns slightly more than 1.0. Exact methods can disagree at the fourth decimal; what matters is choosing one and using it consistently.
POL000004 still has 260 unearned days at 31 December 2025. Its premium for those days is part of the unearned premium reserve.
| Method | Total policy years | vs. exact |
|---|---|---|
| Calendar split (1/365 or 1/366 per day) | 18,579.58 | — |
| Fraction of term (days ÷ term days) | 18,579.95 | +0.002% |
| Days ÷ 365.25 | 18,581.67 | +0.011% |
| Days ÷ 365 | 18,594.40 | +0.080% |
For the whole portfolio the methods agree to within 0.1%. So why fuss?
Three ways to slice the same book of business.
| Basis | Groups by | Answers | Pairs with |
|---|---|---|---|
| Calendar year (CY) | when exposure was earned | What happened during 2024? | accident-year claims |
| Policy year (PY) | when the policy incepted | How did business written in 2024 perform? | claims on those policies |
| Accident year (AY) | when the loss occurred | How many 2024 accidents? | CY exposure |
| Year | CY exposure | PY exposure | AY claims | AY freq. |
|---|---|---|---|---|
| 2022 | 2,440.52 | 4,842.10 | 469 | 0.1922 |
| 2023 | 5,039.23 | 5,251.15 | 930 | 0.1846 |
| 2024 | 5,410.65 | 5,583.13 | 960 | 0.1774 |
| 2025 | 5,689.17 | 2,903.20 | 995 | 0.1749 |
Policy year 2025 looks small because most of its policies are still earning at evaluation. Calendar year 2022 looks small because the book only started that year. Same data — the “year” you choose changes the story.
term_days = (p["expiry_date"] - p["inception_date"]).dt.days + 1
year_days = pd.Series({y: 366 if pd.Timestamp(f"{y}-01-01").is_leap_year
else 365 for y in cy.columns})
# CY exposure × days-in-year → covered days in that year;
# ÷ term days → share of the term; × written premium
ep = (cy.mul(year_days, axis=1)
.div(term_days, axis=0)
.mul(p["written_premium"], axis=0))
ep.sum().round(0)
# 2022 4,418,313
# 2023 9,068,643
# 2024 9,614,807
# 2025 9,984,868.mul(…, axis=0) multiplies each row by that policy’s value; axis=1 each column by that year’s valueEarned premium by calendar year is the denominator of the loss ratio — Lesson 4.
policies.csv with parsed datescover_end, earned_end, and a status column (Expired / Canceled / In force) with np.selectcy and check it sums to 18,579.58| Exposure: expired | 15,129.62 |
| Exposure: canceled | 708.45 |
| Exposure: in force | 2,741.50 |
| Total | 18,579.58 |
| Renewal terms | 12,985 |
| New-business terms | 9,200 |
Renewals: renewal_of is not missing.
Show that the four CY columns sum to the per-policy exposure for every row — then that the PY view sums to the same total.
Follow renewal_of links to find each customer’s tenure in years. What share of 2022 new business is still in force?
Move calendar_exposure into src/wrangling/exposure.py, with a test: a policy covering all of 2024 earns exactly 1.0.
Common bug: forgetting the + 1 when counting inclusive days. Every term then earns 364/365 — a 0.3% understatement that nobody notices until the reconciliation fails.
NaTs.dt for properties and date differencesDateOffset for calendar steps, Timedelta for durationsmin and clipKeys, cardinality, and merges — assembling six tables into one analytical base table.