Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Python Pandas Datetime Conversion, Filtering, and Formatting

Learn how to convert strings to pandas datetime objects, filter rows by date and time, format datetime values, and handle timezone-aware data.

~5 min readPython
#pandas#datetime#data cleaning#time series
Illustration of pandas datetime conversion, filtering, and formatting with a calendar and clock motif

When working with time-series data in pandas, one set of tasks comes up constantly: converting raw strings to proper datetime objects, filtering rows by date or time conditions, and formatting datetime values for output. These operations are straightforward once you understand how pandas represents dates internally and which methods operate on the datetime64 dtype.

Parsing Strings into Datetime with pd.to_datetime

The entry point for most datetime conversions is pd.to_datetime. It accepts a Series, a list, or a scalar. When passed a Series, it returns a Series; when passed a list of strings, it returns a DatetimeIndex; when passed a scalar string, it returns a Timestamp. The function infers many common formats automatically, but explicit format strings give you control and speed.

python
import pandas as pd dates = pd.Series(['2023-01-15', '2023-02-20', '2023-03-25']) datetime_series = pd.to_datetime(dates) print(datetime_series)

This produces a Series where each element is a Timestamp. The default inference handles ISO-like formats well, but it does not know which convention a date like '01/02/2023' uses. If your source uses day/month/year, the default parser may treat the first number as the month. Pass format='%d/%m/%Y' to disambiguate.

python
dates_eu = pd.Series(['01/02/2023', '02/03/2023']) datetime_series = pd.to_datetime(dates_eu, format='%d/%m/%Y')

Specifying the format also improves performance because pandas skips the inference step. For large datasets, this can reduce conversion time noticeably.

Handling Different Date Formats and Mixed Input

Real-world data rarely arrives in a single clean format. You may have a column where some rows are '2023-01-15' and others are '15 Jan 2023'. The default pd.to_datetime tries to parse each element, but it raises a ValueError if it cannot parse the column as a consistent set. One approach is to use errors='coerce' to turn unparseable values into NaT (Not a Time), then inspect the failures.

python
mixed = pd.Series(['2023-01-15', '15 Jan 2023', 'invalid']) parsed = pd.to_datetime(mixed, errors='coerce') print(parsed) # 0 2023-01-15 # 1 2023-01-15 # 2 NaT

If you know the possible formats, you can try each format and combine the results. A practical pattern is to parse with one format, then fill remaining NaT values from the next format:

python
formats = ['%Y-%m-%d', '%d %b %Y', '%m/%d/%Y'] parsed = pd.to_datetime(mixed, format=formats[0], errors='coerce') for fmt in formats[1:]: parsed = parsed.fillna(pd.to_datetime(mixed, format=fmt, errors='coerce'))

Be careful with this approach: if a format partly matches an ambiguous string, it will fill in a date before later formats can try. For example, '01/02/2023' will be parsed by '%m/%d/%Y' as January 2 if that format comes first in the list. Validate the final dates for unexpected months or years.

Filtering Rows by Date and Time Conditions

Once a column is a proper datetime type, filtering is a matter of creating a boolean mask. You can compare the column against a Timestamp or against a string that pandas parses automatically.

python
df = pd.DataFrame({ 'date': pd.to_datetime(['2023-01-01', '2023-02-01', '2023-03-01']), 'value': [10, 20, 30] }) # Rows after February 1, 2023 filtered = df[df['date'] > '2023-02-01']

If your datetime column includes time information, a string like '2023-02-01' is treated as midnight, so df['date'] > '2023-02-01' excludes rows exactly at midnight. To include the entire day, use >= '2023-02-01' and < '2023-02-02'.

You can also filter with the .dt accessor for more granular conditions, such as selecting all rows from a specific month or weekday.

python
# All rows from March 2023 march = df[(df['date'] >= '2023-03-01') & (df['date'] < '2023-04-01')] # All rows on a Monday (weekday 0) monday = df[df['date'].dt.weekday == 0]

For time-of-day filtering, extract the hour with .dt.hour. For example, to select rows between 9 AM and 5 PM:

python
within_business_hours = df[(df['date'].dt.hour >= 9) & (df['date'].dt.hour < 17)]

Extracting Date Components with the .dt Accessor

The .dt accessor exposes datetime properties such as year, month, day, hour, minute, second, weekday, and day of year. This is useful for grouping, aggregating, or creating new features.

python
df['year'] = df['date'].dt.year df['month'] = df['date'].dt.month df['day_of_week'] = df['date'].dt.day_name()

These extracted columns are plain integers or strings, so you can use them directly in groupby or pivot_table. The .dt accessor only works on datetime Series; if you try to use it on an object column, pandas raises an AttributeError. Convert the column first.

Formatting Datetime Output with strftime

When you need to display or export dates in a specific string format, use the strftime method on a datetime Series. The format codes follow the standard Python datetime module conventions.

python
formatted = df['date'].dt.strftime('%Y/%m/%d') print(formatted)

Common format codes include %Y for four-digit year, %m for zero-padded month, %d for day, %H for hour, %M for minute, and %S for second. You can combine them with literal characters like dashes or slashes.

If you need to convert the entire Series to strings for a CSV export, strftime gives you full control over the representation. Without it, pandas uses the default ISO format YYYY-MM-DD HH:MM:SS, which may not match your requirements.

Timezone Handling and Conversion

Timezones make datetime conversion more complex. Strings without an offset produce timezone-naive values. Strings with an offset are parsed as timezone-aware values. If you want to normalize all input to UTC, use utc=True; if you have naive UTC values, use tz_localize to add a timezone and tz_convert to change it.

python
# Strings with offsets are parsed as timezone-aware with_offset = pd.to_datetime(['2023-01-01 10:00:00+02:00']) # Convert naive UTC timestamps to another timezone naive = pd.to_datetime(['2023-01-01 10:00:00']) aware_ny = naive.dt.tz_localize('UTC').dt.tz_convert('America/New_York')

tz_localize assigns a timezone to a naive Series, while tz_convert changes the timezone of an aware Series. Mixing naive and aware timestamps in the same column raises a TypeError, so ensure consistency before filtering or comparing.

Performance Considerations for Large Datasets

Datetime operations in pandas are vectorized: they operate on the entire Series without Python-level loops. Still, some pitfalls can slow down large datasets. One common mistake is using apply with a custom function when a vectorized method is available.

python
# Usually slower: apply with a lambda formatted = df['date'].apply(lambda x: x.strftime('%Y-%m-%d')) # Vectorized: .dt.strftime formatted = df['date'].dt.strftime('%Y-%m-%d')

The vectorized version is usually much faster for large Series. Similarly, when parsing strings, providing an explicit format avoids the overhead of format inference.

Memory usage is another consideration. A datetime64[ns] column uses 8 bytes per element, so it is not usually a memory problem. If profiling shows that date storage is a bottleneck, decide whether you need the time component at all before changing representations.

Common Pitfalls and Edge Cases

One frequent issue is comparing timezone-aware and timezone-naive columns. For example, df[df['date'] > '2023-01-01'] works only if df['date'] is timezone-naive. If it is aware, use an aware comparison value or convert the column first.

Another edge case is daylight saving time transitions. When you localize a naive Series to a timezone with DST, ambiguous times such as 2:30 AM on the day clocks fall back raise an AmbiguousTimeError. You can handle this by passing ambiguous='NaT' or a custom mapping.

python
# Handle ambiguous times by setting them to NaT aware = naive.dt.tz_localize('America/New_York', ambiguous='NaT')

Finally, pd.to_datetime with errors='coerce' silently turns invalid dates into NaT. This can hide data quality problems. After conversion, check the count of NaT values and decide whether to drop, fill, or investigate the source data.

For date arithmetic, pandas supports adding and subtracting Timedelta objects directly. For example, to shift all dates by one day: df['date'] + pd.Timedelta(days=1). This preserves the datetime dtype and handles month boundaries correctly.

When you need to round dates to a specific frequency, use Series.dt.floor, Series.dt.ceil, or Series.dt.round. These methods accept frequency strings like 'D' for day, 'H' for hour, or 'T' for minute. They are vectorized and more reliable than manually truncating the time component.

Understanding these datetime operations lets you move from raw string columns to a clean, filterable, and presentable time series without writing slow Python loops or reinventing date parsing logic.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.