to_datetime in pandas — practice examples (Bamboo Weekly)

Turn strings or numbers into real datetime values you can compute with.

What it does

Have you ever sorted a column of dates and gotten this back?

['1 May 2026', '10 April 2026', '10 March 2026', '9 April 2026']

Pandas was not sorting dates. It was sorting text, one character at a time, because that is all it had. A date that Pandas thinks is a string is barely a date at all: you cannot sort it, subtract one from another, slice a range out of it, or reach for the .dt accessor. pd.to_datetime is the conversion that unlocks all of it.

It takes a series of strings — or numbers, or a small data frame of year, month and day columns — and hands back a series of datetimes. It is a top-level function rather than a method, so it is pd.to_datetime(df['date']) and never df['date'].to_datetime(). That surprises people, but it is also why the function drops so neatly into a method chain.

Official documentation: pandas.to_datetime

The form I actually write

I use this function constantly, and almost always inside assign, replacing the string column with the datetime one under the same name:

df.assign(date=lambda df_: pd.to_datetime(df_['date']))

The lambda is what makes it work. assign calls it with the data frame as it exists at that point in the chain, so df_ is the current, filtered, renamed version rather than whatever df was when you started. Give the new column the old name and it replaces the string column, which is nearly always what you want with a date.

Pandas 3 gives you a second spelling, with one catch. pd.col cannot be handed to an arbitrary function — pd.to_datetime(pd.col('date')) raises TypeError: boolean value of an expression is ambiguous — but it can be piped into one:

df.assign(date=pd.col('date').pipe(pd.to_datetime))

That is the same pipe you already use to end a chain with a chart, doing the same job: handing the thing on its left to the function on its right. Keyword arguments go straight through, so pd.col('date').pipe(pd.to_datetime, format='%d/%m/%Y') is fine.

And if you are reading the file in the same breath, do it at load time instead: pd.read_csv(url, parse_dates=['date']) is one step rather than two, and Bamboo Weekly #3 — the first issue to use the data below — does exactly that. Reach for to_datetime when the data is already in memory, or when you need the arguments parse_dates cannot give you.

The arguments that earn their keep

pd.to_datetime(s, format='%d/%m/%Y')   # this exact layout, and nothing else
pd.to_datetime(s, dayfirst=True)       # a hint, not a promise
pd.to_datetime(s, errors='coerce')     # unparseable values become NaT
pd.to_datetime(s, utc=True)            # everything lands in UTC, timezone-aware
pd.to_datetime(s, unit='ms')           # the numbers are epoch milliseconds
pd.to_datetime(df[['year', 'month', 'day']])   # assemble from separate columns

format= is the one to reach for first, and the usual argument for it needs correcting. People will tell you it is faster. In Pandas 3 that is only sometimes true, because Pandas already infers the format from the first value and reuses it for the whole column. On the ISO 8601 timestamps below, letting Pandas infer took 4.0 ms across 8,546 rows, while spelling the format out took 10.4 ms — slower, not faster.

Where the speed claim does hold is when Pandas cannot infer anything. Then it warns you and falls back to dateutil, one element at a time:

UserWarning: Could not infer format, so each element will be parsed
individually, falling back to `dateutil`. To ensure parsing is consistent and
as-expected, please specify a format.

Parsing the same 8,546 timestamps rendered as 30 December 2025 at 11:51 PM took 204 ms that way, against 10.6 ms with format='%d %B %Y at %I:%M %p' — nineteen times the work. So: treat that warning as a bill, and pay it with a format string.

Writing that format string is the part nobody enjoys, because nobody remembers whether the month is %m or %M. I keep strfti.me open for exactly this: type a format and it shows you the result immediately, so you find out you wanted %-d instead of %d in two seconds rather than after a failed parse.

The safety argument for format=, though, holds every single time, and it is the real reason to type it. That is the next section.

A worked example, on real data

The USGS publishes every earthquake it records through a public API with no key, in CSV, and it is the source that Bamboo Weekly #3 worked with. Here is every event of magnitude 4.5 or greater in 2025:

import pandas as pd

url = ('https://earthquake.usgs.gov/fdsnws/event/1/query.csv'
       '?starttime=2025-01-01&endtime=2025-12-31&minmagnitude=4.5')

raw = pd.read_csv(url, usecols=['time', 'place', 'mag'])
raw.head(3)

                       time  mag                            place
0  2025-12-30T23:51:36.674Z  4.8         69 km E of Yamada, Japan
1  2025-12-30T22:36:09.826Z  4.5  173 km NNE of Tobelo, Indonesia
2  2025-12-30T22:19:46.317Z  4.8        117 km E of Miyako, Japan

That is 8,546 rows, and raw.dtypes reports time as str. Convert it:

df = raw.assign(time=lambda df_: pd.to_datetime(df_['time']))
df.dtypes

time     datetime64[us, UTC]
mag                  float64
place                    str
dtype: object

Two things in that dtype are worth a moment. The resolution is microseconds, not the nanoseconds you may remember; Pandas 3 now picks the resolution to fit the input. And it is timezone-aware, because those strings end in Z. Both details have videos of their own, linked at the end.

Now .dt works, and the question becomes answerable:

(
    df
    .assign(month=lambda df_: df_['time'].dt.month_name())
    .groupby('month', sort=False)
    .agg(quakes=('mag', 'size'), strongest=('mag', 'max'))
    .sort_values('quakes', ascending=False)
    .head()
)

           quakes  strongest
month
July         1322  8.8
August        984  7.5
September     799  7.8
October       756  7.6
January       655  7.1

July is not close. One line explains it:

df.loc[df['mag'] >= 8, ['time', 'place']]

                                 time                                        place
4281 2025-07-29 23:24:52.483000+00:00  2025 Kamchatka Peninsula, Russia Earthquake

A magnitude 8.8 off Kamchatka on 29 July, and the 1,322 events that month are mostly its aftershocks. None of that was reachable while time was a string.

The mistake that matters most

Suppose the same data reached you from a European source, written day first. Same earthquakes, same instants, different rendering:

euro = (
    raw
    .assign(when=lambda df_: pd.to_datetime(df_['time']).dt.strftime('%d/%m/%Y'))
    .sort_values('time')
)

Parse when with no arguments and Pandas stops you, which is the good outcome:

ValueError: time data "13/01/2025" doesn't match format "%m/%d/%Y". You might
want to try:
    - passing `format` if your strings have a consistent format;
    ...

It inferred %m/%d/%Y from 01/01/2025, hit the thirteenth of January, and refused to continue. Now take only the rows where the day is 12 or lower — where both readings are legal — and the protection disappears:

both = euro.loc[lambda df_: pd.to_datetime(df_['time']).dt.day <= 12]

guess = pd.to_datetime(both['when'])
right = pd.to_datetime(both['when'], format='%d/%m/%Y')

len(both), float((guess == right).mean().round(4))

(3548, 0.1012)

Three and a half thousand rows, no error, no warning, and the two readings agree on ten percent of them — the days where the day happens to equal the month. Here is what that costs:

both.loc[both['mag'].idxmax(), ['when', 'mag', 'place']]

when                                    08/02/2025
mag                                            7.6
place    210 km SSW of George Town, Cayman Islands
Name: 7706, dtype: object

The magnitude 7.6 Cayman Islands earthquake of 8 February 2025 becomes 2 August 2025. Aggregate it and August's count goes from 565 to 266 while March's goes from 203 to 368. Every number is plausible and every number is wrong.

dayfirst=True fixes this case, but read the documentation on it carefully: it is described as a hint that Pandas may fall back from. format='%d/%m/%Y' is a statement about your data, and Pandas will raise rather than quietly reinterpret it. When you know the layout, say it.

Five more mistakes people make

Reaching for errors='coerce' to make an error go away. It is a genuinely useful argument — one bad string in fifty thousand should not abort a conversion. But it converts failure into silence, and the row count never changes to tell you. On the column above:

c = pd.to_datetime(euro['when'], errors='coerce')
len(c), int(c.isna().sum())

(8546, 4998)

All 8,546 rows are still there. Nearly 59 percent of them are now NaT, and the 3,548 that survived are exactly the ambiguous ones — every one of them misparsed. Silencing the exception did not fix the data; it hid the fact that the format was wrong. Always follow a coerce with .isna().sum(), and investigate anything you did not expect.

Mixing timezone-aware and timezone-naive values. The USGS column is aware, so comparing it against a naive Timestamp fails outright:

df.loc[df['time'] >= pd.Timestamp('2025-07-29')]

TypeError: Invalid comparison between dtype=datetime64[us, UTC] and Timestamp

Subtracting is no better: TypeError: Cannot subtract tz-naive and tz-aware datetime-like objects. A plain string works, because Pandas localizes it for you — df.loc[df['time'] >= '2025-07-29'] returns 4,322 rows. Decide once, per data frame, whether you are aware or naive. utc=True makes everything aware and in UTC, and it is required outright when a column carries more than one offset, where Pandas raises ValueError: Mixed timezones detected. Pass utc=True in to_datetime or tz='UTC' in DatetimeIndex to convert to a common timezone.

Guessing at unit=. The same earthquakes arrive as epoch integers from the GeoJSON version of that API — 1767138696674 for the first row:

import requests

geo = pd.json_normalize(
    requests.get(url.replace('query.csv', 'query.geojson')).json()['features'])

With unit='ms' those become the same instants the CSV gave us, to the value. With unit='s':

pd.to_datetime(geo['properties.time'], unit='s').dt.year.head(2)

0    57968
1    57968
Name: properties.time, dtype: int32

The year 57968. Pandas does not object, because the arithmetic is valid; it complains only later, when something tries to print a timestamp that far out. Nothing in the number tells you the unit, though the digit count is a good tell — thirteen digits is milliseconds, ten is seconds. Note too that unit= alone gives you a naive column; utc=True is what makes it aware.

Forgetting to assign the result back. This is the classic, and I still catch myself:

pd.to_datetime(raw['time'])   # returns a new series, changes nothing
raw['time'].dt.year

AttributeError: Can only use .dt accessor with datetimelike values

to_datetime is a function that returns a value. It does not modify anything. The output has to go somewhere: back into assign, back into raw['time'] =, or into a variable. If .dt is refusing to work, check df.dtypes before you check anything else.

Parsing one value at a time. Every so often I see apply used here, and it is expensive in a way that does not look expensive:

pd.to_datetime(raw['time'])   #   4 ms
raw['time'].apply(pd.to_datetime)   # 1,169 ms
[pd.to_datetime(v) for v in raw['time']]   # 1,159 ms

Roughly 290 times slower, for 8,546 rows. to_datetime is built to receive the whole column and parse it in one pass; called on scalars it pays Python's overhead 8,546 times. Note that #76: Aging legislators uses df[columns].apply(pd.to_datetime) — but that is apply over three columns, handing each whole column to the function. That is fine. Applying over rows is not.

Assembling from separate columns

One more form, easy to miss. Plenty of official exports hand you the pieces rather than a date — a year column, a month column, a day column. Hand to_datetime a data frame whose columns are named exactly that and it assembles the dates for you:

parts = df.assign(year=pd.col('time').dt.year,
                  month=pd.col('time').dt.month,
                  day=pd.col('time').dt.day)

pd.to_datetime(parts[['year', 'month', 'day']]).head(3)

0   2025-12-30
1   2025-12-30
2   2025-12-30
dtype: datetime64[us]

hour, minute and second are optional extras. The names are not negotiable and neither is the column list. Pass the whole frame — pd.to_datetime(parts) — and you get ValueError: extra keys have been passed to the datetime assemblage: [mag,place,time], while a column named dom instead of day gets you ValueError: to assemble mappings requires at least that [year, month, day] be specified: [day] is missing. Select and rename first, then assemble.

Where it shows up in Bamboo Weekly

Forty-nine of the 185 Bamboo Weekly solutions call pd.to_datetime — about one in four. Four are worth reading, and all four are free.

#52: Border encounters is the format= argument in miniature. US Customs gave separate "Fiscal Year" and "Month (abbv)" columns, so I glued them together with a hyphen — and Pandas warned that the result was ambiguous. format='%Y-%b' settled it, inside an assign, and the new column went straight into set_index.

#33: Fracking is where I argue for this function over parse_dates explicitly: the FracFocus file has illegal date values in it, so parse_dates quietly gives back strings, while to_datetime accepts the arguments you need to say what you mean. That issue ends with errors='coerce' and a deliberate count of the surviving NaTs.

#71: Holidays is the canonical version of the idiom at the top of this page. Thirty thousand rows of world holidays, all strings, converted with .assign(date=lambda df_: pd.to_datetime(df_['date'])) — and the discussion says why that beats converting and reassigning outside the chain.

#70: Moon missions is the pipe version. Wikipedia's launch dates carry footnote markers, so the column needs str.replace before it can be parsed at all, and .str.replace(r'\[\d+\]', '', regex=True).pipe(pd.to_datetime) keeps both steps in one chain.

Practice it

Work through a to_datetime exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/to-datetime/

Go deeper

Once the column is a real datetime, the whole .dt accessor opens up: dt.year and friends for the numeric parts, and resample once you set it as the index. The Pandas user guide's time series chapter is the reference for everything this page skipped.

Two Pandas 3 changes touch this function directly, and both explain something in the dtype above: Microseconds vs nanoseconds: datetime64 changes in Pandas 3 for the [us], and Time zones in Pandas: What Pandas 3 changes (and what it doesn't) for the UTC. Then Pandas time series superpowers shows what the conversion buys you.

More Pandas videos on Python and Pandas with Reuven Lerner.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

.astype() — when the conversion is to a number, a string or a category rather than a date
.assign() — which is where the parsed column usually gets written back
.dt.year() — which is what the parsed column is for once the conversion is done
.dt.date() — when you no longer need the time and want a plain date
.dt.total_seconds() — which is what makes the subtraction produce a timedelta at all

See it on real data

Below are the 47 Bamboo Weekly exercises that use to_datetime on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.

pandas to_datetime