Your Prices Parse as NaN: Fix Number Extraction
Scraped prices like '$1,299.00' become NaN after type conversion. Trace the parsing failure, normalize messy number formats, and fix it before load.
On this page
Why ‘$1,299.00’ Becomes NaN: The Silent Type Conversion Failure
The weekly digest said the average price in the “Laptops” category on store.example.com dropped 18% overnight. No sale was running. Nobody touched the scraper. When you dig into the warehouse table, the price column for 220 of 1,000 rows reads NaN — and pandas mean() skips NaN by default, so the surviving 780 rows (skewed toward cheap accessories) dragged the “average” down. The data corrupted itself three days before anyone noticed.
Here is the root cause, in two lines:
>>> float("$1,299.00")
ValueError: could not convert string to float: '$1,299.00'
Python’s float() does exactly what it should: it refuses. The failure goes quiet the moment someone “fixes” the crashing job with errors="coerce":
import pandas as pd
raw = pd.Series(["$1,299.00", "$99", "from $49.99"], name="price")
pd.to_numeric(raw, errors="coerce")
# 0 NaN
# 1 NaN
# 2 NaN
# Name: price, dtype: float64
No traceback. No warning. The job that used to crash at the type-conversion step now runs green, loads the table, and ships garbage downstream. errors="coerce" is the mechanism by which a parsing bug becomes a data-quality bug.
The typical cleanup people write first — strip the dollar sign, strip commas, then coerce — half-works, which is worse than not working at all:
| Raw scraped string | float(raw) | Naive replace('$','').replace(',','') + to_numeric(coerce) | Verdict |
|---|---|---|---|
'$1,299.00' | ValueError | 1299.0 | Parses |
'1299.00' | 1299.0 | 1299.0 | Parses |
'1 299,00 €' | ValueError | NaN (space and € survive the strip) | Silent null |
'from $49.99' | ValueError | NaN (text prefix survives) | Silent null |
'US$1,299' | ValueError | NaN (US survives) | Silent null |
'$12.99/unit' | ValueError | NaN (suffix survives) | Silent null |
'1.299,00 €' | ValueError | 1.299 | Silently wrong, off by 1000x |
Read that last row again. A German-formatted price, run through a comma-stripping cleanup, becomes 1.299 — a valid float, plausibly cheap, and invisible to every null-check you will ever write. It only surfaces when a human asks why a laptop is listed for a dollar twenty-nine. A NaN at least announces itself in a fill-rate report. A wrong value does not.
Auditing the Mess: Cataloguing Real Price Formats from store.example.com
Before writing the fix, enumerate the enemy. Dump the raw price strings you actually scraped — not the formats you assumed you’d get — and bucket them by pattern. On a 1,000-row sample from store.example.com’s mixed-locale catalog pages:
import re
import pandas as pd
BUCKETS = [
("space_thousands_comma_decimal", re.compile(r"\d[\s\xa0]\d{3},\d")),
("dot_thousands_comma_decimal", re.compile(r"\d\.\d{3},\d")),
("qualifier_prefix", re.compile(r"^\s*(from|starting at|as low as|ab|vanaf|à partir de)\b", re.I)),
("currency_code_prefix", re.compile(r"^\s*(US|CA|AU|NZ|HK)\$")),
("unit_suffix", re.compile(r"/\s*(unit|each|kg|lb|pcs|pack|m2|m²|l|ml)\b", re.I)),
]
def bucket(v: str) -> str:
hits = [name for name, rx in BUCKETS if rx.search(v)]
return "+".join(hits) if hits else "clean_or_other"
df["bucket"] = df["raw_price"].astype(str).map(bucket)
print(df["bucket"].value_counts())
Output:
clean_or_other 780
space_thousands_comma_decimal 97
dot_thousands_comma_decimal 41
qualifier_prefix 36
currency_code_prefix 28
unit_suffix 18
Those five buckets account for exactly the 220 NaN rows. Nothing unexplained. Now map each format to what it means:
| Raw format | Locale reading | Numeric value |
|---|---|---|
'$1,299.00' | en-US: comma thousands, dot decimal | 1299.00 |
'1 299,00 €' | fr-FR: space thousands, comma decimal | 1299.00 |
'US$1,299' | currency-code qualified, en-US separators | 1299.00 |
'$99' | no decimals, still en-US | 99.00 |
'from $49.99' | qualifier text (“starting at”) before the value | 49.99 |
'ab 1.299,00 €' | de-DE: dot thousands, comma decimal, qualifier | 1299.00 |
'$12.99/unit' | unit price suffix | 12.99 |
Notice that every single one of these contains the number 1299 (or its own value) in a recoverable form. None of these strings lost information at scrape time. The parser lost it.
Normalizing Currency Symbols, Separators, and Whitespace with a Cleaning Function
The cleaning function has four jobs, in order: strip qualifier text, strip unit suffixes, strip currency tokens, then normalize the numeric core. One rule drives the separator logic: when both . and , appear, the rightmost one is the decimal separator. That single rule resolves every two-separator format without locale knowledge.
import re
from typing import Optional
NBSP = "\xa0\u202f"
WS_BETWEEN_DIGITS = re.compile(rf"\d[\s{NBSP}]\d")
ALL_WS = re.compile(rf"[\s{NBSP}]+")
QUALIFIER = re.compile(
r"^\s*(from|starting at|as low as|ab|vanaf|desde|à partir de)\b[:\s]*", re.I
)
UNIT_SUFFIX = re.compile(
r"\s*/?\s*(unit|each|piece|pcs|kg|g|lb|oz|l|ml|m2|m²|pack|box|roll)s?\b.*$", re.I
)
CURRENCY_TOKEN = re.compile(
r"(?:(US|CA|AU|NZ|HK)\$)|[$€£¥]|"
r"\b(USD|EUR|GBP|CAD|AUD|JPY|CHF|SEK|NOK|DKK)\b", re.I
)
NUMBER = re.compile(r"-?\d[\d.,]*")
COMMA_DECIMAL_LOCALES = {"de", "fr", "es", "it", "pt", "nl", "pl", "ru", "sv", "da", "no", "fi"}
def _numeric_core_to_float(core: str, locale_hint: str) -> Optional[float]:
has_dot, has_comma = "." in core, "," in core
if has_dot and has_comma:
if core.rfind(",") > core.rfind("."):
core = core.replace(".", "").replace(",", ".") # 1.299,00 -> 1299.00
else:
core = core.replace(",", "") # 1,299.00 -> 1299.00
elif has_comma:
head, tail = core.rsplit(",", 1)
if locale_hint in COMMA_DECIMAL_LOCALES and len(tail) != 3:
core = head.replace(",", "") + "." + tail # 1299,00 -> 1299.00
else:
core = core.replace(",", "") # 1,299 -> 1299
elif has_dot:
head, tail = core.rsplit(".", 1)
if locale_hint in COMMA_DECIMAL_LOCALES and head and len(tail) == 3:
core = core.replace(".", "") # 1.299 -> 1299 (de-DE)
try:
return float(core)
except ValueError:
return None
def clean_price(raw: object, locale_hint: str = "en") -> Optional[float]:
if raw is None:
return None
s = QUALIFIER.sub("", str(raw), count=1)
s = UNIT_SUFFIX.sub("", s)
s = CURRENCY_TOKEN.sub("", s)
# A space grouping digits only happens in comma-decimal locales.
if WS_BETWEEN_DIGITS.search(s) and locale_hint not in COMMA_DECIMAL_LOCALES:
locale_hint = "fr"
s = ALL_WS.sub("", s)
m = NUMBER.search(s)
if not m:
return None
return _numeric_core_to_float(m.group(0), locale_hint)
Three design decisions worth defending. First, it returns None instead of raising or coercing — parse failure becomes an explicit value you can count and quarantine, not an exception at 3 a.m. or a silent NaN. Second, the space-grouping override: 1 299,00 can never be an en-US price, so whitespace between digits is itself a locale signal, and the function uses it. Third, no per-value locale library. Something like Babel’s parse_decimal handles 1 299,00 € with an fr locale, but it throws on from $49.99 and US$1,299 anyway, so you end up writing this exact prefix/suffix stripping around it. Forty lines with tests beats a dependency plus forty lines.
Unit tests against the audited catalog:
import pytest
FORMAT_CATALOG = [
("$1,299.00", "en", 1299.00),
("1 299,00 €", "fr", 1299.00),
("US$1,299", "en", 1299.00),
("US$99", "en", 99.00),
("$99", "en", 99.00),
("from $49.99", "en", 49.99),
("$12.99/unit", "en", 12.99),
("ab 1.299,00 €", "de", 1299.00),
("1299.00", "en", 1299.00),
("", "en", None),
(None, "en", None),
("call for pricing", "en", None),
]
@pytest.mark.parametrize("raw,hint,expected", FORMAT_CATALOG)
def test_clean_price_catalog(raw, hint, expected):
assert clean_price(raw, locale_hint=hint) == expected
Handling Ambiguous Separators: Detecting Locale Instead of Guessing
The catalog exposes one case no string-local rule can solve: a bare number with a single separator. 1,299 is one thousand two hundred ninety-nine in Chicago and one point two-nine-nine in Berlin. Same for 1.299 with the roles reversed. Guessing here is how you get the off-by-1000x wrong value, and a wrong value is strictly worse than a NaN because it passes every null check you have.
The separator meaning follows the page’s locale, so pull the hint from the page instead of the string:
| Page locale | Thousands sep | Decimal sep | '1.299' means | '1,299' means | Typical listing string |
|---|---|---|---|---|---|
| en-US | , | . | 1.299 | 1299 | '$1,299.00' |
| de-DE | . | , | 1299 | 1.299 | '1.299,00 €' |
| fr-FR | space | , | 1.299 | 1.299 | '1 299,00 €' |
import re
def locale_from_html(html: str) -> str:
m = re.search(
r'<html[^>]*\blang="([a-zA-Z]{2})(?:[-_][a-zA-Z]{2})?"', html, re.I
)
return m.group(1).lower() if m else "en"
The <html lang> attribute is present on virtually every localized storefront template. When it is missing, fall back to the URL structure (/de-de/, ?lang=fr), and when you are scraping per-country storefronts deliberately, request an exit IP in that country so the served markup and the locale agree — the details are in scraping localized storefronts with a country exit code.
There is also a column-level trick that beats per-value heuristics: if any price on the page contains both separators, that single string disambiguates the entire column. A page showing 1.234,56 anywhere uses dot-thousands and comma-decimal, period. Vote across the sample:
def locale_from_column(raw_prices: pd.Series, default: str = "en") -> str:
joined = " ".join(raw_prices.dropna().astype(str))
if re.search(r"\d\.\d{3},\d", joined):
return "de" # dot-thousands seen -> comma is decimal
if re.search(r"\d,\d{3}\.\d", joined):
return "en" # comma-thousands seen -> dot is decimal
return default
I run both: locale_from_html as the hint, locale_from_column as the tiebreaker for the bare single-separator case. Belt and suspenders costs nothing here, because both checks are pure string operations.
Catching Failures Early: Validating Prices Before the Data Load
Cleaning gets you to 100% parsing on parseable strings. Validation guarantees that whatever reaches the warehouse conforms, and that anything which doesn’t fails loudly at the boundary instead of quietly downstream. I use pandera for the DataFrame boundary because lazy=True reports every violating row in one pass instead of stopping at the first:
import pandera as pa
PriceRows = pa.DataFrameSchema(
{
"url": pa.Column(str, pa.Check.str_matches(r"^https://store\.example\.com/product/")),
"raw_price": pa.Column(str, nullable=True),
"price": pa.Column(
float,
nullable=False,
checks=[pa.Check.gt(0), pa.Check.le(10_000_000)],
),
"currency": pa.Column(str, pa.Check.isin(["USD", "EUR"])),
"locale": pa.Column(str, pa.Check.isin(["en", "de", "fr"])),
},
strict=True,
)
Rows that fail go to quarantine, not to /dev/null and not to the fact table. The quarantine table is what turns “the data looks weird” into a triage list with evidence:
import numpy as np
def split_for_load(df: pd.DataFrame):
bad = df["price"].isna() | (df["price"] <= 0) | (df["price"] > 10_000_000)
rejected = df[bad].copy()
rejected["reason"] = np.where(rejected["price"].isna(), "parse_failed", "out_of_range")
rejected[["url", "raw_price", "locale", "reason"]].to_sql(
"price_quarantine", engine, if_exists="append", index=False
)
return PriceRows.validate(df[~bad], lazy=True)
An opinion some people push back on: I want the load to hard-fail when the reject rate spikes, not just when a schema constraint trips. If 22% of rows suddenly quarantine, the site redesigned its price template and your selector is now grabbing shipping text. Loading the surviving 78% produces a table that looks fine and is lying about coverage. My threshold is crude — reject rate above 5% aborts the load and pages a human — and I would rather get paged for a false alarm than ship a quietly biased table twice.
Fixing It in the Pipeline: Patching the Scraper and Backfilling Bad Rows
Two repairs, in order: stop producing NaN rows, then repair the ones already loaded.
The original loader did the type conversion inline, which is where the whole bug class lives:
from parsel import Selector
def parse_product(html: str, url: str) -> dict:
sel = Selector(text=html)
raw = sel.css("span.price::text").get(default="").strip()
return {
"url": url,
"raw_price": raw,
"price": pd.to_numeric(
raw.replace("$", "").replace(",", ""), errors="coerce"
), # silently NaN on 22% of rows
}
The patched version pushes the raw string and the parsed float through the cleaning function, with the locale taken from the same HTML response:
def parse_product(html: str, url: str) -> dict:
sel = Selector(text=html)
raw = sel.css("span.price::text").get(default="").strip()
return {
"url": url,
"raw_price": raw,
"price": clean_price(raw, locale_hint=locale_from_html(html)),
"locale": locale_from_html(html),
}
Related Articles
Your Scraped Links Don't Resolve: Fix Relative URLs
Scraped hrefs like /products/42 wreck downstream crawls and dedupe keys. Learn how to resolve relative URLs to absolute ones before you store them.
TutorialIdentical Rows Fail to Join: Fix Unicode Normalization
Two scraped rows look identical but never join because the bytes differ. Spot NFC/NFD clashes, zero-width characters, and normalize keys before load.
TutorialYour Data Sits in an Iframe: Fix Extraction Across Frames
Your selector matches nothing, yet the data is visible in the browser. Here's why iframes break extraction and how to fetch frame content reliably.