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>
48 lines
1.8 KiB
Python
48 lines
1.8 KiB
Python
import pandas as pd
|
|
import numpy as np
|
|
|
|
out = "/Users/sylvawong/Desktop/CURSOR/ONE-OS/驾驶员提成与运营数据表_含排序.xlsx"
|
|
|
|
s1 = pd.read_excel(out, sheet_name="Sheet1")
|
|
sheet2 = pd.read_excel(out, sheet_name="Sheet2")
|
|
|
|
df = sheet2.copy()
|
|
df["日期"] = pd.to_datetime(df["日期"])
|
|
df["月份"] = df["日期"].dt.to_period("M").astype(str)
|
|
|
|
detail = (
|
|
df.groupby(["月份", "驾驶员", "车牌号码", "运营项目", "归属项目"], as_index=False)
|
|
.agg(总运费=("运费", "sum"), 提成金额=("提成金额", "sum"))
|
|
)
|
|
detail[["总运费", "提成金额"]] = detail[["总运费", "提成金额"]].round(2)
|
|
|
|
summary = (
|
|
detail.groupby(["月份", "驾驶员"], as_index=False)
|
|
.agg(总运费=("总运费", "sum"), 同月提成合计=("提成金额", "sum"))
|
|
)
|
|
summary[["总运费", "同月提成合计"]] = summary[["总运费", "同月提成合计"]].round(2)
|
|
|
|
detail["同月提成合计"] = np.nan
|
|
|
|
summary_rows = summary.copy()
|
|
summary_rows["车牌号码"] = ""
|
|
summary_rows["运营项目"] = "【同月合计】"
|
|
summary_rows["归属项目"] = ""
|
|
summary_rows["提成金额"] = summary_rows["同月提成合计"]
|
|
|
|
cols = ["月份", "驾驶员", "车牌号码", "运营项目", "归属项目", "总运费", "提成金额", "同月提成合计"]
|
|
detail = detail[cols]
|
|
summary_rows = summary_rows[cols]
|
|
|
|
detail["_sort"] = 0
|
|
summary_rows["_sort"] = 1
|
|
sheet3 = pd.concat([detail, summary_rows], ignore_index=True)
|
|
sheet3 = sheet3.sort_values(["月份", "驾驶员", "_sort", "车牌号码", "运营项目"], kind="stable").drop(columns="_sort")
|
|
|
|
with pd.ExcelWriter(out, engine="openpyxl") as writer:
|
|
s1.to_excel(writer, sheet_name="Sheet1", index=False)
|
|
sheet2.to_excel(writer, sheet_name="Sheet2", index=False)
|
|
sheet3.to_excel(writer, sheet_name="Sheet3", index=False)
|
|
|
|
print(f"updated {out}, rows={len(sheet3)}")
|