GroupBy and Aggregations

An aggregation is only as trustworthy as the rows entering it. Before summing revenue, define which statuses count, how duplicates are handled, what missing prices mean, and whether product enrichment preserved the order grain.

Problem

You need monthly completed-order revenue by product category, plus order count, units, and average order value. The order file contains duplicates, missing numeric fields, and one product reference that does not exist in the catalog.

Datasets used

Download the Orders Dataset and Products Dataset:

• orders.csv
• products.csv

Orders provide transactions; products provide the category. Keeping them separate mirrors a common fact-and-dimension workflow.

Code: prepare a validated input

from pathlib import Path

import pandas as pd

data = Path("data")
orders = pd.read_csv(data / "orders.csv", parse_dates=["order_date"])
products = pd.read_csv(data / "products.csv")

orders["quantity"] = pd.to_numeric(orders["quantity"], errors="coerce")
orders["unit_price"] = pd.to_numeric(orders["unit_price"], errors="coerce")

clean_orders = orders.drop_duplicates().copy()
completed = clean_orders.loc[clean_orders["status"].eq("Completed")].copy()
completed["revenue"] = completed["quantity"] * completed["unit_price"]
completed["order_month"] = completed["order_date"].dt.to_period("M").astype(str)

This example removes exact duplicate rows because they are known teaching duplicates. It does not deduplicate on order_id alone without examining conflicts. It also leaves unknown revenue missing instead of replacing it with zero.

Code: enrich, validate, and aggregate

enriched = completed.merge(
    products[["product_id", "category"]],
    on="product_id",
    how="left",
    validate="many_to_one",
    indicator=True,
)

unmatched = enriched.loc[enriched["_merge"].eq("left_only")]
print("Unmatched completed product rows:", len(unmatched))

summary = (
    enriched.loc[enriched["_merge"].eq("both")]
    .groupby(["order_month", "category"], as_index=False, dropna=False)
    .agg(
        order_count=("order_id", "nunique"),
        units=("quantity", "sum"),
        revenue=("revenue", "sum"),
        average_order_value=("revenue", "mean"),
    )
    .sort_values(["order_month", "revenue"], ascending=[True, False])
)

summary[["revenue", "average_order_value"]] = summary[
    ["revenue", "average_order_value"]
].round(2)
print(summary.head(10))

validate="many_to_one" asserts that each product key appears at most once on the product side. Without it, an accidental duplicate product could multiply orders and inflate revenue. The merge indicator prevents unmatched transactions from disappearing unnoticed.

Output

The output has one row per month and category:

order_month	category	order_count	units	revenue	average_order_value
2025-01	Electronics	…	…	…	…
2025-01	Office	…	…	…	…
2025-01	Accessories	…	…	…	…

Ellipses are intentional here: run the deterministic files to produce the complete values. A tutorial should show the shape and meaning without copying hundreds of result rows.

Explanation: grain and null behavior

Named aggregation makes output names and source operations explicit. nunique counts order identifiers, while size would count rows. They differ when duplicates or multi-line orders exist. This dataset has one row per order record, but production order systems often have an order-header and order-line grain.

Most aggregation functions skip missing numeric values. That behavior can be convenient and dangerous. A revenue sum may look plausible even when some completed orders lack price or quantity. Measure completeness alongside the result:

quality = completed.agg(
    rows=("order_id", "size"),
    missing_quantity=("quantity", lambda values: values.isna().sum()),
    missing_price=("unit_price", lambda values: values.isna().sum()),
    missing_revenue=("revenue", lambda values: values.isna().sum()),
)
print(quality)

For categorical groupers, decide whether unobserved categories should appear. Pandas behavior and defaults can change across versions, so set relevant options deliberately when stable output shape matters.

Common mistakes

• Summing before removing known duplicate input records.
• Grouping transaction prices without first calculating quantity times price.
• Using row count when the metric requires distinct orders.
• Allowing a many-to-many join to multiply measures.
• Dropping unmatched keys through an inner join without reporting them.
• Treating skipped nulls as if every source value were complete.
• Rounding inputs before aggregation instead of presentation values afterward.

Try it yourself

Build a status summary containing row count, distinct customers, units, revenue, and missing revenue. Then create category revenue for all non-cancelled orders and compare it with completed-only revenue. Explain why returned and pending transactions change interpretation.

Finally, reconcile the grand total from the grouped output to the same filtered rows before grouping. If the totals differ, investigate the join, null handling, and grain rather than forcing the numbers to match.

Production notes

Publish aggregation definitions alongside the output. “Revenue” needs a status rule, currency assumption, tax treatment, return policy, and effective time. Two technically correct groupby expressions can answer different business questions. Metric names should make important distinctions visible instead of relying on notebook context that disappears later.

Reconciliation is strongest when it checks several dimensions: source rows, distinct orders, sum of quantity, sum of revenue, unmatched keys, and missing measures. Store those controls with the run. If the transformation is rerun, identical inputs and code should produce identical controls.

Memory use can grow during grouping because pandas constructs intermediate keys and results. Reduce columns before the operation, use appropriate categorical types for repeated dimensions, and test with representative cardinality. Chunking is possible for additive aggregates, but averages require carrying both numerator and denominator, and distinct counts are harder to combine exactly. When the data no longer fits comfortably or needs distributed execution, preserve the metric contract while moving the computation to a more suitable engine.

Before publishing the summary, verify its schema as well as its totals. Column order, numeric types, precision, and category spelling are part of the consumer contract. Write a small test using a known subset whose answer can be calculated by hand. That fixture catches changes in filtering and null behavior more clearly than a large snapshot whose numbers reviewers cannot independently explain.