Pivot tables with pivot_table() in Data Analysis Python

Introduction

Pivot tables help you quickly summarize and explore data by grouping and calculating values in a simple table.

You want to see total sales by product category and region.

You need to find average test scores by student and subject.

You want to count how many times each event happened per day.

You want to compare monthly expenses by type and month.

Syntax

Data Analysis Python

pandas.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', fill_value=None)

data is your DataFrame.

index sets rows, columns sets columns, and values are the data to summarize.

Examples

Sum sales for each product in each region.

Data Analysis Python

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

Average score per student.

Data Analysis Python

df.pivot_table(values='Score', index='Student', aggfunc='mean')

Count how many amounts recorded per date.

Data Analysis Python

df.pivot_table(values='Amount', index='Date', aggfunc='count')

Sample Program

This code creates a table showing total sales for each product by region. Missing values are filled with 0.

Data Analysis Python

import pandas as pd

data = {
    'Region': ['East', 'East', 'West', 'West', 'East'],
    'Product': ['A', 'B', 'A', 'B', 'A'],
    'Sales': [100, 150, 200, 250, 300]
}
df = pd.DataFrame(data)

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

Important Notes

If some combinations have no data, use fill_value to replace missing values.

You can use different functions like sum, mean, or count with aggfunc.

Summary

Pivot tables group and summarize data easily.

Use index for rows and columns for columns.

Choose how to summarize with aggfunc.