import pandas as pdpath = "成绩名单.xlsx"df = pd.read_excel(path)col_list = df.columnskemu_list = col_list[3:]for kemu in kemu_list: if pd.api.types.is_numeric_dtype(df[kemu]): df[kemu] = df[kemu].fillna(0) q70 = df[kemu].quantile(0.7) #前30% q35 = df[kemu].quantile(0.35) #前65% q10 = df[kemu].quantile(0.1) #前10% df[f"{kemu}等级"] = pd.cut(df[kemu], bins=[0,q10,q35,q70,150],labels=["D","C","B","A"],include_lowest=True)df_dj =df.drop(kemu_list, axis=1)df_dj.to_excel("成绩名册_分等级.xlsx",index=False)
