from pathlib import Path
import pandas as pd
import numpy as np
from _shared import load
ROOT = Path(__file__).resolve().parents[1]
OUT = ROOT / "outputs"
OUT.mkdir(exist_ok=True)
users = load("users")
orders = load("orders")
assert users["user_id"].is_unique
# 没有订单的用户仍然出现在用户分析中。
valid = orders.loc[orders["user_id"].notna()].copy()
joined = valid.merge(users, on="user_id", how="left", validate="many_to_one", indicator=True)
assert len(joined) == len(valid)
assert joined["_merge"].eq("both").all()
summary = valid.groupby("user_id").size().rename("order_count").reset_index()
per_user = users.merge(summary,on="user_id",how="left",validate="one_to_one")
per_user["order_count"] = per_user["order_count"].fillna(0).astype("int64")
assert len(per_user) == len(users)
left = pd.DataFrame({"k":[1,1,None],"left_value":[10,20,30]})
right = pd.DataFrame({"k":[1,1,None],"right_value":[5,6,7]})
demo = left.merge(right,on="k",how="inner")
assert len(demo) == 5  # 2×2 个普通键匹配，加 1 个空键匹配。
try:
    left.merge(right,on="k",validate="many_to_one")
except pd.errors.MergeError:
    print("重复键已被 validate 拦截")
else:
    raise AssertionError("应该检测出右表重复键")
sql_equal_join = left.dropna(subset=["k"]).merge(right.dropna(subset=["k"]),on="k")
assert len(sql_equal_join) == 4
per_user.to_csv(OUT / "pd_merge_users.csv",index=False)
print("非空订单连接行数:",len(joined))
print("每用户订单数:")
print(per_user[["user_id","order_count"]].to_string(index=False))
print("普通 pandas 连接 / SQL 等值语义:",len(demo),len(sql_equal_join))