promptdojo_

Groupby, joins, and first features — step 1 of 7

ch 35 · dataframes with numpy and pandas1/7

Groupby, joins, and your first features

Raw event rows are rarely what a model trains on. Models want one row per entity — per user, per order, per document — with columns that summarize behavior. Getting from events to that table is two moves: groupby and join.

Groupby: split → aggregate

Run the editor: group rows by a key, then aggregate each group. That's everything groupby does. In pandas:

per_user = df.groupby("user")["amount"].agg(["count", "sum", "mean"])

Each aggregate you compute — count of orders, mean amount, days since last event — is a feature: a per-entity number a model can learn from. Feature engineering sounds grand; most of it is groupby.

Joins: gluing tables on a key

Users table + orders table → one table, matched on user_id:

merged = users.merge(per_user, on="user_id", how="left")

The how matters:

  • inner keeps only keys present in both tables — silently drops users with no orders.
  • left keeps every row of the left table, filling NaN where the right side had no match — usually what you want for "attach features to my population."

The bug that eats an afternoon: joining on a key that isn't unique on one side. Every duplicate multiplies rows — a 10,000-row frame quietly becomes 130,000 and every downstream aggregate is wrong. Check len(df) before and after every merge. If it grew and you didn't expect it to, stop.

The time trap

If your label is "did the user churn in March," features must be computed from data before March. A groupby over all history happily bakes the future into your features, and the model will look brilliant in offline eval and useless in production. This is leakage — chapters 36 and 38 return to it, because it is the most expensive mistake in applied ML.

Where AI specifically gets this wrong

  • Default inner joins. Rows vanish, nobody notices until the counts look off. Say how= explicitly, every time.
  • No row-count checks around merges. One duplicated key, exploded table, poisoned averages. Two len() prints prevent it.
  • Aggregating over all time. Cursor doesn't know your label cutoff unless you put the date filter in the prompt — and in the code.