Unlock student data: an easy way to reformat UC Davis rosters

One of the first tasks I had as head TA for BIS2C was to build up a master roster/gradebook for the course. One way to accomplish this is to download the gradebook from Smartsite. This works well for basic uses, but lacks additional information about students that we might want to analyze, such as major or class (i.e. sophomore vs junior).

This information is available on a roster for the class that instructors can obtain from the registrar.  This roster is broken into blocks by section, with each section separated from the next by a header section that contains the relevant section number and CRN (course registration number). Here’s an Example roster from UC Davis.

For our master roster, we want to extract the CRN and section number from the header and add that information into two new columns alongside the student information. The last step is to delete all of the headers, allowing us to easily sort the students by columns or import the data into R for analysis. For a course with only a few sections, this is a quick and simple copy and paste job. However for a large course, like BIS2C (which has 40 sections), doing this manually becomes burdensome. To address this problem, I wrote a Perl script to automate the process.

If you comfortable with running scripts from the command line, you can download the script (roster.parse.pl)  here and give it a try with your own roster or my Example roster.

If you are not comfortable with running scripts from the command line, I’ve  put together a video tutorial on how to do it below (go for it – it’s not as hard as it sounds).

To use this script, you will need to first download a few things:

1. If your computer does not have Perl, you will need to download it from here. If you’re not sure how to determine if you have Perl or not, watch the video tutorial at the bottom of this post.

2. If you are using a PC, you should download cygwin, a Unix emulator. This will allow you to use the same Unix commands as I do in the tutorial.

3. Download my Example roster if you haven’t done so already. Save the file in it’s own folder.

4. Download a text editing program. I recommend smultron for Macs and notepad++ for PCs.

Once you’ve downloaded the necessary components, you’re ready to check out the tutorial – have fun!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s