How pandas turns timestamps into trends

The idea

A time series is a table where the order of the rows and the distance between them matter. pandas makes that explicit with a DatetimeIndex: put order_date in the index and every row knows where it sits on the calendar. After that you can ask three kinds of question, and each one has a different idea of what a "period" is.

Resample cuts the calendar into fixed buckets (days, weeks, months) and aggregates what is inside each one. Rolling slides a window of a fixed length along the series and aggregates what is inside it. Shifting (and pct_change) lines a row up with another row some distance away, and compares them. Buckets don't overlap, windows do, and comparisons connect two rows.

Why it exists

Almost every business question has time on one axis: orders per month, a seven-day average on the dashboard, this November against last November. You can do all of it with groupby and a .dt.month column, but you will trip over the same things: a month label that mixes January 2024 with January 2025, a month with no orders that simply disappears, a week that spans two months.

A DatetimeIndex handles these for you. resample keeps the year with the month, gives the empty months a row, and lets you switch from daily to weekly to monthly by changing one letter.

It also makes the questions honest about time. A report that says "orders were flat in the first quarter" is a claim about calendar periods, and resample makes the periods explicit in the code, so the reader of your code can see exactly which days went into which number. That matters most when someone asks you to rerun it next quarter.

How it works under the hood

Resample: calendar buckets

orders.set_index('order_date').resample('MS').size() does three things. It sorts the rows into buckets, aggregates each bucket, and fills in every bucket between the first and the last, even those with no rows. The letter picks the bucket: D for day, W for week, MS for month starting on the 1st, ME for month ending on the last day, QS for quarter, YS for year.

The label is the part that confuses people. ME labels a month with its last day (2025-01-31), MS with its first (2025-01-01). Both bucket the same orders, but if you later line up the series with another one built the other way, nothing matches and you get a wall of NaN. Pick one for the whole analysis.

size() counts rows, sum() adds a column. For revenue you first need a line_total column, then resample('MS')['line_total'].sum(). Choosing between them is the usual question: am I counting events or adding an amount?

Rolling: a sliding window

rolling(7) means "the last seven rows", and it counts rows, not days. That is only seven days if the series has exactly one row per day. This is why you resample('D') first: it guarantees one row per day, with 0 for days without orders. Then a window of 7 is a week.

A rolling average is a smoother, not a forecast. It reacts after the data does, and the wider the window, the later. Seven days removes the weekday pattern. Thirty days removes most things worth talking about, which is a trade-off, not a bug.

Growth: compare with another row

pct_change() computes (this - previous) / previous. With monthly rows it is month over month. pct_change(12) looks twelve rows back: year over year. shift(12) gives you the older value as its own column, which is handy when you want to show both numbers next to the percentage.

The choice matters when the data has seasons. Harbor & Pine's orders jump in November and December every year, so month over month will always look wild in autumn and say little. Year over year compares like with like. The first twelve months of a series have nothing to compare to, so their year-over-year value is NaN.

Date offsets and gaps

Date arithmetic works on timestamps: order_date + pd.Timedelta(days=30) is a deadline, and returned_on - order_date gives a duration whose .dt.days you can average. For gaps, reindex against pd.date_range shows which days are missing: the paid-ads log has 1,091 rows for 1,096 days. Then you choose what the gap means: fillna(0) if no row means no spend, or interpolate and ffill if it is a missing measurement.

Common misconceptions

"rolling(7) means seven days"

It means seven rows. On a series with one row per day it is seven days. On orders with a gap on Sunday it is a seven-row window that stretches across eight days, and the average quietly changes meaning. Resample to a regular frequency first, or give rolling a time window like rolling('7D'), which counts days.

"Month over month is the growth rate"

It is one growth rate, and in a seasonal business it is the least informative one. The +92.7% from October to November tells you the holiday season exists. The +13.7% from last November tells you the business is growing. Name the comparison in the chart title, so nobody reads the first as the second.

"The resample label is the month the data is from"

The label is a name for the bucket and depends on the letter you chose: MS gives the first day, ME the last. Both are January's orders. The danger is not the label, it is mixing the two when you merge or line up two series: set the same frequency on both before you join them.