Lesson 38 · Pandas Full Course
Group Data by Month, Weekday and Hour in Pandas (Time-Based groupby) | Pandas Course #38
In this lesson you'll be able to: Group by year, month and weekday with .dt parts Group by real time periods with pd.Grouper and resample(on=) Build several…
- CoursePandas Full Course
- Lesson38 of 65
- FormatJupyter notebook · 45 code cells
- Data3 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- stocks.csv preview148.1 KB
- superstore.csv preview1.7 MB
- taxis.csv preview849.0 KB
📓 Full notebook
Download .ipynbPandas 38: Time-based Grouping and Aggregations#
In this lesson you'll be able to:
- Group by year, month and weekday with .dt parts
- Group by real time periods with pd.Grouper and resample(on=)
- Build several time-based summaries in one step
- Find hour-of-day and weekday patterns, in calendar order
- Compare months across years with pivots and transform Data: four years of Superstore orders, plus our New York taxi trips
Warm-up: yearly returns#
Lesson 37 used resample and pct_change on daily prices.
import pandas as pd
stocks = pd.read_csv("data/pandas/stocks.csv", index_col="Date",
parse_dates=True)
year_end = stocks["GSPC"].groupby(stocks.index.year).last()
year_end.pct_change().round(3).head(4)
Group by calendar parts#
Problem: total the sales for each year, month or weekday.
orders = pd.read_csv("data/pandas/superstore.csv")
print(orders.shape)
cols = ["Order ID", "Order Date", "Sales", "Profit"]
orders[cols].sample(3, random_state=42)
Common mistake: using .dt on a text column
orders.groupby(orders["Order Date"].dt.year)["Sales"].sum()
orders = pd.read_csv("data/pandas/superstore.csv",
parse_dates=["Order Date", "Ship Date"],
date_format="%m/%d/%Y")
od = orders["Order Date"]
print(od.min().date(), od.max().date())
by_year = orders.groupby(od.dt.year)["Sales"].sum()
by_year.round(0)
Quick check: which calendar month sells the most, across all four years?
by_month = orders.groupby(od.dt.month)["Sales"].sum()
print(by_month.idxmax(), by_month.max().round(0))
print(by_month.idxmin(), by_month.min().round(0))
yq = orders.groupby([od.dt.year, od.dt.quarter])["Sales"].sum()
yq.unstack().round(0)
per_year = orders.groupby(od.dt.year).agg(
orders=("Order ID", "nunique"),
sales=("Sales", "sum"),
profit=("Profit", "sum"))
per_year.round(0)
ship = (orders["Ship Date"] - od).dt.days
ship.groupby(od.dt.year).mean().round(2)
Group by real periods with pd.Grouper#
Problem: month numbers mix years together. We want each actual month.
per_q = pd.Grouper(key="Order Date", freq="QE")
q_sales = orders.groupby(per_q)["Sales"].sum()
q_sales.head(4).round(0)
q2 = orders.resample("QE", on="Order Date")["Sales"].sum()
print(q2.equals(q_sales))
Quick check: how many groups does a monthly Grouper make, compared with .dt.month?
per_m = pd.Grouper(key="Order Date", freq="ME")
print(orders.groupby(per_m).ngroups)
print(orders.groupby(od.dt.month).ngroups)
per_y = pd.Grouper(key="Order Date", freq="YE")
cat = orders.groupby([per_y, "Product Category"])["Sales"].sum()
cat.unstack().round(0)
per_w = pd.Grouper(key="Order Date", freq="W")
weekly = orders.groupby(per_w)["Order ID"].nunique()
print(len(weekly), weekly.mean().round(1))
print(weekly.idxmax().date(), weekly.max())
Common mistake: Grouper without a key
orders.groupby(pd.Grouper(freq="YE"))["Sales"].sum()
by_idx = orders.set_index("Order Date")
by_idx.groupby(pd.Grouper(freq="YE"))["Sales"].sum().round(0)
Your turn#
Which real month had the most distinct orders, and which the fewest?
n_orders = orders.groupby(per_m)["Order ID"].nunique()
print(n_orders.idxmax().date(), n_orders.max())
print(n_orders.idxmin().date(), n_orders.min())
Several summaries per period#
Problem: one table with orders, sales, profit and margin each month.
monthly = orders.resample("ME", on="Order Date").agg(
orders=("Order ID", "nunique"),
sales=("Sales", "sum"),
profit=("Profit", "sum"))
monthly["margin"] = monthly["profit"] / monthly["sales"]
monthly.head(3).round(2)
best = monthly["margin"].idxmax()
worst = monthly["margin"].idxmin()
print(best.date(), monthly.loc[best, "margin"].round(3))
print(worst.date(), monthly.loc[worst, "margin"].round(3))
monthly["aov"] = monthly["sales"] / monthly["orders"]
monthly["aov"].describe().round(0)
cust = orders.groupby("Customer Name")["Order Date"].agg(
first="min", last="max")
cust["span"] = (cust["last"] - cust["first"]).dt.days
print(len(cust), cust["span"].median())
by_m = orders.resample("ME", on="Order Date")["Profit"]
loss = by_m.agg(lambda x: (x < 0).mean())
print(loss.mean().round(3))
print(loss.idxmax().date(), loss.max().round(3))
prof = orders.pivot_table(index=od.dt.year,
columns="Product Category",
values="Profit", aggfunc="sum")
prof.round(0)
Your turn#
Which quarter had the highest average order value?
qs = orders.resample("QE", on="Order Date").agg(
orders=("Order ID", "nunique"), sales=("Sales", "sum"))
aov_q = qs["sales"] / qs["orders"]
print(aov_q.idxmax().date(), aov_q.max().round(0))
Hour-of-day and weekday patterns#
Problem: when in the day, and on which days, are people busiest?
taxis = pd.read_csv("data/pandas/taxis.csv",
parse_dates=["pickup", "dropoff"])
hour = taxis["pickup"].dt.hour
by_hour = taxis.groupby(hour)["fare"].agg(["count", "mean"])
by_hour.sort_values("count").tail(3).round(2)
print(by_hour["mean"].idxmax(), by_hour["mean"].max().round(2))
print(by_hour.loc[5, "count"])
import matplotlib.pyplot as plt
ax = by_hour["count"].plot(kind="bar", figsize=(9, 4), rot=0)
ax.set_title("Taxi pickups by hour of day")
ax.set_xlabel("Hour of pickup")
ax.set_ylabel("Trips")
plt.show()
card = taxis[taxis["payment"] == "credit card"]
tip_pct = card["tip"] / card["fare"]
by_h = tip_pct.groupby(card["pickup"].dt.hour).mean()
print(by_h.idxmax(), by_h.max().round(3))
print(by_h.idxmin(), by_h.min().round(3))
day = taxis["pickup"].dt.day_name()
taxis.groupby(day)["fare"].count()
Quick check: what order are those weekdays in?
week = ["Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Saturday", "Sunday"]
day_cat = pd.Categorical(day, categories=week, ordered=True)
taxis.groupby(day_cat, observed=True)["fare"].count()
part = pd.cut(hour, bins=[0, 6, 12, 18, 24], right=False,
labels=["night", "morning", "afternoon", "evening"])
grid = pd.crosstab(day_cat, part, rownames=["day"])
grid
per_day = pd.Grouper(key="pickup", freq="D")
daily = taxis.groupby(per_day).size()
print(len(daily), daily.mean().round(1))
print(daily.idxmax().date(), daily.max())
print(daily.idxmin().date(), daily.min())
Your turn#
Which weekday has the longest average trip distance?
dist = taxis.groupby(day_cat, observed=True)["distance"].mean()
print(dist.round(2))
print(dist.idxmax())
Compare periods: pivots, transform and running totals#
Problem: how does each month compare with the same month last year?
piv = orders.pivot_table(index=od.dt.month, columns=od.dt.year,
values="Sales", aggfunc="sum")
(piv / 1000).round(0)
change = piv[2012] / piv[2011] - 1
print(change.idxmax(), change.max().round(3))
print(change.idxmin(), change.min().round(3))
m_sales = monthly["sales"]
year_tot = m_sales.groupby(m_sales.index.year).transform("sum")
share = m_sales / year_tot
share.loc["2012"].round(3).head(3)
smooth = m_sales.rolling(3).mean()
print(smooth.idxmax().date(), smooth.max().round(0))
print(smooth.idxmin().date(), smooth.min().round(0))
yoy = m_sales.pct_change(12)
yoy.loc["2012"].round(3).head(3)
ytd = m_sales.groupby(m_sales.index.year).cumsum()
ytd.loc["2011-11":"2012-02"].round(0)
Your turn#
What were year-to-date sales at the end of June, for each year?
june = ytd[ytd.index.month == 6]
june.round(0)
Mini-project: when should the store staff up?#
Plan: each month's share of its year, averaged, then checked for consistency.
cal = share.groupby(share.index.month).agg(["mean", "min"])
cal.sort_values("mean", ascending=False).round(3).head(4)
busy = share > 1 / 12
years_busy = busy.groupby(busy.index.month).sum()
print(years_busy.to_dict())
q_piv = orders.pivot_table(index=od.dt.quarter, columns=od.dt.year,
values="Sales", aggfunc="sum")
(q_piv / q_piv.sum()).round(3)
ax = cal["mean"].plot(kind="bar", figsize=(8, 4), rot=0)
ax.axhline(1 / 12, color="grey", linestyle="--")
ax.set_title("Average share of yearly sales, by month")
ax.set_xlabel("Month")
ax.set_ylabel("Share of the year's sales")
plt.show()
always = years_busy[years_busy == 4].index.tolist()
never = years_busy[years_busy == 0].index.tolist()
print(f"Busy every year: months {always}; never busy: {never}")
Recap#
- Convert dates first; then .dt.year, .dt.month, .dt.day_name() are group keys
- pd.Grouper(key=..., freq=...) or resample(on=...) groups real periods
- Named aggregation builds a whole report per period in one step
- Ordered categoricals with observed=True keep weekdays in order
- Pivot years side by side; transform and cumsum compare within a year
Challenges#
- Find the Superstore's busiest weekday by number of orders.
- Average taxi fare per weekday and part of the day, with pivot_table.
- Monthly profit margin for Technology only: best and worst month.
What's next#
Lesson 39: Handling Time Zones and Offsets Localise and convert time zones, and move dates with business-day offsets.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



