groupby & merge — the SQL of pandas

groupby is the workhorse of data analysis. Once you understand its three-phase model, every aggregation question becomes mechanical.

The model — split-apply-combine.

Split — partition the rows of the DataFrame by the values in the groupby key. df.groupby('city') creates one logical group per distinct city, each containing the rows for that city.

Apply — run an aggregation function on each group independently. mean(), sum(), count(), or anything else that reduces a Series to a scalar. The function is applied per group, in parallel where possible.

Combine — stitch the per-group results back into one DataFrame, with the groupby key as the index.

The pattern is the same as SQL's GROUP BY, MapReduce's reduce step, Spark's groupBy, BigQuery's GROUP BY. The terminology differs; the mechanics are identical. If you understand this in one tool, you understand it in all of them.

Under the hood, pandas implements groupby with hash-based grouping — it computes the group key for each row, builds a dict mapping key to row indices, then applies the aggregation per group using the underlying NumPy arrays. The aggregations themselves run in C. On 10 million rows, a simple sum-by-key finishes in under a second.

Where groupby shows up in real ML/data work:

Feature engineering — group by user_id, compute mean session length, that becomes a feature.

Dataset statistics — group by class label, compute mean and std of features per class.

Reporting — group by date, sum revenue, plot.

Hyperparameter sweep analysis — group by hyperparameter, compute mean validation accuracy, find the best value.

The pattern is the same. The applications are everywhere.

df1.merge(df2, on='id', how='left')

Four 'how' modes match SQL exactly — left, right, inner, outer. Each one decides what to do with rows that have no match on the other side.

left — keep all rows of df1. Rows with no match on the right get NaN for df2's columns. Use when df1 is your 'main' dataset and you're enriching it with df2's information.

right — keep all rows of df2. Mirror image of left. Rarely used; usually swap the operands and use left.

inner — keep only rows where the join key appears in BOTH. Drops unmatched rows on either side. The default for merge in pandas, and the source of many silent-data-loss bugs.

outer — keep all rows from both. Unmatched rows on either side get NaN for the other side's columns. Used when you want to detect mismatches.

WHY YOU SHOULD ALWAYS SPECIFY HOW.

The default 'inner' merge silently drops rows. If df1 has 1000 rows and you merge with df2 that has only 800 of those keys, you silently lose 200 rows of df1. The output looks fine; you analyse and report on 800 rows; nobody notices the loss.

My rule — every merge has an explicit how= parameter. Defending in code review what you intended.

The debugging trick — indicator=True. Adds a _merge column to the result with values 'left_only', 'right_only', or 'both'. Lets you quickly see how many rows came from each side. The best 'why did rows disappear' debugger pandas has.

Handling duplicate keys — merge does a Cartesian product per key. If df1 has 3 rows with id=42 and df2 has 2 rows with id=42, the merge result has 6 rows for id=42. Easy bug. Validate uniqueness before merging or use validate='one_to_one' / 'one_to_many' to fail fast.

Merges are SQL joins with extra tools. Always specify how. Use indicator=True when debugging.

Modern pandas takes named aggregations directly in the agg() call. The output column names are exactly what you say they should be. Cleaner than the old dict-of-dicts syntax.

Look at the snippet. We groupby on two keys (country, product) and compute four aggregations.

orders=('order_id', 'count') — count the order_id column, name the output 'orders'.

revenue=('total', 'sum') — sum the total column, name it 'revenue'.

avg_value=('total', 'mean') — average the total column, name it 'avg_value'.

first_order=('created_at', 'min') — earliest created_at, name it 'first_order'.

The tuple syntax is (column, function). Pandas accepts string names for built-in aggregations ('count', 'sum', 'mean', 'min', 'max', 'std', 'var', 'first', 'last', 'nunique', 'median', etc) or callables for custom logic.

Multi-key groupby — pass a list ['country', 'product'] instead of a single key. The result has a MultiIndex with country and product as levels. reset_index() converts the MultiIndex back to flat columns.

When you need a custom aggregation that isn't in the built-ins, pass a callable: revenue_3p=('total', lambda s: s.quantile(0.3)). Slower than built-ins (it's Python, not C), but works.

For multiple aggregations per column, the syntax handles it cleanly. revenue=('total', 'sum'), avg_value=('total', 'mean') applies two functions to the same column with different output names. Cleaner than the older agg({'total': ['sum', 'mean']}) which produces awkward MultiIndex columns.

The named-agg syntax landed in pandas 0.25 (mid-2019). Most pandas tutorials online still show the older dict-of-dicts. The named-agg version is more readable. Use it.

Pandas is the right choice for most data work. There's a clear point where it stops being the right choice — and 2026's better answer is polars.

When polars wins:

Large datasets. Past 10 million rows, pandas starts feeling slow. Past 100 million, pandas often runs out of memory or takes hours for what should be minutes. Polars handles 100M+ rows comfortably on a laptop.

Complex pipelines. When you're chaining 20 operations, polars' lazy evaluation kicks in. It analyses the full pipeline before executing, optimises the plan (predicate pushdown, projection pushdown, common subexpression elimination), and runs only what's needed. Often 10-100x faster than the equivalent pandas chain on large data.

Multi-core by default. Pandas is mostly single-threaded. Polars uses Rayon (Rust's data-parallelism library) and multi-cores most operations automatically. On modern 8-16 core laptops, this is a 4-8x speedup for free.

Cleaner expressions for chained operations. The polars expression API (pl.col('x').filter(...).sum()) is more composable than pandas' boolean indexing.

Where pandas still wins:

Notebook-style exploratory analysis. Pandas' integration with Jupyter, matplotlib, and the broader scientific-Python ecosystem is more mature.

ML-specific work where downstream tools (sklearn, statsmodels, plotly) expect pandas DataFrames. Polars can convert to pandas, but the conversion itself takes time.

Legacy codebases where the team knows pandas. Switching cost is real.

My default in 2026 — pandas for ML/notebook work; polars for ETL pipelines or anything past 10M rows. They share enough vocabulary that switching mid-career is straightforward. Both languages of choice for different jobs.

End of Day 32. Pandas core wrapped.

What we covered.

Morning, the split-apply-combine model behind groupby. Three phases that match SQL's GROUP BY, MapReduce's reduce, Spark's groupBy. Once you see the model, every aggregation question is mechanical.

Midday, merge as pandas' SQL joins. Four 'how' modes — left, right, inner, outer. ALWAYS specify how= explicitly. Default 'inner' silently drops rows. indicator=True is the debugger for 'why did my row count change'.

Afternoon, modern named-aggregation syntax. agg(name=(column, function)) gives you exact output column names. Cleaner than the older dict-of-dicts. Multi-key groupby plus named aggs covers most aggregation work.

Evening, polars as the answer when pandas struggles. Past 10M rows, polars wins on speed and memory. Lazy evaluation, query optimisation, multi-core by default. Pandas still wins for ML notebook work and ecosystem integration.

A broader theme. Pandas (and polars) inherit NumPy's vectorised philosophy and apply it to labelled, indexed, mixed-type data. The performance characteristics flow from the underlying array structure. Vectorise everything; iterrows is a smell.

Tomorrow, Day 33, plotting. matplotlib basics, seaborn for statistical plots, pandas .plot() for sanity checks, plotly for interactive. Plus the one chart I make first on every new dataset.

See you in the morning.