LESSON 149 · PRACTICAL PYTHON30 min

Pandas GroupBy Multiple Columns with Named Aggregations

Summarize sales by region and product, with separate total amount and distinct order count columns.

Before you start: Dictionaries · Count and Group Data with Counter and defaultdict · Read and Write JSON and CSV

Example check: Worked and guided code samples are checked against CPython 3.13. Practice uses Pyodide 0.29.5 (Python 3.13) in your browser. How we check lessons

WATCH

Video lesson: Pandas GroupBy Multiple Columns with Named Aggregations

The preview is stored on this site. YouTube loads only when you press Play. Watch on YouTube

Jump to a chapter

Read the video transcript

Select a timestamp to watch that moment on YouTube.

00:00
Suppose a report must show sales for each region and product. If we group only by region, the total can look reasonable while Pen and Pad sales are silently combined. This is the question behind the search phrase pandas groupby multiple columns: how do we define a group by a combination of values, and how do we get a useful flat result afterward? We will run the region-only mistake, compare it with a two-column group, name two aggregations, and check what happens to a row with a missing region. Our sample is tiny and held in memory, so each intermediate result can be inspected. In local Python, install pandas with `python -m pip install pandas`.
00:42
The lesson page's browser console loads its supported pandas package for these examples. Before writing code, decide the business key: a region plus a product is one bucket. Also decide whether missing-key rows should disappear or remain visible for reconciliation. West totals thirteen, and the raw data confirms that this is eight for Pen plus five for Pad. A region-only report is correct if the requested question is total sales per region. It is wrong for sales per region and product because the product dimension has disappeared. Notice why this can survive a superficial quality check: the grand total still agrees with the input.
01:24
We need to verify the result's grain, meaning what one output row represents. Here one row should represent one unique pair of region and product. In pandas, the `by` argument can be a list of column labels. Put both keys in that list. The first corrected result will use a multi-level index by default; that is fine for inspection, but if the report will be exported or merged with another table, ordinary key columns may be easier to work with. We will see both layouts so there is no mystery about where the keys went. The two-column version has a key for each pair, and its dictionary display uses tuples because the grouped Series has a multi-level index.
02:07
We can see Pad and Pen separately. That is the grouping rule we wanted, but our final report needs two measures: summed amount and number of distinct orders. A row count is not always the order count. One order may have two line items, and counting rows would overstate the number of orders. Pandas named aggregation lets us say `total=('amount','sum')` and `orders=('order_id','nunique')`. These names are part of the output schema and make downstream code easier to read. We will also use `as_index=False` so region and product are ordinary columns rather than an index.
02:47
Do not add a third key just because it is present in the table; adding order_id to the group keys would split each transaction and defeat the subtotal. The group key and the measured fields play different roles. Each output record now has region, product, total, and orders. `as_index=False` keeps the dimensions as columns, which is convenient for CSV output and later joins. Named aggregation tells pandas exactly which input column to reduce and what to call the result. We also chose `dropna=False`, and that choice deserves attention. By default, pandas drops rows whose grouping key is missing.
03:28
If a row has an amount but no region, a report made with the default may not reconcile to the source total. With `dropna=False`, the missing region becomes its own group. That does not make the source data good; it simply keeps the data discrepancy visible until it can be investigated. When using real files, inspect the dtypes of amount and order_id. Numeric amounts stored as strings need deliberate conversion before summing, and money may need a decimal or integer-cent representation. The lesson's integers avoid that separate issue so we can verify the grouping itself. The default grouping sums to six because the missing-region amount of three is
04:09
absent. With `dropna=False`, the grouped totals sum to nine, matching the input. The final number is two distinct order IDs across the groups: order one appears twice in East/Pen but contributes one to its group, and order two appears in the missing-region group. This verifies that `nunique` is doing something different from counting rows. It also shows why a report should check its invariants, not only produce a plausible table. For an actual sales pipeline, compare the raw total to the grouped total, count missing keys, and decide whether missing groups should be shown to users or placed in a data-quality queue.
04:52
On the page, complete `summarize_sales`, inspect the flat output columns, and test an additional product. The core operation is a list of grouping columns; the surrounding choices determine whether the result is trustworthy for its business question.

THE IDEA

Understand the concept

The phrase 'pandas groupby multiple columns' means one group for each distinct combination of column values. If region and product identify a sales bucket, grouping on region alone merges unlike products. Pass a list of both column labels to DataFrame.groupby: groupby(['region', 'product']). The group keys become a MultiIndex by default, which is useful for some analyses but awkward when the next step expects ordinary columns or a CSV export.

Use as_index=False to keep region and product as visible columns. Named aggregation gives each computed measure a deliberate output name: total=('amount', 'sum') and orders=('order_id', 'nunique'). This is clearer than receiving an unnamed or multi-level column layout and renaming it afterward. Distinct order count differs from row count when an order can contain multiple line items. Choose the reducer that matches the meaning of the data.

By default, pandas groupby drops rows whose group key is missing. In a report that must reconcile to the input total, this can silently lose money. Use dropna=False when a missing region or product must appear as its own group, and separately investigate why the key is missing. A missing numeric amount has different rules: sum may skip it. The tutorial keeps sample amounts as integers so this distinction is visible without introducing currency or floating-point issues.

Install pandas in local Python with `python -m pip install pandas`; the browser console loads its Pyodide package for these in-memory fixtures. The task returns a DataFrame with columns region, product, total, and orders. Tests check combined keys, output shape, distinct order IDs, and a missing-key group. If your production dataset is large, verify dtypes before grouping and benchmark memory use; this small example teaches the grouping contract, not a performance guarantee.

A list of labels groups by a combination
as_index=False retains group keys as columns
Named aggregation labels each measure
dropna=False retains missing group-key rows

WORKED EXAMPLES

See it step by step

Read the code, predict the output, then compare it with the result.

01. Compare one key with two

PYTHON

import pandas as pd
sales = pd.DataFrame({'region':['West','West'], 'product':['Pen','Pad'], 'amount':[8,5]})
print(sales.groupby('region')['amount'].sum().to_dict())
print(sales.groupby(['region','product'])['amount'].sum().to_dict())

EXPECTED OUTPUT

{'West': 13}
{('West', 'Pad'): 5, ('West', 'Pen'): 8}

The two-key result preserves product distinctions that a region subtotal hides.

02. Return a flat named report

PYTHON

import pandas as pd
sales = pd.DataFrame({'region':['West','West','East'], 'product':['Pen','Pen','Pen'], 'amount':[8,7,4], 'order_id':[1,2,3]})
report = sales.groupby(['region','product'], as_index=False).agg(total=('amount','sum'), orders=('order_id','nunique'))
print(report.to_dict('records'))

EXPECTED OUTPUT

[{'region': 'East', 'product': 'Pen', 'total': 4, 'orders': 1}, {'region': 'West', 'product': 'Pen', 'total': 15, 'orders': 2}]

Named aggregation yields a flat table with deliberate output columns.

03. Keep the missing-key group

PYTHON

import pandas as pd
sales = pd.DataFrame({'region':['East',None], 'product':['Pen','Pen'], 'amount':[2,3]})
print(sales.groupby(['region','product'])['amount'].sum().sum())
print(sales.groupby(['region','product'], dropna=False)['amount'].sum().sum())

EXPECTED OUTPUT

2
5

The default leaves the missing-region row out; dropna=False includes it.

GO DEEPER

A closer look

Follow the reasoning, inspect each result, then try the suggested changes in the console below.

01 / 03

Group on the full business key

Start with two products sold in one region. A report grouped only by region produces one combined number, which is correct for a regional total but wrong for a region-by-product breakdown. The answer to 'groupby multiple columns' is a list of both labels, not a comma-separated string.

The first example prints both shapes. A two-key Series uses tuple-like index keys; this shows the combinations clearly but may be awkward for downstream code. Decide what the result will feed next before choosing an index layout.

PYTHON

import pandas as pd
sales = pd.DataFrame({'region':['West','West'], 'product':['Pen','Pad'], 'amount':[8,5]})
print(sales.groupby('region')['amount'].sum().to_dict())
print(sales.groupby(['region','product'])['amount'].sum().to_dict())

EXPECTED OUTPUT

{'West': 13}
{('West', 'Pad'): 5, ('West', 'Pen'): 8}

Follow the reasoning

Run the one-key and two-key summaries.
Explain why their totals have different shapes even though their grand total agrees.

02 / 03

Name the output measures

For a reusable table, group with as_index=False. The key labels remain ordinary columns and can be exported or joined without resetting an index. Named aggregation states exactly which source column and reducer create each output column.

Use sum for amount and nunique for order_id. Two line items may share an order ID, so row count and order count need not agree. Avoid a generic column name such as amount when the output is actually a total; it invites accidental mixing with raw input rows.

PYTHON

import pandas as pd
sales = pd.DataFrame({'region':['West','West','East'], 'product':['Pen','Pen','Pen'], 'amount':[8,7,4], 'order_id':[1,2,3]})
report = sales.groupby(['region','product'], as_index=False).agg(total=('amount','sum'), orders=('order_id','nunique'))
print(report.to_dict('records'))

EXPECTED OUTPUT

[{'region': 'East', 'product': 'Pen', 'total': 4, 'orders': 1}, {'region': 'West', 'product': 'Pen', 'total': 15, 'orders': 2}]

Follow the reasoning

Run the flat report example.
Compare total and orders for the West/Pen group.

03 / 03

Reconcile missing keys

A missing region is not a product group named 'None'; pandas normally omits rows with missing group keys. When the report must account for every input amount, set dropna=False and inspect the resulting missing-key bucket. A production pipeline should also record why the source key was missing.

Compare the sum of grouped amounts with the source amount sum. This tiny test reveals the default omission immediately. It does not validate numeric dtypes or currency rules, which need their own checks when real CSV data enters the pipeline.

PYTHON

import pandas as pd
sales = pd.DataFrame({'region':['East',None], 'product':['Pen','Pen'], 'amount':[2,3]})
print(sales.groupby(['region','product'])['amount'].sum().sum())
print(sales.groupby(['region','product'], dropna=False)['amount'].sum().sum())

EXPECTED OUTPUT

2
5

Follow the reasoning

Run the default and dropna=False totals.
Explain which input row was absent from the default report.

HANDS-ON

Try it in Python

Edit the example and run it. Python starts in your browser the first time you click Run.

Python console

Ready to run

import pandas as pd

def summarize_sales(frame):
    return frame.groupby('region', as_index=False).agg(total=('amount', 'sum'))

sales = pd.DataFrame({'region': ['West', 'West', 'East'],
                      'product': ['Pen', 'Pen', 'Pen'],
                      'amount': [8, 7, 4], 'order_id': [1, 2, 3]})
print(summarize_sales(sales).to_dict('records'))

Need input()? Add one value per line

Input lines

Ctrl / ⌘ + Enter to run

OUTPUT

Your output appears here.

Your turnGroup by both region and product. Return region, product, total amount, and number of distinct order_id values as ordinary columns. Include rows with missing group keys. The sample should produce East/Pen total 4 and West/Pen total 15.

Need a hint?

Call groupby(['region', 'product'], as_index=False, dropna=False).agg(total=('amount', 'sum'), orders=('order_id', 'nunique')).

Complete the task and select Check task to verify your code.

DEBUG BETTER

Typical mistakes

Everyone meets these errors. See what causes them and how to fix them.

Grouping by one column

COMMON MISTAKE

sales.groupby('region').amount.sum()

FIX

sales.groupby(['region', 'product']).amount.sum()

What happens: Pen and Pad sales in the same region merge into one subtotal.
Use the complete set of dimensions that defines a bucket.

Losing missing-key rows

COMMON MISTAKE

sales.groupby(['region', 'product']).amount.sum()

FIX

sales.groupby(['region', 'product'], dropna=False).amount.sum()

What happens: Rows with a missing region or product are absent from the report.
Choose whether missing-key rows belong in reconciliation totals.

Browse this lesson’s problem fixes

VERIFY THE STORY

Sources and further reading