#!/usr/bin/env python3 """Merge 表2(嘉锦明细) into 表1(车辆氢费明细), generate result + comparison report.""" import pandas as pd import numpy as np from datetime import datetime T1_PATH = "/Users/sylvawong/Desktop/车辆氢费明细_2026-06-06_嘉兴嘉锦亭桥北综合供能服务站.xlsx" T2_PATH = ( "/Users/sylvawong/Library/Containers/com.tencent.xinWeChat/Data/Documents/" "xwechat_files/wxid_l80gh7d3x7u012_385c/temp/drag/嘉锦 4月明细.xls" ) OUT_DATA = "/Users/sylvawong/Desktop/车辆氢费明细_嘉兴嘉锦亭_4月合并结果.xlsx" OUT_COMPARE = "/Users/sylvawong/Desktop/车辆氢费明细_嘉兴嘉锦亭_4月比对结果.xlsx" FILTER_YEAR = 2026 FILTER_MONTH = 4 def norm_date(val): return pd.to_datetime(val).strftime("%Y-%m-%d") def norm_plate(val): return str(val).strip().upper() def norm_kg(val): return round(float(val), 2) def make_key(date_val, plate_val, kg_val): return f"{norm_date(date_val)}|{norm_plate(plate_val)}|{norm_kg(kg_val)}" def fmt_datetime(val): ts = pd.to_datetime(val) if ts.hour == 0 and ts.minute == 0 and ts.second == 0: return ts.strftime("%Y-%m-%dT00:00") return ts.strftime("%Y-%m-%d %H:%M:%S") def parse_status(val): if pd.isna(val): return "未对账" s = str(val) if "已对账" in s: return "已对账" if "未对账" in s: return "未对账" return s def num_equal(a, b, tol=0.01): if pd.isna(a) and pd.isna(b): return True if pd.isna(a) or pd.isna(b): return False return abs(float(a) - float(b)) <= tol def str_equal(a, b): if pd.isna(a) and pd.isna(b): return True if pd.isna(a) or pd.isna(b): return False return str(a).strip() == str(b).strip() def load_table1(): return pd.read_excel(T1_PATH, sheet_name=0) def load_table2(): df = pd.read_excel(T2_PATH, sheet_name=0, header=1) df.columns = [ "日期", "车牌", "加气量kg", "公里数", "加氢站", "是否对账", "车架号", "成本单价", "成本金额", ] return df def filter_april(df, date_col): dt = pd.to_datetime(df[date_col]) mask = (dt.dt.year == FILTER_YEAR) & (dt.dt.month == FILTER_MONTH) return df.loc[mask].copy() def row_from_table2(r2, seq_no=None): dt = pd.to_datetime(r2["日期"]) return { "序号": seq_no, "年": int(dt.year), "月": int(dt.month), "加氢日期": norm_date(r2["日期"]), "加氢时间": fmt_datetime(r2["日期"]), "加氢站名称": r2["加氢站"], "客户名称": np.nan, "车牌号": norm_plate(r2["车牌"]), "加氢量(kg)": norm_kg(r2["加气量kg"]), "成本单价(元/kg)": float(r2["成本单价"]) if pd.notna(r2["成本单价"]) else np.nan, "成本总价(元)": float(r2["成本金额"]) if pd.notna(r2["成本金额"]) else np.nan, "加氢单价(元/kg)": 0, "加氢总价(元)": 0.0, "行驶里程(km)": float(r2["公里数"]) if pd.notna(r2["公里数"]) else np.nan, "备注": np.nan, "业务员": np.nan, "承担方式": np.nan, "状态": parse_status(r2["是否对账"]), "订单编号": np.nan, } def main(): df1_all = load_table1() df2_all = load_table2() df1 = filter_april(df1_all, "加氢日期") df2 = filter_april(df2_all, "日期") df2 = df2.copy() df2["_key"] = df2.apply( lambda r: make_key(r["日期"], r["车牌"], r["加气量kg"]), axis=1 ) df1["_key"] = df1.apply( lambda r: make_key(r["加氢日期"], r["车牌号"], r["加氢量(kg)"]), axis=1 ) df1_orig = df1.copy() t2_map = df2.set_index("_key", drop=False) keys1 = set(df1["_key"]) keys2 = set(df2["_key"]) matched_keys = keys1 & keys2 only_t2_keys = keys2 - keys1 only_t1_keys = keys1 - keys2 overwrite_rows = [] diff_rows = [] unchanged_matched = 0 for idx, row in df1.iterrows(): key = row["_key"] if key not in matched_keys: continue r2 = t2_map.loc[key] if isinstance(r2, pd.DataFrame): r2 = r2.iloc[0] before = row.copy() updates = { "加氢日期": norm_date(r2["日期"]), "加氢时间": fmt_datetime(r2["日期"]), "加氢站名称": r2["加氢站"], "车牌号": norm_plate(r2["车牌"]), "加氢量(kg)": norm_kg(r2["加气量kg"]), "成本单价(元/kg)": float(r2["成本单价"]) if pd.notna(r2["成本单价"]) else np.nan, "成本总价(元)": float(r2["成本金额"]) if pd.notna(r2["成本金额"]) else np.nan, "行驶里程(km)": float(r2["公里数"]) if pd.notna(r2["公里数"]) else np.nan, } changed_fields = [] for field, new_val in updates.items(): old_val = before[field] is_diff = False if field in ("成本单价(元/kg)", "成本总价(元)", "加氢量(kg)", "行驶里程(km)"): is_diff = not num_equal(old_val, new_val) else: is_diff = not str_equal(old_val, new_val) if is_diff: changed_fields.append(field) diff_rows.append( { "匹配键": key, "订单编号": before.get("订单编号"), "差异字段": field, "表1原值": old_val, "表2正确值": new_val, } ) df1.at[idx, field] = new_val overwrite_rows.append( { "操作": "覆盖更新", "匹配键": key, "加氢日期": updates["加氢日期"], "车牌号": updates["车牌号"], "加氢量(kg)": updates["加氢量(kg)"], "表1原客户名称": before["客户名称"], "表1原加氢站名称": before["加氢站名称"], "表1原成本总价(元)": before["成本总价(元)"], "表1原行驶里程(km)": before["行驶里程(km)"], "表2加氢站": r2["加氢站"], "表2成本金额": r2["成本金额"], "表2公里数": r2["公里数"], "变更字段数": len(changed_fields), "变更字段": "、".join(changed_fields) if changed_fields else "无", "订单编号": before.get("订单编号"), } ) if not changed_fields: unchanged_matched += 1 # 表2有、表1无 -> 新增（不生成订单编号） new_rows = [] next_seq = int(df1["序号"].max()) + 1 if len(df1) else 1 for key in sorted(only_t2_keys): r2 = t2_map.loc[key] if isinstance(r2, pd.DataFrame): r2 = r2.iloc[0] new_row = row_from_table2(r2, seq_no=next_seq) new_row["_key"] = key new_rows.append(new_row) next_seq += 1 df_new = pd.DataFrame(new_rows) if new_rows else pd.DataFrame() if len(df_new): df1 = pd.concat([df1, df_new], ignore_index=True) out_cols = [c for c in df1_orig.columns if c != "_key"] df1 = df1.drop(columns=["_key"], errors="ignore")[out_cols] # 比对报告 summary = pd.DataFrame( [ {"项目": "比对范围", "数量": f"{FILTER_YEAR}年{FILTER_MONTH}月"}, {"项目": "表1全量行数", "数量": len(df1_all)}, {"项目": "表1四月行数", "数量": len(df1_orig)}, {"项目": "表2全量行数", "数量": len(df2_all)}, {"项目": "表2四月行数", "数量": len(df2)}, {"项目": "三条件完全命中（覆盖更新）", "数量": len(matched_keys)}, {"项目": "命中且字段完全一致", "数量": unchanged_matched}, {"项目": "命中且存在字段差异", "数量": len(matched_keys) - unchanged_matched}, {"项目": "表2有表1无（新增）", "数量": len(only_t2_keys)}, {"项目": "表1有表2无（保留表1原样）", "数量": len(only_t1_keys)}, {"项目": "合并后四月总行数", "数量": len(df1)}, { "项目": "说明", "数量": "仅比对/合并四月数据；表2无客户名称列，匹配记录客户名称保留表1原值；新增记录客户名称为空", }, ] ) df_overwrite = pd.DataFrame(overwrite_rows) df_diff = pd.DataFrame(diff_rows) df_add = pd.DataFrame(new_rows)[ [ "序号", "加氢日期", "加氢时间", "加氢站名称", "车牌号", "加氢量(kg)", "成本单价(元/kg)", "成本总价(元)", "行驶里程(km)", "状态", "订单编号", ] ] if len(new_rows) else pd.DataFrame( columns=[ "序号", "加氢日期", "加氢时间", "加氢站名称", "车牌号", "加氢量(kg)", "成本单价(元/kg)", "成本总价(元)", "行驶里程(km)", "状态", "订单编号", ] ) only_t2_detail = df2[df2["_key"].isin(only_t2_keys)][ ["日期", "车牌", "加气量kg", "公里数", "加氢站", "是否对账", "成本单价", "成本金额"] ].copy() only_t1_detail = df1_orig[df1_orig["_key"].isin(only_t1_keys)][ ["加氢日期", "车牌号", "加氢量(kg)", "加氢站名称", "客户名称", "成本总价(元)", "订单编号"] ].copy() field_map = pd.DataFrame( [ {"表1字段": "加氢日期", "表2字段": "日期", "处理方式": "覆盖"}, {"表1字段": "加氢时间", "表2字段": "日期", "处理方式": "覆盖（与加氢日期对应）"}, {"表1字段": "加氢站名称", "表2字段": "加氢站", "处理方式": "覆盖"}, {"表1字段": "客户名称", "表2字段": "（表2无此列）", "处理方式": "匹配记录保留表1原值"}, {"表1字段": "车牌号", "表2字段": "车牌", "处理方式": "覆盖"}, {"表1字段": "加氢量(kg)", "表2字段": "加气量（kg）", "处理方式": "覆盖"}, {"表1字段": "成本单价(元/kg)", "表2字段": "成本单价", "处理方式": "覆盖"}, {"表1字段": "成本总价(元)", "表2字段": "成本金额", "处理方式": "覆盖"}, {"表1字段": "加氢单价(元/kg)", "表2字段": "-", "处理方式": "保留表1"}, {"表1字段": "加氢总价(元)", "表2字段": "-", "处理方式": "保留表1"}, {"表1字段": "行驶里程(km)", "表2字段": "公里数", "处理方式": "覆盖"}, {"表1字段": "备注", "表2字段": "-", "处理方式": "保留表1"}, {"表1字段": "业务员", "表2字段": "-", "处理方式": "保留表1"}, {"表1字段": "承担方式", "表2字段": "-", "处理方式": "保留表1"}, {"表1字段": "订单编号", "表2字段": "-", "处理方式": "保留表1；新增记录不生成"}, ] ) with pd.ExcelWriter(OUT_DATA, engine="openpyxl") as writer: df1.to_excel(writer, sheet_name="车辆氢费明细", index=False) with pd.ExcelWriter(OUT_COMPARE, engine="openpyxl") as writer: summary.to_excel(writer, sheet_name="比对总览", index=False) field_map.to_excel(writer, sheet_name="字段映射规则", index=False) df_overwrite.to_excel(writer, sheet_name="覆盖更新明细", index=False) df_diff.to_excel(writer, sheet_name="字段差异明细", index=False) df_add.to_excel(writer, sheet_name="新增记录", index=False) only_t2_detail.to_excel(writer, sheet_name="仅表2有", index=False) only_t1_detail.to_excel(writer, sheet_name="仅表1有", index=False) print("合并结果:", OUT_DATA) print("比对结果:", OUT_COMPARE) print(summary.to_string(index=False)) if __name__ == "__main__": main()