Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Fill Missing Dates in a Time Series from Excel with pandas

Daily exports only have rows for days when something happened, so charts skip the quiet days and averages come out too high. Add the missing dates with reindex and date_range, fill counts with zero and balances with the last known value, do it per store, and aggregate duplicate dates first.
Dates & timesMissing datapandas

Operational exports usually record events, not days. A store that took no orders on Wednesday simply has no Wednesday row. That seems harmless until the data is charted — the line jumps from Tuesday to Thursday — or averaged, when a "seven-day average" is taken over five days and comes out too high. Before anything that assumes regular periods, the missing dates need to exist as rows, and each column needs the right kind of fill: a missing day of orders is zero, a missing day of stock is yesterday's stock. This page, part of Working with Dates and Times in Excel Data, fills gaps in a daily sheet, per store, and handles the duplicate dates that make the obvious method fail.

North store orders before and after filling the gap A line chart of daily orders for the North store from 2 to 6 March. The raw data has no row for 4 March, so the line runs straight from 3 on the 3rd to 4 on the 5th. After filling, 4 March has 0 orders and the line drops to the axis for that day. 0 2 4 6 Mar 2 Mar 3 Mar 4 Mar 5 Mar 6 no row in the export

Prerequisites

Bash
pip install pandas openpyxl

daily.xlsx has seven rows for two stores over 2–6 March 2026: North is missing 4 March; South only has 2 and 6 March, and 6 March appears twice because two orders were exported separately. orders is a count per day; stock is the closing stock level.

Step 1 — Aggregate duplicate dates first

Reindexing needs each date to appear once per store. Check for duplicates, and combine them with the right aggregation per column — orders are summed, stock is a level, so take the last value of the day:

Python
import pandas as pd

daily = pd.read_excel("daily.xlsx")
print(daily.duplicated(["store", "date"]).sum(), "duplicate store-days")

daily = (daily.groupby(["store", "date"], as_index=False)
              .agg(orders=("orders", "sum"), stock=("stock", "last")))
print(daily.to_string(index=False))

Skipping this step is the source of the error cannot reindex on an axis with duplicate labels. Choosing the aggregation deliberately matters as much as removing the duplicates: summing a stock level would double it.

Step 2 — Reindex one series onto a full date range

For a single store, set the date as the index and reindex onto pd.date_range from the first to the last date. Every date in the range gets a row; dates that were missing get NaN:

Python
north = daily[daily["store"] == "North"].set_index("date")[["orders", "stock"]]
full = pd.date_range(north.index.min(), north.index.max(), freq="D")
north = north.reindex(full)
north.index.name = "date"
print(north)

4 March now exists, with NaN in both columns. The range could equally be a fixed reporting period — pd.date_range("2026-03-01", "2026-03-31") — which also adds rows before the first and after the last recorded day, so every report has the same number of rows whatever happened that month.

Step 3 — Fill each column according to what it measures

The NaNs mean different things in different columns. No orders recorded means zero orders; no stock recorded means the stock did not change. Fill accordingly:

Python
north["orders"] = north["orders"].fillna(0).astype(int)
north["stock"] = north["stock"].ffill()
print(north)

Orders on 4 March are 0 and stock is carried forward from 3 March. Getting this wrong in either direction produces plausible-looking errors: forward-filling orders invents sales on days the store was closed, and zero-filling stock makes inventory vanish for a day and reappear.

How to fill a missing day, by type of column A reference table. Flows such as orders, sales and visits are filled with zero, because no record means nothing happened. Levels such as stock, balances and headcount are forward-filled with the last known value. Rates and prices are forward-filled or left missing, depending on whether the last value still applies. Measurements such as temperature are interpolated or left missing. Fill with Why Flows: orders, sales, visits 0 no record means none happened Levels: stock, balance, headcount last value (ffill) unchanged until a new record Rates and prices ffill or leave missing if the last value still applies Measurements interpolate or leave missing the true value is unknown

Step 4 — Fill gaps for every store at once

With several stores, build the complete grid of every store and every date, then reindex the store-date index onto it. MultiIndex.from_product creates the grid:

Python
dates = pd.date_range(daily["date"].min(), daily["date"].max(), freq="D")
grid = pd.MultiIndex.from_product([daily["store"].unique(), dates], names=["store", "date"])

filled = daily.set_index(["store", "date"]).reindex(grid)
filled["orders"] = filled["orders"].fillna(0).astype(int)
filled["stock"] = filled.groupby(level="store")["stock"].ffill()
filled = filled.reset_index()
print(filled.to_string(index=False))

South now has a row for every day from 2 to 6 March, with zero orders on the quiet days. Forward-filling within each store matters: a plain ffill() over the whole frame would carry North's last stock value into South's first missing day. For stock before a store's first record, ffill leaves NaN, which is honest — the level is unknown.

Keep track of which rows were added

A filled zero and a recorded zero look identical, but they mean different things: one is "the export had no row", the other is "the system recorded zero". When the difference matters — a store that was closed versus one whose feed failed — record it before filling:

Python
marked = daily.set_index(["store", "date"]).reindex(grid)
marked["recorded"] = marked["orders"].notna()
marked["orders"] = marked["orders"].fillna(0).astype(int)
marked["stock"] = marked.groupby(level="store")["stock"].ffill()
marked = marked.reset_index()
print(marked.groupby("store")["recorded"].agg(["sum", "size"]).rename(columns={"sum": "recorded", "size": "days"}))

North recorded four of its five days and South two. In the workbook, the recorded column can drive a light grey fill on added rows, so readers see at a glance which figures came from the source and which were inferred. A store with a long run of unrecorded days is worth a question before it goes into a trend line: a missing feed filled with zeros looks exactly like a sudden collapse in sales.

Step 5 — Use business days when weekends do not count

For data that only exists on working days — trades, office tickets — a calendar-day range adds weekend rows full of zeros that drag averages down. Use freq="B" for Monday to Friday:

Python
weekdays = pd.date_range("2026-03-02", "2026-03-13", freq="B")
print(len(weekdays), weekdays.day_name().unique().tolist())

Ten dates, Monday to Friday only. Public holidays need a custom calendar; Calculate Business Days Between Excel Dates with pandas shows how to build one with CustomBusinessDay and a holiday list.

Step 6 — Averages and totals after filling

Filling first changes the answers, which is the point. Compare the average orders per day for South before and after:

Python
south_raw = daily.loc[daily["store"] == "South", "orders"].mean()
south_filled = filled.loc[filled["store"] == "South", "orders"].mean()
print(f"raw: {south_raw:.2f} orders/day, filled: {south_filled:.2f} orders/day")

weekly = filled.set_index("date").groupby("store")["orders"].resample("W-SUN").sum()
print(weekly)

South's raw average is 5 orders a day because it counts only the two days that had orders; over the five-day period it is 2. Weekly totals do not change — zeros add nothing — which is a useful check that filling did not alter the underlying data. Group Excel Rows by Month and Quarter with pandas covers resampling and calendar grouping.

The same South data averaged with and without the empty days Two panels. Averaging only the recorded rows gives 5 orders per day, because the two quiet days and the gap are ignored. Averaging after filling missing days with zero gives 2 orders per day over the five-day period. Weekly totals are 10 in both cases. Recorded rows only 2 rows averaged 5 orders per day weekly total 10 After filling missing days 5 days averaged 2 orders per day weekly total 10

Write the complete series to Excel

A filled series is the right input for a chart in the workbook, because Excel's line and column charts plot one point per row:

Python
with pd.ExcelWriter("daily_filled.xlsx") as writer:
    filled.to_excel(writer, sheet_name="Daily", index=False)
    weekly.reset_index().to_excel(writer, sheet_name="Weekly", index=False)
print(pd.read_excel("daily_filled.xlsx", sheet_name="Daily").shape)

Ten rows — two stores times five days — where the export had seven, and one of those seven was a duplicate. Add a Line Chart to an Excel Report with Python adds the chart itself.

Common pitfalls

  • Reindexing with duplicate dates. Aggregate to one row per key and date first.
  • Zero-filling levels. Stock and balances should carry forward, not drop to zero.
  • Forward-filling across groups. Group by store before ffill.
  • Calendar days for working-day data. Use freq="B" or a custom business calendar.
  • A range that stops at the last record. If a store's final days are quiet, date_range(min, max) ends too early; use the reporting period's fixed start and end instead.
  • Losing the difference between filled and recorded zeros. Keep a recorded flag when a missing feed and a quiet day need different responses.

Performance notes

reindex on a MultiIndex grid is fast, but the grid has one row per group per date: a thousand stores over five years is 1.8 million rows, which is fine in memory but large for a single sheet, and slow to chart in Excel. For very sparse data, fill only the period and groups a report actually needs.

Conclusion

Aggregate duplicate dates with the right function per column, reindex onto a full pd.date_range — or a MultiIndex.from_product grid for several groups — and fill each column according to what it measures: zero for flows, forward-fill within each group for levels. Use business-day frequencies where weekends do not count, and compute averages and charts only after the missing days exist.

Frequently asked questions

Why should I add rows for days with no data? Because averages, rolling windows and charts assume one row per period. Without the empty days, a seven-day average is taken over fewer than seven days, and a line chart joins Tuesday straight to Thursday as if Wednesday did not exist.

Should missing days be filled with zero? Only for flows — orders, sales, visits — where no row really means nothing happened. For levels such as stock or balances, a missing day means the value did not change, so carry the last known value forward instead.

Why does reindex raise "cannot reindex on an axis with duplicate labels"? Some dates appear more than once. Aggregate them first — sum the orders per date, take the last stock value — so each date is unique before reindexing.

How do I fill gaps for each store separately? Build the full grid of every store and every date with pd.MultiIndex.from_product, and reindex the grouped data onto it. Every store then has a row for every day, whether or not it traded.

More on dates & times

More on dates & times →