Fill Gaps in Per-Item Time Series with a Staleness Limit

Supplier prices, exchange rates, stock levels and survey readings often arrive sparsely: a row is recorded only when something changes or when someone checks. For a monthly report you need a value for every item in every month, and the natural assumption is that the last recorded value still holds — so you carry it forward. Done naively, this breaks in three ways. A plain forward fill leaks one item's value into the next item's rows; a value recorded in January is still being reported as "current" in September; and an item that first appears in May gets back-filled or left mysteriously blank before it. This page builds the fill properly with pandas: a full item-by-period grid, a forward fill within each item, a cap on how far a value may be carried, the age of every value, and an Excel matrix that marks stale figures. It belongs to Handling Missing Data in Excel Reports.

Prerequisites

pip install pandas openpyxl

price_observations.xlsx has one row per price check: item, date and unit price. Prices were recorded irregularly between January and September 2026 — some items several times in a month, some only once all year. The report needs a price for every item for every month from January to September.

Step 1 — One observation per item and period

First reduce the raw observations to at most one value per item per month. When an item was checked twice in a month, the later check is the one that holds at month end:

import pandas as pd

obs = pd.read_excel("price_observations.xlsx")
obs["Month"] = obs["Date"].dt.to_period("M")
monthly = (obs.sort_values("Date")
              .groupby(["Item", "Month"], as_index=False)
              .last())
print(monthly[["Item", "Month", "Date", "Price"]].to_string(index=False))

Sorting by date and taking last() per item and month keeps the latest check — Bolt M8 was checked on 3 and 28 April, and the 28 April price of 0.135 is the April value. If your rule is different (the average of the month's readings, the first one), this is the place to apply it; the steps below only need one value per cell.

Step 2 — Build the complete item × month grid

Missing months are not rows at all yet — there is nothing to fill. Create every combination of item and month, then join the observations onto it:

months = pd.period_range("2026-01", "2026-09", freq="M")
items = sorted(monthly["Item"].unique())
grid = pd.MultiIndex.from_product([items, months], names=["Item", "Month"]).to_frame(index=False)
grid = grid.merge(monthly[["Item", "Month", "Date", "Price"]], on=["Item", "Month"], how="left")
print(grid.pivot(index="Item", columns="Month", values="Price").to_string())

MultiIndex.from_product creates all 5 × 9 = 45 item-month pairs; the left merge puts the observed price where there is one and NaN elsewhere. The pivoted view shows the raw sparseness: Rawl plug has a single January price, the drill bit nothing before May. Building the grid from an explicit period_range rather than from the months that happen to appear in the data means a month in which nothing at all was checked still gets a column. The same grid technique appears in Fill Missing Dates in a Time Series with pandas.

Step 3 — Forward-fill within each item, with a limit

Now carry values forward — but only within the same item, and only for a limited number of months. groupby("Item").ffill(limit=n) does both:

MAX_CARRY = 3                                   # a price may be reused for at most 3 months
grid = grid.sort_values(["Item", "Month"])
grid["As of"] = grid["Month"].where(grid["Price"].notna())
grid[["Price filled", "As of"]] = grid.groupby("Item")[["Price", "As of"]].ffill(limit=MAX_CARRY)
grid["Age (months)"] = [(m - a).n if pd.notna(a) else None for m, a in zip(grid["Month"], grid["As of"])]
print(grid[grid["Item"].isin(["Bolt M8", "Rawl plug"])][["Item", "Month", "Price", "Price filled", "As of", "Age (months)"]].to_string(index=False))

Grouping by item before filling is what stops Bolt M8's last price leaking into the next item's first rows — a plain ffill() on the whole column would do exactly that, because it only looks at the previous row. limit=3 fills at most three consecutive gaps after each observation; Rawl plug's January price fills February to April and May onwards stays blank. Filling the As of month alongside the price records which observation each value came from, and the age — the number of months since that observation — falls out of the difference of two periods. Rows before an item's first observation stay blank, because a forward fill never looks ahead: there is no honest value for the drill bit in March.

Step 4 — Flag stale values

Within the limit, older values deserve less trust. A simple rule marks a value as fresh when observed this month, carried for one or two months, and stale at the limit; anything beyond the limit is missing:

def status(age):
    if pd.isna(age):
        return "missing"
    return "observed" if age == 0 else "carried" if age < MAX_CARRY else "stale"

grid["Status"] = grid["Age (months)"].map(status)
print(grid["Status"].value_counts().to_dict())
print(grid.pivot(index="Item", columns="Month", values="Status").to_string())

The status matrix is the honest summary of how good the price list is: only 10 of the 45 cells were actually observed in their month, 13 are carried, 4 are stale and 18 have no usable price — and it shows at a glance which items need a fresh price: Nut M8 and Rawl plug have nothing usable after the spring, and Bolt M8 drops out in August. Washer M8 is a good example — checked in March and then not again until August, it is carried, then stale, then missing in July before the August check refreshes it. The thresholds are business decisions: a three-month-old exchange rate is useless, a three-month-old screw price is probably fine. Make MAX_CARRY a per-item or per-category setting if items differ.

Step 5 — Write a price matrix that shows its own quality

The report sheet shows prices by item and month, with each cell's background saying how old the value is, and a second sheet with the full detail:

from openpyxl.styles import Font, PatternFill

prices = grid.pivot(index="Item", columns="Month", values="Price filled")
status_m = grid.pivot(index="Item", columns="Month", values="Status")
prices.columns = [str(c) for c in prices.columns]
fills = {"carried": "FFF2CC", "stale": "F8CBAD", "missing": "D9D9D9"}

with pd.ExcelWriter("price_matrix.xlsx", engine="openpyxl") as xw:
    prices.to_excel(xw, sheet_name="Prices")
    grid.assign(Month=grid["Month"].astype(str), **{"As of": grid["As of"].astype(str)}) \
        .drop(columns="Date").to_excel(xw, sheet_name="Detail", index=False)
    ws = xw.sheets["Prices"]
    for r, item in enumerate(prices.index, start=2):
        for c, month in enumerate(status_m.columns, start=2):
            s = status_m.loc[item, month]
            if s in fills:
                ws.cell(r, c).fill = PatternFill("solid", fgColor=fills[s])
            ws.cell(r, c).number_format = "0.000"
    ws["A12"] = "White: observed that month · yellow: carried · orange: stale · grey: no recent price"
    ws["A12"].font = Font(italic=True)
    ws.column_dimensions["A"].width = 16
print(prices.round(3).to_string())

Readers see the price table they expect, but the colours tell them which prices were checked recently and which are leftovers, and a legend under the table explains the colours so the sheet makes sense on its own. The Detail sheet keeps the as-of month and age for every cell, so a question about any figure — "where does this 0.13 come from?" — can be answered from the workbook itself. Periods are converted to strings before writing, since Excel has no period type. When next month's checks arrive, append them to the observations file and rerun: the grid, the fill and the colours are all recomputed, so a refreshed price turns its cell white again without anyone editing the sheet.

Common pitfalls

• A plain ffill() across items. Values leak between items; always group by the item first.
• Filling without a limit. Old values are reported as current indefinitely.
• Back-filling before the first observation. There is no honest value there; leave it blank.
• Gaps that are not rows. Build the full grid before filling.
• Hiding the age. Keep the as-of period and show stale values differently.

Conclusion

To complete a sparse per-item time series, reduce observations to one per item and period, build the full item-by-period grid with MultiIndex.from_product, and forward-fill within each item using groupby().ffill(limit=n) so values neither leak between items nor live forever. Fill an as-of period alongside the value to compute its age, classify each cell as observed, carried, stale or missing, and write a matrix whose colours make the quality of every figure visible.

Frequently asked questions

How do I forward-fill values per group in pandas? Sort by group and period, then call df.groupby("Item")cols.ffill(). Grouping stops the last value of one item filling the first rows of the next.

How do I limit how far a value is carried forward? Pass limit to ffill, for example ffill(limit=3), which fills at most three consecutive missing periods after each known value.

How do I know how old a forward-filled value is? Fill the observation period alongside the value, then subtract it from each row's period to get the age in periods.

Why are some periods still blank after filling? Either they come before the item's first observation, which a forward fill never fills, or they are further from the last observation than the limit allows.

Related

• Up one level: Handling Missing Data in Excel Reports — finding and treating gaps.
• Forward-Fill Blank Cells in Grouped Excel Reports — filling layout blanks rather than time gaps.
• Match Rows by Nearest Date with merge_asof — looking up the latest value on a date, with a tolerance.
• Interpolate Missing Numeric Values in Excel Data — estimating between known points instead.