Pandas GroupBy Function: Master Data Aggregation and Analysis

Complete guide to pandas GroupBy for data aggregation. Learn grouping, aggregation functions, multi-level grouping, transformation, and advanced analysis techniques with practical Python examples.

📅 Published: September 8, 2025 ✏️ Updated: September 20, 2025 By Ojaswi Athghara
#pandas #groupby #aggregation #data-analysis #python #tutorial

Pandas GroupBy Function: Master Data Aggregation and Analysis

When GroupBy Changed My Analysis Game

"Calculate the average salary by department," my manager asked. I started writing nested loops, dictionaries, accumulator variables... 30 minutes later, I was still debugging.

My colleague looked at my screen and typed: df.groupby('department')['salary'].mean(). One line. That was it. My mind exploded.

GroupBy is pandas' secret weapon. It's the SQL GROUP BY operation on steroids, enabling powerful split-apply-combine operations that would otherwise require complex logic.

This guide will transform how you analyze data. From basic grouping to advanced aggregations, you'll learn to extract insights with minimal code.

Understanding GroupBy: Split-Apply-Combine

GroupBy follows three steps:

  1. Split - Divide data into groups
  2. Apply - Apply a function to each group
  3. Combine - Combine results into a data structure
import pandas as pd
import numpy as np

# Sample employee data
df = pd.DataFrame({
    'department': ['Sales', 'Sales', 'IT', 'IT', 'HR', 'HR', 'Sales'],
    'employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace'],
    'salary': [70000, 80000, 90000, 85000, 75000, 72000, 78000],
    'experience': [3, 5, 7, 6, 4, 3, 4]
})

print("Employee data:")
print(df)

# Simple groupby
dept_avg = df.groupby('department')['salary'].mean()
print("\nAverage salary by department:")
print(dept_avg)

Basic GroupBy Operations

Single Column Grouping

# Group by and aggregate
print("Count by department:")
print(df.groupby('department').size())

print("\nSum by department:")
print(df.groupby('department')['salary'].sum())

print("\nMultiple statistics:")
print(df.groupby('department')['salary'].describe())

# Access specific groups
grouped = df.groupby('department')
print("\nSales department:")
print(grouped.get_group('Sales'))

Multiple Aggregations

# Single aggregation
result = df.groupby('department')['salary'].mean()

# Multiple aggregations on one column
result = df.groupby('department')['salary'].agg(['mean', 'sum', 'count', 'std'])
print("Multiple aggregations:")
print(result)

# Different aggregations on different columns
result = df.groupby('department').agg({
    'salary': ['mean', 'sum'],
    'experience': ['mean', 'max']
})
print("\nDifferent aggregations per column:")
print(result)

Custom Aggregations

# Custom function
def salary_range(x):
    return x.max() - x.min()

result = df.groupby('department')['salary'].agg([
    ('average', 'mean'),
    ('total', 'sum'),
    ('range', salary_range)
])
print("Custom aggregations:")
print(result)

# Lambda functions
result = df.groupby('department')['salary'].agg([
    ('avg', lambda x: x.mean()),
    ('top_3_avg', lambda x: x.nlargest(3).mean())
])
print("\nWith lambda functions:")
print(result)

Multi-Level Grouping

# Add more dimensions
df['location'] = ['NY', 'NY', 'SF', 'SF', 'LA', 'LA', 'NY']

print("Multi-level grouping:")
result = df.groupby(['department', 'location'])['salary'].mean()
print(result)

# Unstack for better view
print("\nUnstacked:")
print(result.unstack())

# Multiple columns, multiple aggregations
result = df.groupby(['department', 'location']).agg({
    'salary': 'mean',
    'experience': 'sum'
})
print("\nMultiple columns:")
print(result)

Transform and Filter

Transform (Keep Original Shape)

# Add group statistics to original dataframe
df['dept_avg_salary'] = df.groupby('department')['salary'].transform('mean')
df['dept_exp_sum'] = df.groupby('department')['experience'].transform('sum')

print("With group statistics:")
print(df)

# Calculate deviation from group mean
df['salary_vs_dept_avg'] = df['salary'] - df['dept_avg_salary']
print("\nSalary vs department average:")
print(df[['employee', 'salary', 'dept_avg_salary', 'salary_vs_dept_avg']])

Filter Groups

# Keep only groups with more than 2 members
large_depts = df.groupby('department').filter(lambda x: len(x) > 2)
print("Departments with > 2 employees:")
print(large_depts)

# Filter by aggregate condition
high_paying_depts = df.groupby('department').filter(lambda x: x['salary'].mean() > 75000)
print("\nHigh-paying departments:")
print(high_paying_depts)

Real-World Example: Sales Analysis

# Sales data
np.random.seed(42)
dates = pd.date_range('2024-01-01', periods=100)
sales_df = pd.DataFrame({
    'date': dates,
    'product': np.random.choice(['Laptop', 'Phone', 'Tablet', 'Monitor'], 100),
    'region': np.random.choice(['North', 'South', 'East', 'West'], 100),
    'quantity': np.random.randint(1, 20, 100),
    'unit_price': np.random.randint(200, 1500, 100)
})

sales_df['revenue'] = sales_df['quantity'] * sales_df['unit_price']
sales_df['month'] = sales_df['date'].dt.to_period('M')

print("Sales Analysis")
print("="*60)

# 1. Revenue by product
product_revenue = sales_df.groupby('product')['revenue'].sum().sort_values(ascending=False)
print("\n1. Total Revenue by Product:")
print(product_revenue)

# 2. Average quantity by region
region_qty = sales_df.groupby('region')['quantity'].mean()
print("\n2. Average Quantity by Region:")
print(region_qty)

# 3. Monthly revenue trend
monthly_revenue = sales_df.groupby('month')['revenue'].sum()
print("\n3. Monthly Revenue Trend:")
print(monthly_revenue.head(10))

# 4. Top product per region
top_by_region = sales_df.groupby(['region', 'product'])['revenue'].sum()
top_by_region = top_by_region.groupby('region').nlargest(1)
print("\n4. Top Product per Region:")
print(top_by_region)

# 5. Comprehensive statistics
stats = sales_df.groupby('product').agg({
    'quantity': ['sum', 'mean'],
    'revenue': ['sum', 'mean', 'max'],
    'unit_price': 'mean'
}).round(2)
print("\n5. Comprehensive Product Statistics:")
print(stats)

Advanced GroupBy Techniques

Named Aggregations (pandas 0.25+)

result = df.groupby('department').agg(
    avg_salary=('salary', 'mean'),
    total_salary=('salary', 'sum'),
    employee_count=('employee', 'count'),
    max_experience=('experience', 'max')
).round(2)

print("Named aggregations:")
print(result)

Applying Multiple Functions

def custom_stats(group):
    return pd.Series({
        'mean': group.mean(),
        'median': group.median(),
        'std': group.std(),
        '25th': group.quantile(0.25),
        '75th': group.quantile(0.75)
    })

result = df.groupby('department')['salary'].apply(custom_stats)
print("Custom statistics:")
print(result)

GroupBy with MultiIndex

# Create hierarchical index
result = df.groupby(['department', 'location']).agg({
    'salary': ['mean', 'sum'],
    'experience': 'mean'
})

print("MultiIndex result:")
print(result)

# Flatten column names
result.columns = ['_'.join(col).strip() for col in result.columns.values]
print("\nFlattened columns:")
print(result)

Time Series GroupBy

# Time-based data
date_range = pd.date_range('2024-01-01', periods=365, freq='D')
ts_df = pd.DataFrame({
    'date': date_range,
    'value': np.random.randn(365).cumsum() + 100
})

# Resample and group
ts_df['month'] = ts_df['date'].dt.to_period('M')
ts_df['quarter'] = ts_df['date'].dt.to_period('Q')

# Monthly statistics
monthly_stats = ts_df.groupby('month')['value'].agg(['mean', 'min', 'max', 'std'])
print("Monthly statistics:")
print(monthly_stats.head())

# Quarterly aggregation
quarterly = ts_df.groupby('quarter')['value'].mean()
print("\nQuarterly averages:")
print(quarterly)

Cumulative Operations

# Running totals within groups
df = df.sort_values(['department', 'experience'])
df['cumulative_salary'] = df.groupby('department')['salary'].cumsum()
df['rank_in_dept'] = df.groupby('department').cumcount() + 1

print("Cumulative operations:")
print(df[['department', 'employee', 'salary', 'cumulative_salary', 'rank_in_dept']])

Pivot Table (GroupBy's Cousin)

# Pivot table is GroupBy with reshape
pivot = sales_df.pivot_table(
    values='revenue',
    index='product',
    columns='region',
    aggfunc='sum',
    fill_value=0,
    margins=True  # Add totals
)

print("Pivot table:")
print(pivot)

# Multiple aggregations
pivot_multi = sales_df.pivot_table(
    values='revenue',
    index='product',
    columns='region',
    aggfunc=['sum', 'mean'],
    fill_value=0
)

print("\nMultiple aggregations:")
print(pivot_multi)

Performance Tips

import time

# Create large dataset
large_df = pd.DataFrame({
    'category': np.random.choice(['A', 'B', 'C'], 1000000),
    'value': np.random.randn(1000000)
})

# Vectorized operations are faster
start = time.time()
result1 = large_df.groupby('category')['value'].mean()
time1 = time.time() - start

print(f"GroupBy time: {time1:.4f} seconds")

# Use categorical type for repeated values
large_df['category'] = large_df['category'].astype('category')

start = time.time()
result2 = large_df.groupby('category')['value'].mean()
time2 = time.time() - start

print(f"With categorical: {time2:.4f} seconds")
print(f"Speedup: {time1/time2:.2f}x")

Common Patterns and Use Cases

1. Top N per Group

# Top 2 salaries per department
top_n = df.groupby('department').apply(lambda x: x.nlargest(2, 'salary'))
print("Top 2 salaries per department:")
print(top_n)

2. Percentage of Total

df['revenue'] = df['salary'] * 12
total_revenue = df['revenue'].sum()
dept_revenue = df.groupby('department')['revenue'].sum()
dept_pct = (dept_revenue / total_revenue * 100).round(2)

print("Department revenue percentage:")
print(dept_pct)

3. Rolling Window within Groups

ts_df = ts_df.sort_values('date')
ts_df['rolling_avg'] = ts_df.groupby('month')['value'].transform(
    lambda x: x.rolling(window=7, min_periods=1).mean()
)

4. Flag Based on Group Condition

df['is_highest_in_dept'] = df.groupby('department')['salary'].transform(
    lambda x: x == x.max()
)
print("Highest paid in department:")
print(df[df['is_highest_in_dept']])

Complete Analysis Example

# Comprehensive business analysis
analysis_df = pd.DataFrame({
    'date': pd.date_range('2024-01-01', periods=200),
    'sales_rep': np.random.choice(['Alice', 'Bob', 'Charlie', 'David'], 200),
    'product': np.random.choice(['Product A', 'Product B', 'Product C'], 200),
    'region': np.random.choice(['North', 'South', 'East', 'West'], 200),
    'units_sold': np.random.randint(1, 50, 200),
    'revenue': np.random.uniform(100, 5000, 200)
})

print("BUSINESS ANALYSIS REPORT")
print("="*60)

# 1. Top performers
print("\n1. Top Performers by Revenue:")
top_reps = analysis_df.groupby('sales_rep')['revenue'].sum().sort_values(ascending=False)
print(top_reps)

# 2. Best selling products by region
print("\n2. Best Products by Region:")
best_products = analysis_df.groupby(['region', 'product'])['revenue'].sum().groupby('region').nlargest(1)
print(best_products)

# 3. Performance trends
analysis_df['month'] = analysis_df['date'].dt.to_period('M')
monthly_trends = analysis_df.groupby(['month', 'sales_rep'])['revenue'].sum().unstack()
print("\n3. Monthly Revenue by Rep:")
print(monthly_trends)

# 4. Statistical summary
summary = analysis_df.groupby('product').agg({
    'units_sold': ['sum', 'mean'],
    'revenue': ['sum', 'mean', 'std'],
    'sales_rep': 'count'
}).round(2)
summary.columns = ['_'.join(col) for col in summary.columns]
summary = summary.rename(columns={'sales_rep_count': 'transactions'})
print("\n4. Product Performance Summary:")
print(summary)

# 5. Identify opportunities
avg_revenue_per_transaction = analysis_df['revenue'].mean()
underperforming = analysis_df.groupby('sales_rep').filter(
    lambda x: x['revenue'].mean() < avg_revenue_per_transaction
)
print(f"\n5. Reps Below Average ({avg_revenue_per_transaction:.2f}):")
print(underperforming['sales_rep'].unique())

Common GroupBy Patterns and Solutions

Real-world data analysis often requires specific patterns. Here are solutions to common scenarios.

Pattern 1: Top N Per Group

# Get top 3 products by revenue in each region
top_products = (df.groupby('region')
    .apply(lambda x: x.nlargest(3, 'revenue'))
    .reset_index(drop=True))

# Or using sort + groupby
top_products_alt = (df.sort_values('revenue', ascending=False)
    .groupby('region')
    .head(3))

Pattern 2: Percentage of Total

# Calculate percentage contribution of each product
df['revenue_pct'] = df.groupby('product')['revenue'].transform(
    lambda x: x / x.sum() * 100
)

# Or group-wise percentage
df['category_revenue_pct'] = df.groupby('category')['revenue'].transform(
    lambda x: (x / x.sum() * 100).round(2)
)

Pattern 3: Cumulative Operations Within Groups

# Running total by customer
df['cumulative_purchases'] = df.groupby('customer_id')['purchase_amount'].cumsum()

# Rank within group
df['product_rank'] = df.groupby('category')['sales'].rank(ascending=False)

# Days since last transaction per customer
df['days_since_last'] = df.groupby('customer_id')['date'].diff().dt.days

Pattern 4: Conditional Aggregation

# Count only high-value transactions per customer
high_value = df[df['amount'] > 1000].groupby('customer_id').size()

# Or use conditional sum
df.groupby('region').apply(
    lambda x: x[x['revenue'] > 5000]['revenue'].sum()
)

Pattern 5: Multiple Aggregations with Different Conditions

# Complex business metrics
metrics = df.groupby('sales_rep').agg(
    total_sales=('revenue', 'sum'),
    avg_deal_size=('revenue', 'mean'),
    num_deals=('deal_id', 'count'),
    largest_deal=('revenue', 'max'),
    deals_over_10k=('revenue', lambda x: (x > 10000).sum()),
    win_rate=('status', lambda x: (x == 'won').mean() * 100)
)

GroupBy Performance Tips

When working with large datasets, performance matters.

Tip 1: Use Categorical Data Type

# Convert repeated values to categorical
df['category'] = df['category'].astype('category')
df['region'] = df['region'].astype('category')

# GroupBy is much faster with categorical data
result = df.groupby('category')['sales'].sum()

Tip 2: Sort Before Grouping

# Sorting first can speed up groupby
df_sorted = df.sort_values('customer_id')
result = df_sorted.groupby('customer_id')['amount'].sum()

Tip 3: Use Efficient Aggregations

# SLOW: Apply with lambda for simple operations
df.groupby('col').apply(lambda x: x['value'].sum())

# FAST: Use built-in aggregations
df.groupby('col')['value'].sum()

Tip 4: Avoid Chained Operations

# SLOW: Multiple separate groupby operations
means = df.groupby('category')['price'].mean()
sums = df.groupby('category')['price'].sum()
counts = df.groupby('category')['price'].count()

# FAST: Single groupby with multiple aggregations
stats = df.groupby('category')['price'].agg(['mean', 'sum', 'count'])

GroupBy Cheat Sheet

# Basic operations
df.groupby('col').size()              # Count per group
df.groupby('col')['value'].mean()     # Mean per group
df.groupby('col').agg(['mean', 'sum']) # Multiple aggregations

# Multi-level
df.groupby(['col1', 'col2']).mean()   # Multiple grouping columns

# Transform
df.groupby('col')['value'].transform('mean')  # Broadcast to original shape

# Filter
df.groupby('col').filter(lambda x: len(x) > 5)  # Filter groups

# Apply
df.groupby('col').apply(custom_function)  # Custom function per group

# Named aggregations
df.groupby('col').agg(
    avg=('value', 'mean'),
    total=('value', 'sum')
)

Your GroupBy Mastery

You've mastered pandas GroupBy operations:

  1. Basic grouping - Single and multi-level
  2. Aggregations - Built-in and custom functions
  3. Transform - Add group statistics to rows
  4. Filter - Keep/remove groups by condition
  5. Advanced - Named aggregations, cumulative ops
  6. Real-world - Sales analysis, time series

GroupBy is your Swiss Army knife for data aggregation. Master it, and you'll analyze data 10x faster.

Quick GroupBy Mental Model

Think of GroupBy in three stages:

  1. Split: Divide data into groups based on criteria
  2. Apply: Perform operations on each group independently
  3. Combine: Merge results back together

This split-apply-combine pattern is the foundation of all GroupBy operations. Once you internalize this, complex aggregations become intuitive.

Real-World Applications I Use Daily

Business Analytics: Track KPIs per department, region, or time period using GroupBy with date extraction and multiple aggregations.

Customer Segmentation: Group customers by behavior patterns and calculate lifetime value, purchase frequency, and average order size per segment.

Time Series Analysis: Resample time-based data to different frequencies (daily to weekly, monthly trends) using GroupBy with date components.

A/B Testing: Compare metrics between test groups, calculate statistical significance, and identify winning variations using GroupBy with filtering.

Quality Control: Detect anomalies by grouping production batches and identifying outliers within each group using GroupBy with custom functions.

Next Steps

Remember: GroupBy is powerful, but readable code matters. Use clear variable names and add comments!

From Beginner to GroupBy Expert

My GroupBy journey taught me that mastery comes from practice with increasingly complex scenarios:

Week 1-2: Practice simple single-column grouping with basic aggregations (sum, mean, count) on clean datasets.

Week 3-4: Multi-level grouping and multiple aggregations simultaneously. Learn to reshape results with unstack and pivot.

Week 5-6: Transform and filter operations. Understanding when results should match original DataFrame shape versus be aggregated.

Month 2-3: Custom aggregation functions, named aggregations, and combining GroupBy with other pandas operations seamlessly.

Month 4+: Performance optimization, handling large datasets efficiently, and debugging complex GroupBy chains confidently.

The secret? Work with real data from your domain. Nothing beats practical experience with actual business problems.

Found this guide useful? Share it with your data team! Connect with me on Twitter or LinkedIn for more pandas tips.

Support My Work

If this guide helped you master GroupBy, accelerate your data analysis, or unlock powerful aggregation techniques, I'd greatly appreciate your support, I'd really appreciate your support! Creating comprehensive, free content like this takes significant time and effort. Your support helps me continue sharing knowledge and creating more helpful resources for developers.

☕ Buy me a coffee - Every contribution, big or small, means the world to me and keeps me motivated to create more content!

Cover image by Dimitri Karastelev on Unsplash

Related Blogs

Ojaswi Athghara

SDE, 4+ Years

© ojaswiat.com 2025-2027