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

By Michael Nocito, data analyst · Published August 7, 2026 By the end of this page you can take a DataFrame, summarize it by any column or combination of columns, get several statistics at once with sensible names, and account for every row that went in, including the ones pandas would otherwise drop without telling you. It is about twenty-five minutes, and every output shown was produced by actually running the code on the table printed below. Here is what to actually do today. In your next groupby , add a row count beside whatever statistic you are computing, using size , and run it once with dropna=False . Those two additions surface the two most common silent problems in grouped results: averages built on almost nothing, and rows that vanished because their key was missing. The short version: groupby splits the table into one mini-table per key value, applies your function to each, and combines the answers into a new table with one row per group. count skips missing values, size does not, and rows with a missing key are dropped entirely unless you ask otherwise. The split-apply-combine shape is the one idea everything else on this page hangs from, so it gets the picture. The original carries a diagram here. In words: A left-to-right pipeline in three stages. On the left, one table of seven stacked rows, where three rows share one shading, two rows share a second shading, one row has a third shading, and one row at the bottom is drawn hollow with a dashed border, meaning its key is missing. Arrows split the table into three separate mini-tables in the middle, one per shading: a three-row table, a two-row table, and a one-row table. The hollow dashed row's arrow stops at a dashed cross, showing it was dropped rather than assigned to any group. From each mini-table an arrow passes through a small function box and collapses it into a single summary row. On the right the three summary rows stack into one small result table with one row per group. The dropped dashed row never reaches the result. Every output on this page is real. One 14-row sales table, printed in full below, and every result was produced by running the code with pandas. If you already group in SQL, this page is the pandas half of a pair: GROUP BY and HAVING is the same idea in its original home, and I will point at the twin moves as they come up. Here is the whole dataset. Fourteen orders, a region, a category, and an amount. Two things are deliberately imperfect, because real data always is: order 1008 has a missing amount, and orders 1013 and 1014 have a missing region. import pandas as pd import numpy as np df = pd.DataFrame({ 'order_id': [1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014], 'region': ['East','East','East','East','East','West','West','West','West', 'South','South','South',np.nan,np.nan], 'category': ['Chairs','Chairs','Desks','Desks','Lamps','Chairs','Desks','Desks','Lamps', 'Chairs','Chairs','Lamps','Desks','Lamps'], 'amount': [120.0, 80.0, 300.0, 250.0, 40.0, 100.0, 400.0, np.nan, 60.0, 90.0, 110.0, 50.0, 260.0, 45.0], }) | order_id | region | category | amount | |---|---|---|---| | 1001 | East | Chairs | 120 | | 1002 | East | Chairs | 80 | | 1003 | East | Desks | 300 | | 1004 | East | Desks | 250 | | 1005 | East | Lamps | 40 | | 1006 | West | Chairs | 100 | | 1007 | West | Desks | 400 | | 1008 | West | Desks | NaN | | 1009 | West | Lamps | 60 | | 1010 | South | Chairs | 90 | | 1011 | South | Chairs | 110 | | 1012 | South | Lamps | 50 | | 1013 | NaN | Desks | 260 | | 1014 | NaN | Lamps | 45 | 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. summ