python处理一个考试成绩

考试是使用问卷星的系统，系统的结果出来以后，下载excel，然后将答案和正确答案匹配，进行阅卷。

记录一下源码，以防以后再用到

import pandas as pd
from pandas import Series,DataFrame
import numpy as np
import os
FILE_PATH = "E:\\work\\03.系统\\4.考试系统"
FILE_NAME = "exam_answer0801.xls"
#FILE_NAME = "Test_exam_answer1.xls"
def loadData(dataPath,dataName):
    fileName = os.path.join(dataPath,dataName)
    print("fileName=%s" % fileName)
    filedata = pd.read_excel(fileName)
    return filedata
file_path = FILE_PATH;
exam_data = loadData(FILE_PATH,FILE_NAME)

MULTI_CHOOSE_COLUMN = ["Order39","Order40","Order41","Order42","Order43","Order44","Order45","Order46","Order47", "Order48"]
SIMPLE_ANSWER_COLUME = ["Order49","Order50","Order51"]
NAMEANDID_COLUME = ["Order52","Order53"]
SIMPLE_RIGHT_ANSWER49 = "应查尽查|不重不漏|工业企业和产业活动单位|规模化畜禽养殖场|集中式污染治理设施|生活源锅炉|排污口|准备|分区|摸底|建库"
SIMPLE_RIGHT_ANSWER50 = "宣传|调查|配合|培训|其他工作|指导|协调|审核|复核|查阅|查看|改正|报告"
SIMPLE_RIGHT_ANSWER51 = "甘肃|单位|个人|工业|农业|生活|集中式污染治理|移动源|污染物"
RIGHT_ANSWER = pd.Series(['错','错','错','对','错','错','对','对','对','对',
                          'C','D','D','D','A','C','D','D','B','D',
                          'C','D','A','B','D','C','C','C','C','C',
                          'ABCD','ABCD','AC','ACD','ABC','ABC','ABC','AC','BCD','ACD'],
                   index=['Order9', 'Order10','Order11','Order12','Order13','Order14','Order15','Order16','Order17','Order18',
                          'Order19','Order20','Order21','Order22','Order23','Order24','Order25','Order26','Order27','Order28',
                          'Order29','Order30','Order31','Order32','Order33','Order34','Order35','Order36','Order37','Order38',
                          'Order39','Order40','Order41','Order42','Order43','Order44','Order45','Order46','Order47','Order48'])
RIGHT_SCORE = pd.Series([1,1,1,1,1,1,1,1,1,1,
                         2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,
                         2,2,2,2,2,2,2,2,2,2],
                         index=['Order9', 'Order10','Order11','Order12','Order13','Order14','Order15','Order16','Order17','Order18',
                         'Order19','Order20','Order21','Order22','Order23','Order24','Order25','Order26','Order27','Order28',
                         'Order29','Order30','Order31','Order32','Order33','Order34','Order35','Order36','Order37','Order38',
                         'Order39','Order40','Order41','Order42','Order43','Order44','Order45','Order46','Order47','Order48'])
WRONG_SCORE = pd.Series([0,0,0,0,0,0,0,0,0,0,
                         0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
                         0,0,0,0,0,0,0,0,0,0],
                         index=['Order9', 'Order10','Order11','Order12','Order13','Order14','Order15','Order16','Order17','Order18',
                         'Order19','Order20','Order21','Order22','Order23','Order24','Order25','Order26','Order27','Order28',
                         'Order29','Order30','Order31','Order32','Order33','Order34','Order35','Order36','Order37','Order38',
                         'Order39','Order40','Order41','Order42','Order43','Order44','Order45','Order46','Order47','Order48'])

#answer-考生答案
#rightAnswer--参考答案
#totalScore--总分数
#rightCount--正确答案参数字数
def simpleAnswer(answer,rightAnswer,totalScore,rightCount):
    #print('--------------------------------')
    #print("answer==%srightAnswer==%stotalScore==%frightCount==%d"%(answer,rightAnswer,totalScore,rightCount))
    rightAnswerSplit = rightAnswer.split("|")
    rightAnswerCount = len(rightAnswerSplit)
    splitScore = totalScore/rightAnswerCount
    #print("split_score==");print(splitScore)
    #print("splitScore==%f"%splitScore)
    score = 0.0
    for ras in rightAnswerSplit:
        if answer.find(ras) > 0:
            score = score + splitScore
    leftMaxScore = totalScore - score
    leftRate = len(answer)/rightCount
    leftRealScore = leftMaxScore*leftRate
    if leftRealScore > leftMaxScore:
        leftRealScore = leftMaxScore
    score = score + leftRealScore
    if score < 3:
        score = 3
    if score >= totalScore:
        score = totalScore - 1
    return score

def simpleAnswerJudge(answer):
    if answer.name == "Order49":
        return simpleAnswer(answer=answer[0],rightAnswer=SIMPLE_RIGHT_ANSWER49,totalScore=10.0,rightCount=200)
    if answer.name == "Order50":
        return simpleAnswer(answer=answer[0],rightAnswer=SIMPLE_RIGHT_ANSWER50,totalScore=10.0,rightCount=200)
    if answer.name == "Order51":
        return simpleAnswer(answer=answer[0],rightAnswer=SIMPLE_RIGHT_ANSWER51,totalScore=10.0,rightCount=200)    

def dealMultiChoose(answer):
    bstr=answer.split('┋')
    lastStr = ''
    for littleStr in bstr:
        lastStr = lastStr + littleStr[0:1]
    return lastStr

def getMultiCap(answer):
    return dealMultiChoose(answer)   
    
def examUserNO(examName):
    if examName in NAMEANDID_COLUME:
        return examName
    
def dealTrim(x):
    if x.name in MULTI_CHOOSE_COLUMN:
        return getMultiCap(x[0])
    elif x.name in SIMPLE_ANSWER_COLUME:
        return simpleAnswerJudge(x)
    elif x.name in NAMEANDID_COLUME:
        return x[0]
    tmp=x[0]
    try:
        return tmp[0:1]
    except Exception as e:
        return tmp

for index2, row2 in exam_data.iterrows():
    a = exam_data.ix[index2]
    b = a.to_dict()
    userNo = b.get('Order52')
    userName = b.get('Order53')
    c=DataFrame(b,columns=['Order1', 'Order2', 'Order3', 'Order4', 'Order5', 'Order6', 'Order7',
       'Order8', 'Order9', 'Order10', 'Order11', 'Order12', 'Order13',
       'Order14', 'Order15', 'Order16', 'Order17', 'Order18', 'Order19',
       'Order20', 'Order21', 'Order22', 'Order23', 'Order24', 'Order25',
       'Order26', 'Order27', 'Order28', 'Order29', 'Order30', 'Order31',
       'Order32', 'Order33', 'Order34', 'Order35', 'Order36', 'Order37',
       'Order38', 'Order39', 'Order40', 'Order41', 'Order42', 'Order43',
       'Order44', 'Order45', 'Order46', 'Order47', 'Order48', 'Order49',
       'Order50', 'Order51', 'Order52', 'Order53'],index=[0])
    d=c.apply(dealTrim)
    simpleAnswerScore1 = d['Order49']
    simpleAnswerScore2 = d['Order50']
    simpleAnswerScore3 = d['Order51']
    exam_score_info=d['Order9':'Order48']
    finalExamBool = ( exam_score_info == RIGHT_ANSWER )
    #print(finalExamBool)
    finalExamScore = np.where(finalExamBool,RIGHT_SCORE,WRONG_SCORE)
    #print(finalExamScore)
    itemScore1 = finalExamScore.sum()
    itemScore = itemScore1 + simpleAnswerScore1 + simpleAnswerScore2 + simpleAnswerScore3
    #print("考生%s 考生编号为%s 考试成绩为%d"%(userName,userNo,itemScore))
    print("考生编号为%s 考试成绩为%d 主观题%d 客观题%d"%(userNo,itemScore,itemScore1,simpleAnswerScore1 + simpleAnswerScore2 + simpleAnswerScore3))