Child Poverty in London 17


The advantages of wealth and the burdern of poverty.

This is another post about making maps from OpenData. I am trying to learn more geeky stuff rather than my usual blathering about the map and data business, things open and stuff, so I am playing with PostGIS, QGIS, taking some tentative steps into CSS and JavaScript and generally having lots of fun. But hey, you can burn a lot of time wrangling data and making maps.

A week ago I saw this tweet

I couldn’t get any sense of what change had taken place or how the 2 maps compared, so I replied

Then I thought to myself, why don’t I try to make a more useful map? What follows is a summary of what I did, what worked, what didn’t. Hopefully it will be useful to someone and possibly a better map maker may even come up with a better way of doing this.

Data

I couldn’t find the CASE UMBR data set that Barry had used. That was probably a good thing as Lower Super Output Areas (even just for London) is quite a large data set.

But thanks to The London DataStore it wasn’t hard to find some data on poverty in London. I downloaded:

  1. Children in Poverty, Borough and Ward (xls)
  2. Statistical GIS Boundary Files for London (shp)

There are 2 sets of boundary files because at some stage the boundaries changed (this was a source of confusion, data wrangling and I’m not sure if I have got it completely right!)

Data munging

The starting point was to have a look at the data

  • There is a tab for each year (plus one for metadata)
  • The ward classifications changed in 2008 and then there seem to have been some further changes in boundaries after 2011. I decided that I would focus on the changes in Child Poverty for under 16 year olds between 2008 and 2013.
  • There is some regional and borough data at the top of each tab which I deleted (and saved as a separate spreadsheet, just in case)
  • I deleted the columns that I didn’t need
  • I cleaned up the header row and created shorter field names

Sounds easy? Yes, but QGIS doesn’t handle spreadsheet imports as elegantly as one might hope 🙁 The problem was that the %age of children in each ward in poverty came through as a string not a number which meant that I couldn’t do any calculations on it. I tried everything I could think of xls, xlsx, changing formats – nothing worked. I installed the Spreadsheet Layers plugin for QGIS which made things better, the results were still rather flaky but they sort of worked.

Finally I created an extra tab to calculate the difference between child poverty rates per ward from 2008 and to 2013. Not quite as simple as I had expected because the wards changed a bit over the period, but with a bit of wrangling I managed to come up with something that sort of worked. I wanted to understand where poverty rates had reduced (or increased) so I decided to show the change in rate as a percentage (percentage changes in percentages may not be the best statistical tool but it was the best I could come up with and it magnifies the changes, anyone got a better suggestion?). This time round I saved the data as a csv (only one tab from the spreadsheet) which made it easier to import into QGIS.

Mapping the data

Once the the tabular data had been manipulated and formatted it was reasonably easy to open in QGIS and join to the spatial boundaries. I created three layers and saved them as shape files:

  1. Child Poverty 2008
  2. Child Poverty 2013
  3. Child Poverty Change

Thematic maps are really simple in QGIS

  • Select properties for a layer and go to the Style tab
  • Choose ‘Categorised’
  • Select the column to thematically map (% of children under 16 in poverty for 1 & 2 above and change in %age for 3 above)
  • Choosing a percentage to map means that the choropleth is normalised which will keep Ken Field happy (I am sure he will find plenty else that needs imroving)
  • I chose ‘pretty breaks’ for the classes and then played around with the number of classes and colour ramps for each data set until it felt about right
  • I then manually over-rode the class breaks to get nice simple ranges for the classification for each layer (e.g. 0% to 10%, 10% to 20% etc)
  • Finally I made some manual tweaks to the colour ramp so that the 2008 and 2013 datasets had identical ranges and colours

Results

To share the results with you I had to have a first try at using the QGIS Print Composer. I definitely need to spend more time learning how to use this and somehow I managed to screw things up and save my project after removing all of the thematic classes (fortunately it only took a few minutes to repeat the steps above).

Here are the maps

Child Pov 2008

Child Pov 2013

You can see there are some white gaps in the 2013 image which are due to the boundary changes (I think I know how to fix that but that is for another day).

So these two maps are not massively dissimilar to the LSOA maps that Barry Quirk produced. The pattern of poverty is somewhat concentrated in the inner london wards with a sort of ‘cross’ stretching north, south, east and west. Broadly it appears that child poverty rates have reduced over the 6 year period (the pinks and reds are a bit lighter signifying lower poverty rates) but there is a bit more that we can find out. This final map shows the changes in child poverty rates over the period.

Child Pov Changes 2008-13

The darker blue wards show the largest reductions in poverty rates and the dark red show wards where child poverty rates have increased the most. The good news is that across most of London child poverty rates have reduced, which is a bit surprising to me in the light of the austerity measures of the last 3 years of the period. It looks as if the wards that had the highest poverty rates in 2008 have experienced the greatest reductions. If I was in government I would want to understand what factors might have caused the increases (some of which are large) in a few wards.

Putting the results on the web

In case you are interested in the data and want to explore it yourself, I used the wonderful QGIS2Web plugin to publish the data as a web project.

I had to use the OpenLayers output because Leaflet doesn’t support the thematic shading of the layers, OL isn’t quite a s elegant as Leaflet in my opinion but the map works reasonably well considering the volume of data. You can on the wards and see the attribute data in the popup (I dropped the hover option that I started with because it doesn’t seem to work in mobile). The layer control allows you to switch on the 2008 or 2013 layers (best to switch off the change layer and only use one layer at a a time).

A little discovery: my ward in Haringey has one of the highest increases in child poverty rates within London! Not what I would have expected, a flaw in the data, my methodology or ….? Maybe my local councillors will be interested.

Update 27th July, 2016 – Leaflet

Thanks to the brilliant Tom Chadwin I now know that the Leaflet option was throwing a wobbly because one of my field names started with a ‘%’ followed by a space (dumb on my part). I changed the field name and now you can see why I prefer the Leaflet option, it DOES display the thematics and builds a thematic legend as well.

Update 29th July 2016 – Attribution

A humble apology, I neglected to correctly attribute the sources of the data in these maps (my thanks to Nick Duggan for reminding me)

  • Contains National Statistics data © Crown copyright and database right 2012
  • Contains Ordnance Survey data © Crown copyright and database right 2012

17 thoughts on “Child Poverty in London

  • blair freebairn

    Welcome to the world of spatial analytics 🙂 A quick suggestion

    Don’t map percentage changes of percentages. You have four variables moving around simultaneously. In this map you don’t know which of these numbers is the real cause. In your shoes i’d map absolute change in number of kids in poverty. Much more real world

    Also for 08 to 13 data you need to be careful as the 11 census caused a lot of base populations to be restated (some quite dramatically).

    best

    • steven Post author

      Thanks Blair

      Do you think that the changes in the census could account for some of the swings in child poverty rates?

      Will try rerunning with absolute changes

      Steven

    • steven Post author

      Well spotted, my mistake. Now corrected, Leaflet output supports transparency (set at 30%)

    • steven Post author

      Thanks Nick
      I sort of knew about csvt but it just made life even more complicated. The Spreadsheet plugin is pretty useful for less tech folk
      Cheers

    • steven Post author

      Tom

      I have gone back to QGIS and retried with the Leaflet option and I have worked out the problem. I have 3 layers (the change layer, the 2013 layer and the 2008 layer) – for some reason the Leaflet option does not work with the change layer but it does with the 2013 and 2008 layers. It works fine with OpenLayers, Leaflet is much neater as it displays the thematic legend but I can’t get the 3rd layer to work

      When I first used QGIS2Web it asked for a projection (not sure why?) so I went back into the project and manually set all three layers to 27700 (OSGB BNG), not sure if that has anything to do with it?

      Cheers

      Steven

        • steven Post author

          Bingo!

          The leaflet version is here http://knowwhereconsulting.co.uk/WebMaps/LonPov/leaflet/

          It needed the space removing as well as the % and then hey presto!

          One slightly strange thing was that the Change layer is at the top of the QGIS project and also appears at the top of the QGIS2Web dialogue but the layer appears at the bottom in the final output.

          Another annoying little characteristic is that the label settings for the popups don’t seem to get saved when you reopen the project to tweak it. It would also be nice to be able to set all labels to one of the styles without having to wade through each field in each layer.

          Brilliant support, thanks Tom

          • Tom Chadwin

            I think I’ve fixed the special character issue on Github, so thanks for discovering it.

            I have spent more time on layer ordering than almost anything else in the plugin. Are you referring to the stacking order of the map layers themselves, or the order of them in the legend?

            Saving label settings: do you mean labels or popups? If the latter, you are right, as I rewrote the popup code recently, so the old code to save the settings no longer works. I shall try to add it back in.

          • steven Post author

            Tom

            The layer issue is with the legend/layer control, the layers are stacked in the correct order on the map. Also, is there a way to set a scroll bar for the legend when it gets long? I’m guessing that would be essential for mobile usage?

            I meant the label settings for popups (none, inline, above) are not saved as you go in and out of QGIS2Web. It would be great to have the ability to set them all at once for a layer as well as for individual fields (or instead)

            Cheers

            Steven

  • Tom Chadwin

    “I had to use the OpenLayers output because Leaflet doesn’t support the thematic shading of the layers”

    That didn’t sound right. Can you expand?

  • Clare

    Thanks for the demo of QGIS2Web, I’d never come across it before (probably read the name but not paid much attention). That will be really handy for me at work to share maps to FFs/officers. I’m not the ‘official’ GIS person so I don’t have access to our GIS servers, which is annoying, so normally everything goes by PDF as it’s my only way really (or layered PDF if I’m feeling adventurous!) . I’ve been playing around with CartoDB recently which I really like, but only the free version so is public and that’s a bit of a problem. So if I can get this to work locally, it’ll definitely be a good option.

    • steven Post author

      Glad you like it Clare. All praise should go to Tom Chadwin

      If you want to publish a map using QGIS2Web you will need access to a web server (either internal or public facing depending on your organisation). I created a folder on my server and then I can drop each project into a subfolder, it’s quite easy.

      Feel free to ask for help.

      Good luck

      Steven

        • Clare

          Cool 🙂 No webserver is perfect, because I don’t have access to one!
          I’ve been playing around this evening with some random files and it is working really well 🙂 I don’t need it to do anything fancy, just show our high risk households within each station ground (obviously why it can’t go anywhere public) to supplement the rather useless/unwieldy spreadsheet they currently get provided with.
          I’ll be able to export it somewhere on our shared drive and email send each station the link to their html file.
          The first thing they’ll probably do is print it out and stick it on the wall, but we can only try!!

Comments are closed.