Seven rows are not a week: rolling sums in pandas

The report says 130 jobs finished in the last seven days. Open the log and only two batches belong in that period: 60 jobs and 20 jobs. Where did the other 50 come from?

The addition is right. The window is wrong.

In pandas, rolling(7) normally takes the current row and the six rows before it. Those rows might cover seven seconds or seven months. A timestamp index does not change the integer into a duration.

Let's build the disagreement, choose the boundary deliberately, and keep the result from going stale when no new batch arrives.

Give the gap somewhere to hide

This is a complete event log for a batch processor. Each value records new jobs completed at that instant, not a running total. All times are UTC. The code below runs with Python and pandas; the supplied example was checked with Python 3.12.14 and pandas 2.2.3.

import pandas as pd

jobs = pd.Series(
    [10, 10, 10, 10, 10, 10, 60, 20],
    index=pd.to_datetime(
        [
            "2026-09-01T12:00:00Z",
            "2026-09-02T12:00:00Z",
            "2026-09-03T12:00:00Z",
            "2026-09-04T12:00:00Z",
            "2026-09-05T12:00:00Z",
            "2026-09-06T12:00:00Z",
            "2026-09-12T12:00:00Z",
            "2026-09-13T12:00:00Z",
        ],
        utc=True,
    ),
    name="jobs",
)

comparison = pd.DataFrame(
    {
        "jobs": jobs,
        "last_7_rows": jobs.rolling(7, min_periods=1).sum(),
        "last_7_days": jobs.rolling(
            "7D", min_periods=1, closed="right"
        ).sum(),
    }
)
print(comparison.to_string())

The final two rows expose the problem:

At noon on 13 September, the seven-row window reaches back to 2 September. It includes five older ten-job batches. The elapsed-time window includes only the batches on 12 and 13 September, giving 80.

There is nothing wrong with a seven-observation statistic. It answers a different question. Label it that way if those are the seven batches you intend to compare.

Put a rule on the left edge

The time window above means:

reporting time - 7 days < event time <= reporting time

At noon on 13 September, the batch completed at exactly noon on 6 September is excluded. The batch at the reporting instant is included. That is what closed="right" says. Writing it explicitly saves the next reader from having to recall the default.

Change the left boundary and the answer changes:

at = pd.Timestamp("2026-09-13T12:00:00Z")

right = jobs.rolling("7D", min_periods=1, closed="right").sum()
both = jobs.rolling("7D", min_periods=1, closed="both").sum()

assert right.loc[at] == 80
assert both.loc[at] == 90

Those extra ten jobs are precisely the batch on the left edge. This is a useful test because it forces a decision that ordinary mid-window examples never reveal. The pandas rolling reference documents the endpoint choices and the difference between integer and time windows.

Here, seven days means 168 elapsed hours. A report covering seven local calendar dates needs its own date and time-zone rule, particularly around daylight-saving changes. Decide which interval the report promises before choosing the expression.

Don't let a second default obscure the first

Both calculations set min_periods=1. This controls the minimum number of usable observations required for an answer. It does not control how many days have passed.

Without that argument, an integer window of seven waits for seven usable observations; a time window defaults to one. You could otherwise spend time explaining the blank cells at the beginning while missing the more consequential disagreement at the end.

Setting min_periods=7 on the time window would not certify a week of history. Seven events could all happen in one minute. The windowing guide covers minimum observations and the requirement for an ordered time index. Sort imported events by time before applying a trailing window.

Decide what simultaneous batches mean

Suppose another five-job batch finishes at exactly noon on 13 September. A row-by-row rolling result can give intermediate totals to rows sharing a timestamp. For this report, the desired answer is one total per instant, after counting every batch at that instant.

Group those events first:

tied = pd.concat([jobs, pd.Series([5], index=[at], name="jobs")])
per_instant = tied.groupby(level=0).sum().sort_index()
weekly = per_instant.rolling("7D", min_periods=1, closed="right").sum()

assert weekly.loc[at] == 85

The final instant now holds 25 newly completed jobs. Along with the earlier 60, the trailing total is 85. Reordering the two simultaneous batches cannot change that result.

This aggregation fits counts of newly completed jobs. A measurement such as temperature would need a different rule; adding two thermometer readings would change the meaning of the data.

The last event is not necessarily now

There is one more trap. rolling evaluates a result at each input timestamp. It does not keep moving that result forward while the processor is idle.

The last row still says 80 if someone opens the report on 22 September. At that point, every recorded batch is outside the last seven days.

For one report at a specified instant, filter the interval directly:

def total_at(jobs, at):
    at = pd.Timestamp(at)
    if at.tzinfo is None:
        raise ValueError("The reporting time must include a timezone.")
    cutoff = at - pd.Timedelta(days=7)
    included = (jobs.index > cutoff) & (jobs.index <= at)
    return jobs.loc[included].sum()


assert total_at(jobs, "2026-09-13T12:00:00Z") == 80
assert total_at(jobs, "2026-09-22T12:00:00Z") == 0

This also excludes events after the requested reporting time, which matters when rerunning a historical report from a newer export. The zero is justified here because the input is a complete event log of known counts. If the source stopped recording, that is a data-collection problem to resolve before presenting a total.

Make the boundary test earn its place

The smallest useful regression test puts an event just outside each edge, one on each edge, and one inside. Give them different values so the total reveals which ones were counted:

cutoff = at - pd.Timedelta(days=7)
tick = pd.Timedelta(seconds=1)
edge_jobs = pd.Series(
    [1, 2, 4, 8, 16],
    index=[cutoff - tick, cutoff, cutoff + tick, at, at + tick],
)

assert total_at(edge_jobs, at) == 4 + 8

The result must include the event just after the left boundary and the event at the reporting instant. It must exclude the old event, the event exactly seven days old, and the future event. That single sum checks the rule more clearly than a large pile of evenly spaced timestamps.

Before trusting a rolling report, put the reporting instant next to the total. Then pick one old row and ask why it belongs. If the answer is only that it was among the previous seven rows, the calendar has never entered the calculation.