Type Coercion and Parsing
Make every column the right type, once
CSV brings strings, models want numbers, humans want dates, and money wants cents. Coercion with errors='coerce', datetime parsing, and units resolution happen once, at the boundary.
What you will learn
- Convert strings to numbers safely
- Parse dates and handle timezones
- Resolve units before any arithmetic
What is this?
Type coercion transforms columns at the boundary: pd.to_numeric(col, errors='coerce') converts '1,200' or '120.5' to floats, flagging failures as NaN; to_datetime parses dates; astype enforces schema types. Unit and money handling happen here too — cents vs dollars, milliseconds vs seconds — because math on the wrong units is silently wrong.
Why do you need it?
- A numeric column still of dtype object breaks every sum and join.
- One bad parse ('1,200' vs 1200) corrupts totals that nobody re-checks.
- Date formats and timezones skew daily granularity reports.
- Money-as-float vs cents decision is the difference between right and 'looks right'.
Mental model
A customs officer converting currency at the border
Type coercion and parsing
The officer converts euros to dollars once, at the border, with an exchange rate and a receipt — nobody trades every street transaction in mixed currencies. Coercion is that exchange: convert every column at the boundary, record the rate (units), and let the rest of the analysis live in one currency.
How it works
- pd.to_numeric(series, errors='coerce') maps unparsable strings to NaN for later handling.
- pd.to_datetime(col, format=... or format=None) parses fixed and mixed date formats.
- .astype('int'|'float'|'category'|'string') enforces the target schema.
- Units: strip and convert ('1.2k'→1200, '$50'→50) with explicit multipliers.
- Timezone: store UTC at ingestion; convert to local only at display.
- Money: prefer integer cents for joins and sums where float drift matters.
The type boundary.
Examples
One boundary, all types
import pandas as pdraw = {"revenue": ["1,200", "450", "n/a"], "txn_date": ["2026-01-01", "01/02/2026", "2026-03-15"]}df = pd.DataFrame(raw)df["revenue"] = pd.to_numeric(df.revenue.str.replace(",", ""), errors="coerce")df["txn_date"] = pd.to_datetime(df.txn_date)print(df.dtypes)print(df)Commas stripped then coerced to float with NaN for 'n/a'; mixed date formats all became datetimes.
In production
- A billing source converts '$1,234.56' strings to integer cents for exact sums.
- An event pipeline parses ISO timestamps to UTC before any daily aggregation.
- A cleaning ETL reports a coercion summary ('3 rows failed, 2 were blanks') per batch.
Best practices
- Coerce once at the boundary; never re-parse in analysis.
- Use errors='coerce' plus a failure count — silent NaN hides the real problem.
- Store money as integer cents; convert to dollars at the report edge.
- Pin date format (ISO) in storage; format only for humans.
Common mistakes
- Ignoring commas and '$' so '1,200' becomes 1.
- astype('int') on a float with NaN — it raises; use to_numeric first.
- Adding dollars and cents columns without converting.
- Timezone-naive timestamps treated as local when they're UTC.
Debugging tips
- A column sums wrong → check it wasn't object dtype with thousands separators.
- Date drift in reports → audit the parse format and tz step.
- astype errors listing a value → that's your parser's confession; use errors='coerce' then log.
- Money mismatch at the penny → float drift; switch to integer cents.
Interview checkpoint
Hands-on lab
- Coerce currency strings to numeric
- Parse mixed date formats to datetime
- Resolve timezone and report failures
Type a Real Export at the Boundary
30 min · beginner
Quick quiz
errors='coerce' turns unparseable strings in to_numeric into:
ISO-8601 storage plus tz-at-display is a safe time convention.