Revenue by category in pandas

The bitshop data lives in three tables: orders, order_items and products. Revenue sits on the line items, the category sits on the product, and the status sits on the order. You need all three.

Write revenue_by_category(orders, items, products). Each argument is a pandas DataFrame loaded from the matching bitshop CSV:

• orders: order_id, customer_id, order_date, status
• items: order_id, product_id, quantity, unit_price
• products: product_id, name, category, price

Return a DataFrame with exactly two columns, category and revenue:

• revenue is the sum of quantity * unit_price over line items whose order is not cancelled. Returned orders still count.
• Use unit_price from the line item, not the product’s list price.
• One row per category that has revenue. Leave out categories with no qualifying items.
• Sort by revenue descending, then category ascending. Don’t round.

>>> revenue_by_category(orders, items, products).head(2)
   category    revenue
0  Hardware  157421.34
1   Courses   58614.63

Approach

Filter, join, compute, group, sort.

import pandas as pd


def revenue_by_category(orders, items, products):
    live = orders[orders["status"] != "cancelled"][["order_id"]]
    df = items.merge(live, on="order_id").merge(products[["product_id", "category"]], on="product_id")
    df = df.assign(revenue=df["quantity"] * df["unit_price"])
    out = df.groupby("category", as_index=False)["revenue"].sum()
    out = out.sort_values(["revenue", "category"], ascending=[False, True])
    return out.reset_index(drop=True)[["category", "revenue"]]

Selecting [["order_id"]] before the join keeps customer_id, order_date and status out of the result. Selecting [["product_id", "category"]] keeps the product’s list price out, so there’s no chance of summing the wrong price column.

Complexity

The joins are hash joins: O(orders + items + products). Grouping is O(rows), and sorting the handful of categories costs nothing.

Why this matters

This is the same query as SELECT category, SUM(quantity * unit_price) ... GROUP BY category in SQL, and pandas runs it the same way: build a hash table on one side of each join, probe it with the other, then hash-aggregate. The status filter is a business rule, and it’s the part that gets lost when the same metric is computed in three dashboards three ways. Writing it once, as a tested function, is the cheap fix.