pandas groupby, merge, and reshaping

Multi-asset data: wide vs. long

C2-01 was one ticker. Real work involves many, and there are two ways to lay them out:

Wide — one column per ticker, dates as the index:

              AAPL    MSFT    SPY
Date
2024-01-02  185.64  370.87  472.65
2024-01-03  184.25  370.60  466.31

Long (or "tidy") — one row per (date, ticker) observation:

        date ticker   close
0 2024-01-02   AAPL  185.64
1 2024-01-02   MSFT  370.87
2 2024-01-02    SPY  472.65
3 2024-01-03   AAPL  184.25

Rules of thumb: wide wins for cross-asset math — returns, correlations (how strongly each pair of assets moves together, on a scale from −1 to +1; defined properly in A2-06), portfolio arithmetic — because aligned columns let you write prices.pct_change() once for everything. Long wins for storage, databases, and mixed extra information per row (add a sector column and it just works), and it's what most data vendors deliver. A huge fraction of practical pandas work is converting one into the other.

groupby: split, apply, combine

groupby splits rows into groups, applies a computation per group, and combines the results. On a long table:

# Mean daily return per ticker
long_df.groupby("ticker")["ret"].mean()

# Several stats at once
long_df.groupby("ticker")["ret"].agg(["mean", "std", "count"])

# Annualized vol per ticker, via a custom function
long_df.groupby("ticker")["ret"].apply(lambda x: x.std(ddof=1) * 252 ** 0.5)

That last line computes annualized volatility — each ticker's daily volatility (the standard deviation of its daily returns) rescaled to a per-year number. The convention: multiply by the square root of 252, the number of US trading days in a year — 252 ** 0.5 is Python for that square root. (ddof=1 just selects the standard "sample" flavor of the standard deviation; A2 covers why.)

Time-based grouping is everywhere in finance — per-month aggregation, per-year performance tables:

df["month"] = df.index.to_period("M")
monthly = df.groupby("month")["ret"].apply(lambda x: (1 + x).prod() - 1)

(The lambda here compounds each month's daily returns — multiply the 1-plus-return factors, subtract 1 — exactly as in C2-01. For a DatetimeIndex, resample("ME") is the cleaner spelling of the same idea; groupby generalizes it to any key — ticker, sector, volatility bucket, or signal decile, meaning you rank assets by a signal and cut them into ten equal buckets. Grouping by signal decile is the backbone of factor analysis in D2.) Two habits prevent bugs here. Check .size() first, so you know how many observations each group has. And remember that groupby output is indexed by the group key — you'll often want .reset_index() before merging it back.

merge: where alignment bugs are born

pd.merge is the SQL join for DataFrames — it combines two tables by matching rows on a shared key column (here, the date):

merged = pd.merge(prices, signals, on="date", how="inner")

The traps, in descending order of blood spilled:

1. how changes your dataset silently. inner drops non-overlapping dates (the pretest); outer keeps everything and fills NaN; left keeps the left table's calendar. Print len() before and after every merge. A merge that changes row count in a way you can't explain is a bug you haven't found yet.
2. Duplicate keys multiply rows. If a date appears 3 times in one table and 2 in the other, the merge produces 3 × 2 = 6 rows for it. A stray duplicated date in a signals file can silently double positions in a backtest. Guard with validate="one_to_one" — pandas then raises instead of exploding.
3. Timezone-naive vs. aware timestamps never match. A naive timestamp carries no timezone; an aware one does. 2024-01-02 00:00:00 and 2024-01-02 00:00:00+00:00 are different keys: the merge doesn't error, it just finds no overlap and hands you an empty or NaN-riddled result. Normalize with tz_localize(None) (or localize both) before merging.

For two DataFrames that share a date index, a.join(b) is merge-on-index shorthand — same semantics, same traps.

pivot and melt: switching layouts

# long -> wide: dates as rows, tickers as columns
wide = long_df.pivot(index="date", columns="ticker", values="close")

# wide -> long
tidy = wide.reset_index().melt(id_vars="date", var_name="ticker", value_name="close")

pivot raises if any (date, ticker) pair is duplicated — annoying but protective, for exactly the row-explosion reason above. (pivot_table aggregates duplicates instead; only use it when aggregation is what you mean.)

The mini-workflow you'll run a thousand times

Vendor gives you long; correlation math wants wide. The pipeline is three lines:

wide_prices = long_df.pivot(index="date", columns="ticker", values="close")
returns = wide_prices.pct_change().dropna()
corr = returns.corr()

The pivot step is also doing silent calendar alignment. Any ticker missing a date gets NaN there; pct_change propagates the NaN; and .dropna() (which drops whole rows by default) then keeps only the dates where every ticker traded. That's an implicit inner join. For NYSE stocks it's fine; mix in crypto or foreign listings and you're back at the pretest — deciding on purpose this time.

Practice locally (Jupyter)

This lesson has no in-browser exercise — the embedded runtime has NumPy only. Run this in a local Jupyter notebook with pandas and yfinance installed.

import pandas as pd
import yfinance as yf

# 1. Download three tickers (wide), then melt to long to simulate vendor data
raw = yf.download(["AAPL", "MSFT", "SPY"], start="2022-01-01",
                  end="2024-01-01", auto_adjust=False)["Adj Close"]
long_df = raw.reset_index().melt(id_vars="Date", var_name="ticker",
                                 value_name="close")

# 2. groupby: per-ticker observation counts and mean close
print(long_df.groupby("ticker")["close"].agg(["count", "mean"]))

# 3. long -> wide -> returns -> correlation
wide = long_df.pivot(index="Date", columns="ticker", values="close")
rets = wide.pct_change().dropna()
print(rets.groupby(rets.index.to_period("M")).apply(lambda x: (1 + x).prod() - 1).head())
print(rets.corr())

What you should see: (1) ~501 rows per ticker with plausible mean prices; (2) a monthly-returns table whose first row is January 2022 (all three negative — it was a bad month); (3) a symmetric 3×3 correlation matrix with ones on the diagonal (every asset moves perfectly with itself), and AAPL–MSFT and each–SPY correlations somewhere around 0.6–0.9. Then break it on purpose: duplicate one row of long_df and watch pivot raise, and re-run the merge of rets["AAPL"] against a 7-day-calendar series with how="inner" vs how="outer" and compare len().

Explain it in your own words

Your generative activity: explain to a colleague, in plain speech, why a merge that silently changes your row count is dangerous in a backtest, and which two checks (one about len, one about duplicate keys) you'd make routine. No code — just the reasoning.