学生评教，问卷调查表评价教师统计，python+pandas处理数据

先上一个结果表格

几个关键步骤

1、问卷网站上设置相关题目，条目，最好用评分题目（点击文本选项，但是保存下来的是分值），如图

2、pandas清洗数据，包括unstack，其目的是把所有学生得分细分展开，因为班级选科不同，问卷上针对的教师不同，比如有些学生需要评价物理教师，有些需要评价历史老师，教师数据每个维度都存在竖表中，如图

3、pandas以["校区","年级","班级","班主任或学科","题号"],groupby，进一步，按得分列求平均分，这个分数是学科的班级平均分（按评价细分维度，如第1题等等）

4、pandas数据透视表pivot_table，index=["校区","年级","班级","班主任或学科"], columns='题号', values='得分'，建立班级汇总的得分透视表

5、上表已经是教学管理部门要求的最细表格，要作为结果保存在.xlsx中。然后根据教师姓名，利用AVERAGEIFS计算每位教师的汇总分（带的班级较多要计算平均分）。

第2、3步骤的代码：

# -*- coding: gbk -*-

import pandas as pd
import os


xl = pd.ExcelFile('学生细分表.xlsx')
df = xl.parse('Sheet1')


df_melt = df.melt(id_vars=["校区","年级","班级","性别","选科"],var_name="项目",value_name="得分"  )

#print(df_melt)
df_filtered = df_melt[pd.notna(df_melt['得分'])]
df_splitted = df_filtered['项目'].str.split('_',expand=True)
df_splitted = df_splitted.rename({0:'班主任或学科',1:'题号'},axis=1)
df_comb = pd.concat([df_filtered,df_splitted],axis=1)

#'''
for index, row in df_comb.iterrows():
    #print(index,row['性别'],df_comb.at[index, '班主任或学科'])
    if row['性别'] == "男" and row['班主任或学科'] == "体育":
        new_sub = "体育男"
        df_comb.at[index, '班主任或学科'] = new_sub
    if row['性别'] == "女" and row['班主任或学科'] == "体育":
        new_sub ="体育女" 
        df_comb.at[index, '班主任或学科'] = new_sub
#'''


df_comb.to_csv('学生细分表展开.csv', index=False)
print(df_comb.head(20))
#以上正常数据



df_comb=df_comb.groupby(["校区","年级","班级","班主任或学科","题号"], as_index=False).agg({"得分":"mean"}) #跟39行reset_index作用一样

df_class = pd.pivot_table(data=df_comb, index=["校区","年级","班级","班主任或学科"], columns='题号', values='得分')

df_class.to_csv('班级按题号得分表.csv', index=False)


df_comb.to_csv('班级按题号得分表展开.csv', index=False)

 第4步骤的代码

# -*- coding: gbk -*-

import pandas as pd
import numpy as np
import os


df = pd.read_csv('班级按题号得分表展开.csv')
df2 = pd.read_csv('师资安排班师表展开.csv')


df_class = pd.pivot_table(data=df, index=["校区","年级","班级","班主任或学科"], columns='题号', values='得分').reset_index()

df_class['班主任或学科']=df_class['班主任或学科'].astype('category').cat.set_categories(['班主任','语文','数学','英语','物理','化学','生物','政治','历史','地理','音乐','体育女','体育男','美术','信息','通用','心理'])
df_class_sorted = df_class.sort_values(by=["班主任或学科","校区","年级","班级"], ascending=[True, True, True, True],inplace=True)

df_class.insert(3, "教师", np.nan)
df_class.insert(5, "满意率", np.nan)

for index, row in df_class.iterrows():
    #for column_name, value in row.items():
    con1=df_class.at[index, '校区']
    con2=df_class.at[index, '年级']
    con3=df_class.at[index, '班级']
    con4=df_class.at[index, '班主任或学科']

    sum=0
    if df_class.at[index, '班主任或学科'] =="班主任" :
        sum=df_class.at[index, '第1题']+df_class.at[index, '第2题']+df_class.at[index, '第3题']+df_class.at[index, '第4题']
        sum=sum/4
    else:
        sum=df_class.at[index, '第1题']+df_class.at[index, '第2题']+df_class.at[index, '第3题']+df_class.at[index, '第4题']+df_class.at[index, '第5题']+df_class.at[index, '第6题']+df_class.at[index, '第7题']+df_class.at[index, '第8题']
        sum=sum/8
    #print(con1,con2,con3,con4,sum)
    df_class.at[index, '满意率']=sum
    cons=df2.loc[(df2['校区']==con1) & (df2['年级']==con2) & (df2['班级']==con3) & (df2['班主任或学科']==con4),"教师"]
    
    sstr=list(cons)
    
    tname=""
    if len(sstr)>0:
        tname=sstr[0]
    else:
        tname=""
    df_class.at[index, '教师']=tname
    


df_class.to_csv('班级按题号得分表.csv', index=False)

这里提示一下，因为班级汇总表出来后要填写班级任教信息，管理部门一般提供的这样的表格可以用以下代码转换为

df_melt = df.melt(id_vars=["校区","年级","班级","选科"],var_name="班主任或学科",value_name="教师"  )

df_filtered = df_melt[pd.notna(df_melt['教师'])]

df_filtered.to_csv('师资安排班师表展开.csv', index=False)