If you’ve read through some of my previous posts, you know that I tend to use Accountability data from THECB for several of the things I try in Tableau. One challenge has always been the way data is formatted by the Accountability system. It outputs data with one row per institution and separate columns for each measure and year. And of course, sub-levels of each variable are part of the measure names. Needless to say this format doesn’t work very well when trying to use the data in Tableau, or pretty much any program.
My early way of dealing with this was to simply pivot the columns into rows. This created a somewhat usable version of the data with fields for institution, year, name of the measure, and the value. Of course this came with it’s own set of problems. It was possible to compare various institutions on the same measure, but it never worked as well as I would like. Also, it was easy to accidentally include values from multiple measures in an analysis because all values were stored in the same field and you had to use filters to narrow things down to the specific values you were after.
Over Spring Break (week before last), I decided to sit down with my Excel macro and try a new approach to reformatting the data. Long story short, I was able to create a format for the Accountability data that, so far, has been significantly more user-friendly. I’ll be making use of this reformatted data in some of the Tableau examples I’ll be posting over the next couple of weeks. For those that might find it useful here’s the Excel file:
Reformatted Accountability Data (~45 MB)
Now, I should mention that this data is just for public universities and not all institutions in the state. I haven’t spent a lot of time verifying all of the data, so if you need “official” data, you should probably use the Accountability System, or at the very least double check the values you get from this spreadsheet against it. The first few columns include the name of the institution, FICE code, period/year, category, and sub-category. After that are the primary measures from each area of the Accountability system. If time permits, I might get around to providing better documentation for it, but hopefully it’ll make sense to you if you’ve used the Accountability system before.