Fix currency values that pandas reads as text

A CSV contains amounts such as 1,200.50 and a summary fails or concatenates values. Some rows contain currency labels while others use a different decimal convention.

Why this happens

Numbers formatted for display are not always machine-readable numeric values. Removing punctuation without knowing the locale can turn 1,20 into 120 and corrupt the result.

Step-by-step solution

1. Inspect representative raw values and confirm the decimal and thousands separators. Do not combine multiple currencies in one sum without a conversion policy.

2. For a confirmed single convention, remove only the expected grouping separator and convert using to_numeric with errors='coerce'.

3. Count conversion failures and retain their original text. For financial storage, consider integer minor units or decimal arithmetic instead of relying on binary floating-point rounding.

Worked example

import pandas as pd
raw = pd.Series(['1,200.50', '800.00', 'unknown'])
values = pd.to_numeric(raw.str.replace(',', '', regex=False), errors='coerce')
assert values.isna().sum() == 1
assert values.sum() == 2000.50

Verify the fix

The two valid values total 2000.50 and the invalid entry remains detectable. Test a negative amount and a value with a decimal comma before accepting another export source.

Prevent a repeat

Store currency code separately from amount. Keep a conversion specification next to the ingestion code so future changes cannot silently reinterpret prices.

Reference

Official documentation or standards guidance

An original Gistmax troubleshooting guide. Scenarios and sample values are illustrative; configuration depends on your software version and environment. Cover diagram: Gistmax Tutorials.