Dates and times in pandas

Intermediate

Introduction

Dates arrive as text: "2024-03-15", "15/03/2024", sometimes "N/A". Until they are parsed, pandas cannot sort them in time order, subtract them or group them by month. Converting date columns properly is one of the first cleaning steps in almost every analysis of orders, visits, sensor readings or prices.

How it works

Parsing. pd.to_datetime(df["date"]) converts a column to datetime64. Unparseable values raise an error unless you pass errors="coerce", which turns them into NaT, the missing value for dates. Give format="%Y-%m-%d" when you know the format; it is faster and avoids misreading day and month. pd.read_csv(..., parse_dates=["date"]) parses while reading.

d = pd.to_datetime(pd.Series(["2024-03-15", "2024-02-30"]), errors="coerce")
d.dt.year.tolist()          # [2024.0, nan]   (NaT makes the column float)
d.dt.day_name().tolist()    # ['Friday', nan]

Date parts. The .dt accessor gives .dt.year, .dt.month, .dt.day, .dt.dayofweek (Monday = 0), .dt.day_name() and .dt.to_period("M") for the month as a period. These make new columns for grouping: sales by weekday, visits by hour.

Durations. Subtracting two datetime columns gives Timedeltas: (df["delivered"] - df["ordered"]).dt.days is the number of whole days. Missing dates give NaT, and .mean() skips them. You can also add durations: df["ordered"] + pd.Timedelta(days=7).

Resampling. With dates as the index (or on="date"), resample groups by regular periods:

monthly = df.resample("ME", on="date")["amount"].sum()

"ME" means month end (pandas 2.2 and later; older versions use "M"), "W" weekly and "D" daily. Unlike groupby on a month column, resample creates every period between the first and last date, so a month with no sales appears with a sum of 0 instead of disappearing. That matters for charts and for growth rates.

Leap years, month lengths and time zones are handled by the library, which is the main reason never to compute dates with your own arithmetic on strings.

Example

# Dates modelled with the datetime module, as pd.to_datetime and the .dt accessor would see them.
from datetime import date

raw = ["2024-01-30", "2024-02-08", "not a date", "2024-03-01"]
parsed = []
for text in raw:
    try:
        parsed.append(date.fromisoformat(text))
    except ValueError:
        parsed.append(None)  # errors="coerce" gives NaT
print("parsed:", [d.isoformat() if d else "NaT" for d in parsed])
print("day names:", [d.strftime("%A") if d else "NaT" for d in parsed])
print("months:", [d.strftime("%Y-%m") if d else "NaT" for d in parsed])
print("days from first to last:", (parsed[-1] - parsed[0]).days)
Open in compiler

Line by line

  1. date.fromisoformat parses each text; a failure becomes None, as errors="coerce" gives NaT.
  2. strftime("%A") gives the day name, like .dt.day_name().
  3. "%Y-%m" gives the month, the key a monthly resample uses.
  4. Subtracting two dates gives a duration whose .days is a whole number, across the leap day of 2024.

Expected result

parsed: ['2024-01-30', '2024-02-08', 'NaT', '2024-03-01']
day names: ['Tuesday', 'Thursday', 'NaT', 'Friday']
months: ['2024-01', '2024-02', 'NaT', '2024-03']
days from first to last: 31

In the real world

Shops measure delivery times as differences between order and delivery dates, finance teams resample daily prices to monthly returns, and website analysts group visits by weekday and hour. Almost every dashboard has a time axis built with these tools.

Common mistakes

  • Leaving dates as text, so "2024-10-01" sorts before "2024-9-30" and nothing can be subtracted.
  • Mixing up day and month in formats such as 03/04/2024; state the format.
  • Grouping by month with groupby and losing the months with no data.
  • Ignoring NaT values when counting or averaging durations.

Best practices

  • Parse dates as soon as the data is read, with an explicit format.
  • Use errors="coerce" and then count the NaT values to see how many dates were bad.
  • Use resample for regular time periods and check the first and last period.

Summary

pd.to_datetime turns text into dates, with errors="coerce" making invalid values NaT. The .dt accessor extracts years, months and day names, subtracting dates gives Timedeltas whose .dt.days are whole days, and resample aggregates by regular periods, including empty ones.

Quick check

1. What does errors='coerce' do in pd.to_datetime?
2. What is the type of (end - start) for two datetime columns?
3. What does .dt.dayofweek return for a Monday?
4. Why use resample instead of groupby on a month column?

Key takeaways

  • Parse dates once with pd.to_datetime; use errors="coerce" to turn bad values into NaT.
  • Subtracting dates gives Timedeltas; .dt.days turns them into whole days.
  • resample creates every period in the range, so empty months appear with a total of 0.

Practice problems for this lesson

My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.