Pandas: Project: Time Series
Last updated: 2026-08-26
Time series forecasting is not just about modeling — before reaching for ML, understanding trend and seasonality alone can produce solid baseline predictions. In this lesson, we use Alice's coffee shop sales forecasting as a scenario, walking through the full pipeline from preprocessing to basic forecasting with resample + rolling + shift. This is a hands-on application of the tools from Lesson 17 and a prelude to the upcoming ML lessons.
⚠️ Note: The code below must be run in a local Python environment.
1. What You Will Learn
- ❶ Time series preprocessing
- ❷ Trend and seasonality extraction
- ❸ Moving average forecasting
- ❹ Seasonal decomposition
- ❺ Forecast evaluation (MAE/MAPE)
2. Project Background: Coffee Shop Sales Forecasting
(1) Task
Alice wants to forecast next month's coffee shop revenue — the historical data has a trend (steady month-over-month growth) and seasonality (more hot drinks in winter).
(2) Analysis Pipeline
graph TB
A["1. Preprocessing<br>resample+fillna"] --> B["2. Trend Extraction<br>rolling+diff"]
B --> C["3. Seasonality Analysis<br>groupby month"]
C --> D["4. Moving Average Forecast<br>rolling mean"]
D --> E["5. Evaluation<br>MAE/MAPE"]
E --> F["6. Next Month Forecast"]
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
3. Data Preprocessing
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
: Time Series Preprocessing (Difficulty ⭐⭐)
PYTHON
import pandas as pd
import numpy as np
# ============================================
# Step 1: Generate & Preprocess
# ============================================
np.random.seed(42)
# Generate 365 days of sales with trend + seasonality + noise
dates = pd.date_range('2023-01-01', periods=365)
trend = np.linspace(300, 450, 365) # upward trend
seasonality = 50 * np.sin(2 * np.pi * np.arange(365) / 365 * 2) # biannual cycle
winter_effect = np.where(dates.month.isin([11, 12, 1, 2]), 30, 0) # winter boost
noise = np.random.normal(0, 20, 365)
daily_sales = pd.DataFrame({
'sales': np.round(trend + seasonality + winter_effect + noise, 0),
'customers': np.round((trend + seasonality + winter_effect + noise) / 8, 0)
}, index=dates)
# Inject missing days
missing_idx = np.random.choice(365, 15, replace=False)
daily_sales.iloc[missing_idx] = np.nan
# Preprocess: fill missing → ensure daily frequency
daily_sales = daily_sales.asfreq('D') # ensure no missing dates
daily_sales['sales'] = daily_sales['sales'].interpolate(method='time')
daily_sales['customers'] = daily_sales['customers'].interpolate(method='time')
print(f"Date range: {daily_sales.index[0].date()} to {daily_sales.index[-1].date()}")
print(f"Missing after fill: {daily_sales.isnull().sum().sum()}")
print(f"\nDaily sales stats:\n{daily_sales['sales'].describe()}")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
4. Trend Extraction
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
: Trend and Detrending (Difficulty ⭐⭐)
PYTHON
# ============================================
# Step 2: Trend Extraction
# ============================================
# 30-day moving average = trend
daily_sales['trend'] = daily_sales['sales'].rolling(30, center=True).mean()
# Detrended = actual - trend (shows seasonality + noise)
daily_sales['detrended'] = daily_sales['sales'] - daily_sales['trend']
# Month-over-month growth rate
monthly = daily_sales['sales'].resample('M').sum()
monthly_growth = monthly.pct_change() * 100
print("=== Monthly Sales ===")
print(monthly.tail(6))
print(f"\nAvg monthly growth: {monthly_growth.mean():.1f}%")
print(f"Total annual growth: {((monthly.iloc[-1] / monthly.iloc[0]) - 1) * 100:.1f}%")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
5. Seasonality Analysis
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
: Seasonal Index (Difficulty ⭐⭐⭐)
PYTHON
# ============================================
# Step 3: Seasonality Analysis
# ============================================
# Monthly average (across all years)
daily_sales['month'] = daily_sales.index.month
monthly_avg = daily_sales.groupby('month')['sales'].mean()
# Seasonal index = monthly avg / overall avg
overall_avg = daily_sales['sales'].mean()
seasonal_index = (monthly_avg / overall_avg).round(3)
print("=== Seasonal Index ===")
for m, idx in seasonal_index.items():
label = "↑" if idx > 1.05 else ("↓" if idx < 0.95 else "→")
print(f" Month {m:2d}: {idx:.3f} {label}")
# Day of week pattern
daily_sales['dayofweek'] = daily_sales.index.dayofweek
dow_avg = daily_sales.groupby('dayofweek')['sales'].mean()
dow_names = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
print(f"\n=== Day of Week Avg ===")
for i, name in enumerate(dow_names):
print(f" {name}: ${dow_avg[i]:.0f}")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
6. Moving Average Forecast
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
: Rolling Forecast (Difficulty ⭐⭐⭐)
PYTHON
# ============================================
# Step 4: Moving Average Forecast
# ============================================
# Train/test split: last 30 days as test
train = daily_sales.iloc[:-30]
test = daily_sales.iloc[-30:]
# Simple moving average forecast (use last N days' avg)
def ma_forecast(train_series, window, horizon):
"""Forecast next 'horizon' days using moving average"""
last_ma = train_series.rolling(window).mean().iloc[-1]
return pd.Series([last_ma] * horizon,
index=pd.date_range(train.index[-1] + pd.Timedelta(days=1),
periods=horizon))
# Try different windows
for w in [7, 14, 30]:
forecast = ma_forecast(train['sales'], w, 30)
mae = (forecast - test['sales']).abs().mean()
mape = ((forecast - test['sales']) / test['sales']).abs().mean() * 100
print(f"MA({w:2d}): MAE=${mae:.0f}, MAPE={mape:.1f}%")
# Seasonal-adjusted forecast
# forecast = trend_forecast × seasonal_index
last_trend = train['sales'].rolling(30).mean().iloc[-1]
monthly_trend_growth = train['sales'].rolling(30).mean().diff(30).mean()
forecast_months = test.index.month
seasonal_forecast = pd.Series(
[(last_trend + monthly_trend_growth * i) * seasonal_index[m]
for i, m in enumerate(forecast_months)],
index=test.index
)
mae_seasonal = (seasonal_forecast - test['sales']).abs().mean()
mape_seasonal = ((seasonal_forecast - test['sales']) / test['sales']).abs().mean() * 100
print(f"\nSeasonal-adjusted: MAE=${mae_seasonal:.0f}, MAPE={mape_seasonal:.1f}%")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
7. Forecast Evaluation
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
: Evaluation and Next Month Forecast (Difficulty ⭐⭐)
PYTHON
# ============================================
# Step 5-6: Evaluation & Next Month Forecast
# ============================================
# Evaluation metrics
def evaluate(actual, forecast, name="Model"):
mae = (forecast - actual).abs().mean()
rmse = ((forecast - actual) ** 2).mean() ** 0.5
mape = ((forecast - actual) / actual).abs().mean() * 100
print(f"{name}: MAE=${mae:.0f}, RMSE=${rmse:.0f}, MAPE={mape:.1f}%")
return mae, rmse, mape
# Evaluate best model
print("=== Model Evaluation ===")
evaluate(test['sales'], ma_forecast(train['sales'], 14, 30), "MA(14)")
evaluate(test['sales'], seasonal_forecast, "Seasonal-adjusted")
# Next month forecast (Jan 2024)
next_month_days = pd.date_range('2024-01-01', periods=31)
last_trend_full = daily_sales['sales'].rolling(30).mean().iloc[-1]
jan_index = seasonal_index[1]
next_month_forecast = last_trend_full * jan_index
print(f"\n=== January 2024 Forecast ===")
print(f" Daily forecast: ${next_month_forecast:.0f}")
print(f" Monthly forecast: ${next_month_forecast * 31:,.0f}")
print(f" Based on trend: ${last_trend_full:.0f} × seasonal index: {jan_index:.3f}")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.
❓ FAQ
Q How do I choose the moving average window?
A Matching the window to the data's natural cycle is the safest bet. For daily data, a weekly cycle means window=7; a monthly cycle means window=30. Too small a window captures too much noise; too large a window introduces serious lag. Start with a window equal to the natural period, then try a few values above and below, comparing MAPE. In this lesson, MA(14) typically strikes a better balance than MA(7) or MA(30).
Q How do I separate trend from seasonality?
A Trend = long-term moving average (30+ days); seasonality = the ratio of each month's average to the overall average. Detrending means subtracting the trend from the raw values (additive model) or dividing the raw values by the trend (multiplicative model). Retail data usually calls for a multiplicative model (seasonal swings scale with the level), while temperature data fits an additive model.
Q What MAPE is considered good?
A MAPE < 10% is high accuracy, 10-20% is good, 20-50% is acceptable, and > 50% is unreliable. But context matters — for highly volatile daily sales, a 20% MAPE is already decent, and after aggregating to monthly totals, MAPE should drop below 10%. The further out you forecast, the less reliable it gets — a 7-day forecast is far more reliable than a 30-day one.
Q How far ahead can I reliably forecast?
A Rule of thumb: the reliable forecast horizon is roughly 1/3 to 1/2 of your historical data length. With one year of history, you can reliably forecast at most 4-6 months. Moving averages can only project a "flat" continuation — they cannot predict turning points. Trend shifts or unexpected events will invalidate the forecast entirely. Forecasting is a decision-support tool, not a crystal ball.
Q How does this differ from ML forecasting?
A Moving average forecasting assumes "the future is a continuation of the past" — it can only capture trend and seasonality. ML methods (ARIMA/LSTM/Prophet) can model far more complex patterns (nonlinear trends, multivariate interactions, holiday effects). This lesson serves as the ML baseline — if a simple moving average achieves 15% MAPE, an ML model should get below 10% to be worth the added complexity.
Q How is the seasonal index calculated?
A Divide each month's average by the overall average. For example, if January's average is 420 and the overall average is 380, the seasonal index is 420/380 = 1.105 (January runs 10.5% above average). An index > 1 indicates peak season; < 1 indicates off-season. You need at least 2 years of data for a reliable seasonal index (January can differ from year to year).
Q Should I use interpolate or ffill for missing values?
A For time series, interpolate is recommended — interpolating between adjacent values produces smoother results and preserves the trend. ffill carries the last known value forward, creating a staircase effect during gaps (trend discontinuity). For large gaps, use interpolate(method='time') to account for time distance; for small gaps, linear interpolation is sufficient.
📖 Summary
- Preprocessing: asfreq ensures consistent frequency, then interpolate fills missing values
- Trend: rolling(30) moving average; diff reveals the rate of change
- Seasonality: monthly average / overall average = seasonal index
- Forecasting: moving average (simple) → trend × seasonal index (improved)
- Evaluation: MAE (absolute error) / RMSE (root mean square) / MAPE (percentage)
- Reliable forecast horizon ≈ 1/3 of historical data length; moving average serves as the ML baseline
📝 Exercises
- Basic (Difficulty ⭐): Generate 180 days of sales data (with a trend), aggregate to monthly totals with resample('M'), and calculate the month-over-month growth rate.
- Intermediate (Difficulty ⭐⭐): Generate 365 days of data with seasonality, extract a 30-day trend line, compute the 12-month seasonal index, and identify peak and off seasons.
- Challenge (Difficulty ⭐⭐⭐): Complete a full forecasting project: preprocessing → trend + seasonality → MA(7/14/30) forecasts → seasonal-adjusted forecast → MAPE evaluation → next month forecast output.
← Previous: Project - Data Analysis · Next: Project - Comprehensive Analysis →
