Data.World Project – Examining Public Employee Compensation in San Francisco

It has been a while since I last began playing around with data.world, an awesome collaborative website where users can share data sets to draw insights.

I chose to take public compensation records from the City of San Francisco, mostly because public employee data is one of the easier bits of information to get a hold of.

To be honest, I really have no hypothesis or operating assumptions – I’m just seeing what happens, so to speak.

Methods

The first thing I did was download 2013-2017 San Francisco employee compensation records from data.gov, a US run website created in 2009 as an effort towards transparency. Fortunately the information was pretty clean, so no scrubbing was necessary. I then imported the CSV into data.world site as a new project. Data.world has a very cool query builder that allows you to leverage SQL.

I noticed that the most standardized field for categorizing job group was rolled up to a ‘Organizational_Group’ , which fit the entire data set in 6 buckets

Public Protection
Public Works, Transportation & Commerce
Culture & Recreation
Human Welfare & Neighborhood Development
General Administration & Finance
Community Health

I thought it would be useful to see the average salary of each organizational group, here is for 2017

Year Organization Group Average Salary
2017 Community Health $102,846.31
2017 Public Protection $141,707.62
2017 Public Works, Transportation & Commerce $104,978.49
2017 Culture & Recreation $50,408.47
2017 Human Welfare & Neighborhood Development $71,870.05
2017 General Administration & Finance $97,371.90

I thought this was kind-of useful, but it would be cool if I could see what the average salary was for each organizational group, year-over-year. I published the following ‘insight’, which can be shared with followers on data.world

download-2018-12-17T06-19-03-568Z

It looks like while public protection enjoyed, on average, the higher salary or ‘total compensation’, which includes benefits – it had slightly dropped year over year, compared to Community Health, Public Works, and General Administration & Finance which has been growing slowly over time.

My next question was based on the total amount spent on employee compensation by organizational group, year after year.

Here is what I found:

ExcelSalary

Okay, okay… that was done in excel…

THIS, however was another insight published on data.world

download-2018-12-17T06-23-41-610Z

This insight, I hope, shows that each organizational group has taken about the same percentage of the pie year over year, with the most drastic decrease for Public Protection and an increase in Community Health.

What have I proven? Probably nothing, but it was fun to play ‘data scientist’ for a bit!

Make your own profile and follow me on data.world!

https://data.world/gongstad

Advertisements