Building a pandas Profitability Report by Product, Region, and Time Period: Joining, Aggregating, and Exporting a Multi-Dimensional Business Summary

Introduction

You've just been handed three spreadsheets and a deadline. One file has sales transactions — thousands of rows, one per order line. Another has a product catalog with cost data. A third has regional sales territory mappings. The ask: produce a profitability summary broken down by product category, sales region, and quarter so leadership can see where the business is actually making money. In Excel, you'd be deep in VLOOKUP hell, manually building pivot tables, and formatting everything by hand. Every month you'd repeat the same tedious ritual. There's a better way.

This lesson walks you through building a complete, production-quality profitability report in pandas — from raw source files all the way to a formatted, multi-sheet Excel workbook. You'll join transaction data to reference tables, engineer calculated profitability metrics, aggregate across three dimensions simultaneously, handle the messy edge cases that always appear in real business data, and export a report that a non-technical stakeholder can open and immediately understand.

By the end, you'll have a reusable script you can run every month with new data. No manual steps. No reformatting. No copy-pasting pivot tables.

What you'll learn:

• How to join multiple DataFrames to enrich transaction data with product cost and regional metadata
• How to engineer margin and profitability columns from raw revenue and cost fields
• How to aggregate across multiple dimensions (product, region, time period) simultaneously using groupby and pivot_table
• How to build a multi-dimensional summary with subtotals and grand totals
• How to export a polished, multi-sheet Excel workbook using openpyxl

---

Prerequisites

You should be comfortable with pandas basics — loading data, selecting columns, filtering rows. If you've worked through joining DataFrames with pandas merge and grouping and aggregating in pandas, you're in the right place. You should also have a working Python environment; if you're starting from scratch, see setting up Python for data analysis.

pip install pandas openpyxl xlsxwriter

---

Setting Up the Data Model

Before we write a single line of analysis code, we need to understand the shape of our inputs. Profitability reporting almost always involves at least three tables: a fact table (transactions, orders, or invoices), a product dimension (catalog with cost data), and a geography dimension (territory or region mappings). This is a classic star schema, and recognizing that structure upfront saves you from writing confused, hard-to-maintain code.

Here's the schema we'll work with throughout this lesson:

transactions.csv — One row per order line item:

• order_id, order_date, customer_id, product_id, quantity, unit_price, discount_pct, salesperson_id, territory_id

products.csv — Product reference table:

• product_id, product_name, category, subcategory, unit_cost

territories.csv — Territory to region mapping:

• territory_id, territory_name, region, country

Let's build a realistic dataset programmatically so you can follow along exactly:

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random

np.random.seed(42)
random.seed(42)

# ── Date range: two full years
dates = pd.date_range("2022-01-01", "2023-12-31", freq="D")

# ── Reference data
products = pd.DataFrame({
    "product_id": [f"P{str(i).zfill(3)}" for i in range(1, 21)],
    "product_name": [
        "Widget Alpha", "Widget Beta", "Widget Gamma",
        "Gadget Pro", "Gadget Lite", "Gadget Ultra",
        "Component A", "Component B", "Component C", "Component D",
        "Service Pack Basic", "Service Pack Advanced", "Service Pack Premium",
        "Accessory Kit S", "Accessory Kit M", "Accessory Kit L",
        "Software License A", "Software License B",
        "Maintenance Plan", "Extended Warranty"
    ],
    "category": [
        "Widgets", "Widgets", "Widgets",
        "Gadgets", "Gadgets", "Gadgets",
        "Components", "Components", "Components", "Components",
        "Services", "Services", "Services",
        "Accessories", "Accessories", "Accessories",
        "Software", "Software",
        "Services", "Services"
    ],
    "subcategory": [
        "Standard", "Standard", "Premium",
        "Consumer", "Consumer", "Enterprise",
        "Electronic", "Electronic", "Mechanical", "Mechanical",
        "Support", "Support", "Support",
        "Small", "Medium", "Large",
        "Single", "Enterprise",
        "Maintenance", "Warranty"
    ],
    "unit_cost": [
        12.50, 18.75, 34.00,
        45.00, 22.00, 89.00,
        8.50, 14.25, 6.75, 11.00,
        5.00, 12.00, 25.00,
        9.50, 17.00, 28.50,
        35.00, 120.00,
        18.00, 30.00
    ]
})

territories = pd.DataFrame({
    "territory_id": [f"T{str(i).zfill(2)}" for i in range(1, 13)],
    "territory_name": [
        "New England", "Mid-Atlantic", "Southeast", "Great Lakes",
        "South Central", "Mountain West", "Pacific Northwest", "California",
        "UK", "Germany", "France", "APAC"
    ],
    "region": [
        "Northeast", "Northeast", "Southeast", "Midwest",
        "South", "West", "West", "West",
        "EMEA", "EMEA", "EMEA", "APAC"
    ],
    "country": [
        "USA", "USA", "USA", "USA",
        "USA", "USA", "USA", "USA",
        "UK", "Germany", "France", "Multiple"
    ]
})

# ── Generate 15,000 transaction rows
n = 15000
transactions = pd.DataFrame({
    "order_id": [f"ORD-{100000 + i}" for i in range(n)],
    "order_date": np.random.choice(dates, n),
    "customer_id": [f"C{str(np.random.randint(1, 501)).zfill(4)}" for _ in range(n)],
    "product_id": np.random.choice(products["product_id"], n, p=[0.08, 0.07, 0.05, 0.07, 0.06, 0.04, 0.06, 0.06, 0.05, 0.05, 0.05, 0.04, 0.03, 0.05, 0.05, 0.04, 0.06, 0.04, 0.05, 0.05]),
    "quantity": np.random.randint(1, 51, n),
    "unit_price": None,  # we'll set this from product data
    "discount_pct": np.random.choice([0, 0, 0, 0.05, 0.10, 0.15, 0.20], n),
    "territory_id": np.random.choice(territories["territory_id"], n),
    "salesperson_id": [f"SP{str(np.random.randint(1, 31)).zfill(3)}" for _ in range(n)],
})

# Set unit_price as cost * markup (1.4x to 2.5x depending on category), with noise
product_price_map = dict(zip(products["product_id"], products["unit_cost"] * np.random.uniform(1.4, 2.5, len(products))))
transactions["unit_price"] = transactions["product_id"].map(product_price_map).round(2)

# Introduce ~2% missing territory_id to simulate real data problems
missing_idx = np.random.choice(transactions.index, size=int(0.02 * n), replace=False)
transactions.loc[missing_idx, "territory_id"] = np.nan

print(f"Transactions shape: {transactions.shape}")
print(transactions.head(3))

Now save these to CSV files (or just keep them in memory for the tutorial). The important thing is that we have three distinct DataFrames, each with a clear role.

---

Step 1: Validating and Profiling Before You Touch the Data

Experienced analysts never assume source data is clean. Before joining anything, run a quick validation pass on each table. This is the step most tutorials skip, and it's why "it looked right in the notebook" reports end up wrong in production.

def profile_table(df, name):
    """Quick data profile for any DataFrame."""
    print(f"\n{'='*50}")
    print(f"TABLE: {name}")
    print(f"  Shape: {df.shape[0]:,} rows × {df.shape[1]} cols")
    print(f"\n  Null counts:")
    null_counts = df.isnull().sum()
    for col, cnt in null_counts[null_counts > 0].items():
        print(f"    {col}: {cnt:,} ({cnt/len(df):.1%})")
    print(f"\n  Dtypes: {df.dtypes.to_dict()}")

profile_table(transactions, "transactions")
profile_table(products, "products")
profile_table(territories, "territories")

# Check referential integrity: are all product_ids in transactions valid?
tx_products = set(transactions["product_id"].unique())
ref_products = set(products["product_id"].unique())
orphan_products = tx_products - ref_products
print(f"\nOrphan product_ids in transactions: {orphan_products}")

# Same for territories
tx_territories = set(transactions["territory_id"].dropna().unique())
ref_territories = set(territories["territory_id"].unique())
orphan_territories = tx_territories - ref_territories
print(f"Orphan territory_ids in transactions: {orphan_territories}")

Run this before every pipeline execution. It's cheap, takes a second, and will save you from publishing a report that quietly dropped 15% of your revenue because a join key changed upstream.

---

Step 2: Cleaning and Preparing the Source Data

With the profile done, let's handle what we found: missing territory_id values, and we need to parse order_date properly.

# Parse dates — critical for time-based aggregation
transactions["order_date"] = pd.to_datetime(transactions["order_date"])

# Derive time dimensions we'll need for aggregation
transactions["year"] = transactions["order_date"].dt.year
transactions["quarter"] = transactions["order_date"].dt.quarter
transactions["month"] = transactions["order_date"].dt.month
transactions["year_quarter"] = (
    transactions["order_date"].dt.to_period("Q").astype(str)
)

# Handle missing territory_id — assign to an "Unassigned" territory
# rather than dropping rows (we don't want to lose revenue)
transactions["territory_id"] = transactions["territory_id"].fillna("UNKNOWN")

# Add an UNKNOWN territory to the reference table so joins work
unknown_territory = pd.DataFrame([{
    "territory_id": "UNKNOWN",
    "territory_name": "Unassigned",
    "region": "Unassigned",
    "country": "Unknown"
}])
territories = pd.concat([territories, unknown_territory], ignore_index=True)

# Validate numeric fields
assert (transactions["quantity"] > 0).all(), "Found non-positive quantities!"
assert (transactions["unit_price"] > 0).all(), "Found non-positive prices!"
assert transactions["discount_pct"].between(0, 1).all(), "Discount out of range!"

print("Validation passed. Data is ready for joining.")

The date parsing deserves special attention. Creating year_quarter as a period string (like "2022Q1") gives us a clean label that sorts naturally and is immediately readable in a report. If you're new to pandas date handling, working with dates and time series in pandas covers the full range of period and resample operations.

---

Step 3: Joining the Dimensions — Building Your Enriched Fact Table

Now we merge all three tables into a single analysis-ready DataFrame. The strategy is always the same: start with the fact table, then left-join each dimension onto it.

# Join 1: Enrich transactions with product data
enriched = transactions.merge(
    products[["product_id", "product_name", "category", "subcategory", "unit_cost"]],
    on="product_id",
    how="left",
    validate="m:1"  # Many transactions to one product — enforce this
)

print(f"After product join: {enriched.shape}")
print(f"Null unit_cost: {enriched['unit_cost'].isnull().sum()}")

# Join 2: Enrich with territory/region data
enriched = enriched.merge(
    territories[["territory_id", "territory_name", "region", "country"]],
    on="territory_id",
    how="left",
    validate="m:1"
)

print(f"After territory join: {enriched.shape}")
print(f"Null region: {enriched['region'].isnull().sum()}")

Notice the validate="m:1" parameter. This is a pandas feature that most tutorials never mention: it tells merge to enforce that the right-side key is unique (many-to-one). If your products table has duplicate product_id rows — which happens when someone has been maintaining it manually — pandas will raise an error immediately rather than silently blowing up your row count with a Cartesian explosion.

After both joins, your enriched DataFrame has everything you need in one flat table. Let's verify the row count is exactly what we started with:

assert len(enriched) == len(transactions), \
    f"Row count changed! {len(transactions)} → {len(enriched)}"

If this assertion fails, you have a join problem. Either there are duplicate keys in a dimension table, or the merge type is wrong. See detecting and resolving data quality issues across merged DataFrames for a systematic diagnosis framework.

---

Step 4: Engineering Profitability Metrics

With the enriched fact table in hand, we can calculate the financial metrics that make this a profitability report rather than just a revenue report. This is where the business logic lives.

# Revenue = quantity × unit_price × (1 - discount)
enriched["gross_revenue"] = (
    enriched["quantity"] * enriched["unit_price"]
).round(2)

enriched["net_revenue"] = (
    enriched["quantity"] * enriched["unit_price"] * (1 - enriched["discount_pct"])
).round(2)

# COGS = quantity × unit_cost
enriched["cogs"] = (enriched["quantity"] * enriched["unit_cost"]).round(2)

# Gross profit = net_revenue - COGS
enriched["gross_profit"] = (enriched["net_revenue"] - enriched["cogs"]).round(2)

# Gross margin % = gross_profit / net_revenue
# Handle division by zero defensively
enriched["gross_margin_pct"] = np.where(
    enriched["net_revenue"] > 0,
    (enriched["gross_profit"] / enriched["net_revenue"] * 100).round(2),
    0.0
)

# Discount amount (useful for the discount analysis slice)
enriched["discount_amount"] = (
    enriched["gross_revenue"] - enriched["net_revenue"]
).round(2)

# Verify: no negative revenues (would indicate data problems)
negative_revenue = enriched[enriched["net_revenue"] < 0]
if len(negative_revenue) > 0:
    print(f"WARNING: {len(negative_revenue)} rows with negative revenue")
    print(negative_revenue[["order_id", "product_id", "net_revenue"]].head())

print("\nSample metrics:")
print(enriched[["order_id", "gross_revenue", "net_revenue", 
                "cogs", "gross_profit", "gross_margin_pct"]].head(5))

Let's also validate at the aggregate level before we go further. Sanity checks on totals are fast and catch calculation errors before they propagate:

total_gross = enriched["gross_revenue"].sum()
total_net = enriched["net_revenue"].sum()
total_cogs = enriched["cogs"].sum()
total_gp = enriched["gross_profit"].sum()
overall_margin = total_gp / total_net * 100

print(f"\nPortfolio Summary:")
print(f"  Gross Revenue:   ${total_gross:>15,.2f}")
print(f"  Net Revenue:     ${total_net:>15,.2f}")
print(f"  COGS:            ${total_cogs:>15,.2f}")
print(f"  Gross Profit:    ${total_gp:>15,.2f}")
print(f"  Overall Margin:  {overall_margin:.1f}%")
print(f"  Avg Discount:    {enriched['discount_pct'].mean():.1%}")

These totals are your control numbers. Every slice and aggregation you produce later should sum back to these figures. If they don't, you have a bug.

---

Step 5: Building the Multi-Dimensional Aggregations

Now we build the actual report tables. A profitability report typically has several views:

1. By product category — Which categories drive the most profit?
2. By region — Where are margins strongest and weakest?
3. By time period (quarter) — How is profitability trending?
4. Cross-tabulated: category × region — The two-dimensional view leadership always wants
5. Quarterly trend by category — Are margins improving or eroding over time?

Let's build each systematically.

Aggregation 1: By Product Category

def weighted_margin(df):
    """Correct margin calculation: GP / Revenue, not mean of row margins."""
    return (df["gross_profit"].sum() / df["net_revenue"].sum() * 100).round(2)

category_summary = enriched.groupby("category").agg(
    orders=("order_id", "count"),
    total_quantity=("quantity", "sum"),
    gross_revenue=("gross_revenue", "sum"),
    discount_amount=("discount_amount", "sum"),
    net_revenue=("net_revenue", "sum"),
    cogs=("cogs", "sum"),
    gross_profit=("gross_profit", "sum"),
).reset_index()

# Derived metrics at the aggregate level
category_summary["gross_margin_pct"] = (
    category_summary["gross_profit"] / category_summary["net_revenue"] * 100
).round(2)

category_summary["avg_order_size"] = (
    category_summary["net_revenue"] / category_summary["orders"]
).round(2)

category_summary["discount_rate"] = (
    category_summary["discount_amount"] / category_summary["gross_revenue"] * 100
).round(2)

# Revenue share
category_summary["revenue_share_pct"] = (
    category_summary["net_revenue"] / category_summary["net_revenue"].sum() * 100
).round(2)

# Sort by gross profit descending
category_summary = category_summary.sort_values("gross_profit", ascending=False)

print(category_summary[["category", "net_revenue", "gross_profit", 
                         "gross_margin_pct", "revenue_share_pct"]].to_string(index=False))

Aggregation 2: By Region

region_summary = enriched.groupby("region").agg(
    orders=("order_id", "count"),
    total_quantity=("quantity", "sum"),
    gross_revenue=("gross_revenue", "sum"),
    net_revenue=("net_revenue", "sum"),
    cogs=("cogs", "sum"),
    gross_profit=("gross_profit", "sum"),
    discount_amount=("discount_amount", "sum"),
).reset_index()

region_summary["gross_margin_pct"] = (
    region_summary["gross_profit"] / region_summary["net_revenue"] * 100
).round(2)

region_summary["revenue_share_pct"] = (
    region_summary["net_revenue"] / region_summary["net_revenue"].sum() * 100
).round(2)

region_summary = region_summary.sort_values("gross_profit", ascending=False)
print(region_summary.to_string(index=False))

Aggregation 3: By Quarter (Time Series View)

quarterly_summary = enriched.groupby(["year", "quarter", "year_quarter"]).agg(
    orders=("order_id", "count"),
    net_revenue=("net_revenue", "sum"),
    cogs=("cogs", "sum"),
    gross_profit=("gross_profit", "sum"),
    discount_amount=("discount_amount", "sum"),
).reset_index()

quarterly_summary["gross_margin_pct"] = (
    quarterly_summary["gross_profit"] / quarterly_summary["net_revenue"] * 100
).round(2)

quarterly_summary = quarterly_summary.sort_values(["year", "quarter"])

print(quarterly_summary[["year_quarter", "net_revenue", "gross_profit", 
                          "gross_margin_pct"]].to_string(index=False))

For period-over-period comparisons (quarter-over-quarter growth in revenue and margin), you can use pct_change() after sorting by time. For a complete treatment of that pattern, see comparing periods and calculating month-over-month, year-over-year, and rolling changes in pandas.

quarterly_summary["revenue_qoq_pct"] = (
    quarterly_summary["net_revenue"].pct_change() * 100
).round(2)

quarterly_summary["margin_qoq_change"] = (
    quarterly_summary["gross_margin_pct"].diff().round(2)
)

Aggregation 4: Category × Region Cross-Tab (The Two-Dimensional View)

This is the view that's most expensive to build manually in Excel and most natural to build in pandas:

# Multi-dimensional groupby
cat_region = enriched.groupby(["category", "region"]).agg(
    net_revenue=("net_revenue", "sum"),
    gross_profit=("gross_profit", "sum"),
    orders=("order_id", "count"),
).reset_index()

cat_region["gross_margin_pct"] = (
    cat_region["gross_profit"] / cat_region["net_revenue"] * 100
).round(2)

# Pivot to wide format for readability: categories as rows, regions as columns
revenue_pivot = cat_region.pivot_table(
    index="category",
    columns="region",
    values="net_revenue",
    aggfunc="sum",
    margins=True,       # Adds row and column totals
    margins_name="TOTAL",
    fill_value=0
).round(2)

print("\nNet Revenue by Category × Region:")
print(revenue_pivot.to_string())

# Same for margin — here we can't use the default pivot_table aggfunc
# because simple mean of margins is wrong; we need to compute it properly
margin_pivot = cat_region.pivot_table(
    index="category",
    columns="region",
    values="gross_margin_pct",
    aggfunc="mean",   # This works here because each cell is already a weighted margin
    fill_value=0
).round(2)

Aggregation 5: Quarterly Trend by Category

cat_quarter = enriched.groupby(["category", "year_quarter"]).agg(
    net_revenue=("net_revenue", "sum"),
    gross_profit=("gross_profit", "sum"),
).reset_index()

cat_quarter["gross_margin_pct"] = (
    cat_quarter["gross_profit"] / cat_quarter["net_revenue"] * 100
).round(2)

# Pivot: categories as rows, quarters as columns (revenue)
revenue_trend = cat_quarter.pivot_table(
    index="category",
    columns="year_quarter",
    values="net_revenue",
    aggfunc="sum",
    fill_value=0
).round(2)

# Add a total row
revenue_trend.loc["TOTAL"] = revenue_trend.sum()

print("\nRevenue Trend by Category × Quarter:")
print(revenue_trend.to_string())

---

Step 6: Adding Subtotals and Grand Totals Properly

The pivot tables above get you close, but boardroom-ready reports need clean subtotals. The margins=True parameter in pivot_table handles a lot of this, but for more control — especially when you want subtotals at an intermediate grouping level — you need a different approach.

def build_summary_with_totals(df, groupby_cols, value_cols):
    """
    Build a summary DataFrame that includes subtotals for the first
    grouping column and a grand total row.
    """
    # Detailed rows
    detail = df.groupby(groupby_cols)[value_cols].sum().reset_index()
    
    # Subtotals (group by first dimension only)
    subtotals = df.groupby(groupby_cols[0])[value_cols].sum().reset_index()
    subtotals[groupby_cols[1]] = "── SUBTOTAL"
    
    # Grand total
    grand = df[value_cols].sum().to_frame().T
    for col in groupby_cols:
        grand[col] = "GRAND TOTAL"
    
    # Combine: detail rows, then for each category add subtotal
    frames = []
    for cat in detail[groupby_cols[0]].unique():
        cat_rows = detail[detail[groupby_cols[0]] == cat]
        frames.append(cat_rows)
        frames.append(subtotals[subtotals[groupby_cols[0]] == cat])
    frames.append(grand)
    
    result = pd.concat(frames, ignore_index=True)
    
    # Recalculate margin
    result["gross_margin_pct"] = np.where(
        result["net_revenue"] > 0,
        (result["gross_profit"] / result["net_revenue"] * 100).round(2),
        0.0
    )
    return result

value_cols = ["net_revenue", "cogs", "gross_profit", "orders"]
summary_with_totals = build_summary_with_totals(
    enriched.rename(columns={"order_id": "orders"}).assign(orders=1),
    groupby_cols=["category", "region"],
    value_cols=["net_revenue", "cogs", "gross_profit"]
)

For a deeper exploration of building boardroom-ready tables with pandas, including running totals and formatted grand total rows, see calculating running totals, subtotals, and grand totals in pandas for boardroom-ready summary tables.

---

Step 7: Packaging and Exporting to Excel

This is where the report becomes shareable. We'll write a multi-sheet workbook where each sheet corresponds to one of the aggregation views we built, formatted so a non-technical reader can immediately understand it.

from openpyxl import load_workbook
from openpyxl.styles import (
    PatternFill, Font, Alignment, Border, Side, numbers
)
from openpyxl.utils import get_column_letter
from openpyxl.styles.numbers import FORMAT_PERCENTAGE_00, FORMAT_NUMBER_COMMA_SEPARATED1
import os
from datetime import datetime

def format_currency_cols(ws, col_indices, start_row, end_row):
    """Apply currency formatting to specified columns."""
    for col_idx in col_indices:
        col_letter = get_column_letter(col_idx)
        for row in range(start_row, end_row + 1):
            cell = ws[f"{col_letter}{row}"]
            cell.number_format = '#,##0.00'

def format_percentage_cols(ws, col_indices, start_row, end_row):
    """Apply percentage formatting."""
    for col_idx in col_indices:
        col_letter = get_column_letter(col_idx)
        for row in range(start_row, end_row + 1):
            ws[f"{col_letter}{row}"].number_format = '0.00"%"'

def apply_header_style(ws, row_num, num_cols):
    """Bold, dark background, white text for header rows."""
    header_fill = PatternFill(start_color="1F3864", end_color="1F3864", fill_type="solid")
    header_font = Font(bold=True, color="FFFFFF", name="Calibri", size=11)
    for col in range(1, num_cols + 1):
        cell = ws.cell(row=row_num, column=col)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center")

def auto_fit_columns(ws):
    """Approximate column width based on content."""
    for col in ws.columns:
        max_length = 0
        col_letter = get_column_letter(col[0].column)
        for cell in col:
            if cell.value is not None:
                max_length = max(max_length, len(str(cell.value)))
        ws.column_dimensions[col_letter].width = min(max_length + 4, 40)

def write_df_to_sheet(ws, df, title, currency_cols=None, pct_cols=None):
    """Write a DataFrame to a worksheet with formatting."""
    # Title row
    ws["A1"] = title
    ws["A1"].font = Font(bold=True, size=14, name="Calibri", color="1F3864")
    ws["A2"] = f"Generated: {datetime.now().strftime('%Y-%m-%d %H:%M')}"
    ws["A2"].font = Font(italic=True, size=9, color="808080")
    
    # Write the DataFrame starting at row 4
    start_row = 4
    for col_idx, col_name in enumerate(df.columns, 1):
        ws.cell(row=start_row, column=col_idx, value=col_name.replace("_", " ").title())
    
    apply_header_style(ws, start_row, len(df.columns))
    
    # Data rows
    for row_idx, row in enumerate(df.itertuples(index=False), start=start_row + 1):
        for col_idx, value in enumerate(row, 1):
            ws.cell(row=row_idx, column=col_idx, value=value)
    
    data_end_row = start_row + len(df)
    
    # Apply number formats
    if currency_cols:
        format_currency_cols(ws, currency_cols, start_row + 1, data_end_row)
    if pct_cols:
        format_percentage_cols(ws, pct_cols, start_row + 1, data_end_row)
    
    # Alternating row colors
    light_fill = PatternFill(start_color="EBF3FB", end_color="EBF3FB", fill_type="solid")
    for row_idx in range(start_row + 1, data_end_row + 1):
        if (row_idx - start_row) % 2 == 0:
            for col_idx in range(1, len(df.columns) + 1):
                ws.cell(row=row_idx, column=col_idx).fill = light_fill
    
    auto_fit_columns(ws)
    ws.freeze_panes = ws.cell(row=start_row + 1, column=1)

# ── Build the workbook
output_path = "profitability_report.xlsx"

# Use openpyxl as the engine for formatting support
with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
    
    # Sheet 1: Executive Summary (category level)
    category_export = category_summary[[
        "category", "orders", "gross_revenue", "discount_amount",
        "net_revenue", "cogs", "gross_profit", 
        "gross_margin_pct", "revenue_share_pct"
    ]].copy()
    category_export.to_excel(writer, sheet_name="By Category", index=False, startrow=3)
    
    # Sheet 2: Regional Summary
    region_summary[[
        "region", "orders", "net_revenue", "cogs", 
        "gross_profit", "gross_margin_pct", "revenue_share_pct"
    ]].to_excel(writer, sheet_name="By Region", index=False, startrow=3)
    
    # Sheet 3: Quarterly Trend
    quarterly_summary[[
        "year_quarter", "orders", "net_revenue", "cogs", 
        "gross_profit", "gross_margin_pct",
        "revenue_qoq_pct", "margin_qoq_change"
    ]].to_excel(writer, sheet_name="Quarterly Trend", index=False, startrow=3)
    
    # Sheet 4: Category × Region Revenue Pivot
    revenue_pivot.to_excel(writer, sheet_name="Cat x Region Revenue")
    
    # Sheet 5: Category × Quarter Trend Pivot
    revenue_trend.to_excel(writer, sheet_name="Cat x Quarter Trend")
    
    # Sheet 6: Full transaction-level detail (filtered to just key fields)
    enriched[[
        "order_id", "order_date", "product_name", "category",
        "territory_name", "region", "year_quarter",
        "quantity", "gross_revenue", "discount_pct", "discount_amount",
        "net_revenue", "cogs", "gross_profit", "gross_margin_pct"
    ]].sort_values("order_date").to_excel(
        writer, sheet_name="Transaction Detail", index=False, startrow=3
    )

# ── Now apply formatting using openpyxl directly
wb = load_workbook(output_path)

# Format "By Category" sheet
ws = wb["By Category"]
ws["A1"] = "Profitability Report — By Product Category"
ws["A1"].font = Font(bold=True, size=14, name="Calibri", color="1F3864")
ws["A2"] = f"Period: 2022–2023  |  Generated: {datetime.now().strftime('%Y-%m-%d')}"
ws["A2"].font = Font(italic=True, size=9, color="808080")
apply_header_style(ws, 4, len(category_export.columns))
# Currency formatting for revenue columns (cols 3–8 in this layout)
format_currency_cols(ws, [3, 4, 5, 6, 7], 5, 5 + len(category_export))
format_percentage_cols(ws, [8, 9], 5, 5 + len(category_export))
auto_fit_columns(ws)

wb.save(output_path)
print(f"Report saved to: {output_path}")

For a comprehensive deep dive into the full range of Excel formatting options available through openpyxl, including conditional formatting, charts embedded in workbooks, and named styles, see automating Excel reports with pandas and openpyxl.

---

Step 8: Making the Pipeline Reusable and Parameterized

A report you run once is a deliverable. A report you can run every month with a single command is an asset. Let's restructure the entire pipeline into a parameterized function.

from pathlib import Path

def run_profitability_report(
    transactions_path: str,
    products_path: str,
    territories_path: str,
    output_path: str,
    filter_year: int = None,
    filter_regions: list = None,
    min_revenue_threshold: float = 0.0
):
    """
    Full profitability report pipeline.
    
    Parameters
    ----------
    transactions_path : str
        Path to transactions CSV or Excel file.
    products_path : str
        Path to product reference CSV or Excel file.
    territories_path : str
        Path to territory reference CSV or Excel file.
    output_path : str
        Destination path for the output Excel workbook.
    filter_year : int, optional
        If provided, restrict analysis to this year only.
    filter_regions : list, optional
        If provided, restrict analysis to these regions.
    min_revenue_threshold : float
        Exclude transactions below this net_revenue value.
    
    Returns
    -------
    dict
        Summary statistics for logging/monitoring.
    """
    print(f"[{datetime.now():%H:%M:%S}] Loading source data...")
    
    # ── EXTRACT ──────────────────────────────────────────────────────
    def load_file(path):
        p = Path(path)
        if p.suffix == ".csv":
            return pd.read_csv(path)
        elif p.suffix in (".xlsx", ".xls"):
            return pd.read_excel(path)
        else:
            raise ValueError(f"Unsupported file format: {p.suffix}")
    
    tx = load_file(transactions_path)
    prod = load_file(products_path)
    terr = load_file(territories_path)
    
    print(f"  Loaded {len(tx):,} transactions")
    
    # ── TRANSFORM ─────────────────────────────────────────────────────
    # Parse dates and derive time dimensions
    tx["order_date"] = pd.to_datetime(tx["order_date"])
    tx["year"] = tx["order_date"].dt.year
    tx["quarter"] = tx["order_date"].dt.quarter
    tx["year_quarter"] = tx["order_date"].dt.to_period("Q").astype(str)
    
    # Apply filters
    if filter_year:
        tx = tx[tx["year"] == filter_year]
        print(f"  Filtered to year {filter_year}: {len(tx):,} transactions")
    
    # Handle missing territories
    tx["territory_id"] = tx["territory_id"].fillna("UNKNOWN")
    if "UNKNOWN" not in terr["territory_id"].values:
        terr = pd.concat([terr, pd.DataFrame([{
            "territory_id": "UNKNOWN",
            "territory_name": "Unassigned",
            "region": "Unassigned",
            "country": "Unknown"
        }])], ignore_index=True)
    
    # Joins
    enriched = (
        tx
        .merge(prod[["product_id","product_name","category","subcategory","unit_cost"]],
               on="product_id", how="left", validate="m:1")
        .merge(terr[["territory_id","territory_name","region","country"]],
               on="territory_id", how="left", validate="m:1")
    )
    
    if filter_regions:
        enriched = enriched[enriched["region"].isin(filter_regions)]
        print(f"  Filtered to regions {filter_regions}: {len(enriched):,} rows")
    
    # Metric engineering
    enriched["gross_revenue"] = (enriched["quantity"] * enriched["unit_price"]).round(2)
    enriched["net_revenue"] = (
        enriched["quantity"] * enriched["unit_price"] * (1 - enriched["discount_pct"])
    ).round(2)
    enriched["cogs"] = (enriched["quantity"] * enriched["unit_cost"]).round(2)
    enriched["gross_profit"] = (enriched["net_revenue"] - enriched["cogs"]).round(2)
    enriched["discount_amount"] = (enriched["gross_revenue"] - enriched["net_revenue"]).round(2)
    enriched["gross_margin_pct"] = np.where(
        enriched["net_revenue"] > 0,
        (enriched["gross_profit"] / enriched["net_revenue"] * 100).round(2), 0.0
    )
    
    # Apply revenue floor
    if min_revenue_threshold > 0:
        enriched = enriched[enriched["net_revenue"] >= min_revenue_threshold]
    
    # ── AGGREGATE ────────────────────────────────────────────────────
    def agg_profitability(df, groupby_cols):
        result = df.groupby(groupby_cols).agg(
            orders=("order_id", "count"),
            total_quantity=("quantity", "sum"),
            gross_revenue=("gross_revenue", "sum"),
            discount_amount=("discount_amount", "sum"),
            net_revenue=("net_revenue", "sum"),
            cogs=("cogs", "sum"),
            gross_profit=("gross_profit", "sum"),
        ).reset_index()
        result["gross_margin_pct"] = (
            result["gross_profit"] / result["net_revenue"] * 100
        ).round(2)
        result["revenue_share_pct"] = (
            result["net_revenue"] / result["net_revenue"].sum() * 100
        ).round(2)
        return result.sort_values("gross_profit", ascending=False)
    
    cat_summary = agg_profitability(enriched, ["category"])
    region_summary = agg_profitability(enriched, ["region"])
    subcat_summary = agg_profitability(enriched, ["category", "subcategory"])
    qtr_summary = agg_profitability(enriched, ["year", "quarter", "year_quarter"])
    qtr_summary = qtr_summary.sort_values(["year", "quarter"])
    qtr_summary["revenue_qoq_pct"] = qtr_summary["net_revenue"].pct_change().mul(100).round(2)
    
    cat_region = agg_profitability(enriched, ["category", "region"])
    cat_quarter = agg_profitability(enriched, ["category", "year_quarter"])
    cat_quarter = cat_quarter.sort_values(["category", "year_quarter"])
    
    # Pivot tables
    rev_pivot = enriched.pivot_table(
        index="category", columns="region",
        values="net_revenue", aggfunc="sum",
        margins=True, margins_name="TOTAL", fill_value=0
    ).round(2)
    
    margin_pivot = cat_region.pivot_table(
        index="category", columns="region",
        values="gross_margin_pct", aggfunc="mean", fill_value=0
    ).round(2)
    
    rev_trend = cat_quarter.pivot_table(
        index="category", columns="year_quarter",
        values="net_revenue", aggfunc="sum", fill_value=0
    ).round(2)
    rev_trend.loc["TOTAL"] = rev_trend.sum()
    
    # ── LOAD (Export) ─────────────────────────────────────────────────
    print(f"[{datetime.now():%H:%M:%S}] Writing Excel workbook...")
    
    period_label = f"Year {filter_year}" if filter_year else "Full Period"
    region_label = f" | Regions: {', '.join(filter_regions)}" if filter_regions else ""
    
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        cat_summary.to_excel(writer, sheet_name="By Category", index=False, startrow=3)
        region_summary.to_excel(writer, sheet_name="By Region", index=False, startrow=3)
        subcat_summary.to_excel(writer, sheet_name="By Subcategory", index=False, startrow=3)
        qtr_summary.to_excel(writer, sheet_name="Quarterly Trend", index=False, startrow=3)
        cat_region.to_excel(writer, sheet_name="Cat x Region", index=False, startrow=3)
        rev_pivot.to_excel(writer, sheet_name="Revenue Pivot")
        margin_pivot.to_excel(writer, sheet_name="Margin Pivot")
        rev_trend.to_excel(writer, sheet_name="Revenue Trend")
        enriched.sort_values("order_date").to_excel(
            writer, sheet_name="Transaction Detail", index=False, startrow=3
        )
        
        # Add title rows to each sheet
        wb = writer.book
        for sheet_name, title in [
            ("By Category", f"Profitability by Category — {period_label}{region_label}"),
            ("By Region", f"Profitability by Region — {period_label}"),
            ("By Subcategory", f"Profitability by Subcategory — {period_label}"),
            ("Quarterly Trend", f"Quarterly Revenue & Margin Trend"),
            ("Cat x Region", f"Category × Region Detail — {period_label}"),
        ]:
            ws = wb[sheet_name]
            ws["A1"] = title
            ws["A1"].font = Font(bold=True, size=13, name="Calibri", color="1F3864")
            ws["A2"] = f"Generated: {datetime.now().strftime('%Y-%m-%d %H:%M')}  |  Wicked Smart Data Report Engine"
            ws["A2"].font = Font(italic=True, size=8, color="999999")
    
    # ── RETURN SUMMARY ───────────────────────────────────────────────
    summary_stats = {
        "total_transactions": len(enriched),
        "total_net_revenue": enriched["net_revenue"].sum(),
        "total_gross_profit": enriched["gross_profit"].sum(),
        "overall_margin_pct": (enriched["gross_profit"].sum() / enriched["net_revenue"].sum() * 100),
        "output_path": output_path,
    }
    
    print(f"[{datetime.now():%H:%M:%S}] Complete.")
    print(f"  Transactions analyzed: {summary_stats['total_transactions']:,}")
    print(f"  Net Revenue:           ${summary_stats['total_net_revenue']:,.2f}")
    print(f"  Gross Profit:          ${summary_stats['total_gross_profit']:,.2f}")
    print(f"  Margin:                {summary_stats['overall_margin_pct']:.1f}%")
    print(f"  Output:                {output_path}")
    
    return summary_stats

# ── Run it
stats = run_profitability_report(
    transactions_path="transactions.csv",
    products_path="products.csv",
    territories_path="territories.csv",
    output_path="profitability_report_2023.xlsx",
    filter_year=2023
)

This structure — extract, transform, aggregate, load — is the foundation of any data pipeline. For more on structuring reusable analytical workflows and organizing code into modules, see building a reusable ETL pipeline in pandas.

---

Step 9: Performance Considerations at Scale

The pipeline above handles 15,000 rows comfortably in under a second. But when you're dealing with 5 million transaction rows — not unusual in a two-year sales history for a mid-size company — a few adjustments make a dramatic difference.

Use efficient dtypes from the start

# After loading, downcast numeric columns and use categoricals
def optimize_dtypes(df):
    # Categorical for low-cardinality string columns
    for col in ["product_id", "territory_id", "category", "region", 
                "subcategory", "year_quarter"]:
        if col in df.columns:
            df[col] = df[col].astype("category")
    
    # Downcast integers
    for col in ["quantity", "year", "quarter", "month"]:
        if col in df.columns:
            df[col] = pd.to_numeric(df[col], downcast="integer")
    
    # Downcast floats
    for col in ["unit_price", "unit_cost", "discount_pct",
                "gross_revenue", "net_revenue", "cogs", "gross_profit"]:
        if col in df.columns:
            df[col] = pd.to_numeric(df[col], downcast="float")
    
    return df

enriched = optimize_dtypes(enriched)
print(enriched.dtypes)
print(f"Memory usage: {enriched.memory_usage(deep=True).sum() / 1e6:.1f} MB")

Casting product_id, category, and region to category dtype typically cuts memory 60-80% for those columns because pandas stores unique values once and uses integer codes internally. On a 5M-row DataFrame with 20 columns, this can drop memory from 3GB to under 800MB.

Avoid row-by-row operations in the metric engineering step

Everything we did in Step 4 is already vectorized — we're operating on entire Series at once. That's the correct pattern. The thing to avoid is apply() with a Python function for row-level calculations. If you're tempted to write enriched.apply(lambda row: row["quantity"] * row["unit_price"], axis=1), step back and find the vectorized equivalent instead.

For a comprehensive treatment of vectorization patterns, see writing fast pandas code: vectorization instead of apply and loops.

Chunked processing for very large transaction files

If your transactions file genuinely can't fit in memory, process it in chunks:

def process_chunked(filepath, chunksize=100_000):
    """Process a large transaction file in chunks, aggregating as we go."""
    chunk_results = []
    
    for chunk in pd.read_csv(filepath, chunksize=chunksize, 
                              parse_dates=["order_date"]):
        # Apply all transformations and metric engineering
        chunk = (
            chunk
            .merge(products[["product_id","product_name","category","unit_cost"]],
                   on="product_id", how="left")
            .merge(territories[["territory_id","region"]], 
                   on="territory_id", how="left")
        )
        chunk["year_quarter"] = chunk["order_date"].dt.to_period("Q").astype(str)
        chunk["net_revenue"] = (
            chunk["quantity"] * chunk["unit_price"] * (1 - chunk["discount_pct"])
        )
        chunk["cogs"] = chunk["quantity"] * chunk["unit_cost"]
        chunk["gross_profit"] = chunk["net_revenue"] - chunk["cogs"]
        
        # Aggregate this chunk down to the dimensions we care about
        agg_chunk = chunk.groupby(["category", "region", "year_quarter"]).agg(
            net_revenue=("net_revenue", "sum"),
            cogs=("cogs", "sum"),
            gross_profit=("gross_profit", "sum"),
            orders=("order_id", "count"),
        ).reset_index()
        
        chunk_results.append(agg_chunk)
    
    # Combine all chunks and do a final aggregation
    combined = pd.concat(chunk_results, ignore_index=True)
    final = combined.groupby(["category", "region", "year_quarter"]).agg(
        net_revenue=("net_revenue", "sum"),
        cogs=("cogs", "sum"),
        gross_profit=("gross_profit", "sum"),
        orders=("orders", "sum"),
    ).reset_index()
    
    return final

This pattern — aggregate-in-chunks then aggregate-the-aggregates — works because sum is associative. You can't apply it to calculations that require the full distribution (like median), but for totals and derived ratios it produces exact results. For more on this pattern and when to consider alternatives like Polars, see handling large datasets in Python: chunked reading, efficient dtypes, and when to use Polars.

---

Hands-On Exercise

Apply what you've built to a variant scenario that pushes your understanding:

Scenario: Your finance team wants an additional view that was not in the original report: a profitability summary by salesperson, showing each salesperson's total net revenue, gross profit, margin percentage, number of unique customers served, and their best-performing product category by gross profit. Additionally, they want to flag any salesperson whose average discount rate exceeds 12% as a "High Discounter."

Tasks:

1. Add salesperson_id to the enriched DataFrame's groupby, and build a salesperson-level summary using multiple aggregation functions in a single .agg() call. For unique customers, use pd.Series.nunique.

2. Calculate the weighted margin correctly at the salesperson level (sum of GP / sum of revenue), not as a mean of row-level margins.

3. Find each salesperson's best-performing category. This requires a second groupby on ["salesperson_id", "category"], finding the max gross_profit per salesperson, and then merging it back. Alternatively, use transform("idxmax") to locate the row index of the best category per salesperson.

4. Add a "High Discounter" boolean flag using np.where or a simple comparison.

5. Export this as a new sheet in the workbook named "Salesperson Summary", sorted by gross profit descending.

Hint for the best category:

cat_by_sp = enriched.groupby(["salesperson_id", "category"])["gross_profit"].sum().reset_index()
best_cat = cat_by_sp.loc[cat_by_sp.groupby("salesperson_id")["gross_profit"].idxmax()]
best_cat = best_cat.rename(columns={"category": "best_category", "gross_profit": "best_cat_gp"})

---

Common Mistakes & Troubleshooting

Problem: Row count increases after merging

Symptom: len(enriched) > len(transactions) after a merge.

Cause: The dimension table has duplicate values on the join key. For example, a product appears twice in products.csv because someone added an updated record without removing the old one.

Fix: Before merging, check for duplicates: products.duplicated("product_id").sum(). Then either deduplicate the reference table with .drop_duplicates("product_id", keep="last") or use validate="m:1" in the merge call to fail loudly.

Problem: Revenue totals don't match between the detail and the summary

Symptom: category_summary["net_revenue"].sum() doesn't equal enriched["net_revenue"].sum().

Cause: A filter or groupby condition silently excluded rows — perhaps NaN values in the category column caused those rows to be dropped from the groupby.

Fix: Check enriched["category"].isnull().sum(). If it's nonzero, rows with null category were dropped. Either fill them (enriched["category"].fillna("Unknown")) or handle them before grouping.

Problem: Margin percentages look absurd (e.g., 450%)

Symptom: Some categories or regions show margins above 100%.

Cause: The unit_cost didn't join correctly and those rows have NaN cost, causing cogs to be 0 and gross_profit to equal net_revenue.

Fix: After the product join, check enriched["unit_cost"].isnull().sum(). Any nulls here mean the product_id in transactions didn't match anything in the products table. Add the validate="m:1" check and also add: assert enriched["unit_cost"].isnull().sum() == 0, "Unmatched products!".

Problem: The pivot table has way too many columns because region values include NaN

Cause: The territory join left some rows with NaN region, and pivot_table creates a column for NaN.

Fix: Either fill the region column before pivoting (enriched["region"].fillna("Unassigned")), or pass observed=True in the groupby if using categorical dtypes.

Problem: The Excel file opens but all the number columns show as text

Cause: When writing floats that were rounded with .round(2), sometimes the values end up as Python float but the cell format is General or Text, especially if you mixed string and numeric types in the same column.

Fix: After loading with load_workbook, explicitly cast numeric cells. Better: ensure columns are clean float or int dtype before writing, and apply number_format explicitly using openpyxl after writing.

Problem: Running the pipeline monthly overwrites last month's report

Cause: You're using a hardcoded output filename.

Fix: Append a timestamp to the filename:

from datetime import datetime
output_path = f"profitability_report_{datetime.now():%Y%m%d_%H%M}.xlsx"

Or better, organize outputs into dated folders:

from pathlib import Path
output_dir = Path("reports") / datetime.now().strftime("%Y-%m")
output_dir.mkdir(parents=True, exist_ok=True)
output_path = output_dir / "profitability_report.xlsx"

---

Summary & Next Steps

Let's take stock of what you built. Starting from three raw source files, you:

• Profiled and validated each source before touching it, catching referential integrity issues before they could corrupt results
• Joined a transaction fact table to two dimension tables using validate="m:1" to enforce data quality at join time
• Engineered correct financial metrics — gross revenue, net revenue after discounts, COGS, gross profit, and weighted margin percentage — using fully vectorized operations
• Aggregated across five different dimensional views: category, region, time period, category × region, and category × quarter trend
• Built a parameterized, reusable pipeline function that accepts filter parameters and returns summary statistics alongside the Excel output
• Handled the real-world issues: missing join keys, NaN categories, weighted vs. simple average margins
• Applied performance patterns for larger datasets including categorical dtype optimization and chunk-based processing

The report you've built is genuinely production-ready. A finance analyst could run this every month against updated source files, get a consistently formatted workbook, and spend their time interpreting results rather than reformatting pivot tables.

Where to go from here:

• Automate the monthly run without manual intervention by scheduling the script as a cron job or Windows Task — see building and automating recurring reports with pandas for the full scheduling workflow.

• Add period-over-period comparisons to the quarterly trend sheet, calculating not just absolute metrics but the percentage change between Q3 and Q4, or between 2022 and 2023 for the same quarter. The patterns in comparing periods and calculating month-over-month, year-over-year, and rolling changes in pandas apply directly.

• Add ranking columns to the salesperson and product views — who's in the top 10% of margin performers? Who's in the bottom quartile? The window function patterns covered in ranking and window calculations in pandas let you add these without leaving the pandas ecosystem.

• Add a reconciliation layer that verifies the report totals against a separate source of truth (like a finance system export). The pattern for that is laid out in auditing and reconciling data across sources in pandas.

The skills you've applied here — joining, aggregating, profiling, metric engineering, and exporting — are the core of practically every business reporting task a data professional encounters. You've now got a solid, tested pattern to adapt.