41 · Manipulation: groupby, merge, sort
Split-apply-combine with groupby, SQL-style joins with merge, and sorting — the three operations that underlie almost every feature engineering pipeline.
Most data transformations are one of three patterns: **split-apply-combine** (group rows by a key, compute something within each group, reassemble — this is `groupby`), **join** (bring two tables together by a shared key — this is `merge`), or **sort** (reorder rows by value — this is `sort_values`). Mastering these three unlocks feature engineering: you can turn a raw transaction log into a per-user feature matrix in fewer than ten lines.
Without this:
Without `groupby`, computing per-category aggregations (mean purchase value by region, churn rate by user segment, click-through rate by ad campaign) would require manually splitting the DataFrame into subsets, looping, applying, and concatenating — twenty lines for what `groupby().mean()` does in one. Without `merge`, combining information from different tables (customers + orders + products) would require explicit loops over index lookups. These two operations together are the engine of 90% of feature engineering.
The split-apply-combine pattern is the mental model behind groupby:
- Split — partition the DataFrame into groups based on a key column
- Apply — compute something within each group (sum, mean, count, custom function)
- Combine — reassemble the per-group results into a new DataFrame
df.groupby('region')['revenue'].sum() does all three steps in one expression. The result is a Series with the unique values of 'region' as the index.
For multi-column aggregations, .agg() accepts a dict mapping column names to aggregation functions:
df.groupby('region').agg({'revenue': ['mean', 'std', 'count']})
This returns a DataFrame with a MultiIndex on the columns — the outer level is the column name, the inner level is the aggregation function name.
Merge implements relational joins. The four how modes mirror SQL:
inner— keep only rows whose key appears in both tablesleft— keep all rows from the left table;NaNfor unmatched right rowsright— keep all rows from the right table;NaNfor unmatched left rowsouter— keep all rows from both tables;NaNwhere no match
Default is inner. Always check .shape before and after a merge.
Python (in browser)
`.groupby('region')['revenue'].sum()` is the three-step split-apply-combine in one line. Chaining `.sort_values(ascending=False)` immediately gives you the ranking — useful for dashboards, feature importance tables, and any 'top N' analysis.
Python runs entirely in your browser via Pyodide (~6 MB on first Run, cached after).
Python (in browser)
`.agg({'col': ['mean', 'std', 'count']})` returns a DataFrame with a MultiIndex on the columns. To flatten it for downstream use: `agg_result.columns = ['_'.join(c) for c in agg_result.columns]`. In ML, groupby + agg is the engine of feature engineering — `df.groupby('user_id').agg({'purchase': ['sum', 'count', 'mean']})` creates three user-level features from a raw transaction log in one call.
Python runs entirely in your browser via Pyodide (~6 MB on first Run, cached after).
Python (in browser)
A `left` join keeps every row of the left table (customers) and attaches matching rows from the right (orders). Customers with no orders get `NaN` in the order columns — exactly what you want when creating a 'total spend per customer' feature (you'd then `.fillna(0)` on the amount). Inner join would silently drop customers without orders.
Python runs entirely in your browser via Pyodide (~6 MB on first Run, cached after).
Choosing the wrong `how` is a silent bug. `inner` silently drops rows; `outer` silently introduces `NaN`. Always verify with `.shape` before and after the merge — if the row count jumps unexpectedly, check for duplicate keys in one of the tables (one-to-many joins multiply rows).
What does `df.groupby('col').mean()` do when the DataFrame has non-numeric columns?
- `.groupby('key').agg(...)` is split-apply-combine: partition by key, apply an aggregation within each group, return a new DataFrame indexed by the unique key values.
- `.merge(other, on='key', how='left|right|inner|outer')` is a relational join. Verify `.shape` before and after — unexpected row count growth means duplicate keys (Cartesian explosion).
- `.sort_values('col', ascending=False)` sorts rows. `.sort_index()` sorts by the row index.
- In pandas 2.0+, numeric aggregations on a `groupby` silently drop non-numeric columns. Select columns explicitly before aggregating.
Feature engineering is mostly `groupby`. `df.groupby('user_id')['purchase'].sum()` creates a total-spend-per-user feature in one line. Train and test data often live in separate tables joined with `.merge()`. Class-balanced sample weights use `groupby('label').transform('count')`. Leaderboard-style model comparisons use `groupby('model').agg({'metric': ['mean', 'std']})`.
If you remove it: Without `groupby`, computing user-level aggregations from a row-per-transaction log would require writing explicit Python loops — both slower and more error-prone. Without `merge`, combining feature tables with label tables would require manual index alignment that breaks silently when row orders differ. These two operations are so fundamental that pandas documentation calls them 'the bread and butter of data manipulation'.