GroupBy and Aggregation

Difficulty: Intermediate

The groupby operation is one of the most powerful features in Pandas and is fundamental to data analysis. It implements the split-apply-combine paradigm: first, the data is split into groups based on one or more keys; then, a function is applied to each group independently; finally, the results are combined into a new data structure. This pattern covers a vast range of analytical tasks, from computing summary statistics per category to creating complex derived features.

The basic syntax is df.groupby('column'). This returns a GroupBy object, which is lazy: it does not compute anything until you call an aggregation method on it. Common aggregation methods include sum(), mean(), count(), min(), max(), std(), first(), and last(). For example, df.groupby('Department')['Salary'].mean() groups the data by Department, selects the Salary column, and computes the average salary for each department. The result is a Series with Department values as the index.

The agg() method (short for aggregate) provides maximum flexibility by letting you apply multiple aggregation functions at once or apply different functions to different columns. You can pass a list of functions: df.groupby('Department')['Salary'].agg(['mean', 'max', 'count']). Or you can pass a dictionary to apply different aggregations to different columns: df.groupby('Department').agg({'Salary': 'mean', 'Age': 'max'}). You can even use custom lambda functions or named aggregations for clean column names.

The transform() method is different from agg() in an important way: while agg() reduces each group to a single value (like mean), transform() returns a result that is the same size as the input. This is incredibly useful for adding group-level statistics back to the original DataFrame. For example, df.groupby('Department')['Salary'].transform('mean') returns a Series with the same length as the original DataFrame, where each value is replaced by the mean salary of that row's department. This pattern is commonly used to create features like "percentage of group total" or "deviation from group mean."

The pivot_table() function creates a spreadsheet-style pivot table from a DataFrame. It is essentially a more convenient interface to groupby with reshaping. You specify values (the data to aggregate), index (the rows), columns (the columns), and aggfunc (the aggregation function). For example, pd.pivot_table(df, values='Sales', index='Region', columns='Product', aggfunc='sum') creates a table with regions as rows, products as columns, and summed sales as values. Pivot tables are powerful for creating summary reports and cross-tabulations.

Code examples

Basic GroupBy Aggregation

import pandas as pd

df = pd.DataFrame({
    'Department': ['IT', 'HR', 'IT', 'HR', 'IT'],
    'Name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'],
    'Salary': [70000, 50000, 60000, 55000, 65000]
})

print("Mean salary by department:")
print(df.groupby('Department')['Salary'].mean())

print("---")
print("Count per department:")
print(df.groupby('Department')['Name'].count())

groupby('Department') splits the data into HR and IT groups. Calling mean() on the Salary column computes the average for each group. The result is a Series indexed by Department.

Multiple Aggregations with agg()

import pandas as pd

df = pd.DataFrame({
    'Category': ['A', 'B', 'A', 'B', 'A'],
    'Value': [10, 20, 30, 40, 50],
    'Quantity': [5, 3, 8, 2, 7]
})

# Multiple aggregations on one column
print("Aggregations on Value:")
print(df.groupby('Category')['Value'].agg(['sum', 'mean', 'max']))

print("---")
# Different aggregations for different columns
print("Mixed aggregations:")
print(df.groupby('Category').agg({'Value': 'sum', 'Quantity': 'mean'}))

agg() with a list applies multiple functions to the same column. agg() with a dictionary applies different functions to different columns. This is powerful for creating summary tables with exactly the statistics you need.

Using transform() for Group-Level Features

import pandas as pd

df = pd.DataFrame({
    'Department': ['IT', 'HR', 'IT', 'HR', 'IT'],
    'Name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'],
    'Salary': [70000, 50000, 60000, 55000, 65000]
})

# Add department mean as a new column
df['Dept_Mean'] = df.groupby('Department')['Salary'].transform('mean')
df['Dept_Mean'] = df['Dept_Mean'].astype(int)
print(df)

transform('mean') computes the group mean but returns a value for every row. IT employees all get 65000 (the IT mean) and HR employees all get 52500 (the HR mean). This is useful for comparing individual values to their group average.

Pivot Table Basics

import pandas as pd

df = pd.DataFrame({
    'Region': ['North', 'South', 'North', 'South', 'North', 'South'],
    'Product': ['A', 'A', 'B', 'B', 'A', 'B'],
    'Sales': [100, 150, 200, 120, 130, 180]
})

pivot = pd.pivot_table(df, values='Sales', index='Region',
                        columns='Product', aggfunc='sum')
print(pivot)

The pivot table groups by Region (rows) and Product (columns), summing the Sales values. North region sold 230 units of Product A (100+130) and 200 of Product B. This is the same as a groupby with two keys followed by an unstack.

Key points

Concepts covered

groupby(), agg(), transform(), apply(), Pivot Table Basics