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

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

# Create a small demo file so this example runs as-is
# (in your project, sales_data.csv is your real export)
import numpy as np
rng = np.random.default_rng(42)
n = 60
pd.DataFrame({
    "order_date": pd.date_range("2025-10-01", periods=n, freq="3D"),
    "customer_id": rng.integers(101, 121, n),
    "customer_name": rng.choice([" ana", "ben ", "chen", "dana"], n),
    "product_id": rng.integers(1, 9, n),
    "region": rng.choice(["North", "South", "East", "West"], n),
    "category": rng.choice(["Electronics", "Books", "Home"], n),
    "payment_method": rng.choice(["card", "cash", "wallet"], n),
    "segment": rng.choice(["consumer", "business"], n),
    "quantity": rng.integers(1, 6, n),
    "amount": rng.uniform(50, 2500, n).round(2),
}).assign(
    ship_date=lambda d: d["order_date"] + pd.Timedelta(days=2),
    status=lambda d: np.where(d["amount"] > 2000, "priority", "standard"),
).to_csv("sales_data.csv", index=False)

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

# Helper columns: pivot_table() works best with real column names
df["month"] = df["order_date"].dt.to_period("M")
df["year"] = df["order_date"].dt.year

# 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="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="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

Cross-Tabulation Explained

Pivoting on two variables puts one categorical variable on the rows and another on the columns, so each cell shows the result for one combination. It is the quickest way to see interactions, for example which product categories sell best in which regions. When you only need counts, pd.crosstab() is a shortcut; with normalize="index" it converts counts into row percentages.

Worked Example: Counts and Row Percentages

This example compares a count pivot with pd.crosstab() and percentage output.

import pandas as pd

df = pd.DataFrame({
    "region": ["N", "N", "N", "S", "S"],
    "payment": ["card", "cash", "card", "card", "card"],
})

counts = pd.crosstab(df["region"], df["payment"])
print(counts)
# payment  card  cash
# region
# N           2     1
# S           2     0

share = pd.crosstab(df["region"], df["payment"], normalize="index").round(2)
print(share)

The shares show that two thirds of North payments use a card (0.67), while South uses cards for every order (1.0). Percentages make regions with different sizes comparable.

Common Mistakes

• Comparing raw counts between unequal groups: normalise to percentages
• High-cardinality variables: hundreds of columns are unreadable; group rare values into "Other"
• Forgetting missing combinations: use fill_value=0 in pivot_table()
• Swapping rows and columns: put the variable with more values on the rows

Quick Summary

• Two variables = rows and columns of one grid
• Use pd.crosstab() for counts and percentages
• Normalise when group sizes differ

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:

• Choose two important variables in your dataset (e.g., Region + Category or Month + Product) and create a pivot table to see the cross-relationships

Related Tutorials

• Pivot Tables in Pandas – Powerful Data Reshaping with pivot_table() in Python 2026
• Grouping by Multiple Variables in Pandas – Multi-Level GroupBy Best Practices 2026
• Filling Missing Values in Pivot Tables – Best Practices in Pandas 2026