Level 6 · Find and fill the tracking gaps

Missing rows are invisible

A missing value you can see is easy: it’s NaN, and isna() finds it. A missing row is harder. If the tracker had an outage on 28 January 2023, there simply is no paid row for that day, and nothing in the table tells you so. resample and rolling happily run over the hole, and a rolling(7) window quietly covers 8 calendar days.

The fix is to put the data on a complete calendar, so every day that should exist has a row, and the missing ones show up as NaN.

asfreq for one series

For a single series with a DatetimeIndex, asfreq("D") does it in one call:

paid = marketing_spend[marketing_spend["channel"] == "paid"].set_index("spend_date")["amount"]
len(paid)                 # 1091
filled = paid.asfreq("D")
len(filled)               # 1096
filled.isna().sum()       # 5

asfreq builds a regular daily index from the first to the last date and inserts NaN where there was no row. Three years is 1,096 days (2024 is a leap year), so the paid channel is missing 5 days.

reindex with date_range for many columns

asfreq only fills between the first and last date present. If the whole first week were missing, it couldn’t know. pd.date_range lets you state the expected calendar explicitly, and reindex puts the table on it:

days = pd.date_range("2023-01-01", "2025-12-31", freq="D")
wide = marketing_spend.pivot(index="spend_date", columns="channel", values="amount").reindex(days)
wide.isna().sum()
# email 4, paid 5, social 3

pivot turns the long table into one column per channel (chapter 9 covers it in depth), so all three channels get checked against the same calendar at once.

Choosing a fill

Once the gaps are visible, you decide what they mean. For paid spend, the daily average comes out differently depending on that choice:

• Skip them (the default for mean() and sum()): average $814.64 over the 1,091 recorded days.
• fillna(0): “nothing was spent”. Average $810.93 over 1,096 days. Wrong here: the money was spent, only the tracking failed.
• ffill(): carry the last known value forward. The total rises from $888,774.13 to $892,327.94.
• interpolate(): a straight line between the neighbours, total $892,420.97.

Zero is right when a missing day really means zero (no orders on a closed day). Forward fill or interpolation is right when the value existed but wasn’t recorded. Say which one you used.

Common mistakes

• Running rolling(7) on a series with missing days, so the window is longer than a week.
• Using fillna(0) for an outage, which understates spend and overstates return on ad spend.
• Using asfreq when gaps may sit at the very start or end of the range; reindex with an explicit date_range catches those.
• Forgetting that sum() and mean() skip NaN, so a table with gaps still “adds up”, just to the wrong number.