Pythonpythonpandasdata-analysispivot-tableproduction-ready
Pandas Pivot Table Analysis
Advanced pivot table patterns for multi-dimensional data analysis with custom aggregations, margins, and formatting.
import pandas as pd
import numpy as np
from typing import Any
def create_sales_analysis(df: pd.DataFrame) -> dict[str, pd.DataFrame]:
"""Generate comprehensive sales analysis using pivot tables.
Expected columns: date, region, product, category, revenue, quantity, cost
"""
# Ensure date column is datetime
df["date"] = pd.to_datetime(df["date"])
df["month"] = df["date"].dt.to_period("M")
df["quarter"] = df["date"].dt.to_period("Q")
df["profit"] = df["revenue"] - df["cost"]
df["margin_pct"] = (df["profit"] / df["revenue"] * 100).round(1)
# 1. Revenue by Region and Product with margins (totals)
revenue_pivot = pd.pivot_table(
df,
values="revenue",
index="region",
columns="category",
aggfunc="sum",
margins=True,
margins_name="Total",
fill_value=0,
).round(2)
# 2. Multi-metric summary by quarter
quarterly_summary = pd.pivot_table(
df,
values=["revenue", "quantity", "profit"],
index="quarter",
columns="region",
aggfunc={
"revenue": "sum",
"quantity": "sum",
"profit": ["sum", "mean"],
},
fill_value=0,
).round(2)
# 3. Month-over-month growth by region
monthly_revenue = pd.pivot_table(
df,
values="revenue",
index="month",
columns="region",
aggfunc="sum",
fill_value=0,
)
growth_pct = monthly_revenue.pct_change() * 100
growth_pct = growth_pct.round(1)
# 4. Top products per region with rank
product_performance = pd.pivot_table(
df,
values=["revenue", "quantity", "margin_pct"],
index=["region", "product"],
aggfunc={"revenue": "sum", "quantity": "sum", "margin_pct": "mean"},
).round(2)
# Rank products within each region
product_performance["rank"] = product_performance.groupby(level="region")[
"revenue"
].rank(ascending=False, method="dense")
product_performance = product_performance.sort_values(
["region", "rank"]
)
# 5. Cohort analysis: customer retention style pivot
# Shows how revenue from each acquisition month decays over time
def cohort_analysis(df: pd.DataFrame) -> pd.DataFrame:
df["cohort_month"] = df.groupby("customer_id")["date"].transform("min").dt.to_period("M")
df["months_since"] = (
df["date"].dt.to_period("M") - df["cohort_month"]
).apply(lambda x: x.n)
cohort_pivot = pd.pivot_table(
df,
values="customer_id",
index="cohort_month",
columns="months_since",
aggfunc="nunique",
)
# Convert to retention percentages
cohort_sizes = cohort_pivot.iloc[:, 0]
retention = cohort_pivot.div(cohort_sizes, axis=0) * 100
return retention.round(1)
return {
"revenue_by_region": revenue_pivot,
"quarterly_summary": quarterly_summary,
"growth_pct": growth_pct,
"product_performance": product_performance,
}
def format_pivot_for_export(
pivot: pd.DataFrame,
currency_cols: list[str] | None = None,
pct_cols: list[str] | None = None,
) -> pd.DataFrame:
"""Format a pivot table for Excel/CSV export."""
formatted = pivot.copy()
if currency_cols:
for col in currency_cols:
if col in formatted.columns:
formatted[col] = formatted[col].apply(lambda x: f"$" + f"{x:,.2f}")
if pct_cols:
for col in pct_cols:
if col in formatted.columns:
formatted[col] = formatted[col].apply(lambda x: f"{x:.1f}%")
return formattedtitle: "Pandas Pivot Table Analysis" description: "Advanced pivot table patterns for multi-dimensional data analysis with custom aggregations, margins, and formatting." category: "Python" language: "python" tags: ["python", "pandas", "data-analysis", "pivot-table", "production-ready"] createdAt: "2025-02-20" updatedAt: "2025-03-10"
Overview
Advanced pivot table techniques for multi-dimensional business data analysis. Covers revenue breakdowns, month-over-month growth calculations, ranked product performance, and cohort retention analysis using pandas pivot tables and groupby operations.
Code
pivot = pd.pivot_table(
df,
values="revenue",
index="region",
columns="quarter",
aggfunc="sum",
margins=True,
)Notes
margins=Trueadds row/column totals automatically- Multi-value aggregation supports different functions per metric via dict
aggfunc pct_change()on a pivoted DataFrame gives period-over-period growth rates- Ranking within groups uses
groupby().rank()on the pivot result - For very large datasets, consider
daskDataFrames which support the same pivot API