pandas GroupBy: How to Summarize a DataFrame Without Losing Track of Your Rows

1. What groupby actually does: split, apply, combine

Before the explanation: the table has 14 rows. You group by region and get 3 rows back. In your own head, where are the other 11 rows, and what does one row of the result now mean?

The name for the mechanism is split-apply-combine. Split: pandas sorts the rows into one mini-table per distinct key value, every East row together, every West row together, every South row together. Apply: it runs your function, say a mean, on each mini-table separately. Combine: it stacks the answers into a new table with one row per group. Nothing is averaged across groups by accident, because each function call only ever saw one group's rows.

The part worth saying out loud is what one row means afterwards. Before grouping, one row was one order. After grouping by region, one row is one region. That change of meaning is called the grain of the table, and it is exactly the discipline SQL forces with GROUP BY: once the grain is one-row-per-region, an individual order's amount no longer exists in the result, and any column you want to see must be a group key or a summary. pandas will not error the way SQL does if you confuse grains; it will just hand you something you did not mean. Naming the new grain before you type is the cheap defence.

2. The basic move: one column, one statistic

The workhorse line reads almost like the sentence you would say: group by region, take the amount column, take the mean.

df.groupby('region')['amount'].mean()

region
East     158.000000
South     83.333333
West     186.666667
Name: amount, dtype: float64

Check East by hand: (120 + 80 + 300 + 250 + 40) ÷ 5 = 790 ÷ 5 = 158. South: (90 + 110 + 50) ÷ 3 = 250 ÷ 3 = 83.33. West has four rows but one amount is missing, and mean skips missing values: (100 + 400 + 60) ÷ 3 = 560 ÷ 3 = 186.67. Divided by three, not four. Keep that skip in mind; it becomes a whole section shortly.

Two smaller things before moving on. Swap mean for sum, median, min, max, or std and the shape is identical. And the result here is a Series whose index is the group key, which is fine for a quick look and awkward for anything downstream; section five shows the flat-table version.

3. Several statistics at once: agg and named aggregation

A single mean is rarely the deliverable. agg takes a list of functions and computes them per group in one pass.

df.groupby('region')['amount'].agg(['mean', 'sum', 'size', 'count'])

              mean    sum  size  count
region
East    158.000000  790.0     5      5
South    83.333333  250.0     3      3
West    186.666667  560.0     4      3

The version I actually ship is named aggregation, where you name each output column yourself and say which input column and which function feed it. The pattern is new_name=('column', 'function').

df.groupby('region').agg(
    avg_amount=('amount', 'mean'),
    orders=('amount', 'size'),
)

        avg_amount  orders
region
East    158.000000       5
South    83.333333       3
West    186.666667       4

The named form costs a few more characters and pays twice. The output columns are called avg_amount and orders instead of mean and size, so the result is readable without the code beside it. And each statistic states its input column explicitly, so adding a second value column to the table later cannot silently change what gets aggregated.

4. size against count, and which one answers your question

Before the explanation: in the table two sections back, West shows size 4 and count 3. Both claim to be counting. Decide what each one counted before reading the answer.

size counts rows in the group, missing or not. West has four order rows, so size says 4. count counts non-missing values in the chosen column. West's four rows include order 1008 with a missing amount, so count says 3. One missing value is the entire gap between the two answers.

This is exactly SQL's split between COUNT(*) and COUNT(col): size is COUNT(*), rows in the bucket, and count is COUNT(col), values present in one column. Neither is the correct one in general; they answer different questions. "How many orders did West place?" is a size question: 4. "How many West orders have an amount I can add up?" is a count question: 3. The mistake is not picking the wrong function so much as not noticing they can differ, because on clean columns they agree and the habit forms that they always will.

Say in one sentence why West's mean divided by 3 and not 4, before reading on. It is the same fact wearing a different hat: aggregations skip missing values, so the mean's denominator is count, not size. A mean over a column that is one-quarter missing is a statement about the three-quarters that answered.

5. Two grouping columns, the MultiIndex, and getting a flat table back

Pass a list of columns and the grain gets finer: one row per region per category.

df.groupby(['region', 'category'])['amount'].mean()

region  category
East    Chairs      100.0
        Desks       275.0
        Lamps        40.0
South   Chairs      100.0
        Lamps        50.0
West    Chairs      100.0
        Desks       400.0
        Lamps        60.0
Name: amount, dtype: float64

East Chairs by hand: (120 + 80) ÷ 2 = 100. East Desks: (300 + 250) ÷ 2 = 275. The staircase layout on the left is a MultiIndex: the group keys have become a two-level row index rather than ordinary columns. It prints nicely and then fights you the moment you try to merge, filter, plot, or export, because region is no longer a column you can just refer to.

Two ways to get a plain flat table, and they end in the same place. Ask for it up front with as_index=False, which keeps the keys as regular columns, or repair it afterwards with .reset_index(), which moves index levels back into columns.

df.groupby(['region', 'category'], as_index=False)['amount'].mean()

region category  amount
  East   Chairs   100.0
  East    Desks   275.0
  East    Lamps    40.0
 South   Chairs   100.0
 South    Lamps    50.0
  West   Chairs   100.0
  West    Desks   400.0
  West    Lamps    60.0

Also worth noticing: South has no Desks row at all, not a zero. Combinations with no rows simply never form a group, the same way a filtered-out group goes missing in SQL. If a category you know exists is absent from a grouped result, the rows behind it never made it into the split.

6. The small-group problem, carried over from SQL

Rankings of group averages have a structural bias: the smallest groups float to the extremes, because a mean over three values swings much more freely than a mean over three thousand. The SQL guide's fix carries straight over: always compute the group size beside the statistic, and set a floor before you read the ranking.

summary = df.groupby('region').agg(
    avg_amount=('amount', 'mean'),
    orders=('order_id', 'size'),
)
summary[summary['orders'] >= 4]

        avg_amount  orders
region
East    158.000000       5
West    186.666667       4

South, with its 3 orders averaging 83.33, is excluded from the comparison rather than allowed to look like a stable finding. The boolean filter line is pandas's HAVING: it runs on the combined result, after the groups exist, which is the only time a per-group condition can be checked. On fourteen rows the floor of 4 is obviously a demonstration; on real data the floor is a judgement you should pick from the data and write down, and choosing thresholds from the data covers how.

Now picture running the named-aggregation line on your own table, whatever it is: tickets by assignee, revenue by client, scores by school. Which group do you already suspect has single-digit rows behind a confident-looking average? That suspicion is the reason orders= goes in every grouped result I ship.

7. transform: a group statistic on every row

Everything so far collapsed the table. Sometimes you want the opposite: keep all the original rows and attach a group-level number to each, so you can compute each row's share of its group. transform applies the function per group and then broadcasts the answer back to every row of that group, so the result is the same length as the input.

d2 = df.dropna(subset=['region', 'amount']).copy()
d2['region_total'] = d2.groupby('region')['amount'].transform('sum')
d2['share'] = d2['amount'] / d2['region_total']

 order_id region category  amount  region_total  share
     1001   East   Chairs   120.0         790.0  0.152
     1002   East   Chairs    80.0         790.0  0.101
     1003   East    Desks   300.0         790.0  0.380
     1004   East    Desks   250.0         790.0  0.316
     1005   East    Lamps    40.0         790.0  0.051
     1006   West   Chairs   100.0         560.0  0.179
     1007   West    Desks   400.0         560.0  0.714
     1009   West    Lamps    60.0         560.0  0.107
     1010  South   Chairs    90.0         250.0  0.360
     1011  South   Chairs   110.0         250.0  0.440
     1012  South    Lamps    50.0         250.0  0.200

Every East row carries East's total of 790, every West row 560, every South row 250. Order 1007's share checks by hand: 400 ÷ 560 = 0.714, so one desk order is 71% of West's spend. The rule of thumb: agg when the deliverable is one row per group, transform when the deliverable is the original rows with a group fact attached. In SQL this same keep-the-rows move is a window function.

The first line dropped the rows with a missing region or amount on purpose, and out loud, before dividing. Which is the perfect segue.

8. The rows that vanish: dropna

Before the explanation: our table has 14 rows, and the region sizes in section three were 5 + 3 + 4 = 12. Two rows have been missing from every grouped result on this page so far. Which two?

Orders 1013 and 1014, the ones with a missing region. By default groupby runs with dropna=True, which means rows whose group key is missing are excluded before the split even happens. They are not a group that shows up blank; they are simply not there, and no warning is printed. On this page that is 2 rows of 14, holding 260 + 45 = 305 in sales. On a real table it can be a fifth of your revenue, absent from every regional report, with every printed number still individually correct.

One argument surfaces them as their own group.

df.groupby('region', dropna=False)['amount'].mean()

region
East     158.000000
South     83.333333
West     186.666667
NaN      152.500000
Name: amount, dtype: float64

The NaN group's mean checks by hand: (260 + 45) ÷ 2 = 305 ÷ 2 = 152.5. That row is not an error to hide. It is a measurement of your missing data, and the honest move is to report it: "two orders, 305 in sales, have no region recorded." Silent exclusions are exactly the kind of thing that belongs in writing next to a result, and documenting data limitations is the guide for how to say it. My habit is to run the dropna=False version once for every key I group by, read the NaN row, then decide what to do about it, rather than letting the default decide for me.

Edge cases that break grouped results in real data

Five that each cost someone an afternoon.

The key column has near-duplicate values. 'East' and 'East ' with a trailing space are two different groups, and your regional report quietly grows a fourth region. Normalize keys before grouping, df['region'].str.strip() at minimum. When the duplicates are messier than whitespace, that is entity resolution.

A group whose values are all missing returns NaN, not zero. If every amount in a group is missing, sum returns 0 but mean returns NaN, and a NaN in a report column tends to get "fixed" to zero by whoever formats it. A group with no measurable values and a group averaging zero are different findings.

Grouping after a merge counts duplicates. If an upstream join multiplied rows, size now counts pairs, not orders. The check is the one this page keeps using: do the group sizes still add to the row count you expected?

The MultiIndex survives into your export. Write a two-key grouped result straight to CSV and the staircase index becomes blank cells under region, which Excel users will read as missing data. Flatten with as_index=False or reset_index() before anything leaves Python.

Sorting by the index when you meant the values. sort_index() orders by group name, alphabetically. sort_values('avg_amount') orders by the number. A ranking sorted alphabetically still looks sorted, which is what makes it easy to miss.