How to Resample Time Series Data Correctly

Eight thousand seven hundred and sixty hourly readings, and you need daily figures. It’s one aggregation, and there’s exactly one decision to make — but making it wrong produces a series that looks entirely reasonable and means nothing.

Sum or mean is the whole question

It depends on what the number represents, and the distinction is between quantities that accumulate and quantities that are a level.

Accumulating quantities — sales, orders, page views, rainfall, energy consumed — should be summed. Daily sales is the sum of hourly sales. Averaging them gives sales per hour, which is a different and usually unwanted number.

Level quantities — temperature, price, queue length, CPU utilisation, stock on hand — should be averaged or take the last value. Daily temperature is the average of hourly readings. Summing them produces a number with no physical meaning at all.

Getting this backwards produces plausible nonsense: a summed temperature series looks like a chart, has a trend, and is entirely meaningless. Nobody catches it downstream because it’s a number in a column.

A useful test: if you doubled the sampling frequency, should the aggregated value double? For sales, no — the total is the total. For temperature, no either — but summing would double it, which tells you summing is wrong.

Gaps are the second decision

Sensors drop out. Systems restart. A day with four missing hours is not a day.

Leave gaps empty and downstream code has to handle nulls, which is honest but inconvenient.

Forward fill carries the last value forward. For a level quantity during a brief outage this is defensible; over a long gap it produces a flat line that looks exactly like a real measurement of a stable value.

Interpolate invents a smooth transition. Same hazard, more convincing.

Whichever you choose, the filled points should be marked, because nobody downstream can distinguish invented data from measured data once it’s in a column.

Mark partial periods. A day aggregated from twenty hours instead of twenty-four is a lower total for a reason that has nothing to do with the underlying process.

Upsampling invents resolution

Going finer than your measurements — hourly data to five-minute intervals — creates points that were never measured. It’s sometimes necessary to align two series on a common index, and the interpolated points are estimates, not data.

If you upsample, keep the original series too. Someone will eventually ask what was actually measured.

Common mistakes to avoid

• Summing a level quantity, which produces a meaningful-looking chart of nothing.
• Forward-filling a long sensor outage, which turns missing data into an apparently stable reading.
• Including the current, incomplete period in a trend, which always looks like a decline.
• Resampling before removing known-bad readings, so the outliers get baked into the aggregates.
• Losing the timezone, which shifts every day boundary and quietly changes every daily total.

How to do it with Time Series Resampler

The Time Series Resampler makes both decisions explicit, in the browser.

1. Load the series with a timestamp column and a value column.
2. Choose the aggregation based on what the value represents — sum for accumulating, mean for levels.
3. Decide what happens to gaps, and mark anything filled.
4. Check that partial periods are flagged before charting the result.

Other data tools that keep files local are in the tools directory.

Frequently asked questions

Sum or mean?

Sum for things that accumulate — sales, visits, rainfall. Mean or last for things that are a level at a point in time. Mixing them produces plausible nonsense.

Should I fill gaps?

Only if you record that you did. Forward-filling a sensor outage turns missing data into a flat line that looks like a real reading.

What about upsampling?

Going finer than your measurements invents resolution you never had. It’s sometimes needed to align two series, but the interpolated points are estimates.

Final thought

Ask what the number is before you aggregate it. Sum and mean are both one word in a dropdown and they produce different worlds.

Try the free Time Series Resampler