Series and DataFrames, label vs. position indexing, types and missing values — and a first pass at cleaning a genuinely messy claims extract.
DataFrame is built from Series sharing an index.loc[] and by position with .iloc[]NaN from pd.NA, and catch missing values that don’t look missing.str accessor and filter with .query()claims_raw.csvCompanion notebook: lesson-01-pandas-fundamentals.ipynb — worked examples and self-checking exercises.
cd ~/actuarial_work
uv init wrangling --package --python 3.12
cd wrangling
uv add pandas pyarrow matplotlib seaborn \
geopandas openpyxl
uv add --dev jupyterlab pytest ruff
mkdir notebooks testsCopy the course data pack into data/: synthetic policies and claims on real North Carolina geography (US Census, Natural Earth).
This course uses pandas 3. Older tutorials show warnings and behaviors that no longer apply — we’ll flag them as we go.
data/
├── raw/
│ └── claims_raw.csv messy — this lesson
├── motor/ clean schema — Lesson 2 on
│ ├── policies.csv 22,185 policy terms
│ ├── vehicles.csv
│ ├── policy_drivers.csv
│ ├── persons.csv
│ ├── coverages.csv
│ └── claims.csv
└── geo/
└── nc_geo.gpkg Lesson 6Series, DataFrames, and the index that ties them together.
import pandas as pd
paid = pd.Series([1200.0, 350.0, 8800.0],
index=["CLM1", "CLM2", "CLM3"],
name="paid")
paid
# CLM1 1200.0
# CLM2 350.0
# CLM3 8800.0
# Name: paid, dtype: float64
paid.index # Index(['CLM1', 'CLM2', 'CLM3'], dtype='str')
paid.values # array([1200., 350., 8800.])
paid.dtype # dtype('float64')The index is what makes pandas more than NumPy: operations line up by label, not position. Add two Series and matching claim IDs are added together.
raw = pd.read_csv("data/raw/claims_raw.csv")
raw.shape # (3369, 10)
raw.head(3)
raw.info() # dtypes, non-null counts, memory
raw.describe() # summary stats for numeric columns
raw.dtypes
# claim_id str
# policy_id str
# coverage str
# accident_date str
# status str
# paid str ← should be numbers!
# outstanding str ← so should thisSeries with its own dtype; all columns share the row indexstr is pandas 3’s text dtypeFirst thing to do with any file: check .dtypes. Money read as str means something in that column isn’t a clean number. Finding out what is today’s lab.
.loc[] by label, .iloc[] by position.
.loc[] vs. .iloc[]claims = clean.set_index("claim_id").sort_index()
# .loc — by LABEL: [row labels, column labels]
claims.loc["CLM000001", ["coverage", "paid", "status"]]
claims.loc["CLM000001":"CLM000003", ["coverage", "paid"]]
# coverage paid
# claim_id
# CLM000001 LIAB 1829.29
# CLM000002 COLL 2578.12
# CLM000003 LIAB 3507.10
# .iloc — by POSITION: [row numbers, column numbers]
claims.iloc[:3, [2, 7]] # same three rows
claims.iloc[0].name # 'CLM000001'.loc | .iloc | |
|---|---|---|
| Selects by | label | integer position |
| Slice end | included | excluded (like lists) |
| Accepts masks | yes | arrays only |
.loc slices include the end label — "CLM000001":"CLM000003" gives three rows. .iloc follows Python’s exclusive rule.
Prefer .loc with meaningful labels. Positions change when data is sorted or filtered; claim IDs don’t.
open_claims = claims[claims["status"] == "Open"]
# rows AND columns in one .loc call
claims.loc[claims["paid"] > 50_000, ["coverage", "paid"]]
# assign through .loc
claims.loc[claims["cause"].isna(), "cause"] = "Unknown"
# combine conditions: & | ~ (parentheses required)
large_coll = claims[(claims["coverage"] == "COLL")
& (claims["paid"] > 20_000)]True rows.loc[rows, cols] = …pandas 3: Copy-on-Write. A filtered frame like open_claims behaves as an independent copy — changing it never changes claims. The old SettingWithCopyWarning you’ll see in tutorials is gone, and chained assignment like df[mask]["col"] = x simply doesn’t work.
Vectorized assignment, conversions, and the many faces of “missing”.
# New columns: whole-column arithmetic, no loops
claims["incurred"] = claims["paid"] + claims["outstanding"]
claims["large"] = claims["incurred"] > 25_000
# Converting types
raw["paid"].astype(float)
# ValueError: could not convert string to float: '$3,386.85'
pd.to_numeric(raw["paid"].str.replace(r"[$,]", "", regex=True))
pd.to_datetime(raw["accident_date"])
raw["coverage"].astype("category")| Tool | Use for |
|---|---|
pd.to_numeric | text → numbers; errors="coerce" turns failures into missing |
pd.to_datetime | text → timestamps (Lesson 2) |
.astype("category") | few distinct labels, many rows |
.astype("Int64") | integers that may be missing |
category stores each label once plus small integer codes. The coverage column shrinks from 178 KB to 4 KB — and typos can’t sneak in as new values.
NaN, None, and pd.NAimport numpy as np
np.nan == np.nan # False (!)
pd.isna(np.nan) # True — always test this way
pd.Series([1, None, 3]).dtype # float64 — ints became floats
pd.Series([1, None, 3], dtype="Int64") # [1, <NA>, 3]
pd.Series(["a", None], dtype="string") # ['a', <NA>]
df.isna().sum() # missing count per column
df["cause"].fillna("Unknown")
df.dropna(subset=["accident_date"])| Marker | Where you’ll see it |
|---|---|
NaN | float columns (a NumPy float value) |
None | Python object columns |
pd.NA | “nullable” dtypes: Int64, string, boolean |
Nullable dtypes keep integers as integers even with gaps — useful for counts and IDs.
In claims_raw.csv, the outstanding column hides four spellings of “nothing”:
| Token | Rows | Default read_csv |
|---|---|---|
null | 34 | → NaN |
| (empty) | 28 | → NaN |
N/A | 27 | → NaN |
- | 28 | stays as text! |
raw = pd.read_csv("data/raw/claims_raw.csv")
raw["outstanding"].dtype # str ← 28 dashes did this
raw["outstanding"].isna().sum() # 89
raw = pd.read_csv("data/raw/claims_raw.csv",
na_values=["-"])
raw["outstanding"].dtype # float64
raw["outstanding"].isna().sum() # 117Twenty-eight dashes turned an entire money column into text. Always look at the value_counts() of anything unexpected before deciding how to read a file.
Clean text in bulk; filter readably.
.str accessorraw["coverage"].value_counts()
# COLL 1024 · LIAB 944 · COMP 453 · COLL␣ 112 · Coll 102
# collision 102 · Third Party 98 · LIAB␣ 95 · liability 94
# Liability 77 · comprehensive 46 · … (15 spellings)
key = raw["coverage"].str.strip().str.upper()
sorted(key.unique())
# ['COLL', 'COLLISION', 'COMP', 'COMPREHENSIVE',
# 'LIAB', 'LIABILITY', 'THIRD PARTY']
cov_map = {"LIAB": "LIAB", "LIABILITY": "LIAB", "THIRD PARTY": "LIAB",
"COLL": "COLL", "COLLISION": "COLL",
"COMP": "COMP", "COMPREHENSIVE": "COMP"}
raw["coverage"] = key.map(cov_map) # unmapped → NaN.str applies string methods to every value at once: strip, upper, title, replace, contains, startswith, split.map(dict) leaves anything not in the dict as missing — so an isna() check afterwards catches spellings you didn’t anticipateMissing values pass through .str methods as missing. Use na=False in str.contains when you need a clean boolean mask.
.query()clean.query("status == 'Open'") # 411 rows
clean.query("coverage == 'COMP' and paid > 20_000") # 4 rows
clean.query("status == 'Open' and outstanding > 50_000") # 4 rows
threshold = 25_000
clean.query("incurred > @threshold") # 163 rows
# the same filter, written as a mask:
clean[(clean["status"] == "Open") & (clean["outstanding"] > 50_000)]and / or / not instead of & / | / ~; no extra parentheses@name refers to a Python variableColumn names with spaces need backticks inside a query: `paid amount` > 0. Better still: rename columns to snake_case on the way in.
claims_raw.csvna_values; check dtypesvalue_counts on every text column$ and commas → float64coverage and status → category| Rows read | 3,369 |
paid values with $ | 464 |
| Coverage spellings → codes | 15 → 3 |
| Status spellings → values | 8 → 2 |
| Exact duplicates | 15 |
| Invalid records | 39 |
| Clean rows | 3,315 |
policies = pd.read_csv("data/motor/policies.csv",
usecols=["policy_id"])
checks = pd.DataFrame({
"negative_paid": df["paid"] < 0,
"missing_accident_date": df["accident_date"].isna(),
"reported_before_accident":
df["report_date"] < df["accident_date"],
"unknown_policy":
~df["policy_id"].isin(policies["policy_id"]),
})
checks.sum()
# negative_paid 12
# missing_accident_date 8
# reported_before_accident 11
# unknown_policy 8
bad = checks.any(axis=1) # 39 rows
clean = df[~bad]
rejects = df[bad].assign(reason=checks[bad].idxmax(axis=1))checks.sum() counts failures per rule; any(axis=1) flags rows failing any ruleNever silently drop data. A validation log — rule, count, examples — belongs in every data-preparation step of a reserving or pricing exercise.
Stretch: write clean_claims(raw: pd.DataFrame) -> pd.DataFrame in src/wrangling/claims.py, plus a test that it returns 3,315 rows.
.loc or .iloc deliberately — and know their slice rules.loc, and understand Copy-on-Writeto_numeric, to_datetime, astypeisna(), never ==.str + .mapDates, leap years, and mid-term cancellations — computing earned car years exactly.