Combining tables with merge

Two checks that make joins safe

indicator=True adds a _merge column saying where each row came from: both, left_only or right_only. Count it and you know straight away whether anything failed to match:

import pandas as pd

def dataset(name):
    return pd.read_csv(f"https://api.gradient.test/v1/datasets/{name}.csv")

orders = dataset("orders").drop_duplicates()
catalog = dataset("catalog")

check = orders.merge(catalog[["sku", "cost"]], on="sku", how="left", indicator=True)
print(check["_merge"].value_counts())
print(check[check["_merge"] == "left_only"]["sku"].unique())

doubled = pd.concat([catalog, catalog[catalog["sku"] == "TE-101"]])
try:
    orders.merge(doubled, on="sku", how="left", validate="many_to_one")
except pd.errors.MergeError as err:
    print("Refused:", err)

The six unmatched lines are the staff test orders with SKU TEST-000, which isn't a product. The indicator found them without your knowing to look.

validate="many_to_one" promises that every key appears at most once on the right. If the catalog accidentally lists TE-101 twice, every TE-101 order line would match both copies and come out twice. That doubles its revenue without a single error message. With validate, pandas checks the promise and refuses to join. The other options are "one_to_one", "one_to_many" and "many_to_many".

Same names in both tables

The catalog and the product-lines table both have columns called name and category. Merge them and pandas can't keep both under the same name, so it adds suffixes: name_x and name_y. Code that later asks for name breaks, or worse, picks up the wrong one. Avoid it by choosing columns and renaming before the join:

named = lines.rename(columns={"name": "line"})[["line_id", "line"]]
catalog = catalog.merge(named, on="line_id", how="left", validate="many_to_one")

Now the catalog has a clear line column ("Ridgeline", "Ember" and so on) next to the product's own name. Bring only the columns you need into a join. It keeps the result readable and avoids clashes.

When the key has different names in the two tables, say sku in one and product_code in the other, use left_on="sku", right_on="product_code" instead of on.

Stacking tables with concat

merge puts tables side by side. pd.concat stacks tables that have the same columns on top of each other: this month's export under last month's, or one file per store into one table.

import pandas as pd

orders = pd.read_csv("https://api.gradient.test/v1/datasets/orders.csv")
y2024 = orders[orders["date"] < "2025"]
y2025 = orders[orders["date"] >= "2025"]
print(len(y2024), len(y2025))

both = pd.concat([y2024, y2025], ignore_index=True)
print(len(both), both.index[-1])

ignore_index=True numbers the stacked rows afresh, 0 to the end. Without it, each piece keeps its old row labels, and .loc can then match two rows at once. If the pieces' columns don't match exactly, concat still runs, but it fills the gaps with NaN. So check both.isna().sum() after stacking files from different sources.

import pandas as pd

def dataset(name):
    return pd.read_csv(f"https://api.gradient.test/v1/datasets/{name}.csv")

orders = dataset("orders").drop_duplicates()
orders = orders[orders["sku"] != "TEST-000"]
catalog, lines = dataset("catalog"), dataset("product-lines")

units = {}

Rates need the right denominator

Portland earned more than twice what Seattle did. Is Portland's store better? Not necessarily: more people walk into it. Totals mostly measure size. To compare performance, divide by what each store had to work with, here the number of visits. That number is the denominator, and it usually lives in another table, so a rate is a join followed by a division:

import pandas as pd

def dataset(name):
    return pd.read_csv(f"https://api.gradient.test/v1/datasets/{name}.csv")

orders = dataset("orders").drop_duplicates()
orders = orders[orders["sku"] != "TEST-000"].copy()
orders["revenue"] = orders["qty"] * orders["unit_price"]
stores = dataset("stores")

totals = orders.groupby("store", as_index=False)["revenue"].sum()
rates = totals.merge(stores, on="store", how="left", validate="one_to_one")
rates["per_visit"] = (rates["revenue"] / (rates["yearly_visits"] * 2)).round(2)
print(rates[["store", "revenue", "yearly_visits", "per_visit"]].sort_values("per_visit", ascending=False))

The orders cover two years and the visits are per year, so the denominator is visits × 2. Matching the time periods is part of choosing the right denominator. Per visit, Portland still leads, but Denver beats Seattle, and the web shop, the giant by total revenue, is last. Lots of people browse online without buying. The same total can tell opposite stories once it's divided by the right thing.

import pandas as pd

def dataset(name):
    return pd.read_csv(f"https://api.gradient.test/v1/datasets/{name}.csv")

orders = dataset("orders").drop_duplicates()
orders = orders[orders["sku"] != "TEST-000"].copy()
catalog, lines = dataset("catalog"), dataset("product-lines")
orders["revenue"] = orders["qty"] * orders["unit_price"]

before = len(orders)
full = orders.merge(catalog[["sku", "cost", "line_id"]], on="sku", how="left", validate="many_to_one")
full = full.merge(lines.rename(columns={"name": "line"})[["line_id", "line"]], on="line_id", how="left", validate="many_to_one")
print(before, len(full), full["line"].isna().sum())

full["profit"] = full["revenue"] - full["qty"] * full["cost"]
report = full.groupby("line").agg(revenue=("revenue", "sum"), profit=("profit", "sum"))
report["margin_pct"] = (report["profit"] / report["revenue"] * 100).round(1)
print(report.sort_values("profit", ascending=False).round(1))