Resample Irregular Readings to Regular Intervals

Readings rarely arrive on a neat grid. A temperature logger records whenever the value changes by more than half a degree, a meter is read whenever an engineer visits, a price feed sends an update whenever the price moves. Exported to Excel, the timestamps are irregular — three readings in ten minutes, then none for two hours — and a report wants one value per hour or per day. The obvious tool, resample("1h").mean(), averages whatever readings happen to fall in each hour, which over-weights busy periods and leaves empty hours. What the right answer is depends on what the readings mean: a level that holds until the next reading needs a time-weighted average, and a cumulative counter such as a meter needs the difference between interpolated values at the interval boundaries. This page builds both with pandas, keeps genuine gaps visible, handles several devices at once and writes the result to Excel. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl

temperatures.xlsx holds a day of readings from two freezer loggers. Freezer A records roughly every hour, but densely — every few minutes — during a short warm spell in the early afternoon when a door was left open. Freezer B has a gap of several hours with no readings at all. meter.xlsx holds six manual readings of a cumulative electricity meter taken on irregular days in September.

Step 1 — Why a plain resample mean misleads

Resampling to hourly means is the first thing most people try:

import pandas as pd

temps = pd.read_excel("temperatures.xlsx")
a = temps[temps["Device"] == "Freezer A"].set_index("Timestamp")["Temp C"]
hourly_mean = a.resample("1h").mean()
print(hourly_mean.loc["2026-09-28 12:00":"2026-09-28 15:00"].round(2).to_string())
print("daily mean of readings:", round(a.mean(), 2), "| readings per hour:", a.resample("1h").count().loc["2026-09-28 13:00"])

The 13:00 hour shows the warm spell, and that much is right. But the daily average of the readings is pulled noticeably warmer than the freezer really was, because the 45 minutes of warm temperatures produced many readings while the other 23 hours produced about one each. Averaging readings weights each reading equally; what you want is to weight each minute equally. The same bias appears in any log that records more often when something interesting happens — which is most event-driven logs. Hours with no reading at all come out as NaN, which is honest but leaves holes where the value was in fact known (the last reading still held).

Step 2 — Time-weighted averages for levels

For a level — temperature, a price, a stock count — each reading holds until the next one. Resample to a fine grid with forward fill, then average; each minute then counts once:

def time_weighted(series, freq="1h", grid="1min", max_hold="2h"):
    limit = int(pd.Timedelta(max_hold) / pd.Timedelta(grid))
    held = series.resample(grid).last().ffill(limit=limit)
    return held.resample(freq).mean()

tw = time_weighted(a)
compare = pd.DataFrame({"mean of readings": hourly_mean, "time-weighted": tw}).round(2)
print(compare.loc["2026-09-28 12:00":"2026-09-28 15:00"].to_string())
print("daily — mean of readings:", round(a.mean(), 2), "| time-weighted:", round(time_weighted(a, "1D").iloc[0], 2))

Spreading each reading forward minute by minute until the next reading, then averaging per hour, gives every minute the same weight. The hourly values hardly change in quiet hours, but the daily figure moves a lot: the mean of the readings is −16.59 °C, the time-weighted average −17.96 °C — close to the freezer's normal temperature, because the warm spell lasted well under an hour of the day's 1,440 minutes. Even within the 13:00 hour the two differ (−12.78 against −16.20), because the warm readings cluster in part of the hour. max_hold stops a reading being carried indefinitely: after two hours without a new reading the value is treated as unknown rather than assumed — a sensible guard for a logger that might have stopped. For exact results without a minute grid, weight each reading by the time until the next one; the minute grid is simpler and accurate enough for most reports.

Step 3 — Keep real gaps visible

Freezer B's logger stopped for several hours. A forward fill without a limit would invent a perfectly steady temperature for that period; the max_hold limit leaves it blank, and the gap can be reported explicitly:

b = temps[temps["Device"] == "Freezer B"].set_index("Timestamp")["Temp C"]
b_hourly = time_weighted(b)
gaps = b_hourly[b_hourly.isna()]
print("hours with no valid data:", [t.strftime("%H:%M") for t in gaps.index])
coverage = b.resample("1min").last().ffill(limit=120).notna().resample("1h").mean()
print("coverage at 06:00 and 10:00:", coverage.loc["2026-09-28 06:00"], coverage.loc["2026-09-28 10:00"])

The hours with no valid data are listed, and a coverage figure per hour — the share of minutes with a valid held value — shows partial hours at the edges of the gap. Reporting coverage next to each average lets readers judge it: an hourly average based on twelve minutes of data deserves less trust than one based on sixty. For temperature-controlled storage, an unexplained gap is itself an incident to report, so making it visible matters more than filling it. Gap filling for dates is covered in Fill Missing Dates in a Time Series with pandas.

Step 4 — Cumulative meters: usage per interval

A meter reading is a running total. Usage per day or week is the difference between the meter's values at the boundaries, which are rarely the moments the meter was read. Interpolate the reading at each boundary, then take differences:

meter = pd.read_excel("meter.xlsx").set_index("Timestamp")["Reading kWh"]
boundaries = pd.date_range("2026-09-01", "2026-10-01", freq="7D")
boundaries = boundaries[(boundaries >= meter.index.min()) & (boundaries <= meter.index.max())]
combined = meter.reindex(meter.index.union(boundaries)).interpolate(method="time")
at_bounds = combined.loc[boundaries]
weekly = at_bounds.diff().dropna().round(1)
print(at_bounds.round(1).to_string())
print(weekly.rename("kWh used").to_string())
print("check: total between first and last boundary =", round(at_bounds.iloc[-1] - at_bounds.iloc[0], 1))

interpolate(method="time") estimates the meter's value at midnight on each week boundary by assuming steady use between the two readings either side of it, weighted by time. The difference between consecutive boundary values is the estimated usage for that week, and the weekly figures add up exactly to the change in the meter between the first and last boundary. Note the boundary at 1 September 00:00 falls before the first reading at 08:12, so it is excluded rather than extrapolated. Linear interpolation is an estimate — usage is rarely perfectly steady — so present weekly figures from sparse readings as estimates, and the more frequent the readings, the better they get.

Step 5 — Several devices at once, and the report

Real exports mix many devices. Group by device and apply the time-weighted resample to each, then write one tidy table:

hourly = (temps.set_index("Timestamp")
               .groupby("Device")["Temp C"]
               .apply(time_weighted)
               .unstack("Device")
               .round(2))
hourly["Freezer A alarm"] = hourly["Freezer A"] > -17
with pd.ExcelWriter("readings_hourly.xlsx") as xw:
    hourly.reset_index().to_excel(xw, sheet_name="Hourly temps", index=False)
    weekly.rename("kWh used").reset_index().rename(columns={"index": "Week starting"}).to_excel(xw, sheet_name="Weekly usage", index=False)
print(hourly.loc["2026-09-28 12:00":"2026-09-28 14:00"].to_string())

groupby("Device").apply(time_weighted) runs the same resampling for each device and returns a series indexed by device and hour; unstack puts the devices side by side. A simple alarm column flags hours where Freezer A's time-weighted temperature rose above −17 °C — only the 13:00 hour — and Freezer B's gap shows as empty cells rather than invented values. The weekly meter usage goes on its own sheet. Rounding timestamps to slots before grouping is a different, simpler technique for regular data — see Round Timestamps into Time Slots with pandas.

Common pitfalls

• Plain resample means on event-driven logs. Busy periods are over-weighted; weight by time.
• Unlimited forward fill. A stopped logger looks like a steady value; limit how long a reading holds.
• Summing cumulative readings. Meters need differences of interpolated boundary values.
• Extrapolating beyond the first or last reading. Only interpolate between readings.
• Hiding coverage. Report how much of each interval had valid data.

Conclusion

Irregular readings need resampling that matches their meaning. For levels such as temperatures or prices, hold each reading until the next on a fine grid — with a limit, so gaps stay gaps — and average per interval, giving each minute equal weight rather than each reading. For cumulative counters such as meters, interpolate the value at each interval boundary by time and take differences, which adds up exactly to the change between readings. Resample per device with groupby, report coverage and gaps, and write the regular series to Excel.

Frequently asked questions

How do I convert irregular timestamps to hourly values in pandas? Set the timestamp as the index and use resample("1h"). For values that hold until the next reading, resample to a fine grid with last and ffill first, then average per hour to get a time-weighted mean.

Why is my resampled average biased? A plain resample mean gives every reading the same weight, so periods with frequent readings count more. Use a time-weighted average.

How do I calculate usage from cumulative meter readings? Interpolate the meter value at each interval boundary with interpolate(method="time") and take the differences between consecutive boundaries.

How do I avoid filling long gaps in sensor data? Pass a limit to ffill so a reading is carried forward only for a set time, after which values become missing and the gap remains visible.

Related

• Up one level: Working with Dates and Times in Excel Data — dates in Excel data.
• Interpolate Missing Numeric Values in Excel Data — interpolation in more depth.
• Fill Gaps in Per-Item Time Series with a Staleness Limit — carrying values forward per item.
• Calculate Moving Averages with pandas rolling — smoothing the regular series.