Skip to content

pandas

pandas is a data manipulation library in python. The three layers of pandas are

  1. Data structures - Series (1-dim labelled array) and DataFrame (2-dim table of Series sharing one index)
  2. Index - pandas defining feature. drive alignment, lookup and joins. .loc vs iloc, slices, reset_index, Multiindex etc.
  3. vectorized operations - e.g. df['col1'] * df['col2'] is extremely efficient.

Selection & filtering

Three layers: [] for quick column/mask access, .loc for label-based, .iloc for position-based. Prefer .loc/.iloc for anything non-trivial — [] is ambiguous (column? mask? slice? depends on the input).

Column Selection

df["pnl"]                # → Series
df[["pnl", "currency"]]  # → DataFrame (note the double brackets)

Row selection: .loc[row_label, column_label] vs .iloc[row_index, column_index]

Note that loc[] is inclusive while .iloc is exclusive.

# .loc — label-based; slices are INCLUSIVE of the end label
df.loc[0]                          # row with index label 0
df.loc[0:5]                        # labels 0..5 inclusive
df.loc["Strategy_A"]               # row by index name
df.loc[df["pnl"] > 0]              # boolean mask
df.loc[0:5, "pnl"]                 # rows + one column
df.loc[0:5, ["pnl", "currency"]]   # rows + multiple columns

# .iloc — position-based; slices are EXCLUSIVE of the end position
df.iloc[0]                         # first row
df.iloc[-1]                        # last row
df.iloc[0:5]                       # positions 0..4
df.iloc[0:5, 2]                    # first 5 rows, 3rd column
df.iloc[:, 0]                      # all rows, first column

# .at / .iat — fast scalar access
df.at[0, "pnl"]                    # by label
df.iat[0, 2]                       # by position (fastest)

Boolean Filtering: Multiple conditions need & / | (not and/or), with each condition in parentheses — comparison binds tighter than the bitwise operators.

df[df["strategy"] == "Strategy_A"]                          # all columns
df.loc[df["strategy"] == "Strategy_A", "pnl"]               # rows + column

df[(df["strategy"] == "Strategy_A") & (df["pnl"] > 0)]      # AND
df[(df["strategy"] == "Strategy_A") | (df["strategy"] == "Strategy_B")]  # OR
df[~df["strategy"].isin(["Strategy_A"])]                    # NOT

df[df["pnl"].isin(["Strategy_A", "Strategy_B"])]            # membership
df[df["pnl"].between(-100, 100)]                            # numeric range
df[df["currency"].str.contains("USD")]                      # string ops
df[df["strategy"].str.startswith("Strategy")]

df[df["pnl"].isna()]                                        # missing values
df[df["pnl"].notna()]

Querying .query() / Evaluation .eval()

Readable string syntax for complex filters; .eval() builds the mask without materialising intermediate Python objects (fast on large frames).

df.query("strategy == 'Strategy_A' and pnl > 0")
df.query("strategy in ['Strategy_A', 'Strategy_B']")
df.query("pnl > @threshold")            # @ references a local variable

mask = df.eval("a > 0.5 and b < 0.3")   # → boolean Series
df[mask]

Notes

  • Boolean masks with and/or raise ValueError — the truth value of a Series is ambiguous. Use &/|.
  • .loc with a boolean mask + column selection is the safe, idiomatic pattern: df.loc[mask, cols].

Dtypes & casting

# --- numeric ---
df['price']  = df['price'].replace("$", "").replace(",", "").astype(float)
df['col']    = df['col'].astype(int)      # truncates! use .round() first
df['col']    = df['col'].astype(str)
df['col']    = df['col'].astype("category")          # categorical (memory-efficient)

df['col']    = pd.to_numeric(df['col'], errors='coerce')   # bad values → NaN
df['col']    = pd.to_numeric(df['col'], downcast='float')  # float64 → float32

# --- single scalar ---
price = float("$1,234.56".replace("$", "").replace(",", ""))

# --- datetime (prefer pd.to_datetime over .astype("datetime64")) ---
df['date'] = pd.to_datetime(df['date'])                     # auto-format detection
df['date'] = pd.to_datetime(df['date'], format="%Y-%m-%d")  # explicit: faster + safer
df['date'] = pd.to_datetime(df['date'], dayfirst=True)      # DD/MM/YYYY input
df['date'] = pd.to_datetime(df['date'], errors='coerce')    # bad values → NaT
df['date'] = pd.to_datetime(df['ts'], utc=True)             # timezone-aware

Missing data

GroupBy

Split-apply-combine: rows are split into groups by key, a function is applied per group, results are combined back. df.groupby("col") alone returns a lazy GroupBy object — nothing is computed until you aggregate, transform, or filter.

Aggregation .agg() — one value per group; output shape = number of groups.

# single aggregation on one column → Series
df.groupby("strategy")["pnl"].sum()

# multiple aggregations → DataFrame
df.groupby("strategy")["pnl"].agg(["sum", "mean", "count", "std"])

# different aggs per column (named aggregation)
df.groupby("strategy").agg(
    total_pnl=("pnl", "sum"),
    avg_size=("size", "mean"),
    n_trades=("pnl", "count"),
)

# grouping and getting unique values, such that count is greater than 3
df.groupby("strategy").nunique().loc[lambda x : x > 3]

# custom function per group
df.groupby("strategy").apply(lambda g: g["pnl"].sum() / g["size"].sum())

# percentiles
df.groupby("strategy")["size"].quantile([0.1, 0.5, 0.9])

# multiple keys → MultiIndex result
df.groupby(["strategy", "side"])["size"].agg(["min", "max", "count"])

Transformation .transform() — same shape as the original frame; result is broadcast back to each row. Use for group-level features on row-level data.

# each row's pnl minus its group mean (vectorised group demeaning)
df["pnl_rel"] = df["pnl"] - df.groupby("strategy")["pnl"].transform("mean")

# group size as a per-row column
df["group_n"] = df.groupby("strategy")["pnl"].transform("size")

# z-score per group
df["z"] = df.groupby("strategy")["pnl"].transform(lambda x: (x - x.mean()) / x.std())

Filtering groups .filter() — keep or drop whole groups based on a group-level condition.

# keep only strategies with more than 10 trades
df.groupby("strategy").filter(lambda g: len(g) > 10)

# keep groups whose total pnl is positive
df.groupby("strategy").filter(lambda g: g["pnl"].sum() > 0)

VWAP convention (weighted mean of price by size):

vwap = (df["price"] * df["size"]).groupby(df["symbol"]).sum() / \
       df.groupby("symbol")["size"].sum()

Notes

  • agg → one row per group; transform → one value per input row; apply → anything (slowest, least predictable — prefer agg/transform when possible).
  • as_index=False keeps the group key as a column instead of the index (or .reset_index() afterwards).
  • Group keys containing NaN are dropped by default — pass dropna=False to keep them.
  • sort=False skips sorting groups (faster when order doesn't matter); observed=True needed with categorical keys to avoid the cartesian product of categories.
  • Chaining a filter on the aggregated result: .groupby(...).agg(...).loc[lambda x: x["total"] > 1000] — x is the aggregated DataFrame.

Reshaping

Two layouts: long (one row per observation, key columns repeated) and wide (one row per entity, values spread across columns). pivot/melt move between them; stack/unstack do the same for index levels.

Pivot df.pivot() — long → wide. Unique index × columns combinations become cells filled with values. Duplicate keys raise an error.

# index → rows, columns → headers, values → cells
df.pivot(index="date", columns="symbol", values="close")

# multiple value columns → MultiIndex columns
df.pivot(index="date", columns="symbol", values=["close", "volume"])

Pivot table df.pivot_table() — the generalised pivot: takes an aggfunc (default "mean") so duplicate keys are aggregated instead of erroring. Use it whenever the data could collide.

df.pivot_table(index="date", columns="symbol", values="close", aggfunc="sum")

# different agg per column, with row/column totals
df.pivot_table(
    values=["pnl", "size"], index="strategy", columns="side",
    aggfunc={"pnl": "mean", "size": "sum"}, margins=True,
)

>>> df
     A    B      C  D  E
0  foo  one  small  1  2
1  foo  one  large  2  4
2  foo  one  large  2  5
3  foo  two  small  3  5
4  foo  two  small  3  6
5  bar  one  large  4  6
6  bar  one  small  5  8
7  bar  two  small  6  9
8  bar  two  large  7  9

>>> table = pd.pivot_table(df, values=['D', 'E'], index=['A', 'C'], aggfunc={'D': "mean", 'E': "mean"})
>>> table
                D         E
A   C
bar large  5.500000  7.500000
    small  5.500000  8.500000
foo large  2.000000  4.500000
    small  2.333333  4.333333

Melt df.melt() — wide → long (the inverse of pivot). id_vars stay as columns; value_vars are unpivoted into two columns.

# wide: date | AAPL | MSFT   →   long: date | symbol | close
df.melt(
    id_vars="date",            # columns to keep as-is
    value_vars=["AAPL", "MSFT"],  # columns to unpivot (default: all non-id)
    var_name="symbol",         # name for the former column headers
    value_name="close",        # name for the values
)

Stack / unstack — pivot for index levels. unstack moves the innermost index level to columns; stack does the inverse.

# MultiIndex rows → wide columns
df.set_index(["date", "symbol"])["close"].unstack()   # symbol level → columns

# wide columns → long rows
df.stack()                                            # innermost column level → index

# unstack a specific level
df.unstack("symbol")

Transpose df.T — swap rows and columns (rarely useful beyond quick inspection).

df.T    # rows ↔ columns

Notes

  • pivot fails on duplicate keys; pivot_table aggregates them — the common "ValueError: Index contains duplicate entries" fix.
  • melt is the standard pre-step for seaborn/plotting, which expect long-form data.
  • unstack puts NaN where an index level has no value for a column — pair with .fillna() or .dropna() as needed.
  • set_index/reset_index are the simplest reshapes: move a column into the index and back (reset_index(drop=True) discards it).
  • df.explode("col") — one row per list element in a list-valued column (long-form from nested data).

Combining

Three tools: pd.merge (SQL-style joins on keys), pd.concat (stacking frames), .join (index-based shortcut). pd.merge_asof is the time-aware special case.

Merge pd.merge() — SQL-style join on key columns; how= controls which rows survive.

pd.merge(df1, df2, on="symbol", how="inner")                       # same key name
pd.merge(df1, df2, left_on="sym", right_on="symbol", how="left")   # different names
pd.merge(df1, df2, on=["symbol", "date"], how="outer")             # composite key
pd.merge(df1, df2, on="symbol", suffixes=("_left", "_right"))      # overlapping columns
  • how: inner (default, matching keys only), outer (all), left/right (keep one side).
  • Watch for duplicated keys → fan-out (row count explodes); guard with validate="one_to_one" or "many_to_one".

Concat pd.concat() — stack frames vertically (rows) or horizontally (columns).

pd.concat([df1, df2], axis=0, ignore_index=True)   # stack rows, fresh index
pd.concat([df1, df2], axis=1)                      # side by side, aligned on index
  • axis=0 (default) stacks rows; axis=1 aligns on index.
  • ignore_index=True discards the old index; without it, duplicate index labels survive.
  • join="inner" keeps only shared columns (default "outer" fills NaN).

Join df.join() — merge on the index (convenience wrapper).

df1.join(df2, on="symbol")   # df2's index matched against df1's "symbol" column

Merge as-of pd.merge_asof() — time-aware join: for each row in the left frame, attach the most recent row from the right frame at or before the left timestamp. No exact key match needed. Classic uses: attaching the prevailing mid-price to each trade, arrival-price benchmarks, spread at trade time.

# for each trade, attach the most recent order-book snapshot mid (per symbol)
trades = trades.sort_values("timestamp")
book   = book.sort_values("timestamp")

merged = pd.merge_asof(
    trades,
    book[["timestamp", "mid"]],
    on="timestamp",          # the time key (must exist in both frames)
    by="symbol",             # group key: match within each symbol only
    direction="backward",    # most recent snapshot ≤ trade time
    tolerance=pd.Timedelta("5s"),  # max allowed time gap; unmatched → NaN
    allow_exact_matches=True,      # True (default): snapshot at t matches trade at t
)

Parameters:

  • on — the time key; both frames must be sorted by it (sort first, always).
  • by — group key(s) so matching happens per group (e.g. per symbol); prevents cross-symbol leakage.
  • direction — "backward" (default): most recent ≤ t; "forward": next ≥ t; "nearest": closest in time.
  • tolerance — max time difference (e.g. pd.Timedelta("5s")); rows with no match within tolerance get NaN. Essential for stale-data control.
  • allow_exact_matches — whether a row at exactly time t counts (default True); set False to require strictly before/after.
  • left_on/right_on — use when the time columns have different names.

Notes

  • merge_asof fails silently into wrong results if frames aren't sorted — sort both by the time key (and by keys) first.
  • Check output row count after any merge: a fan-out from duplicated keys is the most common silent bug.
  • merge matches on values; join/concat(axis=1) match on index — pick based on where the key lives.

Time Series

Two prerequisites: timestamps as datetime64 (pd.to_datetime) and, for .resample(), a DatetimeIndex (df.set_index("timestamp")). Most time-series bugs are one of these two missing.

Resample .resample() — bucket timestamps into fixed windows and aggregate. The time-series analogue of groupby.

df = df.set_index("timestamp")

# OHLC bars — first/max/min/last of price within each bucket
df.resample("5min").agg(
    open=("price", "first"),
    high=("price", "max"),
    low=("price", "min"),
    close=("price", "last"),
    volume=("size", "sum"),
)

# volume per bucket
df.resample("5min")["size"].sum()

# VWAP per bucket
df.resample("5min").apply(lambda g: (g["price"] * g["size"]).sum() / g["size"].sum())

# frequencies: "1min", "5min", "1H", "1D", "1W", "Q"

Bucket semantics label= / closed= — which edge of the window is included and which carries the label.

# default: label="left", closed="left" → bucket = [t, t+5min), labelled by start
df.resample("5min")["price"].agg(["first", "max", "min", "last"])

# label="right", closed="right" → bucket = (t-5min, t], labelled by end
df.resample("5min", label="right", closed="right")["price"].agg(["first", "max", "min", "last"])

Regular grid + forward-fill — turn irregular timestamps into a regular series, carrying the last observed value forward.

df.resample("1min").last().ffill()   # last() = per-bucket close; ffill() alone fills from index

Rolling / expanding windows — compute over the trailing n observations (or all prior).

df["ma"]     = df["price"].rolling(20).mean()          # trailing mean
df["vol"]    = df["ret"].rolling(20).std()             # trailing volatility
df["ewma"]   = df["price"].ewm(span=20).mean()         # exponentially weighted
df["cummax"] = df["price"].expanding().max()           # running max
df["rsum"]   = df["size"].rolling("1h").sum()          # time-based window (needs DatetimeIndex)

Shift / diff / pct_change — align past or future values against the present.

df["ret"]      = df["price"].pct_change()              # (t / t-1) - 1
df["prev"]     = df["price"].shift(1)                  # yesterday's price on today's row
df["fwd_ret"]  = df["price"].shift(-1) / df["price"] - 1   # forward return (label for ML)
df["gap"]      = df["timestamp"].diff()                # inter-arrival time (irregular data)

Components via .dt accessor (works on any datetime Series, index not required):

df["ts"].dt.year        # .month, .day, .hour, .minute
df["ts"].dt.dayofweek   # Monday = 0
df["ts"].dt.date        # .time, .tz_localize, .tz_convert

Notes

  • resample requires a DatetimeIndex; rolling works on any order but a time-based window ("1h") requires one.
  • rolling(window).apply(fn) is slow — prefer built-ins (mean/std/sum/quantile) whenever possible.
  • Missing datetimes are NaT, not NaN — .isna() catches both.
  • dayfirst=True in pd.to_datetime for DD/MM/YYYY data, else pandas may misread as US format.
  • Timezones: never treat exchange/UTC timestamps as timezone-naive and assume they're local — a classic quant footgun. Parse with utc=True, then .tz_convert("Asia/Singapore") to view.
  • shift on a group must be done per group: df.groupby("symbol")["price"].shift(1) — otherwise values leak across symbols.

Vectorization

# vectorized column operation — operates on the whole Series at once
notional = trades["price"] * trades["size"]

# equivalent row-by-row approaches — all slower:
# (a) apply — calls a Python function per row
trades.apply(lambda r: r["price"] * r["size"], axis=1)
# (b) itertuples
total = 0
for row in trades.itertuples():
    total += row.price * row.size
# (c) iterrows — builds a whole new Series per row (slowest)
total = 0
for _, row in trades.iterrows():
    total += row["price"] * row["size"]

Rule of thumb for iteration choices: vectorize → itertuples → apply → iterrows (last resort).