Skip to content
Tutorial 9 min read

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.

FE
FineData Engineering · Editorial Policy
|
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 stringfloat(raw)Naive replace('$','').replace(',','') + to_numeric(coerce)Verdict
'$1,299.00'ValueError1299.0Parses
'1299.00'1299.01299.0Parses
'1 299,00 €'ValueErrorNaN (space and € survive the strip)Silent null
'from $49.99'ValueErrorNaN (text prefix survives)Silent null
'US$1,299'ValueErrorNaN (US survives)Silent null
'$12.99/unit'ValueErrorNaN (suffix survives)Silent null
'1.299,00 €'ValueError1.299Silently 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 formatLocale readingNumeric value
'$1,299.00'en-US: comma thousands, dot decimal1299.00
'1 299,00 €'fr-FR: space thousands, comma decimal1299.00
'US$1,299'currency-code qualified, en-US separators1299.00
'$99'no decimals, still en-US99.00
'from $49.99'qualifier text (“starting at”) before the value49.99
'ab 1.299,00 €'de-DE: dot thousands, comma decimal, qualifier1299.00
'$12.99/unit'unit price suffix12.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 localeThousands sepDecimal sep'1.299' means'1,299' meansTypical listing string
en-US,.1.2991299'$1,299.00'
de-DE.,12991.299'1.299,00 €'
fr-FRspace,1.2991.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),
    }
#web-scraping #data-cleaning #pandas #number-parsing #python #slot:problems-first

Related Articles