Turning Currency Strings Into Numbers in a CSV Exactly

Money in a CSV is text. $1,234.50, (12.50), 1.234,50 €: each means a number, and each breaks the obvious conversion in a different way. The obvious conversion is "delete everything that is not a digit or a dot, then float()", and it works for exactly the style of data the author had on hand. This article builds a parser that handles the formats in a customer export, shows what the shortcut gets wrong on 200,000 rows, and finds a problem that no parser can fix, a column that quietly contains more than one currency. It is part of the cleaning series; thousands separators in plain integers are in their own article.

The formats

Money turns up in these forms, and a parser has to decide each one:

The parser applies three rules, which follow the conventions described in the Python decimal documentation for exact arithmetic and the Unicode currency symbols block for the symbols. Strip the currency symbols, letters and spaces. If the text contains both a dot and a comma, whichever comes last is the decimal separator. If it contains only a comma, it is a decimal comma when it is followed by one or two digits at the end, and a thousands separator otherwise. Parentheses mean negative. The code returns Decimal, not float:

def parse_money(text):
    """'$1,234.50' -> Decimal('1234.50'); '(12.50)' -> -12.50; '1.234,50 €' -> 1234.50."""
    if text is pd.NA or text is None:
        return None
    t = str(text).strip()
    neg = t.startswith("(") and t.endswith(")")
    t = re.sub(r"[^\d,.\-]", "", t)
    if "," in t and "." in t:
        dec = "," if t.rfind(",") > t.rfind(".") else "."
    elif "," in t:
        dec = "," if re.search(r",\d{1,2}$", t) else "."   # 7,00 is a decimal comma; 1,234 is thousands
        if dec == ".":
            t = t.replace(",", "")
    else:
        dec = "."
    if dec == ",":
        t = t.replace(".", "").replace(",", ".")
    else:
        t = t.replace(",", "")
    try:
        v = Decimal(t)
    except InvalidOperation:
        return None
    return -v if neg else v

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

samples = ["$1,234.50", "$0.00", "(12.50)", "€15.75", "1.234,50 €", "$7,00", "1,234", "1.234", "-$5.00", "12.5%", "1 234,50", "USD 99.00", "-", "N/A", "", "abc"]
print(f"{'text':14s} -> parsed")
for t in samples:
    print(f"{t!r:14s} -> {parse_money(t)}")

df = drop_blank_and_footer(read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv"))[0]
bal = df["balance"].str.strip()
def style(v):
    if v.lower() in MISSING: return "missing"
    if v.startswith("("): return "parentheses"
    if v.endswith("€"): return "european  1.234,50 €"
    if v.startswith("$"): return "dollar  $1,234.50"
    if v.startswith("€"): return "euro prefix  €15.75"
    return "plain  12.50"
print("\nstyles in 200,000 balances:")
print(bal.map(style).value_counts().to_string())

t = time.perf_counter(); parsed = bal.map(parse_money); dt = time.perf_counter() - t
missing = bal.str.lower().isin(MISSING)
print(f"\nparsed {int(parsed.notna().sum())}, missing tokens {int(missing.sum())}, failed on a real value {int((parsed.isna() & ~missing).sum())}, {dt:.2f}s")
sym = bal.str.extract(r"([$€])")[0].fillna("none").value_counts()
print("currency symbols:", sym.to_dict())
vals = [v for v in parsed if v is not None]
exact = sum(vals, Decimal(0))
flt = sum(float(v) for v in vals)
print(f"Decimal sum {exact:,.2f} | float sum {flt:,.2f} | float minus Decimal {flt - float(exact):.6f}")

naive = pd.to_numeric(bal.str.replace(r"[^\d.\-]", "", regex=True), errors="coerce")
agree = (naive.round(2) == parsed.map(lambda v: float(v) if v is not None else float("nan")).round(2)) | (naive.isna() & parsed.isna())
print(f"stripping symbols and commas, then to_numeric, disagrees with the careful parser on {int((~agree).sum())} of {len(bal)} rows")
bad = bal[~agree].head(6).tolist()
print("examples:", bad)

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

text           -> parsed
'$1,234.50'    -> 1234.50
'$0.00'        -> 0.00
'(12.50)'      -> -12.50
'€15.75'       -> 15.75
'1.234,50 €'   -> 1234.50
'$7,00'        -> 7.00
'1,234'        -> 1234
'1.234'        -> 1.234
'-$5.00'       -> -5.00
'12.5%'        -> 12.5
'1 234,50'     -> 1234.50
'USD 99.00'    -> 99.00
'-'            -> None
'N/A'          -> None
''             -> None
'abc'          -> None

styles in 200,000 balances:
balance
dollar  $1,234.50       80747
euro prefix  €15.75     26871
parentheses             26861
european  1.234,50 €    26783
plain  12.50            26544
missing                 11972

parsed 187806, missing tokens 11972, failed on a real value 0, 0.45s
currency symbols: {'$': 80747, 'none': 65377, '€': 53654}
Decimal sum 1,309,379,177.89 | float sum 1,309,379,177.89 | float minus Decimal 0.000000
stripping symbols and commas, then to_numeric, disagrees with the careful parser on 53644 of 199778 rows
examples: ['6.678,66 €', '(6,787.01)', '5.207,63 €', '(2,505.35)', '(2,612.65)', '(10,237.02)']

The first table is the sixteen sample strings and what the parser returned. $1,234.50 gave 1234.50, (12.50) -12.50, €15.75 15.75, 1.234,50 € 1234.50, $7,00 7.00, 1,234 1234, 1 234,50 1234.50, -$5.00 -5.00, USD 99.00 99.00. Missing tokens (-, N/A, empty) returned None, and so did abc. 12.5% returned 12.5, which is a judgment call about a percent sign that I would reject in a money column.

The ambiguous ones

Two rows in the table show where the rules guess. 1.234 came back as 1.234: with only a dot, the parser assumes a decimal point. In a European file 1.234 is one thousand two hundred thirty-four, and nothing in the text says which. The same goes for 1,234, which I read as 1234 (a comma followed by three digits) and which a European reader would see as 1.234. The rules above resolve the ambiguity in favor of the file's most likely convention, and that is a guess. If you know the locale of the source, say so in the code (a parameter, not a heuristic), and run the heuristic only when you do not. A good sanity check is to look for any value that contradicts your assumption: a value like 1.234,50 or 1,234.50 settles which separator is the decimal for the whole column, as with dates.

What the shortcut gets wrong

The shortcut is str.replace(r"[^\d.\-]", "") followed by pd.to_numeric. On the 200,000 row file it disagreed with the careful parser on 53,644 of 199,778 rows. The examples it printed show both failures:

• 6.678,66 € became 6.67866, a number about a thousand times too small and no error. The dot was kept as a decimal point and the comma was deleted.

• (6,787.01) became 6787.01, positive. The parentheses were stripped with the other symbols, and the sign, which is the meaning of the parentheses, was lost.

The 53,644 are exactly the 26,861 values in parentheses plus the 26,783 in European format. Neither produces a missing value, an error or a warning. A quarter of the column is wrong, and the total moves by millions. This is the pattern in this whole series of articles: the shortcut works on the first screen of data and fails on the rest.

The column that mixes currencies

The counts by symbol are the finding I would have missed without looking:

currency symbols: {'$': 80747, 'none': 65377, '€': 53654}

80,747 balances carried a dollar sign, 53,654 a euro sign and 65,377 had no symbol, either because they were written in parentheses, as plain numbers, or with the symbol dropped. A column that holds both dollars and euros, and where a third of the values do not say which, cannot be summed. The sum I printed, 1,309,379,177.89, is a number with no unit. The cleaned output should carry a currency column next to the amount, taken from the symbol where there is one and from the source system's rule where there is not, and anything without a source of truth should be flagged. If the file is one currency by agreement and the symbols are noise, say that in the contract with the provider (contracts are the right place), and do not infer it.

Decimal, and what float does

I parsed to Decimal and also summed as float to see whether the float drift people warn about shows up. On 187,806 values:

Decimal sum 1,309,379,177.89 | float sum 1,309,379,177.89 | float minus Decimal 0.000000

They agree to six decimals. Float error on 188,000 additions of two-decimal numbers is far smaller than the cent, so the total is the same either way. The reason to use Decimal is not that sums drift visibly. It is exact equality (Decimal("0.1") + Decimal("0.2") == Decimal("0.3") is true, and the float version is not), predictable rounding (you choose the rule, half-up or half-even, instead of inheriting whatever the binary value happens to be), and not having to explain to an auditor why 0.1 + 0.2 is 0.30000000000000004. Keep Decimal through the pipeline and write the number out with a fixed number of decimals, or store cents as integers. DuckDB's DECIMAL(18,2) and PostgreSQL's numeric take the cleaned column as is.

Speed

The parser is a Python function applied with Series.map. It processed 199,778 values in 0.45 s, about 2 microseconds each. For a few million rows that is a second or two; for hundreds of millions it is not, and there you either normalize inside the database or vectorize the common formats and fall back to the function for the rest. I would measure before optimizing.

Missing is not zero

11,972 values were missing tokens: -, N/A, null and empty. The parser returns None for them, and the cleaned column keeps them missing. A fillna(0) here changes every average of the column, and it turns "we do not know the balance" into "the balance is zero", which is a different statement. The next article, handling missing values without guessing, is about that choice.

How to tell it worked

Count parsed plus missing plus failed; it should equal the number of rows (187,806 + 11,972 + 0 = 199,778). Look at the failures (here none on real values) and read them. Compare a total with the source system's, per currency. Check the minimum and maximum of the parsed amounts. Spot check the three styles that differ from US format: parentheses, European and symbol-after, against the raw text.

What a parser cannot know

It cannot tell you the currency of a bare number, whether 1.234 is a thousand or a little over one, or whether a negative balance is a refund or a mistake. It can make those questions visible, by keeping the raw text, the parsed value and a flag next to each other, and by counting. The rest is a conversation with the owner of the data.