Python - Data Aggregation and Group Operations

Data Aggregation and Group Operations in Python

Introduction

Data aggregation and group operations are essential techniques in data analysis. They allow you to analyze trends, summarize data, and generate insights by grouping values and applying aggregation functions such as sum, mean, median, count, and more. Pandas, a powerful data analysis library in Python, provides robust and intuitive APIs for grouping and aggregating data using the groupby() method.

This document provides a comprehensive guide to aggregation and group operations in Pandas, covering:

• Understanding grouping and aggregation
• Basic groupby operations
• Multiple aggregation functions
• Custom aggregation
• Transformations and filtering
• Hierarchical grouping
• Real-world use cases and best practices

Understanding GroupBy in Pandas

What is GroupBy?

The GroupBy operation in Pandas involves three steps:

1. Splitting: The data is split into groups based on some criteria.
2. Applying: A function is applied to each group independently.
3. Combining: The results are combined into a data structure.

Creating a Sample DataFrame

import pandas as pd

data = {
    'department': ['HR', 'IT', 'IT', 'HR', 'Finance', 'Finance', 'IT'],
    'employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace'],
    'salary': [50000, 60000, 55000, 52000, 70000, 72000, 61000],
    'bonus': [5000, 7000, 6000, 4500, 8000, 8500, 6500]
}

df = pd.DataFrame(data)
print(df)

Basic Aggregation Functions

Using groupby() with Aggregation

grouped = df.groupby('department')
print(grouped['salary'].mean())  # Average salary per department

Common Aggregation Functions

• mean()
• sum()
• count()
• min()
• max()
• std()

print(grouped['salary'].sum())   # Total salary per department
print(grouped['bonus'].max())    # Maximum bonus per department
print(grouped['employee'].count())  # Number of employees per department

Aggregating Multiple Columns

Using agg()

result = grouped.agg({
    'salary': 'mean',
    'bonus': 'sum'
})
print(result)

Applying Multiple Functions to a Column

result = grouped['salary'].agg(['mean', 'max', 'min'])
print(result)

Renaming Aggregated Columns

result = grouped.agg(
    avg_salary=('salary', 'mean'),
    total_bonus=('bonus', 'sum')
)
print(result)

Custom Aggregation Functions

Using Lambda Functions

result = grouped['salary'].agg(lambda x: x.max() - x.min())
print(result)

Custom Named Aggregations

def range_func(x):
    return x.max() - x.min()

result = grouped.agg(salary_range=('salary', range_func))
print(result)

Transformations with groupby()

What is transform()?

The transform() method applies a function to each group but returns an object that has the same shape as the original DataFrame.

Example: Normalizing Salaries within Department

df['normalized_salary'] = grouped['salary'].transform(lambda x: x / x.mean())
print(df)

Filtering Groups

Using filter()

# Keep only departments with total bonus over 20000
filtered = grouped.filter(lambda x: x['bonus'].sum() > 20000)
print(filtered)

Using size() and boolean indexing

group_sizes = grouped.size()
large_groups = group_sizes[group_sizes > 2]
print(df[df['department'].isin(large_groups.index)])

Hierarchical Grouping (Multiple Keys)

Group by Multiple Columns

grouped_multi = df.groupby(['department', 'employee'])
print(grouped_multi['salary'].sum())

Aggregation with Multiple Levels

multi_agg = grouped_multi.agg(
    salary_mean=('salary', 'mean'),
    bonus_total=('bonus', 'sum')
)
print(multi_agg)

Accessing Data in MultiIndex

print(multi_agg.loc[('Finance', 'Eve')])

GroupBy and DataFrame Operations

Adding Aggregation Results as New Columns

df['dept_avg_salary'] = df.groupby('department')['salary'].transform('mean')
print(df)

Comparing with Department Averages

df['above_avg'] = df['salary'] > df['dept_avg_salary']
print(df)

Using describe() with groupby

Descriptive Statistics

desc = grouped['salary'].describe()
print(desc)

Accessing a Particular Metric

print(desc['mean'])

GroupBy with Pivot Table

Pivot Table for Aggregation

pivot = pd.pivot_table(df, values='salary', index='department', aggfunc='mean')
print(pivot)

Pivot with Multiple Aggregations

pivot = pd.pivot_table(df, values=['salary', 'bonus'], index='department', aggfunc={'salary': 'mean', 'bonus': 'sum'})
print(pivot)

GroupBy with Categorical Data

Using Categorical Types for Performance

df['department'] = df['department'].astype('category')
grouped = df.groupby('department')
print(grouped['salary'].mean())

Real-World Use Cases

1. Sales Analysis

sales_data = pd.DataFrame({
    'region': ['East', 'East', 'West', 'West', 'East', 'West'],
    'salesperson': ['Alice', 'Bob', 'Charlie', 'David', 'Alice', 'Charlie'],
    'sales': [2500, 3000, 2200, 2800, 2700, 2600]
})

region_sales = sales_data.groupby('region')['sales'].sum()
print(region_sales)

2. Employee Performance

performance_data = pd.DataFrame({
    'employee': ['Alice', 'Bob', 'Charlie', 'David', 'Alice'],
    'month': ['Jan', 'Jan', 'Jan', 'Jan', 'Feb'],
    'score': [80, 85, 90, 88, 87]
})

monthly_avg = performance_data.groupby('month')['score'].mean()
print(monthly_avg)

3. Customer Orders

orders = pd.DataFrame({
    'customer': ['Tom', 'Jerry', 'Tom', 'Jerry', 'Spike'],
    'product': ['A', 'B', 'A', 'C', 'B'],
    'amount': [100, 150, 120, 130, 140]
})

customer_total = orders.groupby('customer')['amount'].sum()
print(customer_total)

Best Practices

1. Use Named Aggregations

Using named aggregation provides clear and readable column names after aggregation.

2. Use transform for Alignment

Transform is useful when you want to perform operations and keep the shape of the original DataFrame.

3. Always Sort and Reset Index for Clean Output

agg = df.groupby('department').agg({'salary': 'mean'}).reset_index().sort_values(by='salary', ascending=False)
print(agg)

4. Use dropna=False to Include Missing Categories

grouped = df.groupby('department', dropna=False)
print(grouped['salary'].mean())

5. Validate with describe() and size()

print(grouped.size())
print(grouped.describe())

Conclusion

Pandas' groupby and aggregation capabilities are vital for analyzing structured data. Whether you're working with sales data, HR records, financial statements, or customer transactions, grouping and summarizing information is key to extracting insights.

In this tutorial, we covered:

• Basic aggregation functions
• Custom and multiple aggregations
• Filtering and transforming grouped data
• Advanced operations like hierarchical grouping and pivot tables

By mastering data aggregation and group operations in Pandas, you can enhance your data analysis workflows and produce meaningful summaries, reports, and dashboards.