Fixing Numbers With Thousands Separators in a CSV File

The comma is the delimiter of a CSV and also the thousands separator of half the world's number formats. A producer that writes 1,200 and quotes it correctly is fine. A producer that writes 1,200 without quotes has shifted every column to its right, and one that writes 1.200 for the same number in a European locale has written something a US-minded parser reads as 1.2. This article walks through the three failures, shows what each library does, and gives a parsing rule that says no when the text is ambiguous, instead of guessing. It continues the cleaning series, after currency strings, which use the same separators with a symbol in front.

Python will not parse it

The first failure is the plain one. int("1,200") raises ValueError, and so does float("1,200.5"):

import sys, re, io, csv
sys.path.insert(0, "/batches/csv-0001-0200/lab/clean")
import pandas as pd
from clean import *

def parse_grouped_int(text):
    """Strict: 1,200 and 12,34,567 (Indian grouping) are accepted; 1,20 and 1,2000 are not."""
    t = str(text).strip()
    if re.fullmatch(r"-?\d+", t):
        return int(t)
    if re.fullmatch(r"-?\d{1,3}(,\d{3})+", t) or re.fullmatch(r"-?\d{1,2}(,\d{2})+,\d{3}", t):
        return int(t.replace(",", ""))
    return None

samples = ["1,200", "85", "1,050", "12,34,567", "1,20", "1,2000", "1 200", "1.200", "1,200.5", "-3,000", "N/A"]
print(f"{'text':12s} {'int(text)':12s} {'remove commas':14s} strict")
for s in samples:
    try: plain = int(s)
    except ValueError: plain = "ValueError"
    print(f"{s!r:12s} {str(plain):12s} {str(parse_int(s)):14s} {parse_grouped_int(s)}")

print("\nthousands separator, quoted (a correct CSV):")
print(pd.read_csv(io.StringIO('id,units\n1,"1,200"\n2,85\n'), dtype=str).to_dict("records"))
print("with thousands=',':", pd.read_csv(io.StringIO('id,units\n1,"1,200"\n2,85\n'), thousands=",").dtypes.to_dict(), pd.read_csv(io.StringIO('id,units\n1,"1,200"\n2,85\n'), thousands=",")["units"].tolist())

print("\nthe same value written without quotes shifts the row:")
path = "/batches/csv-0001-0200/lab/data/clean/thousands-unquoted.csv"
with open(path, newline="", encoding="utf-8") as f:
    widths = [len(r) for r in csv.reader(f)]
print("field counts per line:", widths)
try:
    pd.read_csv(path)
except Exception as e:
    print("pandas:", type(e).__name__, str(e)[:80])

print("\nEuropean file (semicolon delimiter, dot thousands, comma decimal):")
print(pd.read_csv("/batches/csv-0001-0200/lab/data/clean/european.csv", sep=";", thousands=".", decimal=",").to_dict("records"))
print(pd.read_csv("/batches/csv-0001-0200/lab/data/clean/european.csv", sep=";").to_dict("records"))

df = drop_blank_and_footer(read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv"))[0].apply(lambda c: c.str.strip())
u = df["units"]
tok = u.str.lower().isin(MISSING)
lenient = u.map(parse_int); strict = u.map(parse_grouped_int)
print(f"\n200k units: missing tokens {int(tok.sum())}, parsed leniently {int(lenient.notna().sum())}, parsed strictly {int(strict.notna().sum())}, differ {int(((lenient != strict) & (lenient.notna() | strict.notna())).sum())}")
print("read_csv(thousands=',') dtype for units:", pd.read_csv("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv", thousands=",", dtype={"id": str})["units"].dtype, "(missing tokens stay text)")

python /batches/csv-0001-0200/lab/clean/t0120.py

text         int(text)    remove commas  strict
'1,200'      ValueError   1200           1200
'85'         85           85             85
'1,050'      ValueError   1050           1050
'12,34,567'  ValueError   1234567        1234567
'1,20'       ValueError   120            None
'1,2000'     ValueError   12000          None
'1 200'      ValueError   1200           None
'1.200'      ValueError   None           None
'1,200.5'    ValueError   None           None
'-3,000'     ValueError   -3000          -3000
'N/A'        ValueError   None           None

thousands separator, quoted (a correct CSV):
[{'id': '1', 'units': '1,200'}, {'id': '2', 'units': '85'}]
with thousands=',': {'id': dtype('int64'), 'units': dtype('int64')} [1200, 85]

the same value written without quotes shifts the row:
field counts per line: [4, 4, 4, 5, 4]
pandas: ParserError Error tokenizing data. C error: Expected 4 fields in line 4, saw 5


European file (semicolon delimiter, dot thousands, comma decimal):
[{'id': 1, 'name': 'Ana', 'units': 1200, 'price': 1234.5}, {'id': 2, 'name': 'Bo', 'units': 85, 'price': 7.0}]
[{'id': 1, 'name': 'Ana', 'units': 1.2, 'price': '1.234,50'}, {'id': 2, 'name': 'Bo', 'units': 85.0, 'price': '7,00'}]

200k units: missing tokens 12083, parsed leniently 187695, parsed strictly 187695, differ 0
read_csv(thousands=',') dtype for units: str (missing tokens stay text)

The first table has three columns of results. int(text) fails on every comma value (1,200, 1,050, 12,34,567, -3,000), which is at least loud. The second column, removing commas then converting (the parse_int of this series), gives 1200, 1050 and 1234567, which is right, and also 1,20 -> 120 and 1,2000 -> 12000. In a file whose numbers are meant to be 1,20 (one and twenty hundredths, a decimal comma) that parse is wrong by a factor of a hundred, and it raises nothing.

A strict rule

Thousands separators have a grammar (the Unicode CLDR number patterns list the conventions by locale). Groups are three digits in most of the world; Indian numbering groups the last three digits and then pairs (12,34,567). A strict parser accepts those patterns and rejects everything else, so that an ambiguous value is reported:

re.fullmatch(r"-?\d{1,3}(,\d{3})+", t)            # 1,200  -3,000
re.fullmatch(r"-?\d{1,2}(,\d{2})+,\d{3}", t)      # 12,34,567

The third column of the table is this rule: 1,200 -> 1200, 1,050 -> 1050, 12,34,567 -> 1234567, -3,000 -> -3000, and None for 1,20, 1,2000, 1 200 (a space grouping, which I chose not to accept), 1.200, 1,200.5 (a decimal part, a different parser) and N/A. None means "I will not guess". On the 187,695 non-missing units values in the large file, the strict and lenient parsers agreed on every one (0 differences), because the generator writes clean groups. On real data the difference between the two is the list of values you need to look at.

pandas: thousands=

For a column that you know is quoted correctly, read_csv has an option (see the reference):

pd.read_csv(io.StringIO('id,units\n1,"1,200"\n2,85\n'), thousands=",")
-> units dtype int64, values [1200, 85]

With thousands=",", the quoted "1,200" became the integer 1200 and the column is int64. Without it, dtype=str kept "1,200" as text. The option applies to every column, so a text column that contains 1,2 would be affected if it looked numeric; pass dtype for the columns that must stay text. And it is not a cleaning tool for a messy column. On the 200,000 row file, read_csv(thousands=",") left units as text (str), because the column also holds -, N/A and null, which are not numbers. The pandas parser gives up on the whole column when one value will not convert. So for a column with missing tokens, read as text and convert with your own function, as the missing-values article does.

The comma that was not quoted

The worse case is a writer that does not quote. This file has 1,050 as an unquoted value:

id,name,units,country
1,Ana,"1,200",US
2,Bo,85,US
3,Cy,1,050,US
4,Di,"2,5",DE

Line 4, 3,Cy,1,050,US, has five fields. The field-count histogram from the csv module, [4, 4, 4, 5, 4], finds it, and pandas stops: ParserError: Error tokenizing data. C error: Expected 4 fields in line 4, saw 5. That is the lucky outcome. With a reader that does not insist on a fixed width, such as plain csv.reader, the same row is ['3', 'Cy', '1', '050', 'US']: units is 1 and country is 050, and nothing complains. Repairing it means deciding that 1,050 was one value, which you can only do if you know which column can contain separators (here units) and that the other fields are never numeric text. A repair is possible when one column is the culprit:

• detect rows with one extra field (len(row) == expected + 1)

• merge the fields at the known position

• check that the merged value matches the strict grouping rule

and refuse otherwise. The check in the last step is what keeps the repair from joining Cy and 1 when the real problem is something else. Better, send the file back: the right fix is a writer that quotes.

The European file

In many countries the roles are swapped: the period groups thousands and the comma marks decimals, and the delimiter is a semicolon to make room for it. Reading such a file with the defaults is quiet and wrong:

id;name;units;price
1;Ana;1.200;1.234,50
2;Bo;85;7,00

With sep=";" only, units became 1.2 and 85.0 (floats: 1.200 read as one point two) and price stayed text ('1.234,50', '7,00'). With sep=";", thousands="." and decimal="," it became 1200 and 85, and 1234.5 and 7.0. The first result has no error. The number 1,200 became 1.2 and the program carried on. Use the three options together, and set them from what you know about the file's origin, not by guessing from the first row. A value like 1.200 is a thousand-two-hundred in one convention and one-point-two in the other, and the values 1.234,50 or 1,234.50 elsewhere in the column are what settle it.

How to tell it worked

Compare the sum of the column with the source system's. Count the values that failed the strict parser and read each distinct one. Check the range: a column of unit counts with a maximum of 12,000,000 where the file's maximum should be 12,000 has a decimal comma read as thousands. Check the field-count histogram of the raw file before parsing. And read the first rows by eye against the original.

When the separator is ambiguous

1,234 is 1234 in a US file and 1.234 in a European one, and the text does not say. Nor does 1.234. When the source locale is not documented, find a value that settles it, ask the provider, or reject the column. The cleaner that knows only one convention and applies it silently is the one that is wrong 91 times too large in German, as the C# article found. Write the locale into the contract with the provider (contracts), and into the code as a parameter.