Pandas Cheatsheet — Central Execution Trading

df.shape, df.columns, df.dtypes, df.info(), df.describe()
df.head(n), df.tail(n), df.sample(n)
df.loc[row_label, col_label]      # label-based
df.iloc[row_pos, col_pos]         # position-based
df[df.col > x]                    # boolean filter
df[(df.a > x) & (df.b < y)]      # multiple conditions (& | ~)
df.isna().sum()                   # missing per column
df.fillna(value), df.dropna()
df.astype({"col": "float64", "cat_col": "category"})
df.rename(columns={"old": "new"})
df.sort_values("col", ascending=False)
df["col"].str.lower(), df["col"].str[:3]
df["new"] = df["a"] * df["b"]     # vectorized derived column
df["rank"] = df.groupby("g")["v"].rank(method="dense", ascending=False)
df.memory_usage(deep=True)

df.groupby("key")["val"].sum() # single aggregation
df.groupby(["k1", "k2"])["v"].agg(["mean", "sum", "count"]) # single column aggregation
df.groupby("g").agg(total=("v","sum"), avg=("v","mean"))   # named agg
df.groupby("g")["v"].transform("mean")     # broadcast to original shape
df.groupby("g").filter(lambda x: len(x) > 100)
df.pivot_table(
    index="r", # keys to group pivot table index. becomes rows. each value will become a row in the new table.
    columns="c", # keys to become the new columns of the table.
    values="v", # data that gets aggregated
    aggfunc="sum", # how to aggregate functions. function / list of functions e.g. ["sum", "mean"]
    fill_value=0
)
df.melt(id_vars="id", value_vars=["a","b"])
df["col"].value_counts()
pd.crosstab(df.r, df.c, margins=True)
df.groupby("g")["v"].quantile([0.1, 0.5, 0.9])

df = df.set_index("timestamp")            # DatetimeIndex
df.index.tz_convert("UTC")
df.index.tz_localize("America/New_York")  # naive -> aware
df.resample("1min").agg({"price":"ohlc", "size":"sum"})
df.resample("10s").ffill()                # upsample + forward fill
df["close"].rolling(5).mean()
df["close"].rolling(5).std()
df["close"].expanding().max()
df["close"].shift(1)                       # lag
df["ret"] = df["close"] / df["close"].shift(1) - 1
df["ewma"] = df["close"].ewm(span=10).mean()
df["z"] = df["close"].rolling(20).apply(
    lambda x: (x[-1] - x.mean()) / x.std())
df.index.hour, df.index.minute            # time components
df.between_time("10:00", "10:30")

pd.merge(left, right, on="key", how="inner"|"left"|"right"|"outer")
pd.merge(left, right, left_on="a", right_on="b")
pd.concat([df1, df2], axis=0)              # vertical
pd.concat([df1, df2], axis=1)             # horizontal
left.join(right, on="key")                # join on index/column
pd.merge_asof(left, right, on="timestamp",
               direction="backward")      # most recent <= timestamp
# merge_asof requires both sorted by the merge key
# duplicate keys cause row explosion (m:n)

# L1 from L2 long format
l1 = ob[ob.level == 1].pivot(index="ts", columns="side",
                              values="price")
l1.columns = ["best_bid", "best_ask"]
l1["spread"] = l1.best_ask - l1.best_bid
l1["mid"] = (l1.best_bid + l1.best_ask) / 2
l1["weighted_mid"] = (l1.best_bid * ask_sz + l1.best_ask * bid_sz) \
                     / (bid_sz + ask_sz)

# Depth & imbalance
depth = ob.groupby(["ts","side"])["size"].sum().unstack()
depth["imbalance"] = (depth.B - depth.A) / (depth.B + depth.A)

# VWAP / TWAP
vwap = (trades.price * trades.size).sum() / trades.size.sum()
vwap_30m = trades.resample("30min").apply(
    lambda x: (x.price * x.size).sum() / x.size.sum())

# Slippage vs arrival (merge_asof)
merged = pd.merge_asof(trades.sort_values("timestamp"),
                       mids.sort_values("timestamp"),
                       on="timestamp", direction="backward")
slip = np.where(merged.side == "B",
                (merged.price - merged.mid) / merged.mid,
                (merged.mid - merged.price) / merged.mid) * 10_000  # bps

# Order flow imbalance
ofi = trades.assign(vol=lambda x: np.where(x.side=="B", x.size, -x.size))
ofi_1m = ofi.resample("1min")["vol"].sum()
ofi_cum = ofi_1m.cumsum()

# Realized spread (1s)
mid_1s = mid.resample("1s").last()
realized = mid_1s - mid_1s.shift(-1)

# Vectorize — avoid iterrows/apply
df["notional"] = df["price"] * df["size"]          # fast
df.apply(lambda r: r.price * r.size, axis=1)        # slow
for _, r in df.iterrows(): ...                      # slowest
[r.price * r.size for r in df.itertuples()]         # faster than iterrows

# eval / query for large DataFrames
df.query("a > 0.5 and b < 0.3") # avoid evaluating using Python. much faster.
mask = df.eval("a > 0.5 and b < 0.3")

# Categorical for low-cardinality strings
df["symbol"] = df["symbol"].astype("category")

# Downcast numerics
df["size"] = pd.to_numeric(df["size"], downcast="integer")
df["price"] = pd.to_numeric(df["price"], downcast="float")

# Memory
df.memory_usage(deep=True).sum() # 

# Chunked processing
for chunk in pd.read_csv("big.csv", chunksize=10000):
    process(chunk)

# cumsum is vectorized — prefer over loops
df["cum_vwap"] = (df.price * df.size).cumsum() / df.size.cumsum()