In pandas, a scraped price column holds strings like '$1,200' and 'N/A' — why does astype(float) raise, and how do you convert it?
answer
- strict cast versus forgiving parse
- one bad value stops the cast
- coerce failures to NaN
- strip symbols before coercing
- count the NaN you created
basics
~10 sastype(float) is all-or-nothing: one value that does not parse, such as '$1,200' or 'N/A', raises ValueError. Strip the symbols with .str.replace, call pd.to_numeric(errors='coerce') so leftovers become NaN, then count those NaN.
solid answer
~30 s`astype(float)` converts every value or fails: the first string that is not a plain number, here `'$1,200'`, raises `ValueError` and nothing is converted. `pd.to_numeric` parses the same way but takes `errors='coerce'`, which sets every unparseable entry to `NaN` instead of raising. Coercing the raw column is the trap: `'$1,200'` is a real price and would become `NaN` right beside `'N/A'`. So clean first with `s.str.replace(r'[$,]', '', regex=True)`, then coerce, then compare the missing count before and after so you know how many values were discarded. The result is `float64`, because a NumPy integer column cannot hold `NaN`.
code
python · 15 linesimport pandas as pd
raw = pd.Series(["$1,200", "N/A", "$85", " 40 "])
try:
raw.astype(float)
except ValueError as exc:
print("astype:", exc)
print(pd.to_numeric(raw, errors="coerce").tolist())
cleaned = raw.str.replace(r"[$,]", "", regex=True)
prices = pd.to_numeric(cleaned, errors="coerce")
print(prices.tolist(), prices.dtype)
print("discarded:", raw[prices.isna() & cleaned.notna()].tolist())go deeper
Recall that astype is strict and raises on the first bad value, while pd.to_numeric with errors='coerce' turns unparseable values into NaN.
Explain why currency symbols and thousands separators must be stripped before coercion, and why the result is float64 rather than int64.
Show the check: compare missing counts before and after coercion and list the raw values that were discarded, so a cleaning rule cannot silently erase real data.
Argue for a team rule that every coercion logs or asserts how many values it discarded, because silent NaN creation corrupts downstream aggregates without failing any job.
## Why `astype` refuses the column `Series.astype(dtype)` asks pandas to convert **every** element to the target dtype. It is a strict cast: if any element cannot be converted, the call raises and returns nothing. A scraped price column usually holds text such as `'$1,200'`, `'85'`, `' 40 '` and `'N/A'`. The first element, `'$1,200'`, is not a number that Python's float parser understands, so `s.astype(float)` raises `ValueError: could not convert string to float: '$1,200'`. That strictness is a feature. `astype` is the right tool when you **know** the data is clean and want a loud failure if it is not. It is the wrong tool for a column you already know is dirty. ## `pd.to_numeric` and its `errors` argument `pd.to_numeric(arg, errors='raise', downcast=None, dtype_backend=...)` is the parsing function for one-dimensional data: a scalar, a list, an array or a Series. Its `errors` argument decides what happens to a value that will not parse: | `errors=` | What an unparseable value does | When to use it | |---|---|---| | `'raise'` (default) | raises `ValueError` naming the value and its position | the data should be clean; a failure is a bug | | `'coerce'` | becomes `NaN` | the column is known to hold junk you are willing to treat as missing | Two details of the parser matter for prices: - It accepts surrounding spaces, so `' 40 '` parses as `40`. - It does **not** understand currency symbols or thousands separators, so `'$1,200'` and `'1,200'` both fail. That second point is why coercion alone is not the fix. ## Clean, then coerce The reliable sequence is: 1. **Strip the formatting** that makes valid numbers unparseable: `s.str.replace(r'[$,]', '', regex=True)`. After this `'$1,200'` is `'1200'`. 2. **Coerce what is left** with `pd.to_numeric(cleaned, errors='coerce')`. Placeholders such as `'N/A'`, `'-'` or `'call'` become `NaN`. 3. **Measure the loss**: compare `cleaned.isna().sum()` before with `result.isna().sum()` after, and look at the raw values that turned into `NaN`. 4. **Decide**: if the newly missing values are all placeholders, keep the result; if some are real prices in a format you did not anticipate (a `'€'` sign, a trailing `'k'`), extend step 1 and run again. Step 3 is what separates a careful answer from a careless one. `errors='coerce'` never tells you what it threw away; a typo in step 1 can silently turn half a column into `NaN`. ```python import pandas as pd raw = pd.Series(["$1,200", "N/A", "$85", " 40 "]) cleaned = raw.str.replace(r"[$,]", "", regex=True) prices = pd.to_numeric(cleaned, errors="coerce") lost = raw[prices.isna() & cleaned.notna()] ``` `lost` holds exactly the raw strings that coercion discarded, here only `'N/A'`. ## What the result looks like - When at least one value was coerced, the result is **`float64`**: `NaN` is a floating-point value, and a NumPy `int64` column has no slot for it. Whole-number prices therefore print as `1200.0`. - When nothing failed and every value is whole, `to_numeric` returns **`int64`**; the docstring says the default result is `float64` or `int64` "depending on the data supplied". - `downcast='integer'`, `'signed'`, `'unsigned'` or `'float'` shrinks a successfully parsed result to the smallest dtype of that family that holds every value. A coerced column that still contains `NaN` cannot become an integer, so `downcast='integer'` leaves it `float64`. - To keep whole prices as integers despite the gaps, cast the result with `astype('Int64')` or pass `dtype_backend='numpy_nullable'` to `to_numeric`; how that missing marker behaves is covered in the lesson on NA-aware dtypes. ## Choosing between the two tools - Use **`astype`** when the column must already be clean, for example one your own code produced; a failure then signals an upstream bug and should stop the job. - Use **`pd.to_numeric(errors='coerce')`** at the edge of the system, where data arrives from scraping, spreadsheets or user input, and always pair it with the discard check. - Use **`pd.to_numeric`** with its default `errors='raise'` when you want its parser but a loud failure: its message names the offending value and its position (`Unable to parse string "$1,200" at position 0`), which `astype`'s message does not. Make the choice per column. A loop that runs the same conversion over every column of a frame treats product names, identifiers and prices alike, and that is where silent damage starts. ## Mistakes interviewers listen for - Calling `astype(float)` and wrapping it in a bare `try/except` that swallows the error, leaving the column as text. - Coercing the raw column and reporting a mean that silently excludes every price written with a currency sign or a thousands separator. - Assuming `to_numeric` reads locale formats; it parses plain numerals (sign, decimal point, exponent), not currency or grouping characters. - Forgetting that `pd.to_numeric` takes one dimension: `pd.to_numeric(df)` raises `TypeError`, so a frame is converted column by column, ideally only on the columns you named. A good interview answer names both tools, says which one fails loudly and which one forgives, and then shows the check that makes forgiveness safe.
- What does to_numeric's downcast argument do, and when does it have no effect?After a successful parse, `downcast='integer'` or `'signed'` picks the smallest signed integer dtype that holds every value (from `int8` up), `'unsigned'` starts at `uint8`, and `'float'` starts at `float32`. It only shrinks; if no smaller dtype fits, the result is unchanged. A column that still holds `NaN` cannot become an integer, so `downcast='integer'` on a coerced column with gaps stays `float64`.
- Why not simply run pd.to_numeric(s, errors='coerce') on the raw column?Because coercion cannot tell junk from formatting. `'$1,200'` is a valid price written in a form the parser does not read, and it becomes `NaN` exactly like `'N/A'`. The column then looks numeric and every statistic on it is quietly wrong. Clean the formatting first, and compare missing counts before and after so the discarded values are visible.
- How do you keep whole-number prices as integers when some are missing?Cast the coerced result with `astype('Int64')`, a nullable integer dtype, or call `pd.to_numeric(cleaned, errors='coerce', dtype_backend='numpy_nullable')`, which returns `Int64` when every parsed value is whole. Plain `astype(int)` raises on the missing entries because NumPy integers cannot represent them.
- astype(float) skips the values it cannot parse and converts the rest
- pd.to_numeric understands currency symbols and thousands separators
- errors='coerce' is safe on the raw column without cleaning first
- A coerced column containing NaN can still be int64 if the other values are whole
- Coercion needs no follow-up check because only junk turns into NaN