Attach all five sample files (sales.csv, sales_log.csv, products.csv, regions.csv, messy.csv) with Add files. Paste each block as the whole editor — later blocks repeat the load so they still run alone.

Goal

Produce sales_report.csv with region, category, monthly revenue, and a margin column. Then print a city × product pivot.

1. Load and parse

import os

print("uploads:", os.listdir("/uploads"))

log = pd.read_csv("sales_log.csv", parse_dates=["date"])
products = pd.read_csv("products.csv")
regions = pd.read_csv("regions.csv")
print(log.head())
print(log.dtypes)
print("rows:", len(log))

2. Revenue and margin

log = pd.read_csv("sales_log.csv", parse_dates=["date"])
products = pd.read_csv("products.csv")
regions = pd.read_csv("regions.csv")
sales = log.merge(products, on="product", how="left", validate="many_to_one")
sales = sales.merge(regions, on="city", how="left", validate="many_to_one")
sales["revenue"] = sales["units"] * sales["price"]
sales["cost_total"] = sales["units"] * sales["cost"]
sales["margin"] = sales["revenue"] - sales["cost_total"]
print(sales.head())
print()
print("merge check — any NA region or category?")
print(sales[["region", "category"]].isna().sum())

validate="many_to_one" errors if a lookup key is duplicated — better than a silent row explosion.

3. Monthly totals by region

log = pd.read_csv("sales_log.csv", parse_dates=["date"])
products = pd.read_csv("products.csv")
regions = pd.read_csv("regions.csv")
sales = log.merge(products, on="product", how="left", validate="many_to_one")
sales = sales.merge(regions, on="city", how="left", validate="many_to_one")
sales["revenue"] = sales["units"] * sales["price"]
sales["cost_total"] = sales["units"] * sales["cost"]
sales["margin"] = sales["revenue"] - sales["cost_total"]
sales["month"] = sales["date"].dt.to_period("M").astype(str)
monthly = (
    sales.groupby(["month", "region"], as_index=False)
    .agg(
        units=("units", "sum"),
        revenue=("revenue", "sum"),
        margin=("margin", "sum"),
        orders=("units", "count"),
    )
    .sort_values(["month", "region"])
)
print(monthly)

4. Pivot: city × product

log = pd.read_csv("sales_log.csv", parse_dates=["date"])
sales = log.copy()
sales["revenue"] = sales["units"] * sales["price"]
grid = sales.pivot_table(
    index="city",
    columns="product",
    values="revenue",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="total",
)
print(grid)

5. Clean `messy.csv`

messy = pd.read_csv("messy.csv")
messy["name"] = messy["name"].str.strip().str.title()
messy["city"] = messy["city"].str.strip()
messy["joined"] = pd.to_datetime(messy["joined"], errors="coerce", format="mixed")
messy["score"] = pd.to_numeric(messy["score"], errors="coerce")
messy = messy.drop_duplicates()
print(messy)
print()
print(messy.isna().sum())

6. Export the report

import os

log = pd.read_csv("sales_log.csv", parse_dates=["date"])
products = pd.read_csv("products.csv")
regions = pd.read_csv("regions.csv")
sales = log.merge(products, on="product", how="left", validate="many_to_one")
sales = sales.merge(regions, on="city", how="left", validate="many_to_one")
sales["revenue"] = sales["units"] * sales["price"]
sales["cost_total"] = sales["units"] * sales["cost"]
sales["margin"] = sales["revenue"] - sales["cost_total"]
sales["month"] = sales["date"].dt.to_period("M").astype(str)
monthly = (
    sales.groupby(["month", "region"], as_index=False)
    .agg(
        units=("units", "sum"),
        revenue=("revenue", "sum"),
        margin=("margin", "sum"),
        orders=("units", "count"),
    )
    .sort_values(["month", "region"])
)
grid = sales.pivot_table(
    index="city",
    columns="product",
    values="revenue",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="total",
)
monthly.to_csv("sales_report.csv", index=False)
grid.to_csv("sales_pivot.csv")
print("wrote sales_report.csv and sales_pivot.csv")
print(os.listdir("/uploads"))

Download both chips. You now have a repeatable path: read → inspect → clean → join → group → pivot → write.

Extra drills

log = pd.read_csv("sales_log.csv", parse_dates=["date"])
products = pd.read_csv("products.csv")
regions = pd.read_csv("regions.csv")
sales = log.merge(products, on="product", how="left", validate="many_to_one")
sales = sales.merge(regions, on="city", how="left", validate="many_to_one")
sales["revenue"] = sales["units"] * sales["price"]
sales["cost_total"] = sales["units"] * sales["cost"]
sales["margin"] = sales["revenue"] - sales["cost_total"]

print("March margin by product")
print(sales.loc[sales["date"].dt.month == 3].groupby("product")["margin"].sum())
print()

nairobi = sales[sales["city"] == "Nairobi"]
print("Nairobi revenue share by product")
print((nairobi.groupby("product")["revenue"].sum() / nairobi["revenue"].sum()).round(3))
print()

sales["city_share"] = sales["revenue"] / sales.groupby("city")["revenue"].transform("sum")
print("shares should sum to 1 per city")
print(sales.groupby("city")["city_share"].sum())
print()

print("Hardware only, city x product")
print(
    sales[sales["category"] == "Hardware"].pivot_table(
        index="city", columns="product", values="revenue", aggfunc="sum", fill_value=0
    )
)

You should see

If a merge validation fails, print(products["product"].duplicated().sum()) and print(regions["city"].duplicated().sum()). Lookups must be unique on the key.