AMR_tables.md

Reproducing London Plan Annual Monitoring Report tables

James Gleeson May 2019

Introduction

The aim of this notebook is to show how to reproduce a few tables in the London Plan Annual Monitoring Report (AMR) from the London Development Database (LDD) data published on the London Datastore, and along the way to show some simple techniques for wrangling data and producing tables. It is a work in progress and will be added to over time.

The LDD includes detailed information on every housing development in London, based on data provided to the Greater London Authority by London's boroughs and development corporations. The data we'll be analysing is an extract from the LDD that covers all homes completed in 2016/17. Note that LDD is frequently revised over time so future snapshopts of completions in 2016/17 may not be exactly the same.

Setup

First let's load the packages we are going to use.

library(tidyverse) # various functions for working with data. Includes dplyr
library(rio) # reads Excel data from URLs
library(formattable)
library(janitor) # useful for some basic data cleaning 

Now let's load the data. We'll read in the 'Unit level' sheet only as that's what's used to generate the tables we're interested in.

unitc <- rio::import("https://data.london.gov.uk/download/london-plan-amr14-tables-and-data/ad929204-cbe9-4bb2-bed7-1c1d28d210c9/LDD%20-%20Housing%20Completions%20for%20AMR14.xlsx",
                   which = 2) %>% # import 'unit level' sheet
clean_names() # convert variable names to a common format

Data prep

The LDD records the exact number of bedrooms each unit has, while in the AMR this is 'topcoded' so that all those with 4 bedrooms or more are counted together. Let's create a similar topcoded variable. Note, although the AMR uses '1 bed', '2 beds' for the sake of brevity, these refer to dwellings with 1 bedroom, 2 bedrooms and so on.

unitc <- unitc %>%
  mutate(bedrooms = case_when(number_of_bedrooms == 1 ~ "1 bed",
                              number_of_bedrooms == 2 ~ "2 beds",
                              number_of_bedrooms == 3 ~ "3 beds",
                              number_of_bedrooms >= 4 ~ "4 beds or more"))

Analysis

Table 3.8

Let's start by trying to recreate table 3.8 in the AMR, which shows the number of bedrooms by tenure. First, we need to reorder the tenure variable so that it matches the order used in the AMR.

unitc$unit_tenure <- ordered(unitc$unit_tenure,
                            levels = c("Social Rented", "Intermediate", "Affordable Rent", "Market"))

Now we can produce the table. The code below filters out any NA records and counts the number of 'proposed units' - i.e. gross completions without netting off demolition. This table uses gross rather than net completions because the the number of bedrooms in demolished units is not always recorded for planning purposes.

There are a couple of things I haven't yet worked out how to do with tables like this: how to change the 'Total' label and how to insert a thousands separator without also adding two decimal places.

unitc %>% 
  filter(!is.na(bedrooms)) %>% # filter out rows where the number of bedrooms is not available
  group_by(bedrooms, Tenure = unit_tenure) %>% # group the data by bedrooms and tenure
  tally(proposed_units) %>% # sum the proposed units in each group
  spread(bedrooms, n) %>% # turn the table into 'wide' or crosstab format
  adorn_totals(where = "row") %>% # add row totals
  format_table() # pass the result through `format_table` to improve its look

Tenure	1 bed	2 beds	3 beds	4 beds or more
Social Rented	1018	1120	814	283
Intermediate	1151	1373	409	40
Affordable Rent	586	840	481	213
Market	16162	14446	4937	1879
Total	18917	17779	6641	2415

#### Table 3.9

This table shows Gross conventional completions by bedrooms and borough. The code below shows how to mix integer and percent formats in one table using format_table. Again I'm not sure how to reduce the number of decimal places in the percent column.

unitc %>% 
  filter(!is.na(bedrooms)) %>%
  group_by(bedrooms, Borough = borough) %>%
  tally(proposed_units) %>%
  spread(bedrooms, n, fill = 0) %>% # need to specify blanks filled with zero rather than NA
  adorn_totals(where = c("row", "col")) %>%
  mutate(`% 3 or more` = ((`3 beds` + `4 beds or more`)/`Total`)) %>%
  format_table(list(area(col = 7) ~ percent)) 

Borough	1 bed	2 beds	3 beds	4 beds or more	Total	% 3 or more
Barking and Dagenham	139	241	168	53	601	36.77%
Barnet	704	1145	436	99	2384	22.44%
Bexley	159	355	151	176	841	38.88%
Brent	654	653	148	23	1478	11.57%
Bromley	428	391	133	83	1035	20.87%
Camden	601	534	134	38	1307	13.16%
City of London	5	7	0	0	12	0.00%
Croydon	1627	878	408	66	2979	15.91%
Ealing	652	645	205	64	1566	17.18%
Enfield	462	289	175	96	1022	26.52%
Greenwich	1444	765	297	30	2536	12.89%
Hackney	494	505	282	89	1370	27.08%
Hammersmith and Fulham	531	480	158	161	1330	23.98%
Haringey	468	330	103	48	949	15.91%
Harrow	259	314	94	59	726	21.07%
Havering	157	206	190	124	677	46.38%
Hillingdon	396	318	65	118	897	20.40%
Hounslow	696	362	114	10	1182	10.49%
Islington	335	301	79	40	755	15.76%
Kensington and Chelsea	156	85	77	77	395	38.99%
Kingston upon Thames	133	107	31	34	305	21.31%
Lambeth	790	571	198	58	1617	15.83%
Lewisham	604	794	216	42	1656	15.58%
Merton	246	176	49	60	531	20.53%
Newham	1095	1058	387	54	2594	17.00%
Redbridge	403	277	81	60	821	17.17%
Richmond upon Thames	203	240	46	48	537	17.50%
Southwark	901	1136	396	96	2529	19.45%
Sutton	379	299	25	20	723	6.22%
Tower Hamlets	2159	1962	832	171	5124	19.57%
Waltham Forest	377	476	186	69	1108	23.01%
Wandsworth	755	1282	301	123	2461	17.23%
Westminster	505	597	476	126	1704	35.33%
Total	18917	17779	6641	2415	45752	19.79%