Open Data in the Wild: How it helped me to accidentally help 37 000 people

This quote from the Open Data Handbook is a perfect way to introduce this post:

Open data, especially open government data, is a tremendous resource that is as yet largely untapped.

Before recently, I had never heard of Mpofana Municipality in KwaZulu-Natal, South Africa. It’s a relativity small municipality in South Africa, consisting of 11 000 homes and a population of 37 000 people.

The background

PETCO, a plastic bottle recycling non-profit, is working on a recycling project in one of the towns in Mpofana. I was looking into the data of the municipality to give the team a better idea of the state of solid waste management there. The availability of open data is the only reason I was able to do this analysis.

Statistics South Africa is the country’s National Statistics Office, and from them I was able to find out very useful demographic information, such as:

  • Population and household numbers
  • Type of refuse removal by household weight (weekly, central dump/collection point etc)
  • Satisfaction levels with refuse removal service

National Treasury, together with OpenUp (formerly Code4SA) published an amazing resource last year in the form of Municipal Money. It’s a website that allows anyone to look at the financial records of all of South Africa’s Municipalities, some dating back to 2009. For a more detailed analysis, the full data sets are available via the API companion site.

Hmmmm…that’s interesting!

I had been playing around with these data sets for a while, taking some time getting to grips with it. Looking at Mpofana was my first really critical look at the data of a single municipality. Like any good data exploration, the first bit of analysis wasn’t showing up anything of much interest – lots of the statistics seemed pretty much in line with what I would expect from a municipality of this size and profile.

That’s until I looked at the Total Operating Expenditure for Solid Waste. Well, when I first looked at the data in table form, I didn’t realise what I was seeing.

That is, until I created a bar chart.

 

Whoa! Where did that come from? Those were some pretty big spending increases, and the municipality only has 11 000 homes in total. Is the municipality taking over solid waste management for the whole district? Were there large projects being implemented?

I dug a bit further into the line items and saw that the majority of the increases came from large increases in Salaries and Wages.

Uh oh.

In South Africa, there is pretty well documented corruption in many spheres of Government, and it’s no secret that the public sector wage bill has also been ballooning for many years – these aren’t always linked but sometimes they are. However, one thing I’ve learnt is that assumptions are useful for guiding where to start exploring data, but not very good for making an analysis. There could be a number of reasons for the increase. Best to find out what was going on. As always, the first step was to verify the data!

Data detectives

After contacting the stellar folks at OpenUp, it was confirmed that yes, the site was working correctly , and the correct data was being pulled from the database. This was the data being provided by  National Treasury.

The next step in the investigation revealed what the problem was. Again, some very helpful people at National Treasury dug into it and and confirmed that this was the data that was submitted to them. However, they found that the accounts were filed incorrectly – the entire wage bill for the whole municipality had been mistakenly placed under the Solid Waste function. They have since issued notices to the municipality to correct the accounts which will be updated in the next round of submissions.

I’m not sure exactly how fixing this rather large accounting error will help the residents of Mpofana, but large errors can cause a host of problems with budgeting, performance monitoring, decision making, allocation of resources and perhaps even service delivery.

Many hands make light work

This small but excellent result would not have been possible without the immense efforts put in my many talented people over many years – allowing me to make such a find in a relativity short space of time, and with relatively little effort.  From National Treasury and Statistics SA who collect and process the data, to organisations like OpenUp who make this data accessible, to the leadership of PETCO who understand that good data allows for good decision-making and actively pursue data analysis as an operational asset. Because of these efforts upfront, someone like me was able to provide that last little bit of effort to use the data and (albeit unintentionally) to go full circle and provide a sense-check on it.

Collecting, processing and analysing data is a skilled profession and there certainly aren’t enough skills to go around. Not every organisation, municipality or department can afford to have full time staff dedicated to analysing data. By opening data up, organisations create the opportunity for value to be added to themselves and society by anyone from anywhere in the world.

Do try this at home!

If you’re interested in accessing these data sets yourself, have a look at Municipal Money site and the Municipal Money API for financial data. For census and survey data from SARS, you can log in as a guest here. The site has a bit of a  learning curve but very useful to access the exact data you need. Be sure to check out the StatsSA 2016 Community Survey site as well.

And lastly, if you’ve done something with open data that was useful to you, please share your story. Greater awareness of the benefits will help keep existing open data portals going and will help make new ones available.