Pandas Dates and Time Series: Resample, Rolling and Cohorts

Almost every business dataset has a time dimension: when the order was placed, when the customer signed up, when the delivery arrived. And almost every important business question is about change over time: Are we growing? Is delivery getting slower? Does demand spike on weekends? Which signup month produced the most loyal customers? Pandas has a rich set of tools for exactly these questions, built around the datetime64 type.

In this lesson we convert text to real dates, extract the parts we need (year, month, weekday), group and resample to coarser periods, smooth noisy daily numbers with rolling windows, compute growth rates, and finish with a cohort retention table, one of the most valuable analyses in any subscription or repeat-purchase business. It uses everything from the previous lessons: groupby, merge and date arithmetic.

Worked examples

Example 1: Strings to dates

Question: Convert the order date column and check it.

import pandas as pd
import numpy as np

orders = pd.read_csv("orders.csv")
print(orders["order_date"].dtype, orders["order_date"].iloc[0])

orders["order_date"] = pd.to_datetime(orders["order_date"], format="%Y-%m-%d")
print(orders["order_date"].dtype)
print(orders["order_date"].min(), orders["order_date"].max())
print(orders["order_date"].max() - orders["order_date"].min())

customers = pd.read_csv("customers.csv", parse_dates=["signup_date"])
print(customers["signup_date"].dtype)

str 2025-01-01
datetime64[us]
2025-01-01 00:00:00 2026-06-30 00:00:00
545 days 00:00:00
datetime64[us]

How it works: pd.to_datetime converts text into datetime64[ns] values. Passing an explicit format is both faster and safer than letting pandas guess. You can also convert while reading with parse_dates=[...]. Subtracting two dates returns a Timedelta.

Reading the result: Orders run from 2025-01-01 to 2026-06-30, a span of 545 days. Once a column is a real datetime, sorting, filtering and arithmetic all work correctly.

Example 2: The dt accessor

Question: Extract year, month, weekday and quarter.

d = orders["order_date"].dt
orders["year"] = d.year
orders["month"] = d.month
orders["weekday"] = d.day_name()
orders["is_weekend"] = d.dayofweek >= 5
orders["quarter"] = d.quarter
orders["week"] = d.isocalendar().week
orders["ym"] = d.to_period("M")

print(orders[["order_date", "year", "month", "weekday", "is_weekend", "quarter", "week", "ym"]].head(3).to_string(index=False))
print(orders["weekday"].value_counts().to_dict())
print(orders.groupby("is_weekend").size().to_dict())

order_date  year  month   weekday  is_weekend  quarter  week      ym
2025-01-01  2025      1 Wednesday       False        1     1 2025-01
2025-01-01  2025      1 Wednesday       False        1     1 2025-01
2025-01-02  2025      1  Thursday       False        1     1 2025-01
{'Sunday': 1079, 'Saturday': 1042, 'Monday': 796, 'Thursday': 776, 'Tuesday': 771, 'Wednesday': 769, 'Friday': 767}
{False: 3879, True: 2121}

How it works: The .dt accessor offers date components (year, month, day, dayofweek where Monday is 0, quarter, dayofyear), names (day_name(), month_name()) and conversions such as to_period("M") (a calendar month). Creating helper columns like these makes later grouping trivial.

Reading the result: The busiest weekday is Sunday with 1,079 orders, and weekends account for 35.4% of orders (2 of 7 days would be 28.6% if demand were flat).

Example 3: Date arithmetic and ranges

Question: Add and subtract time, and select by date range.

first = orders["order_date"].min()
print(first + pd.Timedelta(days=45))
print(first + pd.DateOffset(months=3))
print(pd.Timestamp("2026-06-30") - pd.Timestamp("2026-06-01"))

june = orders[(orders["order_date"] >= "2026-06-01") & (orders["order_date"] <= "2026-06-30")]
print(len(june))

q2 = orders[orders["order_date"].between("2026-04-01", "2026-06-30")]
print(len(q2))

print(pd.date_range("2026-06-01", periods=5, freq="D").strftime("%a %d").tolist())
print(pd.date_range("2026-01-31", periods=3, freq="ME").strftime("%Y-%m-%d").tolist())

2025-02-15 00:00:00
2025-04-01 00:00:00
29 days 00:00:00
630
1856
['Mon 01', 'Tue 02', 'Wed 03', 'Thu 04', 'Fri 05']
['2026-01-31', '2026-02-28', '2026-03-31']

How it works: Timedelta is a fixed duration (days, hours). DateOffset handles calendar logic such as "three months later". Text such as "2026-06-01" is automatically interpreted as a date when compared with a datetime column. date_range builds regular sequences; freq="D" is daily and "ME" is month-end.

Reading the result: June 2026 had 630 orders and the whole second quarter had 1,856. A DateOffset of three months from 2025-01-01 gives 2025-04-01.

Example 4: Daily series and resampling

Question: Turn the order list into a daily series, then weekly and monthly totals.

daily = orders.set_index("order_date").resample("D").size().rename("orders")
print(daily.head(3).to_dict())
print(len(daily), daily.min(), daily.max())

weekly = daily.resample("W").sum()
monthly = daily.resample("ME").sum()
print(weekly.tail(3).to_dict())
print(monthly.head(3).to_dict())

print(orders.groupby(pd.Grouper(key="order_date", freq="ME")).size().head(3).to_dict())
print(monthly.sum() == len(orders))

{Timestamp('2025-01-01 00:00:00'): 2, Timestamp('2025-01-02 00:00:00'): 3, Timestamp('2025-01-03 00:00:00'): 2}
546 0 34
{Timestamp('2026-06-21 00:00:00'): 155, Timestamp('2026-06-28 00:00:00'): 149, Timestamp('2026-07-05 00:00:00'): 33}
{Timestamp('2025-01-31 00:00:00'): 66, Timestamp('2025-02-28 00:00:00'): 67, Timestamp('2025-03-31 00:00:00'): 109}
{Timestamp('2025-01-31 00:00:00'): 66, Timestamp('2025-02-28 00:00:00'): 67, Timestamp('2025-03-31 00:00:00'): 109}
True

How it works: resample is groupby for time: it buckets an index by day ("D"), week ("W"), month-end ("ME") or quarter ("QE") and applies an aggregation. The daily.size() approach also fills days with no orders with 0, giving a complete series, which matters before computing rolling averages. pd.Grouper(key=..., freq=...) does the same inside groupby when the date is still a column.

Reading the result: There are 546 calendar days, with between 0 and 34 orders each. The monthly totals add back up to 6,000 orders (True), a good check that nothing was lost.

Example 5: Rolling windows

Question: Smooth the noisy daily orders with a 7-day average.

roll7 = daily.rolling(7).mean()
roll7c = daily.rolling(7, center=True).mean()
exp = daily.expanding().mean()
cum = daily.cumsum()

out = pd.DataFrame({"orders": daily, "roll7": roll7.round(1), "cum": cum}).iloc[[0, 5, 6, 7, -1]]
print(out)
print(roll7.isna().sum(), roll7.max().round(1), roll7.idxmax().date())
print(daily.rolling("30D").sum().iloc[-1])

            orders  roll7   cum
order_date                     
2025-01-01       2    NaN     2
2025-01-06       2    NaN    16
2025-01-07       2    2.6    18
2025-01-08       1    2.4    19
2026-06-30      19   20.4  6000
6 23.7 2026-05-02
630.0

How it works: rolling(window) looks at the last window observations. The first window - 1 results are NaN because there is not enough history (control this with min_periods). A rolling average removes day-to-day noise and shows the trend. center=True aligns the window around each date, a string window like "30D" uses calendar time, and expanding() uses all history so far.

Reading the result: The 7-day average peaked at 23.7 orders a day around 2026-05-02, and the last 30 days contained 630 orders.

Example 6: Growth rates

Question: How fast are monthly orders growing?

monthly_orders = daily.resample("ME").sum()
growth = monthly_orders.pct_change() * 100
diff = monthly_orders.diff()

table = pd.DataFrame({"orders": monthly_orders, "diff": diff, "growth_pct": growth.round(1)})
print(table.head(4))
print(table["growth_pct"].mean().round(2))

first_full, last_full = monthly_orders.iloc[0], monthly_orders.iloc[-1]
months = len(monthly_orders) - 1
cmgr = ((last_full / first_full) ** (1 / months) - 1) * 100
print(round(cmgr, 2))

yoy = monthly_orders.pct_change(12) * 100
print(yoy.dropna().round(1).head(3).to_dict())

            orders  diff  growth_pct
order_date                          
2025-01-31      66   NaN         NaN
2025-02-28      67   1.0         1.5
2025-03-31     109  42.0        62.7
2025-04-30     131  22.0        20.2
15.16
14.19
{Timestamp('2026-01-31 00:00:00'): 584.8, Timestamp('2026-02-28 00:00:00'): 653.7, Timestamp('2026-03-31 00:00:00'): 439.4}

How it works: pct_change() is the percentage change from the previous row; pct_change(12) compares with 12 months earlier (year over year). The compound monthly growth rate (CMGR) is the steady growth that would take you from the first month to the last: (last / first) ** (1 / months) - 1.

Reading the result: Monthly orders grew from 66 to 630 over 17 months, a compound rate of 14.19% per month, while the average of the individual month-to-month changes was 15.16%. Year-over-year growth for Jan 2026 was 584.8%.

Example 7: Weekday and hour patterns

Question: Which weekday and delivery slot are busiest, and when are deliveries slowest?

order_of_days = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
by_day = orders.groupby("weekday").agg(orders=("order_id", "count"), avg_min=("delivery_minutes", "mean")).reindex(order_of_days).round(2)
print(by_day)

heat = orders.pivot_table(index="weekday", columns="delivery_slot", values="order_id", aggfunc="count").reindex(order_of_days)
print(heat)
print(heat.stack().idxmax(), heat.stack().max())

           orders  avg_min
weekday                   
Monday        796    23.83
Tuesday       771    23.60
Wednesday     769    23.23
Thursday      776    23.84
Friday        767    23.89
Saturday     1042    23.68
Sunday       1079    23.62
delivery_slot  Evening (4-8)  Midday (10-2)  Morning (6-10)  Night (8-11)
weekday                                                                  
Monday                   318            118             188           172
Tuesday                  286            161             155           169
Wednesday                284            134             184           167
Thursday                 292            148             162           174
Friday                   276            149             159           183
Saturday                 370            192             236           244
Sunday                   394            198             224           263
('Sunday', 'Evening (4-8)') 394

How it works: reindex puts the weekday rows in calendar order instead of alphabetical. pivot_table builds a weekday × slot matrix of counts, and stack().idxmax() finds the busiest cell. This kind of table is what you would turn into a heatmap in the visualisation module.

Reading the result: The busiest weekday and slot combination is ('Sunday', 'Evening (4-8)') with 394 orders. The slowest average delivery is on Friday (23.89 minutes).

Example 8: Customer tenure

Question: How long after signing up did each customer place their first order?

first_order = orders.groupby("customer_id")["order_date"].min().rename("first_order")
last_order = orders.groupby("customer_id")["order_date"].max().rename("last_order")

c = customers.merge(first_order, on="customer_id", how="left").merge(last_order, on="customer_id", how="left")
c["days_to_first"] = (c["first_order"] - c["signup_date"]).dt.days
print(c["days_to_first"].describe().round(1))
print((c["days_to_first"] < 0).sum())

as_of = orders["order_date"].max()
c["recency_days"] = (as_of - c["last_order"]).dt.days
print(c["recency_days"].describe().round(0)[["min", "50%", "max"]].to_dict())
print((c["recency_days"] > 90).sum(), c["last_order"].isna().sum())

count    588.0
mean     107.2
std      113.3
min        0.0
25%       25.0
50%       65.5
75%      162.2
max      643.0
Name: days_to_first, dtype: float64
0
{'min': 0.0, '50%': 26.0, 'max': 536.0}
98 12

How it works: Subtracting two datetime columns produces a timedelta column, and .dt.days turns it into whole days. Recency (days since the last order, relative to the latest date in the data) is the key measure of how active a customer still is. Customers who never ordered have NaT and therefore NaN in these columns.

Reading the result: On average customers placed their first order 107 days after signing up; 0 ordered before their recorded signup date (any such rows would be a data-quality flag to investigate). 98 customers have not ordered for over 90 days.

Example 9: Signup cohorts

Question: Group customers by signup month and see how many are still ordering later.

c["cohort"] = c["signup_date"].dt.to_period("M")
cohort_size = c.groupby("cohort").size().rename("customers")
print(cohort_size.head(4).to_dict())

o = orders.merge(c[["customer_id", "cohort"]], on="customer_id")
o["order_month"] = o["order_date"].dt.to_period("M")
o["age"] = (o["order_month"] - o["cohort"]).apply(lambda x: x.n)

active = o.groupby(["cohort", "age"])["customer_id"].nunique().unstack(fill_value=0)
print(active.shape)
print(active.iloc[:3, :6])

{Period('2024-07', 'M'): 27, Period('2024-08', 'M'): 31, Period('2024-09', 'M'): 40, Period('2024-10', 'M'): 34}
(20, 24)
age      0  1  2  3  4   5
cohort                    
2024-07  0  0  0  0  0   0
2024-08  0  0  0  0  0   8
2024-09  0  0  0  0  8  11

How it works: A cohort is a group of customers who share a starting point, here the month they signed up. For every order we compute its age in months since the cohort month, then count distinct active customers per cohort and age. Subtracting two monthly periods gives a month offset (x.n extracts the integer). The result is a triangle: older cohorts have more ages to observe.

Reading the result: There are 20 cohorts and up to 24 ages. The unstack(fill_value=0) call produced a matrix with a row per cohort and a column per month since signup.

Example 10: Retention percentages

Question: What share of each cohort is still ordering after 1, 2, 3 months?

sizes = cohort_size.reindex(active.index)
retention = active.div(sizes, axis=0) * 100

print(retention.iloc[:4, :5].round(0))
avg_curve = retention[retention.columns[:7]].mean().round(1)
print(avg_curve.to_dict())

print(retention.loc[:, 0].mean().round(1))
print(retention.shape)

age        0    1    2     3     4
cohort                            
2024-07  0.0  0.0  0.0   0.0   0.0
2024-08  0.0  0.0  0.0   0.0   0.0
2024-09  0.0  0.0  0.0   0.0  20.0
2024-10  0.0  0.0  0.0  29.0  24.0
{0: 19.0, 1: 33.3, 2: 36.0, 3: 34.4, 4: 37.0, 5: 38.0, 6: 37.7}
19.0
(20, 24)

How it works: Dividing the active counts by the cohort sizes (div(..., axis=0) aligns on the rows) gives the share of the cohort active at each age. Averaging down each column gives an overall retention curve. The numbers in the first column (age 0) are often below 100% because not everyone orders in their signup month.

Reading the result: On average 19.0% of a cohort orders in their signup month, 33.3% in month 1, and 34.4% in month 3. Cohort tables like this are the standard way to see whether new customers stick around.

Common mistakes

1. Leaving dates as text, which sorts alphabetically and breaks arithmetic.

2. Ambiguous day-month formats parsed without an explicit format.

3. Using rolling on an unsorted or incomplete series. Sort and fill missing days first.

4. Forgetting that the last period of a resample may be partial.

5. Comparing a datetime column with a plain number.

orders[orders["order_date"] > 2026]

Traceback (most recent call last):
  File "example.py", line 991, in _cmp_method
    other = self._validate_comparison_value(other)
  File "example.py", line 561, in _validate_comparison_value
    raise InvalidComparison(other)
pandas.errors.InvalidComparison: 2026

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "example.py", line 1, in <module>
    orders[orders["order_date"] > 2026]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 85, in new_method
    return method(self, other)
  File "example.py", line 58, in __gt__
    return self._cmp_method(other, operator.gt)
           ~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 6735, in _cmp_method
    res_values = ops.comparison_op(lvalues, rvalues, op)
  File "example.py", line 341, in comparison_op
    res_values = op(lvalues, rvalues)
  File "example.py", line 85, in new_method
    return method(self, other)
  File "example.py", line 58, in __gt__
    return self._cmp_method(other, operator.gt)
           ~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 995, in _cmp_method
    return invalid_comparison(self, other, op)
  File "example.py", line 55, in invalid_comparison
    raise TypeError(f"Invalid comparison between dtype={left.dtype} and {typ}")
TypeError: Invalid comparison between dtype=datetime64[us] and int

Practice questions

Practice 1 (Easy): Weekend delivery time

Compare the average delivery time on weekends and weekdays.

print(orders.groupby("is_weekend")["delivery_minutes"].mean().round(2))

is_weekend
False    23.68
True     23.65
Name: delivery_minutes, dtype: float64

Practice 2 (Medium): Best month per store

For each store, find the month in which it received the most orders.

m = orders.groupby(["store_id", "ym"]).size().rename("orders").reset_index()
best = m.loc[m.groupby("store_id")["orders"].idxmax()]
print(best.head(4).to_string(index=False))
print(best["ym"].value_counts().head(3).to_dict())

store_id      ym  orders
     S01 2026-03      72
     S02 2026-03      63
     S03 2026-04      53
     S04 2026-06      59
{Period('2026-04', 'M'): 4, Period('2026-03', 'M'): 3, Period('2026-06', 'M'): 3}

Practice 3 (Hard): Seven-day trend per city

Compute a 7-day rolling average of daily orders for each city, and report the city with the highest peak.

stores = pd.read_csv("stores.csv")
o = orders.merge(stores[["store_id", "city"]], on="store_id")
city_daily = o.groupby(["city", "order_date"]).size().rename("orders").reset_index()
city_daily = city_daily.set_index("order_date").groupby("city")["orders"].apply(lambda s: s.resample("D").sum().rolling(7).mean())
peak = city_daily.groupby("city").max().sort_values(ascending=False)
print(peak.round(1).head(3))

city
Delhi        6.0
Bengaluru    5.7
Mumbai       4.7
Name: orders, dtype: float64

Key takeaways

• Convert text to datetime64 with pd.to_datetime(format=...) and extract parts with .dt.
• resample (or pd.Grouper) is groupby for time; complete the series before smoothing.
• rolling, expanding, diff and pct_change give trends and growth rates.
• Use Timedelta for exact durations and DateOffset for calendar moves.
• Recency and tenure come from subtracting date columns and using .dt.days.
• A cohort table (signup month × months since signup) shows whether customers stick around.