最近比较清闲,部门竞标成功了一个联谊活动,和其他两个女生比例比较高的学院一起承办,我是负责报名表的制作,从13号还是报名到现在(19号凌晨1点)已经有超过670位同学报名参加,男女比例趋向于1比2。
好像说的有点多,因为是学校实际数据,数据不会公开,报名表通过问卷星制作并下载得到原始数据表格。原始报名表共有8个,第一个姓名必填,第二个性别必选,第三个学历必选,第四个学院必填,第五个手机号码必填,六七八分别为照片、自我介绍和对另一半的要求,为选填项。
问卷星下载得到的exce格式如下:
0序号 | 1提交答卷时间 | 2所用时间 | 3来源 | 4来源详情 | 5来自IP | 6姓名 | 7性别 | 8学历 | 9所在学院 | 10手机号码 | 11个人照片 | 12自我介绍 | 13对另一半的要求 |
对于8个问题中的姓名、学院、手机号为非空字符串,性别、学历为数字,分别代表可选项的序号,照片为附件类型,如果上传则显示为照片地址,否则为字符串'(空)',介绍和要求如果填写则为对应字符串,否则为'(空)'。
首先,对于学院方面,有的喜欢写缩写,有的写全拼,例如对于文化与新闻传播学院,有的填“文新”、“文新学院”、“文化与新闻学院”等等,为了方便统计,需要对他们输入的千姿百态的学院名做出调整。于是手动整理了一个表格college_name.xls:
第一列为全拼,第二列为他们各种填写方式中对应为该学院的关键词的组合,多个间用空格隔开,并且不同学院的关键词不能相同或者包含关系。
college_name=xlrd.open_workbook(r'collegename.xls')#打开表格
cn=college_name.sheet_by_index(0)#获得第一张表
dictionary=dict()#字典存放关键词到学院的映射
mapping=dict()#字典存放学院到编号的映射
map_number=0#学院编号
start_row=1;#遍历开始行数 第一行为表头,所以跳过
row=start_row;
while row<cn.nrows:#cn.nrows:cn的行数
wordset=re.split(' ',cn.cell_value(row,1))#通过split对多个关键词进行分割,放入wordset里
for word in wordset:
dictionary[word]=cn.cell_value(row,0)#将每个关键词到学院全拼的映射存在dictionary字典
if cn.cell_value(row,0) not in mapping:
mapping[cn.cell_value(row,0)]=map_number
map_number=map_number+1#如果学院名不在mapping的key中,则存入,并映射为map_number
row=row+1
操作完后获得关键词到学院全名的映射和学院全名到编号的映射,之后储存每个学院分别的男女报名人数:
signtable=xlrd.open_workbook(r'联谊活动报名表.xls')#原始表
st=signtable.sheet_by_index(0)
row=start_row;
statistic=np.zeros([len(mapping),6]);#每一行代表一个学院的男生报名数、女生报名数、总报名人数占比、男生占比、女生占比和男女比例 先计算前两列
while row<st.nrows:
college=st.cell_value(row,9);#或者原始输入的学院名
find=0;#标记是否找到
for keyword in dictionary:
if keyword in college:#如果dictionary的key中存在关键词是原始输入学院名的子串,则找到
find=1;#
statistic[mapping[dictionary[keyword]],int(st.cell_value(row,7)-1)]+=1
break#将对应学院所在行(mapping映射的值)的第1列或第2列(取决于性别)+1
if find==0:
print(college+'can not find')
row=row+1
然后用plt的饼状图绘制一下饼状图:
import matplotlib.pyplot as plt
labels = [str(i) for i in range((len(mapping)))]
sizes = [(i[0]+i[1])for i in statistic]
plt.pie(sizes, labels=labels, autopct='%1.1f%%',
shadow=True, startangle=90)
plt.axis('equal')
plt.show()
结果:
由于默认不支持中文所以将每一块的标签设置为mapping映射的编号。
开始写入第一张表,格式如下:
0姓名 | 1填表时长/s | 2学院 | 3性别 | 4学历 | 5手机号码 | 6照片上传情况 | 7介绍 | 8要求 |
import xlwt
workspace=xlwt.Workbook(encoding='ascii')
excel=workspace.add_sheet('报名表完整版',cell_overwrite_ok=True)#添加第一张表
excel.write(0,0,'姓名')
excel.write(0,1,'填表时长/s')
excel.write(0,2,'学院')
excel.write(0,3,'性别')
excel.write(0,4,'学历')
excel.write(0,5,'手机号码')
excel.write(0,6,'照片上传情况')
excel.write(0,7,'介绍')
excel.write(0,8,'要求')#第一行添加表头
for row in range(1,st.nrows):
excel.write(row,0,st.cell_value(row,6))#写入姓名
excel.write(row,1,st.cell_value(row,2)[0:-1])#原始数据为'xxx秒',为了方便以后筛选,在这去掉最后一个字符,故设置为取0:-1
temp_college=st.cell_value(row,9)#原始学院名输入
for keyword in dictionary:
if keyword in temp_college:
excel.write(row,2,dictionary[keyword])#将在字典中查到的完整学院名写入
excel.write(row,3,st.cell_value(row,7))#写入性别
excel.write(row,4,st.cell_value(row,8))#写入学历
excel.write(row,5,st.cell_value(row,10))#写入手机号码
if st.cell_value(row,11)=='(空)':#如果照片未上传,则写为0,否则写入1
excel.write(row,6,0)
else:
excel.write(row,6,1)
excel.write(row,7,st.cell_value(row,12))#写入介绍
excel.write(row,8,st.cell_value(row,13))#写入要求
同理,制作第二张表,用于到时候在群里发布,方便他们根据表内容自行匹配,所以对手机号码、性别进行了部分隐藏。为了阅读方便,用jieba对他们写的介绍和要求进行了核心词的提取:
import jieba.posseg as posseg
excel2=workspace.add_sheet('报名表简洁版',cell_overwrite_ok=True)
excel2.write(0,0,'姓名')
excel2.write(0,1,'填表时长/s')
excel2.write(0,2,'学院')
excel2.write(0,3,'性别')
excel2.write(0,4,'学历')
excel2.write(0,5,'手机号码')
excel2.write(0,6,'照片上传情况')
excel2.write(0,7,'介绍')
excel2.write(0,8,'要求')
word_type=['n','nz','ns','vn','v','a','an']#保留的词性
for row in range(1,st.nrows):
temp_name=st.cell_value(row,6)
if len(temp_name)==2:
write_name=temp_name[0]+'*'
elif len(temp_name)==3:
write_name=temp_name[0]+'*'+temp_name[2]
else:
write_name=temp_name[0]+'**'+temp_name[3]
excel2.write(row,0,write_name)#对姓名的第一个字改为*,四字名字的第二三个字改为*
excel2.write(row,1,st.cell_value(row,2)[0:-1])
temp_college=st.cell_value(row,9)
for keyword in dictionary:
if keyword in temp_college:
excel2.write(row,2,dictionary[keyword])
excel2.write(row,3,st.cell_value(row,7))
excel2.write(row,4,st.cell_value(row,8))
excel2.write(row,5,str(st.cell_value(row,10)[:3])+'****'+str(st.cell_value(row,10)[-4:]))#手机号码的中间四位改为*
if st.cell_value(row,11)=='(空)':
excel2.write(row,6,0)
else:
excel2.write(row,6,1)
words_info = posseg.cut(st.cell_value(row,12))
words_demm = posseg.cut(st.cell_value(row,13))#对他们的介绍和要求进行词性切分
info=''
demm=''
for word, flag in words_info:
if flag in word_type:
info+=word#遍历切分后的每一个词word和他的词性flag,如果词性满足要求,则添加改词
for word, flag in words_demm:
if flag in word_type:
demm+=word
excel2.write(row,7,info)
excel2.write(row,8,demm)
部分相关词性如下:
a | 形容词 |
an | 名形词 |
d | 副词 |
e | 叹词 |
m | 数量词 |
n | 名词 |
ns | 地名 |
nr | 人名 |
nt | 机构名 |
nz | 其他名 |
v | 动词 |
vn | 动名词 |
计算statistic的第3-6列,并写入第三张表作为写入每个学院的数据统计:
excel3=workspace.add_sheet('数据统计',cell_overwrite_ok=True)
excel3.write(0,0,'学院名')
excel3.write(0,1,'报名人数')
excel3.write(0,2,'总占比')
excel3.write(0,3,'男生报名数')
excel3.write(0,4,'男生占比')
excel3.write(0,5,'女生报名数')
excel3.write(0,6,'女生占比')
excel3.write(0,7,'男女比例')
number=1
for college in mapping:
excel3.write(number,0,college)#第三张表写入学院名
boy=statistic[mapping[college],0];#获得该学院男生报名数,之前计算得
girl=statistic[mapping[college],1];
print(college+'总报名人数'+str(boy+girl))
excel3.write(number,1,str(boy+girl))#第二列写入总数
excel3.write(number,3,str(boy))#第四列写入男生人数
excel3.write(number,5,str(girl))#第六列写入女生人数
statistic[mapping[college],2]=round((boy+girl)/st.nrows,5)#储存数据在变量statistic里
print('占'+str(statistic[mapping[college],2]))
statistic[mapping[college],3]=round(boy/191,3)#
statistic[mapping[college],4]=round(girl/384,3)
if girl==0:
girl=1#在计算男女比例时可能出现分母(女生为0)的情况,故设置为1
statistic[mapping[college],5]=round(boy/girl,3)
excel3.write(number,2,str(100*statistic[mapping[college],2])+'%')#分别百分数写入占比
excel3.write(number,4,str(100*statistic[mapping[college],3])+'%')
excel3.write(number,6,str(100*statistic[mapping[college],4])+'%')
excel3.write(number,7,str(statistic[mapping[college],5]))
print('男生占'+str(statistic[mapping[college],3]))
print('女生占'+str(statistic[mapping[college],4]))
print('男女比例'+str(statistic[mapping[college],5]))
number+=1
最后保存表(内含刚刚写入的excel、excel2、excel3)
workspace.save('报名表.xls')
完整代码:
# -*- coding: utf-8 -*-
"""
Created on Sat Nov 16 17:20:04 2019
@author: 71405
"""
import xlrd
import re
import numpy as np
college_name=xlrd.open_workbook(r'collegename.xls')
cn=college_name.sheet_by_index(0)
dictionary=dict()
mapping=dict()
map_number=0
start_row=1;
row=start_row;
while row<cn.nrows:#cn.nrows:行数
wordset=re.split(' ',cn.cell_value(row,1))
for word in wordset:
dictionary[word]=cn.cell_value(row,0)
if cn.cell_value(row,0) not in mapping:
mapping[cn.cell_value(row,0)]=map_number
map_number=map_number+1
row=row+1
signtable=xlrd.open_workbook(r'联谊活动报名表.xls')
st=signtable.sheet_by_index(0)
row=start_row;
statistic=np.zeros([len(mapping),6]);
while row<st.nrows:
college=st.cell_value(row,9);
find=0;
for keyword in dictionary:
if keyword in college:
find=1;
statistic[mapping[dictionary[keyword]],int(st.cell_value(row,7)-1)]+=1
break
if find==0:
print(college+'can not find')
row=row+1
import matplotlib.pyplot as plt
labels = [str(i) for i in range((len(mapping)))]
sizes = [(i[0]+i[1])for i in statistic]
plt.pie(sizes, labels=labels, autopct='%1.1f%%',
shadow=True, startangle=90)
plt.axis('equal')
plt.show()
import jieba.posseg as posseg
import xlwt
workspace=xlwt.Workbook(encoding='ascii')
excel=workspace.add_sheet('报名表完整版',cell_overwrite_ok=True)
excel.write(0,0,'姓名')
excel.write(0,1,'填表时长/s')
excel.write(0,2,'学院')
excel.write(0,3,'性别')
excel.write(0,4,'学历')
excel.write(0,5,'手机号码')
excel.write(0,6,'照片上传情况')
excel.write(0,7,'介绍')
excel.write(0,8,'要求')
for row in range(1,st.nrows):
excel.write(row,0,st.cell_value(row,6))
excel.write(row,1,st.cell_value(row,2)[0:-1])
temp_college=st.cell_value(row,9)
for keyword in dictionary:
if keyword in temp_college:
excel.write(row,2,dictionary[keyword])
excel.write(row,3,st.cell_value(row,7))
excel.write(row,4,st.cell_value(row,8))
excel.write(row,5,st.cell_value(row,10))
if st.cell_value(row,11)=='(空)':
excel.write(row,6,0)
else:
excel.write(row,6,1)
excel.write(row,7,st.cell_value(row,12))
excel.write(row,8,st.cell_value(row,13))
excel2=workspace.add_sheet('报名表简洁版',cell_overwrite_ok=True)
excel2.write(0,0,'姓名')
excel2.write(0,1,'填表时长/s')
excel2.write(0,2,'学院')
excel2.write(0,3,'性别')
excel2.write(0,4,'学历')
excel2.write(0,5,'手机号码')
excel2.write(0,6,'照片上传情况')
excel2.write(0,7,'介绍')
excel2.write(0,8,'要求')
word_type=['n','nz','ns','vn','v','a','an']
for row in range(1,st.nrows):
temp_name=st.cell_value(row,6)
if len(temp_name)==2:
write_name=temp_name[0]+'*'
elif len(temp_name)==3:
write_name=temp_name[0]+'*'+temp_name[2]
else:
write_name=temp_name[0]+'**'+temp_name[3]
excel2.write(row,0,write_name)
excel2.write(row,1,st.cell_value(row,2)[0:-1])
temp_college=st.cell_value(row,9)
for keyword in dictionary:
if keyword in temp_college:
excel2.write(row,2,dictionary[keyword])
excel2.write(row,3,st.cell_value(row,7))
excel2.write(row,4,st.cell_value(row,8))
excel2.write(row,5,str(st.cell_value(row,10)[:3])+'****'+str(st.cell_value(row,10)[-4:]))
if st.cell_value(row,11)=='(空)':
excel2.write(row,6,0)
else:
excel2.write(row,6,1)
words_info = posseg.cut(st.cell_value(row,12))
words_demm = posseg.cut(st.cell_value(row,13))
info=''
demm=''
for word, flag in words_info:
if flag in word_type:
info+=word
for word, flag in words_demm:
if flag in word_type:
demm+=word
excel2.write(row,7,info)
excel2.write(row,8,demm)
excel3=workspace.add_sheet('数据统计',cell_overwrite_ok=True)
excel3.write(0,0,'学院名')
excel3.write(0,1,'报名人数')
excel3.write(0,2,'总占比')
excel3.write(0,3,'男生报名数')
excel3.write(0,4,'男生占比')
excel3.write(0,5,'女生报名数')
excel3.write(0,6,'女生占比')
excel3.write(0,7,'男女比例')
number=1
for college in mapping:
excel3.write(number,0,college)
boy=statistic[mapping[college],0];
girl=statistic[mapping[college],1];
print(college+'总报名人数'+str(boy+girl))
excel3.write(number,1,str(boy+girl))
excel3.write(number,3,str(boy))
excel3.write(number,5,str(girl))
statistic[mapping[college],2]=round((boy+girl)/st.nrows,5)
print('占'+str(statistic[mapping[college],2]))
statistic[mapping[college],3]=round(boy/191,3)
statistic[mapping[college],4]=round(girl/384,3)
if girl==0:
girl=1
statistic[mapping[college],5]=round(boy/girl,3)
excel3.write(number,2,str(100*statistic[mapping[college],2])+'%')
excel3.write(number,4,str(100*statistic[mapping[college],3])+'%')
excel3.write(number,6,str(100*statistic[mapping[college],4])+'%')
excel3.write(number,7,str(statistic[mapping[college],5]))
print('男生占'+str(statistic[mapping[college],3]))
print('女生占'+str(statistic[mapping[college],4]))
print('男女比例'+str(statistic[mapping[college],5]))
number+=1
workspace.save('报名表.xls')
———————————————————————我是分割线————————————————————————————
在进行分析、处理完后,由于报名人数众多,无法一个一个筛选时,又编写了筛选算法:
通过四个条件控制:填表时长、是否上传照片、是否写介绍、是否写要求,一般地,这四个维度的数据就能表明该同学对该活动是否热情、认真,比如某同学用了20s填完,三个可填的都没填,太过于潦草,而有的同学写了很多内容,照片也上传了,填表时长100多秒(数据中最长的填表时间为2600s,大概是20多分钟,一看就认真!)。
import xlrd
import xlwt
workspace=xlwt.Workbook(encoding='ascii')
excel=workspace.add_sheet('报名表筛选版',cell_overwrite_ok=True)
select_list=[1,1,1,1]#分别代表时间/照片/介绍/要求为空时是否筛选
col_num=[1,6,7,8]#四个属性所在列号
time_thre=100#阈值
table=xlrd.open_workbook(r'报名表.xls')#打开之前所写入完成的表的第一张
t=table.sheet_by_index(0)
remain=1#保留的行号
for i in range(t.ncols):#t.ncols:t的列数
excel.write(0,i,t.cell_value(0,i))#老规矩,第一行复制表头
for row in range(1,t.nrows):
state=True#表示是否保留
if select_list[0]==1:
if int(t.cell_value(row,col_num[0]))<time_thre:
state=False#填写时长小于阈值的置为不保留
for i in range(1,4):
if select_list[i]==1:#是否分别开启照片/介绍/要求非空筛选
if t.cell_value(row,col_num[i])=='0' or t.cell_value(row,col_num[i])==0 or t.cell_value(row,col_num[i])=='(空)' or t.cell_value(row,col_num[i])=='无':
state=False#如果对应字符串为无、空、0的任意字符,则不保留
if state:
for i in range(t.ncols):
excel.write(remain,i,t.cell_value(row,i))#如果以上都通过了则写入新表
remain+=1
workspace.save('报名表筛选版.xls')