GroupBy: Split-Apply-Combine Stats per Ticker

GroupBy - Split, Apply, Combine

Scene: the PM wants a one-page risk sheet. Not "what did the book do?" but "what did each name do?" - average return, volatility and observation count, per ticker. Your data arrives in long format: one row per ticker per day, thousands of rows deep. You need one row per ticker.

That reshape has a name, and it is the most-used verb in data analysis: split-apply-combine.

The three steps

1. Split the rows into groups by a key (here, ticker).
2. Apply an aggregation to each group independently (mean, std, count…).
3. Combine the per-group answers into one result, indexed by the key.

Pandas does all three in one expression:

df.groupby("ticker")["ret"].agg(["mean", "std", "count"])

Read it left to right: split by ticker → look only at the ret column → apply these three aggregations to each group.

You already know this in SQL

If you took SQL Foundations, you wrote exactly this query:

SELECT ticker, AVG(ret), STDDEV(ret), COUNT(*)
FROM book
GROUP BY ticker;

groupby is GROUP BY. Same key, same aggregations, same output shape - one row per group. Seeing that two languages express one idea is what makes you fluent in both.

⚠️ The mistake that hides in plain sight

Aggregate without grouping and pandas happily returns an answer - just the wrong one:

df["ret"].agg(["mean", "std", "count"])   # ONE blended number per stat, whole book

It runs. It returns numbers. They are every name mixed together - which is exactly what the starter does, reporting n=9 instead of three groups of n=3. A silent wrong answer is more dangerous than a crash, because nothing tells you to look.

Your Task

The starter aggregates the whole book at once, so the report prints a single ret line. Replace that one line with a groupby so the report prints one line per ticker - AAPL, JPM and MSFT, each with its own mean, vol and count.

Predict before you run: the book holds 3 rows for each of 3 tickers. How many lines should the report print, and what should n= be on each?