Metadata for local authority housing statistics 2017 to 2018.
import pandas as pd
#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
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
#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)
(44, 385)
#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)
#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
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
{'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': ''}}
k = 'A'
xl[metadata[k]['sheet']]
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 |
xl[metadata[k]['sheet']].replace(to_replace=lahs_2017_18_dict).fillna('')
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 |
k = 'C'
tmp = xl[metadata[k]['sheet']]
tmp
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 |
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.
tmp.replace(to_replace=lahs_2017_18_dict).fillna('')
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) |
k = 'I'
tmp = xl[metadata[k]['sheet']]
tmp
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 |
tmp.replace(to_replace=lahs_2017_18_dict).fillna('')
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 |
k = 'J'
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.'
tmp = xl[metadata[k]['sheet']]
tmp
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 |
tmp.replace(to_replace=lahs_2017_18_dict).fillna('')
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 |
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
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 |
j2 = tmp.loc[19:26,[1,2,3]]
j2.columns = tmp.loc[16,[0,2,3]].tolist()
j2
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 |
j3 = tmp.loc[30:37,[1,2,3]]
j3.columns = tmp.loc[27,[0,2,3]].tolist()
j3
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 |
#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)