Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Python Pandas Groupby Aggregate on Multiple Columns

Learn how to aggregate multiple columns with pandas groupby and agg: dictionary and named aggregation syntax, grouping by multiple columns, handling missing values, and performance tips.

~5 min readPython
#pandas#groupby#data aggregation#python
Illustration of pandas groupby aggregation across multiple columns in a DataFrame

Aggregating multiple columns after a groupby is a common pattern when you need to summarize data across several fields. The groupby method splits the DataFrame into groups, and the agg method applies one or more aggregation functions to each group. When the aggregation targets multiple columns, the syntax can become confusing because you need to specify which function applies to which column.

The Basic groupby and agg Pattern

Before handling multiple columns, it helps to recall the standard single-column aggregation. Given a DataFrame with sales data, you might want the total sales per region:

python
import pandas as pd df = pd.DataFrame({ 'region': ['North', 'South', 'North', 'South'], 'sales': [100, 200, 150, 250], 'cost': [80, 120, 90, 130] }) df.groupby('region')['sales'].sum()

This returns a Series with the sum of sales for each region. The ['sales'] selection limits the aggregation to one column. When you need to aggregate more than one column, you have several options.

Aggregating Multiple Columns with a Dictionary

The most direct way to apply different aggregation functions to different columns is to pass a dictionary to agg. The keys are column names, and the values are the functions to apply:

python
df.groupby('region').agg({'sales': 'sum', 'cost': 'mean'})

This computes the total sales and the average cost per region. The result is a DataFrame with one row per region and one column per aggregation. You can also use callables like lambda x: x.max() - x.min() if you need a custom function.

A dictionary works well when the aggregation functions are not the same across columns. If you want to apply the same function to multiple columns, you can pass a list of columns and a single function:

python
df.groupby('region')[['sales', 'cost']].sum()

But this only works when the function is identical. For mixed functions, the dictionary is the clearest approach.

Using Named Aggregation for Readable Output

Since pandas 0.25, you can use named aggregation to give each result column a meaningful name. This is especially useful when you apply multiple functions to the same column or when the default column names are ambiguous.

python
df.groupby('region').agg( total_sales=('sales', 'sum'), avg_cost=('cost', 'mean'), sales_range=('sales', lambda x: x.max() - x.min()) )

Each keyword argument becomes a column in the result. The syntax is new_name=('source_column', 'function'). This approach is more readable than a dictionary when you have several aggregations, and it avoids the awkward column names that pandas generates when you pass a list of functions.

Named aggregation also works when you want to apply multiple functions to the same column:

python
df.groupby('region').agg( sales_sum=('sales', 'sum'), sales_count=('sales', 'count') )

Grouping by Multiple Columns

So far, we have grouped by a single column. Often you need to group by several columns to create a finer partition. For example, you might want sales by region and product:

python
df = pd.DataFrame({ 'region': ['North', 'North', 'South', 'South'], 'product': ['A', 'B', 'A', 'B'], 'sales': [100, 150, 200, 250] }) df.groupby(['region', 'product']).agg(total_sales=('sales', 'sum'))

The result has a MultiIndex with region and product as index levels. You can use as_index=False to keep them as columns instead:

python
df.groupby(['region', 'product'], as_index=False).agg(total_sales=('sales', 'sum'))

When grouping by multiple columns, the same aggregation rules apply. You can use a dictionary or named aggregation to target different columns.

Handling Missing Values and Edge Cases

Aggregations behave differently when the data contains NaN. Most aggregation functions like sum and mean skip missing values by default. However, count counts non-null values, while size counts all rows including those with NaN. If you need to count missing values explicitly, you can use lambda x: x.isna().sum().

Another edge case involves categorical grouping columns. With a regular column, groups are formed from observed values, so empty groups do not appear. With a categorical column, observed=False includes categories that have no rows, while observed=True includes only categories that actually appear in the data.

When aggregating multiple columns, ensure that the columns you reference in the agg call actually exist. A KeyError is raised if you mistype a column name. This is a common source of confusion when the DataFrame has many columns.

Performance Considerations for Large DataFrames

Aggregating multiple columns with groupby can be memory-intensive when the DataFrame is large. The dictionary and named aggregation approaches both materialize the result as a new DataFrame, which may be significantly smaller than the original if the number of groups is small. However, the grouping operation itself requires sorting or hashing the group keys.

For large datasets, consider the following:

  • Use sort=False in groupby if you do not need the groups sorted. This avoids the sorting step and can reduce runtime.
  • Avoid applying Python-level loops or custom functions that are not vectorized. Built-in aggregation functions like sum, mean, and max are implemented in C and are much faster than a lambda that iterates row by row.
  • If you need multiple aggregations, combine them in a single agg call rather than running several groupby operations separately. This avoids repeated grouping work.
  • For very large DataFrames that do not fit in memory, consider using dask or polars, but that is outside the scope of pandas itself.

A common mistake is to call groupby multiple times for different aggregations. For example, computing the sum and then the mean in two separate operations forces pandas to group the data twice. A single agg with both functions is more efficient.

Common Mistakes and How to Avoid Them

One frequent error is confusing agg with apply. agg applies functions to columns, while apply can work on rows or columns and is more flexible but slower. Use agg when you need standard aggregations.

Another mistake is calling agg without selecting the columns you need. For example, if the DataFrame contains non-numeric columns, df.groupby('region').agg('sum') may produce unexpected results. It is safer to explicitly select the columns you want to aggregate.

When using named aggregation, the column names in the result are the keyword arguments. If you use a dictionary, the result columns are named after the original columns, which can be ambiguous if you apply multiple functions to the same column. For example, df.groupby('region').agg({'sales': ['sum', 'mean']}) produces a MultiIndex column, which is harder to work with. Named aggregation avoids this by letting you assign distinct names.

Finally, be aware of the groupby behavior with dropna. By default, groups with NaN in the grouping key are excluded. If you want to include them, use dropna=False. This is often overlooked when grouping by a column that contains missing values.

Combining Groupby with Other DataFrame Operations

Aggregating multiple columns is often part of a larger data pipeline. You might need to merge the aggregated result back into the original DataFrame. The transform method is useful when you want to keep the original row count and add aggregated values as a new column:

python
df['total_sales_by_region'] = df.groupby('region')['sales'].transform('sum')

This adds a column where each row contains the total sales for its region. Unlike agg, transform returns a Series aligned with the original index. This is handy for feature engineering or for creating ratios without losing the original rows.

When you need both the aggregated summary and the original data, consider using merge with the aggregated result. This gives you full control over the join keys and avoids the alignment surprises that can occur with transform.

Understanding how groupby, agg, and transform interact is essential for writing clean and efficient pandas code. The ability to aggregate multiple columns with different functions is a core skill for any data engineer or analyst working with tabular data in Python.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.