This is a cross post from Baynes Media. The Wapping Mole thinks that residents of Wapping and elsewhere in Tower Hamlets might find this useful. No idea why.
Open data? Open government? How is that relevant to me [a resident of Wapping]?
Simple. It’s your money. If you lost a tenner you would want to know where it went. Same principle applies to local councils. You want to know where the cash goes.
Below you can find links to files that contain details of payments over £500 by Tower Hamlets Council to its suppliers. That’s around 158,000 transactions between 2010 and 2013.
Scrubbed data for download (CSV)
- LBTH_PAYMENTS_TO_SUPPLIERS_2010 (CSV) 753kb
- LBTH_PAYMENTS_TO_SUPPLIERS_2011 (CSV) 7.5Mb
- LBTH_PAYMENTS_TO_SUPPLIERS_2012 (CSV) 8.8Mb
- LBTH_PAYMENTS_TO_SUPPLIERS_2013 (CSV) 11.9 Mb
- ALL_FILES (ZIP) 3.3 Mb
(Updated Wednesday 16th April 2014. These datasets will be migrated to Openspending in future. )
Notice: These files contain public sector information licensed under the Open Government Licence v2.0.
You can find all the original spreadsheets and Comma Separated Value (CSV) files on the Tower Hamlets Council website. (A word of warning. The original data is held in 23 Excel spreadsheets. And 177 CSV files. )
But how is this (a) legal and (b) possible? Quite simple really.
“The public should be able to hold local councils to account about the services they provide. To do this, people need information about what decisions local councils are taking, and how local councils are spending public money.”
That’s what the Department for Communities and Local Government (DCLG) wants and who am I to argue?
Indeed our very own Council reinforces the fact that we as residents should have a poke around. This is what Tower Hamlets says:
“One of the benefits to the community in publishing this data is that it means that our spending will come under greater scrutiny by local people. We hope it will inform people about what we do, and encourage people to challenge how we spend money. It is your money and we welcome comments.”
So in December 2013 I thought I would have a look at the data Tower Hamlets Council published and see if there was anything interesting in it.
And I am still wondering.
Because as with many things in life having a look at this ‘open data’ was not quite as straightforward as it could be.
Scrub a dub dub
Those with a leaning towards geekishness will know all too well what I mean when I say I spent a huge amount of time ‘data scrubbing’.
For those without pen pocket protectors, data scrubbing means taking raw data (the stuff in the Council’s files) and going through every cell in every file of all 119,000 + records and getting it into a basic clean format that is usable.
Sometimes you can write programs to do this, other times you use Excel, sometimes it comes down to a very boring manual task. Bottom line is everything needs to be nice and tidy.
That’s why you will find links to only seven files on this web page compared to 200 on the Tower Hamlets Council site.
The only think I have added to the files is a unique id number. I would have used the transaction number but there seems to be some duplication in the original data.
Fido! Breakfast time!
In fact the original data is a mess. The proverbial dogs breakfast.
Different fields. Different names for fields. Varying file names. No consistency in file names. Data values transposed. Descriptions truncated. Nasty.
A less charitable person than myself might suspect that Tower Hamlets Council was trying to conceal something. But obviously that is not the case. I mean, what on earth would they have to hide?
I think the exceptionally poor quality of the original 200 data files is just down to attitude.
The Council might publicly state that they”encourage people to challenge how we spend money” but my experience of trying to do just that indicates the opposite.
Before I started to write this I was going to sum this up as Tower Hamlets Council only abiding by the spirit of open data. But that is not correct.
Just by dumping a couple of hundred files onto a website does not comply with either the spirit or the letter of the guidelines to Local Councils for publishing data.
Yes Tower Hamlets have published details of all spending to suppliers over £500. But the only people who can make use of it are those people like me who have the experience, skills and lack of a social life to spend the time sorting the data out and turning into information.
And that is why these files are published here.
Spot the problem
Just to add insult to injury, even once the data has been scrubbed and turn into nice clean files that can be imported into a MySQL database the information in them is somewhat opaque.
|Directorate||Service||Division||Responsible Unit||Expense Type||Payment Date||Transaction Number||Net Amount||Supplier|
|Communities & Localities||Cultural and Related Services||Culture & Heritage||Community Services Grants||Voluntary Associations||24/12/2012||5990563||£3420||OSMANI TRUST|
Spot the problem with this? The transaction record tells you everything apart from what the Osmani Trust was paid £3,420 for. Eggs? Rockets? Squirrel hats? Who knows. Not us.
We can at least see who has been paid. We do not know what they have been paid for.
The Council must know. But for some reason have decided not to reveal this. Remember that all this data is just a report from some big and nasty computer system in the Town Hall that spends all its time just tracking money.
So it should know what it’s suppliers have been paid for.
But this record – and the other 118,999 records published – does not have the fundamental information Tower Hamlets residents need.
What does Tower Hamlets Council spend our money on? We still don’t know. It seems that other Councils only publish spending transaction to this level of detail which is not much use.
So get challenging people!
Of course having converted this spending data to an accessible format and published it for you all to enjoy I thought I might do some digging and challenging myself. More on this in future posts.
Fortunately for me I have the Wapping Mole to help me out.
For more information:
- Code of recommended practice for local authorities on data transparency
- Local Transparency: Guides to Publishing Data
- Data.gov.uk – Main UK Government Transparency & Open Data site
- GLA London Datastore
- Open Data in the United Kingdom
- Open Data Communities – Tower Hamlets
- UK Councils Open Data Scoreboard – London
- London Borough of Tower Hamlets – Data