Fixing Mixed Date Formats in a CSV Column Without Guessing

A column of dates written four ways is what a CSV looks like after several systems and several people have touched it. Parse it with one format and most values fail. Parse it with a tool that guesses and every value succeeds, some of them wrongly. The fix is neither: find out which formats are present, parse each with its own pattern, decide what to do with what remains, and say plainly that some values cannot be resolved by code. This article does that on a 200,000 row file with numbers for each step. The same problem in DuckDB is in reading a CSV with mixed date formats, and in pandas at read time in parsing dates while reading. This one is the cleaning-pipeline version, part of the series on cleaning a messy CSV.

Find the formats first

Before you write a parser, classify what is there. A handful of regular expressions, one per format, and a count:

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

PATTERNS = {
    "ISO 2025-03-14": r"\d{4}-\d{2}-\d{2}",
    "ISO unpadded 2025-3-6": r"\d{4}-\d{1,2}-\d{1,2}",
    "slash 03/15/2025": r"\d{1,2}/\d{1,2}/\d{4}",
    "day-month-name 15 Mar 2025": r"\d{1,2} [A-Za-z]{3} \d{4}",
}
def classify(v):
    for name, pat in PATTERNS.items():
        if re.fullmatch(pat, v):
            return name
    return "unrecognized"

small = drop_blank_and_footer(read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy.csv"))[0]
print(f"{'value':14s} {'format':28s} parsed")
for v in small["signup_date"]:
    print(f"{v:14s} {classify(v):28s} {parse_date(v)}")

big = drop_blank_and_footer(read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv"))[0]
kinds = big["signup_date"].map(classify)
print("\n200k rows by detected format:")
print(kinds.value_counts().to_string())

slash = big.loc[kinds == "slash 03/15/2025", "signup_date"]
parts = slash.str.extract(r"(\d{1,2})/(\d{1,2})/\d{4}").astype(int)
ambiguous = ((parts[0] <= 12) & (parts[1] <= 12) & (parts[0] != parts[1]))
print(f"\nslash dates: {len(slash)}; both numbers <= 12 and different (could be read either way): {int(ambiguous.sum())}")
print(f"first number above 12 (must be day first): {int((parts[0] > 12).sum())}; second above 12 (must be month first): {int((parts[1] > 12).sum())}")

t = time.perf_counter(); mine = big["signup_date"].map(parse_date); t_mine = time.perf_counter() - t
t = time.perf_counter(); guess = pd.to_datetime(big["signup_date"], format="mixed", errors="coerce"); t_guess = time.perf_counter() - t
mine_ts = pd.to_datetime(mine.astype("object").where(mine.notna(), None))
both = mine_ts.notna() & guess.notna()
print(f"\nexplicit formats: parsed {int(mine.notna().sum())}, failed {int(mine.isna().sum())}, {t_mine:.2f}s")
print(f"to_datetime(format='mixed'): parsed {int(guess.notna().sum())}, failed {int(guess.isna().sum())}, {t_guess:.2f}s")
print(f"rows where the two agree: {int((mine_ts[both] == guess[both]).sum())} of {int(both.sum())} parsed by both")
print("\nrows that did not parse:", big.loc[mine.isna(), "signup_date"].value_counts().to_dict())
valid = pd.Series(mine.dropna())
print("range of parsed dates:", valid.min(), "to", valid.max())

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

value          format                       parsed
2025-03-14     ISO 2025-03-14               2025-03-14
03/15/2025     slash 03/15/2025             2025-03-15
15 Mar 2025    day-month-name 15 Mar 2025   2025-03-15
2025-3-16      ISO unpadded 2025-3-6        2025-03-16
2025-03-20     ISO 2025-03-14               2025-03-20
2025-03-17     ISO 2025-03-14               2025-03-17
not a date     unrecognized                 None
2025-03-18     ISO 2025-03-14               2025-03-18
2025-03-19     ISO 2025-03-14               2025-03-19

200k rows by detected format:
signup_date
ISO 2025-03-14                86836
slash 03/15/2025              40038
day-month-name 15 Mar 2025    39632
ISO unpadded 2025-3-6         32778
unrecognized                    494

slash dates: 40038; both numbers <= 12 and different (could be read either way): 15642
first number above 12 (must be day first): 240; second above 12 (must be month first): 22779

explicit formats: parsed 198819, failed 959, 0.91s
to_datetime(format='mixed'): parsed 198819, failed 959, 1.08s
rows where the two agree: 198819 of 198819 parsed by both

rows that did not parse: {'not a date': 258, '31/02/2025': 240, 'TBD': 236, '2025-13-45': 225}
range of parsed dates: 2025-01-01 to 2025-12-28

The first table is the small file, value by value: 2025-03-14, 03/15/2025, 15 Mar 2025, 2025-3-16 and not a date, each with the format it was recognized as and the date it parsed to. The unpadded 2025-3-16 is the one that surprises people, because %Y-%m-%d rejects a one-digit month and a second pattern is needed. The second table is the large file: 86,836 ISO dates, 40,038 slash dates, 39,632 day-month-name dates and 32,778 unpadded ISO dates, with 494 that match none of the patterns. That count is the first useful number: it is how much of the column no format you have described explains.

Detecting a format is not validating a date

The explicit parser, which tries each strptime pattern in turn and then a fallback for the unpadded form, parsed 198,819 values and failed on 959. The list of what failed:

rows that did not parse: {'not a date': 258, '31/02/2025': 240, 'TBD': 236, '2025-13-45': 225}

Only 494 of those 959 were unrecognized by the classifier. The other 465 (31/02/2025 and 2025-13-45) have the shape of a date and cannot be dates: there is no 31 February and no month 13. A check that stops at "does it match \d{4}-\d{2}-\d{2}" would have passed them. Parse with the real calendar, and treat a parse failure as a different finding from a format you did not expect. The first is bad data to send back to the source; the second is a rule you can add.

I keep failures as nulls in the cleaned column and keep the original text next to it, so the audit is a one-line filter: rows where signup_date_raw is not empty and signup_date is null. Counting them by value, as above, is the report to hand to whoever owns the source. TBD is a placeholder, not a date is a typo someone left in, and the impossible dates are a validation gap in the form that collected them.

The dates no code can fix

The slash format is where parsing runs out. 03/04/2025 is 4 March in the United States and 3 April in most of the rest of the world, and the text does not say which. Of the 40,038 slash dates in the file:

• 22,779 have a second number above 12 (03/15/2025): they can only be month-first.

• 240 have a first number above 12 and an impossible month-day combination, the 31/02/2025 failures.

• 15,642 have both numbers at most 12 and different from each other (03/04/2025, 11/12/2025): either reading is a valid date.

The generator wrote them all month-first, which is why the explicit parser (month-first) agreed with everything. But look at what a day-first assumption would have done: the 22,779 unambiguous values would fail (month 15 does not exist), which you would notice, and the 15,642 ambiguous ones would parse into valid wrong dates, which you would not. The same strings through four readings:

import pandas as pd
# What a DD/MM/YYYY reading would do to the same strings
s = pd.Series(["03/04/2025", "11/12/2025", "13/04/2025", "04/13/2025"])
print("month first :", pd.to_datetime(s, format="%m/%d/%Y", errors="coerce").dt.strftime("%Y-%m-%d").tolist())
print("day first   :", pd.to_datetime(s, format="%d/%m/%Y", errors="coerce").dt.strftime("%Y-%m-%d").tolist())
print("mixed       :", pd.to_datetime(s, format="mixed").dt.strftime("%Y-%m-%d").tolist())
print("mixed, dayfirst=True:", pd.to_datetime(s, format="mixed", dayfirst=True).dt.strftime("%Y-%m-%d").tolist())

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

month first : ['2025-03-04', '2025-11-12', nan, '2025-04-13']
day first   : ['2025-04-03', '2025-12-11', '2025-04-13', nan]
mixed       : ['2025-03-04', '2025-11-12', '2025-04-13', '2025-04-13']
mixed, dayfirst=True: ['2025-04-03', '2025-12-11', '2025-04-13', '2025-04-13']

Month-first read 03/04/2025 as 2025-03-04 and 13/04/2025 as a failure. Day-first read the first as 2025-04-03 and failed on 04/13/2025. And format="mixed" got 03/04/2025 as 2025-03-04, month first, but 13/04/2025 as 2025-04-13: for that value it quietly switched to day-first because month 13 cannot exist. So a single column was read under two conventions, and the output has no sign of it. With dayfirst=True the ambiguous values flipped to 2025-04-03. That is why I do not use mixed as a cleaning step: it makes every value succeed by choosing a convention per value, and that is the opposite of what a cleaning step is for.

The fix for ambiguity is information, not code. Look for a rule that settles it: a column where any value has a first number above 12 is day-first, and a column where any value has a second number above 12 is month-first. If a file contains both kinds of row, it was assembled from two sources and has to be split by source before parsing. If it contains neither, ask. A third source is the data itself: signup dates cannot be in the future, so a reading that puts a date after the file's export date is the wrong one. Write whichever rule you settle on as a comment next to the format list, so the next person does not have to rediscover it.

The pandas shortcut, and what it costs

On the 200,000 rows, pd.to_datetime(col, format="mixed", errors="coerce") parsed 198,819 and failed on 959, exactly the explicit parser's numbers, and the two agreed on all 198,819 values. That agreement is a property of this file, which has only month-first slash dates. The timings were 0.91 s for the explicit parser and 1.08 s for mixed, so the shortcut was not even faster. Use it for exploration, and use the explicit list when the result goes anywhere.

For a column whose formats you know and trust, pd.to_datetime(col, format="%m/%d/%Y") is vectorized and much faster than a Python loop over a list of formats. The loop earns its place when the column really is mixed.

Writing the result

Write dates as ISO 8601, YYYY-MM-DD (RFC 3339 defines the timestamp form), because it is unambiguous, it sorts correctly as text and every tool reads it. The cleaned column goes next to the original, and an extra column is cheap:

• signup_date_raw: the original text, untouched

• signup_date: the parsed ISO date, or empty

• optionally signup_date_note: ambiguous, invalid or placeholder, for review

Then the report is a group by on the note column, and the data owner gets a number for each kind of problem, not a spreadsheet of 959 rows to read.

How to tell it worked

Count parsed plus failed and confirm it equals the number of rows. Check the minimum and maximum of the parsed column (here 2025-01-01 to 2025-12-28): a date in 1970 or 2099 is a parse that went wrong. Compare the distribution by month with the source system's. Spot check rows where both numbers are at most 12 against the original file. And rerun on the cleaned output: it should parse everything and change nothing.

What parsing cannot decide

It cannot tell day-first from month-first when both are valid, and it cannot recover a value that was never a date. It also cannot know the time zone of a timestamp that has none, which is the next problem after this one. For the validating side, checking that every date in a file parses before you load it, see checking that every date in a CSV parses; the cleaning order that puts this step after trimming is in the hub.