Pandas GroupBy: Multi-Level Aggregations, Transformations & Reshaping

Real-world data questions require segment-level analytics: "What is the average transaction size per user region?", "How does each employee's salary compare to their departmental median?" GroupBy, Transform, and Pivot Tables provide the operational toolkit to answer these questions.

The Three Operation Modes of GroupBy

Hands-On Python: GroupBy, Transform & Pivot Table

import pandas as pd
import numpy as np

# Sample transaction dataset
df = pd.DataFrame({
    'region': ['East', 'West', 'East', 'North', 'West', 'East', 'North', 'West'],
    'rep': ['Alice', 'Bob', 'Charlie', 'David', 'Eva', 'Frank', 'Grace', 'Henry'],
    'sales': [450, 720, 310, 890, 640, 520, 410, 930],
    'quarter': ['Q1', 'Q1', 'Q2', 'Q1', 'Q2', 'Q1', 'Q2', 'Q2']
})

# 1. Advanced Aggregations with Named Aggregations
rep_summary = df.groupby('region').agg(
    total_sales=pd.NamedAgg(column='sales', aggfunc='sum'),
    avg_sales=pd.NamedAgg(column='sales', aggfunc='mean'),
    rep_count=pd.NamedAgg(column='rep', aggfunc='count')
)
print("--- Named Aggregations ---\n", rep_summary)

# 2. Group Transformation: Difference from Regional Mean
df['regional_mean'] = df.groupby('region')['sales'].transform('mean')
df['diff_from_mean'] = df['sales'] - df['regional_mean']
print("\n--- Transformed DataFrame ---\n", df[['region', 'rep', 'sales', 'diff_from_mean']])

# 3. Pivot Table: Total Sales by Region across Quarters
pivot_matrix = df.pivot_table(
    values='sales', 
    index='region', 
    columns='quarter', 
    aggfunc='sum', 
    fill_value=0,
    margins=True  # Adds total 'All' row and column
)
print("\n--- Pivot Table Matrix ---\n", pivot_matrix)