Khansa, home
Work with me

Jump to

Pages

Projects

Links

Lesson 2 of 8, about 12 minutes

Prepare time series data in pandas: dates, gaps and resampling

Most forecasting problems are really data problems. Dates stored as text, missing days and blanks quietly break every model. Fix them first and the rest is easy.

By the end: Turn a messy file into a clean series with a proper date index.

Start with a messy file

Real files look like this: dates as text in day/month/year, two missing days and one blank value.

Python
import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
days = pd.date_range("2024-01-01", periods=365, freq="D")
trend = np.linspace(100, 140, 365)                 # sales slowly grow
week = np.array([-8, -6, -4, 0, 6, 16, 12])[days.dayofweek]  # busy weekends
s = pd.Series(trend + week + rng.normal(0, 4, 365), index=days, name="sales")

raw = s.reset_index()
raw.columns = ["date", "sales"]
raw["date"] = raw["date"].dt.strftime("%d/%m/%Y")   # dates as text, like a CSV
raw = raw.drop(index=[10, 11])                      # two missing days
raw.loc[40, "sales"] = np.nan                       # one blank value
raw.head()

Fix the dates and the index

Turn the text into real dates, make the date the index, and tell pandas the frequency. asfreq("D") adds the missing days back as blanks, so every day is present once.

Python
raw["date"] = pd.to_datetime(raw["date"], format="%d/%m/%Y")
clean = raw.set_index("date")["sales"].asfreq("D")

clean.isna().sum()        # 3 blanks: 2 missing days + 1 blank value

Fill the gaps

interpolate() draws a straight line across the gap. That is fine for a few missing points. For a long gap, ask why the data is missing instead of guessing.

Python
clean = clean.interpolate()
clean.isna().sum()        # 0

Change the time step and smooth the noise

resample() changes the frequency. Daily sales summed by week are easier to read. rolling() averages the last few days and smooths the noise away.

Python
weekly = clean.resample("W").sum()      # one number per week
smooth = clean.rolling(7).mean()        # average of the last 7 days
weekly.head(3)

You should see

date
2024-01-07    709.6
2024-01-14    726.6
2024-01-21    727.7

Where you will use this

  • Cleaning a sales export from an online shop before forecasting
  • Turning hourly sensor readings into daily averages
  • Checking that a report has one row per day, with no holes

Common mistake: Never fill gaps using values from the future. Interpolation inside the history is fine, but for features always use data from before the date you are predicting.

Try it yourself

Change resample("W") to resample("M") and read the monthly totals. Then change sum() to mean(). When is each one the right choice?

Check yourself

Why call asfreq("D")?

It makes sure every day appears exactly once. Missing days show up as blanks you can see and fix.

What does rolling(7).mean() do?

It replaces each day with the average of the last 7 days, which smooths out the noise.