← All Kits · Python Kit

Dates in pandas: Parsing Them Once, Properly

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

Dates are the column that breaks a pipeline silently. A wrong number errors or looks absurd. A wrong date looks like a date, sits in the right column, sorts, and is only wrong on the days that could not be a month.

What you do: parse once, at load, with an explicit format, and count what failed.

The short version. If you did not state the format, the parser guessed, and it never tells you which way.

The demonstration

import pandas as pd

s = pd.Series(['03/04/2026', '11/12/2026'])

pd.to_datetime(s)                          # 2026-03-04, 2026-11-12
pd.to_datetime(s, format='%d/%m/%Y')       # 2026-04-03, 2026-12-11

The same two strings, two different results. Left to itself, pandas read them month first. With the format stated, they are day first. Neither call raised anything, and both produced valid dates.

On a real file this is worse than it looks. Days above 12 are unambiguous and parse correctly either way, so only the first twelve days of each month are affected. Roughly a third of your rows are wrong, scattered, and the column still sorts and charts perfectly.

Parsing at load

df = pd.read_csv('orders.csv',
                 parse_dates=['order_date'],
                 date_format='%d/%m/%Y')

Doing it in read_csv rather than afterwards means every later step sees a real datetime, and it removes the window in which somebody writes a comparison against a string and gets a sort that runs alphabetically.

The format codes worth knowing: %Y four-digit year, %y two-digit, %m month number, %b short month name, %B full month name, %d day, %H:%M:%S time. ISO strings, 2026-08-23, need no format at all and are unambiguous everywhere, which is why they are worth insisting on from any system you can influence.

Counting the failures

parsed = pd.to_datetime(df['order_date'], format='%d/%m/%Y', errors='coerce')
bad = parsed.isna() & df['order_date'].notna()
print(bad.sum())
print(df.loc[bad, 'order_date'].head(10))

errors='coerce' turns failures into NaT rather than stopping the script, which is right only if you then look at them. Printing the ten offending strings usually identifies the problem instantly: a second format in the same file, a footer row, or a placeholder such as 0000-00-00.

The dangerous pattern is coerce followed later by dropna(), which deletes exactly the rows whose dates were unusual. That is one of the six operations to challenge in reviewing a cleaning script.

The accessor, which is where the value is

d = df['order_date']

d.dt.year          # 2026
d.dt.month         # 4
d.dt.day_name()    # 'Friday'
d.dt.quarter       # 2
d.dt.to_period('M')          # 2026-04
d.dt.days_in_month
(d.max() - d.min()).days     # span in days

.dt only exists once the column is a real datetime, which makes it a useful test: if .dt raises an attribute error, the column is still text however much it looks like a date.

Grouping by month, two ways

# labels, only the months that occur
df.groupby(df['order_date'].dt.to_period('M'))['amount'].sum()

# a continuous axis, including months with no rows
df.set_index('order_date').resample('MS')['amount'].sum()

The difference matters for reporting. to_period returns only months that have data, so a month with no orders is missing from the result and a chart joins straight across it. resample inserts it as zero, which is usually what a time series needs, and is exactly the gap discussed in reading a rising line.

Date arithmetic

df['days_to_ship'] = (df['shipped_date'] - df['order_date']).dt.days

cutoff = pd.Timestamp('2026-01-01')
df[df['order_date'] >= cutoff]

df['order_date'] + pd.DateOffset(months=1)   # calendar-aware
df['order_date'] + pd.Timedelta(days=30)     # exactly 30 days

The last two are not the same and the difference bites at month ends. DateOffset(months=1) from 31 January gives 28 February; Timedelta(days=30) gives 2 March. Choose deliberately and say which one the business rule means.

Time zones, briefly

A naive timestamp has no zone and comparing it with an aware one raises. If any part of your data is zone-aware, make all of it aware and store in UTC:

d.dt.tz_localize('Europe/London').dt.tz_convert('UTC')

The reporting consequence to know about: an event at 23:30 UTC belongs to a different local day, so daily counts can differ by a whole day's worth of rows depending on which zone the grouping used. Say in the report which zone the days are in.

How to apply this to your own work

1. Add format= to every to_datetime call you own. It is a one-line change per call and it removes a whole class of silent error.
2. After parsing, print the min and max and the count by month. Two lines, and they catch a wrong-way parse.
3. Count the coerced failures and look at ten of them rather than dropping them.
4. Ask any system you control for ISO dates. It is the cheapest data quality improvement available.
5. Decide whether your monthly report needs empty months shown, and pick resample or to_period accordingly.

The one habit to keep

Check the parsed dates before using them. Min, max, and a count by month. Ten seconds, and it is the only reliable way to catch a date column that parsed successfully and wrongly.

Do you know which way round the dates in your main file were read?