Why one '-' turned your revenue column into text (and how to detect it first)

The dtype flip that makes sum() concatenate strings, how to find it in seconds, and the cleaning pattern that stops it.

A revenue column with 4,999 clean numbers and one - is not a numeric column. pandas gives it object dtype, and then:

>>> df["amount"].sum()
'1234.501800.00990.25...'

You did not get an error. You got a string. The report goes out with a total that is a concatenation of every value in the column.

How it actually happens

Excel exports write placeholders for empty cells: -, N/A, – (en dash), null, or a non-breaking space. Any one of them is enough. Also common: 12.5 kg, ¥1,234.50, (500) for negatives in accounting exports.

Detect it in one line

num = pd.to_numeric(df["amount"].astype(str).str.replace(r"[^\d.\-]", "", regex=True),
                    errors="coerce")
print(num.isna().sum())                    # > 0 means junk is in there
print(df.loc[num.isna(), "amount"].head()) # shows you exactly which values

The cleaning pattern that survives real files

s = df["amount"].astype(str).str.replace(r"[^\d.\-]", "", regex=True)
s = s.replace({"": None, "-": None, ".": None})     # placeholders -> missing
df["amount"] = pd.to_numeric(s, errors="coerce")
dropped = df["amount"].isna().sum()
print(f"{dropped} rows have no usable amount")       # say it out loud

Two decisions you must make explicitly, or your totals stay wrong: negative accounting values in parentheses ((500) → -500), and whether a missing amount is zero or should be dropped. Both answers are fine; silence is not.

Check dtype, not vibes

assert pd.api.types.is_numeric_dtype(df["amount"]), "amount is not numeric - stop here"

Put an assert like this at the top of every report script. It converts a silent wrong number into a loud failure, which is always cheaper.

Get the diagnosis before the cleaning

CSV Doctor scans every column, flags "mostly numeric but N values do not parse", lists the offending values, counts mixed date formats, detects encoding and BOM problems, and exits non-zero on HIGH issues so a cron job can refuse to import bad data. It is free — download it here.