§01 期中成绩整理
2021年信号与系统期中考试采取了 考试宝进行课堂练习 方式进行。但是使用考试宝进行测试有以下两方面的问题：
- 考试宝只能接受姓名和手机号，无法接受学号作为标识符；
- 考试宝在考试过程中有的时候会自动退出，所以建议之后的考试宝使用多个题库，每个题库中的题目不超过10个题目；
一、分析考试成绩
利用姓名匹配的方式将考试宝导出的得分汇集到统一的学生成绩表格中。下面是考试宝导出的成绩表格结构：
▲ 图1.1 考试宝导出的EXCEL表格结构
1、将EXCEL转换成LIST
#!/usr/local/bin/python
# -*- coding: gbk -*-
#============================================================
# METEST1.PY -- by Dr. ZhuoQing 2021-07-01
#
# Note:
#============================================================
from headm import *
import inforsub
meorg = tspstring2text("meorg")
listdata = inforsub.showexcel(meorg)
tspsave('melist', listdata=listdata)
printf('\a')
#------------------------------------------------------------
# END OF FILE : METEST1.PY
#============================================================
2、分数统计
（1）原始数据
[41.0, 43.0, 41.0, 38.0, 37.0, 42.0, 34.0, 35.5, 45.5, 36.0, 42.5, 43.0, 47.0, 46.0, 43.5, 42.0, 40.5, 22.5, 46.0, 43.0, 43.0, 46.5, 46.0, 45.0, 42.5, 43.0, 27.5, 48.0, 36.0, 46.0, 47.0, 42.0, 31.0, 41.5, 42.0, 40.5, 44.0, 40.5, 47.0, 33.0, 43.5, 37.5, 42.0, 37.0, 38.5, 26.5, 48.0, 45.0, 38.5, 37.0, 45.0, 39.5, 39.0, 36.0, 46.0, 31.0, 32.5, 44.0, 37.0, 22.5, 41.0, 41.5, 43.0, 46.0, 42.5, 40.0, 43.0, 37.0, 43.0, 36.0, 43.5, 40.0, 40.0, 44.0, 29.0, 35.0, 37.5, 37.5, 40.5, 41.5, 33.0, 32.0, 32.5, 40.0, 46.0, 36.0, 29.5, 41.5, 37.0, 41.0, 34.5, 47.0, 46.0, 43.0, 40.0, 36.0, 46.0, 34.5, 39.0, 44.0, 43.0, 40.0, 41.0, 39.0, 45.0, 44.0, 32.0, 39.5, 39.0, 38.0, 41.5, 40.5, 37.5, 39.0, 39.0, 35.0, 27.0, 42.0, 40.5, 45.0, 30.0, 46.5, 44.0, 36.0, 39.0, 38.0, 40.0, 26.5, 33.5, 35.0, 36.0, 43.0, 36.5, 36.0, 37.5, 26.0, 40.0, 45.0, 38.5, 34.0, 34.5, 35.0, 42.0, 41.0, 45.0, 42.0, 42.0, 43.0, 43.0, 33.0, 40.0, 43.0, 34.5, 42.0, 38.0, 45.0, 46.5, 44.0, 40.0, 39.0, 38.0, 43.0, 42.0, 40.5, 43.0, 34.0, 46.0, 38.0, 23.5, 31.5, 1.0, 0.0, 1.0, 0.0, 0.0, 0.0, 4.0, 1.0, 3.0, 0.5, 37.5, 1.0, 1.0, 1.0, 43.0]
【表1-1 各分数段的人数】
| 0.0-4.8 | 4.8-9.6 | 9.6-14.4 | 14.4-19.2 | 19.2-24.0 | 24.0-28.8 | 28.8-33.6 | 33.6-38.4 | 38.4-43.2 | 43.2-48.0 |
|---|---|---|---|---|---|---|---|---|---|
| 13 | 0 | 0 | 0 | 3 | 5 | 14 | 40 | 72 | 38 |
listdata = tspload('melist', 'listdata')
scoredim = []
namedim = []
for l in listdata:
scoredim.append(float(l[7]))
namedim.append(l[0])
ret = plt.hist(scoredim, 10)
result = '|'.join(['%3.1f-%3.1f'%(s1,s2) for s1,s2 in zip(ret[1][:-1], ret[1][1:])])
result = result + "\n" + '|'.join(['--']*10)
result = result + "\n" + '|'.join(["%d"%s for s in ret[0]])
printf(result)
plt.xlabel("分数段")
plt.ylabel("频次")
plt.grid(True)
plt.tight_layout()
plt.show()
（2）统计分布图
▲ 各个分数段的统计分布
二、合并到最终表格
将上述数据通过姓名匹配的方式合并到最后的表格中。
出现的无关姓名如下：
ERROR: 杨雨翔 ,34.5
ERROR: 卓晴 ,1.0
ERROR: 刘连臣 ,1.0
ERROR: 赵雨晴 ,0.0
ERROR: 叶俊 ,0.0
ERROR: 测试 ,0.0
ERROR: ？ss ,4.0
ERROR: 张东东 ,1.0
ERROR: 卓萍萍 ,3.0
ERROR: 张祖炎 ,0.5
ERROR: 杨牧言 ,37.5
ERROR: 张科雯 ,1.0
ERROR: 张桌子 ,1.0
ERROR: 张几十块 ,1.0
ERROR: 1 ,43.0
#!/usr/local/bin/python
# -*- coding: gbk -*-
#============================================================
# METEST1.PY -- by Dr. ZhuoQing 2021-07-01
#
# Note:
#============================================================
from headm import *
import pandas as pd
listdata = tspload('melist', 'listdata')
resultfile = tspstring2text('meresult')
printf(resultfile)
#------------------------------------------------------------
def setexcelcellstring(filename, sid, col, num):
excelfile = pd.read_excel(filename, sheet_name='sheet1')
rowid = -1
sid = sid.strip(' ')
for id, content in excelfile.items():
if id == '姓名':
content = [str(s) for s in content]
for idd,s in enumerate(content):
if s.find(sid) >= 0:
rowid = idd
break
if rowid >= 0: break
if rowid < 0:
return rowid
excelfile[col] = excelfile[col].astype(str)
excelfile.at[rowid, col] = num
excelfile.to_excel(filename, sheet_name='sheet1', index=False)
return rowid
#------------------------------------------------------------
error = 0
for l in listdata:
name = l[0]
score = l[7]
ret = setexcelcellstring(resultfile, name, '分数', score)
if ret < 0:
error += 1
printf("ERROR:%s,%s"%(name, score))
# break
printf('\a')
#------------------------------------------------------------
# END OF FILE : METEST1.PY
#============================================================
§02 课堂交互成绩
一、课堂交互信息
课堂交互信息记录在TEASOFT服务器中的如下目录中：
F:\Teaching\ServerDatabase\name
主要的记录文件为： PHONENAME_DATA.txt。它的主要结构为：
江群 01055008217 16 3 11 10 10 0 0 631 5 0 0 0 0 0 0 0 0 0 0 0
邢森 04024822972 23 2 11 10 10 0 0 685 10 0 0 0 0 0 0 0 0 0 0 0
张世乙 02991576239 13 0 4 3 3 0 1 90 3 1 0 0 0 0 0 0 0 0 0 0
陈星 03735025025 12 0 11 10 10 0 0 585 6 0 0 0 0 0 0 0 0 0 0 0
李可伊 04200304045 14 0 12 12 12 0 3 610 5 0 0 0 0 0 0 0 0 0 0 0
袁梦 08833104544 14 1 11 10 10 0 1 463 6 1 0 0 0 0 0 0 0 0 0 0
以上各个结构定义为：
【表2-1 课堂记录文件结构】
| 信息1 | 信息2 | 信息3 | 信息4 | 信息5 | 信息6 | 信息7 | 信息8 | 信息9 | 信息10 |
|---|---|---|---|---|---|---|---|---|---|
| 信息 | 图片 | 问题 | 正确 | 分数 | 标号 | 奖励 | 时间 | 查询 | 检索 |
因此，课堂分数是将信息4，信息5，信息7 相加在一起。
二、成绩转换
参照 2020春季学期信号与系统课堂参与信息处理 中处理相关程序，对今年课堂交互文档进行处理。
1、转换程序
#!/usr/local/bin/python
# -*- coding: gbk -*-
#============================================================
# INTERTEST.PY -- by Dr. ZhuoQing 2021-07-02
#
# Note:
#============================================================
from head import *
datafile = tspstring2text('datatxt')
interfile = tspstring2text('inter')
printf(datafile, interfile)
namedim = []
scoredim = []
with open(datafile, 'r', encoding='gbk') as f:
for l in f.readlines():
lsect = l.rstrip('\n').split()
score = int(lsect[5]) + int(lsect[6]) + int(lsect[8])
scoredim.append(score)
namedim.append(lsect[0])
printf(l)
tspsave('classdata', name=namedim, score=scoredim)
printf(namedim, scoredim)
printf
#------------------------------------------------------------
# END OF FILE : INTERTEST.PY
#============================================================
2、转换结果
总人数：164
name=['王洹芊','党添添','陈天瑞','尹子康','江群','邢森','张世乙','陈星','李可伊','袁梦','陈冠宇','张云帆','袁彦龙','王啸轩','徐赫临','明陈林','李永健','余珮琪','黄语萱','吴征鸿','左思成','王浩驰','康宇琦','蔡頌濠','曹健博','杨雨翔','金理想','邢海潼','吕光冉','黄正超','曹哲暄','王韬钧','杨思行','杨凌晓','周建东','黄嘉诚','栾垚','吕梦欣','雍子隆','张凯','尹航','闫梦蓓','邱亦文','苍岳洋','褚驰','王少儒','郑阳','刘逸凯','孙熙凯','杜邦得','于端瑞','刘梓哲','杨一凡','任坤','赖正宇','李俊杰2019011369','梁亨源','陈文泽','王与时','温昊','刘楠','仝牧坤','柴睿','瞿近远','张一博','储勇','武可雯','张卓宇','李若然','昕琦','陈洋','陈禹为','张睿喆','吕畅祺','谭龙涛','王觉','曲世远','李述','姜永鹏','杨宇新','彭昱玚','胡鑫豪','闫泽','杨小诺','何杰铃','杨鼎','张帅','韩东辰','牟准','关博文','陈吉坤','晏国凯','雍征彼','袁铭','许伟超','盘卓实','周义函','杨珂凡','夏星鹏','宋晓斌','高瞻远','刘康城','魏冰川','刘祖炎','左思清','张启航','黄彦彬','姜芯雨','陈卓明','李子韩','沈念辰','张智凯','武昊','时清扬','何琳欣','卢逸名','刘雪洁','姚泊先','宋雨璠','于江伟','孟子斌','王博闻','杨紫妍','付迪帆','蔡漫锋','张博轩','周佳祺','魏浩','邓皓天','徐诣堡','郑姝榕','陈祥艺','姚祎宁','金熠琦','仇铮','李浦豪','周亦哲','骆科燚','孙超','陈华清','李晨韬','吴晔','邢旺','王可','陈浩','何东阳','潘文凯','钟毅','叶金涛','周耀','李亮龙','贾宇飞','谷彧潇','王逸钦','曹展翔','徐昕翃','谭跳','谢宇轩','尹鹏','赵健桐','冉靖尧','张仁刚','周思维','黄昭源']
score=[19.00,14.00,6.00,7.00,20.00,20.00,7.00,20.00,27.00,21.00,13.00,25.00,23.00,11.00,20.00,14.00,23.00,23.00,21.00,10.00,18.00,8.00,12.00,22.00,14.00,6.00,7.00,21.00,12.00,15.00,13.00,23.00,18.00,22.00,22.00,20.00,20.00,20.00,20.00,10.00,22.00,14.00,21.00,20.00,16.00,6.00,12.00,22.00,21.00,7.00,25.00,12.00,16.00,16.00,16.00,14.00,21.00,15.00,22.00,15.00,16.00,21.00,7.00,21.00,20.00,12.00,19.00,22.00,18.00,6.00,17.00,13.00,25.00,20.00,19.00,20.00,22.00,18.00,21.00,20.00,19.00,18.00,22.00,7.00,17.00,14.00,7.00,22.00,18.00,23.00,15.00,18.00,18.00,21.00,20.00,20.00,23.00,18.00,23.00,22.00,14.00,20.00,7.00,21.00,17.00,11.00,13.00,23.00,18.00,6.00,22.00,23.00,20.00,15.00,15.00,21.00,6.00,10.00,18.00,16.00,20.00,7.00,20.00,6.00,21.00,6.00,18.00,23.00,21.00,21.00,20.00,23.00,8.00,20.00,20.00,16.00,14.00,18.00,8.00,4.00,14.00,4.00,6.00,14.00,14.00,4.00,6.00,6.00,14.00,8.00,14.00,16.00,0.00,0.00,12.00,12.00,10.00,6.00,6.00,14.00,0.00,8.00,4.00,0.00]
3、结果统计
-
得分结果统计：
-
平均分：15.5
方差：39.43
▲ 得分统计分布
可以看到课堂参与的得分差别很大，所以这一项的在最终成绩评定中所占的比例缩小到5%。
三、写入标准表格
1、写入程序
#!/usr/local/bin/python
# -*- coding: gbk -*-
#============================================================
# INTERSTAT.PY -- by Dr. ZhuoQing 2021-07-02
#
# Note:
#============================================================
from headm import *
name, score = tspload('classdata', 'name', 'score')
import pandas as pd
resultfile = tspstring2text('inter')
printf(resultfile)
#------------------------------------------------------------
def setexcelcellstring(filename, sid, col, num):
excelfile = pd.read_excel(filename, sheet_name='sheet1')
rowid = -1
sid = sid.strip(' ')
for id, content in excelfile.items():
if id == '姓名':
content = [str(s) for s in content]
for idd,s in enumerate(content):
if s.find(sid) >= 0:
rowid = idd
break
if rowid >= 0: break
if rowid < 0:
return rowid
excelfile[col] = excelfile[col].astype(str)
excelfile.at[rowid, col] = num
excelfile.to_excel(filename, sheet_name='sheet1', index=False)
return rowid
#------------------------------------------------------------
error = 0
for id,name in enumerate(name):
ss = score[id]
ret = setexcelcellstring(resultfile, name, '分数', ss)
if ret < 0:
error += 1
printf("ERROR:%s,%s"%(name, ss))
printf('\a')
#------------------------------------------------------------
# END OF FILE : INTERSTAT.PY
#============================================================
2、写入结果
出现了三个姓名匹配错误：
ERROR:蔡頌濠,22
ERROR:杨雨翔,6
ERROR:李俊杰2019011369,14
写入之后总共有12个人得分为0.
strid = 22
strall = [s.split() for s in tspgetdopstring(strid).split('\r\n') if len(s) > 0]
zeros = [s[0]+':'+s[1] for s in strall if float(s[1]) == 0]
printf(zeros)
printf(len(zeros))
['冉靖尧:0.0', '黄昭源:0', '刘毓灵:0', '陈景浩:0', '黄哲:0', '王逸钦:0.0', '李俊杰:0', '刘禹辰:0', '谷彧潇:0.0', '蔡颂濠:0', '付欣旺:0', '王献文:0']
※ 分析结果 ※
1、INFORSUB附件程序
#!/usr/local/bin/python
# -*- coding: gbk -*-
#============================================================
# INFORSUB.PY -- by Dr. ZhuoQing 2020-06-18
#
# Note:
#============================================================
from headm import *
import pandas as pd
#------------------------------------------------------------
def showexcel(filename):
excelfile = pd.read_excel(filename, sheet_name="sheet1")
listdata = excelfile.values.tolist()
strall = ''
for id,l in enumerate(listdata):
printff(id, l)
return listdata
def showexcelinfor(filename, sid):
excelfile = pd.read_excel(filename, sheet_name='sheet1')
rowid = -1
for id, content in excelfile.items():
if id == '学号':
content = [str(s) for s in content]
if str(sid) in content:
rowid = content.index(str(sid))
break
if rowid < 0:
return rowid
printf(excelfile.values.tolist()[rowid])
return rowid
#------------------------------------------------------------
def setexcelcellstring(filename, sid, col, num):
excelfile = pd.read_excel(filename, sheet_name='sheet1')
rowid = -1
for id, content in excelfile.items():
if id == '学号':
content = [str(s) for s in content]
for idd,s in enumerate(content):
if s.find(sid) >= 0:
rowid = idd
break
if rowid >= 0: break
if rowid < 0:
return rowid
excelfile[col] = excelfile[col].astype(str)
excelfile.at[rowid, col] = num
excelfile.to_excel(filename, sheet_name='sheet1', index=False)
return rowid
#------------------------------------------------------------
def appendexcelcellstring(filename, sid, col, num):
excelfile = pd.read_excel(filename, sheet_name='sheet1')
rowid = -1
for id, content in excelfile.items():
if id == '学号':
content = [str(s) for s in content]
if str(sid) in content:
rowid = content.index(str(sid))
break
if rowid < 0:
return rowid
printff(rowid, col)
excelfile[col] = excelfile[col].astype(str)
if excelfile.at[rowid, col] != 'nan':
excelfile.at[rowid, col] = num + ', ' + excelfile.at[rowid, col]
else: excelfile.at[rowid, col] = num
if len(num) == 0: excelfile.at[rowid, col] = ''
excelfile.to_excel(filename, sheet_name='sheet1', index=False)
return rowid
#------------------------------------------------------------
def getexcelcellstring(filename, sid, col):
excelfile = pd.read_excel(filename, sheet_name='sheet1')
rowid = -1
for id, content in excelfile.items():
if id == '学号':
content = [str(s) for s in content]
if str(sid) in content:
rowid = content.index(str(sid))
break
if rowid < 0:
return rowid
# printff(rowid, col)
# excelfile[col] = excelfile[col].astype(str)
return excelfile.at[rowid, col]
#------------------------------------------------------------
if __name__ == "__main__":
# setexcelcellstring(tshfile, '2020210975', 'HW1', 88)
# s = getexcelcellstring(tshfile, '2020210975', 'HW1')
# printf(s)
# showexcel(shzhfile)
x=showexcelinfor(shzhfile, '2020280355')
printf(x)
#------------------------------------------------------------
# END OF FILE : INFORSUB.PY
#============================================================
扫一扫
1583
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
