skip to content

Grouping and Aggregating

groupby by columns, levels or pd.Grouper, then agg with a list, dict or named aggregation. Interviewers ask size versus count, as_index and dropna on missing keys.

on this pageshow

questions

5

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 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, 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