skip to content

GroupBy

groupby splits rows by keys, then aggregates, transforms or filters each group. Interviewers ask agg versus transform versus apply, named aggregation and size versus count.

on this pageshow

explore

questions

11

In pandas, what does `orders.groupby('customer_id')['amount'].sum()` return, and how do you get `customer_id` back as a column?

level: juniorimportance: must knowfreq 80%

answer

  1. where the group keys end up
  2. one column selected, one dimension back
  3. an option on groupby itself
  4. reset_index after the fact

basics

~20 s

A Series of totals, one per customer, whose index holds the sorted customer_id values and whose name is amount. Pass as_index=False to groupby, or call reset_index() on the result, to get a DataFrame with customer_id as a column.

solid answer

~30 s

It returns a `Series`. Selecting one column with single brackets gives a one-dimensional result, the group keys become its index (an `Index` named `customer_id`, sorted because `sort=True` is the default), and the Series keeps the name `amount`. Two calls give a flat `DataFrame` with `customer_id` as an ordinary column: `orders.groupby('customer_id', as_index=False)['amount'].sum()`, or `.sum().reset_index()` on the Series; both end with a fresh `RangeIndex`. Double brackets, `[['amount']]`, also give a `DataFrame`, but the keys stay in the index. Grouping by a list of columns puts a `MultiIndex` in the index instead, and the same two calls flatten it.

code

python · 15 lines
python
import numpy as np
import pandas as pd

orders = pd.DataFrame({
    "customer_id": ["c2", "c1", "c2", "c1", "c3"],
    "amount": [35.0, 20.0, 15.0, np.nan, 50.0],
})

totals = orders.groupby("customer_id")["amount"].sum()
print(type(totals).__name__, totals.index.name)
print(totals)

flat = orders.groupby("customer_id", as_index=False)["amount"].sum()
print(flat)
print(totals.reset_index().equals(flat))

go deeper

for a junior

Recall that groupby puts the keys in the index of the result, and name both as_index=False and reset_index() as ways to make customer_id a column again.

for a middle

Explain the chain DataFrameGroupBy, SeriesGroupBy, aggregation, and how single versus double brackets decide Series versus DataFrame while the keys stay in the index either way.

for a senior

Show you guard downstream steps: merges and exports expect keys as columns, and a MultiIndex from several keys or a pd.Grouper must be flattened deliberately before it leaves the function.

for a principal

Argue for a team convention, such as always as_index=False or a final reset_index in shared transforms, so grouped outputs have one predictable shape across a codebase.

## What the call returns `orders.groupby('customer_id')` does not compute anything yet. It returns a **`DataFrameGroupBy`** object that remembers how the rows are split. Selecting `['amount']` narrows it to a **`SeriesGroupBy`**, and `.sum()` is the aggregation that finally runs, producing **one row per group**. The result is a **`Series`**, and its parts are worth naming precisely in an interview: - **Values**: the total of `amount` for each customer. Missing amounts are skipped, as in `Series.sum`. - **Index**: the distinct `customer_id` values, in an `Index` whose `name` is `customer_id`. The key has moved out of the data and into the row labels. - **Name**: `amount`, carried over from the selected column. - **Order**: sorted by key, because `groupby` has `sort=True` by default. `sort=False` keeps the order in which each key first appears. - **Dtype**: whatever the aggregation produces; a float `amount` column gives a `float64` total. So the answer to "what comes back" is: a Series indexed by the group keys, not a table with two columns. ## Why the keys go to the index Putting the keys in the index is not an accident of formatting; it makes the result a lookup table keyed by customer: - `totals.loc['c1']` reads one customer's total directly by label. - Arithmetic between two grouped results lines up by key. Dividing `orders.groupby('customer_id')['amount'].sum()` by `orders.groupby('customer_id').size()` gives the average order value per customer, matched label to label, even if one result lists the customers in a different order. - `totals.sort_values(ascending=False).head(3)` ranks customers while keeping their IDs attached as labels. The index layout is the right shape for further pandas work; the column layout is the right shape for leaving pandas, such as writing a file or handing the table to another tool. ## Getting the key back as a column Reports, merges and CSV exports usually want `customer_id` as an ordinary column. There are two standard ways, plus one that people mistake for a third: | Expression | Returns | Where `customer_id` lives | |---|---|---| | `orders.groupby('customer_id')['amount'].sum()` | `Series` | the index | | `orders.groupby('customer_id')[['amount']].sum()` | `DataFrame` | still the index | | `orders.groupby('customer_id', as_index=False)['amount'].sum()` | `DataFrame` | a column; new `RangeIndex` | | `orders.groupby('customer_id')['amount'].sum().reset_index()` | `DataFrame` | a column; new `RangeIndex` | 1. **`as_index=False`** tells the aggregation to put the group keys in columns from the start. It is an argument of `groupby` and applies to the aggregations that follow, including `agg` and `size` (where the count arrives in a column called `size`). 2. **`reset_index()`** works on any result after the fact: it moves every index level into columns and installs a default `RangeIndex`. It is the more general tool, because it also flattens results you did not build yourself. Double brackets are the trap. `[['amount']]` selects a one-column `DataFrame`, so the result is two-dimensional, but the keys are still in the index; the printout looks like a table with a column header row for the index, which misleads people into thinking the key became a column. ## Keys other than a single column `by` accepts more than one column name, and every form changes what the index looks like: - **A list of columns**, `groupby(['customer_id', 'category'])`, gives a **`MultiIndex`** with one level per key and one row per combination that occurs. `as_index=False` or `reset_index()` turn every level into a column. - **An index level**, `groupby(level='customer_id')`, groups a frame whose index already holds the key; `level` also takes a position. - **A function** is called on each index label, and its return value is the group key. - **A Series** or array of the same length, such as `orders['order_date'].dt.month`, groups by those values. - **`pd.Grouper`** describes a key with options: `pd.Grouper(key='order_date', freq='ME')` bins a datetime column into month-end periods and can sit in a list beside other keys. Time binning as a topic belongs to the resampling material; here it is just one more way to say "the key". ## Common slips - Expecting `customer_id` and `amount` as two columns from the plain call, then failing on `result['customer_id']` with a `KeyError`. - Calling `reset_index(drop=True)` to "clean up" the result, which discards the customer IDs instead of moving them into a column. - Assuming results come back in the original row order; they come back sorted unless `sort=False` is passed. - Merging a grouped Series back onto the orders table without first turning the key into a column, or without telling `merge` to use the index. Remember the rule in one line: **the keys go to the index unless you say otherwise**, and `as_index=False` or `reset_index()` is how you say otherwise.

  • What changes if you group by `['customer_id', 'category']` instead?
    The result's index becomes a two-level `MultiIndex`, one entry per customer and category combination that actually occurs in the data. Selecting by customer then takes one label, and by both a tuple. `as_index=False` or `reset_index()` turns both levels into ordinary columns, which is what a flat report or an export wants.
  • How would you total orders per calendar month without leaving groupby?
    Pass a `pd.Grouper` as a key: `orders.groupby(pd.Grouper(key='order_date', freq='ME'))['amount'].sum()` bins the datetime column into month-end periods and labels each row with the period end. It combines with other keys in a list, such as `[pd.Grouper(key='order_date', freq='ME'), 'customer_id']`.
  • When would you pass `sort=False` to `groupby`?
    When the sorted order is not needed: the totals are identical, but rows come back in the order each key first appears in the data, and skipping the sort can save time with many groups. If a report must be sorted by total rather than by key, sort the result explicitly afterwards.

  • The plain call returns a DataFrame with customer_id and amount columns.
  • reset_index(drop=True) moves customer_id into a column.
  • Double brackets, [['amount']], put customer_id into the columns.
  • groupby returns groups in the original row order by default.
  • as_index=False changes which rows are summed, not just the result's layout.
open as a page

In pandas, how do you compute several differently named summary columns per customer with `groupby().agg`, and why prefer named aggregation?

level: middleimportance: must knowfreq 70%

basics

~20 s

Pass keywords to agg, each naming an output column and pairing a source column with a function: agg(total=('amount', 'sum'), orders=('amount', 'size')). Lists and dicts of functions produce two-level column labels to flatten; named aggregation gives flat, chosen names.

open as a page

In pandas, how do `groupby().size()`, `groupby().count()` and `groupby().nunique()` differ when the `amount` column has missing values?

level: middleimportance: must knowfreq 65%

basics

~20 s

size() counts every row in each group, missing values included, and returns one number per group. count() counts non-missing values per column, so a NaN amount lowers it. nunique() counts distinct non-missing values per column.

open as a page

In pandas, how does groupby transform('sum') put each store's total on every row, and how does transform with a function differ?

level: middleimportance: must knowfreq 70%

basics

~20 s

transform returns a result with the grouped frame's index and length. transform('sum') broadcasts each store's total onto its rows for a share column; a function runs per group in Python and must return a scalar or a group-length result.

open as a page

In pandas, why is a per-department z-score computed with groupby apply slow when there are many departments, and what do you replace it with?

level: seniorimportance: must knowfreq 65%

basics

~20 s

groupby apply calls a Python function once per group and then concatenates the pieces and infers the result's shape, so its cost grows with the number of groups. (x - g.transform('mean')) / g.transform('std') computes the same z-score with grouped built-ins.

open as a page

In pandas, how do you add a running sales total, the previous sale and a sale number within each store using groupby?

level: juniorimportance: should knowfreq 50%

basics

~20 s

Call cumsum, shift(1) and cumcount on df.groupby('store'). Each returns one value per input row, aligned to the original index and restarting at every store, in the frame's current row order, so sort by date first.

open as a page

In pandas 3.0, why can `orders.groupby('customer_id')['amount'].agg(np.std)` return different numbers from `.agg('std')`, and which should you pass?

level: middleimportance: should knowfreq 45%

basics

~20 s

In pandas 3.0 agg calls a NumPy or builtin function as given instead of swapping in the pandas method. np.std then gives the population deviation (ddof=0) while 'std' uses ddof=1, and np.median returns NaN for groups with NaN. Pass string aliases.

open as a page

In pandas, what does groupby filter return when dropping stores with fewer than five sales, and when is a transform mask better?

level: middleimportance: should knowfreq 55%

basics

~20 s

df.groupby('store').filter(lambda g: len(g) >= 5) returns the original rows of every store whose function returned True, with their original labels, order and columns. A mask from transform('size') >= 5 selects the same rows without a Python call per store.

open as a page

In pandas, per-customer totals from `orders.groupby('customer_id')['amount'].sum()` add up to less than `orders['amount'].sum()`; why, and how do you fix it?

level: seniorimportance: should knowfreq 50%

basics

~20 s

groupby defaults to dropna=True, so rows whose customer_id is missing belong to no group and their amounts vanish from the totals without a warning. Pass dropna=False to keep a NaN group, or fill the key first, for example with 'guest'.

open as a page

In pandas 3.0, why does a function passed to df.groupby('dept').apply() raise KeyError on g['dept'], and how do you fix it?

level: seniorimportance: should knowfreq 50%

basics

~10 s

In pandas 3.0, DataFrameGroupBy.apply passes each group without the grouping columns, and include_groups=True raises ValueError. Read the key from g.name, or select the columns you need, key included, right after groupby.

open as a page

In pandas, how do you keep the top two products by revenue in each store, and why prefer sort_values then groupby head over apply with nlargest?

level: seniorimportance: should knowfreq 60%

basics

~10 s

Sort by revenue descending, then call groupby('store').head(2). head is a vectorized filtration that returns the original rows, labels and columns; apply(lambda g: g.nlargest(2, 'revenue')) runs Python per store and adds a store index level.

open as a page