Local Authority Housing Returns

Metadata for local authority housing statistics 2017 to 2018.

In [1]:
import pandas as pd
In [2]:
#The simplest full dataset has code based column labels
lahs_2017_18 = pd.read_csv('LAHS_2017_18/LAHS_2017-18.csv')

#TIdy up whitespace in column names
lahs_2017_18.columns = [c.strip() for c in lahs_2017_18.columns]

#Find Island data
lahs_2017_18 = lahs_2017_18[lahs_2017_18['Area name'].str.contains('Wight')]

#Grab a dict of the Island data (colnames are dict keys, cell values are dict values)
lahs_2017_18_dict = lahs_2017_18.to_dict(orient='records')[0]

lahs_2017_18
Out[2]:
MHCLG code Area code Area name a1a a1b a2a a2aa a2ab a2b a2ba ... j3da j3db j3ea j3eb j3fa j3fb j3ga j3gb j3ha j3hb
21 P2114 E06000046 Isle of Wight 0 5 0 0.0 0 0 0.0 ... 0 0 0 0 0 0 0 0 0 0

1 rows × 385 columns

In [54]:
#Simple data quality check
#Number of informative / non-empty / non-zero cols vs total cols
lahs_2017_18.replace('0','').replace('-','').iloc[0].astype(bool).sum(), len(lahs_2017_18.columns)
Out[54]:
(44, 385)
In [3]:
#Read the bulk upload guidance doc
xl = pd.read_excel('LAHS_guidance_notes_bulk_upload.xlsx', sheet_name=None)

#Clean it...
for k in xl.keys():
    #Drop empty cols and empty rows
    xl[k] = xl[k].dropna(axis=0, how='all').dropna(axis=1, how='all').reset_index(drop=True)
    
In [4]:
#What are the sheets?
for k in xl.keys():
    print(k)
Cover
Guidance
Upload Guidance
A Stock
B Disposals
C Allocations
D Lettings
E Vacants
F Condition
G Management
H Rents and Rent Arrears
I Affordable Housing Supply
J Affordable Housing Starts
Data Sign Off
Bulk Upload Sheet
In [5]:
import re

#Create a lookup of stuff
metadata={}
#Just grab sheetnames starting with one letter in range A-J then a space...
for k in [k for k in xl.keys() if re.search('^[ABCDEFGHIJ]\s',k) ]:
    
    metadata[k.split()[0]]={'sheet':k,'fullname':xl[k].columns[0],
                            'name':' '.join(k.split()[1:]), 'desc':''}
    xl[k].columns = list(range(0,len(xl[k].columns)))
                         
metadata
Out[5]:
{'A': {'sheet': 'A Stock',
  'fullname': 'Section A  - Dwelling Stock',
  'name': 'Stock',
  'desc': ''},
 'B': {'sheet': 'B Disposals',
  'fullname': 'Section B  - Local Authority Dwelling Sales and Transfers ',
  'name': 'Disposals',
  'desc': ''},
 'C': {'sheet': 'C Allocations',
  'fullname': 'Section C  - Allocations',
  'name': 'Allocations',
  'desc': ''},
 'D': {'sheet': 'D Lettings',
  'fullname': 'Section D  - Lettings, Nominations and Mobility Schemes',
  'name': 'Lettings',
  'desc': ''},
 'E': {'sheet': 'E Vacants',
  'fullname': 'Section E: Vacants',
  'name': 'Vacants',
  'desc': ''},
 'F': {'sheet': 'F Condition',
  'fullname': 'Section F  - Condition of Dwelling Stock',
  'name': 'Condition',
  'desc': ''},
 'G': {'sheet': 'G Management',
  'fullname': 'Section G  - Stock Management',
  'name': 'Management',
  'desc': ''},
 'H': {'sheet': 'H Rents and Rent Arrears',
  'fullname': 'Section H  - Local Authority Rents and Rent Arrears',
  'name': 'Rents and Rent Arrears',
  'desc': ''},
 'I': {'sheet': 'I Affordable Housing Supply',
  'fullname': 'Section I  - Affordable Housing Supply',
  'name': 'Affordable Housing Supply',
  'desc': ''},
 'J': {'sheet': 'J Affordable Housing Starts',
  'fullname': 'Section J - Provision of New Build Affordable Housing STARTS',
  'name': 'Affordable Housing Starts',
  'desc': ''}}

A Stock

In [169]:
k = 'A'
In [170]:
xl[metadata[k]['sheet']]
Out[170]:
0 1 2 3 4
0 Dwelling Stock In your Local Authority Area as... NaN NaN NaN NaN
1 This section collects information on dwelling ... NaN NaN NaN NaN
2 1. Number of dwellings located in your local a... NaN NaN Census Definition NaN
3 NaN a. Local Authority Owned (including those owne... NaN a1a NaN
4 NaN b. 'Other' public sector (eg government depart... NaN a1b NaN
5 Dwelling Stock Owned by your Local Authority NaN NaN NaN NaN
6 This section collects information on dwelling ... NaN NaN NaN NaN
7 2. Number of Dwellings owned by your Local Aut... NaN NaN of which NaN
8 NaN NaN Social Rent Affordable Rent All
9 NaN a. Bedsits (including Public Finance Initiativ... a2aa a2ab a2a
10 NaN b. One bedroom (including Public Finance Initi... a2ba a2bb a2b
11 NaN c. Two bedrooms (including Public Finance Init... a2ca a2cb a2c
12 NaN d. Three bedrooms (including Public Finance In... a2da a2db a2d
13 NaN e. Four bedrooms (including Public Finance Ini... a2ea a2eb a2e
14 NaN f. Five bedrooms (including Public Finance Ini... a2fa a2fb a2f
15 NaN g. Six or more bedrooms (including Public Fina... a2ga a2gb a2g
16 NaN h. Dwellings equivalent of HMOs Hostels (inclu... a2ha a2hb a2h
17 NaN i. Total (including Public Finance Initiative ... a2iaa a2iab a2ia
18 NaN Total excluding PFI and Shared Ownership NaN NaN a2ib
19 NaN NaN Social Rent NaN All (£ millions)
20 3. Total value of stock at 1st January 1999 pr... NaN a3aa NaN a3a
21 Changes to Local Authority owned stock NaN NaN NaN NaN
22 This section refers to stock owned by your Loc... NaN NaN NaN NaN
23 stock at the beginning of the year as it colle... NaN NaN NaN NaN
24 had any demolitions, conversions, acquisitions... NaN NaN NaN NaN
25 4. Changes to Local Authority owned stock NaN NaN NaN NaN
26 NaN a. Number of demolitions NaN a4a NaN
27 NaN b i. Number of conversions resulting in an inc... NaN a4ba NaN
28 NaN b ii. Number of conversions resulting in a dec... NaN a4bb NaN
29 NaN c. Number of new builds NaN a4c NaN
30 NaN d. Number of acquisitions NaN a4d NaN
31 Private Sector Demolition NaN NaN NaN NaN
32 This section should be completed by all Local ... NaN NaN NaN NaN
33 5. Total number of private sector dwellings de... NaN NaN a5a NaN
In [206]:
xl[metadata[k]['sheet']].replace(to_replace=lahs_2017_18_dict).fillna('')
Out[206]:
0 1 2 3 4
0 Dwelling Stock In your Local Authority Area as...
1 This section collects information on dwelling ...
2 1. Number of dwellings located in your local a... Census Definition
3 a. Local Authority Owned (including those owne... 0
4 b. 'Other' public sector (eg government depart... 5
5 Dwelling Stock Owned by your Local Authority
6 This section collects information on dwelling ...
7 2. Number of Dwellings owned by your Local Aut... of which
8 Social Rent Affordable Rent All
9 a. Bedsits (including Public Finance Initiativ... 0 0 0
10 b. One bedroom (including Public Finance Initi... 0 0 0
11 c. Two bedrooms (including Public Finance Init... 0 0 0
12 d. Three bedrooms (including Public Finance In... 0 0 0
13 e. Four bedrooms (including Public Finance Ini... 0 0 0
14 f. Five bedrooms (including Public Finance Ini... 0 0 0
15 g. Six or more bedrooms (including Public Fina... 0 0 0
16 h. Dwellings equivalent of HMOs Hostels (inclu... 0 0 0
17 i. Total (including Public Finance Initiative ... 0 0 0
18 Total excluding PFI and Shared Ownership 0
19 Social Rent All (£ millions)
20 3. Total value of stock at 1st January 1999 pr... 0 0
21 Changes to Local Authority owned stock
22 This section refers to stock owned by your Loc...
23 stock at the beginning of the year as it colle...
24 had any demolitions, conversions, acquisitions...
25 4. Changes to Local Authority owned stock
26 a. Number of demolitions 0
27 b i. Number of conversions resulting in an inc... 0
28 b ii. Number of conversions resulting in a dec... 0
29 c. Number of new builds 0
30 d. Number of acquisitions 0
31 Private Sector Demolition
32 This section should be completed by all Local ...
33 5. Total number of private sector dwellings de... 0

C Allocations

In [215]:
k = 'C'
In [216]:
tmp = xl[metadata[k]['sheet']]
tmp
Out[216]:
0 1 2 3 4
0 NaN NaN NaN As at 1st April 2018 NaN
1 Waiting Lists NaN NaN NaN NaN
2 1. Total households on the housing waiting lis... NaN NaN NaN cc1a
3 NaN How many bedrooms did these households require? NaN NaN NaN
4 NaN (cc1a should equal the sum of cc1aa to cc1ae) NaN NaN NaN
5 NaN a. Households requiring 1 bedroom NaN NaN cc1aa
6 NaN b. Households requiring 2 bedrooms NaN NaN cc1ab
7 NaN c. Households requiring 3 bedrooms NaN NaN cc1ac
8 NaN d. Households requiring more than 3 bedrooms NaN NaN cc1ad
9 NaN e. Households requiring an unspecif... NaN NaN cc1ae
10 2. Have you changed your waiting list criteria... NaN NaN NaN cc2a
11 3. Do your waiting list criteria include: NaN NaN NaN NaN
12 (only answer Y to one of these questions; if y... NaN NaN NaN NaN
13 NaN a. A residency test? (Y or N) NaN NaN cc3a
14 NaN NaN i . If yes, how many years of residency are re... NaN cc3aa
15 NaN NaN If your residency test requires less than 1 ye... NaN NaN
16 NaN NaN household on the waiting list then please rep... NaN NaN
17 NaN b. A local connection test? (Y or N) NaN NaN cc3b
18 4. Do your waiting list criteria disqualify an... NaN NaN NaN cc4a
19 NaN a. If your waiting list criteria do disqualify... NaN NaN cc4aa
20 NaN NaN i. If yes, how many such exceptions have you m... NaN cc4ab
21 5. Total number of households on the housing w... NaN NaN NaN cc5a
22 NaN How many of these households were in each reas... NaN NaN NaN
23 NaN (cc5a does not need to equal the sum of a-e be... NaN NaN NaN
24 NaN a. People who are homeless within the meaning ... NaN NaN cc5aa
25 NaN b. People who are owed a duty by any local hou... NaN NaN cc5ab
26 NaN Act (or under section 65(2) or 68(2) of the Ho... NaN NaN NaN
27 NaN secured by any such authority under section 19... NaN NaN NaN
28 NaN c. People occupying insanitary or overcrowded ... NaN NaN cc5ac
29 NaN conditions NaN NaN NaN
30 NaN d. People who need to move on medical or welfa... NaN NaN cc5ad
31 NaN e. People who need to move to a particular loc... NaN NaN cc5ae
32 6. If your scheme gives additional preference ... NaN NaN NaN cc6a
33 needs, how many households on your waiting lis... NaN NaN NaN NaN
34 NaN a. Of which, how many are members of the Armed... NaN NaN cc6aa
35 Allocations NaN NaN NaN NaN
36 7. Do you participate in a choice-based lettin... NaN NaN NaN cc7a
37 from a selection of available to let vacancie... NaN NaN NaN NaN
38 housing need)? (Y or N) NaN NaN NaN NaN
39 8. Does your allocation scheme or transfer pol... NaN NaN NaN cc8a
40 their current home? (Y or N) NaN NaN NaN NaN
In [8]:
txt='''
In terms of the households required by numbers of bedrooms:

\t- {cc1aa} required 1 bedroom
\t- {cc1ab} required 2 bedrooms
\t- {cc1ac} required 3 bedrooms
\t- {cc1ad} required more than bedrooms
\t- {cc1ae} required an unspecified number of bedrooms.
'''

print(txt.format( **lahs_2017_18_dict ))
In terms of the households required by numbers of bedrooms:

	- 1030 required 1 bedroom
	- 600 required 2 bedrooms
	- 342 required 3 bedrooms
	- 89 required more than bedrooms
	- 0 required an unspecified number of bedrooms.

In [217]:
tmp.replace(to_replace=lahs_2017_18_dict).fillna('')
Out[217]:
0 1 2 3 4
0 As at 1st April 2018
1 Waiting Lists
2 1. Total households on the housing waiting lis... 2061
3 How many bedrooms did these households require?
4 (cc1a should equal the sum of cc1aa to cc1ae)
5 a. Households requiring 1 bedroom 1030
6 b. Households requiring 2 bedrooms 600
7 c. Households requiring 3 bedrooms 342
8 d. Households requiring more than 3 bedrooms 89
9 e. Households requiring an unspecif... 0
10 2. Have you changed your waiting list criteria... N
11 3. Do your waiting list criteria include:
12 (only answer Y to one of these questions; if y...
13 a. A residency test? (Y or N) Y
14 i . If yes, how many years of residency are re... 5
15 If your residency test requires less than 1 ye...
16 household on the waiting list then please rep...
17 b. A local connection test? (Y or N) cc3b
18 4. Do your waiting list criteria disqualify an... N
19 a. If your waiting list criteria do disqualify... N
20 i. If yes, how many such exceptions have you m... ..
21 5. Total number of households on the housing w... 1442
22 How many of these households were in each reas...
23 (cc5a does not need to equal the sum of a-e be...
24 a. People who are homeless within the meaning ... 63
25 b. People who are owed a duty by any local hou... 148
26 Act (or under section 65(2) or 68(2) of the Ho...
27 secured by any such authority under section 19...
28 c. People occupying insanitary or overcrowded ... 1194
29 conditions
30 d. People who need to move on medical or welfa... 533
31 e. People who need to move to a particular loc... 0
32 6. If your scheme gives additional preference ... 9
33 needs, how many households on your waiting lis...
34 a. Of which, how many are members of the Armed... 0
35 Allocations
36 7. Do you participate in a choice-based lettin... Y
37 from a selection of available to let vacancie...
38 housing need)? (Y or N)
39 8. Does your allocation scheme or transfer pol... Y
40 their current home? (Y or N)

I Affordable Housing Supply

In [212]:
k = 'I'
In [213]:
tmp = xl[metadata[k]['sheet']]
tmp
Out[213]:
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
0 This section should be completed by all Local ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 Provision of New Build Additional Affordable H... NaN NaN NaN Provision of additional affordable housing oth... NaN NaN NaN NaN NaN NaN NaN Affordable housing funded with recycled Right ... NaN NaN NaN NaN NaN NaN NaN
2 Units sold under the Help to buy scheme are no... NaN NaN NaN Please see guidance for more detailed definitions NaN NaN NaN NaN NaN NaN Units NaN NaN NaN NaN NaN NaN NaN NaN
3 For Questions 1 and 2 please report all new bu... NaN NaN Units completed NaN NaN NaN NaN NaN NaN NaN i6a Units counted in question 17 may also be count... NaN NaN NaN NaN NaN NaN NaN
4 1. In populations of less than 3,000 people NaN NaN i1a 6. In populations of less than 3,000 people (a... NaN NaN NaN NaN NaN NaN NaN affordable housing supply. NaN NaN NaN NaN NaN NaN NaN
5 2. On Rural Exception Sites NaN NaN i2a In Questions 7, 8, 9 only report affordable ho... NaN NaN NaN NaN NaN NaN NaN 17. Number of affordable homes completed with ... NaN New Build Acquisitions NaN NaN NaN NaN
6 NaN NaN NaN NaN NaN NaN Units\nSocial Rent\n\n(a) Units Intermediate Rent\n(b) Units Affordable Rent \n(c) Units Affordable Ownership (exc SO) (d) Shared Ownership\n(e) Total number of units\n(f) recycled RTB receipts NaN NaN NaN NaN NaN NaN NaN
7 In Questions 3, 4 and 5 only report new build ... NaN NaN NaN If any dwellings are not funded by the HCA/GLA... NaN NaN NaN NaN NaN NaN NaN NaN a. 1 bedroom i17aa i17ab of which are Flats of which Other ex-local authority General market
8 3. Owned by Local Authority, not reported to H... NaN (a) (b) 7.Owned by Local Authority (not reported to HC... NaN i7a i7b i7c i7d i7e i7f NaN b. 2 bedrooms i17ba i17bb NaN Buy Back NaN NaN
9 NaN NaN Units completed without developer contributions Units completed with developer contributions t... NaN NaN NaN NaN NaN NaN NaN NaN NaN c. 3+ bedrooms i17ca i17cb NaN (SI No 501) NaN NaN
10 If any dwellings are not funded by the HCA/GLA... NaN NaN NaN 8.Owned by Private Registered Providers (not r... NaN i8a i8b i8c i8d i8e i8f NaN d. Total i17da i17db i17e i17dc i17dd i17de
11 please record how the dwellings are funded in ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN Include where the local authority contra... NaN NaN NaN NaN NaN NaN NaN
12 NaN NaN NaN NaN 9. Owned by Non-registered provider NaN i9a i9b i9c i9d i9e i9f Excludes any provision with HCA or GLA g... NaN NaN NaN NaN NaN NaN NaN
13 NaN a.Social Rent i3aa i3ab NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
14 NaN b.Affordable Rent i3ba i3bb NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
15 NaN NaN NaN NaN Planning for Affordable Housing units with dev... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
16 NaN c.Intermediate Rent i3ca i3cb NaN NaN NaN NaN NaN NaN NaN Units with developer contributions NaN NaN NaN NaN NaN NaN NaN NaN
17 NaN NaN NaN NaN 10. Affordable units granted final planning pe... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
18 NaN d.Affordable Home Ownership (excluding Shared ... i3da i3db NaN a. For Social Rent NaN NaN NaN NaN NaN i10a NaN NaN NaN NaN NaN NaN NaN NaN
19 NaN e.Shared Ownership i3ea i3eb NaN b. For Affordable Rent NaN NaN NaN NaN NaN i10b NaN NaN NaN NaN NaN NaN NaN NaN
20 NaN f.Total Number of units i3fa i3fb NaN c. For Intermediate Rent NaN NaN NaN NaN NaN i10c NaN NaN NaN NaN NaN NaN NaN NaN
21 NaN NaN NaN NaN NaN d. For Affordable Home Ownership (excluding Sh... NaN NaN NaN NaN NaN i10d NaN NaN NaN NaN NaN NaN NaN NaN
22 4. Owned by Private Registered Providers (incl... NaN Units completed without developer contributions Units completed with developer contributions t... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
23 (note Affordable Rent delivery is not asked fo... NaN NaN NaN NaN e. For Shared Ownership NaN NaN NaN NaN NaN i10e NaN NaN NaN NaN NaN NaN NaN NaN
24 If any dwellings are not funded by the HCA/GLA... NaN NaN NaN NaN f. For Starter Homes NaN NaN NaN NaN NaN i10f NaN NaN NaN NaN NaN NaN NaN NaN
25 please record how the dwellings are funded in ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
26 NaN a.Social Rent i4aa i4ab NaN g. For unknown affordable tenure NaN NaN NaN NaN NaN i10g NaN NaN NaN NaN NaN NaN NaN NaN
27 NaN b.Affordable Rent i4ba i4bb NaN h. Total number of units NaN NaN NaN NaN NaN i10h NaN NaN NaN NaN NaN NaN NaN NaN
28 NaN c.Intermediate Rent i4ca i4cb NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
29 NaN NaN NaN NaN Other Developer Contributions to Affordable Ho... NaN NaN NaN NaN NaN NaN hectares NaN NaN NaN NaN NaN NaN NaN NaN
30 NaN d.Affordable Home Ownership (excluding Shared ... i4da i4db NaN NaN NaN NaN NaN NaN NaN i11a NaN NaN NaN NaN NaN NaN NaN NaN
31 NaN NaN NaN NaN 11. Amount of discounted or free land received... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
32 NaN e.Shared Ownership i4ea i4eb NaN NaN NaN NaN Value of contributions £ thousands NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
33 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN i12a NaN NaN NaN NaN NaN NaN NaN NaN
34 NaN f.Total Number of units i4fa i4fb 12.Financial contributions from planning oblig... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
35 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN i13a NaN NaN NaN NaN NaN NaN NaN NaN
36 5. Owned by non-registered providers NaN Units completed without developer contributions Units completed with developer contributions t... 13.Financial contributions from planning oblig... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
37 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN i14a NaN NaN NaN NaN NaN NaN NaN NaN
38 If any dwellings are not funded by the HCA/GLA... NaN NaN NaN 14.Financial contributions from planning oblig... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
39 please record how the dwellings are funded in ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
40 NaN a.Social Rent i5aa i5ab NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
41 NaN NaN NaN NaN Cash Incentive Scheme Grants NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
42 NaN b.Affordable Rent i5ba i5bb (Only grants for the purchase of private secto... NaN NaN NaN NaN NaN NaN units NaN NaN NaN NaN NaN NaN NaN NaN
43 NaN NaN NaN NaN 15. Total number of grants NaN NaN NaN NaN NaN NaN i15a NaN NaN NaN NaN NaN NaN NaN NaN
44 NaN c.Intermediate Rent i5ca i5cb NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
45 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN £ thousands NaN NaN NaN NaN NaN NaN NaN NaN
46 NaN d.Affordable Home Ownership (excluding Shared ... i5da i5db 16. Total expenditure (£000s) NaN NaN NaN NaN NaN NaN i16a NaN NaN NaN NaN NaN NaN NaN NaN
47 NaN e.Shared Ownership i5ea i5eb NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
48 NaN f.Total Number of units i5fa i5fb NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
In [214]:
tmp.replace(to_replace=lahs_2017_18_dict).fillna('')
Out[214]:
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
0 This section should be completed by all Local ...
1 Provision of New Build Additional Affordable H... Provision of additional affordable housing oth... Affordable housing funded with recycled Right ...
2 Units sold under the Help to buy scheme are no... Please see guidance for more detailed definitions Units
3 For Questions 1 and 2 please report all new bu... Units completed 0 Units counted in question 17 may also be count...
4 1. In populations of less than 3,000 people 13 6. In populations of less than 3,000 people (a... affordable housing supply.
5 2. On Rural Exception Sites 0 In Questions 7, 8, 9 only report affordable ho... 17. Number of affordable homes completed with ... New Build Acquisitions
6 Units\nSocial Rent\n\n(a) Units Intermediate Rent\n(b) Units Affordable Rent \n(c) Units Affordable Ownership (exc SO) (d) Shared Ownership\n(e) Total number of units\n(f) recycled RTB receipts
7 In Questions 3, 4 and 5 only report new build ... If any dwellings are not funded by the HCA/GLA... a. 1 bedroom 0 0 of which are Flats of which Other ex-local authority General market
8 3. Owned by Local Authority, not reported to H... (a) (b) 7.Owned by Local Authority (not reported to HC... 0 0 0 0 0 0 b. 2 bedrooms 0 0 Buy Back
9 Units completed without developer contributions Units completed with developer contributions t... c. 3+ bedrooms 0 0 (SI No 501)
10 If any dwellings are not funded by the HCA/GLA... 8.Owned by Private Registered Providers (not r... 0 0 0 0 0 0 d. Total 0 0 0 0 0 0
11 please record how the dwellings are funded in ... Include where the local authority contra...
12 9. Owned by Non-registered provider 0 0 0 0 0 0 Excludes any provision with HCA or GLA g...
13 a.Social Rent 0 0
14 b.Affordable Rent 0 0
15 Planning for Affordable Housing units with dev...
16 c.Intermediate Rent 0 0 Units with developer contributions
17 10. Affordable units granted final planning pe...
18 d.Affordable Home Ownership (excluding Shared ... 0 0 a. For Social Rent -
19 e.Shared Ownership 0 0 b. For Affordable Rent -
20 f.Total Number of units 0 0 c. For Intermediate Rent -
21 d. For Affordable Home Ownership (excluding Sh... -
22 4. Owned by Private Registered Providers (incl... Units completed without developer contributions Units completed with developer contributions t...
23 (note Affordable Rent delivery is not asked fo... e. For Shared Ownership -
24 If any dwellings are not funded by the HCA/GLA... f. For Starter Homes -
25 please record how the dwellings are funded in ...
26 a.Social Rent 0 0 g. For unknown affordable tenure 351
27 b.Affordable Rent 0 13 h. Total number of units 351
28 c.Intermediate Rent 0 0
29 Other Developer Contributions to Affordable Ho... hectares
30 d.Affordable Home Ownership (excluding Shared ... 0 0 0
31 11. Amount of discounted or free land received...
32 e.Shared Ownership 0 0 Value of contributions £ thousands
33 -
34 f.Total Number of units 0 13 12.Financial contributions from planning oblig...
35 10
36 5. Owned by non-registered providers Units completed without developer contributions Units completed with developer contributions t... 13.Financial contributions from planning oblig...
37 0
38 If any dwellings are not funded by the HCA/GLA... 14.Financial contributions from planning oblig...
39 please record how the dwellings are funded in ...
40 a.Social Rent 0 0
41 Cash Incentive Scheme Grants
42 b.Affordable Rent 0 0 (Only grants for the purchase of private secto... units
43 15. Total number of grants 0
44 c.Intermediate Rent 0 0
45 £ thousands
46 d.Affordable Home Ownership (excluding Shared ... 0 0 16. Total expenditure (£000s) 0
47 e.Shared Ownership 0 0
48 f.Total Number of units 0 0

J Affordable Housing Starts'

In [208]:
k = 'J'
In [209]:
metadata['J']['desc']= 'Completed by all Local Authorities. Report units STARTED during financial year 2017-18.\n \
             Units that have been started and completed in the year will be counted both in section I and J.'
In [210]:
tmp = xl[metadata[k]['sheet']]
tmp
Out[210]:
0 1 2 3
0 This section should be completed by all Local ... NaN NaN NaN
1 Provision of New Build Additional Affordable H... NaN NaN NaN
2 In Questions 1, 2 and 3 only report new build ... NaN NaN NaN
3 Units that have been started and completed in ... NaN NaN NaN
4 1. Owned by Local Authority, not reported to H... NaN (a) (b)
5 NaN NaN Units started without developer contributions Units started with developer contributions thr...
6 If any dwellings are not funded by the HCA/GLA... NaN NaN NaN
7 please record how the dwellings are funded in ... NaN NaN NaN
8 NaN a.Social Rent j1aa j1ab
9 NaN b.Affordable Rent j1ba j1bb
10 NaN c.Intermediate Rent j1ca j1cb
11 NaN d.Affordable Home Ownership (excluding Shared ... j1da j1db
12 NaN e.Shared Ownership j1ea j1eb
13 NaN f.Starter Homes j1fa j1fb
14 NaN g.Unknown tenure j1ga j1gb
15 NaN h.Total Number of units j1ha j1hb
16 2. Owned by Private Registered Providers (incl... NaN Units starteed without developer contributions Units started with developer contributions thr...
17 If any dwellings are not funded by the HCA/GLA... NaN NaN NaN
18 please record how the dwellings are funded in ... NaN NaN NaN
19 NaN a.Social Rent j2aa j2ab
20 NaN b.Affordable Rent j2ba j2bb
21 NaN c.Intermediate Rent j2ca j2cb
22 NaN d.Affordable Home Ownership (excluding Shared ... j2da j2db
23 NaN e.Shared Ownership j2ea j2eb
24 NaN f.Starter Homes j2fa j2fb
25 NaN g.Unknown tenure j2ga j2gb
26 NaN h.Total Number of units j2ha j2hb
27 3. Owned by non-registered providers NaN Units started without developer contributions Units started with developer contributions thr...
28 If any dwellings are not funded by the HCA/GLA... NaN NaN NaN
29 please record how the dwellings are funded in ... NaN NaN NaN
30 NaN a.Social Rent j3aa j3ab
31 NaN b.Affordable Rent j3ba j3bb
32 NaN c.Intermediate Rent j3ca j3cb
33 NaN d.Affordable Home Ownership (excluding Shared ... j3da j3db
34 NaN e.Shared Ownership j3ea j3eb
35 NaN f.Starter Homes j3fa j3fb
36 NaN g.Unknown tenure j3ga j3gb
37 NaN h.Total Number of units j3ha j3hb
In [211]:
tmp.replace(to_replace=lahs_2017_18_dict).fillna('')
Out[211]:
0 1 2 3
0 This section should be completed by all Local ...
1 Provision of New Build Additional Affordable H...
2 In Questions 1, 2 and 3 only report new build ...
3 Units that have been started and completed in ...
4 1. Owned by Local Authority, not reported to H... (a) (b)
5 Units started without developer contributions Units started with developer contributions thr...
6 If any dwellings are not funded by the HCA/GLA...
7 please record how the dwellings are funded in ...
8 a.Social Rent 0 0
9 b.Affordable Rent 0 0
10 c.Intermediate Rent 0 0
11 d.Affordable Home Ownership (excluding Shared ... 0 0
12 e.Shared Ownership 0 0
13 f.Starter Homes 0 0
14 g.Unknown tenure 0 0
15 h.Total Number of units 0 0
16 2. Owned by Private Registered Providers (incl... Units starteed without developer contributions Units started with developer contributions thr...
17 If any dwellings are not funded by the HCA/GLA...
18 please record how the dwellings are funded in ...
19 a.Social Rent 0 0
20 b.Affordable Rent 0 0
21 c.Intermediate Rent 0 0
22 d.Affordable Home Ownership (excluding Shared ... 0 0
23 e.Shared Ownership 0 0
24 f.Starter Homes 0 0
25 g.Unknown tenure 0 0
26 h.Total Number of units 0 0
27 3. Owned by non-registered providers Units started without developer contributions Units started with developer contributions thr...
28 If any dwellings are not funded by the HCA/GLA...
29 please record how the dwellings are funded in ...
30 a.Social Rent 0 0
31 b.Affordable Rent 0 0
32 c.Intermediate Rent 0 0
33 d.Affordable Home Ownership (excluding Shared ... 0 0
34 e.Shared Ownership 0 0
35 f.Starter Homes 0 0
36 g.Unknown tenure 0 0
37 h.Total Number of units 0 0
In [133]:
j1 = tmp.loc[8:15,[1,2,3]].reset_index(drop=True)
j1.columns = tmp.loc[4,[0]].tolist() + tmp.loc[5,[2,3]].tolist()
j1
Out[133]:
1. Owned by Local Authority, not reported to HCA or GLA Units started without developer contributions Units started with developer contributions through planning obligations
0 a.Social Rent j1aa j1ab
1 b.Affordable Rent j1ba j1bb
2 c.Intermediate Rent j1ca j1cb
3 d.Affordable Home Ownership (excluding Shared ... j1da j1db
4 e.Shared Ownership j1ea j1eb
5 f.Starter Homes j1fa j1fb
6 g.Unknown tenure j1ga j1gb
7 h.Total Number of units j1ha j1hb
In [134]:
j2 = tmp.loc[19:26,[1,2,3]]
j2.columns = tmp.loc[16,[0,2,3]].tolist()
j2
Out[134]:
2. Owned by Private Registered Providers (including HAs) not reported to HCA or GLA Units starteed without developer contributions Units started with developer contributions through planning obligations
19 a.Social Rent j2aa j2ab
20 b.Affordable Rent j2ba j2bb
21 c.Intermediate Rent j2ca j2cb
22 d.Affordable Home Ownership (excluding Shared ... j2da j2db
23 e.Shared Ownership j2ea j2eb
24 f.Starter Homes j2fa j2fb
25 g.Unknown tenure j2ga j2gb
26 h.Total Number of units j2ha j2hb
In [135]:
j3 = tmp.loc[30:37,[1,2,3]]
j3.columns = tmp.loc[27,[0,2,3]].tolist()
j3
Out[135]:
3. Owned by non-registered providers Units started without developer contributions Units started with developer contributions through planning obligations
30 a.Social Rent j3aa j3ab
31 b.Affordable Rent j3ba j3bb
32 c.Intermediate Rent j3ca j3cb
33 d.Affordable Home Ownership (excluding Shared ... j3da j3db
34 e.Shared Ownership j3ea j3eb
35 f.Starter Homes j3fa j3fb
36 g.Unknown tenure j3ga j3gb
37 h.Total Number of units j3ha j3hb

Unused But Possibly Useful Elsewhere

In [166]:
#https://gist.github.com/bgusach/a967e0587d6e01e889fd1d776c5f3729
#Python string multireplacement
import re

def multireplace(string, replacements):
    """
    Given a string and a replacement map, it returns the replaced string.
    :param str string: string to execute replacements on
    :param dict replacements: replacement dictionary {value to find: value to replace}
    :rtype: str
    """
    # Place longer ones first to keep shorter substrings from matching where the longer ones should take place
    # For instance given the replacements {'ab': 'AB', 'abc': 'ABC'} against the string 'hey abc', it should produce
    # 'hey ABC' and not 'hey ABc'
    substrs = sorted(replacements, key=len, reverse=True)

    # Create a big OR regex that matches any of the substrings to replace
    regexp = re.compile('|'.join(map(re.escape, substrs)))

    # For each match, look up the new string in the replacements
    return regexp.sub(lambda match: replacements[match.group(0)], string)