Contents
Figure 1: Pandas Hourly Data: Calendar Features and a Leak-Free Split — the whole pipeline from a messy CSV to a walk-forward score you can trust
You have a CSV of hourly readings and a deadline. You need calendar features, lag features, and a validation split that does not cheat. Googling each piece separately wastes an afternoon.
This guide gives you the whole pipeline as copy-paste pandas code: clean the index, add calendar and cyclical features, build leak-free lags, split by time, and validate with walk-forward folds. I ran every block in order on a three-year hourly dataset, so the numbers below are real. The run used pandas 3.0.6, scikit-learn 1.9.1 and numpy 2.5.3.
What You Will Build
By the end, you will have a workflow with five parts:
- A clean hourly index with no duplicates or gaps.
- Calendar features like hour, weekday, month, weekend and holiday flags.
- Cyclical encodings so midnight sits next to 11 PM.
- Lag and rolling features that use only the past.
- A time-based train, validation and test split, plus walk-forward cross-validation.
Replace the demo data with your own file, and the rest runs as written.
Step 1: Load or Generate Hourly Data
I generated three years of hourly demand with a daily cycle, a weekday bump, a yearly cycle, and noise. In your project, swap the generator for pd.read_csv with parse_dates and index_col.
import numpy as np
import pandas as pd
from pandas.tseries.holiday import USFederalHolidayCalendar
from sklearn.ensemble import HistGradientBoostingRegressor
from sklearn.metrics import mean_absolute_error
from sklearn.model_selection import TimeSeriesSplit
# ---- Demo data (replace with your file)
rng = np.random.default_rng(0)
idx = pd.date_range('2023-01-01', '2025-12-31 23:00', freq='h')
h, dow, doy = idx.hour.values, idx.dayofweek.values, idx.dayofyear.values
demand = (100 + 25 * np.sin(2 * np.pi * (h - 14) / 24)
+ 10 * (dow < 5)
+ 15 * np.sin(2 * np.pi * (doy - 30) / 365.25)
+ rng.normal(0, 4, len(idx)))
df = pd.DataFrame({'demand': demand}, index=idx)
df.index.name = 'timestamp'
# ---- Your real data would look like this:
# df = pd.read_csv('data.csv', parse_dates=['timestamp'], index_col='timestamp')
The demo frame has 26,304 rows, from January 1, 2023 to December 31, 2025. Everything below needs a DatetimeIndex, so confirm yours with df.index.dtype before you continue.
Step 2: Clean the Index First
Hourly data arrives messy. Duplicated timestamps, missing hours and unsorted rows break lag features silently, because shift(24) only means "24 hours ago" when every hour exists exactly once.
assert df.index.is_monotonic_increasing, 'sort your data first'
print('duplicates:', df.index.duplicated().sum())
df = df[~df.index.duplicated(keep='first')].sort_index()
full = pd.date_range(df.index.min(), df.index.max(), freq='h')
print('missing hours:', len(full.difference(df.index)))
df = df.asfreq('h') # inserts NaN rows for any missing hours
My demo data had no duplicates and no missing hours. Real data usually does. After asfreq('h'), decide how to treat the NaN rows. Forward fill is safe, but backward fill leaks the future. For long gaps, leave the NaN and drop those rows instead of inventing data.
Step 3: Add Calendar Features
Calendar features come straight from the index. They never leak, because you know tomorrow's date today.
def add_calendar(d):
d = d.copy()
ix = d.index
d['hour'] = ix.hour
d['dayofweek'] = ix.dayofweek # Monday = 0
d['month'] = ix.month
d['dayofyear'] = ix.dayofyear
d['weekofyear'] = ix.isocalendar().week.astype(int).values
d['is_weekend'] = (ix.dayofweek >= 5).astype(int)
d['is_month_start'] = ix.is_month_start.astype(int)
holidays = USFederalHolidayCalendar().holidays(ix.min(), ix.max())
d['is_holiday'] = ix.normalize().isin(holidays).astype(int)
return d
Two details deserve a note. First, isocalendar().week returns a nullable integer type, so the .astype(int).values call keeps the result clean. Second, USFederalHolidayCalendar covers US federal holidays only [3]. Other countries need their own calendar, and the third-party holidays package covers many of them.
Step 4: Add Cyclical Encodings
Hour 23 and hour 0 sit one hour apart, but the raw numbers sit 23 apart. Sine and cosine fix that by placing each value on a circle.
# (inside add_calendar, before 'return d')
for col, period in [('hour', 24), ('dayofweek', 7), ('month', 12)]:
offset = 1 if col == 'month' else 0 # months run 1 to 12
angle = 2 * np.pi * (d[col] - offset) / period
d[f'{col}_sin'] = np.sin(angle)
d[f'{col}_cos'] = np.cos(angle)
The month offset matters: January maps to angle 0 only after you subtract 1. Tree models like the one below do not need these columns, since they split raw hours happily. Keep them anyway if you also plan to try linear or neural models, and drop them if you only use trees.
Step 5: Add Lag and Rolling Features Without Leaking
Hourly series repeat on daily and weekly rhythms, so the most useful lags are 1 hour, 24 hours and 168 hours (one week) [4]. Rolling statistics summarize recent history.
def add_lags(d, target='demand'):
d = d.copy()
for lag in (1, 24, 168):
d[f'lag_{lag}'] = d[target].shift(lag)
past = d[target].shift(1) # shift FIRST, then roll
d['roll_24_mean'] = past.rolling(24).mean()
d['roll_168_mean'] = past.rolling(168).mean()
d['roll_24_std'] = past.rolling(24).std()
return d
feat = add_lags(add_calendar(df)).dropna()
The shift(1) before rolling guarantees each window ends at the previous hour. Skip it, or use center=True, and the feature includes the very value you try to predict. The dropna() removes the first 168 rows, where the weekly lag has no history. My feature table ended with 26,136 rows and 21 columns, including the target.
One limitation: these lags assume you predict one hour ahead, with yesterday's and last week's values known. If you forecast a full day ahead, a lag of 1 hour would not exist at prediction time. Use a minimum lag at least as large as your forecast horizon.
Step 6: Split by Time, Not at Random
Now the split. Use fixed date cutoffs: train on the oldest data, validate on the next block, and keep the newest block as an untouched test set.
train = feat.loc[:'2024-12-31 23:00']
valid = feat.loc['2025-01-01':'2025-09-30 23:00']
test = feat.loc['2025-10-01':]
assert train.index.max() < valid.index.min() < test.index.min()
The sizes in my run were 17,376 training rows (January 8, 2023 to December 31, 2024), 6,552 validation rows (January through September 2025), and 2,208 test rows (October through December 2025). Note that .loc slices on a datetime index include the end label, so write the cutoffs deliberately [1]. The assert line catches overlap mistakes before they cost you anything.
Step 7: Train, Validate, and Check Baselines
Fit on the training block and evaluate on validation. Always compare against naive baselines, which are free.
target = 'demand'
X_cols = [c for c in feat.columns if c != target]
model = HistGradientBoostingRegressor(random_state=0, max_iter=300)
model.fit(train[X_cols], train[target])
print('model MAE:', mean_absolute_error(valid[target], model.predict(valid[X_cols])))
print('same hour yesterday:', mean_absolute_error(valid[target], valid['lag_24']))
print('same hour last week:', mean_absolute_error(valid[target], valid['lag_168']))
My results:
| Forecast | Validation MAE |
|---|---|
| Gradient boosting model | 3.46 |
| Same hour yesterday | 6.13 |
| Same hour last week | 4.63 |
The model beats both baselines comfortably. One sanity check helps here. My synthetic noise had a standard deviation of 4, and a perfect model facing normal noise with that spread would still average an error near 3.2. So 3.46 sits close to the floor, which tells you the model captured the structure. On real data you will not know the floor, so lean on the baselines instead.
Refit and Test Once
After you finish tuning, retrain on training plus validation, then score the test block a single time.
trval = feat.loc[:'2025-09-30 23:00']
model.fit(trval[X_cols], trval[target])
print('test MAE:', mean_absolute_error(test[target], model.predict(test[X_cols])))
The test MAE came out at 3.37, in line with validation. If your test score differs wildly from validation, suspect leakage or a regime change.
Step 8: Walk-Forward Cross-Validation With a Gap
One validation block gives you one number. TimeSeriesSplit gives you several by sliding the test window forward [5]. Add a gap so training labels do not overlap with test rows.
tscv = TimeSeriesSplit(n_splits=5, test_size=24 * 30, gap=24)
maes = []
for tr, te in tscv.split(feat):
m = HistGradientBoostingRegressor(random_state=0, max_iter=150)
m.fit(feat.iloc[tr][X_cols], feat.iloc[tr][target])
pred = m.predict(feat.iloc[te][X_cols])
maes.append(mean_absolute_error(feat.iloc[te][target], pred))
print(maes, sum(maes) / len(maes))
Each fold tests on 30 days (720 hours). My five folds scored 3.38, 3.43, 3.57, 3.32 and 3.45, with a mean of 3.43. The spread stays small, which suggests stable performance across the last five months. In the first fold, training ended on August 2 at 11 PM and testing began on August 4 at midnight, so the 24-hour gap worked as intended.
Step 9: Handle Time Zones and Daylight Saving
Hourly data and daylight saving time (DST) clash. In a DST zone, a local day sometimes has 23 hours and sometimes 25, and a local clock time sometimes occurs twice or not at all.
I tested these behaviours for America/New_York in 2025:
- Spring forward (March 9): the local day has 23 hours.
- Fall back (November 2): the local day has 25 hours.
- Ambiguous times: localising a naive 1:30 AM on November 2 raises an error, because that time occurs twice.
- Nonexistent times: localising 2:30 AM on March 9 raises an error, because the clock skips it.
Here is the safe handling:
# Best practice: store and sort timestamps in UTC
idx_utc = pd.to_datetime(df.index, utc=True)
# Build features from LOCAL time, because people behave by the local clock
local = idx_utc.tz_convert('America/New_York')
hour_local = local.hour
# If you must localize naive timestamps:
naive = pd.DatetimeIndex(['2025-11-02 00:30', '2025-11-02 01:30', '2025-11-02 01:30'])
fixed = naive.tz_localize('America/New_York', ambiguous='infer')
gap_times = pd.DatetimeIndex(['2025-03-09 01:30', '2025-03-09 02:30'])
shifted = gap_times.tz_localize('America/New_York', nonexistent='shift_forward')
The ambiguous='infer' option works when the repeated times appear in order, and nonexistent='shift_forward' moved the missing 2:30 AM to 3:00 AM in my test [2].
Three rules keep you safe. Keep the index in UTC for lags and splits, because UTC has no gaps or repeats. Compute calendar features from local time, because demand follows the local clock. And never mix tz-aware and tz-naive indexes in one comparison, since pandas raises an error.
A quick example shows why local time matters. A UTC timestamp at 00:00 on January 1 has a UTC hour of 0 but a New York hour of 19. A model that uses the UTC hour for human behaviour learns a shifted, and shifting, daily cycle.
Quick Reference
Keep this order in your own projects:
- Clean the index: sort, drop duplicates,
asfreq('h'). - Pick a time zone strategy: UTC for the index, local time for features.
- Add calendar features: hour, weekday, month, weekend, holiday.
- Add cyclical encodings when your model needs them.
- Add lags and rolling stats, shifting before rolling, with lags at least as long as your horizon.
- Split by date cutoffs: train, validation, test.
- Compare against naive baselines.
- Cross-validate with
TimeSeriesSplit, using a gap and a fixed test size. - Refit on train plus validation, then test once.
Common Pitfalls
- Using
train_test_spliton hourly data. It shuffles rows and leaks the future. - Rolling before shifting. The window includes the target hour.
- Skipping
asfreq. Missing hours silently break everyshift. - Backward filling gaps. It copies future values into the past.
- Building hour features from UTC when behaviour follows local time. The model learns the wrong cycle.
- Forgetting that lags must exist at prediction time. A 1-hour lag fails for day-ahead forecasts.
- Encoding months from 1 without an offset. January and December then overlap incorrectly.
Wrapping Up
The whole pipeline fits on one screen: clean the index, add calendar and cyclical features, shift before you roll, split by date, and validate walk-forward with a gap. On my demo data it beat the best naive baseline by about a quarter (3.46 against 4.63) and landed close to the noise floor.
So paste the blocks in order, point them at your own CSV, and check three numbers: the missing-hour count, the baseline errors, and your first validation score. If the baselines look strong, your model has work to do, and now you have an honest way to measure it.
Frequently Asked Questions
Why can't I use train_test_split on hourly data?
It shuffles rows by default, so future hours land in training and past hours land in testing. Lag features then hand the model values from the test period, and your validation error comes out too low. Split by date cutoffs or use TimeSeriesSplit instead.
How big should the gap be in TimeSeriesSplit for hourly data?
At least as many hours as your forecast horizon, plus the longest lookback in your features if you want to be strict. For a one-hour-ahead model a gap of 24 is already generous; for a day-ahead model a gap of 24 is the minimum. The gap is counted in rows, not days.
Should I forward fill missing hours?
Forward fill is safe because it copies a past value into the future, while backward fill copies a future value into the past and leaks. For long gaps, leave the NaN and drop those rows rather than inventing data. Never interpolate across a gap that spans your train/test boundary.
Should I build hour features from UTC or local time?
Build calendar features from local time, because demand follows the local clock, but keep the index in UTC for lags and splits, because UTC has no gaps or repeats. A UTC timestamp at 00:00 on January 1 is 19:00 the previous day in New York, so a UTC hour column teaches the model a shifted daily cycle.
Do I need cyclical encoding for a gradient boosting model?
Usually not. Trees split the raw hour axis into ranges and handle the wrap-around well enough. Keep the sine and cosine columns if you also plan to try linear or neural models, and drop them if you only use trees. They cost two columns per cycle, so the price of keeping them is small.
Sources
[1]pandas, Time series / date functionality — the user guide forDatetimeIndex,asfreq, partial string indexing with.loc, and the time zone handling rules that the cleanup and split steps rely on: pandas.pydata.org/docs/user_guide/timeseries.html[2]pandas, DatetimeIndex.tz_localize — the API reference for theambiguousandnonexistentarguments, including theinferandshift_forwardbehaviours used above: pandas.pydata.org/docs/reference/api/pandas.DatetimeIndex.tz_localize.html[3]pandas, USFederalHolidayCalendar — the holiday calendar used for theis_holidayflag, and the entry point for the other country and market calendars pandas ships with: pandas.pydata.org/docs/reference/api/pandas.tseries.holiday.USFederalHolidayCalendar.html[4]pandas, DataFrame.shift — the method behind every lag feature, including thefreqargument you should avoid when your index has gaps: pandas.pydata.org/docs/reference/api/pandas.DataFrame.shift.html[5]scikit-learn, TimeSeriesSplit — the cross-validator behind the walk-forward loop, withn_splits,test_size,gapandmax_train_size: scikit-learn.org/stable/modules/generated/sklearn.model_selection.TimeSeriesSplit.html