Python - Pivot Tables

Pivot Tables in Python

Pivot tables are one of the most powerful and flexible features in the pandas library. A pivot table allows you to transform and summarize data in a tabular format. It is widely used in data analysis and business intelligence for its ability to slice, dice, filter, and aggregate large datasets with minimal code. In pandas, the pivot table functionality is provided through the pivot_table() method.

This guide covers everything you need to know about working with pivot tables in Python using pandas. We will cover pivot basics, multi-level aggregation, handling missing data, margins, reshaping, and real-world applications of pivot tables in analytics.

What is a Pivot Table?

A pivot table is a data summarization tool that allows you to aggregate and reshape data. In pandas, a pivot table allows you to specify:

• The index: which column(s) to group by on the rows
• The columns: which column(s) to group by on the columns
• The values: which data to aggregate
• The aggfunc: the aggregation function(s) to apply

Creating a Basic Pivot Table

Example Dataset

import pandas as pd

data = {
    'Department': ['HR', 'HR', 'IT', 'IT', 'Finance', 'Finance', 'HR', 'IT'],
    'Employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace', 'Henry'],
    'Salary': [50000, 55000, 60000, 65000, 70000, 75000, 52000, 62000],
    'Gender': ['F', 'M', 'M', 'M', 'F', 'M', 'F', 'M']
}

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

Basic Pivot Table

We can start with a simple pivot table that shows the average salary per department.

pivot = pd.pivot_table(df, index='Department', values='Salary', aggfunc='mean')
print(pivot)

Using Multiple Aggregation Functions

Sometimes, you want to calculate more than one metric for each group. You can pass a list of aggregation functions to aggfunc.

pivot = pd.pivot_table(df, index='Department', values='Salary', aggfunc=['mean', 'min', 'max', 'count'])
print(pivot)

Renaming Columns After Multiple Aggregations

pivot.columns = ['Average Salary', 'Min Salary', 'Max Salary', 'Count']
print(pivot.reset_index())

Adding Columns in Pivot Table

You can group by both rows and columns using the columns argument.

pivot = pd.pivot_table(df, index='Department', columns='Gender', values='Salary', aggfunc='mean')
print(pivot)

Result Interpretation

This shows the average salary by department and gender.

Using Multiple Index Levels

You can use multiple fields as indices by passing a list to index.

pivot = pd.pivot_table(df, index=['Department', 'Gender'], values='Salary', aggfunc='mean')
print(pivot)

Accessing MultiIndex Rows

print(pivot.loc[('IT', 'M')])

Using Multiple Columns in Pivot Table

pivot = pd.pivot_table(df, index='Department', columns=['Gender'], values='Salary', aggfunc=['mean', 'count'])
print(pivot)

Margins and Grand Totals

You can use margins=True to get row and column totals.

pivot = pd.pivot_table(df, index='Department', columns='Gender', values='Salary', aggfunc='mean', margins=True)
print(pivot)

Change the Name of Totals Row and Column

pivot = pd.pivot_table(df, index='Department', columns='Gender', values='Salary', aggfunc='mean', margins=True, margins_name='Total')
print(pivot)

Handling Missing Data

Filling Missing Values with fill_value

pivot = pd.pivot_table(df, index='Department', columns='Gender', values='Salary', aggfunc='mean', fill_value=0)
print(pivot)

Detecting NaNs in Pivot

print(pivot.isnull())

Working with Categorical Data

If you're working with categoricals, ensure that unused categories appear in the pivot.

df['Department'] = df['Department'].astype('category')
df['Department'].cat.set_categories(['HR', 'IT', 'Finance', 'Legal'], inplace=True)

pivot = pd.pivot_table(df, index='Department', values='Salary', aggfunc='mean', fill_value=0)
print(pivot)

Reshaping Pivot Tables

Resetting Index

pivot_reset = pivot.reset_index()
print(pivot_reset)

Flattening MultiIndex Columns

pivot.columns = ['_'.join(col).strip() if isinstance(col, tuple) else col for col in pivot.columns.values]
print(pivot)

Real-World Use Cases

Sales Data Analysis

sales_data = {
    'Region': ['East', 'West', 'East', 'North', 'South', 'West', 'South', 'East'],
    'Product': ['A', 'B', 'A', 'C', 'A', 'B', 'C', 'B'],
    'Revenue': [100, 200, 150, 300, 250, 220, 270, 180]
}

sales_df = pd.DataFrame(sales_data)

pivot = pd.pivot_table(sales_df, index='Region', columns='Product', values='Revenue', aggfunc='sum', fill_value=0)
print(pivot)

Customer Retention

retention_df = pd.DataFrame({
    'CustomerID': [1, 2, 3, 1, 2, 3, 1, 2],
    'Month': ['Jan', 'Jan', 'Jan', 'Feb', 'Feb', 'Feb', 'Mar', 'Mar'],
    'Purchased': [1, 1, 0, 1, 0, 0, 1, 1]
})

pivot = pd.pivot_table(retention_df, index='CustomerID', columns='Month', values='Purchased', aggfunc='max', fill_value=0)
print(pivot)

Advanced Aggregation with Custom Functions

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

pivot = pd.pivot_table(df, index='Department', values='Salary', aggfunc=salary_range)
print(pivot)

Comparing pivot() and pivot_table()

There are two similar methods in pandas: pivot() and pivot_table().

• pivot() does not allow aggregation. It is used for reshaping data and assumes there are no duplicate index/column combinations.
• pivot_table() allows aggregation via aggfunc.

Using pivot()

simple_df = pd.DataFrame({
    'ID': [1, 2, 3],
    'Date': ['2024-01', '2024-02', '2024-01'],
    'Value': [100, 200, 300]
})

pivoted = simple_df.pivot(index='ID', columns='Date', values='Value')
print(pivoted)

Using Pivot Tables with Datetime

sales = pd.DataFrame({
    'Date': pd.date_range(start='2024-01-01', periods=6, freq='M'),
    'Revenue': [1000, 1500, 1200, 1300, 1800, 1700],
    'Region': ['East', 'West', 'East', 'North', 'West', 'South']
})

pivot = pd.pivot_table(sales, index=sales['Date'].dt.year, columns=sales['Date'].dt.month, values='Revenue', aggfunc='sum')
print(pivot)

Tips for Working with Pivot Tables

• Always use fill_value when missing values might be present.
• Use margins to get subtotals and grand totals.
• Flatten multi-index columns for easier data export.
• Use reset_index() to convert the pivot back into a DataFrame.

Exporting Pivot Tables

Export to Excel

pivot.to_excel('pivot_output.xlsx')

Export to CSV

pivot.to_csv('pivot_output.csv')

Pivot tables in pandas are incredibly useful for transforming and analyzing structured data. They enable you to quickly summarize datasets with just a few lines of code. Mastery of pivot_table() equips data professionals with the ability to perform deep analytical operations on any tabular dataset. With options like multiple aggregation functions, margins, and hierarchical indexes, pandas pivot tables serve as a robust alternative to Excel-based pivoting and are a must-have tool in a data analyst's toolkit.

Whether you're analyzing sales performance, aggregating financial reports, or tracking user behavior, pivot tables provide the flexibility and performance necessary for modern data analysis in Python.