Pandas by verb · print(Purple)

The operations below are the whole pandas vocabulary that a cross-sectional or time-series study on a panel needs. Each is one line, written the way that survives across pandas versions (see portable pandas idioms). The examples run on a daily equity file with one row per ticker per day; the verbs are the same for any panel.

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")

Everything that follows assumes the frame is sorted by group and then by time. Every “per group along time” verb is g[col].<verb> or g[col].transform(lambda x: x.<verb>).

Change: `pct_change`, `diff`

df["ret"] = g["close"].pct_change()             # (close_t / close_{t-1}) - 1, NaN on each ticker's first row
df["dclose"] = g["close"].diff()                # close_t - close_{t-1}
df["logret"] = np.log(df["close"]).groupby(df["ticker"]).diff()   # log return: same idea on the log price

The groupby is what stops the first row of ticker B being computed from the last row of ticker A.

Shift within group: `shift`

df["ret_prev"] = g["ret"].shift(1)              # yesterday's return: a FEATURE (looks back)
df["fwd_ret"] = g["ret"].shift(-1)              # tomorrow's return: a TARGET (looks forward)
df["fwd_ret5"] = g["ret"].transform(lambda x: x.shift(-5).rolling(5).sum())   # next five days' return, summed

shift(1) moves values down one row, so the row for day t sees day t−1. shift(-1) moves them up, so the row for day t sees day t+1. A target must use a negative shift. See forward targets and leakage.

Rolling per group: `transform` + `rolling`

df["ma20"] = g["close"].transform(lambda x: x.rolling(20).mean())
df["vol20"] = g["ret"].transform(lambda x: x.rolling(20).std())
df["ret5"] = g["ret"].transform(lambda x: x.rolling(5).sum())
df["vmax60"] = g["volume"].transform(lambda x: x.rolling(60, min_periods=20).max())

Windows are in rows, not calendar days; for a daily file with no gaps they are the same thing. min_periods lets the window produce values before it is full. Why transform and not g.rolling(20): see rolling windows per group.

Expanding and exponentially weighted

df["cummax"] = g["close"].cummax()
df["ret_expanding_mean"] = g["ret"].transform(lambda x: x.expanding(min_periods=60).mean())
df["ewm_vol"] = g["ret"].transform(lambda x: x.ewm(halflife=10).std())

Relative to own history

df["relvol"] = df["volume"] / g["volume"].transform(lambda x: x.rolling(20).mean())     # ratio to trailing mean
df["vol_z"] = (df["volume"] - g["volume"].transform(lambda x: x.rolling(60).mean())) / g["volume"].transform(lambda x: x.rolling(60).std())
df["vol_rank_own"] = g["volume"].transform(lambda x: x.rolling(250, min_periods=60).rank(pct=True))   # rank within own trailing year

`transform` versus `apply` versus `agg`

per_ticker_mean = g["ret"].agg("mean")                            # one number per group  -> short Series indexed by ticker
df["ret_demeaned"] = df["ret"] - g["ret"].transform("mean")       # broadcast back to every row -> same length as df
ticker_stats = g["ret"].apply(lambda x: pd.Series({"n": x.notna().sum(), "std": x.std()})).unstack()   # arbitrary per-group logic
print(ticker_stats.head(3).round(5).to_string())

             n      std
ticker                 
A       1258.0  0.01548
AAL     1258.0  0.02246
AAP     1258.0  0.01896

agg collapses, transform broadcasts, apply does whatever the function does (here it returns a Series per group, which stacks into a two-level index; unstack() turns that into one row per group). When the intent is “compute per group and put it back on every row”, it is always transform. See groupby: transform versus apply.

Across the cross-section: rank, z-score, bucket per date

dfc = df.dropna(subset=["ret", "fwd_ret"]).copy()      # per-date ops need the column to exist on that date
by_date = dfc.groupby("date")
dfc["ret_pct"] = by_date["ret"].rank(pct=True)                                              # 0..1 within each date
dfc["ret_z"] = (dfc["ret"] - by_date["ret"].transform("mean")) / by_date["ret"].transform("std")
dfc["ret_dec"] = by_date["ret"].transform(lambda x: pd.qcut(x.rank(method="first"), 10, labels=False))   # deciles 0..9

rank(method="first") inside qcut breaks ties, so ten bins always come out even when many values are equal. Without it qcut raises on duplicate bin edges. The dropna first is not optional either: on the first date of the file every return is NaN, and qcut on an all-NaN group raises.

Correlate per date

ic = (dfc.groupby("date")[["ret", "fwd_ret"]]
         .apply(lambda x: x["ret"].corr(x["fwd_ret"], method="spearman"))
         .dropna())
print("mean daily rank corr(ret today, ret tomorrow): %+.4f over %d days" % (ic.mean(), len(ic)))

Select the columns before apply so the lambda receives only what it needs; it is faster and it avoids the pandas 2.2 deprecation around grouping columns being passed through. This is the daily rank IC in one expression.

Mean of a target by signal bucket

dec_means = dfc.groupby("ret_dec")["fwd_ret"].mean()
print((dec_means * 1e4).round(2).to_string())

Wide and long: `pivot`, `stack`, `melt`

wide = df.pivot(index="date", columns="ticker", values="close")     # one column per ticker
print(wide.shape)
long = wide.stack().rename("close").reset_index()                   # back to one row per (date, ticker)
print(long.shape)

A wide frame is the natural shape for market-level quantities: wide.pct_change().mean(axis=1) is the equal-weight market return per day.

Merge on date

mkt = df.groupby("date")["ret"].mean().rename("mkt_ret").reset_index()
df = df.merge(mkt, on="date", how="left")
df["excess"] = df["ret"] - df["mkt_ret"]

Left-merge a per-date table back onto the panel, then compute row-wise. For per-ticker tables (sector, listing date) the key is ticker. Always check how="left" did not change the row count.

print(len(df), "rows after merge")

Resample by calendar period

monthly_close = (df.set_index("date").groupby("ticker")["close"]
                   .resample("M").last()                        # last close of each month, per ticker
                   .reset_index())
monthly_close["mret"] = monthly_close.groupby("ticker")["close"].pct_change()
print(monthly_close.head(3).to_string(index=False))

resample needs a DatetimeIndex, hence the set_index("date") first. “M” is month end; “W-FRI” is weekly on Fridays; “Q” is quarter end.

Filter groups by a property

per = g.size()
df = df[df["ticker"].isin(per[per >= 1000].index)]          # drop short histories
print(df["ticker"].nunique(), "tickers kept")

df.groupby("ticker").filter(lambda x: len(x) >= 1000) does the same in one step and is slower on large frames.

Categoricals: one-hot with the training columns

train = pd.DataFrame({"sector": ["tech", "bank", "tech", "energy"], "x": [1.0, 2.0, 3.0, 4.0]})
test = pd.DataFrame({"sector": ["bank", "retail"], "x": [5.0, 6.0]})           # "retail" was never seen in training
Xtr = pd.get_dummies(train, columns=["sector"], dtype=float)
Xte = pd.get_dummies(test, columns=["sector"], dtype=float).reindex(columns=Xtr.columns, fill_value=0.0)
print(list(Xtr.columns))
print(Xte.to_string(index=False))

['x', 'sector_bank', 'sector_energy', 'sector_tech']
  x  sector_bank  sector_energy  sector_tech
5.0          1.0            0.0          0.0
6.0          0.0            0.0          0.0

The design matrix is defined by the training frame. reindex to its columns drops any level the model never saw (a row of zeros, which is the honest encoding of “unknown”) and adds zeros for levels absent from the test frame. Without the reindex, the two frames have different columns and the model either raises or, worse, silently reads the wrong column. The same rule applies to every transformation fitted on data: means for scaling, medians for imputation, category lists for encoding all come from the training rows only.

Impossible values, in one pass

print("high < low:", (df["high"] < df["low"]).sum(), "| zero volume:", (df["volume"] == 0).sum(),
      "| close outside [low, high]:", ((df["close"] > df["high"]) | (df["close"] < df["low"])).sum())

Dates

df["year"] = df["date"].dt.year
df["dow"] = df["date"].dt.dayofweek                      # Monday = 0
df["month_end"] = df["date"].dt.is_month_end
gap_days = df.groupby("ticker")["date"].diff().dt.days      # calendar days between consecutive rows within a ticker (daily data)
gap_hours = df.groupby("ticker")["date"].diff().dt.total_seconds() / 3600   # hourly or intraday data: .dt.days is 0 for any gap under a day
cut, embargo = pd.Timestamp("2016-06-30"), pd.Timedelta(days=7)
print("rows after the embargo:", (df["date"] > cut + embargo).sum())

Things that look right and are wrong

df["fwd_ret"] = df["ret"].shift(-1)                    # WRONG: crosses ticker boundaries
df["ma20"] = df["close"].rolling(20).mean()            # WRONG: same problem
df["ret_rank"] = df["ret"].rank(pct=True)              # WRONG: ranks across all dates, not within each date
df = df.sort_values("date")                            # WRONG before any per-ticker shift/rolling

Each of these runs without error and silently produces a feature that mixes tickers or dates. The groupby is not decoration.

Change: pct_change, diff

Shift within group: shift

Rolling per group: transform + rolling