With added how to work StatsWales!
I’m trying to put together some basic data journalism how tos. As a lot of the stuff out there seems to involve coding, so there’s kind of an expectation you shouldn’t have a problem with using free web tools so some of them seem a little lacking in the instructions department.
This is really just a collection of things, I’ve learnt while trying, and occassionally failing to make openheatmaps (I think one badly formatted column of numbers kept me in the office til 7.30pm on Saturday, learn from my mistakes people!).
1. Get your data
Openheatmap only really seems to support councils properly so stick to them for now – handily the Welsh Government releases quite a bit of data brokendown in this way.
The actual stats releases are as pdfs and as such are useless, but most of the data also goes on StatsWales in a more downloadable form.
Let’s use the homelessness figures – under housing and homelessness and then the first report on decisions taken by eligibility.
To change the data shown, click on one of the underlined links to select what you want to look at – we want the authorities to be in the columns section, eligibility for the rows, so drag them to the right place, and just the most recent annual data (click period and tick and untick boxes as necessary).
Show table and use the download button to save it as an Excel spreadsheet.
2. Cleaning up the data
Open up the data in Excel/Openoffice Calc.
Now we just need to get rid of the columns and rows we don’t need.
Start by deleting the extra information (make a note of anything you’re going to need to add as a caption to the map later) and the row for Wales.
We’re going to focus on the numbers for eligible, unintentionally homeless and in priority need (the people councils have to house), so lose all the other columns.
Next the column headers need changing so the council one becomes uk_council and the number of households becomes value.
Last thing you need to check everything is in a format openheatmaps will read, it’s a bit picky about council names (and public bodies don’t use a standard labelling form), in particular lose any commas in Rhondda Cynon Taf and & in Neath Port Talbot, although the site will tell you if it can’t read the name.
Also format the numbers in the value column as numbers, rather than currency or percentage.
Finally save the file either as an excel spreadsheet or as a csv file.
3. Making a map
Head over to openheatmap.
Click create your map and chose the excel or csv option, then click upload on the next screen and find the file where you saved it.
Hopefully there were no problems.
If there were, it should tell you which line is the problem, so just go back to the spreadsheet and make any changes, the documentation section has some information about locations that are recognised, if that’s the issue.
When you open your map, you can then zoom in to any bit of it or use the settings below to change the colours.
When you’re done, hit save and view and your map is ready to share or embed.
4. Adding a timeline
Openheatmaps also allows you to create several maps you can play through, you just need to add a time column.
So lets head back to StatsWales for some more data. Using the same table as before select a few years under period (after dragging it to above eligibility in the columns section), while in eligibility untick everything except eligible, unintentionally homeless.
Download again and then you need to start adding the extra data to your original spreadsheet.
First on the original spreadsheet, add a time column with the dates for the original data. If you’re doing months or days, you need to add it as year/month/date, otherwise the website won’t put it in the right order.
Next you need to add the other years. Copy and paste the numbers for 2009/10 and 2008/09 for all the 22 councils directly under the numbers you already have in the values column.
Then copy and paste the council names twice (or as many times as you need, it helps to either already have them in a standard order, as StatsWales does, or sort into alphabetical order first so copy and pasting is easy).
Add the dates to correspond with the data for that year. Save.
Back to openheatmap and repeat the earlier steps. When you click through to view map, you should see a play button in the bottom left hand corner of the map, which allows you to play through the maps.
Change any settings you want and then save and there you have a map that shows how homelessness in Wales has changed over time.
You can link together as many maps as you like, or use values that cover the whole of England, Wales and Scotland.
– So was this helpful? Did it even make sense?