Dates and time series

Why it matters

A large share of real data has a timestamp: daily sales, hourly sensor readings, weekly case counts, monthly instrument calibrations. Questions about it are about time: "How did monthly visits change?", "Which weekday is busiest?", "Is there a trend once the daily noise is smoothed out?" pandas has excellent tools for this, but only once your dates are real dates rather than text.

Step 1: make dates real

df["date"] = pd.to_datetime(df["date"])                 # "2026-01-05" -> Timestamp
df["date"] = pd.to_datetime(df["date"], format="%d/%m/%Y")  # be explicit for 05/01/2026
df["date"] = pd.to_datetime(df["date"], errors="coerce")    # bad values -> NaT (missing)

Always give format when dates are written day-first or month-first. 01/02/2026 is 1 February in the UK and January 2 in the US, and pandas cannot know which you mean.

Once a column is datetime64, the .dt accessor gives its parts:

df["date"].dt.year
df["date"].dt.month
df["date"].dt.day_name()     # "Monday", ...
df["date"].dt.to_period("M") # 2026-01

Step 2: a DatetimeIndex

Most time-series tools work on the index:

daily = df.set_index("date").sort_index()
daily.loc["2026-02"]                 # every row in February 2026
daily.loc["2026-01-10":"2026-01-20"] # a date range (both ends included)

Resampling: change the frequency

resample is groupby for time. Pick a frequency and an aggregation:

monthly = daily["visits"].resample("MS").sum()     # month start labels
weekly  = daily["visits"].resample("W").mean()      # weeks ending Sunday

Choose the aggregation that matches the measure: sum for counts and money, mean or median for levels such as temperature or concentration, last for balances.

Comparing periods

monthly.pct_change() * 100      # % change from the previous month
monthly.diff()                  # absolute change

Smoothing with a rolling window

Daily data is noisy. A rolling mean averages each day with the days before it:

daily["visits_7d"] = daily["visits"].rolling(7).mean()

The first six values are NaN because a full 7-day window is not yet available. A 7-day window also removes the weekly pattern, which makes the underlying trend easier to see.

Grouping by calendar parts

To find the busiest weekday, group by the day name rather than resampling:

by_weekday = daily.groupby(daily.index.day_name())["visits"].mean()
by_weekday.idxmax()

A worked example

import numpy as np

rng = np.random.default_rng(7)
dates = pd.date_range("2026-01-01", "2026-03-31", freq="D")
daily = pd.DataFrame({"visits": rng.integers(80, 121, size=len(dates))}, index=dates)

monthly = daily["visits"].resample("MS").sum()
print(monthly)                   # three rows: Jan, Feb, Mar
print(monthly.pct_change().round(3))

February has 28 days and January 31, so February's total will usually be lower even if each day is just as busy. When months differ in length, compare the daily mean (resample("MS").mean()) as well as the total.

Common mistakes

• Dates left as text. Sorting text dates puts "10/01" before "2/01". Convert first.
• Ambiguous day/month order. Pass format= explicitly.
• Unsorted index. Sort before slicing ranges or computing rolling windows.
• Summing a level. Monthly "sum of temperatures" is meaningless; use the mean.
• Ignoring time zones. Mixed time zones shift events across midnight. Use tz_localize/tz_convert when data comes from several places.

Quick recap

• pd.to_datetime (with format when ambiguous) makes dates real; .dt gives their parts.
• Set a sorted DatetimeIndex, then slice with .loc["2026-02"].
• resample("MS").sum() changes frequency; choose sum or mean to match the measure.
• rolling(7).mean() smooths noise; pct_change compares periods; group by day_name() for weekday patterns.

Try it

In the Do tab you will convert a date column, resample daily visits to monthly totals and daily means, and find the busiest weekday.