Pandas GroupBy Multiple Columns Explained

How to groupby multiple columns in pandas DataFrame and compute multiple aggregations? groupby() can take the list of columns to group by multiple columns and use the aggregate functions to apply single or multiple aggregations at the same time.

Key Points –

The groupby() function allows you to group data based on multiple columns by passing a list of column names.
You can apply aggregation functions (like sum, mean, count) to groups defined by multiple columns, making it easier to analyze data at multiple levels of granularity.
The order of the columns in the list passed to groupby() matters, as it affects the structure of the grouped data. Different orders can lead to different grouping results.
Using groupby without specifying an aggregation function will return a DataFrameGroupBy object, which can be iterated over or further manipulated.
You can apply different aggregation functions to different columns in a single groupby operation using the agg() method.
You can use agg() to apply different aggregation functions to different columns simultaneously.

Quick Examples of GroupBy Multiple Columns

Following are examples of groupby on multiple columns & apply multiple aggregations.


# Quick examples of groupby multiple columns

# Groupby multiple columns
result = df.groupby(['Courses','Fee']).count()

# Groupby multiple columns and aggregate on selected column
result = df.groupby(['Courses','Fee'])['Courses'].count()

# Groupby multiple columns and aggregate()
result = df.groupby(['Courses','Fee'])['Duration'].aggregate('count')

# Groupby multiple aggregations
result = df.groupby('Courses')['Fee'].aggregate(['min','max'])

# Groupby & multiple aggregations on different columns
result = df.groupby('Courses').aggregate({'Duration':'count','Fee':['min','max']})

Pandas GroupBy Multiple Columns Example

You can apply different aggregation functions to different columns in a single groupby operation using the agg() method.Most of the time when you are working on a real-time project in Pandas DataFrame you are required to do groupby on multiple columns. You can do so by passing a list of column names to DataFrame.groupby() function. Let's create a DataFrame to understand this with examples.


import pandas as pd
technologies = {
    'Courses':["Spark","PySpark","Hadoop","Python","PySpark","Spark","Spark"],
    'Fee' :[20000,25000,26000,22000,25000,20000,35000],
    'Duration':['30day','40days','35days','40days','60days','60days','70days'],
    'Discount':[1000,2300,1200,2500,2000,2000,3000]
              }

df = pd.DataFrame(technologies)
print("Create DataFrame:\n", df)

Yields below output.

Now let's do a group on multiple columns and then calculate count aggregation.


# Groupby multiple columns
result = df.groupby(['Courses','Fee']).count()
print("After grouping by multiple columns:\n", result)

Yields below output. When you apply count on the entire DataFrame, pretty much all columns will have the same values.

So when you want to group by count just select a column, you can even select from your group columns.


# Group by multiple columns and get 
# count of one of grouping column
result = df.groupby(['Courses','Fee'])['Courses'].count(\n", result)
print("Get count of one of the grouping column:\n", result)

# Output:
# Get count of one of the grouping column:
# Courses  Fee  
# Hadoop   26000    1
# PySpark  25000    2
# Python   22000    1
# Spark    20000    2
#          35000    1
# Name: Courses, dtype: int6

Using aggregate()

You can use aggregate() to perform multiple aggregations on different columns after grouping by multiple columns. This takes the count function as a string param.


# Groupby multiple columns and aggregate()
result = df.groupby(['Courses','Fee'])['Courses'].aggregate('count')
print("After grouping by multiple columns:\n", result)

# Output:
# After grouping by multiple columns:
# Courses  Fee  
# Hadoop   26000    1
# PySpark  25000    2
# Python   22000    1
# Spark    20000    2
#          35000    1
# Name: Duration, dtype: int64

Pandas Multiple Aggregations

You can also compute multiple aggregations simultaneously in Pandas by passing a list of aggregation functions to the aggregate() function.


# Groupby & multiple aggregations
result = df.groupby('Courses')['Fee'].aggregate(['min','max'])
print("After applying multiple aggregations on multiple group columns:\n", result)

# Output:
# After applying multiple aggregations on multiple group columns:
#        min    max
# Courses              
# Hadoop   26000  26000
# PySpark  25000  25000
# Python   22000  22000
# Spark    20000  35000

In the above example, calculate the minimum and maximum values on the Fee column. Now, let's expand this process to calculate various aggregations on different columns

When multiple aggregations are applied to a single column in a Pandas DataFrame, the resulting columns will indeed contain a MultiIndex.


# Groupby multiple columns & multiple aggregations
result = df.groupby('Courses').aggregate({'Duration':'count','Fee':['min','max']})
print("After applying multiple aggregations on single group column:\n", result)

# Output:
#  After applying multiple aggregations on single group column:
#         Duration    Fee       
#            count    min    max
# Courses                       
# Hadoop         1  26000  26000
# PySpark        2  25000  25000
# Python         1  22000  22000
# Spark          3  20000  35000

Observing that this creates a MultiIndex, it's worth noting that managing multi-indexed columns can be challenging. Therefore, I'd suggest simplifying the structure by renaming the columns.

FAQ on Pandas GroupBy Multiple Columns

To group a Pandas DataFrame by multiple columns, you can pass a list of column names to the groupby() function. This will allow you to group the data based on the unique combinations of values from the specified columns.

You can apply multiple aggregation functions to different columns by passing a dictionary to the agg() method.

To apply multiple aggregation functions to a single column in Pandas, you can pass a list of aggregation functions to the agg() method. This allows you to calculate multiple statistics for the same column within each group.

To get the size of each group when grouping by multiple columns, you can use the size() method after applying groupby(). This will return the number of rows in each group.

To filter groups based on a condition after using groupby(), you can use the filter() method. The filter() method allows you to apply a condition to the grouped data and retain only the groups that meet that condition.

Conclusion

In this article, I have explained group DataFrame rows by multiple columns and also learned how to compute different aggregations on a column.