Resampling Irregular Sensor Timestamps to a Fixed Grid #

Sensors do not report on the second. A device configured for a one-minute cadence produces readings 59.7, 60.4, 61.1 seconds apart, drifts with temperature, skips an interval when the radio retries, and jumps when its clock is corrected. Every one of those is normal, and every downstream operation that compares two sensors, computes a rolling statistic, or feeds a spatial interpolation needs them on a common grid first. Resampling is that step — and the way it is usually written quietly fabricates data. This guide does it without doing that, as part of timestamp alignment and timezone normalization.

Resampling Is Two Decisions, Not One #

The first decision is the grid: how wide is a bin, and where do its edges fall? Bin width should be at or above the nominal reporting cadence — resampling one-minute data onto a ten-second grid manufactures nine empty bins for every real one. Edges should be aligned to the UTC epoch, so the same data reprocessed next month produces identical bins.

The second decision is what happens inside a bin, and it has three cases that are routinely conflated:

• Several readings in the bin. Aggregate them: mean, median or last, per the nature of the quantity.
• Exactly one reading. Use it. This is the common case at cadence-matched bin widths.
• No readings. Emit the bin with a null value and a flag. Do not interpolate here.

The third case is where most implementations go wrong, because pandas makes filling so convenient that .resample("1min").mean().interpolate() reads like one idea. It is two, and the second one silently converts a coverage gap into a measurement.

Production-Ready Implementation #

# python 3.11 · pandas==2.2.2 · numpy==1.26.4
from __future__ import annotations

import numpy as np
import pandas as pd

# CF-convention-aligned codes, matching the QC flag vocabulary used across the pipeline
QC_GOOD = 1
QC_INTERPOLATED = 8      # a bin whose value came from a gap-filling step, not a sensor
QC_MISSING = 9


def resample_to_grid(
    df: pd.DataFrame,
    freq: str = "1min",
    *,
    how: str = "mean",
    value_col: str = "value",
    time_col: str = "observed_at",
    device_col: str = "device_id",
) -> pd.DataFrame:
    """Bin readings onto a fixed UTC grid, one row per device per interval.

    Gaps are preserved as null values flagged QC_MISSING — nothing is filled here.
    Bin edges are epoch-aligned and left-closed, so reprocessing is reproducible
    and a reading exactly on a boundary belongs to exactly one bin.
    """
    if df[time_col].dt.tz is None:
        raise ValueError(f"{time_col} must be timezone-aware UTC before resampling")

    out = (
        df.set_index(time_col)
        .groupby(device_col)
        .resample(freq, closed="left", label="left", origin="epoch")
        .agg(
            value=(value_col, how),
            n_samples=(value_col, "count"),
        )
        .reset_index()
    )

    out["qc_flag"] = np.where(out["n_samples"] > 0, QC_GOOD, QC_MISSING)
    out.loc[out["n_samples"] == 0, "value"] = np.nan
    return out

Three arguments in that resample call are doing real work. origin="epoch" anchors the bins to 1970-01-01 rather than to the first reading in the frame, so a backfill and a live run produce identical boundaries. closed="left" with label="left" gives half-open bins [t, t+freq) — matching the interval convention used for registry validity and for tumbling windows. And keeping n_samples turns an invisible property into a column: a bin built from six readings and a bin built from one look identical afterwards unless you record the difference.

Filling, when it is wanted, is a separate function that flags what it invents:

def fill_short_gaps(
    df: pd.DataFrame, max_gap: int = 2, *, device_col: str = "device_id"
) -> pd.DataFrame:
    """Linearly interpolate runs of at most `max_gap` missing bins, flagged as interpolated.

    Longer runs stay missing: beyond a couple of intervals, interpolation is a
    guess dressed as a measurement.
    """
    def _fill(group: pd.DataFrame) -> pd.DataFrame:
        filled = group["value"].interpolate(method="linear", limit=max_gap, limit_area="inside")
        invented = filled.notna() & group["value"].isna()
        group = group.assign(value=filled)
        group.loc[invented, "qc_flag"] = QC_INTERPOLATED
        return group

    return df.groupby(device_col, group_keys=False).apply(_fill)

limit_area="inside" is the guard that stops interpolation running off the ends of the series and extrapolating into periods when the sensor was not deployed at all.

Parameter Tuning Guide #

The last row is the exception that breaks the pattern and is worth stating loudly: a tipping-bucket rain gauge reports events, not a level. Averaging tips is meaningless, and filling a gap with interpolated rainfall invents precipitation that never fell. Sum the bin, and leave gaps as gaps.

Verification and Testing #

Test the three bin cases explicitly, and test that the boundary instant lands in one bin only.

# python 3.11 · pandas==2.2.2 · pytest==8.2.0
import pandas as pd


def test_bins_preserve_gaps_and_count_samples():
    readings = pd.DataFrame({
        "device_id": ["s1"] * 4,
        "observed_at": pd.to_datetime([
            "2026-08-01T10:00:10Z",     # bin 10:00 — two readings
            "2026-08-01T10:00:44Z",
            # bin 10:01 — none: a real gap
            "2026-08-01T10:02:03Z",     # bin 10:02 — one reading
            "2026-08-01T10:03:00Z",     # bin 10:03 — exactly on the edge
        ]),
        "value": [10.0, 12.0, 20.0, 30.0],
    })

    out = resample_to_grid(readings, "1min")

    assert list(out["n_samples"]) == [2, 0, 1, 1]
    assert out.loc[0, "value"] == 11.0                 # mean of the two
    assert pd.isna(out.loc[1, "value"])                 # gap preserved
    assert list(out["qc_flag"]) == [1, 9, 1, 1]


def test_a_reading_on_the_boundary_belongs_to_the_later_bin():
    edge = pd.Timestamp("2026-08-01T10:03:00Z")
    readings = pd.DataFrame({"device_id": ["s1"], "observed_at": [edge], "value": [30.0]})
    out = resample_to_grid(readings, "1min")
    assert out.loc[0, "observed_at"] == edge           # left-closed: the edge opens its own bin

Beyond unit tests, the operational check is the distribution of n_samples. On a healthy one-minute feed resampled to one-minute bins, almost every bin should have exactly one sample. A rising share of bins with two or more means the device is reporting faster than configured; a rising share of zeros is a coverage problem for QC flagging to record.

Gotchas #

origin="start_day" (the pandas default for some frequencies) drifts with the data. Two runs over overlapping ranges produce different bin edges, so the same reading lands in differently labelled bins. Always pass origin="epoch" for reproducible pipelines.

Resampling before normalizing timezones. Binning local timestamps means the bins themselves shift by an hour twice a year, and the duplicated hour during the autumn transition merges two distinct intervals. Normalize to UTC first, always.

.mean() over a bin containing a sentinel value. A device that reports −999 for “no data” will drag a bin average anywhere. Convert sentinels to NaN at decode time, long before this step.

Chained .resample().interpolate(). The reason this page separates the two functions: chained, there is no point at which a caller can see which values were measured. Once the flag column exists, that distinction survives all the way to export.

FAQ #

Related #

• Timestamp Alignment and Timezone Normalization for Environmental IoT — the timestamp normalization stage this belongs to
• Handling Timezone Drift in High-Frequency IoT Streams — making the timestamps trustworthy before you bin them
• Implementing Tumbling Windows for Air Quality Metrics — the streaming counterpart of the same binning decision