pandas: groupby, joins, resampling and time zones

pandas is where research code actually lives, and the operations that matter are the ones that respect grouping and time. Nearly every subtle bug in a research pipeline is a join that dropped rows, a groupby that reduced when it should have transformed, or a timestamp in the wrong zone.

Verb	Returns	Use for
`agg`	One row per group	Summaries — mean return by sector
`transform`	The original shape	Within-group standardisation
`apply`	Whatever you return	Flexible and slow; a last resort
`filter`	A subset of rows	Keeping groups meeting a condition

Table 3.1 · The groupby verbs. transform is the one that matters most in signal work: cross-sectional z-scoring is df.groupby("date")["signal"].transform(lambda s: (s - s.mean()) / s.std()), and it returns something you can assign straight back.

Figure 3.2 · Joining a hundred thousand rows. Both give the same answer. merge and merge_asof sort and sweep; a loop that looks up each row in a DataFrame by value does the other one, which is why a join written as a loop appears to hang rather than to be slow.

Join	Keeps	Watch for
inner	Only matching keys	Silent row loss
left	All of the left	NaNs where the right had no match
outer	Everything	Row explosion on duplicate keys
`merge_asof`	Nearest earlier key	Both sides must be sorted

Table 3.3 · Joins. Always check the row count before and after a merge. A join on a key with duplicates multiplies rows rather than matching them, and it is the failure that most often goes unnoticed until a result looks too good.

Why merge_asof is the market-data join. Market data arrives on irregular timestamps, so an exact-match join finds almost nothing and a nearest-match join can reach forward in time. merge_asof with the default backward direction matches each row to the most recent row on the other side at or before it — which is precisely "what did I know at this instant". The tolerance argument then caps how stale a match may be, so a quote from an hour ago does not get attached to a trade. It is the one join whose semantics are built for causality rather than for set membership.

Example 3.4

You join a daily signal onto minute-bar prices and the row count explodes from 100,000 to 39 million. What happened?

Show the worked solutionHide the worked solution

Worked solution

Formula $\text{rows}_{\text{out}} = \sum_k n_k^{\text{left}} \times n_k^{\text{right}}$
Substitute $\text{joining on date alone, with } 390 \text{ minutes per date}$
Solve $100{,}000 \times 390 = 39{,}000{,}000$
Answer $\text{a many-to-many join on a non-unique key}$

Sanity check. That may even be what you wanted — broadcasting a daily signal onto every minute is a legitimate operation. What is not acceptable is being surprised by it, which is why checking the row count against the expected multiple is a standard step.

pandas: groupby, joins, resampling and time zones

After this lesson you should be able to

The rest of this lesson is in Premium