Before this: Joins where missing keys never match
The idea
pandas has sensible defaults for exploration that are wrong for cleaning contracts. merge matches missing keys to each other; .str.strip() without arguments removes every Unicode whitespace character; read_csv turns the text "NA" into a missing value; and arithmetic on money in float64 introduces rounding error.
Override each one on purpose: drop missing keys from the lookup side before merging, pass the exact characters to strip, read with keep_default_na=False, and use decimal.Decimal (or integer cents) for money.
Use the nullable dtypes (string, Int64, boolean) so missing values stay missing instead of turning integers into floats, and use merge(..., validate="many_to_one") to fail loudly if a lookup table has duplicate keys.
Worked example
Input: orders.customer_key has <NA>; customers has one row with <NA> key and segment "VIP"
- Visual
Look up segment: missing keys never match in the visual engine.
- SQL (DuckDB)
LEFT JOIN customers c ON o.customer_key = c.customer_key -- NULL = NULL is not true in SQL
- pandas
lookup = customers[customers["customer_key"].notna()] df.merge(lookup, on="customer_key", how="left", validate="many_to_one")
Result: Orders with a missing key get a missing segment, not "VIP".
Common mistakes
- Merging with missing keys on both sides.
- Using float64 for money.
- Default read_csv NA handling on text data.
Check yourself
Does pandas merge match <NA> to <NA>?
Yes, which is why missing keys must be removed from the lookup side first.
What does validate="many_to_one" protect against?
Duplicate keys in the lookup table, which would multiply rows and totals.
Practice it
Solve the join in the Python track without letting a missing key match the customer row whose key is missing.