Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Pandas 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)
Date
2007      NaN
2008   -0.385
2009    0.235
2010    0.128
Name: GSPC, dtype: float64

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)
(8399, 21)
Order ID Order Date Sales Profit
33 2532 10/10/2011 426.037 -78.96
5281 53412 9/15/2009 2544.730 375.87
4912 7783 1/12/2012 31.350 -14.62

Common mistake: using .dt on a text column

orders.groupby(orders["Order Date"].dt.year)["Sales"].sum()
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[4], line 1
----> 1 orders.groupby(orders["Order Date"].dt.year)["Sales"].sum()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:6194, in NDFrame.__getattr__(self, name)
   6187 if (
   6188     name not in self._internal_names_set
   6189     and name not in self._metadata
   6190     and name not in self._accessors
   6191     and self._info_axis._can_hold_identifiers_and_holds_name(name)
   6192 ):
   6193     return self[name]
-> 6194 return object.__getattribute__(self, name)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\accessor.py:230, in Accessor.__get__(self, obj, cls)
    227 if obj is None:
    228     # we're accessing the attribute of the class, i.e., Dataset.geo
    229     return self._accessor
--> 230 return self._accessor(obj)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\accessors.py:698, in CombinedDatetimelikeProperties.__new__(cls, data)
    695 elif isinstance(data.dtype, PeriodDtype):
    696     return PeriodProperties(data, orig)
--> 698 raise AttributeError("Can only use .dt accessor with datetimelike values")

AttributeError: Can only use .dt accessor with datetimelike values
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())
2009-01-01 2012-12-30
by_year = orders.groupby(od.dt.year)["Sales"].sum()
by_year.round(0)
Order Date
2009    4209139.0
2010    3549681.0
2011    3436817.0
2012    3719964.0
Name: Sales, dtype: float64

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))
12 1467490.0
6 1030859.0
yq = orders.groupby([od.dt.year, od.dt.quarter])["Sales"].sum()
yq.unstack().round(0)
Order Date 1 2 3 4
Order Date
2009 1260412.0 886878.0 1035551.0 1026298.0
2010 825915.0 843584.0 856166.0 1024016.0
2011 847393.0 748583.0 838533.0 1002307.0
2012 964967.0 929194.0 863826.0 961976.0
per_year = orders.groupby(od.dt.year).agg(
    orders=("Order ID", "nunique"),
    sales=("Sales", "sum"),
    profit=("Profit", "sum"))
per_year.round(0)
orders sales profit
Order Date
2009 1394 4209139.0 434539.0
2010 1393 3549681.0 363871.0
2011 1317 3436817.0 381456.0
2012 1392 3719964.0 341902.0
ship = (orders["Ship Date"] - od).dt.days
ship.groupby(od.dt.year).mean().round(2)
Order Date
2009    1.92
2010    2.23
2011    1.95
2012    2.02
dtype: float64

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)
Order Date
2009-03-31    1260412.0
2009-06-30     886878.0
2009-09-30    1035551.0
2009-12-31    1026298.0
Freq: QE-DEC, Name: Sales, dtype: float64
q2 = orders.resample("QE", on="Order Date")["Sales"].sum()
print(q2.equals(q_sales))
True

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)
48
12
per_y = pd.Grouper(key="Order Date", freq="YE")
cat = orders.groupby([per_y, "Product Category"])["Sales"].sum()
cat.unstack().round(0)
Product Category Furniture Office Supplies Technology
Order Date
2009-12-31 1472672.0 1034642.0 1701825.0
2010-12-31 1252665.0 899874.0 1397142.0
2011-12-31 1269661.0 800349.0 1366807.0
2012-12-31 1183593.0 1017897.0 1518474.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())
209 26.3
2010-09-26 42

Common mistake: Grouper without a key

orders.groupby(pd.Grouper(freq="YE"))["Sales"].sum()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[16], line 1
----> 1 orders.groupby(pd.Grouper(freq="YE"))["Sales"].sum()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\util\_decorators.py:336, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
    330 if len(args) > num_allow_args:
    331     warnings.warn(
    332         msg.format(arguments=_format_argument_list(allow_args)),
    333         klass,
    334         stacklevel=find_stack_level(),
    335     )
--> 336 return func(*args, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:10835, in DataFrame.groupby(self, by, level, as_index, sort, group_keys, observed, dropna)
  10832 if level is None and by is None:
  10833     raise TypeError("You have to supply one of 'by' and 'level'")
> 10835 return DataFrameGroupBy(
  10836     obj=self,
  10837     keys=by,
  10838     level=level,
  10839     as_index=as_index,
  10840     sort=sort,
  10841     group_keys=group_keys,
  10842     observed=observed,
  10843     dropna=dropna,
  10844 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\groupby.py:1095, in GroupBy.__init__(self, obj, keys, level, grouper, exclusions, selection, as_index, sort, group_keys, observed, dropna)
   1092 self.dropna = dropna
   1094 if grouper is None:
-> 1095     grouper, exclusions, obj = get_grouper(
   1096         obj,
   1097         keys,
   1098         level=level,
   1099         sort=sort,
   1100         observed=observed,
   1101         dropna=self.dropna,
   1102     )
   1104 self.observed = observed
   1105 self.obj = obj

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\grouper.py:800, in get_grouper(obj, key, level, sort, observed, validate, dropna)
    798 # a passed-in Grouper, directly convert
    799 if isinstance(key, Grouper):
--> 800     grouper, obj = key._get_grouper(obj, validate=False, observed=observed)
    801     if key.key is None:
    802         return grouper, frozenset(), obj

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\resample.py:2565, in TimeGrouper._get_grouper(self, obj, validate, observed)
   2550 """
   2551 Parameters
   2552 ----------
   (...)
   2562 A tuple of grouper, obj (possibly sorted)
   2563 """
   2564 # create the resampler and return our binner
-> 2565 r = self._get_resampler(obj)
   2566 return r._grouper, cast(NDFrameT, r.obj)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\resample.py:2541, in TimeGrouper._get_resampler(self, obj)
   2533 elif isinstance(ax, TimedeltaIndex):
   2534     return TimedeltaIndexResampler(
   2535         obj,
   2536         timegrouper=self,
   2537         group_keys=self.group_keys,
   2538         gpr_index=ax,
   2539     )
-> 2541 raise TypeError(
   2542     "Only valid with DatetimeIndex, "
   2543     "TimedeltaIndex or PeriodIndex, "
   2544     f"but got an instance of '{type(ax).__name__}'"
   2545 )

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex'
by_idx = orders.set_index("Order Date")
by_idx.groupby(pd.Grouper(freq="YE"))["Sales"].sum().round(0)
Order Date
2009-12-31    4209139.0
2010-12-31    3549681.0
2011-12-31    3436817.0
2012-12-31    3719964.0
Freq: YE-DEC, Name: Sales, dtype: float64

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())
2012-03-31 136
2012-11-30 91

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)
orders sales profit margin
Order Date
2009-01-31 133 516302.96 62326.40 0.12
2009-02-28 101 332480.64 30422.68 0.09
2009-03-31 124 411628.73 22802.96 0.06
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))
2011-10-31 0.16
2010-03-31 0.005
monthly["aov"] = monthly["sales"] / monthly["orders"]
monthly["aov"].describe().round(0)
count      48.0
mean     2718.0
std       497.0
min      1663.0
25%      2435.0
50%      2643.0
75%      3031.0
max      3882.0
Name: aov, dtype: float64
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())
795 1077.0
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))
0.508
2010-02-28 0.584
prof = orders.pivot_table(index=od.dt.year,
                          columns="Product Category",
                          values="Profit", aggfunc="sum")
prof.round(0)
Product Category Furniture Office Supplies Technology
Order Date
2009 61805.0 178089.0 194645.0
2010 9402.0 117047.0 237423.0
2011 51682.0 86139.0 243635.0
2012 -5455.0 136746.0 210611.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))
2009-03-31 3521.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)
count mean
pickup
17 388 13.72
19 406 12.20
18 417 12.36
print(by_hour["mean"].idxmax(), by_hour["mean"].max().round(2))
print(by_hour.loc[5, "count"])
5 16.77
51
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()
No description has been provided for this image
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))
2 0.263
3 0.212
day = taxis["pickup"].dt.day_name()
taxis.groupby(day)["fare"].count()
pickup
Friday       1115
Monday        708
Saturday     1046
Sunday        868
Thursday      905
Tuesday       825
Wednesday     966
Name: fare, dtype: int64

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()
Monday        708
Tuesday       825
Wednesday     966
Thursday      905
Friday       1115
Saturday     1046
Sunday        868
Name: fare, dtype: int64
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
pickup night morning afternoon evening
day
Monday 30 206 233 239
Tuesday 36 224 270 295
Wednesday 35 288 310 333
Thursday 63 232 285 325
Friday 114 276 334 391
Saturday 160 208 323 355
Sunday 153 181 309 225
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())
32 201.0
2019-03-14 260
2019-02-28 1

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())
Monday       3.24
Tuesday      3.00
Wednesday    3.15
Thursday     3.01
Friday       2.92
Saturday     2.91
Sunday       3.03
Name: distance, dtype: float64
Monday

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)
Order Date 2009 2010 2011 2012
Order Date
1 516.0 337.0 251.0 341.0
2 332.0 272.0 300.0 276.0
3 412.0 218.0 296.0 348.0
4 393.0 267.0 288.0 268.0
5 230.0 284.0 263.0 385.0
6 263.0 293.0 198.0 277.0
7 381.0 230.0 288.0 243.0
8 330.0 208.0 275.0 303.0
9 325.0 418.0 276.0 318.0
10 362.0 365.0 306.0 351.0
11 249.0 291.0 368.0 256.0
12 416.0 368.0 329.0 355.0
change = piv[2012] / piv[2011] - 1
print(change.idxmax(), change.max().round(3))
print(change.idxmin(), change.min().round(3))
5 0.464
11 -0.304
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)
Order Date
2012-01-31    0.092
2012-02-29    0.074
2012-03-31    0.094
Freq: ME, Name: sales, dtype: float64
smooth = m_sales.rolling(3).mean()
print(smooth.idxmax().date(), smooth.max().round(0))
print(smooth.idxmin().date(), smooth.min().round(0))
2009-03-31 420137.0
2010-08-31 243634.0
yoy = m_sales.pct_change(12)
yoy.loc["2012"].round(3).head(3)
Order Date
2012-01-31    0.355
2012-02-29   -0.079
2012-03-31    0.176
Freq: ME, Name: sales, dtype: float64
ytd = m_sales.groupby(m_sales.index.year).cumsum()
ytd.loc["2011-11":"2012-02"].round(0)
Order Date
2011-11-30    3107939.0
2011-12-31    3436817.0
2012-01-31     340627.0
2012-02-29     616759.0
Freq: ME, Name: sales, dtype: float64

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)
Order Date
2009-06-30    2147290.0
2010-06-30    1669499.0
2011-06-30    1595976.0
2012-06-30    1894161.0
Name: sales, dtype: float64

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)
mean min
Order Date
12 0.098 0.095
1 0.096 0.073
10 0.093 0.086
9 0.090 0.077
busy = share > 1 / 12
years_busy = busy.groupby(busy.index.month).sum()
print(years_busy.to_dict())
{1: 3, 2: 1, 3: 3, 4: 2, 5: 1, 6: 0, 7: 2, 8: 0, 9: 2, 10: 4, 11: 1, 12: 4}
q_piv = orders.pivot_table(index=od.dt.quarter, columns=od.dt.year,
                           values="Sales", aggfunc="sum")
(q_piv / q_piv.sum()).round(3)
Order Date 2009 2010 2011 2012
Order Date
1 0.299 0.233 0.247 0.259
2 0.211 0.238 0.218 0.250
3 0.246 0.241 0.244 0.232
4 0.244 0.288 0.292 0.259
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()
No description has been provided for this image
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}")
Busy every year: months [10, 12]; never busy: [6, 8]

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#

  1. Find the Superstore's busiest weekday by number of orders.
  2. Average taxi fare per weekday and part of the day, with pivot_table.
  3. 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.