Aggregation & Grouping

Overview

Learn how to group datasets by key columns, compute multiple summary statistics, and count frequency patterns.

Learning Objectives

• Group tables using groupby().
• Apply multiple aggregations (sum, mean) using agg().
• Count value distributions using value_counts().

Concept Explanation

Aggregation groups rows that share values in specific columns. Use groupby() to group data, and use agg() to calculate multiple summaries (like sum, mean, or count) at the same time. value_counts() tallies value frequencies.

Code Examples

Example 1 — Basics

This example introduces the fundamental syntax and concepts.

import pandas as pd
# df.groupby('department')['salary'].mean()
# df['category'].value_counts()

Example 2 — Everyday Usage

This example demonstrates a realistic scenario handling business parameters.

# Calculate average salaries per department
data = {
    'dept': ['HR', 'IT', 'HR', 'IT'],
    'salary': [50000, 70000, 52000, 80000]
}
df = pd.DataFrame(data)
summary = df.groupby('dept')['salary'].mean()
print(summary)

Example 3 — Practical Data Engineering Example

This example shows clean, production-grade code structure following senior development standards.

# Calculate multiple aggregations at the same time
data = {
    'store_id': [101, 101, 102, 102],
    'sales': [150.0, 200.0, 100.0, 300.0],
    'items': [3, 5, 2, 8]
}
df = pd.DataFrame(data)
# Group by store_id and calculate total sales, average sales, and max items
report = df.groupby('store_id').agg({
    'sales': ['sum', 'mean'],
    'items': 'max'
})
print(report)

Visual Flow

The following execution flow represents the step-by-step evaluation inside the interpreter:

Split table by key values → Apply calculations to groups → Combine results into summary DataFrame

Common Mistakes

Review these common pitfalls when working with this topic:

• Forgetting to call an aggregation method after groupby() (this returns a GroupBy object instead of a summary DataFrame).
• Trying to aggregate non-numeric columns like text or dates.
• Confusing value_counts() (tally frequencies) with count() (count non-null rows).
• Using slow loops to calculate group statistics manually.

Best Practices

Enforce these Pythonic best practices in your codebase:

for d in df['dept'].unique():
    print(d, df[df['dept'] == d]['salary'].mean()) # Slow manual grouping

print(df.groupby('dept')['salary'].mean()) # Fast optimized grouping

Quick Revision

Use these key summaries for last-minute revision:

• groupby() splits datasets by key values.
• agg() calculates multiple summaries at the same time.
• value_counts() tallies value frequencies in a Series.
• Common aggregates include 'sum', 'mean', 'count', and 'max'.
• Aggregation methods return a DataFrame or a Series.