Time series in Pandas

The last lesson kept treating the timestamp as just another column — something to pivot on or sort by. But time is not just another column: it has order, a frequency, gaps, and a whole calendar buried in it. Real-world data almost always carries a timestamp, and pandas was built for this — once your data has a true DatetimeIndex, resampling to daily totals, computing 7-day moving averages, and slicing by '2026-01' all collapse into one-liners. The catch: a “date column with dates in it” is not a DatetimeIndex, and everything depends on getting that one thing right first.

Get a real DatetimeIndex

A string-typed column of dates and a parsed datetime64 index are different objects — and the entire time-series toolkit (.resample(), date-string slicing like df.loc["2026-01"], time-based .rolling()) requires the index, not a column of strings.

import pandas as pd

# Pretend we read this from a CSV
events = pd.DataFrame({
    "timestamp": [
        "2026-01-01 09:14:00",
        "2026-01-01 09:47:00",
        "2026-01-01 13:22:00",
        "2026-01-02 08:05:00",
        "2026-01-02 19:31:00",
        "2026-01-03 11:18:00",
    ],
    "user_id":   [1, 2, 1, 3, 2, 1],
    "duration":  [42, 18, 7, 31, 55, 12],
})

# Wrong way: leaves it as a string column
print("dtype before:", events["timestamp"].dtype)

# Right way: parse + set as index
events["timestamp"] = pd.to_datetime(events["timestamp"])
events = events.set_index("timestamp")
print("dtype after: ", events.index.dtype)
print(events)

dtype before: object
dtype after:  datetime64[ns]
                     user_id  duration
timestamp                             
2026-01-01 09:14:00        1        42
2026-01-01 09:47:00        2        18
2026-01-01 13:22:00        1         7
2026-01-02 08:05:00        3        31
2026-01-02 19:31:00        2        55
2026-01-03 11:18:00        1        12

object (a string) before, datetime64[ns] after — that single conversion is what unlocks everything below. When reading directly from CSV, do it in one step with parse_dates:

df = pd.read_csv("events.csv", parse_dates=["timestamp"], index_col="timestamp")

Now slicing by time strings just works:

import pandas as pd

events = pd.DataFrame({
    "user_id":  [1, 2, 1, 3, 2, 1],
    "duration": [42, 18, 7, 31, 55, 12],
}, index=pd.to_datetime([
    "2026-01-01 09:14:00",
    "2026-01-01 09:47:00",
    "2026-01-01 13:22:00",
    "2026-01-02 08:05:00",
    "2026-01-02 19:31:00",
    "2026-01-03 11:18:00",
]))
events.index.name = "timestamp"

# String slicing — partial dates work
print("Jan 1 only:\n", events.loc["2026-01-01"])
print("\nFirst two days:\n", events.loc["2026-01-01":"2026-01-02"])

Jan 1 only:
                      user_id  duration
timestamp                             
2026-01-01 09:14:00        1        42
2026-01-01 09:47:00        2        18
2026-01-01 13:22:00        1         7

First two days:
                      user_id  duration
timestamp                             
2026-01-01 09:14:00        1        42
2026-01-01 09:47:00        2        18
2026-01-01 13:22:00        1         7
2026-01-02 08:05:00        3        31
2026-01-02 19:31:00        2        55

"2026-01-01" matched every row in that day without you specifying hours — partial-string indexing, a DatetimeIndex superpower a plain string column could never give you.

Resample to a different frequency

.resample('D') says “bucket rows by day, then apply an aggregation.” It is the single most useful time-series method in pandas:

import pandas as pd
import numpy as np

rng = np.random.default_rng(0)

# ~21 days of synthetic event logs, ~72 events per day (every 20 min)
n = 1500
ts = pd.date_range("2026-01-01", periods=n, freq="20min")
df = pd.DataFrame({
    "user_id":  rng.integers(1, 100, n),
    "duration": rng.exponential(30, n).round(1),
}, index=ts)

# Daily total events and total duration
daily = df.resample("D").agg(
    events=("user_id", "count"),
    total_min=("duration", "sum"),
    unique_users=("user_id", "nunique"),
)
print(daily.head(7))

            events  total_min  unique_users
2026-01-01      72     2240.9            51
2026-01-02      72     2001.9            49
2026-01-03      72     2346.0            52
2026-01-04      72     1751.0            53
2026-01-05      72     2252.9            53
2026-01-06      72     2416.6            52
2026-01-07      72     2182.9            55

A 1,500-row, 20-minute log collapsed into one row per day — 72 events each (1440 min ÷ 20), summed duration, unique users — exactly the daily rollup a dashboard needs. The frequency strings are the same alphabet you use everywhere in pandas:

• 'D' daily, 'B' business day
• 'h', 'min', 's' — hourly, minutely, second
• 'W' weekly, 'W-MON' weekly anchored on Monday
• 'ME' month-end, 'MS' month-start
• '5min', '15min' — any multiple

Rolling windows — the 7-day moving average

When daily counts are noisy, a 7-day rolling mean smooths them out and reveals the trend:

import pandas as pd
import numpy as np

rng = np.random.default_rng(0)
ts = pd.date_range("2026-01-01", periods=60, freq="D")
dau = pd.Series(
    150 + 50 * np.sin(np.arange(60) / 7) + rng.normal(0, 15, 60),
    index=ts, name="DAU",
).round().astype(int)

# 7-day rolling mean — the classic "smooth out weekday seasonality" trick
dau_7d = dau.rolling(7).mean()

# Expanding mean — cumulative average from the start
dau_expanding = dau.expanding().mean()

out = pd.concat([dau, dau_7d.round(1), dau_expanding.round(1)], axis=1)
out.columns = ["DAU", "rolling_7", "expanding"]
print(out.head(14))

            DAU  rolling_7  expanding
2026-01-01  152        NaN      152.0
2026-01-02  155        NaN      153.5
2026-01-03  174        NaN      160.3
2026-01-04  172        NaN      163.2
2026-01-05  169        NaN      164.4
2026-01-06  188        NaN      168.3
2026-01-07  207      173.9      173.9
2026-01-08  206      181.6      177.9
2026-01-09  185      185.9      178.7
2026-01-10  179      186.6      178.7
2026-01-11  190      189.1      179.7
2026-01-12  201      193.7      181.5
2026-01-13  165      190.4      180.2
2026-01-14  195      188.7      181.3

rolling(7) looks backwards — the value on Jan 7 (173.9) is the mean of Jan 1–7, which is why the first six days are NaN (no full window yet) and why rolling_7 is so much smoother than the jumpy raw DAU. The expanding mean instead averages everything from the start (so it has a value on day 1 and settles slowly). Pass min_periods=1 if you want partial windows filled rather than NaN.

The .dt accessor — pulling out components

For a datetime column (not index), .dt exposes year, month, day, weekday, hour, and friends:

import pandas as pd

ts = pd.date_range("2026-01-01", periods=10, freq="36h")
s = pd.Series(ts, name="timestamp")

print("year:    ", s.dt.year.tolist())
print("month:   ", s.dt.month.tolist())
print("weekday: ", s.dt.day_name().tolist())
print("hour:    ", s.dt.hour.tolist())
print("is_month_end:", s.dt.is_month_end.tolist())

year:     [2026, 2026, 2026, 2026, 2026, 2026, 2026, 2026, 2026, 2026]
month:    [1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
weekday:  ['Thursday', 'Friday', 'Sunday', 'Monday', 'Wednesday', 'Thursday', 'Saturday', 'Sunday', 'Tuesday', 'Wednesday']
hour:     [0, 12, 0, 12, 0, 12, 0, 12, 0, 12]
is_month_end: [False, False, False, False, False, False, False, False, False, False]

Stepping every 36 hours flips the hour between 00:00 and 12:00 and walks the weekday forward — and day_name(), is_month_end, and the rest are exactly the calendar features you engineer for a model (“is this a weekend?”, “what hour did it happen?”). For a DataFrame with a DatetimeIndex, use df.index.month, df.index.dayofweek, etc. — no .dt needed, the index exposes them directly.

Timezones briefly

Naive datetimes carry no timezone. To give them one:

df.index = df.index.tz_localize("UTC")              # tag a naive index as UTC
df.index = df.index.tz_convert("America/New_York")  # convert UTC → ET

The rule: localize once (tag the timestamps with the zone they are in), then convert as needed. Mixing tz-aware and tz-naive data raises errors — which is exactly what you want.

In one breath

Time-series power starts with a real DatetimeIndex (pd.to_datetime + set_index, or read_csv(parse_dates=, index_col=)) — a string column won’t do. Then: partial-string slicing (df.loc["2026-01"]), resample('D') to bucket rows into a coarser frequency and aggregate (the daily-rollup workhorse; freq alphabet D/h/min/W/ME, lowercase in pandas 2.2+), and rolling(7) for a trailing moving average (first n−1 are NaN) versus expanding() for a cumulative one. The .dt accessor (.dt.year, .dt.day_name(), .dt.is_month_end) extracts calendar features from a datetime column, while a DatetimeIndex exposes them directly. Timezones: localize once, then convert.

Practice

A question to carry forward

Step back and look at the shape of every worked example across this whole pandas section. Read a file → filter rows → group → aggregate → reshape → resample. Each step either reassigned a temporary variable (df = df.something(), over and over) or buried calls inside calls. It works, but it reads like a pile of statements rather than a single thought.

There is a cleaner way to write a whole transformation — one readable, top-to-bottom chain: df.query(...).assign(...).groupby(...).agg(...), no throwaway variables, each line a verb. The next lesson is method chaining: how .assign, .pipe, and .query let you compose an entire pipeline as one fluent expression, why production pandas codebases prefer it, and the one place (debugging) where breaking the chain still pays.