Pivot with aggregation functions in Pandas

Introduction

Pivoting helps you rearrange data to see it from a new angle. Using aggregation functions lets you summarize data while pivoting, like finding sums or averages.

You want to see total sales per product for each month.

You need to find the average score of students by class and subject.

You want to count how many times each category appears per region.

You want to compare maximum temperatures recorded each day for different cities.

Syntax

Pandas

DataFrame.pivot_table(index=None, columns=None, values=None, aggfunc='mean')

index is the column(s) to keep as rows.

columns is the column(s) to spread out as new columns.

values is the column to aggregate.

aggfunc is the function to summarize data, like 'sum', 'mean', or 'count'.

Examples

Sum of sales for each product by month.

Pandas

df.pivot_table(index='Product', columns='Month', values='Sales', aggfunc='sum')

Average score per class and subject.

Pandas

df.pivot_table(index='Class', columns='Subject', values='Score', aggfunc='mean')

Count of IDs per region and category.

Pandas

df.pivot_table(index='Region', columns='Category', values='ID', aggfunc='count')

Sample Program

This code creates a table showing total sales for each product in January and February.

Pandas

import pandas as pd

data = {
    'Product': ['A', 'A', 'B', 'B', 'C', 'C'],
    'Month': ['Jan', 'Feb', 'Jan', 'Feb', 'Jan', 'Feb'],
    'Sales': [100, 150, 200, 250, 300, 350]
}
df = pd.DataFrame(data)

pivot = df.pivot_table(index='Product', columns='Month', values='Sales', aggfunc='sum')
print(pivot)

Important Notes

If you don't specify aggfunc, it uses the average by default.

Pivot tables can handle missing data by showing NaN where no data exists.

You can use multiple aggregation functions by passing a list to aggfunc.

Summary

Pivot tables rearrange data to compare values easily.

Aggregation functions summarize data during pivoting.

Common functions include sum, mean, and count.