PyInns - Home

Pivot on Two Variables in Pandas – Creating Cross-Tabulations with pivot_table() 2026

Pivoting on two variables (one for rows and one for columns) is one of the most common and insightful ways to analyze data. In 2026, using pivot_table() to create cross-tabulations between two variables (e.g., Region vs Category, Month vs Product Type) remains one of the fastest ways to uncover patterns and relationships in your data.

TL;DR — Pivot on Two Variables

Use index for the row variable
Use columns for the column variable
Use values for the metric to summarize
Use aggfunc to control how values are aggregated

1. Basic Pivot on Two Variables

import pandas as pd

df = pd.read_csv("sales_data.csv", parse_dates=["order_date"])

# Simple pivot: Sales by Region and Category
pivot = pd.pivot_table(
    df,
    values="amount",
    index="region",           # First variable (rows)
    columns="category",       # Second variable (columns)
    aggfunc="sum",            # How to summarize
    margins=True,             # Add grand totals
    margins_name="Total"
).round(2)

print(pivot)

2. Pivot on Two Variables with Time Dimension

# Region vs Month
monthly_pivot = pd.pivot_table(
    df,
    values="amount",
    index="region",
    columns=df["order_date"].dt.to_period("M").rename("month"),
    aggfunc="sum",
    margins=True
).round(2)

print(monthly_pivot)

3. Advanced Pivot on Two Variables with Multiple Statistics

detailed_pivot = pd.pivot_table(
    df,
    values="amount",
    index=["region", "category"],
    columns=df["order_date"].dt.year.rename("year"),
    aggfunc=["sum", "mean", "count"],
    margins=True
).round(2)

print(detailed_pivot)

4. Best Practices in 2026

Choose meaningful variables for index and columns — usually one categorical and one time-based
Use margins=True to get row and column totals automatically
Apply round(2) for clean, professional-looking reports
Use date components extracted with .dt.to_period() for clean month/year columns
Start simple with one metric, then expand to multiple statistics using aggfunc as a dictionary or list

Conclusion

Pivoting on two variables is one of the most effective ways to explore relationships in your data. In 2026, pd.pivot_table() makes this process clean, fast, and flexible. Whether you are comparing sales by region and category, performance by month and product, or any other combination — mastering two-variable pivots will significantly improve your data analysis and reporting capabilities.

Next steps: