Intro
Hello and welcome to another blog post for Anthony’s Techie Thoughts! It's been a few months since my last post, but I've kept busy studying and progressing toward my goal of making a career transition from mathematics teacher to front-end web developer. If you're new here, in my blog posts I reflect on my journey and share things I'm learning along the way. You can find me GitHub, Hashnode, DEV.to, and CodeNewbie.
In this blog post, I want to share how I used a Python script to make my day job a little easier. Specifically, I used the script to create individual .CSV files with students' assignments and grades. Throughout the post, I will share snippets of the code, but if you'd like to see the script in its entirety, you can view it in this GitHub repo.
The Problem
At the school I work at we use an online database management software to manage things such as attendance, grades, etc. For the most part, it gets the job done but there’s one feature (a vital one in my opinion) that’s missing: the ability to generate individualized reports for students. Having the ability to do this helps solve two problems:
Answering the question “What’s my grade?”
Improving student’s ability to complete missing assignments
In my experience, when students can receive regular updates on their progress their overall learning and understanding of the content improve. However, the software we currently use does not provide this feature (or at least I haven’t been able to find it). The result is that come progress reports or end-of-semester reports students (and parents) are sometimes surprised about what their overall grade is.
To avoid this shock and surprise at report time I put in the effort to export my grade book as an Excel spreadsheet, then import it into Google Sheets (we use Google’s Education tools at my school), and then create a separate Google Sheet for each student which is then shared to that student’s individual school email account.
This process is both tedious, repetitive, and time-consuming. In particular, it was something that a computer could do and give me back time that could be spent doing more productive things like planning lessons, helping struggling students (before they receive their final grade report and it’s too late), or simply just spending less time staring at a screen. So I sent out on a quest to find out how to automate this process with a Python script.
My Python Script Solution
Exporting the Spreadsheet
The first thing I needed to do was to export the grade book as an Excel spreadsheet and do some data cleaning. In particular, I needed to remove duplicate columns and unnecessary rows. Then save the document as a CSV.
This part took some trial and error. At first, I attempted to use the .read_excel() method from the Pandas library directly on the exported Excel file, but the way the grade book software exports the Excel file made it unreadable using this method. As such, it was easier to modify the file and export it as a CSV file. Then use the .read_csv() pandas method.
Importing pandas and creating a dataframe
Once I had the properly cleaned and formatted CSV file it was time to import the Pandas library and create a dataframe:
import pandas as pd
data = pd.read_csv("data.csv")
Once I had the dataframe set up the next part was to set up a loop to iterate through the rows in the dataframe and then save them as an individual CSV file. In this case, each row of the dataframe represents a student and their corresponding assignment grades.
This is the first time I have worked with Python in while — for the past few months I’ve been studying JavaScript. So in my first attempt to write the for loop it didn’t work out because for loops are used differently in Python than in JavaScript. Instead, in Python, I had to make use of the while loop. Here’s a quick tangent on the difference between a for loop and a while loop in Python.
for vs. while loop in Python
In Python, “a for loop is used for iterating over a sequence (that is either a list, a tuple, a dictionary, a set, or a string)”. In other words, it won’t work for iterating over the dataframe so instead,n I had to make use of a while loop:
i = 0
while i < len(data):
data.iloc[i].to_csv(str(today) + '_' + str(data.iloc[i]["Name"]) +'.csv')
i+=1
Here the loop goes through each row in the dataframe and then saves that row as it’s own CSV file which includes the current date (more on that in the next section) and the student’s name.
Importing date from datetime
Initially, when I saved the split files I had manually typed in the current date as part of the file’s name, but then I realized that the whole point of writing this script was to be able to automate the process and having to type in the current date each time I used the script kind of defeated the overall purpose.
So instead I figured out how to import the current date and have it written into the file name automatically using the following set of code:
from datetime import date
today = date.today()
Then the variable today can be used within the naming scheme for the file name inside the while loop (see the previous snippet above).
Sharing the files
Once I had the files created the next step was to upload them to Google Drive and share them with the students. This part I had to do this manually but I suspect there is a way to automate it which leads me to the ideas for improving the script.
Features to Add
Here’s the short list I’ve come up with for features to add to the script:
Export as a PDF instead of a CSV
- My students are familiar with using Google Sheets to read CSV files, but I can envision use cases where it might help to have a PDF version instead. Maybe even have the ability for the user to choose.
Highlight only missing assignments
- This is another feature that’s missing from the grade book software the school uses. There’s no way to run a report of just the missing assignments. This is something I’ve found valuable in the past because it acts as a checklist for students on what work they need to complete and improve their grades. (Assuming the student’s low grade is due to lack of completed assignments and not conceptual misunderstandings)
Email students directly
- I had to do this part manually, but I know it’s possible to use Gmail’s API to send emails via a Python script so this would something I would like to add because it would cut out the steps of uploading the files to Google Drive and sharing them with each student.
Continue working on seeing if I can work directly from the exported Excel spreadsheet
- As stated previously, I initially tried to read the Excel file and convert it to a dataframe but struggled with getting it to work. This is something I’d like to possibly explore because it would eliminate another step in the process. This would especially be useful if I could also automate the data cleaning step of removing the unneeded rows and columns that come with the exported spreadsheet.
Conclusion
These are a few of the ideas I have for improving the script. However, at a certain point, this begins to develop into a full-fledged database management software system — something the school is already paying for even though it lacks, in my opinion, some critical and helpful features.
For now, I’m happy with my simple script because it saves me time and I was able to put into practice my programming skills in a real-world setting. Python completed this task in seconds whereas it took me over than hour to manually separate each spreadsheet row and save them to an individual file that could be shared with 80+ students.
Resources
Along with copious amounts of Internet searching, I found the following resources useful in learning how to create this script:
Thanks for reading
Thanks so much for reading this post. I hope you found it useful.
How do you use automation to save yourself time? Share your thoughts in the comments. Hearing your thoughts makes this more of a conversation and helps us all learn.
Thanks for stopping by and happy coding!
