Building features on a panel

A panel is many groups observed over the same axis: tickers over days, customers over months, sensors over minutes. Almost every mistake in feature construction on a panel comes from forgetting one of two things: that every derived quantity has to be computed within a group, and that a raw number is meaningless when its scale differs by orders of magnitude between groups.

Setup

import pandas as pd, numpy as np

df = pd.read_csv("all_stocks_5yr.csv")
df["date"] = pd.to_datetime(df["date"])
df = df.rename(columns={"Name": "ticker"}).sort_values(["ticker", "date"]).reset_index(drop=True)
g = df.groupby("ticker")

Sorting by group then time first is not optional. shift, pct_change and rolling all assume the rows of a group are in time order.

The eight lines

# 1. a return: change relative to the previous row, within the group
df["ret"] = g["close"].pct_change()

# 2. a forward target: the NEXT row's value. shift(-1) looks forward. Never shift(1) for a target.
df["fwd_ret"] = g["ret"].shift(-1)

# 3. a multi-day signal: rolling sum of the last 5 returns, per group
df["ret5"] = g["ret"].transform(lambda x: x.rolling(5).sum())

# 4. a trailing scale: 20-day standard deviation of returns, per group
df["vol20"] = g["ret"].transform(lambda x: x.rolling(20).std())

# 5. relative to own history: today's volume over the group's trailing 20-day mean volume
df["relvol"] = df["volume"] / g["volume"].transform(lambda x: x.rolling(20).mean())

# 6. a normalised move: today's absolute return in units of the group's own volatility
df["move"] = df["ret"].abs() / df["vol20"]

# 7. a within-date rank (0..1): where this group sits in today's cross-section
df["ret_rank"] = df.groupby("date")["ret"].rank(pct=True)

# 8. a within-date z-score: same idea, in standard deviations
df["ret_z"] = (df["ret"] - df.groupby("date")["ret"].transform("mean")) / df.groupby("date")["ret"].transform("std")

Lines 1 to 6 group by the entity and operate along time. Lines 7 and 8 group by the date and operate across entities. Confusing the two axes is the second most common feature bug after getting the sign of the shift wrong.

Check the direction of the shift by eye

cols = ["date", "close", "ret", "fwd_ret"]
print(df.loc[df["ticker"] == "AAPL", cols].head(4).to_string(index=False))

      date   close       ret   fwd_ret
2013-02-08 67.8542       NaN  0.010422
2013-02-11 68.5614  0.010422 -0.025067
2013-02-12 66.8428 -0.025067 -0.001903
2013-02-13 66.7156 -0.001903 -0.000899

On each row, fwd_ret equals the next row’s ret. The first row has no return (nothing before it) and the last row of each ticker will have no target (nothing after it). A row’s features and its target must never share a day; see forward targets and look-ahead leakage.

Why per group, and why relative to own history

Volume in this file spans from zero to 618 million shares a day, and the median ticker trades about two million. A “high volume” threshold in shares would flag the same fifty names every day. Volume over the ticker’s own trailing mean asks the question that was meant: is today busy for this stock?

The same applies to moves. A 2% day is large for a utility and unremarkable for a semiconductor. Dividing by the stock’s trailing 20-day volatility puts every stock’s moves on one scale, which is what the relative volume case study needed before its target made sense.

print("volume, raw:           median %.0f   99th pct %.0f" % (df["volume"].median(), df["volume"].quantile(0.99)))
print("volume / own 20d mean: median %.2f   99th pct %.2f" % (df["relvol"].median(), df["relvol"].quantile(0.99)))

volume, raw:           median 2082094   99th pct 37862765
volume / own 20d mean: median 0.91   99th pct 2.77

Rolling windows: the version-safe way

g[col].transform(lambda x: x.rolling(n).f()) is deliberately used instead of g.rolling(n). The groupby.rolling path changes the shape and index of its result between pandas versions, and the transform path always returns a Series aligned to the original rows. It is slower on very large frames and worth it. See rolling windows per group.

What not to build yet

Not the third and fourth signal. Build the target, the one signal in the protocol, and the baseline the signal must beat. Look at the distribution of each, then score. Feature construction expands only after a baseline number exists.