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", dtype={"order_id": "string", "product_id": "string"},
                     parse_dates=["order_date"])
products = pd.read_csv("products.csv", dtype={"product_id": "string"})

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")

for frame in (orders, products):
    key = frame["product_id"]
    if key.isna().any() or key.eq("").any():
        raise ValueError("Product IDs must be present and nonblank")

from math import isfinite

for frame, columns in [
    (orders, ["quantity", "unit_price"]),
    (products, ["unit_cost"]),
]:
    for column in columns:
        if frame[column].isna().any() or not frame[column].map(isfinite).all():
            raise ValueError(f"Invalid numeric values in {column}")

orders["order_date"] = pd.to_datetime(orders["order_date"], errors="coerce")
if orders["order_date"].isna().any() or orders["order_id"].isna().any():
    raise ValueError("Order dates and order IDs must be present and valid")

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,
                   dropna=False, observed=True)
    .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.

Prove the answer with a three-line sales example

Before processing a large file, create a small fixture whose totals you can calculate by hand. Order A has two lines: two units of product P1 at ₹100 and one unit of P2 at ₹300. Order B has three units of P1 at ₹100. P1 costs ₹60 per unit and belongs to Accessories; P2 costs ₹180 and belongs to Services. The fixture therefore contains three lines, two distinct orders, six units, ₹800 revenue, ₹480 cost and ₹320 gross margin.

If both orders belong to the same month and region, the Accessories summary has ₹500 revenue and ₹200 margin. Services has ₹300 revenue and ₹120 margin. Both margins are 40% for this particular fixture, but do not generalise that coincidence. For a combined margin percentage, divide combined margin by combined revenue rather than averaging row percentages.

Distinct order counts also require care. Order A appears in both categories, so adding the category-level distinct counts gives three while the overall dataset contains only two distinct orders. A metric can be correct within each group without being additive across groups. Explain that limitation alongside the summary.

Reject missing keys before they become convincing matches

The preparation block now rejects missing and blank product keys in both inputs. This closes a subtle gap: pandas can match null join keys to each other. A missing product on an order must not acquire a category or cost merely because a reference row also has a missing key. The merge relationship check establishes uniqueness; it does not establish that a key has a meaningful business identity.

Read identifiers as strings at import time. If a product code such as 0012 is first read as a number, later converting it to text cannot recover the lost leading zeros. Strip unwanted whitespace consistently, but avoid changing letter case or punctuation unless the identifier rules allow it. Keep an exception count so rejected inputs do not disappear from the story.

Keep missing groups visible

By default, GroupBy excludes rows with missing grouping keys. The example now explicitly keeps those groups and selects observed categories, avoiding reliance on changing categorical defaults. A missing region therefore remains visible for investigation instead of silently removing its sales from the summary. Missing dates are rejected before month creation because an unknown reporting period needs an explicit resolution.

Converting invalid numbers to missing values is a detection step, not a completed cleaning strategy. The added checks stop when quantities, prices or costs are missing or non-finite. Separately decide whether negative quantities represent legitimate returns. Do not replace every negative value with zero or remove it merely to make a chart look cleaner.

Run four controlled failure checks

First, duplicate the P1 reference row and confirm that the analysis rejects the invalid relationship rather than doubling revenue. Second, change one order’s product to an unknown code and confirm that the unmatched-key check identifies it. Third, remove a region and confirm that total revenue remains ₹800 while the missing-region group becomes visible. Fourth, replace a price with invalid text and confirm that validation stops before metrics are calculated.

Keep these outcomes with the normal fixture results. For recurring files, the Python CSV validation and reporting pipeline guide shows how to organise incoming-file checks and exception reports before grouping and merging. A repeatable input boundary makes it easier to explain whether a changed dashboard reflects new business activity or a changed source file.

Additional official documentation: pandas merge reference; pandas GroupBy reference.

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.