a27e3b8e43
Optimized the root .gitignore to exclude virtual environments, node modules, and temp folders to ensure clean and lightweight version tracking. Co-authored-by: Cursor <cursoragent@cursor.com>
121 lines
4.8 KiB
Python
121 lines
4.8 KiB
Python
#!/usr/bin/env python3
|
|
"""车辆信息(车辆状态) vs 交车任务(交车许可)：同车牌「已交车」是否一致，不一致仅输出车牌"""
|
|
|
|
import re
|
|
import shutil
|
|
from pathlib import Path
|
|
|
|
import pandas as pd
|
|
from openpyxl import load_workbook
|
|
from openpyxl.styles import PatternFill
|
|
|
|
FILE_VEHICLE = Path("/Users/sylvawong/Downloads/车辆信息-1779792181510.xlsx")
|
|
FILE_TASK = Path(
|
|
"/Users/sylvawong/Library/Containers/com.tencent.xinWeChat/Data/Documents/"
|
|
"xwechat_files/wxid_l80gh7d3x7u012_385c/temp/drag/"
|
|
"交车任务列表-导出-2026-05-26 (5).xlsx"
|
|
)
|
|
OUT = Path("/Users/sylvawong/Desktop/车辆信息与交车任务差异报告.xlsx")
|
|
OUT_PROJ = Path("/Users/sylvawong/Desktop/CURSOR/ONE-OS/车辆信息与交车任务差异报告.xlsx")
|
|
|
|
RED = PatternFill(start_color="FF9999", end_color="FF9999", fill_type="solid")
|
|
STATUS = "已交车"
|
|
|
|
|
|
def norm_plate(v) -> str:
|
|
if pd.isna(v):
|
|
return ""
|
|
return re.sub(r"[\s\-·]", "", str(v).strip().upper())
|
|
|
|
|
|
def plate_sets(df, plate_col, status_col):
|
|
"""返回：已交车车牌集合、该表全部车牌->状态（取首条）"""
|
|
df = df.copy()
|
|
df["_plate"] = df[plate_col].map(norm_plate)
|
|
df = df[df["_plate"] != ""]
|
|
status_map = df.drop_duplicates(subset=["_plate"], keep="first").set_index("_plate")[
|
|
status_col
|
|
].astype(str).str.strip()
|
|
delivered = set(status_map[status_map == STATUS].index)
|
|
return delivered, status_map
|
|
|
|
|
|
def run():
|
|
df_v = pd.read_excel(FILE_VEHICLE, sheet_name=0)
|
|
df_t = pd.read_excel(FILE_TASK, sheet_name=0)
|
|
|
|
set_v, map_v = plate_sets(df_v, "车牌号", "车辆状态")
|
|
set_t, map_t = plate_sets(df_t, "车牌号", "交车许可")
|
|
|
|
both_ok = sorted(set_v & set_t)
|
|
only_v = sorted(set_v - set_t) # 车辆信息=已交车，交车任务≠已交车
|
|
only_t = sorted(set_t - set_v) # 交车任务=已交车，车辆信息≠已交车
|
|
inconsistent = sorted(set(only_v) | set(only_t))
|
|
|
|
row_v = len(df_v[df_v["车辆状态"].astype(str).str.strip() == STATUS])
|
|
row_t = len(df_t[df_t["交车许可"].astype(str).str.strip() == STATUS])
|
|
consistent = len(only_v) == 0 and len(only_t) == 0
|
|
|
|
summary = pd.DataFrame(
|
|
[
|
|
["比对说明", "同车牌：车辆信息「车辆状态=已交车」与交车任务「交车许可=已交车」是否一致"],
|
|
["车辆信息 已交车行数", row_v],
|
|
["交车任务 已交车行数", row_t],
|
|
["车辆信息 已交车唯一车牌", len(set_v)],
|
|
["交车任务 已交车唯一车牌", len(set_t)],
|
|
["两边均已交车(一致)", len(both_ok)],
|
|
["是否完全一致", "是" if consistent else "否"],
|
|
["不一致车牌合计", len(inconsistent)],
|
|
["仅车辆信息为已交车(任务侧非已交车)", len(only_v)],
|
|
["仅交车任务为已交车(车辆信息侧非已交车)", len(only_t)],
|
|
],
|
|
columns=["项目", "结果"],
|
|
)
|
|
|
|
def display_plate(key: str) -> str:
|
|
mask_v = df_v["车牌号"].map(norm_plate) == key
|
|
mask_t = df_t["车牌号"].map(norm_plate) == key
|
|
if mask_v.any():
|
|
return str(df_v.loc[mask_v, "车牌号"].iloc[0])
|
|
if mask_t.any():
|
|
return str(df_t.loc[mask_t, "车牌号"].iloc[0])
|
|
return key
|
|
|
|
def plates_df(keys, diff_type):
|
|
if not keys:
|
|
return pd.DataFrame(columns=["差异类型", "车牌号"])
|
|
return pd.DataFrame(
|
|
{"差异类型": diff_type, "车牌号": [display_plate(k) for k in keys]}
|
|
)
|
|
|
|
df_only_v = plates_df(only_v, "车辆信息=已交车，交车任务≠已交车")
|
|
df_only_t = plates_df(only_t, "交车任务=已交车，车辆信息≠已交车")
|
|
df_all_bad = pd.concat([df_only_v, df_only_t], ignore_index=True)
|
|
df_both_ok = plates_df(both_ok, "两边均为已交车(一致)")
|
|
|
|
with pd.ExcelWriter(OUT, engine="openpyxl") as w:
|
|
summary.to_excel(w, sheet_name="比对汇总", index=False)
|
|
df_all_bad.to_excel(w, sheet_name="不一致车牌", index=False)
|
|
if len(only_v):
|
|
df_only_v.to_excel(w, sheet_name="仅车辆信息已交车", index=False)
|
|
if len(only_t):
|
|
df_only_t.to_excel(w, sheet_name="仅交车任务已交车", index=False)
|
|
df_both_ok.to_excel(w, sheet_name="一致车牌", index=False)
|
|
|
|
wb = load_workbook(OUT)
|
|
for sn in ["不一致车牌", "仅车辆信息已交车", "仅交车任务已交车"]:
|
|
if sn in wb.sheetnames:
|
|
ws = wb[sn]
|
|
for r in range(2, ws.max_row + 1):
|
|
for c in range(1, ws.max_column + 1):
|
|
ws.cell(row=r, column=c).fill = RED
|
|
wb.save(OUT)
|
|
shutil.copy(OUT, OUT_PROJ)
|
|
|
|
print(f"已输出: {OUT}")
|
|
print(summary.to_string(index=False))
|
|
|
|
|
|
if __name__ == "__main__":
|
|
run()
|