Normalizing Prices, Dates and Units

Three field types cause most of the parsing work in any scraper, and this page — part of Cleaning and Validating Scraped Data — gives a runnable normaliser for each, with the test cases that prove it.

For money, detect the currency separately from the amount, work out which of . and , is the decimal separator from their positions, and return a Decimal. For dates, never let a parser guess the day-month order — supply it as a setting, and convert everything to an aware UTC datetime. For sizes, pick one base unit per dimension and store the converted value alongside the original string. All three normalisers should raise on anything they cannot parse, so the validation stage sees a rejected row instead of a plausible wrong number.

Money: Currency, Separators and Decimal

A displayed price is four facts glued together: an optional prefix (from, only, starting at), a currency marker that may be a symbol or a three-letter code, a numeric part with locale-specific grouping, and an optional suffix (incl. VAT, each, /kg). Pulling out the number with a single regular expression and calling float() on it fails on the second and third of those.

The separator problem is the hard one. 1.299,00 is one thousand two hundred and ninety-nine euros in most of continental Europe; 1,299.00 is the same amount in the UK and the US. The rule that resolves it without knowing the locale: whichever separator appears last is the decimal separator, and any earlier ones are grouping. When only one separator appears, the tell is the digit count after it — exactly three digits means grouping, anything else means decimal.

import html
import re
import unicodedata
from decimal import Decimal, InvalidOperation

SYMBOL_TO_CODE = {"£": "GBP", "$": "USD", "€": "EUR", "¥": "JPY", "₹": "INR"}
KNOWN_CODES = {"GBP", "USD", "EUR", "JPY", "INR", "CAD", "AUD", "CHF", "SEK"}
NUMBER_RE = re.compile(r"\d[\d\s., ']*\d|\d")


def squash(value: str) -> str:
    text = unicodedata.normalize("NFKC", html.unescape(value))
    return " ".join(text.split())


def detect_currency(text: str) -> str | None:
    for symbol, code in SYMBOL_TO_CODE.items():
        if symbol in text:
            return code
    for token in re.findall(r"[A-Z]{3}", text.upper()):
        if token in KNOWN_CODES:
            return token
    return None


def to_decimal(number: str) -> Decimal:
    """Resolve the decimal separator from position, then build an exact Decimal."""
    digits = re.sub(r"[\s ']", "", number)
    last_dot, last_comma = digits.rfind("."), digits.rfind(",")

    if last_dot == -1 and last_comma == -1:
        return Decimal(digits)

    decimal_pos = max(last_dot, last_comma)
    tail = digits[decimal_pos + 1:]
    head = digits[:decimal_pos]

    # Three trailing digits with no earlier separator is a grouping mark.
    if len(tail) == 3 and not any(c in ".," for c in head):
        return Decimal(re.sub(r"[.,]", "", digits))
    # Repeated identical separators are all grouping marks.
    if len(tail) == 3 and digits.count(digits[decimal_pos]) > 1:
        return Decimal(re.sub(r"[.,]", "", digits))

    return Decimal(f"{re.sub(r'[.,]', '', head)}.{tail}")


def parse_price(raw: str) -> tuple[Decimal, str | None]:
    text = squash(raw)
    match = NUMBER_RE.search(text)
    if match is None:
        raise ValueError(f"no numeric part in price {raw!r}")
    try:
        return to_decimal(match.group(0)), detect_currency(text)
    except InvalidOperation as exc:
        raise ValueError(f"cannot parse price {raw!r}") from exc


CASES = [
    ("£12.99", Decimal("12.99"), "GBP"),
    ("from £1.299,00 incl. VAT", Decimal("1299.00"), "GBP"),
    ("$1,234.56", Decimal("1234.56"), "USD"),
    ("1 234,50 EUR", Decimal("1234.50"), "EUR"),
    ("USD 2.500,00", Decimal("2500.00"), "USD"),
    ("£1,234", Decimal("1234"), "GBP"),
    ("9.99", Decimal("9.99"), None),
]
for raw, amount, code in CASES:
    assert parse_price(raw) == (amount, code), (raw, parse_price(raw))
print(f"{len(CASES)} price cases pass")

Use Decimal, never float. A float cannot represent 0.1 exactly, so a column of two-decimal prices summed as floats drifts away from the true total, equality comparisons fail on values that display identically, and a figure reconciled against an invoice comes out a fraction of a penny wrong. Decimal(str) is exact for any decimal literal; Decimal(float) is not, so always build it from the string.

Store the currency as a separate column. A price without a currency is not a number you can compare, and multi-region sites serve different currencies from the same template depending on the visitor's IP or a cookie — a detail worth pinning down before a crawl, since the pages themselves rarely say which one you got.

Dates: Ambiguity, Time Zones and Relative Strings

03/04/2026 is 3 April in London and 3 March in New York, and no amount of code can tell them apart from the string alone. The only safe method is to determine the ordering once, from a date on the site where the day exceeds twelve, and then pass that ordering as a fixed setting for every subsequent parse.

Where an explicit format is known, datetime.strptime is the right tool: it is fast and it fails loudly on anything that does not match. Where formats vary within a site, dateutil.parser with an explicit dayfirst flag handles the variety. Where the ordering is genuinely unknown, parse both readings and record a conflict rather than picking one.

import re
from datetime import datetime, timedelta, timezone

from dateutil import parser as dateutil_parser

KNOWN_FORMATS = ("%Y-%m-%d", "%d %b %Y", "%d %B %Y", "%Y-%m-%dT%H:%M:%S%z")

RELATIVE_RE = re.compile(
    r"(?P<count>\d+)\s*(?P<unit>second|minute|hour|day|week|month|year)s?\s*ago",
    re.IGNORECASE,
)
UNIT_DAYS = {"day": 1, "week": 7, "month": 30, "year": 365}


def parse_relative(raw: str, now: datetime) -> datetime | None:
    match = RELATIVE_RE.search(raw)
    if match is None:
        lowered = raw.strip().lower()
        if lowered in {"today", "just now"}:
            return now
        if lowered == "yesterday":
            return now - timedelta(days=1)
        return None

    count = int(match.group("count"))
    unit = match.group("unit").lower()
    if unit in UNIT_DAYS:
        return now - timedelta(days=count * UNIT_DAYS[unit])
    return now - timedelta(**{f"{unit}s": count})


def parse_date(raw: str, *, dayfirst: bool, now: datetime | None = None) -> datetime:
    """Return an aware UTC datetime, or raise if the string cannot be read."""
    text = " ".join(raw.split())
    now = now or datetime.now(timezone.utc)

    relative = parse_relative(text, now)
    if relative is not None:
        return relative

    for fmt in KNOWN_FORMATS:
        try:
            parsed = datetime.strptime(text, fmt)
        except ValueError:
            continue
        return parsed if parsed.tzinfo else parsed.replace(tzinfo=timezone.utc)

    parsed = dateutil_parser.parse(text, dayfirst=dayfirst)
    return parsed if parsed.tzinfo else parsed.replace(tzinfo=timezone.utc)


NOW = datetime(2026, 8, 1, 12, 0, tzinfo=timezone.utc)
for raw in ("2026-04-03", "3 Apr 2026", "2026-04-03T14:05:00+02:00",
            "2 days ago", "45 minutes ago", "yesterday", "April 3, 2026 2:05 pm"):
    print(f"{raw!r:28} -> {parse_date(raw, dayfirst=True, now=NOW).isoformat()}")

print(parse_date("03/04/2026", dayfirst=True, now=NOW).date())    # 2026-04-03
print(parse_date("03/04/2026", dayfirst=False, now=NOW).date())   # 2026-03-04

Three things in that function are deliberate. KNOWN_FORMATS is tried before dateutil because strptime is roughly an order of magnitude faster and rejects unambiguously — and note that slash-separated formats are deliberately absent from the list, so ambiguous input is forced through the explicit dayfirst path rather than being resolved by accident. Every return is timezone-aware, because mixing naive and aware datetimes raises TypeError on comparison and the failure surfaces weeks later in an unrelated query. And relative strings are resolved against an injectable now, which is what makes the function testable.

Relative timestamps deserve suspicion. "2 days ago" is only meaningful relative to when the page was fetched, so store the fetch time alongside the resolved value; re-parsing a cached page a week later otherwise produces a date that is a week wrong. "3 months ago" is worse still — the 30-day approximation above is a documented lie, and if month boundaries matter, use dateutil.relativedelta instead and accept that the source only ever had month-level precision.

Units: One Base Per Dimension

Sizes mix systems inside a single catalogue: 500g, 1.5 kg, 2 lbs and 12.5 fl oz can all appear on adjacent product pages. Sorting or filtering on the raw string is meaningless, so convert to one canonical base unit per dimension and keep the original text in a separate column for auditing.

import re
from decimal import Decimal

BASE_UNITS = {"mass": "g", "volume": "ml", "length": "mm"}

FACTORS: dict[str, tuple[str, Decimal]] = {
    "mg": ("mass", Decimal("0.001")), "g": ("mass", Decimal("1")),
    "kg": ("mass", Decimal("1000")), "oz": ("mass", Decimal("28.349523125")),
    "lb": ("mass", Decimal("453.59237")),
    "ml": ("volume", Decimal("1")), "cl": ("volume", Decimal("10")),
    "l": ("volume", Decimal("1000")), "floz": ("volume", Decimal("29.5735295625")),
    "cup": ("volume", Decimal("236.5882365")),
    "mm": ("length", Decimal("1")), "cm": ("length", Decimal("10")),
    "m": ("length", Decimal("1000")), "in": ("length", Decimal("25.4")),
    "ft": ("length", Decimal("304.8")),
}
ALIASES = {
    "gram": "g", "grams": "g", "kilogram": "kg", "kilograms": "kg", "kgs": "kg",
    "ounce": "oz", "ounces": "oz", "pound": "lb", "pounds": "lb", "lbs": "lb",
    "litre": "l", "liter": "l", "litres": "l", "liters": "l", "ltr": "l",
    "fl oz": "floz", "fluid ounce": "floz", "millilitre": "ml", "milliliter": "ml",
    "cups": "cup", "inch": "in", "inches": "in", "foot": "ft", "feet": "ft",
    "centimetre": "cm", "centimeter": "cm", "metre": "m", "meter": "m",
}

QTY_RE = re.compile(r"(?P<value>\d+(?:[.,]\d+)?)\s*(?P<unit>[a-zA-Z. ]+?)\s*$")


def parse_quantity(raw: str) -> tuple[Decimal, str, str]:
    """Return (value in the base unit, base unit, dimension) for a size string."""
    text = " ".join(raw.split()).lower()
    match = QTY_RE.search(text)
    if match is None:
        raise ValueError(f"no quantity found in {raw!r}")

    value = Decimal(match.group("value").replace(",", "."))
    unit = match.group("unit").strip().rstrip(".")
    unit = ALIASES.get(unit, unit.replace(" ", ""))
    unit = ALIASES.get(unit, unit)

    if unit not in FACTORS:
        raise ValueError(f"unknown unit {unit!r} in {raw!r}")

    dimension, factor = FACTORS[unit]
    return value * factor, BASE_UNITS[dimension], dimension


for raw in ("12.5 fl oz", "1.5 kg", "500g", "2 lbs", "1 L", "12 inches", "30 cm"):
    value, base, dimension = parse_quantity(raw)
    print(f"{raw!r:14} -> {value} {base}  ({dimension})")

try:
    parse_quantity("3 furlongs")
except ValueError as exc:
    print("rejected:", exc)

12.5 fl oz converts to 369.66911953125 ml. That number of digits looks absurd, and it is the point: the factors are exact Decimal values from the international definitions, so no rounding error accumulates through a chain of conversions. Round once, at the point of display or when writing to a fixed-scale database column, and never in the middle of the pipeline.

The alias table is doing the unglamorous work. fl oz, fl. oz., fluid ounce and floz all name the same unit, and the double ALIASES.get above handles both the spaced form and the compacted one. Any unit not in the table raises rather than defaulting, which means a catalogue that starts listing a new unit produces rejected rows you can see instead of silent nonsense.

One trap worth naming: a US fluid ounce is 29.5735 ml and an imperial fluid ounce is 28.4131 ml — a four per cent difference. The same is true of pints and gallons. If a site serves both regions, the unit token alone does not identify the unit, and the region has to come from the URL or the page's locale markers.

Edge Cases and Caveats

• Price ranges. "£12.99 – £24.99" matches the first number only. Detect the dash (including – and —) before parsing and store price_min and price_max rather than a single value.
• Negative and parenthesised amounts. Accounting-style (12.99) means minus twelve ninety-nine. If negatives are possible in the source, handle the parentheses explicitly, because the regular expression above drops them.
• Zero as a placeholder. Many templates render "price on application" as 0.00. A zero price should be rejected by validation, not stored.
• Two-digit years. strptime maps %y values 69–99 to the 1900s and 00–68 to the 2000s. On a site with historic listings that silently produces dates a century out.
• Daylight saving. Converting a naive local timestamp to UTC needs zoneinfo.ZoneInfo("Europe/London") rather than a fixed offset, or every timestamp shifts by an hour for part of the year.
• Unicode digits. Arabic-Indic and full-width digits are matched by \d in Python's re but rejected by Decimal. NFKC folding in squash converts the full-width forms; anything else needs an explicit translation table.
• Thin and non-breaking spaces as group separators. French and Scandinavian formatting uses U+00A0 or U+202F between thousands. The squash step normalises these, which is why it has to run before the number is extracted.

Frequently Asked Questions

How do I decide whether a comma is a decimal point or a thousands separator? Use position first: when both . and , appear, the one further right is the decimal separator. When only one appears, exactly three digits after it means grouping and any other count means a decimal. 1,234 stays genuinely ambiguous, so if the source can contain values under ten with three decimal places, take the ordering from the site's locale instead of guessing.

Should prices be stored as Decimal, integer minor units, or float?Decimal or integer minor units — pence, cents — are both correct; float is not. Integer minor units are the most compact and rule out fractional-penny drift entirely, at the cost of remembering the scale for currencies that do not use two decimal places, such as JPY. Decimal is easier to read in a database and maps directly to a NUMERIC column.

Is dateutil safe to use on unknown date formats? It is safe as long as you pass dayfirst explicitly and never rely on the default. Left to itself, dateutil resolves ambiguous input using its own heuristics, which means the same string can parse differently depending on the other tokens present. Determine the ordering once per source and pass it in every call.

What should a normaliser do when it cannot parse a value? Raise. Returning None, an empty string or a zero puts a plausible wrong value into the dataset that nobody will question. Raising sends the row into the rejects file with the original text attached, so the failure is visible and the batch can be replayed once the parser is fixed.

Related

• Cleaning and Validating Scraped Data — the parent guide and the pipeline these parsers sit in
• Validating Scraped Data with Pydantic — turning these parsers into before validators on a typed model
• Deduplicating Records with Fuzzy Matching — why normalising has to happen before matching
• Exporting Scraped Data to CSV and Parquet — keeping decimal precision through a file format