(Disclaimer: The “Sector” and “Organization type” categories are not part of the original Government of Ontario dataset, I added them. There might be errors, and if there are, it’s my fault and not that of the Ontario Public Service. Use at your own risk.)
I’ve written before about how important I think visual presentations of information are. It only makes sense that eventually I would try my hand at producing them. I decided to look at the Ontario public salary disclosure list. Whether or not it’s good policy is debatable, but it’s a really fun data set. I’ve been playing around with the list and see if I could show something interesting that isn’t immediately apparent from just looking at the list in table form. I used an app called Tableau Public to generate a filterable interactive dashboard (unfortunately WordPress won’t let me embed the interactive version, but I’ve included an image below that you can click on to see the full version). The dashboard is really the star of this post, so go take a look. You can click on sectors or organizations in the bar charts to filter the dashboard to display just those items.
There are a few lessons that I learned while making this. First, data formatting matters; I would encourage organizations and individuals to make data available is the most accessible form possible. The Ontario disclosure list is published in HTML tables over multiple pages, which isn’t as bad as a PDF (thankfully Excel can import data directly from websites, and I had a bit of free time to reformat it), but still takes more effort to work with than an .xls (Excel) or .csv (universal) file.
The second lesson was that making good visualizations is surprisingly difficult. This may sound obvious, but I don’t think many people really realize what goes in to making graphics that effectively convey a large amount of information. In addition to technical skills, it’s necessary to understand how to present images in a way that meshes with our natural ability to take in information; it’s like learning how to write an essay all over again, but in a new medium. I had visions of making a wonderful intuitive visualization that could quickly tell at least a portion of the story of public compensation in Ontario, but I don’t think it succeeded. I’m pleased with the outcome, and I think it’s much more useful than the original disclosure listing, but it isn’t quite as natural and quick to understand as I’d hoped. Any suggestions to make it better?
– By Brent Barron
5 Comments Add yours
I found that with a sector that has thousands of entires, when you get to the list of employers (or employees within an employer) the sheer number of entries becomes overwhelming. The most I can grasp from the list is who are the top five.
One thing to try could be to find a way to sub-divide the data further. Rather than go directly from sector to a list of employers, maybe an intermediate step. Something like geography or arbitrarily grouping them by total salary paid (maybe <1 million, 2-9 mil, 10-99 mil, 100-500mil, and OPG could get its own group).
Another could be to create a summary statistic that would scale down the relative differences between the bars. For instance, for the employers how many employees are in some income group. For the employees, maybe the ratio of employee's earnings to the median earning.
I'm curious to see how this evolves!
The complexity and overwhelmingness of it is certainly a concern I had. After I made that, I did a post-secondary only one, and I think it’s a lot easier to digest. http://public.tableausoftware.com/views/OntarioPSESalaries/Dashboard1
I was thinking about also adding headcount for each organization (or for the biggest ones) and showing which has the highest percentage of 100k+ers. I also love the idea of mapping them, and really wish there was some kind of automatically generated location data. I don’t know how to do this, but it would be interesting to make a heatmap, where more intense colours correlated with greater density of employees or something like that.
Projects for another day I suppose.
Brilliant. Do you have the cleaned up file for the post secondary sector in excel format that you might be willing to share. I saw that you had assigned record numbers and I am assuming that you did some major cleaning of the files that you got from the website?
Ask and ye shall receive!
Hello just wanted to prodive you with a fast heads up. The written text inside your article seem to be operating from the screen within Internet explorer. I am not sure if this can be a format problem or something like that to do with internet browser compatibility however i figured Id publish to let you know. The look look great although! Hope you obtain the issue solved quickly. Many thanks