普通高中选课数据分析和可视化（4）

# 读文件source_df = pd.read_csv("xk73.csv",sep=',',header='infer',encoding='utf-8')schools = source_df['学校代码'].unique() #生成学校代码名称列表
for sch in schools: #遍历每一所学校，生成7选3原始报名数据到excel文件    sch_df = source_df[source_df['学校代码']==sch]    writer = pd.ExcelWriter(f'各校原始报名文件\{sch}.xlsx')    sch_df.to_excel(writer,sheet_name='7选3原始数据',startcol=0,index=False)    writer.save()    writer.close()

    # 合成学生选课组合信息    courses = []      for r in sch_df.index:         km = ""        for c in sch_df.columns:            if sch_df.at[r,c] == 1:                km += kms_dic[c]        courses.append(km)    zuhe_df = sch_df.loc[:, ['学生编号','学校代码','姓名']] #筛选某些行和列    zuhe_df.insert(3, '选课组合', courses) #插入选课组合列    zuhe_df = zuhe_df.sort_values(['选课组合'],ascending=False)        #生成7选3固定班级名单    kmzh_3_nums = zuhe_df['选课组合'].value_counts()    base = 40  #固定班人数下限    in_list = [c for c in kmzh_3_nums.index if kmzh_3_nums[c]>=base]    stu_1 = zuhe_df.loc[zuhe_df['选课组合'].isin(in_list)]  #也可以去掉.loc    stu_2 = zuhe_df.loc[~zuhe_df['选课组合'].isin(in_list)] #不在，前面加一个~

#生成7选2固定班级名单    kmzh_2_df = pd.DataFrame(columns=stu_2.columns)    kmzh_2_df["组合2"] = ""    base = 40 #固定班人数下限    for k in kmzh_2:        tmp = stu_2.loc[(stu_2["选课组合"].str.contains(k[0])) & (stu_2["选课组合"].str.contains(k[1]))]        tmp.insert(4, "组合2", [k] * len(tmp))         kmzh_2_df = kmzh_2_df.append(tmp)    kmzh_2_nums = kmzh_2_df['组合2'].value_counts()    in_list = [c for c in kmzh_2_nums.index if kmzh_2_nums[c]>=base]    stu_3 = kmzh_2_df.loc[kmzh_2_df['组合2'].isin(in_list)]  #也可以去掉.loc    stu_4 = kmzh_2_df.loc[~kmzh_2_df['组合2'].isin(in_list)] #不在，前面加一个~