待解决问题:
要对900+份问卷的4个问题进行分数统计,有规定的量化标准。
直接输出到excel里。
代码:
#提取excel中特定列的数据并且计算个数,得到分数值。
import pandas as pd
import numpy as np
import xlsxwriter as xlwt
import openpyxl
#输入数字,判断对应的题号及加分数并返回
def count_score(i,x):
ex_score=0
if i==79:
if (50<x & x<=100):
ex_score=ex_score+5
elif 100<x & x<=200:
ex_score=ex_score+10
elif 200 < x :
ex_score=ex_score+15
elif i==80:
x = str(x)
if x == '是的,经常长于安排时间' :
ex_score+=20
elif x =='我做事一向准时':
ex_score += 70
elif x =='我常常在期限前完成工作':
ex_score += 90
elif i==81:
x = str(x)
if x == '好' :
ex_score+=90
elif x =='一般':
ex_score += 60
elif x =='差':
ex_score += 20
elif i == 82:
x = str(x)
if x == '是' :
ex_score+=60
elif x =='否':
ex_score=0
return ex_score
def main():
ex_scores_79=[]
ex_scores_80 = []
ex_scores_81 = []
ex_scores_82 = []
path = r'C:\Users\83543\Desktop\dataset.xlsx'
temp_data = pd.read_excel(path, sheet_name='六、主观自我评价', engine='openpyxl')
#print(temp_data)
#提取对应列的数据
Closewindows_79 = temp_data['79、请用一段话对自己进行主观评价,包括但不限于:性格、优缺点、兴趣特长等。']
Closewindows_80 = temp_data['80、你的工作总是会比预计的或安排的花费时间长吗?']
Closewindows_81 = temp_data['81、你认为你的抗压能力如何']
Closewindows_82 = temp_data['82、“既然决定做一件事情,就要把它做好。”你相信这句话吗?']
#遍历提取出来的数据,记录每一个数据的字符数并且调用加分函数。
for i, t_str in enumerate(Closewindows_79):
str_len = len(str(t_str))
ex_score=count_score(79,str_len)
#print(i) 序号是0-936一共937个数据。
ex_scores_79.append(ex_score)
for i, t_str in enumerate(Closewindows_80):
ex_score=count_score(80,t_str)
ex_score=ex_score*0.2438
ex_scores_80.append(ex_score)
for i, t_str in enumerate(Closewindows_81):
ex_score=count_score(81,t_str)
ex_score=ex_score*0.3
ex_scores_81.append(ex_score)
for i, t_str in enumerate(Closewindows_82):
ex_score=count_score(82,t_str)
ex_score=ex_score*0.2375
ex_scores_82.append(ex_score)
#写入
workbook = openpyxl.load_workbook(path)
worksheet = workbook.worksheets[5]
#遍历每一列,把第1组-第2组评分进行修改。其中,第五列的79问题是只算了字数的,第六列是80-82是最终比例分数之和。
for index, row in enumerate(worksheet.rows):
#修改第五列,4.第一行不管.index从0-937.list从0-936
#print(index)
if index!=0:
row[5].value=ex_scores_79[index-1]
row[6].value=ex_scores_80[index-1]+ex_scores_81[index-1]+ex_scores_82[index-1]
workbook.save(path)
if __name__ == '__main__':
main()