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, portfolio arithmetic — because aligned columns let you write prices.pct_change() once for everything. Long wins for storage, databases, and heterogeneous metadata (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)

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)

(For a DatetimeIndex, resample("ME") from C2-01 is the cleaner spelling of the same idea; groupby generalizes it to any key — ticker, sector, volatility bucket, signal decile. Grouping by signal decile is the backbone of factor analysis in D2.) Two habits that prevent bugs: 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 SQL join for DataFrames:

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. 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 it, and .dropna() (row-wise by default) then keeps only dates where every ticker traded — an implicit inner join. For NYSE stocks that'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, ones on the diagonal, 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.