Time Series Analysis with Pandas: Working with Dates and Trends

Time series data is everywhere — stock prices, server logs, sensor readings, website traffic, and sales records all carry a timestamp. Working with dates effectively often determines whether your analysis reveals real patterns or just noise.

Pandas has excellent time series support built right into its DataFrame and Series objects. This post covers the most practical patterns for everyday time series work.

Parsing Dates on Import

The single biggest time-saver is parsing dates at load time rather than converting them later:

import pandas as pd

# Parse date column directly when reading
df = pd.read_csv("sales.csv", parse_dates=["date"], index_col="date")

# Or parse multiple date columns
df = pd.read_csv("logs.csv", parse_dates=["created_at", "updated_at"])

Setting the date column as the index unlocks Pandas’ time-based operations — slicing, resampling, and rolling windows — without manual iteration.

Setting Up a DatetimeIndex

If your data is already loaded without parsing, convert the column explicitly:

df["date"] = pd.to_datetime(df["date"])
df = df.set_index("date")

pd.to_datetime() handles most common formats automatically — ISO 8601, YYYY-MM-DD, MM/DD/YYYY, and even mixed formats within a single column. For unusual formats, pass a format string:

df["date"] = pd.to_datetime(df["date"], format="%d-%m-%Y")

Using format is up to 5x faster on large datasets because Pandas skips the format inference step.

Slicing by Date

With a DatetimeIndex, date-based slicing is intuitive and fast:

# All of 2025
df_2025 = df.loc["2025"]

# A specific month
df_march = df.loc["2025-03"]

# A date range
df_range = df.loc["2025-01-01":"2025-06-30"]

The slicing is inclusive on both ends, which matches how most people think about date ranges. Partial strings work — "2025-03" expands to "2025-03-01" through "2025-03-31".

Resampling: Changing Time Frequencies

Resampling aggregates data from one frequency to another. This is one of Pandas’ most powerful time series features:

# Daily → Monthly average
monthly = df.resample("M")["revenue"].mean()

# Daily → Weekly sum
weekly = df["revenue"].resample("W").sum()

# Hourly → Daily max
daily_max = df.resample("D")["cpu_usage"].max()

Common frequency aliases:

For custom aggregations, pass a dict or a function:

summary = df.resample("M").agg({
    "revenue": "sum",
    "units_sold": "sum",
    "customer_count": "mean",
})

Rolling Windows

Rolling windows compute statistics over a sliding window of observations — essential for smoothing noisy data or detecting trends:

# 7-day rolling average
df["revenue_sma_7"] = df["revenue"].rolling(window=7).mean()

# 30-day rolling standard deviation
df["revenue_std_30"] = df["revenue"].rolling(window=30).std()

# Rolling with minimum periods (useful for sparse data)
df["revenue_sma_7"] = df["revenue"].rolling(window=7, min_periods=3).mean()

The min_periods parameter is important at the start of your series — without it, the first six rows in a 7-day window would be NaN.

Expanding Windows

An expanding window uses all data from the start to the current point — useful for cumulative statistics:

df["cumulative_revenue"] = df["revenue"].expanding().sum()
df["running_avg"] = df["revenue"].expanding().mean()

Shifting and Lag Features

Lag features — values from previous time steps — are fundamental for forecasting models:

# Revenue from previous day
df["revenue_lag_1"] = df["revenue"].shift(1)

# Revenue from 7 days ago
df["revenue_lag_7"] = df["revenue"].shift(7)

# Day-over-day change
df["revenue_diff"] = df["revenue"].diff()

# Percentage change
df["revenue_pct_change"] = df["revenue"].pct_change()

Shift is also useful for calculating leads (future values), though be careful with lookahead bias in modeling contexts.

Extracting Date Components

Sometimes you need the day of week, month, or quarter as a separate feature:

df["year"] = df.index.year
df["month"] = df.index.month
df["day"] = df.index.day
df["dayofweek"] = df.index.dayofweek  # Monday=0, Sunday=6
df["quarter"] = df.index.quarter
df["is_weekend"] = df.index.dayofweek.isin([5, 6])

These features let you model seasonality — for example, identifying that weekends consistently have 30% lower traffic than weekdays.

Handling Irregular Time Series

Real-world timestamps are rarely perfectly spaced. Missing business days, holidays, and off-hours all create gaps. Two practical approaches:

Forward Fill

# Fill missing dates with last known value
df = df.asfreq("D").ffill()

Reindex to a Complete Calendar

# Create a complete date range and reindex
full_range = pd.date_range(start=df.index.min(), end=df.index.max(), freq="D")
df = df.reindex(full_range).ffill()

asfreq changes the frequency and inserts NaN for missing dates; chaining .ffill() fills gaps with the last valid observation. This is the standard approach for creating evenly-spaced time series from irregular logs.

A Complete Workflow Example

Here’s how these pieces fit together in practice:

import pandas as pd
import matplotlib.pyplot as plt

# Load and parse
df = pd.read_csv("server_logs.csv", parse_dates=["timestamp"], index_col="timestamp")

# Resample to daily max CPU
daily = df["cpu_pct"].resample("D").max()

# 7-day rolling median (more robust to spikes than mean)
daily_smooth = daily.rolling(7, center=True).median()

# Plot raw vs smoothed
fig, ax = plt.subplots(figsize=(10, 5))
daily.plot(alpha=0.4, label="Daily max CPU", ax=ax)
daily_smooth.plot(linewidth=2, label="7-day rolling median", ax=ax)
ax.set_title("Server CPU — Daily Max with Trend")
ax.legend()
plt.show()

The smoothed line reveals the underlying trend — a gradual increase over time — that the daily spikes obscure.

Conclusion

Time series analysis is where Pandas truly shines. A well-structured DatetimeIndex, combined with resampling, rolling windows, and shift operations, handles the vast majority of temporal data tasks you’ll encounter. The patterns above cover parsing, aggregation, smoothing, and feature engineering — enough to move from raw timestamped data to insights-ready features.

The key takeaway: treat your timestamps as first-class citizens from the moment you load the data. Parse them early, index on them, and let Pandas’ vectorized operations do the heavy lifting.

~ Kang Ifaz