The CSV number that parses silently into the wrong value

Most bad data announces itself. You get a ValueError, a traceback, a row that obviously failed. This one does not:

>>> float("1.234,56")
ValueError: could not convert string to float: '1.234,56'

>>> float("1,234")
ValueError: could not convert string to float: '1,234'

>>> float("1.234")
1.234                      # <- no error. and quite possibly wrong.

Those first two are fine. They are loud, you notice them, you fix them. The third is the one that costs money.

In an export written anywhere that groups thousands with a dot — Portugal, Spain, Germany, Italy, Brazil, most of Europe — 1.234 means one thousand two hundred and thirty-four. Python read it as one-point-two-three- four. Nothing raised. Nothing logged. The number is simply wrong, by a factor of a thousand, and it is now in a total.

Why locale is not the answer

The documented fix is locale.atof with the right locale set. It has two problems, and the first is that it often is not there at all:

>>> import locale
>>> locale.setlocale(locale.LC_NUMERIC, "pt_PT.UTF-8")
locale.Error: unsupported locale setting

That is a real result on a real Debian server — the same machine where de_DE.UTF-8 works fine. Locales are an OS package, not a Python one. On a slim container image there are usually none beyond C, so code that works on your laptop raises in production, on a line nobody tested because it "obviously" works.

The second problem is worse, because it survives installing the locale: one process has one LC_NUMERIC, and files do not come one convention at a time. Two exports from two subsidiaries, a file where someone pasted a column from a US system, a report where the totals row was typed by hand — setlocale is global state, and any answer it gives is the same answer for every column in every file you open until you change it back. It is also not thread-safe, which is its own afternoon.

The thing that actually decides it

Look at 1.234 for as long as you like; it will not tell you what it means. Five characters cannot settle it. So stop asking the value and ask the column, which nearly always tells on itself somewhere:

• A value carrying both separators settles it outright. In 1.234,56 the comma is last, so the comma is the decimal mark, so the dot groups thousands — and that holds for the whole column.
• A value carrying the same separator twice settles it too: in 1.234.567 the dots cannot be decimal marks, so the other character is.
• If neither appears anywhere in the column, there is nothing to infer from, and the honest thing is to say so rather than to guess.

Roughly forty lines, no dependencies:

import re

NUMBER = re.compile(r"^[\s€$£]*-?[\d.,\s]+\s*[%€$£]?\s*$")

def infer_decimal(values):
    """Which of . and , this column uses as its decimal mark, or None."""
    for v in values:
        if "." in v and "," in v:
            return "." if v.rfind(".") > v.rfind(",") else ","
    for v in values:
        if v.count(".") > 1:
            return ","          # 1.234.567 -> dots group
        if v.count(",") > 1:
            return "."
    return None                 # the column never says

def parse_number(value, decimal=None):
    """A float, or None. None means 'not a number', never 'zero'."""
    v = value.strip()
    if not v or not NUMBER.match(v):
        return None
    v = re.sub(r"[\s€$£%]", "", v)
    last_dot, last_comma = v.rfind("."), v.rfind(",")
    if decimal == ",":
        v = v.replace(".", "").replace(",", ".")
    elif decimal == ".":
        v = v.replace(",", "")
    elif last_dot > last_comma:
        v = v.replace(",", "")
    elif last_comma > last_dot:
        v = v.replace(".", "").replace(",", ".")
    try:
        return float(v)
    except ValueError:
        return None

# Ask the column first, then convert every value in it the same way.
column = ["1.234,56", "99,00", "2.000", ""]
mark = infer_decimal(column)              # ','
print([parse_number(v, mark) for v in column])
# [1234.56, 99.0, 2000.0, None]

Run that same column without asking the column first and you get [1234.56, 99.0, 2.0, None]. Three of the four are right: the last-separator rule handles 1.234,56 and 99,00 correctly all by itself, because those values carry their own evidence.

Only 2.000 is wrong — 2.0 where it should be 2000.0 — and that is exactly what makes it dangerous. The values that could go wrong come out right, so a spot check of the column looks clean, and the one broken number is the one nobody has any reason to look at twice. There is no exception anywhere.

Two rules that matter more than the parsing

Return None for "not a number", never 0. A zero flows into a sum and looks like data. A None has to be dealt with. Sums of columns containing silent zeros are how a report is wrong for a quarter before anyone notices.

Count what you could not read, and say so. A converter that silently leaves 3% of a column alone is worse than one that refuses the file, because you will find out from an invoice rather than from a log line.

And a smaller trap in the same file

While you are here: csv.Sniffer raises on a single-column file, which is a perfectly valid CSV.

>>> import csv
>>> csv.Sniffer().sniff("a;b;c\n1;2;3").delimiter
';'
>>> csv.Sniffer().sniff("so_uma_coluna\n1").delimiter
_csv.Error: Could not determine delimiter

Worth a try and a sensible default, rather than a crash on the one export that happened to have one column.

← HookForge: fixed-price automation scripts, delivered in 48h