Time-Bucketed Rollups with Pandas

A dashboard rarely wants just one summary of a metric — it wants it by hour for an operations view, by day for a weekly report, and by month for a trend line. Writing that as three (or six) separate, nearly-identical blocks of groupby code is how a script quietly turns into something nobody wants to touch. This article covers the two habits that avoid it: deriving calendar features in one line each, and factoring the rollup itself into one function called several times.

Calendar features, one line at a time#

Once a column is a real datetime64, the .dt accessor turns any calendar component into a vectorized column assignment:

df["hour"] = df["timestamp"].dt.hour

The same shape covers everything else a rollup needs: .dt.date for the calendar day, .dt.isocalendar().week for the ISO week number (which, unlike a naive day-of-year ÷ 7, never splits a week across a year boundary), .dt.month, .dt.year, and .dt.day_name() for the weekday label. Each is the identical one-line pattern — pull one component off the timestamp into its own column — so there is little reason to write more than you need for the granularities a given report actually asks for.

One rollup function, several granularities#

def rollup(df, group_by, label):    result = (        df.groupby(["entity_id", *group_by], as_index=False)        .agg(avg_value=("value", "mean"))    )    result["period"] = label    result["period_value"] = result[group_by].astype(str).agg("-".join, axis=1)    return result

Parameterizing the grouping columns means the same function produces an hourly, daily, or monthly rollup depending only on what group_by and label it is called with — *group_by unpacks the list into groupby alongside the entity id, so a single- or multi-column grouping needs no special case. Collapsing the grouping columns into one period_value string is what lets every granularity share one schema: period says which granularity a row belongs to, period_value identifies which bucket, and a downstream query can filter to one granularity with a plain WHERE.

Calling the function once per granularity and stacking the results is then the entire remaining pipeline:

hourly = rollup(df, ["hour"], "hour")daily = rollup(df, ["date"], "day")

The same call, repeated for whatever other granularities a report needs — week, month, year — each producing the same three columns, ready to concatenate into one long-format table with pd.concat([hourly, daily, ...], ignore_index=True). ignore_index=True matters here: without it, every piece keeps its own row index restarting at zero, and the combined frame ends up with duplicate index values instead of one continuous range.

A derived "share of total" column#

A common extra: each period's share of its entity's overall total, useful for spotting a skewed profile (all the volume concentrated in one hour, say) without a second aggregation:

totals = hourly.groupby("entity_id")["avg_value"].transform("sum")hourly["share"] = hourly["avg_value"] / totals

transform is what makes this a two-liner: unlike a plain groupby().sum(), it returns a result aligned back to the original rows — every row gets its own entity's total — so the division works directly, with no separate merge step to attach the group total back to each row.

Common mistakes#

Duplicating the aggregation once per granularity#

Six copies of the same groupby().agg() block, one per time bucket, is exactly the pattern this article's rollup function replaces. If two granularities' code differs only in which columns they group by, that difference belongs in a parameter, not in copy-pasted blocks.

Forgetting ignore_index=True on concat#

Without it, stacking several same-shaped frames produces a result with repeated index values — harmless until something relies on the index being unique, at which point it fails in a confusing way.

Confusing ISO week with a naive week number#

.dt.isocalendar().week follows the ISO 8601 definition, not "day of year divided by seven." A hand-rolled week calculation can silently split or merge weeks at year boundaries in ways that only show up as a suspiciously short or long week in a report.

Further reading#

• pandas: Time series / date functionality — the full range of what the .dt accessor exposes.
• ISO week date — the definition behind isocalendar().week.
• Group by: split-apply-combine — transform and the rest of the groupby toolkit.
• pandas.concat — stacking same-shaped frames, including the ignore_index option.

See also#