2025010172 张景悦
统计成绩
import pandas as pd
from openpyxl import Workbook# 1. 等级对应分数
grade_to_score = {'A+': 100, 'A': 95, 'A-': 90,'B+': 85, 'B': 80, 'B-': 75,'C+': 70, 'C': 65, 'C-': 60,'D': 50, 'F': 0
}# 2. 读取Excel
df = pd.read_excel("1.xlsx", header=1)
df = df.dropna(subset=["学号", "姓名"])
score_cols = ["第一次", "第二次", "第三次", "第四次", "第五次"]# 3. 等级转分 + 个人平均分
for col in score_cols:df[col+"_score"] = df[col].map(grade_to_score)
df["avg_personal"] = df[[c+"_score" for c in score_cols]].mean(axis=1).round(2)# 4. 写入新Excel
wb = Workbook()
ws = wb.active
ws.append(["学号", "姓名"] + [x for c in score_cols for x in [c+"_grade", c+"_score"]] + ["avg_personal"])for _, row in df.iterrows():ws.append([row["学号"], row["姓名"],*[val for c in score_cols for val in [row[c], row[c+"_score"]]],row["avg_personal"]])wb.save("成绩结果.xlsx")
print("处理完成，已生成成绩结果.xlsx")
![mmexport1780514909012]()
