All UM Classes in One Spreadsheet

While looking for the UM registration schedule, I came across the schedule of classes for the whole semester in one CSV file. Each class has information on what school it’s in, what type of class it is, what days of the week it’s on, the professor, and how many credits it is.

1

There are 21,797 classes going on this semester at UM. However, 10,442 of those are Individual Study, which aren’t classes that most people go to. They’re classes like Dissertation Preparation or Research on Selected Topics. I’m not going to count those, so there are 11,355 classes.

2

There are over 3,000 classes a day! Fridays are indeed easier than the other days. Most of the classes on Saturday are

  • Arts Administration Special Topics
  • Entrepreneurial Studies New Venture Creation
  • Weekend Masters of Business Administration
  • Social Work Advanced Topics in IP

Sunday’s classes are:

3.png

4

1561 of the classes had credit ranges like 1.00-16.00 which was unhelpful, so for this graph I only counted those with a specific credit value. The average credit for a class is 3.14.

5.png

LSA has by far the most classes with 3.3 times as many as the College of Engineering.

6

There are 811 different class numbers, with the most popular being 101 as in Engineering 101 or Astronomy 101.

9

When the the amount of classes for each class number are plotted by rank, there is a logarithmic distribution, much like that seen in cities. Class number 101 at rank 1 has many times more occurrences than class number 204 at rank 220, for instance.

7

If count is graphed with a log axis, the points lie along a straight line except for the highest values.

8.png

10

Unfortunately, only the last name is available. Some of these instructor last names represent several individuals who share that last name. For instance, a Walker teaches Musical Theater, Chemistry, Pharmacy, English Language and Literature, and Military Science. It’s probably not the same person. Some classes have multiple instructors listed, like the 7th in this list.

There are 734 different locations where classes are held. Of the 11,355 classes, 2,166 are “To Be Arranged”. Not counting that, here are the 10 most used classrooms at UM.

11.png

12

The classes did not have a building column already, so this one took some work. I could only use the column for location which had the room number and building in it like this: “2100 OBL”. Excel does not have Regex built in, but Malcolm Poindexter has made a Regex Add-In for Excel that worked quite well. I used the xSTARTSWITH() and xGROUP() functions from it to pull out just the building name, like “OBL”. regex101.com was very helpful in coming up with the expressions I needed.

These were the cases I needed to handle:

  1. 1028 DANA → DANA
  2. B830 EQ → EQ
  3. 5A UM HOSP → UM HOSP
  4. BURSLEY → BURSLEY
  5. DANCE STUDIO A → DANCE STUDIO A
=IF(xSTARTSWITH("[a-zA-Z]{2,}",Location Cell),Location Cell,xGROUP(".+?\s(.+)",Location Cell,1))

The syntax is IF(statement to evaluate, value if true, value if false).

The regex “[a-zA-Z]{2,}” says to match any phrase that has 2 or more letters in a row. This would be true for cases 4 and 5. That means there’s no room number, it’s only the building, so the value if true is the entire location value. If it’s false, meaning there is a room number in front of the building name, I use this regex: “.+?\s(.+)”. The period matches any character. The “.+?” means every character up to the \s, which is a space. After the space the “.+” matches the rest of the line, which is the building. The parentheses are to capture that part of the expression.  The whole expression is matched, but only what is in the parentheses is returned by xGROUP().

I replaced the building abbreviations with their full names and created a pivot table to count them up as I did with the other graphs.

Thanks to UM for putting this data out there!

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s