Pandas GroupBy and Merge: Build a Practical Sales Analysis

Python course module guide: Python libraries for data analysis

Pandas GroupBy and merge solve two common analysis tasks: summarizing transactions and combining related tables. A trustworthy result also requires key validation, reconciliation and explicit treatment of missing or duplicated records.

Project question and data model

Assume an orders file contains order ID, date, product ID, region, quantity and selling price. A product file contains product ID, category and unit cost. The analysis should calculate revenue and gross margin by region and category while proving that the join did not duplicate or lose transactions.

import pandas as pd

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

Use pandas Python programming examples if DataFrame selection, filtering and basic operations need review.

Inspect schema before calculations

required_orders = {
    "order_id", "order_date", "product_id",
    "region", "quantity", "unit_price",
}
required_products = {"product_id", "category", "unit_cost"}

missing_orders = required_orders - set(orders.columns)
missing_products = required_products - set(products.columns)
if missing_orders or missing_products:
    raise ValueError(
        f"Missing columns: orders={missing_orders}, products={missing_products}"
    )

Column existence is only the first check. Inspect data types, null counts, duplicate keys, negative quantities and unexpected category values. The data-cleaning guide for Excel, SQL and Python covers a broader workflow.

Prepare keys and numeric columns

orders["product_id"] = orders["product_id"].astype("string").str.strip()
products["product_id"] = products["product_id"].astype("string").str.strip()

for column in ["quantity", "unit_price"]:
    orders[column] = pd.to_numeric(orders[column], errors="coerce")
products["unit_cost"] = pd.to_numeric(products["unit_cost"], errors="coerce")

if products["product_id"].duplicated().any():
    raise ValueError("Product reference contains duplicate product IDs")

Do not automatically drop duplicate reference keys. A duplicate may indicate two conflicting costs or categories and can multiply order rows during a merge.

Merge with explicit validation

merged = orders.merge(
    products,
    how="left",
    on="product_id",
    validate="many_to_one",
    indicator=True,
)

unmatched = merged.loc[merged["_merge"] != "both", "product_id"].unique()
if len(unmatched):
    raise ValueError(f"Unknown product IDs: {unmatched[:10].tolist()}")

merged = merged.drop(columns="_merge")

validate="many_to_one" expresses that many orders may reference one product row. The indicator column makes unmatched keys visible. Be careful with null join keys: pandas can match null keys to each other differently from typical SQL expectations, so reject or handle them before merging.

Create business measures

merged["revenue"] = merged["quantity"] * merged["unit_price"]
merged["cost"] = merged["quantity"] * merged["unit_cost"]
merged["gross_margin"] = merged["revenue"] - merged["cost"]
merged["month"] = merged["order_date"].dt.to_period("M").astype(str)

Define each metric before reporting it. Gross margin here excludes shipping, discounts, tax and overhead. A label must not imply more than the calculation includes.

Summarize with named aggregation

summary = (
    merged.groupby(["month", "region", "category"], as_index=False)
    .agg(
        orders=("order_id", "nunique"),
        units=("quantity", "sum"),
        revenue=("revenue", "sum"),
        gross_margin=("gross_margin", "sum"),
    )
)

summary["margin_pct"] = (
    summary["gross_margin"] / summary["revenue"]
).where(summary["revenue"] != 0)

Named aggregation controls the output column names. Built-in GroupBy operations are generally clearer and faster than sending every group through a custom apply function.

Reconcile before exporting

source_revenue = merged["revenue"].sum()
summary_revenue = summary["revenue"].sum()

if not abs(source_revenue - summary_revenue) < 0.01:
    raise AssertionError("Revenue reconciliation failed")
if len(merged) != len(orders):
    raise AssertionError("The merge changed the order row count")

Reconciliation is what separates a plausible chart from trustworthy analysis. Also compare distinct order IDs, total quantity and unmatched-key counts. Export a separate exception file when invalid rows require business review.

Connect API collection to analysis

When orders arrive from an API rather than CSV, collect them with pagination, timeouts and stable identifiers. The Python API pagination and retries guide prepares a reliable input pipeline for this analysis.

Common GroupBy and merge mistakes

• Joining without checking reference-key uniqueness.
• Using an inner join that silently discards unmatched transactions.
• Calculating metrics before fixing numeric types and nulls.
• Using row count when the metric requires distinct orders.
• Dividing by zero when calculating percentages.
• Skipping reconciliation because the output looks reasonable.

Portfolio deliverables

Publish the cleaning assumptions, schema checks, merge validation, metric definitions, reconciliation results and final summary. Add one chart only after the table is correct. Explain one decision the analysis supports, such as investigating a low-margin category or an unmatched product feed.

Pandas GroupBy and merge FAQs

Why did my merge increase row count?

One or both join keys are probably duplicated. Inspect key uniqueness and use the validate argument to state the expected relationship.

Should I use apply for every grouped calculation?

No. Prefer built-in aggregations and transformations when they express the result. They are generally clearer and more efficient.

How should unmatched keys be handled?

Keep them visible with an indicator, measure their impact and decide with the data owner whether to reject, repair or report them.

Official reference: pandas GroupBy user guide.