Pandas Time Series Cheat Sheet
Covers datetime indexing, resampling, rolling windows, and shifting operations for analyzing and transforming time series data in pandas.
2 PagesIntermediateMar 5, 2026
Datetime Parsing & Indexing
Turn a date column into a queryable DatetimeIndex.
python
import pandas as pddf["date"] = pd.to_datetime(df["date"])df = df.set_index("date").sort_index()# Date range generationdates = pd.date_range(start="2024-01-01", end="2024-12-31", freq="D")# Extract componentsdf["year"] = df.index.yeardf["month"] = df.index.monthdf["day_of_week"] = df.index.dayofweek# Slice by date rangedf.loc["2024-03"] # all of March 2024df.loc["2024-01-01":"2024-06-30"]
Resampling
Aggregate a time series into fixed-frequency bins.
python
# Downsample daily -> monthly totalsmonthly = df["sales"].resample("ME").sum() # 'ME' = month end# Downsample to weekly averageweekly = df["sales"].resample("W").mean()# Upsample and forward-fillhourly = df["sales"].resample("h").ffill()# groupby + resample togetherdf.groupby("store").resample("ME")["sales"].sum()
Rolling Windows & Shifting
Compute moving averages, lags, and period-over-period change.
python
df["rolling_7d_avg"] = df["sales"].rolling(window=7).mean()df["rolling_7d_std"] = df["sales"].rolling(window=7).std()df["expanding_sum"] = df["sales"].expanding().sum()df["sales_lag1"] = df["sales"].shift(1) # previous perioddf["sales_pct_change"] = df["sales"].pct_change()df["sales_ewm"] = df["sales"].ewm(span=7).mean() # exponential weighted moving avg
Key Concepts
Core building blocks of time series work in pandas.
- DatetimeIndex- Index type enabling label-based date slicing, resampling, and time-aware alignment
- resample()- Groups time series data into fixed frequency bins (e.g. 'D', 'W', 'ME') and aggregates
- asfreq()- Converts to a specified frequency without aggregation, inserting NaN for missing periods
- rolling()- Applies a function over a sliding window (e.g. a 7-day moving average)
- shift()- Shifts values forward/backward by n periods, used for lag features and period-over-period change
- Timezone handling- Use tz_localize() to assign a timezone and tz_convert() to convert between timezones
- Freq aliases- Common: 'D' day, 'W' week, 'ME' month end, 'QE' quarter end, 'YE' year end, 'h' hour
Timezone Localization & Conversion
Attach, convert, and safely compare timezone-aware timestamps.
python
# Localize naive timestamps to a timezone, then convertdf.index = df.index.tz_localize("America/New_York")df_utc_index = df.index.tz_convert("UTC")# Handle ambiguous/nonexistent times during DST transitionsdf.index = df.index.tz_localize( "America/New_York", ambiguous="infer", nonexistent="shift_forward")# Comparing tz-naive vs tz-aware raises TypeError - always align firstts_naive = pd.Timestamp("2024-03-10 02:30:00")ts_aware = pd.Timestamp("2024-03-10 02:30:00", tz="UTC")ts_naive.tz_localize("UTC") == ts_aware # True
Business-Day Offsets & Holiday Calendars
Advance dates and build reindex grids that respect trading/holiday calendars.
python
from pandas.tseries.offsets import BDay, CustomBusinessDayfrom pandas.tseries.holiday import USFederalHolidayCalendar# Shift by business days, skipping weekendsnext_biz_day = pd.Timestamp("2024-07-04") + BDay(1)# Custom calendar that also skips US federal holidaysus_cal = CustomBusinessDay(calendar=USFederalHolidayCalendar())trading_days = pd.date_range("2024-01-01", "2024-01-15", freq=us_cal)# Reindex a series onto a business-day calendar, filling small gapsdf = df.reindex(pd.date_range(df.index.min(), df.index.max(), freq=us_cal))df["sales"] = df["sales"].ffill(limit=2)
PeriodIndex for Time Spans
Represent and aggregate calendar spans rather than instants.
python
# Period represents a *span* of time (e.g. "March 2024"), not an instantdf["period"] = df.index.to_period("M")monthly = df.groupby("period")["sales"].sum()p = pd.Period("2024-03", freq="M")p.start_time, p.end_time # first/last timestamp in the periodp + 1 # next period (April 2024)# Convert back to a DatetimeIndex anchored at each period's startmonthly.index = monthly.index.to_timestamp()
merge_asof() & Grid Reindexing
Nearest-time joins and interpolation onto a fixed time grid.
python
# Nearest-match join on sorted time keys (e.g. trades vs quotes)merged = pd.merge_asof( trades.sort_values("time"), quotes.sort_values("time"), on="time", by="ticker", direction="backward", tolerance=pd.Timedelta("2s"),)# Reindex onto a fixed hourly grid with controlled fillgrid = pd.date_range("2024-01-01", "2024-01-31", freq="h")df = df.reindex(grid, method="nearest", limit=1)# Interpolate gaps using time-aware spacing (not just row count)df["sales"] = df["sales"].interpolate(method="time")
Advanced Time Series Concepts
Terminology and tools for deeper time series analysis.
- Stationarity- A series whose mean/variance don't drift over time; test with statsmodels' adfuller() before fitting ARIMA-style models
- Seasonal decomposition- statsmodels.tsa.seasonal_decompose() splits a series into trend, seasonal, and residual components
- ACF / PACF- Autocorrelation and partial autocorrelation measure a series' correlation with its own lags; used to pick AR/MA orders
- to_period() / to_timestamp()- Convert between an instant-based DatetimeIndex and a span-based PeriodIndex
- Timedelta arithmetic- pd.Timedelta and .dt.total_seconds() compute durations between timestamps
- Anchored offsets- Freq strings like 'W-MON' or 'QS-JAN' anchor a period boundary to a specific weekday/month
- Multi-entity panels- pd.MultiIndex.from_product([tickers, dates]) stacks multiple time series (e.g. per-ticker) into one frame
Pro Tip
When resampling irregular timestamped event data, resample() to a fixed frequency first before applying rolling() - rolling() operates on row count by default, not elapsed time, unless you pass a time-based window like rolling('7D').
Was this cheat sheet helpful?
Explore Topics
#PandasTimeSeries#PandasTimeSeriesCheatSheet#DataScience#Intermediate#DatetimeParsingIndexing#Resampling#RollingWindowsShifting#KeyConcepts#Databases#MachineLearning#CheatSheet#SkillVeris
