"""Lesson 5: pandas in depth - merge, groupby with several aggregations, pivot_table, cleaning."""
import json
from pathlib import Path
import pandas as pd
HERE = Path(__file__).resolve().parent
sales = pd.read_csv(HERE / "sales.csv")
sales["date"] = pd.to_datetime(sales["date"], format="%Y-%m-%d", errors="coerce")
sales["qty"] = pd.to_numeric(sales["qty"], errors="coerce")
print("Bad rows dropped:", sales[["date", "qty"]].isna().any(axis=1).sum())
sales = sales.dropna(subset=["date", "qty"])
cust = pd.read_csv(HERE / "customers.csv")
prod = pd.DataFrame(json.load(open(HERE / "products.json"))).T.rename_axis("code").reset_index()
prod = prod.rename(columns={"name": "product"})      # customers also have "name": rename before merging
df = sales.merge(cust, on="customer_id", how="left").merge(prod, on="code", how="left")
df["amount"] = df["qty"] * df["price"].astype(float)
summary = df.groupby("region").agg(invoices=("invoice", "count"), amount=("amount", "sum"), avg=("amount", "mean")).round(0)
print(summary.sort_values("amount", ascending=False).to_string())
df["month"] = df["date"].dt.strftime("%Y-%m")
pv = df.pivot_table(index="category", columns="month", values="amount", aggfunc="sum", fill_value=0)
print(pv.round(0).astype(int).to_string())
print("Top customer:", df.groupby("name")["amount"].sum().idxmax())