""" 功能:计算成绩数据 """ import pandas as pd import numpy as np import config scoredata = pd.read_excel(config.scorepath) scoredata = scoredata[scoredata["卷面总分"] > 0] paperdata = pd.read_excel(config.paperpath) paperdisdata = pd.read_excel(config.paperdistributionpath) def excute1(courseCode, papertype, paperName): """ 计算试卷特征量数 """ # 根据试卷类型，试卷名称，科目划分成绩 score_groups = scoredata.groupby(by=["科目代码", "试卷类型", "试卷名称"]) groupdata = score_groups.get_group((courseCode, papertype, paperName)) courseName = groupdata["科目名称"].values[0] # 获取当前试卷的试题结构 current_paper = paperdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] # 信度1,信度2 # 做统一处理 # 试题数量 k = current_paper.shape[0] # 计算客观题 # 每题方差的和 var = 0 def solve(x): if x.isnull().loc["客观分得分明细"]: pass else: # 拆分客观题 idx = 1 for y in x["客观分得分明细"].split(";"): x["object_" + str(idx)] = float(y.split(":")[1]) idx = idx + 1 if x.isnull().loc["主观分得分明细"]: pass else: idx = 1 for y in x["主观分得分明细"].split(";"): x["subject_" + str(idx)] = float(y) idx = idx + 1 return x groupdata_new = groupdata.apply(solve, axis=1) columns = set(groupdata_new.columns) var_1 = 0 var_2 = 0 for column in columns: if "subject" in column or "object" in column: var = var + groupdata_new[column].var(ddof=0) if int(column.split("_")[1]) % 2 != 0: var_1 = var_1 + groupdata_new[column] else: var_2 = var_2 + groupdata_new[column] trust_1 = round((k / (k - 1)) * (1 - var / groupdata["卷面总分"].var(ddof=0)), 2) # trust_2有问题 trust_2 = round(2 * (1 - ((var_1.std(ddof=0) / var_1.mean() + var_2.std(ddof=0) / var_2.mean()) / ( groupdata["卷面总分"].std(ddof=0) / groupdata["卷面总分"].mean()))), 2) print(courseCode, courseName, papertype, paperName, current_paper["小题满分"].sum(), groupdata["卷面总分"].max(), groupdata["卷面总分"].max() - groupdata["卷面总分"].min(), round(groupdata["卷面总分"].mean(), 2) , round(groupdata["卷面总分"].std(ddof=0), 2), round(groupdata["卷面总分"].std(ddof=0) / groupdata["卷面总分"].mean() * 100, 2), trust_1, trust_2, round(groupdata["卷面总分"].mean() / current_paper["小题满分"].sum(), 2) ) def excute2(courseCode, papertype, paperName, pingdu=10, customline=[0, 110, 122, 227]): """ 计算科目成绩总分频率分布，频度默认10,如需修改，请自行修改 分数线默认[0,100,120,227],如需修改，请修改customline """ score_groups = scoredata.groupby(by=["科目代码", "试卷类型", "试卷名称"]) groupdata = score_groups.get_group((courseCode, papertype, paperName)) courseName = groupdata["科目名称"].values[0] # 获取当前试卷的试题结构 current_paper = paperdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] pingdudatarange = [x for x in range(0, current_paper["小题满分"].sum(), pingdu)] pingdudatarange.append(float('inf')) # groupdata["分数段"]=pd.cut(groupdata["卷面总分"],bins=pingdudatarange,right=False) # groupdata.sort_values(by="分数段",inplace=True) # table_1=groupdata["分数段"].value_counts().sort_index() # dict_table={"分数段":table_1.index,"个数":table_1.values} # data_1=pd.DataFrame(dict_table) # data_1["频率"]=data_1["个数"]/data_1["个数"].sum() # data_1["累计个数"]=data_1["个数"].cumsum() # data_1["累计频率"]=data_1["累计个数"]/data_1["个数"].sum() data_1 = scorefenduan(groupdata, pingdudatarange) print(data_1) # 计算自定义分数段 customline.append(float('inf')) data_2 = scorefenduan(groupdata, customline) print(data_2) def scorefenduan(data, cutline): data["分数段"] = pd.cut(data["卷面总分"], bins=cutline, right=False) data.sort_values(by="分数段", inplace=True) table_1 = data["分数段"].value_counts().sort_index() dict_table = {"分数段": table_1.index, "个数": table_1.values} data_1 = pd.DataFrame(dict_table) data_1["频率"] = data_1["个数"] / data_1["个数"].sum() data_1["累计个数"] = data_1["个数"].cumsum() data_1["累计频率"] = data_1["累计个数"] / data_1["个数"].sum() return data_1 def exctute3(courseCode, papertype, paperName, customline=[0, 110, 122, 227]): """ 科目成绩占初试总分权重 """ # 先取该科目成绩 score_groups = scoredata.groupby(by=["科目代码", "试卷类型", "试卷名称"]) groupdata = score_groups.get_group((courseCode, papertype, paperName)) # 统计该科目学号下的每个人的成绩总和 total_score_data = scoredata[scoredata["学号"].isin(list(groupdata["学号"]))] current_paper = paperdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] # 开始进行分箱 customline.append(float('inf')) total_score_data["分数段"] = pd.cut(total_score_data["卷面总分"], bins=customline, right=False) total_score_data.sort_values(by="分数段", inplace=True) result = pd.DataFrame(columns=["分数段", "人数占比", "初始总分平均分", "本科目成绩平均分", "本科目难度", "占总分权重%"]) scorelist = set(total_score_data["分数段"]) for scorerange in scorelist: cur_score_data = total_score_data[total_score_data["分数段"] == scorerange] total_avg = cur_score_data["卷面总分"].mean() cur_course_avg = cur_score_data[cur_score_data["科目代码"] == courseCode]["卷面总分"].mean() cur_course_diffcult = cur_score_data[cur_score_data["科目代码"] == courseCode]["卷面总分"].mean() / \ current_paper["小题满分"].sum() zongfen_quanzhong = total_avg / cur_course_avg * 100 result = result.append( {"分数段": scorerange, "人数占比": cur_score_data.shape[0] / total_score_data.shape[0], "初始总分平均分": total_avg, "本科目成绩平均分": cur_course_avg, "本科目难度": cur_course_diffcult, "占总分权重%": zongfen_quanzhong}, ignore_index=True) result = result.append( {"分数段": "全体考生", "人数占比": 1, "初始总分平均分": total_score_data["卷面总分"].mean(), "本科目成绩平均分": total_score_data[total_score_data["科目代码"] == courseCode]["卷面总分"].mean(), "本科目难度": total_score_data["卷面总分"].mean() / current_paper["小题满分"].sum(), "占总分权重%": total_score_data["卷面总分"].mean() / total_score_data[total_score_data["科目代码"] == courseCode][ "卷面总分"].mean() * 100}, ignore_index=True) # print(result) def excute4(courseCode, papertype, paperName): """ 试卷项目编排 """ score_groups = scoredata.groupby(by=["科目代码", "试卷类型", "试卷名称"]) groupdata = score_groups.get_group((courseCode, papertype, paperName)) courseName = groupdata["科目名称"].values[0] # 获取当前试卷的试题结构 current_paper = paperdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] def solve(x): if x.isnull().loc["客观分得分明细"]: pass else: # 拆分客观题 idx = 1 for y in x["客观分得分明细"].split(";"): x["object_" + str(idx)] = float(y.split(":")[1]) idx = idx + 1 if x.isnull().loc["主观分得分明细"]: pass else: idx = 1 for y in x["主观分得分明细"].split(";"): x["subject_" + str(idx)] = float(y) idx = idx + 1 return x groupdata_new = groupdata.apply(solve, axis=1) # 统计每一个子题号的难度指数,按照大题题号，小题号进行分组 number_group = current_paper.groupby(by=["大题号", "小题号"]) result = pd.DataFrame(columns=["大题号", "小题号", "难度"]) object_index_number = 1 subject_index_number = 1 for index, questiondata in number_group: main_number = index[0] sub_number = index[1] object_question_data = questiondata[questiondata["是否客观题"] == 1] subject_question_data = questiondata[questiondata["是否客观题"] == 0] total_score = questiondata["小题满分"].sum() sum_var = 0 for i in range(0, object_question_data.shape[0]): sum_var = sum_var + groupdata_new["object_" + str(object_index_number)] object_index_number = object_index_number + 1 for i in range(0, subject_question_data.shape[0]): sum_var = sum_var + groupdata_new["subject_" + str(subject_index_number)] subject_index_number = subject_index_number + 1 avg = sum_var.mean() diffcult = avg / total_score result = result.append({"大题号": main_number, "小题号": sub_number, "难度": diffcult}, ignore_index=True) print(result) def excute5(courseCode, papertype, paperName): """ 试卷特征量数 """ score_groups = scoredata.groupby(by=["科目代码", "试卷类型", "试卷名称"]) groupdata = score_groups.get_group((courseCode, papertype, paperName)) courseName = groupdata["科目名称"].values[0] # 获取当前试卷的试题结构 current_paper = paperdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] def solve(x): if x.isnull().loc["客观分得分明细"]: pass else: # 拆分客观题 idx = 1 for y in x["客观分得分明细"].split(";"): x["object_" + str(idx)] = float(y.split(":")[1]) idx = idx + 1 if x.isnull().loc["主观分得分明细"]: pass else: idx = 1 for y in x["主观分得分明细"].split(";"): x["subject_" + str(idx)] = float(y) idx = idx + 1 return x groupdata_new = groupdata.apply(solve, axis=1) # 统计每一个子题号的难度指数,按照大题题号，小题号进行分组 number_group = current_paper.groupby(by=["大题号", "小题号"]) result = pd.DataFrame(columns=["大题号", "小题号", "满分", "最高分", "最低分", "平均分", "差异系数", "区分度", "零分人数", "有效卷数"]) object_index_number = 1 subject_index_number = 1 for index, questiondata in number_group: main_number = index[0] sub_number = index[1] object_question_data = questiondata[questiondata["是否客观题"] == "是"] subject_question_data = questiondata[questiondata["是否客观题"] == "否"] total_score = questiondata["小题满分"].sum() sum_var = 0 for i in range(0, object_question_data.shape[0]): sum_var = sum_var + groupdata_new["object_" + str(object_index_number)] object_index_number = object_index_number + 1 for i in range(0, subject_question_data.shape[0]): sum_var = sum_var + groupdata_new["subject_" + str(subject_index_number)] subject_index_number = subject_index_number + 1 avg = sum_var.mean() diffcult = avg / total_score import math result = result.append( {"大题号": main_number, "小题号": sub_number, "满分": questiondata["小题满分"].sum(), "最高分": sum_var.max(), "最低分": sum_var.min(), "平均分": round(sum_var.mean(), 2), "标准差": sum_var.std(ddof=0), "差异系数": round(sum_var.std(ddof=0) / sum_var.mean(), 2) , "区分度": (((sum_var * groupdata_new["卷面总分"]).sum()) - ( sum_var.sum() * groupdata_new["卷面总分"].sum() / sum_var.shape[0])) / math.sqrt( ((sum_var ** 2).sum() - sum_var.sum() ** 2 / sum_var.shape[0]) * ( (groupdata_new["卷面总分"] ** 2).sum() - (( groupdata_new["卷面总分"].sum()) ** 2) / sum_var.shape[ 0])), "零分人数": (sum_var == 0).astype(int).sum(), "有效卷数": sum_var.shape[0]}, ignore_index=True) print(result) # excute5("CS2021001", "A", "Y2021090201") def excute6(courseCode, papertype, paperName, type): """ 题型难度分布 type为 题型1，题型2，题型3，内容1,2,3等，参考导入模板 """ score_groups = scoredata.groupby(by=["科目代码", "试卷类型", "试卷名称"]) groupdata = score_groups.get_group((courseCode, papertype, paperName)) courseName = groupdata["科目名称"].values[0] # 获取当前试卷的试题结构 current_paper = paperdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] current_paper_dis = paperdisdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] # 题型难度一 current_paper_dis["idx"] = np.array([i for i in range(1, current_paper_dis.shape[0] + 1)]) current_paper["idx"] = np.array([i for i in range(1, current_paper.shape[0] + 1)]) question_dis_group_1 = current_paper_dis.groupby(type) def solve(x): idx = 1 if x.isnull().loc["客观分得分明细"]: pass else: # 拆分客观题 for y in x["客观分得分明细"].split(";"): x["object_" + str(idx)] = float(y.split(":")[1]) idx = idx + 1 if x.isnull().loc["主观分得分明细"]: pass else: for y in x["主观分得分明细"].split(";"): x["subject_" + str(idx)] = float(y) idx = idx + 1 return x groupdata_new = groupdata.apply(solve, axis=1) print("题型,题量,满分,难度") for title, data in question_dis_group_1: qustion_data = current_paper[current_paper["大题号"].isin(data["大题号"]) & current_paper["小题号"].isin(data["小题号"])] sum_var = 0 hard_num = 0 hard_score = 0 normal_num = 0 normal_score = 0 easy_num = 0 easy_score = 0 for index, ques in qustion_data.iterrows(): question_index = ques["idx"] if "object_" + str(question_index) in groupdata_new.columns: cur_questiton = groupdata_new["object_" + str(question_index)] sum_var = sum_var + cur_questiton elif "subject_" + str(question_index) in groupdata_new.columns: cur_questiton = groupdata_new["subject_" + str(question_index)] sum_var = sum_var + cur_questiton diffcult = cur_questiton.mean() / ques["小题满分"] if diffcult < 0.4: # 高难度 hard_num = hard_num + 1 hard_score = hard_score + ques["小题满分"] elif 0.4 <= diffcult <= 0.6: normal_num = normal_num + 1 normal_score = normal_score + ques["小题满分"] elif diffcult > 0.6: easy_num = easy_num + 1 easy_score = easy_score + ques["小题满分"] print(title, data.shape[0], qustion_data["小题满分"].sum(), sum_var.mean() / qustion_data["小题满分"].sum()) print("高难度") print("题量", "分值", "%") print(hard_num, hard_score, hard_num / data.shape[0]) print("中难度") print("题量", "分值", "%") print(normal_num, normal_score, normal_num / data.shape[0]) print("低难度") print("题量", "分值", "%") print(easy_num, easy_score, easy_num / data.shape[0]) def excute7(courseCode, papertype, paperName, pingdu=10, customline=[0, 110, 122, 227]): """ 试题难度分组分布，10分一段 """ score_groups = scoredata.groupby(by=["科目代码", "试卷类型", "试卷名称"]) groupdata = score_groups.get_group((courseCode, papertype, paperName)) courseName = groupdata["科目名称"].values[0] # 获取当前试卷的试题结构 current_paper = paperdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] current_paper_dis = paperdisdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] # 题型难度一 current_paper_dis["idx"] = np.array([i for i in range(1, current_paper_dis.shape[0] + 1)]) current_paper["idx"] = np.array([i for i in range(1, current_paper.shape[0] + 1)]) question_dis_group_1 = current_paper_dis.groupby(type) def solve(x): idx = 1 if x.isnull().loc["客观分得分明细"]: pass else: # 拆分客观题 for y in x["客观分得分明细"].split(";"): x["object_" + str(idx)] = float(y.split(":")[1]) idx = idx + 1 if x.isnull().loc["主观分得分明细"]: pass else: for y in x["主观分得分明细"].split(";"): x["subject_" + str(idx)] = float(y) idx = idx + 1 return x groupdata_new = groupdata.apply(solve, axis=1) # 按总分进行切片 pingdudatarange = [x for x in range(0, current_paper["小题满分"].sum(), pingdu)] pingdudatarange.append(float('inf')) score_fenduan_1(groupdata_new, pingdudatarange, current_paper) customline.append(float('inf')) score_fenduan_1(groupdata_new, customline, current_paper) def score_fenduan_1(data, cutline, paper): data["分数段"] = pd.cut(data["卷面总分"], bins=cutline, right=False) group_fenduan = data.groupby(by=["分数段"]) for index, current_ques in paper.iterrows(): print("大题号,小题号") print(current_ques["大题号"], current_ques["小题号"]) question_index = current_ques["idx"] for scorerange, group_data in group_fenduan: if group_data.shape[0] != 0: if "object_" + str(question_index) in group_data.columns: cur_questiton_score = group_data["object_" + str(question_index)] elif "subject_" + str(question_index) in group_data.columns: cur_questiton_score = group_data["subject_" + str(question_index)] avg = cur_questiton_score.mean() else: avg = 0 # print(cur_questiton_score) print(scorerange, avg / current_ques["小题满分"]) # groupdata_new.sort_values(by="分数段", inplace=True) def excute8(courseCode, papertype, paperName, type): """ 题型区分度分布 type为 题型1，题型2，题型3，内容1,2,3等，参考导入模板 """ score_groups = scoredata.groupby(by=["科目代码", "试卷类型", "试卷名称"]) groupdata = score_groups.get_group((courseCode, papertype, paperName)) courseName = groupdata["科目名称"].values[0] # 获取当前试卷的试题结构 current_paper = paperdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] current_paper_dis = paperdisdata[ (paperdata["科目代码"] == courseCode) & (paperdata["试卷类型"] == papertype) & (paperdata["试卷名称"] == paperName)] # 题型难度一 current_paper_dis["idx"] = np.array([i for i in range(1, current_paper_dis.shape[0] + 1)]) current_paper["idx"] = np.array([i for i in range(1, current_paper.shape[0] + 1)]) question_dis_group_1 = current_paper_dis.groupby(type) def solve(x): idx = 1 if x.isnull().loc["客观分得分明细"]: pass else: # 拆分客观题 for y in x["客观分得分明细"].split(";"): x["object_" + str(idx)] = float(y.split(":")[1]) idx = idx + 1 if x.isnull().loc["主观分得分明细"]: pass else: for y in x["主观分得分明细"].split(";"): x["subject_" + str(idx)] = float(y) idx = idx + 1 return x groupdata_new = groupdata.apply(solve, axis=1) print("题型,题量,满分,难度") for title, data in question_dis_group_1: qustion_data = current_paper[current_paper["大题号"].isin(data["大题号"]) & current_paper["小题号"].isin(data["小题号"])] sum_var = 0 hard_num = 0 hard_score = 0 normal_num = 0 normal_score = 0 easy_num = 0 easy_score = 0 bad_num=0 bad_score=0 for index, ques in qustion_data.iterrows(): question_index = ques["idx"] if "object_" + str(question_index) in groupdata_new.columns: cur_questiton = groupdata_new["object_" + str(question_index)] sum_var = sum_var + cur_questiton elif "subject_" + str(question_index) in groupdata_new.columns: cur_questiton = groupdata_new["subject_" + str(question_index)] sum_var = sum_var + cur_questiton # diffcult=cur_questiton.mean()/ques["小题满分"] import math dimeistion = round(sum_var.std(ddof=0) / sum_var.mean(), 2) if dimeistion >= 0.4: # 高难度 hard_num = hard_num + 1 hard_score = hard_score + ques["小题满分"] elif 0.3 <= dimeistion < 0.4: normal_num = normal_num + 1 normal_score = normal_score + ques["小题满分"] elif 0.2 <= dimeistion < 0.3: easy_num = easy_num + 1 easy_score = easy_score + ques["小题满分"] elif dimeistion < 0.2: bad_num = bad_num + 1 bad_score = bad_score + ques["小题满分"] print(title, data.shape[0], qustion_data["小题满分"].sum(), sum_var.mean() / qustion_data["小题满分"].sum()) print("优") print("题量", "分值", "%") print(hard_num, hard_score, hard_num / data.shape[0]) print("良") print("题量", "分值", "%") print(normal_num, normal_score, normal_num / data.shape[0]) print("中") print("题量", "分值", "%") print(easy_num, easy_score, easy_num / data.shape[0]) print("差") print("题量", "分值", "%") print(bad_num, bad_score, bad_num / data.shape[0]) excute5(242,"#",242) # excute6("CS2021001","A","Y2021090201","内容1") # excute7("CS2021001","A","Y2021090201") # excute1("CS2021001","A","Y2021090201") # excute2("CS2021001","A","Y2021090201")