!wget https://www.gov.uk/government/uploads/system/uploads/attachment_data/file/390701/Local_Authority_Housing_Statistics_dataset_2013-14.xlsx
--2015-04-14 09:15:17-- https://www.gov.uk/government/uploads/system/uploads/attachment_data/file/390701/Local_Authority_Housing_Statistics_dataset_2013-14.xlsx Resolving www.gov.uk... 185.31.19.144 Connecting to www.gov.uk|185.31.19.144|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 977934 (955K) [application/vnd.openxmlformats-officedocument.spreadsheetml.sheet] Saving to: 'Local_Authority_Housing_Statistics_dataset_2013-14.xlsx' Local_Authority_Hou 100%[=====================>] 955.01K 32.2KB/s in 29s 2015-04-14 09:15:48 (32.7 KB/s) - 'Local_Authority_Housing_Statistics_dataset_2013-14.xlsx' saved [977934/977934]
import pandas as pd
dfx=pd.ExcelFile('Local_Authority_Housing_Statistics_dataset_2013-14.xlsx')
dfx.sheet_names
[u'Menu', u'A', u'B', u'C', u'D', u'E', u'F', u'G', u'H', u'I']
#Menu sheet parse to identify sheets A-I
import re
sd=re.compile(r'Section (\w) - (.*)$')
sheetDetails={}
for row in dfx.parse('Menu')[[1]].values:
if str(row[0]).startswith('Section'):
sheetDetails[sd.match(row[0]).group(1)]=sd.match(row[0]).group(2)
sheetDetails
{u'A': u'Dwelling Stock', u'B': u'Local Authority Housing Disposals', u'C': u'Allocations', u'D': u'Lettings, Nominations and Mobility Schemes', u'E': u'Vacants', u'F': u'Condition of Dwelling Stock', u'G': u'Stock Management', u'H': u'Local authority Rents and Rent Arrears', u'I': u'Affordable Housing Supply'}
dfx.parse('A',header=None)[:15]
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ... | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | Local Authority Housing Statistics dataset (in... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
1 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
3 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Total number of dwellings located in your loca... | NaN | Total Number of Dwellings owned by your Local ... | ... | NaN | NaN | NaN | NaN | NaN | Changes to Local Authority owned stock over 20... | NaN | NaN | NaN | Total number of private sector dwellings demol... |
4 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Bedsits | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
5 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Local Authority Owned (including those owned b... | 'Other' public sector | All, including PFI & SO | ... | Social Rent | Affordable Rent | Total - Excluding PFI and Shared ownership | Total value of stock at January 1999 prices (i... | Total value of social rent stock at January 19... | Number of demolitions | Number of conversions (can be positive or nega... | Number of new builds | Number of acquisitions | NaN |
6 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
7 | NaN | DCLG code | Former\nONS code | Current\nONS code | Met and Shire County Totals | Lower and Single Tier Authority Data | NaN | a1a 1 | a1b 1 2 | a2a | ... | a2iaa | a2iab | a2ib | a3a | a3aa | a4a | a4b | a4c | a4d | a5a |
8 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
9 | England | NaN | NaN | NaN | NaN | NaN | NaN | 1668683 | 100344 | 41682.55 | ... | 1664371 | 2935 | 1643032 | 75901.5 | 75072.96 | 4361 | 226 | 1289 | 790 | 1243 |
10 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
11 | Unitary Authorities | NaN | NaN | NaN | NaN | NaN | NaN | 336044 | 20698 | 6776 | ... | 340577 | 589 | 338782 | 12076.52 | 12033.25 | 1081 | 116 | 231 | 186 | 269 |
12 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
13 | NaN | F0114 | 00HA | E06000022 | NaN | Bath and North East Somerset UA | NaN | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
14 | NaN | K0235 | 00KB | E06000055 | NaN | Bedford UA | NaN | 0 | 460 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
15 rows × 44 columns
df=dfx.parse('A',header=None)
df=df.dropna(how='all')
row = df[df.apply(lambda x: (x == "DCLG code").any(), axis=1)].index.tolist()[0]#.values[0] # will be an array
row
7
df=dfx.parse('A',header=row).dropna(how='all').dropna(how='all',axis=1)
df=df[df['DCLG code'].notnull()].reset_index(drop=True)
df[:5]
DCLG code | Former ONS code | Current ONS code | Met and Shire County Totals | Lower and Single Tier Authority Data | a1a 1 | a1b 1 2 | a2a | a2aa | a2ab | ... | a2iaa | a2iab | a2ib | a3a | a3aa | a4a | a4b | a4c | a4d | a5a | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | F0114 | 00HA | E06000022 | NaN | Bath and North East Somerset UA | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0.000 | 0.000 | 0 | 0 | 0 | 0 | 0 |
1 | K0235 | 00KB | E06000055 | NaN | Bedford UA | 0 | 460 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0.000 | 0.000 | 0 | 0 | 0 | 0 | 0 |
2 | M2372 | 00EX | E06000008 | NaN | Blackburn with Darwen UA | 0 | 46 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0.000 | 0.000 | 0 | 0 | 0 | 0 | 18 |
3 | J2373 | 00EY | E06000009 | NaN | Blackpool UA | 5059 | 0 | 95 | 95 | 0 | ... | 4916 | 143 | 5059 | 130.737 | 126.400 | 176 | 55 | 0 | 0 | 0 |
4 | G1250 | 00HN | E06000028 | NaN | Bournemouth UA | 5089 | 0 | 375 | 375 | 0 | ... | 5076 | 29 | 5089 | 248.866 | 246.417 | 3 | 3 | 5 | 6 | 0 |
5 rows × 42 columns
def dfgrabber(dfx,sheet):
#First pass - identify row for headers
df=dfx.parse(sheet,header=None)
df=df.dropna(how='all')
row = df[df.apply(lambda x: (x == "DCLG code").any(), axis=1)].index.tolist()[0]#.values[0] # will be an array
#Second pass - generate dataframe
df=dfx.parse(sheet,header=row).dropna(how='all').dropna(how='all',axis=1)
df=df[df['DCLG code'].notnull()].reset_index(drop=True)
df.columns=[c.split(' ')[0] for c in df.columns]
return df,row
dfgrabber(dfx,'A')[:5]
( DCLG Former\nONS \ 0 F0114 00HA 1 K0235 00KB 2 M2372 00EX 3 J2373 00EY 4 G1250 00HN 5 R0335 00MA 6 Q1445 00ML 7 Z0116 00HB 8 P0240 00KC 9 R0660 00EQ 10 A0665 00EW 11 D0840 00HE 12 X1355 00EJ 13 N1350 00EH 14 C1055 00FK 15 E2001 00FB 16 D0650 00ET 17 H0724 00EB 18 W1850 00GA 19 P2114 00MW 20 Z0835 00HF 21 V2004 00FA 22 W2465 00FN 23 B0230 00KA 24 A2280 00LC 25 W0734 00EC 26 Y0435 00MG 27 B2002 00FC 28 Y2003 00FD 29 D0121 00HC .. ... ... 363 R1845 47UG 364 These cells contain imputed data - this data s... NaN 365 These cells contain unfinalised data - local a... NaN 366 Local authorities that no longer exist but are... NaN 367 Notes NaN 368 1. Question 1 uses the Census definition of th... NaN 369 2. Other public sector dwellings includes dwel... NaN 370 Source NaN 371 Local Authority Housing Statistics 2013-14, th... NaN 372 https://www.gov.uk/government/statistics/local... NaN 373 The relevant live tables can be found here NaN 374 https://www.gov.uk/government/statistical-data... NaN 375 Guidance and Questionaire Form NaN 376 Guidance on the above data and a blank version... NaN 377 https://www.gov.uk/government/publications/com... NaN 378 Regional Statistics NaN 379 For information on why regional statistics are... NaN 380 https://www.gov.uk/government/speeches/statist... NaN 381 New Geographies NaN 382 On 1st April 2009 nine new unitary authorities... NaN 383 Figures are no longer collected on the previou... NaN 384 Cheshire West UA from Chester, Ellesmere Port ... NaN 385 Cheshire East UA was formed from Congleton, Cr... NaN 386 Bedford UA from Bedford; and Central Bedfordsh... NaN 387 The remaining five new UAs were each formed fr... NaN 388 Contact: NaN 389 Telephone: 0303 44 41861 NaN 390 Email: housing.statistics@communities.gsi.gov.uk NaN 391 Latest update: 11 December 2014 NaN 392 Next update: Early 2015 (small update after co... NaN Current\nONS Met Lower a1a a1b a2a a2aa \ 0 E06000022 NaN Bath and North East Somerset UA 0 0 0 0 1 E06000055 NaN Bedford UA 0 460 0 0 2 E06000008 NaN Blackburn with Darwen UA 0 46 0 0 3 E06000009 NaN Blackpool UA 5059 0 95 95 4 E06000028 NaN Bournemouth UA 5089 0 375 375 5 E06000036 NaN Bracknell Forest UA 76 359 0 0 6 E06000043 NaN Brighton and Hove UA 11733 40 726 726 7 E06000023 NaN Bristol, City of UA 27826 500 334 334 8 E06000056 NaN Central Bedfordshire UA 5158 674 70 70 9 E06000049 NaN Cheshire East UA 97 18 0 0 10 E06000050 NaN Cheshire West and Chester UA 5643 224 0 0 11 E06000052 NaN Cornwall UA 10462 700 19 19 12 E06000047 NaN County Durham UA 18484 0 58 58 13 E06000005 NaN Darlington UA 5367 0 0 0 14 E06000015 NaN Derby UA 13381 50 35 35 15 E06000011 NaN East Riding of Yorkshire UA 11533 165 161 161 16 E06000006 NaN Halton UA 0 0 0 0 17 E06000001 NaN Hartlepool UA 302 28 0 0 18 E06000019 NaN Herefordshire, County of UA 34 321 0 0 19 E06000046 NaN Isle of Wight UA 0 3 0 0 20 E06000053 NaN Isles of Scilly UA 6 124 0 0 0 21 E06000010 NaN Kingston upon Hull, City of UA 25347 235 151 151 22 E06000016 NaN Leicester UA 21956 600 501 501 23 E06000032 NaN Luton UA 8082 0 173 173 24 E06000035 NaN Medway UA 3018 304 305 305 25 E06000002 NaN Middlesbrough UA 0 0 0 0 26 E06000042 NaN Milton Keynes UA 11314 5 289 289 27 E06000012 NaN North East Lincolnshire UA 9 165 0 0 28 E06000013 NaN North Lincolnshire UA 18 26 0 0 29 E06000024 NaN North Somerset UA 0 0 0 0 .. ... ... ... ... ... ... ... 363 E07000239 NaN Wyre Forest 0 14 0 0 364 NaN NaN NaN NaN NaN NaN NaN 365 NaN NaN NaN NaN NaN NaN NaN 366 NaN NaN NaN NaN NaN NaN NaN 367 NaN NaN NaN NaN NaN NaN NaN 368 NaN NaN NaN NaN NaN NaN NaN 369 NaN NaN NaN NaN NaN NaN NaN 370 NaN NaN NaN NaN NaN NaN NaN 371 NaN NaN NaN NaN NaN NaN NaN 372 NaN NaN NaN NaN NaN NaN NaN 373 NaN NaN NaN NaN NaN NaN NaN 374 NaN NaN NaN NaN NaN NaN NaN 375 NaN NaN NaN NaN NaN NaN NaN 376 NaN NaN NaN NaN NaN NaN NaN 377 NaN NaN NaN NaN NaN NaN NaN 378 NaN NaN NaN NaN NaN NaN NaN 379 NaN NaN NaN NaN NaN NaN NaN 380 NaN NaN NaN NaN NaN NaN NaN 381 NaN NaN NaN NaN NaN NaN NaN 382 NaN NaN NaN NaN NaN NaN NaN 383 NaN NaN NaN NaN NaN NaN NaN 384 NaN NaN NaN NaN NaN NaN NaN 385 NaN NaN NaN NaN NaN NaN NaN 386 NaN NaN NaN NaN NaN NaN NaN 387 NaN NaN NaN NaN NaN NaN NaN 388 NaN NaN NaN NaN NaN NaN NaN 389 NaN NaN NaN NaN NaN NaN NaN 390 NaN NaN NaN NaN NaN NaN NaN 391 NaN NaN NaN NaN NaN NaN NaN 392 NaN NaN NaN NaN NaN NaN NaN a2ab ... a2iaa a2iab a2ib a3a a3aa a4a a4b a4c a4d \ 0 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 1 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 2 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 3 0 ... 4916.00 143 5059 130.737 126.400 176 55 0 0 4 0 ... 5076.00 29 5089 248.866 246.417 3 3 5 6 5 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 6 0 ... 11733.00 0 11733 661.123 661.123 0 0 15 1 7 0 ... 27826.00 0 27826 912.101 912.101 4 0 6 0 8 0 ... 5158.00 0 5158 324.602 324.392 0 0 0 1 9 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 10 0 ... 5570.00 0 5570 184.839 184.839 0 0 0 0 11 0 ... 10458.00 4 10462 399.941 399.398 0 0 9 5 12 0 ... 18487.00 0 18487 456.047 456.047 47 2 0 0 13 0 ... 5367.00 0 5367 146.863 146.863 0 0 0 0 14 0 ... 13372.00 9 13381 369.458 368.468 65 0 65 0 15 0 ... 11000.00 291 11291 356.123 345.141 0 0 24 37 16 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 17 0 ... 0.00 0 0 0.000 0.000 0 0 0 35 18 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 19 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 20 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 21 0 ... 25347.00 0 25347 542.316 542.316 254 50 0 0 22 0 ... 21956.00 0 21956 513.617 513.617 0 -9 0 0 23 0 ... 8061.00 6 8056 288.574 288.000 0 0 4 35 24 0 ... 3014.00 0 3014 118.521 118.521 0 0 0 0 25 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 26 0 ... 12300.65 0 11309 474.246 474.246 1 0 0 4 27 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 28 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 29 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 .. ... ... ... ... ... ... ... ... ... ... ... 363 0 ... 0.00 0 0 0.000 0.000 0 0 0 0 364 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 365 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 366 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 367 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 368 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 369 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 370 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 371 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 372 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 373 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 374 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 375 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 376 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 377 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 378 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 379 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 380 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 381 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 382 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 383 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 384 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 385 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 386 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 387 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 388 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 389 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 390 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 391 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN 392 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN a5a 0 0 1 0 2 18 3 0 4 0 5 0 6 0 7 15 8 0 9 0 10 0 11 0 12 47 13 0 14 0 15 0 16 0 17 52 18 0 19 0 20 0 21 93 22 0 23 0 24 0 25 0 26 2 27 2 28 0 29 0 .. ... 363 0 364 NaN 365 NaN 366 NaN 367 NaN 368 NaN 369 NaN 370 NaN 371 NaN 372 NaN 373 NaN 374 NaN 375 NaN 376 NaN 377 NaN 378 NaN 379 NaN 380 NaN 381 NaN 382 NaN 383 NaN 384 NaN 385 NaN 386 NaN 387 NaN 388 NaN 389 NaN 390 NaN 391 NaN 392 NaN [393 rows x 42 columns], 7)
pd.read_csv('messyindex.csv',header=[0,1,2])
Total number of dwellings located in your local authority area (using the Census definition) | Unnamed: 1_level_0 | Total Number of Dwellings owned by your Local Authority as at 1 April 2013 | Unnamed: 3_level_0 | Unnamed: 4_level_0 | Unnamed: 5_level_0 | Unnamed: 6_level_0 | Unnamed: 7_level_0 | Unnamed: 8_level_0 | Unnamed: 9_level_0 | ... | Unnamed: 27_level_0 | Unnamed: 28_level_0 | Unnamed: 29_level_0 | Unnamed: 30_level_0 | Unnamed: 31_level_0 | Changes to Local Authority owned stock over 2012-13 | Unnamed: 33_level_0 | Unnamed: 34_level_0 | Unnamed: 35_level_0 | Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Unnamed: 0_level_1 | Unnamed: 1_level_1 | Bedsits | Unnamed: 3_level_1 | Unnamed: 4_level_1 | One bedroom | Unnamed: 6_level_1 | Unnamed: 7_level_1 | Two bedrooms | Unnamed: 9_level_1 | ... | Unnamed: 27_level_1 | Unnamed: 28_level_1 | Unnamed: 29_level_1 | Unnamed: 30_level_1 | Unnamed: 31_level_1 | Unnamed: 32_level_1 | Unnamed: 33_level_1 | Unnamed: 34_level_1 | Unnamed: 35_level_1 | Unnamed: 36_level_1 | |
Local Authority Owned (including those owned by other Local Authorities) | 'Other' public sector | All, including PFI & SO | Social Rent | Affordable Rent | All, including PFI & SO | Social Rent | Affordable Rent | All, including PFI & SO | Social Rent | ... | Social Rent | Affordable Rent | Total - Excluding PFI and Shared ownership | Total value of stock at January 1999 prices (in millions of pounds, to 3 decimal places) | Total value of social rent stock at January 1999 prices (in millions of pounds, to 3 decimal places) | Number of demolitions | Number of conversions (can be positive or negative) | Number of new builds | Number of acquisitions | Unnamed: 36_level_2 | |
0 | 4,115 | 759 | 39 | 39 | 0 | 611 | 610 | 1 | 1,746 | 1,708 | ... | 4,024 | 91 | 4,115 | 174 | 170 | 48 | 0 | 0 | 2 | 0 |
1 rows × 37 columns
#Next step - decode the column codes
row=7
df=dfx.parse('A',header=None)
title=df[0][[0]][0]
title
u'Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section A'
df=df[1:row+1].dropna(how='all').dropna(how='all',axis=1)
df
1 | 2 | 3 | 4 | 5 | 7 | 8 | 9 | 10 | 11 | ... | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
3 | NaN | NaN | NaN | NaN | NaN | Total number of dwellings located in your loca... | NaN | Total Number of Dwellings owned by your Local ... | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | Changes to Local Authority owned stock over 20... | NaN | NaN | NaN | Total number of private sector dwellings demol... |
4 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Bedsits | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
5 | NaN | NaN | NaN | NaN | NaN | Local Authority Owned (including those owned b... | 'Other' public sector | All, including PFI & SO | Social Rent | Affordable Rent | ... | Social Rent | Affordable Rent | Total - Excluding PFI and Shared ownership | Total value of stock at January 1999 prices (i... | Total value of social rent stock at January 19... | Number of demolitions | Number of conversions (can be positive or nega... | Number of new builds | Number of acquisitions | NaN |
7 | DCLG code | Former\nONS code | Current\nONS code | Met and Shire County Totals | Lower and Single Tier Authority Data | a1a 1 | a1b 1 2 | a2a | a2aa | a2ab | ... | a2iaa | a2iab | a2ib | a3a | a3aa | a4a | a4b | a4c | a4d | a5a |
4 rows × 42 columns
xx=dfx.parse('A',header=None)[1:row].dropna(how='all')
xx
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ... | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
3 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Total number of dwellings located in your loca... | NaN | Total Number of Dwellings owned by your Local ... | ... | NaN | NaN | NaN | NaN | NaN | Changes to Local Authority owned stock over 20... | NaN | NaN | NaN | Total number of private sector dwellings demol... |
4 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Bedsits | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
5 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Local Authority Owned (including those owned b... | 'Other' public sector | All, including PFI & SO | ... | Social Rent | Affordable Rent | Total - Excluding PFI and Shared ownership | Total value of stock at January 1999 prices (i... | Total value of social rent stock at January 19... | Number of demolitions | Number of conversions (can be positive or nega... | Number of new builds | Number of acquisitions | NaN |
3 rows × 44 columns
#Fill down
xx.fillna(method='ffill', axis=0,inplace=True)
#Fill across
xx=xx.fillna(method='ffill', axis=1)
xx
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ... | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
3 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Total number of dwellings located in your loca... | Total number of dwellings located in your loca... | Total Number of Dwellings owned by your Local ... | ... | Total Number of Dwellings owned by your Local ... | Total Number of Dwellings owned by your Local ... | Total Number of Dwellings owned by your Local ... | Total Number of Dwellings owned by your Local ... | Total Number of Dwellings owned by your Local ... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Total number of private sector dwellings demol... |
4 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Total number of dwellings located in your loca... | Total number of dwellings located in your loca... | Bedsits | ... | Total | Total | Total | Total | Total | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Total number of private sector dwellings demol... |
5 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Local Authority Owned (including those owned b... | 'Other' public sector | All, including PFI & SO | ... | Social Rent | Affordable Rent | Total - Excluding PFI and Shared ownership | Total value of stock at January 1999 prices (i... | Total value of social rent stock at January 19... | Number of demolitions | Number of conversions (can be positive or nega... | Number of new builds | Number of acquisitions | Total number of private sector dwellings demol... |
3 rows × 44 columns
xx=xx.append(dfx.parse('A',header=None)[row:row+1])
xx
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ... | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
3 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Total number of dwellings located in your loca... | Total number of dwellings located in your loca... | Total Number of Dwellings owned by your Local ... | ... | Total Number of Dwellings owned by your Local ... | Total Number of Dwellings owned by your Local ... | Total Number of Dwellings owned by your Local ... | Total Number of Dwellings owned by your Local ... | Total Number of Dwellings owned by your Local ... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Total number of private sector dwellings demol... |
4 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Total number of dwellings located in your loca... | Total number of dwellings located in your loca... | Bedsits | ... | Total | Total | Total | Total | Total | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Changes to Local Authority owned stock over 20... | Total number of private sector dwellings demol... |
5 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Local Authority Owned (including those owned b... | 'Other' public sector | All, including PFI & SO | ... | Social Rent | Affordable Rent | Total - Excluding PFI and Shared ownership | Total value of stock at January 1999 prices (i... | Total value of social rent stock at January 19... | Number of demolitions | Number of conversions (can be positive or nega... | Number of new builds | Number of acquisitions | Total number of private sector dwellings demol... |
7 | NaN | DCLG code | Former\nONS code | Current\nONS code | Met and Shire County Totals | Lower and Single Tier Authority Data | NaN | a1a 1 | a1b 1 2 | a2a | ... | a2iaa | a2iab | a2ib | a3a | a3aa | a4a | a4b | a4c | a4d | a5a |
4 rows × 44 columns
dfx.parse('A',header=None)[row:row+1]
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ... | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
7 | NaN | DCLG code | Former\nONS code | Current\nONS code | Met and Shire County Totals | Lower and Single Tier Authority Data | NaN | a1a 1 | a1b 1 2 | a2a | ... | a2iaa | a2iab | a2ib | a3a | a3aa | a4a | a4b | a4c | a4d | a5a |
1 rows × 44 columns
#Add empty row trick via
#http://nbviewer.ipython.org/github/rasbt/python_reference/blob/master/tutorials/things_in_pandas.ipynb
import numpy as np
def addemptyrow(df):
df = df.append(pd.Series([np.nan]*len(df.columns),index=df.columns),ignore_index=True)
return df
xx=addemptyrow(xx)
xx.to_csv('multi_index.csv',header=False,index=False)
mxx=pd.read_csv('multi_index.csv',header=[0,1,2])
mxx
Unnamed: 0_level_0 | Unnamed: 1_level_0 | Unnamed: 2_level_0 | Unnamed: 3_level_0 | Unnamed: 4_level_0 | Unnamed: 5_level_0 | Unnamed: 6_level_0 | Total number of dwellings located in your local authority area (using the Census definition) | Total Number of Dwellings owned by your Local Authority as at 1 April 2013 | Changes to Local Authority owned stock over 2012-13 | Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Unnamed: 0_level_1 | Unnamed: 1_level_1 | Unnamed: 2_level_1 | Unnamed: 3_level_1 | Unnamed: 4_level_1 | Unnamed: 5_level_1 | Unnamed: 6_level_1 | Total number of dwellings located in your local authority area (using the Census definition) | Bedsits | ... | Total | Changes to Local Authority owned stock over 2012-13 | Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority | |||||||||
Unnamed: 0_level_2 | Unnamed: 1_level_2 | Unnamed: 2_level_2 | Unnamed: 3_level_2 | Unnamed: 4_level_2 | Unnamed: 5_level_2 | Unnamed: 6_level_2 | Local Authority Owned (including those owned by other Local Authorities) | 'Other' public sector | All, including PFI & SO | ... | Social Rent | Affordable Rent | Total - Excluding PFI and Shared ownership | Total value of stock at January 1999 prices (in millions of pounds, to 3 decimal places) | Total value of social rent stock at January 1999 prices (in millions of pounds, to 3 decimal places) | Number of demolitions | Number of conversions (can be positive or negative) | Number of new builds | Number of acquisitions | Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority | |
0 | NaN | DCLG code | Former\nONS code | Current\nONS code | Met and Shire County Totals | Lower and Single Tier Authority Data | NaN | a1a 1 | a1b 1 2 | a2a | ... | a2iaa | a2iab | a2ib | a3a | a3aa | a4a | a4b | a4c | a4d | a5a |
1 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
2 rows × 44 columns
for c in mxx.columns.get_level_values(0).tolist():
if c.startswith('Unnamed'):
mxx = mxx.drop(c, level=0, axis=1)
mxx
Total number of dwellings located in your local authority area (using the Census definition) | Total Number of Dwellings owned by your Local Authority as at 1 April 2013 | Changes to Local Authority owned stock over 2012-13 | Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total number of dwellings located in your local authority area (using the Census definition) | Bedsits | One bedroom | Two bedrooms | ... | Total | Changes to Local Authority owned stock over 2012-13 | Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority | ||||||||||||||
Local Authority Owned (including those owned by other Local Authorities) | 'Other' public sector | All, including PFI & SO | Social Rent | Affordable Rent | All, including PFI & SO | Social Rent | Affordable Rent | All, including PFI & SO | Social Rent | ... | Social Rent | Affordable Rent | Total - Excluding PFI and Shared ownership | Total value of stock at January 1999 prices (in millions of pounds, to 3 decimal places) | Total value of social rent stock at January 1999 prices (in millions of pounds, to 3 decimal places) | Number of demolitions | Number of conversions (can be positive or negative) | Number of new builds | Number of acquisitions | Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority | |
0 | a1a 1 | a1b 1 2 | a2a | a2aa | a2ab | a2b | a2ba | a2bb | a2c | a2ca | ... | a2iaa | a2iab | a2ib | a3a | a3aa | a4a | a4b | a4c | a4d | a5a |
1 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
2 rows × 37 columns
dd=mxx.to_dict(orient='list')
dd
{('Changes to Local Authority owned stock over 2012-13', 'Changes to Local Authority owned stock over 2012-13', 'Number of acquisitions'): ['a4d', nan], ('Changes to Local Authority owned stock over 2012-13', 'Changes to Local Authority owned stock over 2012-13', 'Number of conversions (can be positive or negative)'): ['a4b', nan], ('Changes to Local Authority owned stock over 2012-13', 'Changes to Local Authority owned stock over 2012-13', 'Number of demolitions'): ['a4a', nan], ('Changes to Local Authority owned stock over 2012-13', 'Changes to Local Authority owned stock over 2012-13', 'Number of new builds'): ['a4c', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Bedsits', 'Affordable Rent'): ['a2ab', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Bedsits', 'All, including PFI & SO'): ['a2a', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Bedsits', 'Social Rent'): ['a2aa', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Equivalent of HMOs Hostels', 'Affordable Rent'): ['a2hb', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Equivalent of HMOs Hostels', 'All, including PFI & SO'): ['a2h', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Equivalent of HMOs Hostels', 'Social Rent'): ['a2ha', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Five bedrooms', 'Affordable Rent'): ['a2fb', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Five bedrooms', 'All, including PFI & SO'): ['a2f', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Five bedrooms', 'Social Rent'): ['a2fa', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Four bedrooms', 'Affordable Rent'): ['a2eb', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Four bedrooms', 'All, including PFI & SO'): ['a2e', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Four bedrooms', 'Social Rent'): ['a2ea', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'One bedroom', 'Affordable Rent'): ['a2bb', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'One bedroom', 'All, including PFI & SO'): ['a2b', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'One bedroom', 'Social Rent'): ['a2ba', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Six or more bedrooms', 'Affordable Rent'): ['a2gb', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Six or more bedrooms', 'All, including PFI & SO'): ['a2g', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Six or more bedrooms', 'Social Rent'): ['a2ga', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Three bedrooms', 'Affordable Rent'): ['a2db', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Three bedrooms', 'All, including PFI & SO'): ['a2d', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Three bedrooms', 'Social Rent'): ['a2da', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Affordable Rent'): ['a2iab', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'All, including PFI & SO'): ['a2ia 1', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Social Rent'): ['a2iaa', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Total - Excluding PFI and Shared ownership'): ['a2ib', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Total value of social rent stock at January 1999 prices (in millions of pounds, to 3 decimal places)'): ['a3aa', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Total value of stock at January 1999 prices (in millions of pounds, to 3 decimal places)'): ['a3a', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Two bedrooms', 'Affordable Rent'): ['a2cb', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Two bedrooms', 'All, including PFI & SO'): ['a2c', nan], ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Two bedrooms', 'Social Rent'): ['a2ca', nan], ('Total number of dwellings located in your local authority area (using the Census definition)', 'Total number of dwellings located in your local authority area (using the Census definition)', "'Other' public sector"): ['a1b 1 2', nan], ('Total number of dwellings located in your local authority area (using the Census definition)', 'Total number of dwellings located in your local authority area (using the Census definition)', 'Local Authority Owned (including those owned by other Local Authorities)'): ['a1a 1', nan], ('Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority', 'Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority', 'Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority'): ['a5a', nan]}
keyx={}
for r in dd:
keyx[dd[r][0].split(' ')[0]]=r
keyx
{'a1a': ('Total number of dwellings located in your local authority area (using the Census definition)', 'Total number of dwellings located in your local authority area (using the Census definition)', 'Local Authority Owned (including those owned by other Local Authorities)'), 'a1b': ('Total number of dwellings located in your local authority area (using the Census definition)', 'Total number of dwellings located in your local authority area (using the Census definition)', "'Other' public sector"), 'a2a': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Bedsits', 'All, including PFI & SO'), 'a2aa': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Bedsits', 'Social Rent'), 'a2ab': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Bedsits', 'Affordable Rent'), 'a2b': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'One bedroom', 'All, including PFI & SO'), 'a2ba': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'One bedroom', 'Social Rent'), 'a2bb': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'One bedroom', 'Affordable Rent'), 'a2c': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Two bedrooms', 'All, including PFI & SO'), 'a2ca': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Two bedrooms', 'Social Rent'), 'a2cb': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Two bedrooms', 'Affordable Rent'), 'a2d': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Three bedrooms', 'All, including PFI & SO'), 'a2da': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Three bedrooms', 'Social Rent'), 'a2db': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Three bedrooms', 'Affordable Rent'), 'a2e': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Four bedrooms', 'All, including PFI & SO'), 'a2ea': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Four bedrooms', 'Social Rent'), 'a2eb': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Four bedrooms', 'Affordable Rent'), 'a2f': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Five bedrooms', 'All, including PFI & SO'), 'a2fa': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Five bedrooms', 'Social Rent'), 'a2fb': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Five bedrooms', 'Affordable Rent'), 'a2g': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Six or more bedrooms', 'All, including PFI & SO'), 'a2ga': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Six or more bedrooms', 'Social Rent'), 'a2gb': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Six or more bedrooms', 'Affordable Rent'), 'a2h': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Equivalent of HMOs Hostels', 'All, including PFI & SO'), 'a2ha': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Equivalent of HMOs Hostels', 'Social Rent'), 'a2hb': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Equivalent of HMOs Hostels', 'Affordable Rent'), 'a2ia': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'All, including PFI & SO'), 'a2iaa': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Social Rent'), 'a2iab': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Affordable Rent'), 'a2ib': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Total - Excluding PFI and Shared ownership'), 'a3a': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Total value of stock at January 1999 prices (in millions of pounds, to 3 decimal places)'), 'a3aa': ('Total Number of Dwellings owned by your Local Authority as at 1 April 2013', 'Total', 'Total value of social rent stock at January 1999 prices (in millions of pounds, to 3 decimal places)'), 'a4a': ('Changes to Local Authority owned stock over 2012-13', 'Changes to Local Authority owned stock over 2012-13', 'Number of demolitions'), 'a4b': ('Changes to Local Authority owned stock over 2012-13', 'Changes to Local Authority owned stock over 2012-13', 'Number of conversions (can be positive or negative)'), 'a4c': ('Changes to Local Authority owned stock over 2012-13', 'Changes to Local Authority owned stock over 2012-13', 'Number of new builds'), 'a4d': ('Changes to Local Authority owned stock over 2012-13', 'Changes to Local Authority owned stock over 2012-13', 'Number of acquisitions'), 'a5a': ('Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority', 'Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority', 'Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority')}
import math
import collections
def coldecoder(dfx,sheet,row):
zz=dfx.parse(sheet,header=None)
stitle=zz[0][[0]][0]
xx=zz[1:row].dropna(how='all')
#Fill down
xx.fillna(method='ffill', axis=0,inplace=True)
#Fill across
xx=xx.fillna(method='ffill', axis=1)
#How many rows in the header?
keydepth=len(xx)
header=[i for i in range(0,keydepth)]
xx=xx.append(zz[row:row+1])
xx.to_csv('multi_index.csv',header=False,index=False,encoding='utf-8')
mxx=pd.read_csv('multi_index.csv',header=header,encoding='utf-8')
for c in mxx.columns.get_level_values(0).tolist():
if c.startswith('Unnamed'):
mxx = mxx.drop(c, level=0, axis=1)
dd=mxx.to_dict(orient='list')
keyx=collections.OrderedDict() #{}
for r in dd:
if not pd.isnull(dd[r][0]):
keyx[dd[r][0].split(' ')[0]]=r
return stitle,keyx,keydepth
df,row=dfgrabber(dfx,'B')
sname,skey,kd=coldecoder(dfx,'B',row)
sname
u'Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section B'
df,row=dfgrabber(dfx,'C')
sname,skey,kd=coldecoder(dfx,'C',row)
skey
{u'c5a': (u'Do you participate in a choice-based lettings scheme (i.e. a system allowing housing applicants to choose from a selection of available to let vacancies advertised openly or made known to registered applicants in housing need)? (Y or N)', u'Do you participate in a choice-based lettings scheme (i.e. a system allowing housing applicants to choose from a selection of available to let vacancies advertised openly or made known to registered applicants in housing need)? (Y or N)'), u'cc1a': (u'Total households on the housing waiting list at 1st April 2013', u'Total households on the housing waiting list at 1st April 2013'), u'cc1aa': (u'How many bedrooms did these households require?', u'Households requiring 1 bedroom'), u'cc1ab': (u'How many bedrooms did these households require?', u'Households requiring 2 bedrooms'), u'cc1ac': (u'How many bedrooms did these households require?', u'Households requiring 3 bedrooms'), u'cc1ad': (u'How many bedrooms did these households require?', u'Households requiring more than 3 bedrooms'), u'cc1ae': (u'How many bedrooms did these households require?', u'Households requiring an unspecified number of bedrooms or those on the register more than once'), u'cc2a': (u'Have you changed your waiting list criteria since last year in light of the changes in the Localism Act? (Y or N)', u'Have you changed your waiting list criteria since last year in light of the changes in the Localism Act? (Y or N)'), u'cc3a': (u'Total households on the housing waiting list in a reasonable preference category at 1st April 2013', u'Total households on the housing waiting list in a reasonable preference category at 1st April 2013'), u'cc3aa': (u'How many of these households were in each reasonable preference group?', u'People who are homeless within the meaning given in Part VII of the Act, regardless of whether there is a statutory duty to house them'), u'cc3ab': (u'How many of these households were in each reasonable preference group?', u'People who are owed a duty by any local housing authority under section 190(2), 193(2) or 195(2) of the Act\xa0(or under section 65(2) or 68(2) of the Housing Act 1985) or who are occupying accommodation secured by any such authority under section 192(3) of the Act'), u'cc3ac': (u'How many of these households were in each reasonable preference group?', u'People occupying insanitary or overcrowded housing or otherwise living in unsatisfactory housing conditions'), u'cc3ad': (u'How many of these households were in each reasonable preference group?', u'People who need to move on medical or welfare grounds, including grounds relating to a disability'), u'cc3ae': (u'How many of these households were in each reasonable preference group?', u'People who need to move to a particular locality in the district of the authority, where failure to meet that need would cause hardship (to themselves or to others).'), u'cc4a': (u'If your scheme gives additional preference to those in reasonable preference groups with urgent housing needs, how many households on your waiting lists does this apply to?', u'If your scheme gives additional preference to those in reasonable preference groups with urgent housing needs, how many households on your waiting lists does this apply to?')}
df
DCLG | Former ONS | Current ONS | Met | Lower | cc1a | cc1aa | cc1ab | cc1ac | cc1ad | cc1ae | cc2a | cc3a | cc3aa | cc3ab | cc3ac | cc3ad | cc3ae | cc4a | c5a | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | F0114 | 00HA | E06000022 | NaN | Bath and North East Somerset UA | 4747 | 2757.000000 | 1303.000000 | 477.000000 | 210.000000 | 0.000000 | Y | 608 | 33 | 15.000000 | 23.000000 | 296.000000 | 10.000000 | 206 | Y |
1 | K0235 | 00KB | E06000055 | NaN | Bedford UA | 945 | 356.000000 | 347.000000 | 158.000000 | 84.000000 | 0.000000 | Y | 884 | 94 | 23.000000 | 497.000000 | 151.000000 | 3.000000 | 30 | Y |
2 | M2372 | 00EX | E06000008 | NaN | Blackburn with Darwen UA | 4892 | 2711.000000 | 1431.000000 | 580.000000 | 170.000000 | 0.000000 | N | 1956 | 41 | 6.000000 | 1048.000000 | 520.000000 | 50.000000 | 373 | Y |
3 | J2373 | 00EY | E06000009 | NaN | Blackpool UA | 4843 | 2756.000000 | 1408.000000 | 490.000000 | 181.000000 | 8.000000 | Y | 1349 | 40 | 15.000000 | 179.000000 | 460.000000 | 138.000000 | 119 | Y |
4 | G1250 | 00HN | E06000028 | NaN | Bournemouth UA | 4122 | 2129.000000 | 1218.000000 | 660.000000 | 115.000000 | 0.000000 | N | 3806 | 661 | 0.000000 | 2304.000000 | 1291.000000 | 124.000000 | 15 | Y |
5 | R0335 | 00MA | E06000036 | NaN | Bracknell Forest UA | 2069 | 1089.000000 | 404.000000 | 357.000000 | 219.000000 | 0.000000 | N | 1843 | 63 | 60.000000 | 1214.000000 | 202.000000 | 1.000000 | 1 | Y |
6 | Q1445 | 00ML | E06000043 | NaN | Brighton and Hove UA | 19071 | 10471.000000 | 5284.000000 | 2715.000000 | 601.000000 | 0.000000 | N | 13319 | 1048 | 0.000000 | 4528.000000 | 4290.000000 | 65.000000 | 0 | Y |
7 | Z0116 | 00HB | E06000023 | NaN | Bristol, City of UA | 14513 | 7940.000000 | 3851.000000 | 1497.000000 | 407.000000 | 818.000000 | N | 5309 | 347 | 9.000000 | 1973.000000 | 1147.000000 | 1833.000000 | 303 | Y |
8 | P0240 | 00KC | E06000056 | NaN | Central Bedfordshire UA | 3673 | 1450.000000 | 1129.000000 | 325.000000 | 88.000000 | 681.000000 | N | 195 | 65 | 39.000000 | 13.000000 | 78.000000 | 0.000000 | 0 | Y |
9 | R0660 | 00EQ | E06000049 | NaN | Cheshire East UA | 6018 | 3125.000000 | 2054.000000 | 684.000000 | 139.000000 | 16.000000 | N | 2686 | 31 | 14.000000 | 504.000000 | 410.000000 | 0.000000 | 58 | Y |
10 | A0665 | 00EW | E06000050 | NaN | Cheshire West and Chester UA | 6085 | 4281.000000 | 1208.000000 | 339.000000 | 257.000000 | 0.000000 | Y | 536 | 26 | 11.000000 | 188.000000 | 262.000000 | 49.000000 | 122 | Y |
11 | D0840 | 00HE | E06000052 | NaN | Cornwall UA | 26915 | 13540.137105 | 8685.444664 | 3742.416008 | 947.002223 | 0.000000 | Y | 12816 | 2506 | 0.000000 | 3308.000000 | 2268.000000 | 354.000000 | 159 | Y |
12 | X1355 | 00EJ | E06000047 | NaN | County Durham UA | 11302 | 7493.000000 | 2727.000000 | 945.000000 | 137.000000 | 0.000000 | N | 5148 | 27 | 51.000000 | 925.000000 | 3965.000000 | 180.000000 | 263 | Y |
13 | N1350 | 00EH | E06000005 | NaN | Darlington UA | 1632 | 273.000000 | 655.000000 | 483.000000 | 221.000000 | 0.000000 | N | 522 | 6 | 130.000000 | 111.000000 | 222.000000 | 27.000000 | 0 | Y |
14 | C1055 | 00FK | E06000015 | NaN | Derby UA | 3868 | 1954.000000 | 1169.000000 | 398.000000 | 347.000000 | 0.000000 | Y | 3562 | 449 | 103.000000 | 2242.000000 | 1293.000000 | 1750.000000 | 1954 | Y |
15 | E2001 | 00FB | E06000011 | NaN | East Riding of Yorkshire UA | 5021 | 395.000000 | 1262.000000 | 302.000000 | 76.000000 | 2986.000000 | y | 1568 | 44 | 126.665064 | 691.301933 | 339.389332 | 71.381903 | 0 | n |
16 | D0650 | 00ET | E06000006 | NaN | Halton UA | 3099 | 1860.000000 | 892.000000 | 268.000000 | 79.000000 | 0.000000 | N | 568 | 19 | 9.000000 | 183.000000 | 366.000000 | 0.000000 | 35 | Y |
17 | H0724 | 00EB | E06000001 | NaN | Hartlepool UA | 2615 | 1465.000000 | 742.000000 | 252.000000 | 156.000000 | 0.000000 | Y | 821 | 5 | 28.000000 | 2.000000 | 573.000000 | 0.000000 | 39 | Y |
18 | W1850 | 00GA | E06000019 | NaN | Herefordshire, County of UA | 4736 | 2610.000000 | 1333.000000 | 670.000000 | 123.000000 | 0.000000 | N | 1678 | 150 | 111.000000 | 863.000000 | 452.000000 | 102.000000 | 0 | Y |
19 | P2114 | 00MW | E06000046 | NaN | Isle of Wight UA | 8677 | 4674.000000 | 2509.000000 | 1132.000000 | 362.000000 | 0.000000 | N | 5511 | 528 | 216.000000 | 4953.000000 | 860.000000 | 0.000000 | 15 | y |
20 | Z0835 | 00HF | E06000053 | NaN | Isles of Scilly UA 6 | 79 | 63.000000 | 11.000000 | 5.000000 | 0.000000 | 0.000000 | n | 35 | 2 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0 | n |
21 | V2004 | 00FA | E06000010 | NaN | Kingston upon Hull, City of UA | 7451 | 3863.026794 | 2137.222166 | 926.162860 | 276.521438 | 248.066742 | Y | 4526 | 42 | 39.000000 | 0.000000 | 155.000000 | 0.000000 | 233 | Y |
22 | W2465 | 00FN | E06000016 | NaN | Leicester UA | 9442 | 4320.000000 | 3076.000000 | 1376.000000 | 670.000000 | 0.000000 | N | 4111 | 593 | 24.000000 | 2994.000000 | 347.000000 | 4.000000 | 0 | Y |
23 | B0230 | 00KA | E06000032 | NaN | Luton UA | 6616 | 2387.000000 | 2791.000000 | 1204.000000 | 234.000000 | 0.000000 | y | 4368 | 1106 | 950.000000 | 1821.000000 | 508.000000 | 2.000000 | 0 | y |
24 | A2280 | 00LC | E06000035 | NaN | Medway UA | 18773 | 9794.000000 | 5718.000000 | 2435.000000 | 826.000000 | 0.000000 | Y | 1516 | 147 | 142.000000 | 256.000000 | 737.000000 | 234.000000 | 154 | Y |
25 | W0734 | 00EC | E06000002 | NaN | Middlesbrough UA | 5412 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 5412.000000 | N | 981 | 146 | 46.000000 | 10.000000 | 521.000000 | 177.000000 | 1 | y |
26 | Y0435 | 00MG | E06000042 | NaN | Milton Keynes UA | 0 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | N | 0 | 0 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0 | N |
27 | B2002 | 00FC | E06000012 | NaN | North East Lincolnshire UA | 9902 | 5563.000000 | 2249.000000 | 1653.000000 | 437.000000 | 0.000000 | N | 387 | 99 | 46.000000 | 18.000000 | 193.000000 | 37.000000 | 0 | Y |
28 | Y2003 | 00FD | E06000013 | NaN | North Lincolnshire UA | 9223 | 5235.000000 | 2038.000000 | 1618.000000 | 332.000000 | 0.000000 | N | 326 | 95 | 24.000000 | 10.000000 | 135.000000 | 65.000000 | 0 | Y |
29 | D0121 | 00HC | E06000024 | NaN | North Somerset UA | 3937 | 2038.000000 | 1347.000000 | 470.000000 | 82.000000 | 0.000000 | Y | 1125 | 127 | 84.000000 | 596.000000 | 377.000000 | 0.000000 | 74 | Y |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
363 | R1845 | 47UG | E07000239 | NaN | Wyre Forest | 3277 | 1998.000000 | 847.000000 | 327.000000 | 104.000000 | 1.000000 | Y | 734 | 706 | 28.000000 | 89.000000 | 159.000000 | 64.000000 | 36 | Y |
364 | These cells contain imputed data - this data s... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
365 | These cells contain unfinalised data - local a... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
366 | Local authorities that no longer exist but are... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
367 | Notes | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
368 | 1. It should be noted that the number of peopl... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
369 | 2. Other public sector dwellings includes dwel... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
370 | Source | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
371 | Local Authority Housing Statistics 2013-14, th... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
372 | https://www.gov.uk/government/statistics/local... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
373 | The relevant live tables can be found here | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
374 | https://www.gov.uk/government/statistical-data... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
375 | Guidance and Questionaire Form | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
376 | Guidance on the above data and a blank version... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
377 | https://www.gov.uk/government/publications/com... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
378 | Regional Statistics | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
379 | For information on why regional statistics are... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
380 | https://www.gov.uk/government/speeches/statist... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
381 | New Geographies | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
382 | On 1st April 2009 nine new unitary authorities... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
383 | Figures are no longer collected on the previou... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
384 | Cheshire West UA from Chester, Ellesmere Port ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
385 | Cheshire East UA was formed from Congleton, Cr... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
386 | Bedford UA from Bedford; and Central Bedfordsh... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
387 | The remaining five new UAs were each formed fr... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
388 | Contact: | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
389 | Telephone: 0303 44 41861 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
390 | Email: housing.statistics@communities.gsi.gov.uk | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
391 | Latest update: 11 December 2014 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
392 | Next update: Early 2015 (small update after co... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
393 rows × 20 columns
kq={}
for k in skey:
kq[k]=[]
for j in skey[k]:
if j not in kq[k]: kq[k].append(j)
kq
{u'c5a': [u'Do you participate in a choice-based lettings scheme (i.e. a system allowing housing applicants to choose from a selection of available to let vacancies advertised openly or made known to registered applicants in housing need)? (Y or N)'], u'cc1a': [u'Total households on the housing waiting list at 1st April 2013'], u'cc1aa': [u'How many bedrooms did these households require?', u'Households requiring 1 bedroom'], u'cc1ab': [u'How many bedrooms did these households require?', u'Households requiring 2 bedrooms'], u'cc1ac': [u'How many bedrooms did these households require?', u'Households requiring 3 bedrooms'], u'cc1ad': [u'How many bedrooms did these households require?', u'Households requiring more than 3 bedrooms'], u'cc1ae': [u'How many bedrooms did these households require?', u'Households requiring an unspecified number of bedrooms or those on the register more than once'], u'cc2a': [u'Have you changed your waiting list criteria since last year in light of the changes in the Localism Act? (Y or N)'], u'cc3a': [u'Total households on the housing waiting list in a reasonable preference category at 1st April 2013'], u'cc3aa': [u'How many of these households were in each reasonable preference group?', u'People who are homeless within the meaning given in Part VII of the Act, regardless of whether there is a statutory duty to house them'], u'cc3ab': [u'How many of these households were in each reasonable preference group?', u'People who are owed a duty by any local housing authority under section 190(2), 193(2) or 195(2) of the Act\xa0(or under section 65(2) or 68(2) of the Housing Act 1985) or who are occupying accommodation secured by any such authority under section 192(3) of the Act'], u'cc3ac': [u'How many of these households were in each reasonable preference group?', u'People occupying insanitary or overcrowded housing or otherwise living in unsatisfactory housing conditions'], u'cc3ad': [u'How many of these households were in each reasonable preference group?', u'People who need to move on medical or welfare grounds, including grounds relating to a disability'], u'cc3ae': [u'How many of these households were in each reasonable preference group?', u'People who need to move to a particular locality in the district of the authority, where failure to meet that need would cause hardship (to themselves or to others).'], u'cc4a': [u'If your scheme gives additional preference to those in reasonable preference groups with urgent housing needs, how many households on your waiting lists does this apply to?']}
colmapper={}
for kkq in kq:
curr_level = colmapper
depth=0
for path in kq[kkq]:
depth=depth+1
if path not in curr_level:
if depth<len(kq[kkq]):
curr_level[path] = {}
curr_level = curr_level[path]
else: curr_level[path] = kkq
else:
curr_level = curr_level[path]
colmapper
{u'Do you participate in a choice-based lettings scheme (i.e. a system allowing housing applicants to choose from a selection of available to let vacancies advertised openly or made known to registered applicants in housing need)? (Y or N)': u'c5a', u'Have you changed your waiting list criteria since last year in light of the changes in the Localism Act? (Y or N)': u'cc2a', u'How many bedrooms did these households require?': {u'Households requiring 1 bedroom': u'cc1aa', u'Households requiring 2 bedrooms': u'cc1ab', u'Households requiring 3 bedrooms': u'cc1ac', u'Households requiring an unspecified number of bedrooms or those on the register more than once': u'cc1ae', u'Households requiring more than 3 bedrooms': u'cc1ad'}, u'How many of these households were in each reasonable preference group?': {u'People occupying insanitary or overcrowded housing or otherwise living in unsatisfactory housing conditions': u'cc3ac', u'People who are homeless within the meaning given in Part VII of the Act, regardless of whether there is a statutory duty to house them': u'cc3aa', u'People who are owed a duty by any local housing authority under section 190(2), 193(2) or 195(2) of the Act\xa0(or under section 65(2) or 68(2) of the Housing Act 1985) or who are occupying accommodation secured by any such authority under section 192(3) of the Act': u'cc3ab', u'People who need to move on medical or welfare grounds, including grounds relating to a disability': u'cc3ad', u'People who need to move to a particular locality in the district of the authority, where failure to meet that need would cause hardship (to themselves or to others).': u'cc3ae'}, u'If your scheme gives additional preference to those in reasonable preference groups with urgent housing needs, how many households on your waiting lists does this apply to?': u'cc4a', u'Total households on the housing waiting list at 1st April 2013': u'cc1a', u'Total households on the housing waiting list in a reasonable preference category at 1st April 2013': u'cc3a'}
iw='E06000046'
df[df['Current\nONS']==iw]['cc1a'].iloc[0]
8677.0
#http://stackoverflow.com/a/10756547/454773
def myprint(d,l=None):
if l is None: l=''
for k, v in d.iteritems():
if isinstance(v, dict):
print("{}{}".format(l,k))
myprint(v,l=l+'-')
else:
print "{0} {1} : {2}".format(l,k.encode('utf-8'), v)
#dfx=pd.ExcelFile('Local_Authority_Housing_Statistics_dataset_2013-14.xlsx')
import collections
def colmapbuilder(dfx,sheet,code,retval=True):
df,row=dfgrabber(dfx,sheet)
sname,skey,kd=coldecoder(dfx,sheet,row)
kq=collections.OrderedDict() #{}
for k in skey:
kq[k]=[]
for j in skey[k]:
if j not in kq[k]: kq[k].append(j)
colmapper=collections.OrderedDict() #{}
for kkq in kq:
curr_level = colmapper
depth=0
for path in kq[kkq]:
depth=depth+1
if path not in curr_level:
if depth<len(kq[kkq]):
curr_level[path] = collections.OrderedDict() #{}
curr_level = curr_level[path]
else:
if retval:
curr_level[path] = df[df['Current\nONS']==code][kkq].iloc[0]
else:
curr_level[path] = kkq
else:
curr_level = curr_level[path]
return sname, colmapper
#iw='E06000046'
myprint(colmapbuilder(dfx,'C','E06000046'))
How many bedrooms did these households require? - Households requiring 1 bedroom : 4674.0 - Households requiring 2 bedrooms : 2509.0 - Households requiring 3 bedrooms : 1132.0 - Households requiring an unspecified number of bedrooms or those on the register more than once : 0.0 - Households requiring more than 3 bedrooms : 362.0 If your scheme gives additional preference to those in reasonable preference groups with urgent housing needs, how many households on your waiting lists does this apply to? : 15.0 How many of these households were in each reasonable preference group? - People who are homeless within the meaning given in Part VII of the Act, regardless of whether there is a statutory duty to house them : 528.0 - People who are owed a duty by any local housing authority under section 190(2), 193(2) or 195(2) of the Act (or under section 65(2) or 68(2) of the Housing Act 1985) or who are occupying accommodation secured by any such authority under section 192(3) of the Act : 216.0 - People who need to move to a particular locality in the district of the authority, where failure to meet that need would cause hardship (to themselves or to others). : 0.0 - People occupying insanitary or overcrowded housing or otherwise living in unsatisfactory housing conditions : 4953.0 - People who need to move on medical or welfare grounds, including grounds relating to a disability : 860.0 Total households on the housing waiting list at 1st April 2013 : 8677.0 Total households on the housing waiting list in a reasonable preference category at 1st April 2013 : 5511.0 Have you changed your waiting list criteria since last year in light of the changes in the Localism Act? (Y or N) : N Do you participate in a choice-based lettings scheme (i.e. a system allowing housing applicants to choose from a selection of available to let vacancies advertised openly or made known to registered applicants in housing need)? (Y or N) : y
myprint(colmapbuilder(dfx,'A','E06000046'))
Total Number of Dwellings owned by your Local Authority as at 1 April 2013 -Total -- All, including PFI & SO : 0.0 -- Affordable Rent : 0.0 -- Total - Excluding PFI and Shared ownership : 0.0 -- Total value of social rent stock at January 1999 prices (in millions of pounds, to 3 decimal places) : 0.0 -- Total value of stock at January 1999 prices (in millions of pounds, to 3 decimal places) : 0.0 -- Social Rent : 0.0 -Three bedrooms -- Social Rent : 0.0 -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -Six or more bedrooms -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -- Social Rent : 0.0 -Five bedrooms -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -- Social Rent : 0.0 -Four bedrooms -- All, including PFI & SO : 0.0 -- Affordable Rent : 0.0 -- Social Rent : 0.0 -Two bedrooms -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -- Social Rent : 0.0 -One bedroom -- Social Rent : 0.0 -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -Bedsits -- Social Rent : 0.0 -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -Equivalent of HMOs Hostels -- Social Rent : 0.0 -- All, including PFI & SO : 0.0 -- Affordable Rent : 0.0 Changes to Local Authority owned stock over 2012-13 - Number of new builds : 0.0 - Number of demolitions : 0.0 - Number of acquisitions : 0.0 - Number of conversions (can be positive or negative) : 0.0 Total number of dwellings located in your local authority area (using the Census definition) - 'Other' public sector : 3.0 - Local Authority Owned (including those owned by other Local Authorities) : 0.0 Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority : 0.0
myprint(colmapbuilder(dfx,'D','E06000046'))
Dwellings let to new tenants to social housing 3 - of which: a. Tenant has moved from outside your LA area : 0.0 -of which: b. Dwellings let at Affordable Rent -- Total : 0.0 -- of which, dwellings previously let at Social Rent : 0.0 - Dwellings let to new tenants on secure tenancies (include introductory tenancies that will become secure tenancies after the introductory period) : 0.0 - Total : 0.0 - Dwellings let to new tenants on flexible (fixed term) tenancies (include introductory tenancies that will become flexible tenancies after the introductory period) : 0.0 Total LA dwellings let - of which: a. Lettings within General needs housing : 0.0 - Total : 0.0 - of which: b. Lettings within Supported housing : 0.0 Other local authority lettings from own stock - LA tenants of your LA moving to social housing dwellings in another LA area : 0.0 Dwellings let to existing social housing tenants transferring into LAs own stock from a social housing dwelling 2 - Dwellings let to existing tenants of your local authority : 0.0 - Dwellings let to existing tenants of Private Registered Providers : 0.0 - Dwellings let to existing tenants of another LA : 0.0 -Lettings to existing social tenants -- of which: b. on flexible (fixed term) tenancies : 0.0 -- Total : 0.0 -- of which: a. Tenant has moved from outside your LA area : 0.0 Nominations taken up - 11. Private Registered Provider dwellings let to households in response to a nomination from your LA : 390.0 - Other social landlord dwellings (not PRP) let to households in response to a nomination from your LA : 0.0 Dwellings let through mutual exchanges 4 - of which: a. Tenant has moved from within your LA Area : 0.0 - of which: b. Tenant has moved from outside your LA area : 0.0 - Total : 0.0
myprint(colmapbuilder(dfx,'E','E06000046'))
Dwellings vacant for between 6 weeks and 6 months 2 - Total : 0.0 - Vacant but available for letting : 0.0 - Vacant but not available for letting : 0.0 Dwellings vacant for 0 - 6 weeks 2 - Total : 0.0 - Vacant but not available for letting : 0.0 - Vacant but available for letting : 0.0 Dwellings vacant for over 6 months 2 - Total : 0.0 - Vacant but not available for letting : 0.0 - Vacant but available for letting : 0.0 Total dwellings vacant 2 - Total : 0.0 - Vacant but not available for letting : 0.0 - Vacant but available for letting : 0.0 Number of vacant dwellings in your local authority area at 1 April 2013 1 - Local Authority owned (including those owned by other Local Authorities) : 0.0 - 'Other' Public sector : 0.0
myprint(colmapbuilder(dfx,'F','E06000046'))
Decent Homes Delivery 1 -Dwellings without a reasonable degree of thermal comfort -- Number of dwellings : 0.0 -- The proportion of LA homes which were non-decent (%) : 0.0 -- Total cost £000s : 0.0 -- Average cost per dwelling (£) : 0.0 - Dwellings receiving capital works from backlog funding 2 during 2013-14 : 0.0 -Associated expenditure -- Money spent making dwellings decent during year £000s : 0.0 -- Costs arising from dwellings becoming non-decent during year £000s : 0.0 -- Money spent preventing dwellings becoming non-decent during year £000s : 0.0 -- Cost to make all dwellings decent at 1st April : 0.0 - Tenant refusals : 0.0 -Dwellings not in a reasonable state of repair -- Total cost £000s : 0.0 -- Number of dwellings : 0.0 -- Average cost per dwelling (£) : 0.0 -Of your HRA stock (both decent & non-decent) how many homes have not had work carried out due to tenant's refusal? -- Non-decent : 0.0 -- Decent : 0.0 - Demolitions : 0.0 - Decent Homes Backlog 2 remaining at 1 April 2014 : 0.0 -Dwellings with category 1 hazards (HHSRS) -- Total cost £000s : 0.0 -- Average cost per dwelling (£) : 0.0 -- Number of dwellings : 0.0 -Forecast number of Non-decent homes as at 1st April -- 2015 : 0.0 -- 2016.0 : 0.0 -- 2018.0 : 0.0 -- 2017.0 : 0.0 -- 2019.0 : 0.0 -Dwellings without reasonably modern amenities and services -- Number of dwellings : 0.0 -- Average cost per dwelling (£) : 0.0 -- Total cost £000s : 0.0 - Partial transfers : 0.0 -Backlog dwellings 2 made decent during reporting year: -- With all funding : 0.0 -- With backlog funding : 0.0 -Non-decent dwellings at 1 April 2014 -- Number of dwellings : 0.0 -- Total cost £000s : 0.0 -- Average cost per dwelling (£) : 0.0 Estimated cost (£ thousands) of removing all category 1 hazards from the private sector dwellings in f6aa? : 162.0 Number of apprenticeships secured with backlog funding 2 -Conversion -- £000s : 0.0 -- Dwellings : 0.0 -g. Replacement of windows -- Dwellings : 0.0 -- £000s : 0.0 -a. Rewiring -- £000s : 0.0 -- Dwellings : 0.0 -Number of HRA dwellings that received capital works and associated expenditure -- Dwellings : 0.0 -- £000s : 0.0 -d. Doors -- Dwellings : 0.0 -- £000s : 0.0 -f. Bathrooms -- Dwellings : 0.0 -- £000s : 0.0 -New build -- £000s : 0.0 -- Dwellings : 0.0 -h. Replacement of boilers -- Dwellings : 0.0 -- £000s : 0.0 -k.other -- Dwellings : 0.0 -- £000s : 0.0 -b. Roof structure -- £000s : 0.0 -- Dwellings : 0.0 -Demolition -- £000s : 0.0 -- Dwellings : 0.0 -c. Roof covering -- Dwellings : 0.0 -- £000s : 0.0 -i. Installation of insulation -- Dwellings : 0.0 -- £000s : 0.0 -Acquisitions -- Dwellings : 0.0 -- £000s : 0.0 - Total Capital Expenditure on Social Rented Housing (within the Housing Revenue Account) £000s : 0.0 -e. Kitchens -- Dwellings : 0.0 -- £000s : 0.0 -j.Installation of renewable energies -- Dwellings : 0.0 -- £000s : 0.0 -f. All capital works (Dwellings receiving more than one capital works should only be counted once) -- £000s : 0.0 -- Dwellings : 0.0 Number of dwellings owned by your Local Authority that received the following capital works over 2012-13 and those planned for 2013-14: -Replacement of windows -- Planned 2014-15 : 0.0 -- 2013-14 : 0.0 -Of which cavity walls -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Of which solid walls -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Installation of insulation -- Planned 2014-15 : 0.0 -- 2013-14 : 0.0 -Of which replaced with boilers that do not meet the current standard -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Of which lofts -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Of which floors -- Planned 2014-15 : 0.0 -- 2013-14 : 0.0 -Of which replaced with windows that do not meet the current standard -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Installation of renewable technologies -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Replacement of boilers -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Please specify type(s) using codes below (if more than one technology installed please sum all relevant codes for your answer) -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 Information on loans, grants and other assistance for 2013-14 -Private rented -- LA expenditure on loans & other assistance £000s : 0.0 -- Number of dwellings improved : 19.0 -- LA expenditure on grants £000s : 14.0 -Owner occupiers -- LA expenditure on loans & other assistance £000s : 0.0 -- Number of dwellings improved : 45.0 -- LA expenditure on grants £000s : 148.0 -Change in the number of non-decent dwellings during the reporting year -- Other reduction in non-decent dwellings : 0.0 -- Number of non-decent 1st of April : 0.0 -- Dwellings made decent during year : 0.0 -- Dwellings becoming non-decent during year : 0.0 -- Dwellings receiving work to prevent them becoming non-decent during year : 0.0 -Total -- Number of dwellings improved : 64.0 Re-Forecast annual profile of backlog stock to be made decent 2: -All funding sources -- 2015-16 : 0.0 -- 2014-15 : 0.0 -- Total : 0.0 -Through backlog funding -- Total : 0.0 -- 2015-16 : 0.0 -- 2014-15 : 0.0 Total number of dwellings in f5a, which are in your local authority area : 0.0 Total number of private sector dwellings in your Local Authority Area with Category 1 hazards which were made free from those hazards as a direct result of action by your Local Authority during 2013-14? : 400.0 Provide an estimate of the total number of HMOs within your authority. : 424.0 Provide an estimate of the total number of mandatory licensable HMOs within your Local Authority Area : 33.0 Estimated cost (£ thousands) of removing category 1 hazards from all dwellings in f5a? : 0.0 Of which, owned by the private sector? : 17367.0 Total number of ALL dwellings with category 1 hazards (HHSRS) in your Local Authority Area : 17367.0 State the actual number of properties with mandatory HMO licences (issued within your authority). : 30.0 Total number of dwellings with category 1 hazards (HHSRS) owned by your Local Authority : 0.0 How many properties identified as being mandatory licensable HMOs have been found upon inspection to have Category 1 hazards (according to the HHSRS)? : 3.0
myprint(colmapbuilder(dfx,'G','E06000046'))
General Management of Local Authority Stock - Average relet time (days) 1 : 0.0 Evictions obtained by local authority landlords - (of which) b. For anti-social behaviour : 0.0 - (of which) a. For rent arrears : 0.0 - (of which) d. For other reason : 0.0 - Number of evictions during 2012-13 (include all types of evictions) 2 : 0.0 - (of which) c. For both : 0.0
myprint(colmapbuilder(dfx,'H','E06000046'))
Average weekly rent per dwelling on an actual basis 1: - Social Rent : 0.0 - Affordable Rent : 0.0 Average weekly rent per dwelling on a standardised 52 week basis for 1: -c. Two bedrooms -- Affordable Rent : 0.0 -- Social Rent : 0.0 -b. One bedroom -- Affordable Rent : 0.0 -- Social Rent : 0.0 -f. Five bedrooms -- Affordable Rent : 0.0 -- Social Rent : 0.0 -a. Bedsits -- Social Rent : 0.0 -- Affordable Rent : 0.0 -g. Six or more bedrooms -- Social Rent : 0.0 -- Affordable Rent : 0.0 -h. All dwellings -- Social Rent : 0.0 -- Affordable Rent : 0.0 -e. Four bedrooms -- Social Rent : 0.0 -- Affordable Rent : 0.0 -d. Three bedrooms -- Social Rent : 0.0 -- Affordable Rent : 0.0 Rent collection rate expressed as a percentage (1 Decimal Places) 4 : 0.0 Rent reductions and refunds (enter as positive) (2 Decimal Places, in £s) : 0.0 Rent income to HRA (ie total rent collectable) (0 Decimal Places, in £s) 2 : 0.0 Current tenants' cumulative arrears of rent at the end of the last full collection period excluding arrears of council tax, water rates and heating/service charges (2 Decimal Places, in £s) : 0.0 Number of chargeable rent weeks: - Affordable Rent : 0.0 - Social Rent : 0.0 Rent arrears written off the HRA (enter as positive) (0 Decimal Places, in £s) : 0.0 Average weekly rent per dwelling on a standardised 52 week basis 1: - Affordable Rent : 0.0 - Social Rent : 0.0 Estimated arrears of rent outstanding at the end of the last full collection period (enter as positive) : 0.0 Former tenants' cumulative arrears of rent at the end of the last full collection period excluding arrears of council tax, water rates and heating/service charges (2 Decimal Places, in £s) : 0.0 Total value of rent roll (including rent rebates) (2 Decimal Places, in £s) : 0.0 Total cumulative arrears as a percentage of rent roll (1 Decimal Places) 3 : 0.0 Rent loss on void dwellings (enter as positive) (2 Decimal Places, in £s) : 0.0
myprint(colmapbuilder(dfx,'I','E06000046'))
Affordable housing funded with recycled Right to Buy receipts - Number of affordable homes completed with local authority recycled RTB receipts 1 -1 bedroom -- Acquisitions : 0.0 -- New Build : 0.0 -Total -- New Build : 0.0 -- Acquisitions : 0.0 -3+ bedroom -- Acquisitions : 0.0 -- New Build : 0.0 -2 bedroom -- Acquisitions : 0.0 -- New Build : 0.0 New build affordable housing - Owned by Local Authority, not reported to HCA 1 -d.Affordable Home Ownership -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 -e.Total Number of units -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 -c.Intermediate Rent -- Units completed without developer contributions : 0.0 -- Units completed with developer contributions through planning obligations : 0.0 -b.Affordable Rent -- Units completed without developer contributions : 0.0 -- Units completed with developer contributions through planning obligations : 0.0 -a.Social Rent -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 New build affordable housing - Owned by Private Registered Providers (including HAs) not reported to HCA 1 -d.Total Number of units -- Units completed with developer contributions through planning obligations : 2.0 -- Units completed without developer contributions : 0.0 -b.Intermediate Rent -- Units completed without developer contributions : 0.0 -- Units completed with developer contributions through planning obligations : 0.0 -c.Affordable Home Ownership -- Units completed with developer contributions through planning obligations : 2.0 -- Units completed without developer contributions : 0.0 -a.Social Rent -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 New build affordable housing - Owned by non-registered providers 1 -d.Total Number of units -- Units completed without developer contributions : 0.0 -- Units completed with developer contributions through planning obligations : 12.0 -b.Intermediate Rent -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 -c.Affordable Home Ownership -- Units completed with developer contributions through planning obligations : 12.0 -- Units completed without developer contributions : 0.0 -a.Social Rent -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 Provision of additional affordable housing other than new build (acquisitions) -Owned by Private Registered Providers (not reported to HCA) 1 -- Affordable Ownership : 0.0 -- Total : 0.0 -- Social Rent : 0.0 -- Intermediate Rent : 0.0 -Owned by Non-registered provider 1 -- Social Rent : 0.0 -- Affordable Ownership : 0.0 -- Total : 0.0 -- Intermediate Rent : 0.0 -Owned by Local Authority (not reported to HCA) 1 -- Total : 0.0 -- Affordable Ownership : 0.0 -- Social Rent : 0.0 -- Affordable Rent : 0.0 -Other Developer Contributions to Affordable Housing 2 -- Amount of discounted or free land received during last year (hectares) : 0.0 -- Financial contributions from planning obligations (s106) spent during financial year (£000s) : 0.0 -- Financial contributions from planning obligations (s106) held at the start of the year (£000s) : 170.0 -- Financial contributions from planning obligations (s106) received during financial year (£000s) : 10.0 -Affordable units granted final planning permission during the year -- For unknown affordable tenure : 0.0 -- For Intermediate Rent : 0.0 -- Total number of units : 6.0 -- For Social Rent : 0.0 -- For Affordable Home Ownership : 0.0 -- For Affordable Rent : 6.0 - In populations of less than 3,000 people (all non-new build affordable housing) : 0.0 -Cash Incentive Scheme Grants 3 -- Total number of grants : 0.0 -- Total expenditure (£000s) : 0.0
ll=dfx.sheet_names
ll.remove('Menu')
ll
[u'A', u'B', u'C', u'D', u'E', u'F', u'G', u'H', u'I']
for lll in ll:
sname,cmb=colmapbuilder(dfx,lll,'E06000046')
print(sname+'\n')
myprint(cmb)
print('\n\n')
Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section A Total Number of Dwellings owned by your Local Authority as at 1 April 2013 -Total -- All, including PFI & SO : 0.0 -- Affordable Rent : 0.0 -- Total - Excluding PFI and Shared ownership : 0.0 -- Total value of social rent stock at January 1999 prices (in millions of pounds, to 3 decimal places) : 0.0 -- Total value of stock at January 1999 prices (in millions of pounds, to 3 decimal places) : 0.0 -- Social Rent : 0.0 -Three bedrooms -- Social Rent : 0.0 -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -Six or more bedrooms -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -- Social Rent : 0.0 -Five bedrooms -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -- Social Rent : 0.0 -Four bedrooms -- All, including PFI & SO : 0.0 -- Affordable Rent : 0.0 -- Social Rent : 0.0 -Two bedrooms -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -- Social Rent : 0.0 -One bedroom -- Social Rent : 0.0 -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -Bedsits -- Social Rent : 0.0 -- Affordable Rent : 0.0 -- All, including PFI & SO : 0.0 -Equivalent of HMOs Hostels -- Social Rent : 0.0 -- All, including PFI & SO : 0.0 -- Affordable Rent : 0.0 Changes to Local Authority owned stock over 2012-13 - Number of new builds : 0.0 - Number of demolitions : 0.0 - Number of acquisitions : 0.0 - Number of conversions (can be positive or negative) : 0.0 Total number of dwellings located in your local authority area (using the Census definition) - 'Other' public sector : 3.0 - Local Authority Owned (including those owned by other Local Authorities) : 0.0 Total number of private sector dwellings demolished by your authority, or by a third party paid for by your authority : 0.0 Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section B 2. Sales/transfers completed -a. Right to Buy --of which : 3+ bedrooms --- Selling price (net of discounts) (£000s) : 0.0 --- No. dwellings : 0.0 --- Discounts allowed (£000s) : 0.0 --Total --- Discounts allowed (£000s) : 0.0 --- Selling price (net of discounts) (£000s) : 0.0 --- No. dwellings : 0.0 --of which : 2 bedrooms --- No. dwellings : 0.0 --- Discounts allowed (£000s) : 0.0 --- Selling price (net of discounts) (£000s) : 0.0 --On flexible tenancies --- Discounts allowed (£000s) : 0.0 --- Selling price (net of discounts) (£000s) : 0.0 --- No. dwellings : 0.0 --of which : 1 bedroom --- No. dwellings : 0.0 --- Discounts allowed (£000s) : 0.0 --- Selling price (net of discounts) (£000s) : 0.0 -b. Social Homebuy -- Discounts allowed (£000s) : 0.0 -- No. dwellings : 0.0 -- Selling price (net of discounts) (£000s) : 0.0 -e. Transfers to PRPs -- Selling price (net of discounts) (£000s) : 0.0 -- No. dwellings : 0.0 -of which: a. all flats -- No. dwellings : 0.0 -- Selling price (net of discounts) (£000s) : 0.0 -- Discounts allowed (£000s) : 0.0 -of which: b. RTB flats -- Discounts allowed (£000s) : 0.0 -- Selling price (net of discounts) (£000s) : 0.0 -- No. dwellings : 0.0 -of which: b. SHB flats -- Selling price (net of discounts) (£000s) : 0.0 -- No. dwellings : 0.0 -- Discounts allowed (£000s) : 0.0 -f. Total dwelling sales/transfers -- Selling price (net of discounts) (£000s) : 0.0 -- Discounts allowed (£000s) : 0.0 -- No. dwellings : 0.0 -d. Other sales -- No. dwellings : 0.0 -- Discounts allowed (£000s) : 0.0 -- Selling price (net of discounts) (£000s) : 0.0 -c. Other sales to sitting tenants -- No. dwellings : 0.0 -- Selling price (net of discounts) (£000s) : 0.0 -- Discounts allowed (£000s) : 0.0 3. Buy-back of ex-council dwellings -a. Total -- Selling price (net of discounts) (£000s) : 0.0 -- No. dwellings : 0.0 -- Discounts allowed (£000s) : 0.0 -of which: Flats within total -- No. dwellings : 0.0 -- Discounts allowed (£000s) : 0.0 -- Selling price (net of discounts) (£000s) : 0.0 1. Number of Right to Buy applications received during the period : 0.0 Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section C How many bedrooms did these households require? - Households requiring 1 bedroom : 4674.0 - Households requiring 2 bedrooms : 2509.0 - Households requiring 3 bedrooms : 1132.0 - Households requiring an unspecified number of bedrooms or those on the register more than once : 0.0 - Households requiring more than 3 bedrooms : 362.0 If your scheme gives additional preference to those in reasonable preference groups with urgent housing needs, how many households on your waiting lists does this apply to? : 15.0 How many of these households were in each reasonable preference group? - People who are homeless within the meaning given in Part VII of the Act, regardless of whether there is a statutory duty to house them : 528.0 - People who are owed a duty by any local housing authority under section 190(2), 193(2) or 195(2) of the Act (or under section 65(2) or 68(2) of the Housing Act 1985) or who are occupying accommodation secured by any such authority under section 192(3) of the Act : 216.0 - People who need to move to a particular locality in the district of the authority, where failure to meet that need would cause hardship (to themselves or to others). : 0.0 - People occupying insanitary or overcrowded housing or otherwise living in unsatisfactory housing conditions : 4953.0 - People who need to move on medical or welfare grounds, including grounds relating to a disability : 860.0 Total households on the housing waiting list at 1st April 2013 : 8677.0 Total households on the housing waiting list in a reasonable preference category at 1st April 2013 : 5511.0 Have you changed your waiting list criteria since last year in light of the changes in the Localism Act? (Y or N) : N Do you participate in a choice-based lettings scheme (i.e. a system allowing housing applicants to choose from a selection of available to let vacancies advertised openly or made known to registered applicants in housing need)? (Y or N) : y Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section D Dwellings let to new tenants to social housing 3 - of which: a. Tenant has moved from outside your LA area : 0.0 -of which: b. Dwellings let at Affordable Rent -- Total : 0.0 -- of which, dwellings previously let at Social Rent : 0.0 - Dwellings let to new tenants on secure tenancies (include introductory tenancies that will become secure tenancies after the introductory period) : 0.0 - Total : 0.0 - Dwellings let to new tenants on flexible (fixed term) tenancies (include introductory tenancies that will become flexible tenancies after the introductory period) : 0.0 Total LA dwellings let - of which: a. Lettings within General needs housing : 0.0 - Total : 0.0 - of which: b. Lettings within Supported housing : 0.0 Other local authority lettings from own stock - LA tenants of your LA moving to social housing dwellings in another LA area : 0.0 Dwellings let to existing social housing tenants transferring into LAs own stock from a social housing dwelling 2 - Dwellings let to existing tenants of your local authority : 0.0 - Dwellings let to existing tenants of Private Registered Providers : 0.0 - Dwellings let to existing tenants of another LA : 0.0 -Lettings to existing social tenants -- of which: b. on flexible (fixed term) tenancies : 0.0 -- Total : 0.0 -- of which: a. Tenant has moved from outside your LA area : 0.0 Nominations taken up - 11. Private Registered Provider dwellings let to households in response to a nomination from your LA : 390.0 - Other social landlord dwellings (not PRP) let to households in response to a nomination from your LA : 0.0 Dwellings let through mutual exchanges 4 - of which: a. Tenant has moved from within your LA Area : 0.0 - of which: b. Tenant has moved from outside your LA area : 0.0 - Total : 0.0 Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section E Dwellings vacant for between 6 weeks and 6 months 2 - Total : 0.0 - Vacant but available for letting : 0.0 - Vacant but not available for letting : 0.0 Dwellings vacant for 0 - 6 weeks 2 - Total : 0.0 - Vacant but not available for letting : 0.0 - Vacant but available for letting : 0.0 Dwellings vacant for over 6 months 2 - Total : 0.0 - Vacant but not available for letting : 0.0 - Vacant but available for letting : 0.0 Total dwellings vacant 2 - Total : 0.0 - Vacant but not available for letting : 0.0 - Vacant but available for letting : 0.0 Number of vacant dwellings in your local authority area at 1 April 2013 1 - Local Authority owned (including those owned by other Local Authorities) : 0.0 - 'Other' Public sector : 0.0 Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section F Decent Homes Delivery 1 -Dwellings without a reasonable degree of thermal comfort -- Number of dwellings : 0.0 -- The proportion of LA homes which were non-decent (%) : 0.0 -- Total cost £000s : 0.0 -- Average cost per dwelling (£) : 0.0 - Dwellings receiving capital works from backlog funding 2 during 2013-14 : 0.0 -Associated expenditure -- Money spent making dwellings decent during year £000s : 0.0 -- Costs arising from dwellings becoming non-decent during year £000s : 0.0 -- Money spent preventing dwellings becoming non-decent during year £000s : 0.0 -- Cost to make all dwellings decent at 1st April : 0.0 - Tenant refusals : 0.0 -Dwellings not in a reasonable state of repair -- Total cost £000s : 0.0 -- Number of dwellings : 0.0 -- Average cost per dwelling (£) : 0.0 -Of your HRA stock (both decent & non-decent) how many homes have not had work carried out due to tenant's refusal? -- Non-decent : 0.0 -- Decent : 0.0 - Demolitions : 0.0 - Decent Homes Backlog 2 remaining at 1 April 2014 : 0.0 -Dwellings with category 1 hazards (HHSRS) -- Total cost £000s : 0.0 -- Average cost per dwelling (£) : 0.0 -- Number of dwellings : 0.0 -Forecast number of Non-decent homes as at 1st April -- 2015 : 0.0 -- 2016.0 : 0.0 -- 2018.0 : 0.0 -- 2017.0 : 0.0 -- 2019.0 : 0.0 -Dwellings without reasonably modern amenities and services -- Number of dwellings : 0.0 -- Average cost per dwelling (£) : 0.0 -- Total cost £000s : 0.0 - Partial transfers : 0.0 -Backlog dwellings 2 made decent during reporting year: -- With all funding : 0.0 -- With backlog funding : 0.0 -Non-decent dwellings at 1 April 2014 -- Number of dwellings : 0.0 -- Total cost £000s : 0.0 -- Average cost per dwelling (£) : 0.0 Estimated cost (£ thousands) of removing all category 1 hazards from the private sector dwellings in f6aa? : 162.0 Number of apprenticeships secured with backlog funding 2 -Conversion -- £000s : 0.0 -- Dwellings : 0.0 -g. Replacement of windows -- Dwellings : 0.0 -- £000s : 0.0 -a. Rewiring -- £000s : 0.0 -- Dwellings : 0.0 -Number of HRA dwellings that received capital works and associated expenditure -- Dwellings : 0.0 -- £000s : 0.0 -d. Doors -- Dwellings : 0.0 -- £000s : 0.0 -f. Bathrooms -- Dwellings : 0.0 -- £000s : 0.0 -New build -- £000s : 0.0 -- Dwellings : 0.0 -h. Replacement of boilers -- Dwellings : 0.0 -- £000s : 0.0 -k.other -- Dwellings : 0.0 -- £000s : 0.0 -b. Roof structure -- £000s : 0.0 -- Dwellings : 0.0 -Demolition -- £000s : 0.0 -- Dwellings : 0.0 -c. Roof covering -- Dwellings : 0.0 -- £000s : 0.0 -i. Installation of insulation -- Dwellings : 0.0 -- £000s : 0.0 -Acquisitions -- Dwellings : 0.0 -- £000s : 0.0 - Total Capital Expenditure on Social Rented Housing (within the Housing Revenue Account) £000s : 0.0 -e. Kitchens -- Dwellings : 0.0 -- £000s : 0.0 -j.Installation of renewable energies -- Dwellings : 0.0 -- £000s : 0.0 -f. All capital works (Dwellings receiving more than one capital works should only be counted once) -- £000s : 0.0 -- Dwellings : 0.0 Number of dwellings owned by your Local Authority that received the following capital works over 2012-13 and those planned for 2013-14: -Replacement of windows -- Planned 2014-15 : 0.0 -- 2013-14 : 0.0 -Of which cavity walls -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Of which solid walls -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Installation of insulation -- Planned 2014-15 : 0.0 -- 2013-14 : 0.0 -Of which replaced with boilers that do not meet the current standard -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Of which lofts -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Of which floors -- Planned 2014-15 : 0.0 -- 2013-14 : 0.0 -Of which replaced with windows that do not meet the current standard -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Installation of renewable technologies -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Replacement of boilers -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 -Please specify type(s) using codes below (if more than one technology installed please sum all relevant codes for your answer) -- 2013-14 : 0.0 -- Planned 2014-15 : 0.0 Information on loans, grants and other assistance for 2013-14 -Private rented -- LA expenditure on loans & other assistance £000s : 0.0 -- Number of dwellings improved : 19.0 -- LA expenditure on grants £000s : 14.0 -Owner occupiers -- LA expenditure on loans & other assistance £000s : 0.0 -- Number of dwellings improved : 45.0 -- LA expenditure on grants £000s : 148.0 -Change in the number of non-decent dwellings during the reporting year -- Other reduction in non-decent dwellings : 0.0 -- Number of non-decent 1st of April : 0.0 -- Dwellings made decent during year : 0.0 -- Dwellings becoming non-decent during year : 0.0 -- Dwellings receiving work to prevent them becoming non-decent during year : 0.0 -Total -- Number of dwellings improved : 64.0 Re-Forecast annual profile of backlog stock to be made decent 2: -All funding sources -- 2015-16 : 0.0 -- 2014-15 : 0.0 -- Total : 0.0 -Through backlog funding -- Total : 0.0 -- 2015-16 : 0.0 -- 2014-15 : 0.0 Total number of dwellings in f5a, which are in your local authority area : 0.0 Total number of private sector dwellings in your Local Authority Area with Category 1 hazards which were made free from those hazards as a direct result of action by your Local Authority during 2013-14? : 400.0 Provide an estimate of the total number of HMOs within your authority. : 424.0 Provide an estimate of the total number of mandatory licensable HMOs within your Local Authority Area : 33.0 Estimated cost (£ thousands) of removing category 1 hazards from all dwellings in f5a? : 0.0 Of which, owned by the private sector? : 17367.0 Total number of ALL dwellings with category 1 hazards (HHSRS) in your Local Authority Area : 17367.0 State the actual number of properties with mandatory HMO licences (issued within your authority). : 30.0 Total number of dwellings with category 1 hazards (HHSRS) owned by your Local Authority : 0.0 How many properties identified as being mandatory licensable HMOs have been found upon inspection to have Category 1 hazards (according to the HHSRS)? : 3.0 Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section G General Management of Local Authority Stock - Average relet time (days) 1 : 0.0 Evictions obtained by local authority landlords - (of which) b. For anti-social behaviour : 0.0 - (of which) a. For rent arrears : 0.0 - (of which) d. For other reason : 0.0 - Number of evictions during 2012-13 (include all types of evictions) 2 : 0.0 - (of which) c. For both : 0.0 Local Authority Housing Statistics dataset, England 2013-14: Section H Average weekly rent per dwelling on an actual basis 1: - Social Rent : 0.0 - Affordable Rent : 0.0 Average weekly rent per dwelling on a standardised 52 week basis for 1: -c. Two bedrooms -- Affordable Rent : 0.0 -- Social Rent : 0.0 -b. One bedroom -- Affordable Rent : 0.0 -- Social Rent : 0.0 -f. Five bedrooms -- Affordable Rent : 0.0 -- Social Rent : 0.0 -a. Bedsits -- Social Rent : 0.0 -- Affordable Rent : 0.0 -g. Six or more bedrooms -- Social Rent : 0.0 -- Affordable Rent : 0.0 -h. All dwellings -- Social Rent : 0.0 -- Affordable Rent : 0.0 -e. Four bedrooms -- Social Rent : 0.0 -- Affordable Rent : 0.0 -d. Three bedrooms -- Social Rent : 0.0 -- Affordable Rent : 0.0 Rent collection rate expressed as a percentage (1 Decimal Places) 4 : 0.0 Rent reductions and refunds (enter as positive) (2 Decimal Places, in £s) : 0.0 Rent income to HRA (ie total rent collectable) (0 Decimal Places, in £s) 2 : 0.0 Current tenants' cumulative arrears of rent at the end of the last full collection period excluding arrears of council tax, water rates and heating/service charges (2 Decimal Places, in £s) : 0.0 Number of chargeable rent weeks: - Affordable Rent : 0.0 - Social Rent : 0.0 Rent arrears written off the HRA (enter as positive) (0 Decimal Places, in £s) : 0.0 Average weekly rent per dwelling on a standardised 52 week basis 1: - Affordable Rent : 0.0 - Social Rent : 0.0 Estimated arrears of rent outstanding at the end of the last full collection period (enter as positive) : 0.0 Former tenants' cumulative arrears of rent at the end of the last full collection period excluding arrears of council tax, water rates and heating/service charges (2 Decimal Places, in £s) : 0.0 Total value of rent roll (including rent rebates) (2 Decimal Places, in £s) : 0.0 Total cumulative arrears as a percentage of rent roll (1 Decimal Places) 3 : 0.0 Rent loss on void dwellings (enter as positive) (2 Decimal Places, in £s) : 0.0 Local Authority Housing Statistics dataset (including imputed data), England 2013-14: Section I Affordable housing funded with recycled Right to Buy receipts - Number of affordable homes completed with local authority recycled RTB receipts 1 -1 bedroom -- Acquisitions : 0.0 -- New Build : 0.0 -Total -- New Build : 0.0 -- Acquisitions : 0.0 -3+ bedroom -- Acquisitions : 0.0 -- New Build : 0.0 -2 bedroom -- Acquisitions : 0.0 -- New Build : 0.0 New build affordable housing - Owned by Local Authority, not reported to HCA 1 -d.Affordable Home Ownership -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 -e.Total Number of units -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 -c.Intermediate Rent -- Units completed without developer contributions : 0.0 -- Units completed with developer contributions through planning obligations : 0.0 -b.Affordable Rent -- Units completed without developer contributions : 0.0 -- Units completed with developer contributions through planning obligations : 0.0 -a.Social Rent -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 New build affordable housing - Owned by Private Registered Providers (including HAs) not reported to HCA 1 -d.Total Number of units -- Units completed with developer contributions through planning obligations : 2.0 -- Units completed without developer contributions : 0.0 -b.Intermediate Rent -- Units completed without developer contributions : 0.0 -- Units completed with developer contributions through planning obligations : 0.0 -c.Affordable Home Ownership -- Units completed with developer contributions through planning obligations : 2.0 -- Units completed without developer contributions : 0.0 -a.Social Rent -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 New build affordable housing - Owned by non-registered providers 1 -d.Total Number of units -- Units completed without developer contributions : 0.0 -- Units completed with developer contributions through planning obligations : 12.0 -b.Intermediate Rent -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 -c.Affordable Home Ownership -- Units completed with developer contributions through planning obligations : 12.0 -- Units completed without developer contributions : 0.0 -a.Social Rent -- Units completed with developer contributions through planning obligations : 0.0 -- Units completed without developer contributions : 0.0 Provision of additional affordable housing other than new build (acquisitions) -Owned by Private Registered Providers (not reported to HCA) 1 -- Affordable Ownership : 0.0 -- Total : 0.0 -- Social Rent : 0.0 -- Intermediate Rent : 0.0 -Owned by Non-registered provider 1 -- Social Rent : 0.0 -- Affordable Ownership : 0.0 -- Total : 0.0 -- Intermediate Rent : 0.0 -Owned by Local Authority (not reported to HCA) 1 -- Total : 0.0 -- Affordable Ownership : 0.0 -- Social Rent : 0.0 -- Affordable Rent : 0.0 -Other Developer Contributions to Affordable Housing 2 -- Amount of discounted or free land received during last year (hectares) : 0.0 -- Financial contributions from planning obligations (s106) spent during financial year (£000s) : 0.0 -- Financial contributions from planning obligations (s106) held at the start of the year (£000s) : 170.0 -- Financial contributions from planning obligations (s106) received during financial year (£000s) : 10.0 -Affordable units granted final planning permission during the year -- For unknown affordable tenure : 0.0 -- For Intermediate Rent : 0.0 -- Total number of units : 6.0 -- For Social Rent : 0.0 -- For Affordable Home Ownership : 0.0 -- For Affordable Rent : 6.0 - In populations of less than 3,000 people (all non-new build affordable housing) : 0.0 -Cash Incentive Scheme Grants 3 -- Total number of grants : 0.0 -- Total expenditure (£000s) : 0.0