Pandas: Labelled Data, and How Not to Fight It

Pandas is where most ML projects actually spend their time. It is also where most of them are slowest and most subtly wrong. Two ideas explain nearly all of that: the Index (which aligns everything silently) and the block manager (which decides whether an operation is a memcpy or a full reallocation).

The two data structures

A Series is a 1-D array with an index. A DataFrame is an ordered dict of Series sharing one index.

import pandas as pd, numpy as np

s = pd.Series([1.0, 2.0, 3.0], index=["a", "b", "c"], name="score")
df = pd.DataFrame({
    "user":  ["u1", "u2", "u1", "u3"],
    "event": ["click", "view", "buy", "click"],
    "value": [1.5, 0.0, 42.0, 2.0],
})

The Index is not decoration

The index drives automatic alignment. Every binary operation between two labelled objects aligns on labels first, and non-matching labels become NaN.

a = pd.Series([1, 2, 3], index=[0, 1, 2])
b = pd.Series([10, 20, 30], index=[1, 2, 3])
a + b        # 0: NaN, 1: 12, 2: 23, 3: NaN  -- not element-wise!

This is the single most common source of mysterious NaNs. After a filter, a groupby, or a concat, indices no longer line up. If you want positional arithmetic, drop the labels:

a.to_numpy() + b.to_numpy()          # positional, or
a.reset_index(drop=True) + b.reset_index(drop=True)

The same mechanism is why assigning a filtered Series back into a DataFrame "works" — it aligns on the index — and why assigning a NumPy array of the wrong length raises.

dtypes and memory

Pandas stores each column as a typed array. Getting dtypes right is usually the single biggest memory win available.

The object dtype is the enemy. A column of 1M short strings as object is a million Python string objects — roughly 60–80 bytes each plus a pointer. As category with 50 distinct values, it is 1M int8 codes plus 50 strings.

def shrink(df):
    out = df.copy()
    for c in out.columns:
        col = out[c]
        if pd.api.types.is_integer_dtype(col):
            out[c] = pd.to_numeric(col, downcast="integer")
        elif pd.api.types.is_float_dtype(col):
            out[c] = pd.to_numeric(col, downcast="float")
        elif (pd.api.types.is_object_dtype(col) or pd.api.types.is_string_dtype(col)) and col.nunique() / max(len(col), 1) < 0.5:
            out[c] = col.astype("category")
    return out

df.memory_usage(deep=True).sum() / 1e6      # deep=True actually measures strings

deep=True matters: without it, an object column reports only the 8 bytes per pointer and hides the actual strings.

PyArrow backing (dtype_backend="pyarrow", pandas 2.0+) is worth adopting for string-heavy data: it stores strings contiguously, supports genuine missing values across all types, and interoperates with Parquet and Polars without unnecessary Python-object conversion in compatible operations. Zero-copy transfer still depends on schema and operation. Check error tolerances before float downcasting; smaller storage is not worth erasing meaningful differences.

Selection: [], .loc, .iloc, and the ones to avoid

.loc's inclusive slice endpoint surprises everyone once:

df.loc["2024-01-01":"2024-01-31"]   # includes Jan 31
df.iloc[0:10]                        # rows 0..9

Chained assignment and SettingWithCopyWarning

df[df.value > 0]["flag"] = 1        # WRONG — sets on a temporary, silently lost
df.loc[df.value > 0, "flag"] = 1    # RIGHT — one indexing operation

The first line calls __getitem__ (producing a copy or a view — pandas cannot tell you which) and then __setitem__ on that object. Whether the original is modified is an implementation detail, which is why pandas warns. Always use a single .loc with both axes.

Pandas 3.0 makes Copy-on-Write the only behaviour, which removes the ambiguity: every indexing result behaves like a copy, and chained assignment simply never propagates. Writing .loc-style code today makes that migration a no-op.

Missing data

df.isna().sum()                     # count per column
df.isna().mean().sort_values()      # fraction per column — the useful view
df.dropna(subset=["y"])             # drop rows missing the target
df.fillna({"a": 0, "b": df.b.median()})
df["x"].ffill()                     # forward fill — time series only
df["x"].interpolate(method="time")

Two rules that matter more than the API:

1. Missingness is information. Add an indicator column before imputing; "this field was blank" is often predictive.
2. Fit imputation on train only. df.fillna(df.mean()) computed over the whole dataset leaks the test set's statistics into training. Use sklearn.impute.SimpleImputer inside a Pipeline so the fit/transform split is enforced.

Note the sentinel differences: np.nan for floats, pd.NaT for datetimes, pd.NA for the nullable dtypes. None in an object column becomes NaN in a float column. np.nan != np.nan, so always use .isna(), never == np.nan.

GroupBy: split, apply, combine

Four apply-shapes, and choosing the right one is most of the skill:

g = df.groupby("user", observed=True, sort=False)

g["value"].agg(["mean", "std", "count"])
g.agg(total=("value", "sum"), n_events=("event", "nunique"))   # named aggregation

df["value_z"] = g["value"].transform(lambda s: (s - s.mean()) / s.std())
df["group_mean"] = g["value"].transform("mean")                # vectorised, much faster

big = g.filter(lambda d: len(d) >= 10)

Performance notes that actually matter:

• Set observed=True explicitly for categorical grouping across versions (it is the pandas 3 default). With observed=False, pandas can produce the full Cartesian product of category levels — a real memory bomb with multiple categorical keys.
• sort=False skips sorting the group keys when you do not need ordered output.
• String aggregation names beat lambdas. transform("mean") uses a Cython path; transform(lambda s: s.mean()) runs Python per group. The gap is often 50×.
• as_index=False or .reset_index() to get a flat frame back.
• .apply() on many small groups is the classic slow line in a pandas pipeline. Look for an agg/transform equivalent first.

The window functions people reimplement badly

For chronological features, first sort by user and event timestamp, then rebuild g = df.groupby("user", observed=True, sort=False). Grouping itself does not sort each user's observations. The following rolling(7) means seven rows, not seven days, and includes the current observation; choose strict-past semantics explicitly when the feature must describe only earlier events.

df["rank_in_user"]  = g["value"].rank(method="dense", ascending=False)
df["cum_value"]     = g["value"].cumsum()
df["prev_value"]    = g["value"].shift(1)
df["delta"]         = df["value"] - df["prev_value"]
df["roll7"]         = g["value"].rolling(7, min_periods=1).mean().reset_index(0, drop=True)
df["expanding_max"] = g["value"].expanding().max().reset_index(0, drop=True)

shift inside a group is how you build lag features without leaking across users. Getting the groupby wrong here — computing a lag over the whole frame — is one of the most common causes of leakage in time-series feature engineering.

Joins and merges

pd.merge(left, right, on="key", how="inner",
         validate="one_to_many", indicator=True, suffixes=("_l", "_r"))

validate= is the most under-used argument in pandas. "one_to_one", "one_to_many", "many_to_one" — it raises if the relationship you assumed is violated. An unexpected many-to-many join silently multiplies your row count, duplicates training examples, and inflates your metrics. Assert the cardinality instead of discovering it later.

indicator=True adds a _merge column showing left_only / right_only / both, which turns "why did I lose 3% of rows?" into a one-line check.

before = len(df)
out = df.merge(dim, on="id", how="left", validate="many_to_one", indicator=True)
assert len(out) == before, f"row count changed: {before} -> {len(out)}"
print(out["_merge"].value_counts())

Other joining tools:

• df.join(other) — joins on the index, convenient but easy to misuse.
• pd.concat([a, b], axis=0) — stack rows; axis=1 aligns on index.
• pd.merge_asof(left, right, on="time", by="id", direction="backward") — the as-of join: for each left row, take the most recent right row at or before its timestamp. Both inputs must be sorted by the merge-time key. For historical correctness, join on when the value became available, not just its event date; use tolerance for freshness and allow_exact_matches=False when ties must be excluded. A backward join alone does not prove absence of leakage.

Reshaping

wide = df.pivot_table(index="user", columns="event", values="value",
                      aggfunc="sum", fill_value=0, observed=True)

long = wide.reset_index().melt(id_vars="user", var_name="event", value_name="value")

pivot requires unique index/column pairs and raises otherwise; pivot_table aggregates duplicates. Reach for pivot_table unless you want the uniqueness check.

explode turns a column of lists into one row per element — the standard move for tokenised text or multi-label targets.

Time series

df["ts"] = pd.to_datetime(df["ts"], utc=True, format="ISO8601")
df = df.set_index("ts").sort_index()

df.resample("1D").agg({"value": "sum", "user": "nunique"})
df["value"].rolling("7D", closed="left").mean()  # value-only, strict-past window
df.between_time("09:00", "17:00")
df.tz_convert("America/New_York")
df.asfreq("1h").ffill()             # lowercase hourly alias; unique index required

The .dt accessor gives calendar features in one line:

df["hour"]    = df.index.hour
df["dow"]     = df.index.dayofweek
df["is_wknd"] = df.index.dayofweek >= 5
# cyclical encoding so 23:00 and 00:00 are adjacent
df["hour_sin"] = np.sin(2 * np.pi * df["hour"] / 24)
df["hour_cos"] = np.cos(2 * np.pi * df["hour"] / 24)

Timezones are where correctness lives. Store UTC, convert for display, and distinguish local wall time from an absolute instant. A naive index has no DST knowledge. Parsing a naive local time with utc=True interprets it as UTC; it does not recover the intended timezone. Localize first with tz_localize(zone, ambiguous=..., nonexistent=...), then convert to UTC. Duration windows and local calendar-day windows differ around clock changes. See the time-series guide.

Never shuffle a time series into a random train/test split. Use TimeSeriesSplit or an explicit date cutoff; a random split lets the model see the future.

Performance

# 1M rows, a simple derived column
df["z"] = df.apply(lambda r: r.a * 2 + r.b, axis=1)   # ~10 s
df["z"] = df.a * 2 + df.b                              # ~5 ms

Other levers:

• Read only what you need: pd.read_csv(..., usecols=[...], dtype={...}, parse_dates=[...]). Specifying dtypes up front avoids a full inference pass and the object fallback.
• Chunk large files: pd.read_csv(path, chunksize=500_000) yields frames you can aggregate incrementally.
• Use Parquet, not CSV. Columnar, compressed, typed, and it stores the schema, so no re-inference. Typically 5–10× smaller and 10–50× faster to load.
• pd.eval / df.query avoid intermediate temporaries for large frames.
• Avoid growing a frame in a loop. pd.concat inside a loop is \(O(n^2)\); collect a list and concat once.
• copy=False and in-place-ish patterns matter less than they used to; under Copy-on-Write pandas avoids most defensive copies itself.

When to leave pandas

DuckDB is the highest-leverage addition to a pandas toolkit: duckdb.sql("SELECT ... FROM 'data/*.parquet'").df() runs a vectorised, multi-threaded, larger-than-memory query and hands you back a DataFrame.

Correctness checklist for an ML pipeline

• Assert row counts around every merge.
• validate= on every merge.
• Check for duplicates on the key before joining: df.id.duplicated().any().
• Verify the target's distribution did not change after transformation.
• Never fit a scaler, imputer, or encoder on the full dataset — inside a Pipeline, fit on train only.
• Use merge_asof or grouped shift for any feature derived from history.
• Freeze the schema: assert dtypes and column sets after loading, so an upstream change fails loudly rather than silently producing object columns.
• Log df.describe() and df.isna().mean() at pipeline boundaries; the diff between runs catches data drift early.

Common bugs

Self-check

Worked ingestion and temporal checks

Pandas merges match null keys to null keys, unlike ordinary SQL equality joins. Decide whether that is meaningful before joining. A failed validate should lead to a documented duplicate-resolution rule, not arbitrary drop_duplicates merely to silence the exception. A left anti-join can be obtained from a validated left merge's left_only indicator rows.

from io import StringIO
import numpy as np
import pandas as pd
raw = "user,value\na,10\nb,4\na,20\nb,6\n"
parts = [chunk.groupby("user")["value"].agg(["sum", "count"])
         for chunk in pd.read_csv(StringIO(raw), dtype={"user": "string", "value": "int64"}, chunksize=2)]
totals = pd.concat(parts).groupby(level=0).sum()
assert totals.loc["a", "sum"] == 30 and totals.loc["b", "count"] == 2
assert pd.isna(pd.Series([True, pd.NA], dtype="boolean").iloc[1])
assert (pd.NA & False) is False
local = pd.DatetimeIndex(["2025-03-09 01:30", "2025-03-09 03:30"])
utc = local.tz_localize("America/New_York", ambiguous="raise", nonexistent="raise").tz_convert("UTC")
assert utc[1] - utc[0] == pd.Timedelta("1h")
events = pd.DataFrame({"user": ["a", "a"], "time": pd.to_datetime(["2025-01-10", "2025-01-03"], utc=True)})
prices = pd.DataFrame({"user": ["a"], "available": pd.to_datetime(["2025-01-02"], utc=True), "price": [10.]})
joined = pd.merge_asof(events.sort_values("time"), prices.sort_values("available"),
    left_on="time", right_on="available", by="user", direction="backward", tolerance=pd.Timedelta("3D"))
assert len(joined) == len(events)
assert joined.price.iloc[0] == 10 and pd.isna(joined.price.iloc[1])
left = pd.DataFrame({"key": [1., np.nan]})
right = pd.DataFrame({"key": [np.nan], "value": [7]})
assert left.merge(right, on="key", how="left", validate="many_to_one").value.iloc[1] == 7
print("schema, chunk aggregation, nullable logic, DST, freshness, null-key checks passed")

Why sum counts rather than average chunk means? Unequal chunk/group sizes make the unweighted mean of means wrong; retain mergeable sufficient statistics. For variance, use a stable count/mean/centered-sum-of-squares combination rather than subtracting huge nearly equal raw moments. Why retain unavailable rows? Dropping stale unmatched rows can silently change the evaluation population; represent unavailable features explicitly and test the model's missing-data policy. The merge API documents null matching and cardinality validation.

1. a + b on two Series of the same length gives NaNs. Give the cause and two fixes.
2. What is the difference between .agg, .transform, and .apply on a GroupBy, and which produces the same number of rows as the input?
3. Why is df[df.a > 0]["b"] = 1 wrong, and what replaces it?
4. Your merge turned 1M rows into 1.4M. Name the argument that would have caught it at the moment of the join.
5. A 4 GB CSV becomes 22 GB in memory. What are the two most likely causes?
6. You need "the most recent price at or before each trade". Which pandas function, and why is a plain merge wrong?
7. Why must a scaler be fit inside a Pipeline rather than on the full frame?

Where to go next

• NumPy — the arrays underneath every column.
• Scikit-learn — pipelines, and where the fit/transform discipline is enforced.
• Visualization — plotting what you just wrangled.