Tutorial 8: Data Aggregation and Grouping with Pandas -

Introduction

In the previous chapter, we calculated overall statistics like mean, median, and correlation. Now we’ll learn to answer more practical, business-style questions: What’s the average salary by department? Which department has the highest total sales? How many employees are in each city? This is where grouping and aggregation become essential.

You’ll learn: groupby(), aggregation functions, multiple aggregations, grouping by multiple columns, pivot_table(), crosstab(), and how to answer business questions with grouped data.

1. Create a Sample Dataset

import pandas as pd

data = {
    "Employee": ["Amit", "Priya", "Rahul", "Sneha", "Vikram", "Anjali", "Kiran", "Neha"],
    "Department": ["IT", "HR", "Finance", "IT", "Sales", "Finance", "IT", "HR"],
    "City": ["Mumbai", "Delhi", "Mumbai", "Pune", "Delhi", "Mumbai", "Pune", "Delhi"],
    "Salary": [75000, 60000, 85000, 90000, 65000, 95000, 80000, 70000],
    "Experience": [5, 3, 7, 8, 4, 10, 6, 5]
}

df = pd.DataFrame(data)
df

2. What is Aggregation?

Aggregation means summarizing many rows into a smaller number of meaningful values.

Individual Employees → GROUP by Department → AGGREGATE → Average Salary

Example result:

Department	Average Salary
Finance	₹90,000
HR	₹65,000
IT	₹81,667
Sales	₹65,000

3. Basic `groupby()`

df.groupby("Department")

This creates a “grouped object” — it doesn’t show results yet; it’s a plan waiting for an aggregation to be applied.

Calculate average salary:

df.groupby("Department")["Salary"].mean()

Output:

Department
Finance    90000
HR         65000
IT         81667
Sales      65000

Interpretation: Finance has the highest average salary in this dataset.

4. Count Records by Group

df.groupby("Department")["Employee"].count()

Or, equivalently:

df["Department"].value_counts()

Both give the same answer — which one to use often comes down to whether you’re already mid-chain on a groupby() or working with the column directly.

5. Common Aggregation Functions

Function	Purpose
`count()`	Count values
`sum()`	Total
`mean()`	Average
`median()`	Middle value
`min()` / `max()`	Minimum / maximum
`std()`	Standard deviation

Example:

df.groupby("Department")["Salary"].max()

6. Multiple Aggregations with `agg()`

Get minimum, maximum, and average salary all at once:

df.groupby("Department")["Salary"].agg(["min", "max", "mean"])

Example output:

Department	min	max	mean
Finance	85000	95000	90000
HR	60000	70000	65000
IT	75000	90000	81667
Sales	65000	65000	65000

7. Named Aggregations

Make the output more readable with custom column names:

df.groupby("Department").agg(
    Average_Salary=("Salary", "mean"),
    Maximum_Salary=("Salary", "max"),
    Employee_Count=("Employee", "count")
)

This is particularly useful for building business reports, since the column names in the output become self-explanatory rather than generic.

8. Grouping by Multiple Columns

Group by Department and City together:

df.groupby(["Department", "City"])["Salary"].mean()

This produces a more detailed, two-level breakdown — for instance, IT employees in Mumbai vs. IT employees in Pune, rather than just “IT” as a whole.

9. Reset the Index

Grouped results often come back with a “hierarchical index” (multiple levels stacked). Flatten it back into a normal table:

result = (
    df.groupby(["Department", "City"])["Salary"]
    .mean()
    .reset_index()
)
result

10. Business Questions Using `groupby()`

Which department has the highest average salary?

avg_salary = df.groupby("Department")["Salary"].mean()
avg_salary.idxmax()

What’s the highest salary in each department?

df.groupby("Department")["Salary"].max()

How many employees work in each city?

df.groupby("City")["Employee"].count()

What’s the average experience by department?

df.groupby("Department")["Experience"].mean()

11. Pivot Tables

A pivot table summarizes data using rows, columns, and values — like a spreadsheet pivot table (in fact, that’s exactly what inspired the name).

Calculate average salary by department and city:

pd.pivot_table(
    df,
    values="Salary",
    index="Department",
    columns="City",
    aggfunc="mean"
)

12. Pivot Table with Multiple Aggregations

pd.pivot_table(
    df,
    values="Salary",
    index="Department",
    aggfunc=["mean", "max", "min"]
)

13. Pivot Table with Multiple Values

pd.pivot_table(
    df,
    values=["Salary", "Experience"],
    index="Department",
    aggfunc="mean"
)

This gives, for each department, both the average salary and the average experience side-by-side.

14. Cross-Tabulation with `crosstab()`

pd.crosstab() is primarily used to count combinations of two categorical variables — how many employees are in each department/city combination?

pd.crosstab(df["Department"], df["City"])

15. Cross-Tabulation with Percentages

pd.crosstab(df["Department"], df["City"], normalize="index")

This answers: what percentage of each department’s employees work in each city?

16. Add Margins

pd.crosstab(df["Department"], df["City"], margins=True)

margins=True adds row totals, column totals, and a grand total.

17. Visualize Aggregated Data

avg_salary = df.groupby("Department")["Salary"].mean()

import matplotlib.pyplot as plt

avg_salary.plot(kind="bar")
plt.title("Average Salary by Department")
plt.xlabel("Department")
plt.ylabel("Average Salary")
plt.show()

Or with Seaborn:

import seaborn as sns

sns.barplot(data=df, x="Department", y="Salary")
plt.title("Average Salary by Department")
plt.show()

18. Complete Business Analysis Example

avg_salary = df.groupby("Department")["Salary"].mean()
employee_count = df["Department"].value_counts()
max_salary = df.groupby("Department")["Salary"].max()
avg_experience = df.groupby("Department")["Experience"].mean()

print("Average Salary:")
display(avg_salary)

print("Employee Count:")
display(employee_count)

print("Maximum Salary:")
display(max_salary)

print("Average Experience:")
display(avg_experience)

19. Create a Department Summary

Combine everything into one clean summary table:

department_summary = (
    df.groupby("Department")
    .agg(
        Employee_Count=("Employee", "count"),
        Average_Salary=("Salary", "mean"),
        Maximum_Salary=("Salary", "max"),
        Average_Experience=("Experience", "mean")
    )
    .reset_index()
)

department_summary

This is a classic business-ready summary table — exactly the kind of output you’d paste into a report or dashboard.

`groupby()` vs. `pivot_table()` vs. `crosstab()`

Tool	Best Used For
`groupby()`	Flexible grouping and aggregation
`pivot_table()`	Spreadsheet-style summaries
`crosstab()`	Counting combinations of categories

Simple decision guide: Need statistics by group? → groupby(). Need a table with rows and columns? → pivot_table(). Need category counts? → crosstab().

Practice Exercise

Using the Titanic dataset, calculate: average age by passenger class, survival rate by gender, and average fare by passenger class. Then build a pivot table of survival rate by gender and class, and a cross-tabulation of gender vs. class.

Key Takeaway: Data aggregation transforms detailed row-level data into meaningful summaries that help us understand patterns and answer business questions.