按照五个实验分数计算期末成绩分数

'''
从五个实验分数出发,计算出最终的实验分数
目前设置:
有1,2个优秀的为优
有2,3个中的为中
其余为良

0    45
1    14
2    13
4     5
3     3
5     1
优>=2的为优,且签到次数=2
----
0    60
2     7
5     6
1     4
4     2
3     2
Name: 中, dtype: int64
中>=2的为中
'''
import pandas as pd
import os

df_3 = pd.read_excel("./签到提交/计科203班打分和签到情况.xlsx")
df_4 = pd.read_excel("./签到提交/计科204班打分和签到情况.xlsx")
df = pd.concat([df_3, df_4], axis=0)
for i in ["优", "良", "中"]:
    df[i] = 0
'''for _,student in df.iterrows():
    c1,c2,c3=0,0,0
    for i in [1,2,3,4,5]:
        shiyan=f"实验{i}"
        if student[shiyan]=="优":
            c1+=1
        elif student[shiyan]=="良":'''
for i in [1, 2, 3, 4, 5]:
    shiyan = f"实验{i}"
    df.loc[df[shiyan] == "优", "优"] += 1
    df.loc[df[shiyan] == "良", "良"] += 1
    df.loc[df[shiyan] == "中", "中"] += 1

# 全改成良
df["评分"] = "良"
# 好的改成优
df.loc[(df["签到情况"] == 2) & (df["优"] >= 3) & (df["中"] == 0), "评分"] = "优"
df.loc[(df["签到情况"] == 2) & (df["优"] == 2) & (df["良"] == 3), "评分"] = "优"
# 坏的改成中
df.loc[(df["签到情况"] == 0), "评分"] = "中"
df.loc[(df["签到情况"] == 1) & (df["中"] >= 2), "评分"] = "中"
df.to_excel("./result_打分表/两班打分表.xlsx")
print(df["评分"].value_counts() / 81)
print(df["评分"].value_counts())

# print(f"优{(a0/81):.2f} 良{a1/81:.2f} 中{a2/82:.2f}")