Totals by Week and Month: resample
Total a column by month with groupby on dt.to_period('M'), and by week with resample('W') on a date index, which labels each week by the Sunday it ends on and keeps every week from the first to the last, so a week with no orders shows as 0 instead of disappearing, while its mean is NaN.
Journeyman · Python · Updated
After this lesson you can
- Total a column by month with dt.to_period('M'), and by week with resample('W') on a date index
- Explain why resample keeps an empty week that groupby leaves out, and what sum, count and mean give for it
Key takeaways
- A date column must hold dates, not text, before you total by period. read_csv's parse_dates does that as the file loads.
- groupby(orders["ordered_at"].dt.to_period("M")) totals by month, one row for each month that has rows.
- resample("W") totals by week on a date index (the dates as row labels), and labels each week by the Sunday it ends on.
- resample keeps every period from the first to the last, so an empty week shows a sum and a count of 0, while groupby leaves it out.
- The mean of an empty week is NaN, not 0, because an average of no orders doesn't exist.
"Are we busier than we were in June?" Crust & Co. Pizza's owner can't answer that from 3,006 separate orders, and a total for each of 84 days jumps around too much to read. Totals by week or by month show the trend. Weekly sales reports, monthly budgets, a science lab's readings per hour and a website's visits per day are all the same job: add up the rows that fall in each stretch of time.
The data is the shop's delivery orders, orders.csv: 3,006 orders from Monday, June 1 to Sunday, August 30, 2026, with each order's order_id, ordered_at (its date and time), neighborhood, size and total in dollars, among other columns.
Dates that pandas knows are dates
Grouping by time needs a column that holds real dates, not text that looks like dates. parse_dates= in read_csv converts the columns you name as the file loads, as Parsing Dates with to_datetime explains:
import pandas as pd
orders = pd.read_csv("orders.csv", parse_dates=["ordered_at"])
print(orders["ordered_at"].dtype)
datetime64[us]
datetime64 is pandas' type for a date and time ([us] says how fine-grained it is: microseconds). Now pandas can tell which month and week each order falls in.
Totals by month: to_period
dt.to_period("M") turns each order's date and time into its month, a whole calendar month such as June 2026. Grouping by those months gives one total each:
months = orders["ordered_at"].dt.to_period("M")
orders.groupby(months)["total"].sum()
ordered_at
2026-06 26328.12
2026-07 20631.42
2026-08 27541.69
Freq: M, Name: total, dtype: float64
Read 2026-06 as "the month of June 2026". Freq: M says the labels are months. July looks like a bad month, about $6,000 below the others. Was it? The weeks will tell.
Totals by week: resample
resample("W") cuts a timeline into weeks and summarizes each one. It works on a date index, the dates as the row labels, so set_index("ordered_at") moves the dates there first:
weekly = orders.set_index("ordered_at")["total"].resample("W").sum()
weekly
ordered_at
2026-06-07 6339.97
2026-06-14 6420.94
2026-06-21 6727.88
2026-06-28 5412.22
2026-07-05 6097.27
2026-07-12 0.00
2026-07-19 5895.19
2026-07-26 5860.79
2026-08-02 6646.18
2026-08-09 6357.22
2026-08-16 6033.61
2026-08-23 6672.43
2026-08-30 6037.53
Freq: W-SUN, Name: total, dtype: float64
W-SUN means weeks that end on Sunday, and each week is labeled by its last day: 2026-06-07 is Monday, June 1 to Sunday, June 7. Every week sells about $6,000, except the week ending July 12, which sold nothing. The owner explains: the shop closed that week to replace its oven. July wasn't a slow month; it was a short one. Use resample("ME") ("month end") for months the same way.
agg with named columns works after resample too, so one table holds each week's orders and sales:
weeks = orders.set_index("ordered_at").resample("W")
weeks.agg(orders=("order_id", "count"), sales=("total", "sum")).head(7)
orders sales
ordered_at
2026-06-07 262 6339.97
2026-06-14 254 6420.94
2026-06-21 267 6727.88
2026-06-28 224 5412.22
2026-07-05 249 6097.27
2026-07-12 0 0.00
2026-07-19 235 5895.19
The empty week
Grouping by week with to_period("W") looks like it should give the same answer:
orders.groupby(orders["ordered_at"].dt.to_period("W"))["total"].sum().head(7)
ordered_at
2026-06-01/2026-06-07 6339.97
2026-06-08/2026-06-14 6420.94
2026-06-15/2026-06-21 6727.88
2026-06-22/2026-06-28 5412.22
2026-06-29/2026-07-05 6097.27
2026-07-13/2026-07-19 5895.19
2026-07-20/2026-07-26 5860.79
Freq: W-SUN, Name: total, dtype: float64
The week of July 6 to 12 is missing. groupby makes a group only from rows that exist, and that week has none, so it leaves only 12 weeks. resample walks the calendar from the first date to the last and makes every period on the way, so all 13 weeks are there. On a chart, groupby's line would jump straight from July 5 to July 19 and hide the closure; resample's line drops to 0, which is what happened.
The empty week's sum and count are 0, but its mean is something else:
weeks["total"].mean().round(2).head(7)
ordered_at
2026-06-07 24.20
2026-06-14 25.28
2026-06-21 25.20
2026-06-28 24.16
2026-07-05 24.49
2026-07-12 NaN
2026-07-19 25.09
Freq: W-SUN, Name: total, dtype: float64
NaN ("not a number", pandas' mark for a missing value) is right: an average order size for a week with no orders doesn't exist, and 0 would claim the orders were free.
Check yourself
2 questions to try on the lesson page.
So what
Time is the most common way to group business data, and resample cuts it into weeks or months while keeping the empty ones, so a closed week shows up as the 0 it was instead of quietly vanishing.