pandas turned my money column into dates, and 123 tests said nothing

I sell a small Python tool that takes a folder of messy exports — CSV, TSV, Excel — and turns them into one clean workbook. Its whole pitch is that nothing is changed silently: every alteration is written to a Summary sheet so the client can check the work instead of trusting it.

Two days ago I found out it had been silently destroying money columns. Then I found the same defect in two more tools. Then a different defect, in all three. This is what happened, in order, because the order is the lesson.

Day one: 4141.98 becomes the year 4141

The tool guesses column types. A column of text that looks like dates becomes dates, text that looks like numbers becomes numbers. To decide whether a value might be a date before spending a parser on it, there was a cheap regex:

DATE_HINT = re.compile(r"\d[-/.]\d|\d{1,2}:\d{2}|[A-Za-z]{3,}")

Digit, separator, digit. 03.02.2026 matches. So does 4141.98.

That alone would be harmless, because the parser should reject 4141.98 as a date. It doesn't. pd.to_datetime("4141.98", format="mixed") happily returns the year 4141. And a column that has already been read as float — 4821.55 — goes through a different path and comes out as 1 January 1970 plus 4821 nanoseconds.

Nothing crashed. The workbook opened. The Summary said "columns to dates: 1". The invoice amounts were gone.

How it was found: not by the 123 tests, not by the 52 adversarial inputs, not by the 600,000-row performance run. A client-facing demo needed fresh sample data, and this time I wrote the amounts with "{:.2f}" — no thousands separator. Every previous fixture had written 4,141.98, and the comma breaks the date heuristic before it starts. A hundred and twenty-three tests had been checking the same shape of number over and over.

The fix: a date written in digits needs two separators, and a column of bare numbers is vetoed before any parsing is attempted.

DATE_HINT = re.compile(r"\d[-/.]\d+[-/.]\d|\d{1,2}:\d{2}|[A-Za-z]{3,}")
BARE_NUMBER = re.compile(r"^[-+]?[\d\s]*[\d][\s\d]*(?:[.,]\d+)?$")

Fourteen new tests. Done, I thought.

Day two: two separators are necessary but not sufficient

The next evening I was auditing a second tool — a web scraper that writes the same kind of workbook — and tried a column of product sizes: 10.5.2, 11.5.3, 12.5.4.

['Widget A', 1234.56, datetime.datetime(2002, 5, 10, 0, 0)]

Ten May 2002. Two separators, exactly as required. The fix from day one was correct and useless here.

Worse: it was unstable. Article numbers like 10.20.30, 11.20.31, 12.20.32 — three of them became dates. Add a fourth, 13.20.33, and month 13 fails to parse, the share of parseable values drops below the threshold, and the whole column stays text. Same site, same selector: scrape three items and lose the column, scrape four and keep it. No test will ever see a bug that depends on how many rows the page had that day.

The rule that actually separates a date from a size is not the number of separators. It is the year: written with dots, a date carries a four-digit year. 03.02.2026 is a date. 10.5.2 is a size. Slashes and dashes keep the two-digit year, because 12/31/26 is how half the world writes it.

DATE_HINT = re.compile(
    r"\d{1,2}[-/]\d{1,2}[-/]\d{2,4}(?!\d)"      # 31/12/26, 31-12-2026
    r"|\d{4}[-/.]\d{1,2}[-/.]\d{1,2}(?!\d)"     # 2026-01-12
    r"|\d{1,2}\.\d{1,2}\.\d{4}(?!\d)"           # 03.02.2026 - dots need a full year
    r"|\d{1,2}:\d{2}"
    r"|[A-Za-z]{3,}")

The part that changed how I work

The scraper had the original one-separator regex. So did a third tool that pulls JSON from APIs. All three had been written from the same template, and a defect in the template had been copied faithfully into every copy.

Once I looked, it wasn't just the regex:

Four times in twenty-four hours, the same sentence: found in one, present in the others.

The rule I now follow, written on the wall: when you find a defect in one tool, check every sibling the same day. Not next sprint. The same day, while you still remember exactly what the defect looks like.

Day three: not a date bug at all

With the date rule fixed in all three tools, I ran the API tool against a real public API and opened the result to look at it. The phone column read:

8,025,285,988
8,028,575,318

Money formatting on a telephone number. The API had sent "8025285988" as a string. The tool saw ten digits, called it a number, and Excel added the commas. Any leading zero would have been dropped for good.

Then I checked the CSV cleaner with a column of postal codes:

['05401', '05672', '05344']  ->  [5401.0, 5672.0, 5344.0]

The leading zero is gone and it is not coming back. Of everything in this post, that is the one you cannot recover from.

The guard is two cheap signals. A value that starts with 0 and has more digits after it is an identifier at any length — no quantity is written 05401. And a long unbroken run of digits where every value in the column is the same width is an identifier too: phones, accounts and barcodes are fixed-width, populations and amounts are not.

LONG_DIGITS  = re.compile(r"^\d{7,}$")
LEADING_ZERO = re.compile(r"^0\d+$")

def is_identifier(vals, threshold=0.8):
    if vals.map(lambda v: bool(LEADING_ZERO.match(v))).mean() >= threshold:
        return True
    longs = vals.map(lambda v: bool(LONG_DIGITS.match(v)))
    if longs.mean() >= threshold:
        widths = {len(v) for v, k in zip(vals, longs) if k}
        return len(widths) == 1
    return False

In the API tool there is a third signal, and it is the strongest: the source sent the digits as a string. A JSON number arrives as a number and never reaches this code. If the API bothered to quote it, it is telling you it is not a quantity.

What the tests were doing all this time

Passing. All of them. 123, then 137, then 150.

None of these defects crashed. Every one finished, wrote a workbook that looked correct, and was wrong. That is the only kind of failure that reaches a user, and it is the kind a test suite is worst at, because a test checks what you thought of, and you can only think of what you have already seen.

Three of the four were found by looking at the output — a demo image that happened to use different numbers, a preview of a listing where the phones had commas, a summary sheet opened in LibreOffice where a label was cut in half. None of those are tests. All of them are now.

The suite is at 167 checks, 35 of them marked REGRESSION — each one a bug that was in the code and shipped nothing, because it was caught. I keep the count honest by counting them with a command, after discovering that a number I had typed into the README from memory was wrong by two.

If you type-coerce columns, three questions

1. Does your date heuristic accept a bare decimal? Try 4141.98. Try a float 4821.55. Try 10.5.2.
2. What happens to 05401? If the answer is 5401, you are destroying postal codes, account numbers and product codes, and nobody will tell you.
3. Do your fixtures all write numbers the same way? Mine did. A hundred and twenty-three tests, one shape of number.

And the one that isn't about code: do you have a sibling tool built from the same template? Go and check it. Today.

The tool, its tests and the adversarial suite are public: github.com/kerimff7-rgb/csv-excel-cleaner. Every number in this post can be reproduced from it. If you find one that can't, I would like to know.