*** Socrative responses to Canvas grade book

Save all socrative report as xlsx files.

Rename all report files using the dates.

Using a Jupyter notebook to analyze NetworkdID in each file, summarize them in Pandas dataframe, output to Excel file.

Create assignment on Canvas for attendance by dates. (This step was skipped on 20190220).
Then Export gradebook from Canvas as csv.

Update the grade csv files. (Make sure the point row was preserved. This can be done to sort the grade on a separate sheet, and then copy the sorted and merged grade back).

Upload grade csv files to Canvas gradebook. Visually check some changes. Wait for grades change to be updated.

In [1]:

import os
import pandas as pd
debug = 1

load class roster from gradebook

In [2]:

mygradebook = '../grades/2019-01-26T2215_Grades-Data_Structures_and_Program_Design.csv'

In [3]:

import csv
#with open(mygradebook, newline='') as csvfile:
#    grades = csv.reader(csvfile, delimiter=' ', quotechar='|')
csvfile = open(mygradebook, newline='')
grades = csv.reader(csvfile)
name2id = {}
id2name = {}
for row in grades:
        print(row[0] +  "\t\t" + row[2])
        if (row[0] != "Student") & (row[0] != "Test Student") & (row[0] !="    Points Possible"):
            name2id[row[0]] = row[2]
            id2name[row[2]] = row[0]

In [5]:

id2name

Out[5]:

In [6]:

grades.line_num

Out[6]:

27

In [7]:

len(id2name)

Out[7]:

24

In [8]:

id2attendance = {} #attendance dictionary

In [9]:

report_df = pd.DataFrame(pd.Series(id2name), columns=["name"])
report_df

Out[9]:

read socrative reports

In [10]:

os.chdir("/Users/hqin/github/socrative/Socrative")
mypath = 'cpsc1110Sp2019_cleaned'
filenames = os.listdir(mypath) # returns list
filenames.sort()
print(filenames)

['01_08_2019.xlsx', '01_10_2019.xlsx', '01_15_2019.xlsx', '01_17_2019.xlsx', '01_22_2019.xlsx', '01_24_2019.xlsx']

In [11]:

from openpyxl import load_workbook
for i in range(len(filenames)): 
#for i in range(0,1): 
    myfilename = filenames[i]
    mycolumnlabel = myfilename.replace(".xlsx","")
    if (debug > 0):
                print("i=" , i , "myfilename =" , myfilename )
    wb = load_workbook(filename = mypath + "/" + myfilename)
    ws = wb["Sheet1"]

    unqiue_netids = set()
    for i in range(1,50):
        current_value = ws.cell(row=i, column=5).value
        if type(current_value) == type("x"):
            current_value = current_value.upper().replace(' ','') #remove white spaces
            #if (debug > 0):
                #print( current_value )
            unqiue_netids.add(current_value)
    
    for id in id2name: 
        if id in unqiue_netids: 
            id2attendance[ id ] =  1
        else:
            id2attendance[ id ] =  0   
    
    print(id2attendance)

    report_df[mycolumnlabel] = pd.Series(id2attendance,index=id2attendance.keys() )

In [12]:

report_df

Out[12]:

In [13]:

report_df.to_excel("socrative_summary.xlsx", sheet_name='scorative_report', index_label ="netid")  # doctest: +SKIP

In [ ]: