I Viz RI Getting the Data
Looking into the states payroll was quite the adventure, you can download the full data 2011-2018 (up to Oct 6th) over at RI's Transparency Portal. It contains 137,998 records from 49 different departments and 3,849 unique job titles. The data consists of 5 payment columns these are regular, overtime, total, other and annual pay. Records prior to 2016 don't contain annual salary amounts.
Viewing the titles, it doesn't take long to notice they're a disaster (with a 30 char limit). 7,343 records are missing a title, most state college employees are listed as "non classified", some titles prior to 2016 bleed into their own cell shifting all the values right 1 or 2 extra cells, multiple different abbreviations for the same title, some titles tagging the department on the end, a few misspelled and the changing of formats throughout different years. However after some time spent cleaning the data in excel I got it down to 2,489 titles and 2,246 broad titles. For an example of the issues here is the same title "Chf Strategic Plan Mont & Eval" 7 different ways.
Cleaning the Data
One thing I was interested in was how employees pay changed over time and the progression of their careers. Having the annual salary only for 2016+ that's the furthest back I can go. This was pretty difficult because the only identifying information is first name, last name and middle initial. 31,852 have no middle initial and 56 of those... have numbers for the middle initial. I created a id column by combining year,first,last,mi example (2017 HURLEY DANIEL S) which works for the most part only resulting in 361 duplicates which I dealt with manually. A lot of the duplicates end up being really small payments usually under $1,000. I then used vlookup to join 2016 and 2017 data for people with the same id, so if 2 people had the same first, last and mi it may have cause some errors in the raise and title change raises sections of the viz.
Results
As far as pay goes:
- 8,071(46%) made over $50,000
- 2,258(13%) made over $100,000
- 15(0.09%) made over $250,000
- 1(0.006%) made over $400,000
As with most states, the top earner was a coach. In RI's case it was Daniel Hurley of URI who made $870,000 in 2017. The viz is the 1st Tableau story I've made with 8 exploratory dashboards they are.
- Graph of changes from 2011-2017
- Average/Median pay by title/department
- Number of employees by title/department
- Raises of people who kept the same job title 2016-2017
- Raises of people who got promoted/changed job titles
- Overtime pay by department/person
- Those who made more then double their regular pay in overtime/other
- Salary grouped with a search at the bottom for all 2017 employees