def splitCSV(): #get course ID and token courseID = input("\nEnter Course ID: ").strip() try: #gather data from the Canvas API assignmentGroups = api.getAssignmentGroups(courseID) courseDetails = api.getCourseDetails(courseID) courseSections = api.getCourseSections(courseID) courseName = courseDetails.get('name') courseCode = courseDetails.get('course_code') numSections = len(courseSections) decision = input( "\nFound course \"" + courseName + "\". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() while decision.lower() == "change": courseID = input("\nEnter Course ID: ").strip() assignmentGroups = api.getAssignmentGroups(courseID) courseDetails = api.getCourseDetails(courseID) courseSections = api.getCourseSections(courseID) courseName = courseDetails.get('name') numSections = len(courseSections) decision = input( "\nFound course " + courseName + ". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() #read full CSV export while True: title = input( "\nMake sure the exported Canvas Gradebook CSV file is in the same directory as this script. Enter the name of the file, including the extension (.csv), and then press Enter: " ) try: df = pandas.read_csv(title) break except FileNotFoundError: decision = input( "\nFile not found. Make sure you have placed the exported Canvas Gradebook CSV file in the same directory as this script and that you typed the filename correctly, including the extension (.csv). Hit Enter to try again or type 'quit' to exit: " ) if decision.lower() == "quit": return #if there were muted assignments, remove the extra row that was generated for column in df.columns: if df[column].astype(str).str.contains('Muted').any(): df = df.iloc[1:] break #check that the spreadsheet contains the Lab column, which is required for this script to work if 'Lab' not in df.columns: input( 'Your course does not have the lab column in the Gradebook, therefore this script cannot work. Hit ENTER to close the program:' ) return #delete undesired columns (by default, only columns required for a successful import back into Canvas are kept) if 'ID' in df.columns: del df['ID'] if 'SIS Login ID' in df.columns: del df['SIS Login ID'] if 'Notes' in df.columns: del df['Notes'] if 'Student Number' in df.columns: del df['Student Number'] if 'Lecture' in df.columns: del df['Lecture'] if 'Tutorial' in df.columns: del df['Tutorial'] if 'Current Points' in df.columns: del df['Current Points'] if 'Final Points' in df.columns: del df['Final Points'] if 'Current Grade' in df.columns: del df['Current Grade'] if 'Unposted Current Grade' in df.columns: del df['Unposted Current Grade'] if 'Final Grade' in df.columns: del df['Final Grade'] if 'Unposted Final Grade' in df.columns: del df['Unposted Final Grade'] if 'Current Score' in df.columns: del df['Current Score'] if 'Unposted Current Score' in df.columns: del df['Unposted Current Score'] if 'Final Score' in df.columns: del df['Final Score'] if 'Unposted Final Score' in df.columns: del df['Unposted Final Score'] #loop removes all of the "read only" columns that are generated for each assignment group for i in range(0, len(assignmentGroups)): if assignmentGroups[i]['name'] + ' Current Points' in df.columns: del df[assignmentGroups[i]['name'] + ' Current Points'] if assignmentGroups[i]['name'] + ' Final Points' in df.columns: del df[assignmentGroups[i]['name'] + ' Final Points'] if assignmentGroups[i]['name'] + ' Current Score' in df.columns: del df[assignmentGroups[i]['name'] + ' Current Score'] if assignmentGroups[i][ 'name'] + ' Unposted Current Score' in df.columns: del df[assignmentGroups[i]['name'] + ' Unposted Current Score'] if assignmentGroups[i]['name'] + ' Final Score' in df.columns: del df[assignmentGroups[i]['name'] + ' Final Score'] if assignmentGroups[i][ 'name'] + ' Unposted Final Score' in df.columns: del df[assignmentGroups[i]['name'] + ' Unposted Final Score'] #build CSV output files for size in range(0, numSections): #the last 3 characters of the section usually contains the lab info (e.g. for section CHEM 111 L01, L01 is what we want to match in the lab column) name = courseSections[size]['name'][-3:] #don't create a file for the students that are exempt from the labs if "XMT" in name: continue else: #create a folder for the output files in the same directory as script path = os.getcwd() directory = path + "/" + courseCode + "/" os.makedirs(os.path.dirname(directory), exist_ok=True) #separate the files based on lab section df2 = df.loc[df['Lab'] == name] #add the points possible row back in pandas.concat([df2[:0], df.iloc[0, :].to_frame().T, df2[0:]], ignore_index=True).to_csv( directory + courseCode + " " + name + ".csv", encoding="utf-8", index=False) input( "\nSuccess! CSV files for each lab section have been created and can be found in a folder titled " + courseCode + " in the same directory as this script. Hit enter or close this window to exit:" ) except: decision = input( "\nSomething went wrong. Make sure you have added your token to the canvas.cfg file and that you entered the correct course ID. Hit Enter to restart the program or type 'quit' to exit: " ) if decision.lower() == "quit": return else: splitCSV()
def DownloadDiscussion(): #get course ID courseID = input("\nEnter Course ID: ").strip() #Try to read the file (if the user wants to quit, the title will be 'quit') try: #api calls to gather course and student information courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') decision = input( "\nFound course \"" + courseName + "\". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() #if the course doesn't exist, either let the user reenter or quit the program while decision.lower() == "change": courseID = input("\nEnter Course ID: ").strip() courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') decision = input( "\nFound course " + courseName + ". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() courseStudents = api.getCourseStudents(courseID) studentList = {} for student in courseStudents: studentList[student['name']] = student['sis_user_id'] try: topicID = input("\nPlease Enter Topic ID: ").strip() topicDetails = api.getSingleTopic(courseID, topicID) #Enforces FIPPA compliance fippaDecision = input( "Do you wish to have Student Name and ID showing (yes/no, default=yes): " ).strip() #This will clean the input from the user. Using in function any combination of 'y', 'e', 's', or '' will be run yes and 'n' and 'o' will set to no if fippaDecision in 'yes': fippaDecision = 'yes' elif fippaDecision in 'no': fippaDecision = 'no' else: #If they don't enter any character of '', 'y', 'e', 's', 'n', or 'o' it will repeat asking the user for input while (fippaDecision not in 'yes') and (fippaDecision not in 'no'): fippaDecision = input( "Do you wish to have Student Name and ID showing (yes/no): " ).strip() if fippaDecision in 'yes': fippaDecision = 'yes' elif fippaDecision in 'no': fippaDecision = 'no' try: outputFile = FIPPA(fippaDecision, topicDetails, studentList) try: title = input( "\nPlease enter what you would like to name the completed file, including the extension (.csv), and then press Enter (or leave empty for " + courseName + "_DiscussionDownload.csv): ").strip() if (title in ""): title = courseName + '_DiscussionDownload.csv' #If user forgets to add .csv system will add it in here if title.endswith('.csv'): pass else: print( "\nIt seems like you forgot to add the .csv extension. Don't worry I've added it for you!" ) title = os.path.splitext(title)[0] + '.csv' #If the title is somehow blank or already take, ask user to re-enter name and loop until its a valid name if (os.path.isfile(title) or not title or not title.endswith('.csv') or title == '.csv'): while (os.path.isfile(title) or not title or not title.endswith('.csv') or title == '.csv'): if title == 'quit': return else: title = loopFileName(courseName) #Creates a csv with the given title name and tell user that the file has been created! outputFile.to_csv(title, encoding="utf-8", index=False) input( "\nSuccess! CSV file named: " + title + " has been successfully created in the same directory as this script. Hit enter or close this window to exit:" ).strip() #Raise error if issue while naming and creating file except Exception as e: print(e) decision = input( "\nThere was an issue while attempting to name and/or creating the file. Please contact the CTL for assitional assistance. Hit Enter to restart the program or type 'quit' to exit: " ) if decision.lower() == "quit": return else: DownloadDiscussion() #Raise error if problem creating the digital file except Exception as e: print(e) decision = input( "\nThere was an issue while attempting to create the outputted file. Please contact the CTL for assitional assistance. Hit Enter to restart the program or type 'quit' to exit: " ) if decision.lower() == "quit": return else: DownloadDiscussion() #If there is an issue with the discussion, ask users to try again except Exception as e: print(e) decision = input( "\nThere was an error attempting to connect to your discussion. Please check to make sure you have the correct discussion ID and try again. Hit Enter to restart the program or type 'quit' to exit: " ) if decision.lower() == "quit": return else: DownloadDiscussion() #If there is an issue with API or token, ask user to check except Exception as e: print(e) decision = input( "\nSomething went wrong. Perhaps you entered an invalid Canvas API Access Token or Course ID? Hit Enter to restart the program or type 'quit' to exit: " ) if decision.lower() == "quit": return else: DownloadDiscussion()
def CommentExport(): #get course ID courseID = input("\nEnter Course ID: ").strip() try: #api calls to gather course and student information courseStudents = api.getCourseStudents(courseID) courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') courseAssignments = api.getAssignments(courseID) #Creates a dictionary of every student and prepares it for the addition of their commented assignment (basically reorganize) assignmentByStudent = {} for student in courseStudents: assignmentByStudent[student.get('name')] = { "studentName": student.get('name'), "studentID": student.get('id') } decision = input( "\nFound course \"" + courseName + "\". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() while decision.lower() == "change": courseID = input("\nEnter Course ID: ").strip() courseStudents = api.getCourseStudents(courseID) courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') decision = input( "\nFound course " + courseName + ". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() #For each assignment, check to see if it has a comment and if it does give the corresponding student in "assignmentByStudent"the name of the assignment and it's comments for assignment in courseAssignments: courseSubmissions = api.getAssignmentSubmission( courseID, assignment.get('id')) for submission in courseSubmissions: if submission.get('submission_comments'): assignmentByStudent[submission.get('user').get('name')][ assignment.get('name')] = [] for comment in submission.get('submission_comments'): assignmentByStudent[submission.get('user').get( 'name')][assignment.get('name')].append( comment.get('comment')) spreadsheet = pandas.DataFrame.from_dict(assignmentByStudent, orient='index') #Ask user to enter name of prefered csv output (if blank uses their course name _GroupList.csv) title = input( "\nPlease enter what you would like to name the completed file, including the extension (.csv), and then press Enter (or leave empty for " + courseName + "_AllComments.csv): ").strip() if (title in ""): title = courseName + '_AllComments.csv' #If user forgets to add .csv system will add it in here if title.endswith('.csv'): pass else: print( "\nIt seems like you forgot to add the .csv extension. Don't worry I've added it for you!" ) title = os.path.splitext(title)[0] + '.csv' #If the title is somehow blank or already take, ask user to re-enter name and loop until its a valid name if (os.path.isfile(title) or not title or not title.endswith('.csv') or title == '.csv'): while (os.path.isfile(title) or not title or not title.endswith('.csv') or title == '.csv'): if title == 'quit': return else: title = loopFileName(courseName) #Creates a csv with the given title name and tell user that the file has been created! spreadsheet.to_csv(title, encoding="utf-8", index=False) input( "\nSuccess! CSV file named: " + title + " has been successfully created in the same directory as this script. Hit enter or close this window to exit:" ).strip() except Exception as e: print(e) decision = input( "\nSomething went wrong. Perhaps you entered an invalid Canvas API Access Token or Course ID? Hit Enter to restart the program or type 'quit' to exit: " ) if decision.lower() == "quit": return else: CommentExport()
def formatCSV(): #get course ID courseID = input("\nEnter Course ID: ").strip() try: #api calls to gather course and student information courseStudents = api.getCourseStudents(courseID) courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') decision = input("\nFound course \"" + courseName + "\". Hit Enter if this is the correct course or type 'change' to select a different course: ").strip() while decision.lower() == "change": courseID = input("\nEnter Course ID: ").strip() courseStudents = api.getCourseStudents(courseID) courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') decision = input("\nFound course " + courseName + ". Hit Enter if this is the correct course or type 'change' to select a different course: ").strip() #lists for storing student information studentNumbers = [] studentNames = [] studentIDs = [] #store necessary data for each student in the course for student in courseStudents: studentNumbers.append(student.get('sis_user_id')) studentNames.append(student.get('name')) studentIDs.append(student.get('id')) #read the OMR CSV file while True: title = input("\nEnsure the OMR CSV file is in the same location as this script. Enter the name of the file (including the extension .csv) and then press Enter: ") try: df = pandas.read_csv(title) break except FileNotFoundError: decision = input("\nFile not found. Make sure you have placed the OMR CSV file in the same location as this script and that you typed the filename correctly (including the extension .csv). Hit Enter to try again or type 'quit' to exit: ") if decision.lower() == "quit": return #handles any blank cells df.fillna("N/A", inplace=True) assignmentTitle = input("\nType in the desired name for this assessment: ") pointsPossible = input("Type in the points total for this assessment: ") #get the data columns from the OMR CSV file and perform some data cleansing/validation studentID_column = df['Student ID'] for number in range(0, len(studentID_column.values)): if isinstance(studentID_column.values[number], str): studentID_column.values[number] = re.sub("[^0-9]", "", str(studentID_column.values[number])) else: studentID_column.values[number] = re.sub("[^0-9]", "", str(int(studentID_column.values[number]))) if studentID_column.values[number] == "": studentID_column.values[number] = 0 if 'First Name' in df.columns and 'Last Name' in df.columns: firstName_column = df['First Name'] lastName_column = df['Last Name'] if 'Percent Score' in df.columns: percent_column = df['Percent Score'] scores_column = df['Total Score'] #lists that will contain the column data for the output CSV file outputIDs = [] outputNames = ["Student", " Points Possible"] outputNumbers = ["SIS User ID", ""] outputSections = ["Section", ""] outputPercent = ["" + assignmentTitle + " (Percent)", 100] outputScores = [assignmentTitle, pointsPossible] #lists that will contain the data for the error log errorNum = [] errorFName = [] errorLName = [] errorScore = [] errorPercent = [] #match student numbers from the OMR file to student numbers from the course and build output accordingly for student in range(0, len(studentID_column.values)): for number in range(0, len(studentNumbers)): if str(int(studentID_column.values[student])) == str(studentNumbers[number]): outputIDs.append(studentIDs[number]) outputNames.append(studentNames[number]) outputNumbers.append(studentNumbers[number]) outputSections.append(courseName) outputScores.append(scores_column[student]) if 'Percent Score' in df.columns: outputPercent.append(percent_column[student]) #build the error log file if a student from the OMR file does not match a student in the course for student in range(0, len(studentID_column.values)): if str(int(studentID_column.values[student])) not in outputNumbers: errorNum.append(studentID_column.values[student]) if 'First Name' in df.columns and 'Last Name' in df.columns: errorFName.append(firstName_column.values[student]) errorLName.append(lastName_column.values[student]) errorScore.append(scores_column.values[student]) if 'Percent Score' in df.columns: errorPercent.append(percent_column.values[student]) elg.logGenerator(df, errorNum, errorFName, errorLName, errorScore, errorPercent) #combine output columns output = [outputNames,outputNumbers,outputSections] output.append(outputScores) if 'Percent Score' in df.columns: output.append(outputPercent) #build CSV output if len(output) == 4: rows = zip(output[0], output[1], output[2], output[3]) elif len(output) == 5: rows = zip(output[0], output[1], output[2], output[3], output[4]) with open('Import_into_Canvas.csv', 'w', newline='') as f: writer = csv.writer(f) for row in rows: writer.writerow(row) input("\nSuccess! A CSV file titled Import_into_Canvas is ready to be uploaded into the Canvas Gradebook and can be found in the same directory as this script. Hit Enter or close this window to exit:") except: decision = input("\nSomething went wrong. Perhaps you entered an invalid Canvas API Access Token or Course ID? Hit Enter to restart the program or type 'quit' to exit: ") if decision.lower() == "quit": return else: formatCSV()
def revel(): #get course ID courseID = input("\nEnter Course ID: ").strip() #Try to read the file (if the user wants to quit, the title will be 'quit') try: #api calls to gather course and student information courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') decision = input( "\nFound course \"" + courseName + "\". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() #if the course doesn't exist, either let the user reenter or quit the program while decision.lower() == "change": courseID = input("\nEnter Course ID: ").strip() courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') decision = input( "\nFound course " + courseName + ". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() #gather the remaining single call api calls we need courseStudents = api.getCourseStudents(courseID) studentList = {} for student in courseStudents: studentList[student.get('name').replace( " ", "").lower()] = [student.get('sis_user_id')] #Get file name from user title = input( "\nEnsure the Revel XLSX file is in the same location as this script. Enter the name of the file (including the extension .xlsx) and then press Enter: " ).strip() try: if title == 'quit': #If users want to quit, exit program return originalFile = pandas.read_excel(title, skiprows=1) try: #Start processing the file #Combine first and last name originalFile["Student"] = originalFile["First Name"].map( str) + " " + originalFile["Last Name"] originalFile["Student"] = originalFile["Student"].str.title() #Create column for ID and Section originalFile["SIS User ID"] = "" originalFile["Section"] = "" #Drop columns & reorganize originalFile = originalFile.drop( ['Email', 'First Name', 'Last Name'], axis=1) cols = list(originalFile.columns) cols = cols[-3:] + cols[:-3] originalFile = originalFile[cols] #Create a list of students to drop who aren't in the Canvas course studentToDrop = [] for index, row in originalFile.iterrows(): if ((originalFile.at[index, 'Student']).replace(" ", "").lower() in studentList): if (studentList[( originalFile.at[index, 'Student']).replace( " ", "").lower()][0] is None): studentToDrop.append(index) else: originalFile.at[index, 'SIS User ID'] = studentList[( originalFile.at[index, 'Student'] ).replace(" ", "").lower()][0] elif (originalFile.at[index, 'Student'] == 'Total Points'): pass else: studentToDrop.append(index) #If students are going to be dropped, create a error log excel sheet with proper information if (len(studentToDrop) != 0): droppedStudents = pandas.DataFrame(columns=cols) for student in studentToDrop: droppedStudents = droppedStudents.append( originalFile.iloc[student], ignore_index=True) droppedStudents = droppedStudents.drop( ['SIS User ID', 'Section'], axis=1) #Finish formatting Canvas ready file originalFile = originalFile.drop(studentToDrop) originalFile.loc[-1] = '' originalFile.at[0, 'Student'] = 'Points Possible' originalFile.at[0, 'SIS User ID'] = '' originalFile.sort_index(inplace=True) originalFile.index = originalFile.index + 1 # shifting index #Start naming process title = input( "\nPlease enter what you would like to name the completed file, including the extension (.csv), and then press Enter (or leave empty for " + courseName + "_GroupList.csv): ").strip() if (title in ""): title = 'Revel grades C8 from p353 DO THIS ONE SECOND.csv' #If user forgets to add .csv system will add it in here if title.endswith('.csv'): pass else: print( "\nIt seems like you forgot to add the .csv extension. Don't worry I've added it for you!" ) title = os.path.splitext(title)[0] + '.csv' #If the title is somehow blank or already take, ask user to re-enter name and loop until its a valid name if (os.path.isfile(title) or not title or not title.endswith('.csv') or title == '.csv'): while (os.path.isfile(title) or not title or not title.endswith('.csv') or title == '.csv'): if title == 'quit': return else: title = loopFileName() #Creates a csv with the given title name and tell user that the file has been created! originalFile.to_csv(title, encoding="utf-8", index=False) if (len(studentToDrop) != 0): droppedStudents.to_csv('errorLog.csv', encoding="utf-8", index=False) print( '\nAn Error Log was produced. Not all students in the given file exist in Canvas. Please check the Error Log for which students could not be identified.' ) input( "\nSuccess! CSV file named: " + title + " has been successfully created in the same directory as this script. Hit enter or close this window to exit:" ).strip() except Exception as e: print(e) print( 'There was an issue formatting ' + str(title) + '. Please check the integrity of the file and try again.') #If there is an issue reading the file, throw an error except Exception as e: print(e) print('There was an issue reading ' + str(title) + '. Please check the integrity of the file and try again.') #If the program fails in general, throw this error and ask user if they want to retry except Exception as e: print(e) decision = input( "\nSomething went wrong. Perhaps you entered an invalid Canvas API Access Token or Course ID? Hit Enter to restart the program or type 'quit' to exit: " ) if decision.lower() == "quit": return else: revel()
def groupListGenerator(): #get course ID courseID = input("\nEnter Course ID: ").strip() try: #api calls to gather course and student information courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') decision = input( "\nFound course \"" + courseName + "\". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() #if the course doesn't exist, either let the user reenter or quit the program while decision.lower() == "change": courseID = input("\nEnter Course ID: ").strip() courseDetails = api.getCourseDetails(courseID) courseName = courseDetails.get('name') decision = input( "\nFound course " + courseName + ". Hit Enter if this is the correct course or type 'change' to select a different course: " ).strip() #gather the remaining single call api calls we need courseStudents = api.getCourseStudents(courseID) courseSections = api.getCourseSections(courseID) courseGroupCategory = api.getGroupCategory(courseID) #Ask User if they want Student Number, Sections, and/or Section Check numberDecision = input( "Would you like to include Student Numbers in the output? (y/n): " ).strip() numberDecision = inputYNCheck(numberDecision, 'yes') sectionDecision = input( "Would you like to include the Section each Student is enrolled in the output? (y/n): " ).strip() sectionDecision = inputYNCheck(sectionDecision, 'yes') if (sectionDecision == 'yes'): checkDecision = input( "Would you like to include checking that each Student in each Group is from the same Section in the output? (y/n): " ).strip() checkDecision = inputYNCheck(checkDecision, 'yes') else: checkDecision = 'no' decisionList = [numberDecision, sectionDecision, checkDecision] #for each section in the course, get it's id and name to put into a dictionary courseList = {} for section in courseSections: courseList[section.get('id')] = [section.get('name')] #for each student create a dictionary regarding their canvas id, name, and section (used to have sis_id but doesn't work anymore) studentDic = {} for student in courseStudents: #since a studnet can be in multiple "sections" (lecture and tutorial, ect.) generate a list, sort list, and convert to string enrolledSections = [] stringEnrolledSections = "" for section in student.get('enrollments'): enrolledSections.append( courseList[section.get('course_section_id')][0]) enrolledSections.sort() for section in enrolledSections: stringEnrolledSections = stringEnrolledSections + section + ", " stringEnrolledSections = stringEnrolledSections[:-2] studentDic[student.get('id')] = [ student.get('name'), student.get('sis_user_id'), stringEnrolledSections ] #generate variables that will be used defaultColumns = ['GroupSet', 'Group', 'GroupSize'] spreadsheet = pandas.DataFrame( columns=defaultColumns ) #only need these two as the amount of students varies based off of group and set GroupSet = "" Group = "" maxGroupSize = 0 #for each group set gather it's individual groups for GroupCategory in courseGroupCategory: courseGroupSetGroups = api.getGroups(GroupCategory.get('id')) groupCategoryUnassignedStudents = api.getUnassignedGroup( GroupCategory.get('id')) GroupSet = GroupCategory.get('name') #for each group in a group set gather it's students and create an entry into spreadsheet for groupSet in courseGroupSetGroups: Group = groupSet.get('name') groupMembers = api.getGroupMembers(courseID, groupSet.get('id')) spreadsheet = spreadsheet.append( { 'GroupSet': GroupSet, 'Group': Group }, ignore_index=True) counter = 1 #for each group member in a group in a set gather their name and section and add them in a row to a group for student in groupMembers: addStudentToList(spreadsheet, studentDic, student, 'user_id', counter, decisionList) counter = counter + 1 if (counter - 1 > maxGroupSize): maxGroupSize = counter - 1 spreadsheet.at[spreadsheet.index[-1], 'GroupSize'] = counter - 1 #Same as above except only for students not in a group if (len(groupCategoryUnassignedStudents) > 0): counter = 1 listCounter = 1 spreadsheet = spreadsheet.append( { 'GroupSet': GroupSet, 'Group': 'Unassigned List ' + str(listCounter) }, ignore_index=True) for student in groupCategoryUnassignedStudents: #Due to how many students can be unassigned I decided that each unassigned list can only contain 5 students (on the 6th it creates a new list and ass the student as 1st) if ( counter == 6 ): #this is inclusive. So what ever number is compared to it will restart on that number spreadsheet.at[spreadsheet.index[-1], 'GroupSize'] = len( groupCategoryUnassignedStudents) listCounter = listCounter + 1 spreadsheet = spreadsheet.append( { 'GroupSet': GroupSet, 'Group': 'Unassigned List ' + str(listCounter) }, ignore_index=True) counter = 1 addStudentToList(spreadsheet, studentDic, student, 'id', counter, decisionList) counter = counter + 1 spreadsheet.at[spreadsheet.index[-1], 'GroupSize'] = len( groupCategoryUnassignedStudents) #Compares each student in each group to see if they're from the same section. #Due to how this works, it must be the last column in the spreadsheet otherwise the code needs to be modified if (decisionList[2] == 'yes'): #Gets some variables ready decisionListYesCount = 0 for option in decisionList: if (option == 'yes'): decisionListYesCount = decisionListYesCount + 1 #If there is more than 1 member in any group, check for same section (need this otherwise the column won't work) if (maxGroupSize > 1): numberOfRows = int(len(spreadsheet.index)) #Create the column for the excel string spreadsheet['Same Section?'] = "" #Makes the excel macro string for studentGroup in range(len(defaultColumns), numberOfRows + len(defaultColumns)): excelQuery = "=IF(AND(" for studentEnrolledInSection in range( 0, (maxGroupSize - 1) * decisionListYesCount, decisionListYesCount): #Yes I know it's ugly but it works. It will scale for each decision you give the user so you shouldn't need to modify it :) excelQuery = excelQuery + 'OR(' + numToLetter( len(defaultColumns) + decisionListYesCount ) + str(studentGroup - 1) + '=' + numToLetter( studentEnrolledInSection + 2 + len(defaultColumns) + ((decisionListYesCount - 1) * 2)) + str( studentGroup - 1) + ', ISBLANK(' + numToLetter( studentEnrolledInSection + 2 + len(defaultColumns) + ((decisionListYesCount - 1) * 2)) + str(studentGroup - 1) + ')),' excelQuery = excelQuery[:-1] + '), "Same", "Different")' #Once the query has been made, add it to that row for use spreadsheet.at[studentGroup - len(defaultColumns), 'Same Section?'] = excelQuery #Remove any nan entries (gotta scrub) spreadsheet = spreadsheet.replace(np.nan, '', regex=True) #Ask user to enter name of prefered csv output (if blank uses their course name _GroupList.csv) title = input( "\nPlease enter what you would like to name the completed file, including the extension (.csv), and then press Enter (or leave empty for " + courseName + "_GroupList.csv): ").strip() if (title in ""): title = courseName + '_GroupList.csv' #If user forgets to add .csv system will add it in here if title.endswith('.csv'): pass else: print( "\nIt seems like you forgot to add the .csv extension. Don't worry I've added it for you!" ) title = os.path.splitext(title)[0] + '.csv' #If the title is somehow blank or already take, ask user to re-enter name and loop until its a valid name if (os.path.isfile(title) or not title or not title.endswith('.csv') or title == '.csv'): while (os.path.isfile(title) or not title or not title.endswith('.csv') or title == '.csv'): if title == 'quit': return else: title = loopFileName(courseName) #Creates a csv with the given title name and tell user that the file has been created! spreadsheet.to_csv(title, encoding="utf-8", index=False) input( "\nSuccess! CSV file named: " + title + " has been successfully created in the same directory as this script. Hit enter or close this window to exit:" ).strip() #If the program fails in general, throw this error and ask user if they want to retry except Exception as e: print(e) decision = input( "\nSomething went wrong. Perhaps you entered an invalid Canvas API Access Token or Course ID? Hit Enter to restart the program or type 'quit' to exit: " ) if decision.lower() == "quit": return else: groupListGenerator()