开发环境：win10，python3，jupyter notebook
所用到的分析excel表：https://pan.baidu.com/s/1I-xbJSXmIX1Ii9MO1HT19w，提取码：ztsh
一、开发环境的搭设
- 安装pandas库
一个非常强大python的数据分析库,拥有Series 与 DataFrame 两种数据结构。
pip install pandas
- 安装 jupter notebook
一个基于浏览器的交互式的笔记本，使得python编写更加便捷，特别适合边分析边写代码，很适合进行数据分析与可视化图形处理。
pip install jupyter
- 修改jupyter notebook 默认的工作目录
在python3的安装目录中找到：python3\Scripts\ jupyter-notebook.exe
双击exe应用程序即可进行工作，但是默认的工作目录也在python安装目录中，不是很方便，可以进行修改为自己的workspace。将exe快捷方式发送至桌面，右键属性，在起始位置输入自己的工作目录，如图：
jupyter notebook-修改默认工作目录.png
至此，常规的环境已经搭设完毕，还可以进行jupyter notebook，请在网上自行搜索美化主题。
二、读取数据
我们需要读取两个表格：
（1）学生的作答选择情况，主要由题号及选择的答案组成
（2）题库的模板情况，主要由问题与选项情况组成
最终想要的结果：学生每道题目的各选项的选择率表格
- 用DataFrame数据格式读取excel表：
import pandas as pd # 导入pandas模块
from pandas import Series,DataFrame
pd.set_option('display.max_columns', None) # 显示所有的列
pd.set_option('display.max_rows', None) # 显示所有的行
filename1 = '体育问卷-测试--作答情况.xls'
df1 = pd.read_excel(filename1,index_col=0) # index_col 设置默认的列索引
df1.head(16) # 默认显示前5行，这里显示16行
-
代码结束运行（Shift+回车）结果：
载入excel1.png
同样的，读取另外一个excel表格：
filename2 = '体育问卷导入模版.xlsx'
df2 = pd.read_excel(filename2)
df2.head()
Shift+回车 结果：
载入excel2.png
三、数据清洗与筛选
- 数组的行列数
df1.shape # 输出一个长度为2的列表，第一个是行数，第二个是列数
- 丢弃重复项
df1 = df1.drop_duplicates(subset='personID',keep='first')
# 返回删除重复的‘userID’列,保留第一个（first）
- 寻找空值项
df1.isnull()
df2.isnull()
- 将df2中的空值填补为0
df2 = df2.fillna(value=0)
四、DataFrame中选取数据与操作数据
- 两个数据表中的题目号进行对应
对比发现 df2中的‘题库id’中的题号为纯数字，而df1中最上一列的column题号为‘q_ +数字’ 的格式，因此要进行对应。直接应用map函数进行：
# 将题库中的题号与作答情况博表格中的题号进行对应
df2['题库id'] = df2['题库id'].map(lambda x:'q_'+str(x))
# 由于需要用题库id进行索引，因此重新设置df2的索引
df2 = df2.set_index('题库id')
对于DataFrame应用函数主要有三种：
（1）apply()：是一种让函数作用于列或者行的操作
（2）applymap() ：是一种让函数作用于DataFrame每一个元素的操作
（3）map()：是一种让函数作用于Series每一个元素的操作
- 用列表确定所有题目的题号
Columns = []
for i in df1.columns:
if str(i).startswith('q'): #题目标号均是由q字母开头
Columns.append(str(i))
- 确定最终数组的索引情况：
INDEX1 = ['题干','题目选项','选项编号','总选择率']
unique_school= list(df1['schoolName'].unique()) # 将所有的学校转为一个列表
Index = INDEX1 + unique_school # 确定最终DF的索引index
- 确定最大的选项标号：
ABC = []
for i in df2.columns:
if len(i) == 1: # 判断字符串的长度是否为1
ABC.append(i)
ABC # 确定最大的选项号
五、分析逻辑
- 自定义分析选择率的函数
def count_select(df):
total = df.shape[0] # 列数即为作答的人数
result = {} # 定义一个空字典
String=''
for i in df:
String = String + i
for i in String:
result[i] = "%.2f%%" % (String.count(i)/total*100) # 将小数转化为百分数并保留两位
return result # 返回这个字典
- 由于部分选项选择率为0，直接通过字典的健值输出会报错，因此，自定义一个字典与选项输出函数，例如当‘B’选项不在字典中，B的选择率其实就是0
def get_selection(dirs,j):
if j in dirs.keys():
return dirs[j]
else:
return 0
- 有了这两个函数，我们就可以设计最终的函数了。其实就是分学校进行统计，再分不同的题目进行分析，涉及到两个循环。
df_merge = []
for i in Columns:
dirs = {}
for j in ABC:
if df2.loc[i,j] != 0:
dirs_key = i+'_'+j
dirs[dirs_key] = [df2.loc[i,'题目标题']] # 加入题干,首次组装为列表
dirs[dirs_key].append(df2.loc[i,j]) # 加入题目选项
dirs[dirs_key].append(j) # 加入选项
# dirs[dirs_key].append(count_select(df1[i])[j]) # 根据选项增总选项的选择率
dirs[dirs_key].append(get_selection(count_select(df1[i]),j))
for school in unique_school:
# 根据学校加入学校的选择率
dirs[dirs_key].append(
get_selection(
count_select(
df1[i].loc[df1['schoolName'].isin([school])]),j))
df_merge.append(pd.DataFrame(dirs,index=Index))
# 将字典转化为DataFrame
- 最终，合并所有的DataFrame
df = df_merge[0]
for i in range(1,len(df_merge)):
df = df.join(df_merge[i])
df
最后我们可以看到各个题目的选择率了：
最终选择率.png
- 最后一步，我们将得到的DataFrame存储到excel中，以便进一步分析：
outfile = "xuanzelv.xlsx"
df.to_excel(outfile)
我们的目录此时就出现了xuanzelv.xlsx文件了
如果输出时报错，缺少一些模块，直接用pip install 安装就可以了。
