Below is a script to find cheating students in a quiz in a daily moodle activity log exported in .xls. It works fine and is written in a procedural way.
Essentially the script isolates the activity log entry in a worksheet for students listed in another worksheet, identifies when they are taking a quiz (looking ahead in the activity log) and if they are taking an unusually long time or doing something different while taking a quiz (they will complete). These different scenarios are then highlighted in a different row colour in an xl file listing all activity for each student in the list. The script also writes to a .txt file, logging a summary for each student and all quizzes completed and the students listed who did not take any quizzes.
The questions are simple:
1. How can I make it run faster?
2. How can I write it in a OOP paradigm, and will it run faster?
<!--- --->
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# The master data set is contained in excel file ('moodle.xls'). The toponomy for the activity is the one in a 2.5+ moodle instance. The colums in excel follow the format
# columns = module name/date,time/(Not used):e.g. IP address/name student/activity type (full)/activity name
# rows = chronological order in time period
# The class (moodlecheat) returns: a log containing a summary of cheats per students, a set of individual xl files with a log per student with quiz question highlighted.
# Each row is colour coded to facilitate detection: yellow/any valid quiz question answered | light yellow/any question which is answered in more than 2 minutes | grey/any non quiz activity during a quiz | Red/any activity taking longer than 5 minutes during a quiz
# The data listed in a log (log.tx) which details the running times per module and the activity for all students highlighting any suspected cheating
# Worksheet 1 = xl report for moodle activity log for the time period e.g. a day
# Worksheet 2 = Name of users/students whose quiz data is to be mined out
# The typical running time for the class on a 8GB Mac with dual processor OSX 10.9.3 is 31 seconds to find the quiz activity of 120 students for 3 quizzes amongst 12k students records
from mmap import mmap, ACCESS_READ
from xlrd import open_workbook, XL_CELL_TEXT, cellname, cellnameabs, colname
import xlwt
from xlwt import Workbook, easyxf
#from xlutils.styles import Styles
from operator import itemgetter
from time import gmtime, strftime, strptime, clock
from datetime import datetime, timedelta
from xlutils.copy import copy
class moodlecheat(object):
mois={'january':'jan','February':'Feb','March':'Mar','April':'Apr','May':'May','June':'Jun','July':'Jul','August':'Aug','September':'Sep','October':'Oct','November':'Nov','December':'Dec'}
def __init__(self, logfilepath, sourcefilepath):
self.sourcefilepath = sourcefilepath
self.logfilepath=logfilepath
self.studentsentries=[]
self.xlout=[]
self.validquizzes=[]
self.donequizzes=[]
self.listofquizzes=[]
self.inactivestudents=[]
self.activestudents=[]
self.stylo = {1:easyxf('pattern: pattern no_fill;' 'font: name Arial, height 260, italic True'),
2:easyxf('pattern: pattern solid_fill, fore_colour yellow;' 'font: name Arial, height 260'),
3:easyxf('pattern: pattern solid_fill, fore_colour gray50;' 'font: name Arial, height 260'),
4:easyxf('pattern: pattern solid_fill, fore_colour light_yellow;' 'font: name Arial, height 260, bold True'),
5:easyxf('pattern: pattern solid_fill, fore_colour light_orange;' 'font: name Arial, height 260')}
# ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
# 0 1 2 3 4 5
# studentsentries module name date,time IP address name student activity type (full) activity name
# xlout name student, time activity name activity type (short) time since previous question xl file colour row
# valid quizzes name student activity name number of question answered in quiz.
# done quizzes name student activity name
# done quizzes name student activity name
# list of quizzes activity name
# inactivestudents name student
# active students name student flag student taking quiz
# 0 1
# /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
# ------------------------------- Functions -------------------------
# function returning True when a student is taking a quiz
def takingquiz(self,item,series):
position = [(i, name.index(item)) for i, name in enumerate(series) if item in name]
return (series[position[0][0]][(position[0][1])+1])
# function to return a moodle date in date.time/python convention <y=01 m=11 d=1 sec=50 msec=00> <- unicode strings/moodle moodle convention <01 September 2001, 11:50AM>
def udconv(self,datein):
d=datein.split(',')[0]
d1=datein.split(',')[1]
d2=d+d1
d3 = moodlecheat.mois[d2.split(' ')[1]]
d4=d2.split(' ')[0]+' '+d3+' '+d2.split(' ')[2]+' '+d2.split(' ')[3]+d2.split(' ')[4]
d5 = datetime.strptime(d4, '%d %b %Y %I:%M%p')
return d5
# function to calculate between 2 dates in unicode string/moodle log format e.g. <'01 September 2001, 11:50AM'> -> time/python convention
def timegap(self,i,j):
self.gap = self.udconv(i) - self.udconv(j)
return self.gap
# ---------------------------- Methods -----------------------------------------------
def openworkbook(self):
self.wb = open_workbook(self.sourcefilepath+'activitylog.xlsx', on_demand=True)
def extractstudentslist(self):
# Extract the list [activestudents] of all students from the 3rd worksheet (list student names) in moodle.xlsx
self.sh = self.wb.sheet_by_index(1)
self.activestudents = [[x,False] for x in self.sh.col_values(0)]
def extractstudentsentries(self):
# Extract activity from the master dataset <studentsentries> for all the students listed in <activestudents>
self.sh = self.wb.sheet_by_index(0)
for r in range(self.sh.nrows):
if [self.sh.cell_value(r,3),False] in self.activestudents:
self.studentsentries.append(self.sh.row_values(r))
def listquizzes(self):
# list all quizes that were completed into <validquizzes>
for x in self.studentsentries:
if x[4][:10]=='quiz close' and [x[3],x[5]] not in self.validquizzes:
self.validquizzes.append([x[3],x[5],-1])
def flip_quiz(self,item,series):
# method to change the student record to show taking a quiz
position = [(i, name.index(item)) for i, name in enumerate(series) if item in name]
(series[position[0][0]][(position[0][1])+1])=not(series[position[0][0]][(position[0][1])+1])
def logtimestamp(self,message):
# writes <message> in log.txt at sourcefilepath
self.message=message
flog=open (self.logfilepath+'log.txt', "a")
flog.write('\n == '+self.message+ ' at '+strftime("%Y-%m-%d %H:%M:%S", gmtime()))
print self.message
def logmessage(self,message):
# writes <message> in log.txt at logfilepath
self.message=message
flog=open (self.logfilepath+'log.txt', "a")
flog.write('\n'+self.message)
def perstudentquizreport(self):
# writes start and completion times for each students
self.logmessage ('\n\nReport on quizzes')
self.logmessage ('=================\n')
self.oldtime = str('25 April 2014, 11:41 AM')
for m in (sorted(reversed(self.studentsentries), key=itemgetter(3))):
self.sc=self.stylo[1]
# If student starting a quiz and quiz has not been completed yet => log the entries into [quizzdb], log the quizz name into [validquizzes], flip the flag to show student is taking a quiz
if (m[4].split(' (')[0]=='quiz continue attempt' or m[4].split(' (')[0]=='quiz attempt' ) and (self.takingquiz(m[3],self.activestudents)==False) and (any(p[0]==m[3] and p[1]==m[5] for p in self.validquizzes)) and not (any(p[0]==m[3] and p[1]==m[5] for p in self.donequizzes)):
self.flip_quiz(m[3],self.activestudents)
self.logmessage (m[3]+' has made first answer in the quiz '+m[5]+ ' at '+m[1].split(', ')[1])
if (m[4].split(' (')[0]=='quiz continue attempt' or m[4].split(' (')[0]=='quiz attempt' ) and (self.takingquiz(m[3],self.activestudents)==False) and (any(p[0]==m[3] and p[1]==m[5] for p in self.validquizzes)) and (any(p[0]==m[3] and p[1]==m[5] for p in self.donequizzes)):
self.flip_quiz(m[3],self.activestudents)
self.logmessage (m[3]+' retook the quiz '+m[5]+ ' at '+m[1].split(', ')[1]+' - This is an issue if the quiz is not a practice quiz')
elif m[4][:10]=='quiz close' and (any(p[0]==m[3] and p[1]==m[5] for p in self.validquizzes)): # finished a quiz
self.donequizzes.append([m[3],m[5]])
self.flip_quiz(m[3],self.activestudents)
self.logmessage(m[3]+' has finished the quiz '+m[5]+ ' at '+m[1].split(', ')[1])
self.logmessage(('-')*130)
if (self.takingquiz(m[3],self.activestudents)==True):
if m[4][:4]!='quiz': # unusual action during the quiz
self.logmessage('!! -- '+unicode(m[4].split('(')[0])+' '+unicode(m[5])+' at '+unicode(m[1].split(', ')[1]))
self.sc = self.stylo[3]
elif (self.timegap(m[1],self.oldtime))>timedelta(minutes=1): # More than 1 minute for an action
self.sc=self.stylo[4]
else:
self.sc=self.stylo[2]# Answering quiz
if (self.timegap(m[1],self.oldtime))>timedelta(minutes=3): # More than 3 minutes for an action
self.logmessage('!! - Time gap between answers is greater than 3 minutes. '+unicode((self.timegap(m[1],self.oldtime)))+' at '+unicode(m[1].split(', ')[1]))
self.sc = self.stylo[5]
if (m[4].split(' (')[0]=='quiz continue attempt' or m[4].split(' (')[0]=='quiz attempt'): #log the number of questions answered
self.quest=[ l[2] for l in self.validquizzes if l[0]==m[3] and l[1]==m[5] ]
self.validquizzes.remove([m[3],m[5],self.quest[0]])
self.validquizzes.append([m[3],m[5],self.quest[0]+1])
self.xlout.append([m[3], m[1].split(', ')[1], m[5], m[4].split('(')[0], self.timegap(m[1],self.oldtime),self.sc]) #(self.takingquiz(m[3],self.activestudents))
self.oldtime = m[1]
def listallquizzes(self):
# report a list of quizzes
self.logmessage('\nList of quizzes submitted by the students')
self.logmessage(('=')*47)
for i in self.donequizzes:
if i[1] not in self.listofquizzes:
self.listofquizzes.append(i[1])
self.logmessage(i[1])
def listinactivestudents(self):
# report a list of students who didn't take a quiz
self.logmessage('\nList of students who did not take any quiz')
self.logmessage(('=')*37)
self.inactivestudents=[x for x in ([i[0] for i in self.activestudents]) if x not in ([j[0] for j in self.donequizzes])]
for i in self.inactivestudents:
self.logmessage(i)
# ------------------------------------- xl writing ---------------------------------------------
def createallstudentfiles(self):
# Create individual xl files for the students
for self.x in [i for i in self.activestudents if i[0] not in self.inactivestudents]:
self.wpath=self.logfilepath+str(self.x[0])+'.xls'
self.w = xlwt.Workbook(encoding="utf-8") # Creates new workbook for the students
self.sh = self.w.add_sheet('Activity')
self.w.save(self.wpath) # Create all the workbooks for that student
def fillingindividualstudentfiles(self):
# Fill in individual xl files for the students
self.name='empty'
self.row=0
for self.x in (sorted(self.xlout, key=itemgetter(0))):
self.row +=1
# row=(open_workbook(wpath).sheet_by_index(0).nrows) use if studentsentries or xlout is not ordered by name in the loop
for self.y in range(1,5):
self.w.get_sheet(0).write(self.row, self.y-1, str(self.x[self.y]),self.x[5])
self.w.get_sheet(0).col(0).width = 256*19
self.w.get_sheet(0).col(1).width = 256*90
self.w.get_sheet(0).col(2).width = 256*30
self.w.get_sheet(0).col(3).width = 256*30
if self.x[0]!=self.name or self.xlout.index(self.x)==(len(self.xlout)-1): #next student in the list or student last in the list
self.w.save(self.wpath)
self.name=self.x[0]
self.row=0
self.wpath=self.logfilepath+str(self.x[0])+'.xls'
self.w=copy(open_workbook(self.wpath, formatting_info=True, on_demand=True)) #Re-open existing workbook for student and makes a copy in w for editing
def main(self):
self.logtimestamp('Importing the data from the workbook started')
self.openworkbook()
self.logtimestamp('Data uploaded')
self.logtimestamp('Uploading students list')
self.extractstudentslist()
self.logtimestamp('Students list uploaded')
self.logtimestamp('Dumping data in holding table started')
self.extractstudentsentries()
self.logtimestamp('Data dumped ')
self.logtimestamp('Creating a list of quizzes completed started')
self.listquizzes()
self.logtimestamp('List of quizzes completed dumped')
self.logtimestamp('Data processing started')
self.perstudentquizreport()
self.logtimestamp('Data processing complete')
self.logtimestamp('Finding missing students and listing quizzes')
self.listallquizzes()
self.listinactivestudents()
self.logtimestamp('Found missing students and listed quizzes')
self.logtimestamp('Writing individual student xl files')
self.createallstudentfiles()
self.fillingindividualstudentfiles()
self.logtimestamp('Students files written')
moo=moodlecheat ('/Users/macadmin1/Desktop/moodle reports/Reports/','/Users/macadmin1/Desktop/moodle reports/')
moo.main()
Any feedback is welcome, especially about defining data structures and classes that would supplant list. I think I understand (to an extent) how to write a simple procedural script like this one in Python and I really want to move onto OOP now.