Pandas Pivot Table: Multidimensional Data Analysis

In data analytics, you often start with hundreds or millions of transactional records. Consider this realistic sales log:

Scanning individual rows manually is slow and prone to errors. As datasets grow to thousands of rows, it becomes impossible to spot trends. A Pivot Table transforms this flat structure into a summarized 2D matrix:

Before touching any Python code, pause and answer these four questions about your analytical goal:

In Pandas, we construct a pivot table using the top-level function pd.pivot_table() (or the DataFrame method df.pivot_table()):

import pandas as pd

# Creating a 2D summarized pivot table
pivot_df = pd.pivot_table(
    df,                # 1. Source DataFrame
    index="Product",   # 2. Row group(s)
    columns="Region",  # 3. Column group(s)
    values="Sales",    # 4. Numerical metric to summarize
    aggfunc="sum"      # 5. Calculation to perform ('sum', 'mean', etc.)
)

Given the sales dataset below, create a Pivot Table showing total Sales for each Product across each Region:

Both groupby() and pivot_table() summarize raw records into aggregated numbers. However, their presentation and analytical purpose differ:

df.groupby(["Product", "Region"])["Sales"].sum()

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

Changing the aggfunc changes the analytical question you are answering:

What if products belong to higher-level categories (e.g., Electronics, Furniture)? You can pass a list of column names to index:

# Passing a list to index creates a two-level hierarchical index
pd.pivot_table(
    sales,
    index=["Category", "Product"],  # Multi-level rows
    columns="Region",
    values="Sales",
    aggfunc="sum"
)

In real data, not every product is sold in every region. By default, Pandas places NaNin cells where no records exist. In an executive revenue report, stakeholders prefer seeing $0rather than NaN.

Use fill_value=0 to replace empty group intersections with 0:

Consider this comprehensive sales dataset across multiple categories, products, and regions:

In executive scorecards, you often want to see both the total volume and the average transaction size together. You can pass a list of functions to aggfunc:

# Passing a list of functions creates hierarchical column headers
pd.pivot_table(
    sales,
    index="Product",
    columns="Region",
    values="Sales",
    aggfunc=["sum", "mean"],  # Both Total and Average
    fill_value=0
)

You are given the company performance dataset df with columns Department, Region, Employee, Performance, and Sales: