Module 4: Combining and Summarizing Data · Lecture 17 of 32
A time series needs a frequency, a timezone, and a missing-period policy. Build a complete daily revenue series and distinguish a seven-day rolling mean from a seven-observation mean.
What you will learn
- Aggregate transactions into local calendar days.
- Reindex to a declared reporting window.
- Interpret rolling windows and incomplete edges.
Understand the problem
Transactions are not a time grid
An order table has one row per event, with several events on some dates and none on others. A daily report needs one row per local date. Convert timestamps to the reporting timezone, index by those instants, and resample to daily sums. The resulting grid is a different grain from the source orders.
Zero activity and missing delivery differ
For this generator, the January–March source window is known. We can reindex to all 90 local calendar dates and fill absent daily totals with zero. In a real report, first verify that every source partition arrived. Otherwise a missing export becomes a false zero and can make a rolling trend appear to collapse.
Two meanings of a window
rolling(7) uses seven observations. On a complete daily grid, that is seven days; on an irregular transaction series, it is seven orders. rolling(‘7D’) uses a time-based interval whose boundary inclusion also matters. Pick the form that expresses the business question. Set min_periods explicitly so early partial windows do not look like full-week estimates.
Aggregation loses detail
A daily total can hide a refund spike, a missing channel, or a changed customer mix. Keep drill-down keys and reconcile the daily sum to the accepted-order total. When comparing periods of unequal duration, distinguish totals from average daily rates and account for incomplete final periods. Rolling smoothing improves readability but does not create new evidence.
Run the worked example
Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 17 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.
from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT
orders = load_orders()
local = orders.assign(local_time=orders['ordered_at'].dt.tz_convert('Asia/Kolkata'))
daily = local.set_index('local_time')['net_paise'].resample('D').sum()
calendar = pd.date_range('2026-01-01', '2026-03-31', freq='D', tz='Asia/Kolkata')
daily = daily.reindex(calendar, fill_value=0)
rolling = daily.rolling(7, min_periods=7).mean()
result = pd.DataFrame({'daily_inr': daily / 100, 'seven_day_mean_inr': rolling / 100})
print(result.head(9).round(2).to_string())
assert len(result) == 90
assert rolling.iloc[:6].isna().all()
assert int(daily.sum()) == int(orders['net_paise'].sum())
Verified output
daily_inr seven_day_mean_inr
2026-01-01 00:00:00+05:30 995.0 NaN
2026-01-02 00:00:00+05:30 2093.0 NaN
2026-01-03 00:00:00+05:30 2295.4 NaN
2026-01-04 00:00:00+05:30 3995.0 NaN
2026-01-05 00:00:00+05:30 3395.0 NaN
2026-01-06 00:00:00+05:30 2032.3 NaN
2026-01-07 00:00:00+05:30 1497.0 2328.96
2026-01-08 00:00:00+05:30 2935.2 2606.13
2026-01-09 00:00:00+05:30 5792.0 3134.56
Walk through the result
The daily index uses local midnight boundaries. The first six rolling values remain missing because a complete seven-day window is not yet available. The seventh row averages the first seven calendar days, including explicit zeros if any were present. The total reconciliation checks that changing grain did not lose revenue. A rolling mean is reported in INR per day here, not total INR per week.
Common mistakes and diagnosis
Do not call a rolling mean “weekly revenue”; its unit is average daily revenue. Do not fill the first six rolling values with zero, which would falsely claim a measured mean. A 30-day rolling window is not a calendar month. Centered windows include future observations relative to the displayed date and are inappropriate for a feature that must be available at prediction time.
Practice assignment
Compute seven-day rolling revenue totals and prove that each complete total equals seven times the corresponding rolling mean. Then calculate calendar-month totals and explain why February is not directly comparable to a 31-day month without a duration note.
Worked solution and explanation
Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.
weekly_total = daily.rolling(7, min_periods=7).sum()
assert np.allclose(weekly_total.dropna().astype(float), (rolling.dropna() * 7).astype(float))
monthly = daily.resample('MS').sum()
assert int(monthly.sum()) == int(daily.sum())
print('Monthly INR:', (monthly / 100).round(2).tolist())
Check your understanding
Why can rolling(7) mean seven different lengths of time?
Answer guide
It counts observations, not elapsed time. Seven transactions can occur in minutes or months. Only after establishing a complete daily grid does seven observations correspond to seven calendar days. A time-based rolling window expresses elapsed duration directly, but still requires clear timezone and boundary policies.
Reference and next step
Download the complete course kit: datasets, all examples, solutions, and capstone.
Return to the full Data Analysis with Python and pandas curriculum.
