Missing Dates & Gaps

By the end of this lesson you will be able to find every missing day in a series, fill the gaps in a way that matches what the numbers mean, and clean up duplicate dates. Forecasting methods expect one row per step, with no holes, so this is the last cleaning job before modelling.

Why gaps matter

A forecasting method that uses "the value one step ago" assumes each step is the same length. If 3 July is missing, then "one row ago" on 4 July is really two days ago. The method gets confused without telling you.

Think of a school attendance register. If a page is torn out, you cannot just close the gap and pretend the dates still run in order. You first write the missing date back in, then decide what to put next to it.

There are two kinds of gap:

• Missing rows: the date does not appear at all. This is common in shop exports, where a day with no sales has no bill and so no row.
• Missing values: the date is present, but the value is empty (NaN), for example when a sensor failed.

Step 1: find the missing dates

Build the full calendar you expect, then compare it with the dates you have.

import pandas as pd

sales = pd.Series(
    [5200, 4800, 6100, 5900, 7400, 6900],
    index=pd.to_datetime(["2025-07-01", "2025-07-02", "2025-07-04",
                          "2025-07-05", "2025-07-08", "2025-07-09"]),
    name="sales",
)
full = pd.date_range(sales.index.min(), sales.index.max(), freq="D")
missing = full.difference(sales.index)
print("Rows:", len(sales), " Days in range:", len(full))
print("Missing:", [d.strftime("%d %b (%a)") for d in missing])

Rows: 6  Days in range: 9
Missing: ['03 Jul (Thu)', '06 Jul (Sun)', '07 Jul (Mon)']

You should see three missing days. difference returns the dates in the full range that are not in the data. Printing the weekday helps you guess the cause. A missing Sunday may simply be the weekly holiday. A missing Thursday in the middle of the week deserves a question to the shop owner.

Step 2: fill each gap the right way

After reindex(full), every expected date has a row, and the missing ones hold NaN. What you put there depends on what the number means. This is a business question, not a coding question.

The code below fills three series for the same five days. The shop was closed on 3 July.

import pandas as pd

idx = pd.to_datetime(["2025-07-01", "2025-07-02", "2025-07-04", "2025-07-05"])
sales = pd.Series([5200.0, 4800, 6100, 5900], index=idx)        # shop closed on 3 July
stock = pd.Series([120.0, 95, 160, 140], index=idx)             # units on the shelf
temp = pd.Series([31.0, 30.0, 34.0, 33.5], index=idx)           # Jaipur afternoon temperature

full = pd.date_range("2025-07-01", "2025-07-05", freq="D")
table = pd.DataFrame({
    "sales_zero": sales.reindex(full, fill_value=0),
    "stock_ffill": stock.reindex(full).ffill(),
    "temp_interp": temp.reindex(full).interpolate(),
})
print(table)

            sales_zero  stock_ffill  temp_interp
2025-07-01      5200.0        120.0         31.0
2025-07-02      4800.0         95.0         30.0
2025-07-03         0.0         95.0         32.0
2025-07-04      6100.0        160.0         34.0
2025-07-05      5900.0        140.0         33.5

Read the 3 July row. Sales are 0 because the shop was shut. Stock stays at 95, because nobody sold anything and the shelf did not change. Temperature becomes 32.0, halfway between 30 and 34.

Be careful with zero. If the shop was open but the data export failed, a zero is a lie that drags every average down and teaches the model a fake dip. In that case, leave the value missing, or fill it by interpolation and record that you did so.

Step 3: fix duplicate dates

Sometimes a date appears twice or more. A billing system exports one row per bill, so a busy day has many rows. reindex refuses to work on duplicate labels. The fix is to add up (or average) the rows for each date first.

import pandas as pd

bills = pd.Series(
    [850, 1240, 610, 2300, 400, 975],
    index=pd.to_datetime(["2025-07-01", "2025-07-01", "2025-07-02",
                          "2025-07-04", "2025-07-04", "2025-07-04"]),
    name="amount",
)
print("Index unique?", bills.index.is_unique)

full = pd.date_range("2025-07-01", "2025-07-04", freq="D")
try:
    bills.reindex(full)
except ValueError as err:
    print("reindex failed:", err)

daily = bills.resample("D").sum()          # one row per day; empty days become 0
print(daily)

Index unique? False
reindex failed: cannot reindex on an axis with duplicate labels
2025-07-01    2090
2025-07-02     610
2025-07-03       0
2025-07-04    3675
Freq: D, Name: amount, dtype: int64

resample("D").sum() does two jobs at once: it adds the bills for each day, and it creates a row for 3 July with a total of 0. For totals this is exactly right. For averages, resample("D").mean() would give NaN on empty days instead. The next lesson explains resampling fully.

Advanced: uneven readings and time-based interpolation

Some data arrives at uneven times, such as a weather station that reports at 6, 9 and 15 o'clock. Plain interpolate() treats the rows as equally spaced. interpolate(method="time") uses the real time gaps.

import pandas as pd

readings = pd.Series(
    [28.0, 31.0, 36.0],
    index=pd.to_datetime(["2025-05-10 06:00", "2025-05-10 09:00", "2025-05-10 15:00"]),
)
wanted = pd.to_datetime(["2025-05-10 10:00", "2025-05-10 12:00"])
grid = readings.reindex(readings.index.union(wanted))

print(pd.DataFrame({
    "by_position": grid.interpolate(),
    "by_time": grid.interpolate(method="time"),
}).round(2))

                     by_position  by_time
2025-05-10 06:00:00        28.00    28.00
2025-05-10 09:00:00        31.00    31.00
2025-05-10 10:00:00        32.67    31.83
2025-05-10 12:00:00        34.33    33.50
2025-05-10 15:00:00        36.00    36.00

Between 9:00 and 15:00 the temperature rose 5 degrees over 6 hours. At 12:00, halfway in time, the honest estimate is 33.5, which method="time" gives. The position method splits the gap into three equal rows and gets 34.33, which is wrong. For time zones and joining uneven series, see Time Zones, Missing Dates & merge_asof.

Common mistakes

• Filling every gap with zero. Zero is right only when the true value was zero.
• Forward filling sales. Copying yesterday's sales into a closed day invents money.
• Ignoring duplicates. Aggregate with resample or groupby(level=0) before reindexing.
• Interpolating across the train/test boundary. It leaks future values into training.

Key points

• Build the full expected calendar with date_range, then reindex to expose missing rows.
• full.difference(series.index) lists the missing dates; check their weekdays.
• Fill by meaning: zero for closed-day totals, ffill for lasting states, interpolate for smooth readings.
• Duplicate dates must be aggregated before reindexing; resample("D").sum() does both jobs.
• Use interpolate(method="time") when readings are unevenly spaced.

import pandas as pd

changes = pd.Series([104.80, 105.10, 104.95],
                    index=pd.to_datetime(["2025-03-01", "2025-03-04", "2025-03-08"]))
daily = changes.reindex(pd.date_range("2025-03-01", "2025-03-09", freq="D")).ffill()
print(daily)

2025-03-01    104.80
2025-03-02    104.80
2025-03-03    104.80
2025-03-04    105.10
2025-03-05    105.10
2025-03-06    105.10
2025-03-07    105.10
2025-03-08    104.95
2025-03-09    104.95
Freq: D, dtype: float64