Parsing Dates While Reading a CSV With pandas Correctly

A CSV has no date type, so pandas reads dates as text and waits to be told. Telling it is easy for ISO dates and a source of quiet errors for everything else. The failures are not exotic. A day-first file read month first produces valid dates that are wrong, a column with two formats stays as strings with no complaint, and the pandas option that guesses per value will agree with different conventions on different rows of one column. This article runs each case on a few lines of data, explains what pandas did, and ends on the rule I follow: one declared format per column. The DuckDB version of the same problem, with its own traps, is in reading a CSV with mixed date formats, and the broader Python picture is in the guide.

ISO dates: ask for them

The general rule from the read_csv article applies: pandas infers nothing about dates unless you ask, and the same is true of identifiers, which is why a ZIP code column needs its own declaration.

A file with ISO 8601 dates, which is the unambiguous form:

id,joined,amount
1,2025-03-14,10
2,2025-03-15,20
3,2025-03-16,30

import warnings
import pandas as pd

df = pd.read_csv("dates-iso.csv")
print(df.dtypes.to_dict())
df = pd.read_csv("dates-iso.csv", parse_dates=["joined"])
print(df.dtypes.to_dict(), "|", df["joined"].iloc[0])
df = pd.read_csv("dates-iso.csv", parse_dates=["joined"], index_col="joined")
print(type(df.index).__name__, df.index.min(), df.index.max())

python /batches/csv-0001-0200/lab/py/pd_dates.py

{'id': dtype('int64'), 'joined': <StringDtype(na_value=nan)>, 'amount': dtype('int64')}
{'id': dtype('int64'), 'joined': dtype('<M8[us]'), 'amount': dtype('int64')} | 2025-03-14 00:00:00
DatetimeIndex 2025-03-14 00:00:00 2025-03-16 00:00:00

The first line is the default read. joined is a string dtype, so you cannot subtract, resample or compare it with a date. With parse_dates=["joined"] the dtype is datetime64[us] and the first value is 2025-03-14 00:00:00. In pandas 3 the resolution is microseconds by default (us), where earlier versions used nanoseconds; if you pass datetimes to another library, check the unit it expects. The third line puts the parsed column into the index with index_col="joined", which gives a DatetimeIndex running from 14 March to 16 March, the form you want for time series work.

A day-first file

Dates written dd/mm/yyyy are the norm in most of the world and wrong by default for code written with US assumptions. This file has three dates:

id,joined
1,03/04/2025
2,04/05/2025
3,13/04/2025

The author of the file meant day first: 03/04/2025 is 3 April, 04/05/2025 is 4 May, and 13/04/2025 can only be day first, since there is no month 13. I read the column as text and converted it four ways:

import warnings
import pandas as pd

warnings.simplefilter("always")
raw = pd.read_csv("dates-us.csv", dtype={"joined": "str"})
print("default :", pd.to_datetime(raw["joined"], format="mixed").dt.strftime("%Y-%m-%d").tolist())
print("dayfirst:", pd.to_datetime(raw["joined"], format="mixed", dayfirst=True).dt.strftime("%Y-%m-%d").tolist())
print("explicit:", pd.to_datetime(raw["joined"], format="%m/%d/%Y", errors="coerce").dt.strftime("%Y-%m-%d").tolist())
try:
    pd.to_datetime(raw["joined"], format="%m/%d/%Y")
except ValueError as e:
    print("strict ValueError:", str(e)[:120])
df = pd.read_csv("dates-us.csv", parse_dates=["joined"], date_format="%d/%m/%Y")
print("date_format %d/%m/%Y:", df["joined"].dt.strftime("%Y-%m-%d").tolist())

python /batches/csv-0001-0200/lab/py/pd_dates_us.py

default : ['2025-03-04', '2025-04-05', '2025-04-13']
dayfirst: ['2025-04-03', '2025-05-04', '2025-04-13']
explicit: ['2025-03-04', '2025-04-05', nan]
strict ValueError: time data "13/04/2025" doesn't match format "%m/%d/%Y". You might want to try:
    - passing `format` if your strings ha
date_format %d/%m/%Y: ['2025-04-03', '2025-05-04', '2025-04-13']

• **Default, format="mixed":** ['2025-03-04', '2025-04-05', '2025-04-13']. The first two are month first, 4 March and 5 April. The third is 13 April, day first, because month 13 does not exist, so pandas fell back for that one value. One column now holds two conventions, and two of the three dates are wrong. That is the dangerous result, and it is silent.

• **dayfirst=True:** ['2025-04-03', '2025-05-04', '2025-04-13']. Now all three agree with the author's intent.

• **A strict format="%m/%d/%Y" with errors="coerce":** the first two parse month first and the third is NaT, because 13 is not a month. Without coerce, it raises ValueError: time data "13/04/2025" doesn't match format "%m/%d/%Y". The error is the right behavior: it found the row that proves the assumption is wrong.

• **On the reader, date_format="%d/%m/%Y" with parse_dates=["joined"]:** ['2025-04-03', '2025-05-04', '2025-04-13']. This is the one I recommend, since the format is stated once and a row that does not fit it fails.

The reasoning from the DuckDB article applies here: 03/04/2025 has no right answer without knowing the source. Look for a row like 13/04/2025 that settles it, and ask the owner of the file if there is none.

A column with several formats

When one column contains dates in different formats, the strict options all fail. What does parse_dates do? I used the file from the DuckDB article, with four values:

id,joined
1,2025-03-14
2,03/14/2025
3,14 Mar 2025
4,not a date

import warnings
import pandas as pd

with warnings.catch_warnings(record=True) as w:
    warnings.simplefilter("always")
    df = pd.read_csv("dates-mixed.csv", parse_dates=["joined"])
print("parse_dates dtype:", df["joined"].dtype, "| warnings:", [f"{x.category.__name__}" for x in w])
print(df["joined"].tolist())
parsed = pd.to_datetime(df["joined"], format="mixed", errors="coerce")
print(parsed.dt.strftime("%Y-%m-%d").tolist())
print("failed:", df.loc[parsed.isna(), "joined"].tolist())

python /batches/csv-0001-0200/lab/py/pd_dates_mixed.py

parse_dates dtype: str | warnings: []
['2025-03-14', '03/14/2025', '14 Mar 2025', 'not a date']
['2025-03-14', '2025-03-14', '2025-03-14', nan]
failed: ['not a date']

With parse_dates=["joined"] the dtype stayed str, the values were returned unchanged, and no warning was raised. pandas tried to infer one format from the first value, did not find that the rest matched, and left the column alone. If you assume parse_dates always yields dates, a column like this will pass through your pipeline as text and fail later in a subtraction, or never.

pd.to_datetime(col, format="mixed", errors="coerce") parses each value on its own: the output was ['2025-03-14', '2025-03-14', '2025-03-14', nan], three dates, with not a date as NaT. That is useful for a one-off cleanup and a hazard in a pipeline, for the reason shown in the previous section: per value guessing is how an ambiguous string is read as a different convention from its neighbor. Use it, then list the failures (df.loc[parsed.isna(), "joined"] gave ['not a date']) and check any ambiguous values by hand.

The pattern I use

Read the column as text, convert it in a separate step with a format you wrote down, and look at what fails:

1. pd.read_csv(path, dtype={"joined": "str"})

2. pd.to_datetime(df["joined"], format="%d/%m/%Y", errors="coerce")

3. df["joined"].isna() & raw.notna() to find the values that did not parse.

For several known formats, convert with each in turn and fill from the first that succeeds, parsed = parsed.fillna(pd.to_datetime(raw, format=f2, errors="coerce")), ordering them from most to least specific. Keep the original text column until the end so that you can show someone the row that failed. On the four value file this is the whole thing:

import pandas as pd

raw = pd.read_csv("dates-mixed.csv", dtype={"joined": "str"})["joined"]
parsed = pd.to_datetime(raw, format="%Y-%m-%d", errors="coerce")
for fmt in ["%m/%d/%Y", "%d %b %Y"]:
    parsed = parsed.fillna(pd.to_datetime(raw, format=fmt, errors="coerce"))
print(parsed.dt.strftime("%Y-%m-%d").tolist())
print("failed:", raw[parsed.isna() & raw.notna()].tolist())

python /batches/csv-0001-0200/lab/py/pd_dates_multi.py

['2025-03-14', '2025-03-14', '2025-03-14', nan]
failed: ['not a date']

Time zones and timestamps

Everything above is about dates. A timestamp such as 2025-03-14 10:15:00 with no offset parses to a naive timestamp, and what it means depends on the system that wrote it. If the source is in UTC, localize it: .dt.tz_localize("UTC"). If it is local time, say which zone, with the daylight saving gap in mind: an hour that does not exist or happens twice cannot be converted without a rule. I did not test time zone handling for this article.

How to tell it worked

Check the dtype, and then the range: df["joined"].min() and .max() should be dates you expect, and a value in 1970 or 2262 is a parse that went wrong. Count the NaT values and compare with the number of blank or bad cells in the source. Spot check rows with a day of 12 or lower, since those are the ones that can be swapped, against the original file. And compare the number of distinct dates with another tool.

What pandas cannot decide for you

It cannot tell you whether 03/04/2025 is March or April. It cannot tell you what not a date was meant to be. It will not warn you when a column stays text, and it will not warn you when a per value guess disagrees with itself. The one part that is in your hands is the declaration: write the format, run the strict version, and let the first failure tell you something about the file.