03-Modelling Data Download and Prep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Modelling Data Downloading & Preparation\n",
    "\n",
    "This notebook focusses on the remaining dimensions used by the Random Forest to learn about, and make predictions of, the scores. Where data can be downloaded automatically this notebook will do so. Where it cannot (hello Nomis and your incomprehensible API and broken API tool!) then I have made it obvious from which Census table the data were taken and how the downloaded data were processed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import matplotlib as mpl\n",
    "mpl.use('TkAgg')\n",
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# For reproducibility\n",
    "import random\n",
    "import numpy as np\n",
    "r_state = 42\n",
    "random.seed(r_state) \n",
    "np.random.seed(r_state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import requests\n",
    "import glob\n",
    "import re\n",
    "import os\n",
    "import io\n",
    "import zipfile\n",
    "from io import BytesIO\n",
    "\n",
    "from geoconvert import geoconvert\n",
    "\n",
    "lkp = os.path.join('data','lkp')\n",
    "src = os.path.join('data','src')\n",
    "\n",
    "canonical  = os.path.join('data','canonical')\n",
    "converted  = os.path.join(canonical,'converted')\n",
    "greenspace = os.path.join(canonical,'greenspace')\n",
    "dwelling   = os.path.join(canonical,'dwellings')\n",
    "travel     = os.path.join(canonical,'travel')\n",
    "household  = os.path.join(canonical,'households')\n",
    "housing    = os.path.join(canonical,'housing')\n",
    "work       = os.path.join(canonical,'work')\n",
    "\n",
    "for d in [canonical,converted,greenspace,dwelling,travel,household,housing,work]:\n",
    "    if not os.path.exists(d):\n",
    "        os.makedirs(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Make sure you always run this!\n",
    "boroughs = ['City of London','Barking and Dagenham','Barnet','Bexley','Brent','Bromley',\n",
    "            'Camden','Croydon','Ealing','Enfield','Greenwich','Hackney','Hammersmith and Fulham',\n",
    "            'Haringey','Harrow','Havering','Hillingdon','Hounslow','Islington',\n",
    "            'Kensington and Chelsea','Kingston upon Thames','Lambeth','Lewisham',\n",
    "            'Merton','Newham','Redbridge','Richmond upon Thames','Southwark','Sutton',\n",
    "            'Tower Hamlets','Waltham Forest','Wandsworth','Westminster']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ldn2011 = pd.read_pickle(os.path.join(lkp,'LSOAs 2011.pkl'))\n",
    "ldn2004 = pd.read_pickle(os.path.join(lkp,'LSOAs 2004.pkl'))\n",
    "\n",
    "print(\"Have built London LSOA filter data for use where needed...\")\n",
    "print(\"\\t2001: \" + str(ldn2004.shape[0]) + \" rows.\")\n",
    "print(\"\\t2011: \" + str(ldn2011.shape[0]) + \" rows.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def convert_to_2011(df,src,dest,nm):\n",
    "    gc = geoconvert.geoconvert()\n",
    "    gc.auto_2001_to_2011(os.path.join(src,nm))\n",
    "\n",
    "    for f in glob.glob(re.sub(\"-\\d+\\.csv\",\"*\",nm)):\n",
    "        fn = re.sub(\"-converted\",\"\",f)\n",
    "        print(\"Moving \" + f + \" to \" + converted)\n",
    "        os.rename(f, os.path.join(converted,fn))\n",
    "    \n",
    "    dfc = pd.read_csv(os.path.join(converted,nm), index_col=False)\n",
    "    \n",
    "    dfc.columns = df.columns\n",
    "    \n",
    "    dfc.to_csv(os.path.join(dest,nm), index=False)\n",
    "    print(\"\\tConverted file has \" + str(dfc.shape[0]) + \" rows.\")\n",
    "    print(dfc.sample(2, random_state=r_state))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ward to LSOA Conversion (Greenspace)\n",
    "\n",
    "For some strange reason access to greenspace has only been tracked at the [Ward level](https://data.london.gov.uk/dataset/access-public-open-space-and-nature-ward) using a mix of 2013 and 2014 ward codes spread across _two_ sheets in an XLSX so we need to combine them and convert to some kind of LSOA score using the [ONS lookup](http://geoportal.statistics.gov.uk/datasets/lower-layer-super-output-area-2011-to-ward-2015-lookup-in-england-and-wales). I have tried using the 2011 LSOA-to-2015 Ward lookup table but end up missing out on data for Hackney, Tower Hamlets, and Kensington & Chelsea so the `_JR` file from the ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lsoa2ward  = pd.read_csv(os.path.join(lkp,'LSOA_WARD_JR.csv'))\n",
    "\n",
    "print(\"There are \" + str(lsoa2ward.gss_cd.unique().shape[0]) + \" wards.\")\n",
    "print(\"There are \" + str(lsoa2ward.shape[0]) + \" 2011 LSOAs.\")\n",
    "lsoa2ward.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "url = ('https://files.datapress.com/london/dataset/'\n",
    "       'access-public-open-space-and-nature-ward/'\n",
    "       'access-public-open-space-ward.xls')\n",
    "\n",
    "# Retrieve it\n",
    "greensp1 = pd.read_excel(url, sheet_name='Open space 2013 wards')\n",
    "\n",
    "# Rename columns\n",
    "greensp1.rename(columns={\n",
    "    'Ward_GSS_CODE':'wardgsscd',\n",
    "    'BOROUGH_Name':'Borough',\n",
    "    '% Open Space with access':'% open space with access'\n",
    "}, inplace=True)\n",
    "\n",
    "greensp1 = greensp1[['wardgsscd','% open space','% open space with access','% of open space that has access']]\n",
    "\n",
    "### Now the renamed wards...\n",
    "greensp2 = pd.read_excel(url, sheet_name='Open space 2014 wards')\n",
    "\n",
    "# Rename columns\n",
    "greensp2.rename(columns={\n",
    "    'Ward_GSS_CODE':'wardgsscd',\n",
    "    'BOROUGH_Name':'Borough',\n",
    "    'Percentage_OpenSpace_Area_ALL_GIGL_open_space':'% open space',\n",
    "    'Percent_OpenSpace_Area_GIGLdesignatedsitesWithAccess':'% open space with access'\n",
    "}, inplace=True)\n",
    "\n",
    "greensp2 = greensp2[['wardgsscd','% open space','% open space with access','% of open space that has access']]\n",
    "\n",
    "greensp = pd.concat([greensp1, greensp2], ignore_index=True)\n",
    "\n",
    "# Sanity check\n",
    "print(\"Combined we have \" + str(greensp.shape[0]) + \" rows.\")\n",
    "greensp.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Inner join should yield about 4.8k matches as LSOAs smaller than wards\n",
    "lsoagrn = pd.merge(greensp, lsoa2ward, how='inner', left_on='wardgsscd', right_on='gss_cd')\n",
    "\n",
    "# Drop the bits we're not interested in\n",
    "lsoagrn = lsoagrn[['lsoacd','% open space','% open space with access','% of open space that has access']]\n",
    "\n",
    "# Save it\n",
    "lsoagrn.to_csv(os.path.join(greenspace,'Share.csv'), index=False)\n",
    "\n",
    "print(\"Matching rows: \" + str(lsoagrn.shape[0]))\n",
    "lsoagrn.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ward to LSOA Conversion (Greenspace Access)\n",
    "\n",
    "For some strange reason access to greenspace has only been tracked at the [Ward level](https://data.london.gov.uk/dataset/access-public-open-space-and-nature-ward) using 2013 and 2014 ward codes so we need to convert it to some kind of LSOA score using the [ONS lookup](http://geoportal.statistics.gov.uk/datasets/lower-layer-super-output-area-2011-to-ward-2015-lookup-in-england-and-wales). I have tried using the [2011 LSOA-to-2015 Ward lookup table](https://opendata.arcgis.com/datasets/07a6d14d4a0540769f0662f4d1450bae_0.csv) from the ONS but end up missing out on data for Hackney, Tower Hamlets, and Kensington & Chelsea because it doesn't have the 2014 boundary changes or something."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lsoa2ward  = pd.read_csv(os.path.join(lkp,'LSOA_WARD_JR.csv'))\n",
    "\n",
    "url = ('https://files.datapress.com/london/dataset/'\n",
    "       'access-public-open-space-and-nature-ward/'\n",
    "       'access-public-open-space-ward.xls')\n",
    "\n",
    "# Retrieve it\n",
    "greenspacc1 = pd.read_excel(url, sheet_name='Access to open space 2013 wards', header=1)\n",
    "\n",
    "# Rename columns to be GIS-friendly\n",
    "greenspacc1.rename(columns={\n",
    "    'WD13CD':'gss_cd',\n",
    "    'Borough name':'Borough'\n",
    "}, inplace=True)\n",
    "\n",
    "greenspacc1 = greenspacc1[['gss_cd','Open Space','Local Parks','District Parks','Metropolitan Parks','Regional Parks']]\n",
    "print(\"Have \" + str(greenspacc1.shape[0]) + \" rows from 2013 wards.\")\n",
    "#greenspacc1.sample(3, random_state=r_state)\n",
    "\n",
    "### Now the renamed wards...\n",
    "greenspacc2 = pd.read_excel(url, sheet_name='Access to open space 2014 wards', header=1)\n",
    "\n",
    "# Rename columns to be GIS-friendly\n",
    "greenspacc2.rename(columns={\n",
    "    'WD13CD':'gss_cd',\n",
    "    'Borough name':'Borough'\n",
    "}, inplace=True)\n",
    "\n",
    "greenspacc2 = greenspacc2[['gss_cd','Open Space','Local Parks','District Parks','Metropolitan Parks','Regional Parks']]\n",
    "print(\"Have \" + str(greenspacc2.shape[0]) + \" rows from 2014 wards.\")\n",
    "#greenspacc2.sample(3, random_state=r_state)\n",
    "\n",
    "greenspacc = pd.concat([greenspacc1, greenspacc2], ignore_index=True)\n",
    "\n",
    "# Sanity check\n",
    "print(\"Combined we have \" + str(greenspacc.shape[0]) + \" rows.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Inner join should yield about 4.8k matches as LSOAs smaller than wards\n",
    "#test = pd.merge(green, lsoa2ward, how='inner', left_on='Ward name', right_on='WD15NM')\n",
    "lsoagacc = pd.merge(greenspacc, lsoa2ward, how='inner', left_on='gss_cd', right_on='gss_cd')\n",
    "\n",
    "# Duplicates in these sheets for some reason (this is poorly-managed data!)\n",
    "#lsoagacc[lsoagacc.duplicated(subset='lsoa11cd', keep='first')].index.values\n",
    "lsoagacc = lsoagacc.drop(lsoagacc[lsoagacc.duplicated(subset='lsoacd', keep='first')].index.values)\n",
    "\n",
    "# Drop the bits we're not interested in\n",
    "lsoagacc = lsoagacc[['lsoacd','Open Space','Local Parks','District Parks','Metropolitan Parks','Regional Parks']]\n",
    "\n",
    "# Save it\n",
    "lsoagacc.to_csv(os.path.join(greenspace,'Access.csv'), index=False)\n",
    "\n",
    "# Sanity check\n",
    "print(\"Matching rows: \" + str(lsoagacc.shape[0]))\n",
    "lsoagacc.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dwelling Period by LSOA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "url = ('https://files.datapress.com/london/dataset/'\n",
    "       'property-build-period-lsoa/'\n",
    "       'dwelling-period-built-2014-lsoa.csv')\n",
    "\n",
    "# Note suppressed values\n",
    "age = pd.read_csv(url, index_col=False, na_values=\"-\")\n",
    "age.rename(columns={'lsoa':'lsoacd'}, inplace=True)\n",
    "age.drop(['GEOG','Name'], axis=1, inplace=True)\n",
    "\n",
    "# Not formatted as numeric\n",
    "for c in age.columns[3:]:\n",
    "    age[c] = pd.to_numeric(age[c].str.replace(\",\",\"\"))\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(age.shape[0]) + \" rows data.\")\n",
    "age.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "url = ('https://files.datapress.com/london/dataset/'\n",
    "       'property-build-period-lsoa/'\n",
    "       'dwelling-period-built-2015-lsoa-msoa.csv')\n",
    "\n",
    "# Note suppressed values\n",
    "age = pd.read_csv(url, index_col=False, low_memory=False, na_values=\"-\")\n",
    "age.rename(columns={'ECODE':'lsoacd', 'ALL_PROPERTIES':'total'}, inplace=True)\n",
    "\n",
    "age = age.loc[age.BAND=='All',:]\n",
    "age.drop(['GEOGRAPHY','AREA_NAME','BAND'], axis=1, inplace=True)\n",
    "\n",
    "# Not formatted as numeric\n",
    "for c in age.columns[1:]:\n",
    "    age[c] = pd.to_numeric(age[c].str.replace(\",\",\"\"))\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(age.shape[0]) + \" rows data.\")\n",
    "age.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Drop the non-London LSOAs\n",
    "ldn_age  = age.loc[age.lsoacd.isin(ldn2011.lsoacd.values)].copy()\n",
    "\n",
    "# Rename columns and set index\n",
    "ldn_age.set_index('lsoacd', inplace=True)\n",
    "ldn_age.rename(columns=lambda x:x.replace('_','-').replace('BP-','Build Period: '), inplace=True)\n",
    "\n",
    "# I did experiment with trying to calculate a per annum \n",
    "# construction rate in the period prior to each Census\n",
    "# but the time periods are a bit arbitrary and so it's\n",
    "# difficult to line them up in a useful way\n",
    "ldn_age.drop(['Build Period: 2000-2009','Build Period: 2010-2015'], inplace=True, axis=1)\n",
    "ldn_age.fillna(0, inplace=True)\n",
    "#recent = pd.DataFrame()\n",
    "#recent['pre_2001pa'] = age['1993_1999'].apply(lambda x: np.around(x/7.0))  # 7 years in data\n",
    "#recent['pre_2011pa'] = age['2000_2009'].apply(lambda x: x/10.0) # 10 years in data\n",
    "#recent['pre_2021pa'] = age['2010_2014'].apply(lambda x: x/5.0)  # 5 years in data\n",
    "#ldn_age = ldn_age.reset_index().merge(recent, how='left', left_on='lsoacd', right_index=True).fillna(0).set_index('lsoacd')\n",
    "\n",
    "ldn_age.to_csv(os.path.join(dwelling,'Age.csv'), index=True)\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(ldn_age.shape[0]) + \" rows data.\")\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Travel Time to Major Infrastructure\n",
    "\n",
    "<p style=\"color:red;font-weight:bold\">**Do not run this block on a limited connection or one where you are charged per MB or GB.**</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# This data is part of a big zipfile, don't \n",
    "# download on a billable connection\n",
    "import os, io, requests, zipfile, glob\n",
    "from io import BytesIO\n",
    "\n",
    "if not os.path.exists(os.path.join(src,'tmp')):\n",
    "    os.makedirs(os.path.join(src,'tmp'))\n",
    "\n",
    "# Where to get it, and then how we extract it...\n",
    "url = ('https://www.gov.uk/government/uploads/system/uploads/'\n",
    "       'attachment_data/file/318920/'\n",
    "       'connectivity-statistics.zip')\n",
    "\n",
    "print(\"Downloading...\")\n",
    "r = requests.get(url, stream=True)\n",
    "z = zipfile.ZipFile(BytesIO(r.content))\n",
    "print(\"Extracting...\")\n",
    "z.extractall(os.path.join(src,'tmp'))\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# We're only interested in the con0111 file\n",
    "connect = pd.read_excel(glob.glob(os.path.join(src,'tmp','*con0111*'))[0], \n",
    "                        sheet_name='CON0111b_Selected', header=7, skipfooter=28, na_values=\"..\")\n",
    "\n",
    "# This is a huge data set, so let's drop the \n",
    "# irrelevant data as quickly as possible\n",
    "s11 = set(ldn2011.lsoacd.values)\n",
    "s04 = set(ldn2004.lsoacd.values)\n",
    "\n",
    "connect = connect[connect['LSOA Code'].isin(list(s11.union(s04)))]\n",
    "\n",
    "# Tidy up\n",
    "connect.drop(['Upper tier local authority code',\n",
    "              'Upper tier local authority name',\n",
    "              'LSOA population (2011)',\n",
    "              'LSOA Name',\n",
    "              'Unnamed: 5'\n",
    "             ], axis=1, inplace=True)\n",
    "\n",
    "# Rename column\n",
    "connect.rename(columns={\n",
    "    'LSOA Code':'lsoacd'\n",
    "}, inplace=True)\n",
    "\n",
    "# And set as index\n",
    "connect.set_index('lsoacd', inplace=True) # 'LSOA04CD', inplace=True)\n",
    "\n",
    "# Let's focus on airports\n",
    "connect = connect.loc[:, [re.search(\"(?:Heathrow|London|Gatwick|Stansted)\",x) is not None for x in connect.columns] ]\n",
    "connect = connect.replace('..',np.NaN) # This should have happened above... but just in case\n",
    "\n",
    "# Sanity check, should be 4765\n",
    "print(\"Have \" + str(connect.shape[0]) + \" rows of data.\")\n",
    "connect.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# The first set of columns above is for public transit travel time\n",
    "pub_trans = connect[[col for col in connect.columns if '.1' not in col]]\n",
    "\n",
    "# Find the columns to do with Heathrow (LHR) and Gatwick (LGW)\n",
    "lhr = tuple([col for col in pub_trans.columns if 'Heathrow' in col])\n",
    "lgw = tuple([col for col in pub_trans.columns if 'Gatwick' in col])\n",
    "\n",
    "# Calculate mean travel time to the airport as a whole\n",
    "pub_trans = pub_trans.assign(Heathrow=pub_trans.loc[:, lhr].mean(axis=1))\n",
    "pub_trans = pub_trans.assign(Gatwick=pub_trans.loc[:, lgw].mean(axis=1))\n",
    "\n",
    "# Drop the terminal-specific columns\n",
    "pub_trans = pub_trans.loc[:, [re.search(\"(?:T\\d|Terminal)\",x) is None for x in pub_trans.columns]]\n",
    "\n",
    "# Save\n",
    "pub_trans.to_csv(os.path.join(travel,'Infrastructure Access-Public Transit.csv'), index=True)\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(pub_trans.shape[0]) + \" rows data.\")\n",
    "pub_trans.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# The second set of columns above is for private vehicle travel time\n",
    "pri_trans = connect[[col for col in connect.columns if '.1' in col]]\n",
    "\n",
    "# Find the columns to do with Heathrow (LHR) and Gatwick (LGW)\n",
    "lhr = tuple([col for col in pri_trans.columns if 'Heathrow' in col])\n",
    "lgw = tuple([col for col in pri_trans.columns if 'Gatwick' in col])\n",
    "\n",
    "# Calculate mean travel time to the airport as a whole\n",
    "pri_trans = pri_trans.assign(Heathrow=pri_trans.loc[:, lhr].mean(axis=1))\n",
    "pri_trans = pri_trans.assign(Gatwick=pri_trans.loc[:, lgw].mean(axis=1))\n",
    "\n",
    "# Drop the terminal-specific columns -- this is \n",
    "# probably where the set-by-copy warning is coming\n",
    "# from and could be fixed by taking the inverse of \n",
    "# the re.search result and adding an inplace=True to\n",
    "# a drop command.\n",
    "pri_trans = pri_trans.loc[:, [re.search(\"(?:T\\d|Terminal)\",x) is None for x in pri_trans.columns]].copy()\n",
    "\n",
    "# Tidy up the column names\n",
    "pri_trans.rename(columns=lambda x: x.replace(\".1\",\"\"), inplace=True)\n",
    "\n",
    "# Save\n",
    "pri_trans.to_csv(os.path.join(travel,'Infrastructure Access-Private Vehicle.csv'), index=True)\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(pri_trans.shape[0]) + \" rows data.\")\n",
    "pri_trans.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import shutil \n",
    "import os\n",
    "shutil.rmtree(os.path.join(src,'tmp'))\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Travel time to Bank Station\n",
    "\n",
    "This data is provided by the 'My London' service from the London Data Store (usefully demonstrated here: [My London](http://my.london.gov.uk/)). It would be nice if this were properly time-stamped so that we could compare travel times in 2001 with travel times now, but this sort of thing appears not to be on the GLA's or TfL's radar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "url = ('https://files.datapress.com/london/dataset/'\n",
    "       'mylondon/MyLondon_traveltime_to_Bank_station_OA.csv')\n",
    "\n",
    "ttb  = pd.read_csv(url)\n",
    "\n",
    "# Add on LSOA data\n",
    "lsoa2oa = pd.read_csv(os.path.join(lkp,'LSOA_OA_JR.csv'))\n",
    "ttb = pd.merge(lsoa2oa, ttb, left_on='oacd', right_on='OA11CD', how='inner')\n",
    "\n",
    "# Tidy up\n",
    "ttb.drop(['oacd','OA11CD'], axis=1, inplace=True)\n",
    "\n",
    "# Calculate mean travel time for the LSOA from all OAs\n",
    "ttb_lsoa = ttb.groupby('lsoacd').mean()\n",
    "\n",
    "# Save it\n",
    "ttb_lsoa.to_csv(os.path.join(travel,'Travel Time To Bank.csv'), index=True)\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(ttb_lsoa.shape[0]) + \" rows of data.\")\n",
    "print(\"Done.\")\n",
    "ttb_lsoa.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## My Fare Zone\n",
    "\n",
    "From the same My London service is one that tells you the Fare Zone for any Output Area in London. We take the _average_ Fare Zone for each LSOA since that is still a reasonable representation of the cost of travel for that LSOA. An alternative would be to take the mode:\n",
    "```python\n",
    "mfz.groupby('lsoacd')['Fare_Zone'].agg(lambda x:x.value_counts().index[0])\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "url = ('https://files.datapress.com/london/dataset/'\n",
    "       'mylondon/MyLondon_fare_zone_OA.csv')\n",
    "\n",
    "mfz  = pd.read_csv(url)\n",
    "\n",
    "# Add on LSOA data\n",
    "mfz = pd.merge(lsoa2oa, mfz, left_on='oacd', right_on='OA11CD', how='inner')\n",
    "\n",
    "# Tidy up\n",
    "mfz.drop(['oacd','OA11CD'], axis=1, inplace=True)\n",
    "\n",
    "# Calculate mean travel time for the LSOA from all OAs\n",
    "# -- nearly a pointless exercise, but useful to know if\n",
    "# LSOAs straddle a fare boundary and could give us some\n",
    "# handy 'interpolation'\n",
    "mfz_lsoa = mfz.groupby('lsoacd').mean()\n",
    "\n",
    "# Save it\n",
    "mfz_lsoa.to_csv(os.path.join(travel,'Fare Zone.csv'), index=True)\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(mfz_lsoa.shape[0]) + \" rows data.\")\n",
    "print(\"Done.\")\n",
    "mfz_lsoa.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accommodation Mix\n",
    "\n",
    "Tables:\n",
    "- UV042\n",
    "- QS401EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dtype = pd.read_csv(os.path.join(src,'2001','uv042.csv.gz'),\n",
    "    header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')\n",
    "\n",
    "# Convert the columns names to something more tractable -- \n",
    "# note the very slight (but annoying differences) between \n",
    "# the two Census years.\n",
    "dtype.rename(columns={\n",
    "    'mnemonic':'lsoacd',\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'All categories: Accommodation type':'total'\n",
    "}, inplace=True)\n",
    "dtype.rename(columns=lambda x: re.sub('^Unshared dwelling(?:\\: Whole house or bungalow|\\: Flat, maisonette or apartment)?: ','',x), inplace=True)\n",
    "dtype.rename(columns=lambda x: re.sub(' \\(.+?\\)','',x), inplace=True)\n",
    "\n",
    "# Drop the non-London LSOAs\n",
    "dtype.drop(['lsoanm'], axis=1, inplace=True)\n",
    "dtype = dtype[dtype.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# And save\n",
    "dtype.to_csv(os.path.join(src,'Type-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(dtype, src, dwelling, 'Type-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dtype2011 = pd.read_csv(os.path.join(src,'2011','qs401ew.csv.gz'), \n",
    "                      skip_blank_lines=True, header=6, skipfooter=5, engine='python', compression='gzip')\n",
    "\n",
    "# Convert the column names to something more tractable\n",
    "dtype2011.rename(columns={\n",
    "    'mnemonic':'lsoacd',\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'All categories: Accommodation type':'total'\n",
    "}, inplace=True)\n",
    "dtype2011.rename(columns=lambda x: re.sub('^Unshared dwelling(?:\\: Whole house or bungalow|\\: Flat, maisonette or apartment)?: ','',x), inplace=True)\n",
    "dtype2011.rename(columns=lambda x: re.sub(' \\(.+?\\)','',x), inplace=True)\n",
    "\n",
    "# Drop the non-London LSOAs\n",
    "dtype2011.drop(['lsoanm'], axis=1, inplace=True)\n",
    "dtype2011 = dtype2011[dtype2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# And save\n",
    "dtype2011.to_csv(os.path.join(dwelling,'Type-2011.csv'), index=False)\n",
    "\n",
    "# Sanity check\n",
    "print(\"2011 Accommodation Type data frame contains \" + str(dtype2011.shape[0]) + \" rows.\")\n",
    "dtype2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Travel Mode\n",
    "\n",
    "Tables:\n",
    "- UV039 \n",
    "- QS701EW\n",
    "\n",
    "Note that the ONS [points out](https://www.nomisweb.co.uk/census/2011/qs701ew) that is not advisable to directly compare these two data sets because of changes in the way that home-workers are counted. Unfortunately, their suggested Custom Table (CT0015) alternative only has LA-level data which is rather unhelpful. My hope is that by focussing on percentage share, not raw numbers we can control for this to some extent. As well, since all LSOAs should be affected in the same way this _may_ not be as much of a problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ttw2001 = pd.read_csv(os.path.join(src,'2001','uv039.csv.gz'),\n",
    "    header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')\n",
    "\n",
    "# Convert the columns names to something more tractable -- \n",
    "# note the very slight (but annoying differences) between \n",
    "# the two Census years.\n",
    "ttw2001.rename(columns=lambda x: re.sub(\"; measures: Value\",\"\",re.sub(\"Method of travel to work: \",\"\",x)), inplace=True)\n",
    "ttw2001.rename(columns={\n",
    "    'mnemonic':'lsoacd',\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'All categories: Method of travel to work':'Total',\n",
    "    'Work mainly at or from home':'Homeworker', \n",
    "    'Underground, metro, light rail or tram':'Tube or Tram',\n",
    "    'Train':'Train', \n",
    "    'Bus, minibus or coach':'Bus',\n",
    "    'Motorcycle, scooter or moped':'Motorcycle or Moped',\n",
    "    'Driving a car or van':'Private Vehicle',\n",
    "    'Passenger in a car or van':'Passenger in Private Vehicle',\n",
    "    'Taxi or minicab':'Taxi',\n",
    "    'Bicycle':'Bicycle',\n",
    "    'On foot':'Foot',\n",
    "    'Other method of travel to work':'Other travel method',\n",
    "    'Not currently working':'Not in employment'\n",
    "}, inplace=True)\n",
    "\n",
    "# Drop the non-London LSOAs\n",
    "ttw2001.drop(['lsoanm'], axis=1, inplace=True)\n",
    "ttw2001 = ttw2001[ttw2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# And save\n",
    "ttw2001.to_csv(os.path.join(src,'TTW-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(ttw2001, src, travel, 'TTW-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Using the QS703EW table...\n",
    "ttw2011 = pd.read_csv(os.path.join(src,'2011','qs701ew.csv.gz'), \n",
    "                      skip_blank_lines=True, header=6, skipfooter=5, engine='python', compression='gzip')\n",
    "\n",
    "# Convert the column names to something more tractable\n",
    "ttw2011.rename(columns=lambda x: re.sub(\"; measures: Value\",\"\",re.sub(\"Method of Travel to Work: \",\"\",x)), inplace=True)\n",
    "ttw2011.rename(columns={\n",
    "    'mnemonic':'lsoacd',\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'All categories: Method of travel to work':'Total',\n",
    "    'Work mainly at or from home':'Homeworker', \n",
    "    'Underground, metro, light rail, tram':'Tube or Tram',\n",
    "    'Train':'Train', \n",
    "    'Bus, minibus or coach':'Bus',\n",
    "    'Motorcycle, scooter or moped':'Motorcycle or Moped',\n",
    "    'Driving a car or van':'Private Vehicle',\n",
    "    'Passenger in a car or van':'Passenger in Private Vehicle',\n",
    "    'Bicycle':'Bicycle',\n",
    "    'On foot':'Foot',\n",
    "    'Other method of travel to work':'Other travel method'\n",
    "}, inplace=True)\n",
    "\n",
    "ttw2011.sample(3, random_state=r_state)\n",
    "\n",
    "# Drop the non-London LSOAs\n",
    "ttw2011.drop(['lsoanm'], axis=1, inplace=True)\n",
    "ttw2011 = ttw2011[ttw2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# And save\n",
    "ttw2011.to_csv(os.path.join(travel,'TTW-2011.csv'), index=False)\n",
    "\n",
    "# Sanity check\n",
    "print(\"2011 TTW data frame contains \" + str(ttw2011.shape[0]) + \" rows.\")\n",
    "ttw2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cars & Vans\n",
    "\n",
    "Tables:\n",
    "- KS017\n",
    "- KS404EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cv2001 = pd.read_csv(os.path.join(src,'2001','ks017.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "cv2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Car or van availability':'total',\n",
    "    'No cars or vans in household':'No vehicle',\n",
    "    '1 car or van in household':'1 vehicle',\n",
    "    '2 cars or vans in household':'2 vehicles',\n",
    "    '3 cars or vans in household':'3 vehicles',\n",
    "    '4 or more cars or vans in household':'4 or more vehicles',\n",
    "    'sum of all cars or vans in the area':'Count of vehicles'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "cv2001.drop(['lsoanm','Count of vehicles'], axis=1, inplace=True)\n",
    "\n",
    "# Drop the rows we don't need\n",
    "cv2001 = cv2001[cv2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Save it\n",
    "cv2001.to_csv(os.path.join(src,'Cars and Vans-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(cv2001, src, travel, 'Cars and Vans-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cv2011 = pd.read_csv(os.path.join(src,'2011','ks404ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "cv2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Car or van availability':'total',\n",
    "    'No cars or vans in household':'No vehicle',\n",
    "    '1 car or van in household':'1 vehicle',\n",
    "    '2 cars or vans in household':'2 vehicles',\n",
    "    '3 cars or vans in household':'3 vehicles',\n",
    "    '4 or more cars or vans in household':'4 or more vehicles',\n",
    "    'sum of all cars or vans in the area':'Count of vehicles'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "cv2011.drop(['lsoanm','Count of vehicles'], axis=1, inplace=True)\n",
    "\n",
    "# Drop the rows we don't need\n",
    "cv2011 = cv2011[cv2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Save it\n",
    "cv2011.to_csv(os.path.join(travel,'Cars and Vans-2011.csv'), index=False)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(cv2011.shape[0]) + \" rows of data.\")\n",
    "cv2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Age Structure\n",
    "\n",
    "Tables:\n",
    "- KS002\n",
    "- KS102EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "age2001 = pd.read_csv(os.path.join(src,'2001','ks002.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "age2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All usual residents':'total'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "age2001.drop(['lsoanm'], axis=1, inplace=True)\n",
    "age2001 = age2001[age2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Set index\n",
    "age2001.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Aggregate into a new data frame\n",
    "age2001agg = pd.DataFrame()\n",
    "\n",
    "# Drop the rows we don't need\n",
    "age2001agg['Young Children'] = age2001[['Age 0 to 4','Age 5 to 7','Age 8 to 9']].sum(axis=1)\n",
    "age2001agg['Teenagers'] = age2001[['Age 10 to 14','Age 15','Age 16 to 17']].sum(axis=1)\n",
    "age2001agg['Young Adults'] = age2001[['Age 18 to 19','Age 20 to 24','Age 25 to 29']].sum(axis=1)\n",
    "age2001agg['Adults'] = age2001[['Age 30 to 44','Age 45 to 59','Age 60 to 64']].sum(axis=1)\n",
    "age2001agg['Retired'] = age2001[['Age 65 to 74','Age 75 to 84']].sum(axis=1)\n",
    "age2001agg['Elderly'] = age2001[['Age 85 to 89','Age 90 and over']].sum(axis=1)\n",
    "age2001agg['total'] = age2001[['total']]\n",
    "\n",
    "# Save it\n",
    "age2001agg.reset_index(inplace=True, drop=False)\n",
    "age2001agg.to_csv(os.path.join(src,'Age Structure-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(age2001agg, src, household, 'Age Structure-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "age2011 = pd.read_csv(os.path.join(src,'2011','ks102ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "age2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All usual residents':'total'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "age2011.drop(['lsoanm'], axis=1, inplace=True)\n",
    "age2011 = age2011[age2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "age2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Aggregate into a new data frame\n",
    "age2011agg = pd.DataFrame()\n",
    "\n",
    "# Drop the rows we don't need\n",
    "age2011agg['Young Children'] = age2011[['Age 0 to 4','Age 5 to 7','Age 8 to 9']].sum(axis=1)\n",
    "age2011agg['Teenagers'] = age2011[['Age 10 to 14','Age 15','Age 16 to 17']].sum(axis=1)\n",
    "age2011agg['Young Adults'] = age2011[['Age 18 to 19','Age 20 to 24','Age 25 to 29']].sum(axis=1)\n",
    "age2011agg['Adults'] = age2011[['Age 30 to 44','Age 45 to 59','Age 60 to 64']].sum(axis=1)\n",
    "age2011agg['Retired'] = age2011[['Age 65 to 74','Age 75 to 84']].sum(axis=1)\n",
    "age2011agg['Elderly'] = age2011[['Age 85 to 89','Age 90 and over']].sum(axis=1)\n",
    "age2011agg['total'] = age2011[['total']]\n",
    "\n",
    "# Save it\n",
    "age2011agg.to_csv(os.path.join(household,'Age Structure-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(age2011agg.shape[0]) + \" rows of data.\")\n",
    "age2011agg.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Marital Status\n",
    "\n",
    "Tables:\n",
    "- KS004\n",
    "- KS013EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "mar2001 = pd.read_csv(os.path.join(src,'2001','ks004.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "mar2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All people':'total',\n",
    "    'Separated (but still legally married)':'Separated'\n",
    "}, inplace=True)\n",
    "\n",
    "# Aggregate these for compatibility with 2011\n",
    "mar2001['Married'] = mar2001[['Married (first marriage)','Re-married']].sum(axis=1)\n",
    "\n",
    "# Don't need these\n",
    "mar2001.drop(['lsoanm','Married (first marriage)','Re-married'], axis=1, inplace=True)\n",
    "mar2001 = mar2001[mar2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Save it\n",
    "mar2001.to_csv(os.path.join(src,'Marital Status-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(mar2001, src, household, 'Marital Status-2001.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that transition from marriage to incorporate 'registered same-sex civil partnership'. It's hard to tell if these are directly comparable. I am assuming that the composition hasn't changed radically, but we'll need to test this carefully."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "mar2011 = pd.read_csv(os.path.join(src,'2011','ks103ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "mar2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All usual residents aged 16+':'total',\n",
    "    'Single (never married or never registered a same-sex civil partnership)':'Single (never married)',\n",
    "    'Separated (but still legally married or still legally in a same-sex civil partnership)':'Separated',\n",
    "    'Divorced or formerly in a same-sex civil partnership which is now legally dissolved':'Divorced',\n",
    "    'Widowed or surviving partner from a same-sex civil partnership':'Widowed',\n",
    "    'Married':'Married Temp'\n",
    "}, inplace=True)\n",
    "\n",
    "# Aggregate these for compatibility with rest of data\n",
    "mar2011['Married'] = mar2011[['Married Temp','In a registered same-sex civil partnership']].sum(axis=1)\n",
    "\n",
    "# Don't need these\n",
    "mar2011.drop(['lsoanm','Married Temp','In a registered same-sex civil partnership'], axis=1, inplace=True)\n",
    "mar2011 = mar2011[mar2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "mar2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Save it\n",
    "mar2011.to_csv(os.path.join(household,'Marital Status-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(mar2011.shape[0]) + \" rows of data.\")\n",
    "mar2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ethnicity\n",
    "\n",
    "Tables:\n",
    "- KS006\n",
    "- KS201EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "eth2001 = pd.read_csv(os.path.join(src,'2001','ks006.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "eth2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Ethnic group':'total'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "eth2001 = eth2001[eth2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "eth2001.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Aggregate these for compatibility with 2011\n",
    "eth2001agg = pd.DataFrame()\n",
    "eth2001agg['White']   = eth2001[[x for x in eth2001.columns if re.search('^White: ',x)]].sum(axis=1)\n",
    "eth2001agg['Black']   = eth2001[[x for x in eth2001.columns if re.search('^Black',x)]].sum(axis=1)\n",
    "eth2001agg['Asian']   = eth2001[[x for x in eth2001.columns if re.search('^Asian',x)]].sum(axis=1)\n",
    "eth2001agg['Mixed']   = eth2001[[x for x in eth2001.columns if re.search('^Mixed: ',x)]].sum(axis=1)\n",
    "eth2001agg['Other Ethnicity'] = eth2001[[x for x in eth2001.columns if re.search('Other: Chinese',x)]].sum(axis=1)\n",
    "eth2001agg['Chinese'] = eth2001[[x for x in eth2001.columns if re.search('Other: Other',x)]].sum(axis=1)\n",
    "eth2001agg['total']   = eth2001[['total']]\n",
    "\n",
    "# Save it\n",
    "eth2001agg.reset_index(inplace=True, drop=False)\n",
    "eth2001agg.to_csv(os.path.join(src,'Ethnicity-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(eth2001agg, src, household, 'Ethnicity-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "eth2011 = pd.read_csv(os.path.join(src,'2011','ks201ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "eth2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All usual residents':'total',\n",
    "    'Mixed/multiple ethnic groups':'Mixed',\n",
    "    'Black/African/Caribbean/Black British':'Black',\n",
    "    'Other ethnic group':'Other Ethnicity'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "eth2011 = eth2011[eth2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "eth2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Aggregate these for compatibility with rest of data\n",
    "eth2011agg = pd.DataFrame()\n",
    "eth2011agg = eth2011.loc[:,['White','Mixed','Black','Other Ethnicity']]\n",
    "eth2011agg['Asian'] = eth2011[[x for x in eth2011.columns if re.search('^Asian/Asian British: [^C]',x)]].sum(axis=1)\n",
    "eth2011agg['Chinese'] = eth2011[[x for x in eth2011.columns if re.search('Chinese$',x)]].sum(axis=1)\n",
    "eth2011agg['total'] = eth2011[['total']]\n",
    "\n",
    "# Save it\n",
    "eth2011agg.to_csv(os.path.join(household,'Ethnicity-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(eth2011agg.shape[0]) + \" rows of data.\")\n",
    "eth2011agg.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Religion\n",
    "\n",
    "Tables:\n",
    "- KS007\n",
    "- KS209EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rel2001 = pd.read_csv(os.path.join(src,'2001','ks007.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "rel2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Religion':'total',\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "rel2001.drop(['lsoanm'], axis=1, inplace=True)\n",
    "rel2001 = rel2001[rel2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Save it\n",
    "rel2001.to_csv(os.path.join(src,'Religion-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(rel2001, src, household, 'Religion-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rel2011 = pd.read_csv(os.path.join(src,'2011','ks209ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "rel2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Religion':'total',\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "rel2011.drop(['lsoanm'], axis=1, inplace=True)\n",
    "rel2011 = rel2011[rel2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "rel2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Save it\n",
    "rel2011.to_csv(os.path.join(household,'Religion-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(rel2011.shape[0]) + \" rows of data.\")\n",
    "rel2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hours Worked\n",
    "\n",
    "Tables:\n",
    "- KS010\n",
    "- KS604EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "hrs2001 = pd.read_csv(os.path.join(src,'2001','ks010.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "hrs2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "hrs2001 = hrs2001[hrs2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Aggregate these for compatibility with 2011\n",
    "hrs2001['Males - Part-time - 15 hours or less worked'] = hrs2001[['Males - Part-time - 1 to 5 hours','Males - Part-time - 6 to 15 hours']].sum(axis=1)\n",
    "hrs2001['Males - Full-time - 31 to 48 hours worked']   = hrs2001[['Males - Full-time - 31 to 37 hours','Males - Full-time - 38 to 48 hours']].sum(axis=1)\n",
    "hrs2001['Females - Part-time - 15 hours or less worked'] = hrs2001[['Females - Part-time - 1 to 5 hours','Females - Part-time - 6 to 15 hours']].sum(axis=1)\n",
    "hrs2001['Females - Full-time - 31 to 48 hours worked']   = hrs2001[['Females - Full-time - 31 to 37 hours','Females - Full-time - 38 to 48 hours']].sum(axis=1)\n",
    "hrs2001['total']   = hrs2001[['All males aged 16-74 in employment','All females aged 16-74 in employment']].sum(axis=1)\n",
    "\n",
    "hrs2001.drop(['lsoanm','Males - Part-time - 1 to 5 hours','Males - Part-time - 6 to 15 hours',\n",
    "              'Males - Full-time - 31 to 37 hours','Males - Full-time - 38 to 48 hours',\n",
    "              'Females - Part-time - 1 to 5 hours','Females - Part-time - 6 to 15 hours',\n",
    "              'Females - Full-time - 31 to 37 hours','Females - Full-time - 38 to 48 hours',\n",
    "              'All males aged 16-74 in employment','All females aged 16-74 in employment'], axis=1, inplace=True)\n",
    "\n",
    "hrs2001.rename(columns=lambda x: re.sub(' worked$','',re.sub(' - Full-time - ',\": \",re.sub(' - Part-time - ',\": \",x))), inplace=True)\n",
    "hrs2001.sample(3, random_state=r_state)\n",
    "\n",
    "# Save it\n",
    "hrs2001.to_csv(os.path.join(src,'Hours Worked-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(hrs2001, src, work, 'Hours Worked-2001.csv')\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(pd.read_csv(os.path.join(work,'Hours Worked-2001.csv')).shape[0]) + \" rows of data.\")\n",
    "pd.read_csv(os.path.join(work,'Hours Worked-2001.csv'), index_col=0).sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "hrs2011 = pd.read_csv(os.path.join(src,'2011','ks604ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "hrs2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All usual residents aged 16 to 74 in employment the week before the census':'total',\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "hrs2011.drop(['lsoanm'], axis=1, inplace=True)\n",
    "hrs2011 = hrs2011[hrs2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "hrs2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Rename cols for compatibility with 2001\n",
    "hrs2011.rename(columns=lambda x: re.sub(' worked$','',re.sub('Full-time: ','',re.sub('Part-time: ','',x))), inplace=True)\n",
    "\n",
    "# Save it\n",
    "hrs2011.to_csv(os.path.join(work,'Hours Worked-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(hrs2011.shape[0]) + \" rows of data.\")\n",
    "hrs2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Industry\n",
    "\n",
    "Real compatibilty issues here: [this](http://www.businessballs.com/industrialclassifications.htm) is the best comparison I can find to use for a consistent mapping.\n",
    "\n",
    "Tables:\n",
    "- KS011a\n",
    "- KS607EW\n",
    "\n",
    "**ALSO**: note that you can't use a comma even in the header of the CSV file since GeoConvert can't cope."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ind2001 = pd.read_csv(os.path.join(src,'2001','ks011a.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "# More analysis-friendly column names\n",
    "ind2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Industry':'total'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "ind2001.drop(['lsoanm'], axis=1, inplace=True)\n",
    "ind2001 = ind2001[ind2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "ind2001.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Aggregate/rename as needed for compatibility\n",
    "ind2001agg = pd.DataFrame()\n",
    "ind2001agg['A. Agriculture et al'] = ind2001[[x for x in ind2001.columns if re.search('^[AB] ',x)]].sum(axis=1)\n",
    "ind2001agg['C. Mining'] = ind2001[[x for x in ind2001.columns if re.search('^[C] ',x)]].sum(axis=1)\n",
    "ind2001agg['D. Manufacturing'] = ind2001[[x for x in ind2001.columns if re.search('^[D] ',x)]].sum(axis=1)\n",
    "ind2001agg['E. Utilities'] = ind2001[[x for x in ind2001.columns if re.search('^[E] ',x)]].sum(axis=1)\n",
    "ind2001agg['F. Construction'] = ind2001[[x for x in ind2001.columns if re.search('^[F] ',x)]].sum(axis=1)\n",
    "ind2001agg['G. Wholesale and retail'] = ind2001[[x for x in ind2001.columns if re.search('^[G] ',x)]].sum(axis=1)\n",
    "ind2001agg['H. Hotels and restaurants'] = ind2001[[x for x in ind2001.columns if re.search('^[H] ',x)]].sum(axis=1)\n",
    "ind2001agg['I. Transport storage and comms'] = ind2001[[x for x in ind2001.columns if re.search('^[I] ',x)]].sum(axis=1)\n",
    "ind2001agg['J. Financial'] = ind2001[[x for x in ind2001.columns if re.search('^[J] ',x)]].sum(axis=1)\n",
    "ind2001agg['K. Professional'] = ind2001[[x for x in ind2001.columns if re.search('^[K] ',x)]].sum(axis=1)\n",
    "ind2001agg['L. Public Sector'] = ind2001[[x for x in ind2001.columns if re.search('^[L] ',x)]].sum(axis=1)\n",
    "ind2001agg['M. Education'] = ind2001[[x for x in ind2001.columns if re.search('^[M] ',x)]].sum(axis=1)\n",
    "ind2001agg['N. Health and social work'] = ind2001[[x for x in ind2001.columns if re.search('^[N] ',x)]].sum(axis=1)\n",
    "ind2001agg['P. All Other'] = ind2001[[x for x in ind2001.columns if re.search('^[O],',x)]].sum(axis=1)\n",
    "ind2001agg['total'] = ind2001[['total']]\n",
    "\n",
    "# Save it\n",
    "ind2001agg.reset_index(inplace=True, drop=False)\n",
    "ind2001agg.to_csv(os.path.join(src,'Industry-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(ind2001agg, src, work, 'Industry-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ind2011 = pd.read_csv(os.path.join(src,'2011','ks607ew.csv.gz'), \n",
    "                        header=7, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "ind2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Industry':'total'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "ind2011.drop(['lsoanm'], axis=1, inplace=True)\n",
    "ind2011 = ind2011[ind2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "ind2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Aggregate/rename as needed for compatibility\n",
    "ind2011agg = pd.DataFrame()\n",
    "ind2011agg['A. Agriculture et al'] = ind2011[[x for x in ind2011.columns if re.search('^[A] ',x)]].sum(axis=1)\n",
    "ind2011agg['C. Mining'] = ind2011[[x for x in ind2011.columns if re.search('^[B] ',x)]].sum(axis=1)\n",
    "ind2011agg['D. Manufacturing'] = ind2011[[x for x in ind2011.columns if re.search('^[C] ',x)]].sum(axis=1)\n",
    "ind2011agg['E. Utilities'] = ind2011[[x for x in ind2011.columns if re.search('^[DE] ',x)]].sum(axis=1)\n",
    "ind2011agg['F. Construction'] = ind2011[[x for x in ind2011.columns if re.search('^[F] ',x)]].sum(axis=1)\n",
    "ind2011agg['G. Wholesale and retail'] = ind2011[[x for x in ind2011.columns if re.search('^[G] ',x)]].sum(axis=1)\n",
    "ind2011agg['H. Hotels and restaurants'] = ind2011[[x for x in ind2011.columns if re.search('^[I] ',x)]].sum(axis=1)\n",
    "ind2011agg['I. Transport storage and comms'] = ind2011[[x for x in ind2011.columns if re.search('^[HJ] ',x)]].sum(axis=1)\n",
    "ind2011agg['J. Financial'] = ind2011[[x for x in ind2011.columns if re.search('^[K] ',x)]].sum(axis=1)\n",
    "ind2011agg['K. Professional'] = ind2011[[x for x in ind2011.columns if re.search('^[LMN] ',x)]].sum(axis=1)\n",
    "ind2011agg['L. Public Sector'] = ind2011[[x for x in ind2011.columns if re.search('^[O] ',x)]].sum(axis=1)\n",
    "ind2011agg['M. Education'] = ind2011[[x for x in ind2011.columns if re.search('^[P] ',x)]].sum(axis=1)\n",
    "ind2011agg['N. Health and social work'] = ind2011[[x for x in ind2011.columns if re.search('^[Q] ',x)]].sum(axis=1)\n",
    "ind2011agg['P. All Other'] = ind2011[[x for x in ind2011.columns if re.search('^[RSTU],',x)]].sum(axis=1)\n",
    "ind2011agg['total'] = ind2011[['total']]\n",
    "\n",
    "# Save it\n",
    "ind2011agg.to_csv(os.path.join(work,'Industry-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(ind2011agg.shape[0]) + \" rows of data.\")\n",
    "ind2011agg.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tenure\n",
    "\n",
    "Tables:\n",
    "- KS018\n",
    "- KS402EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ten2001 = pd.read_csv(os.path.join(src,'2001','ks018.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "ten2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Tenure':'total',\n",
    "    'Owned outright':'Owned: Outright',\n",
    "    'Owned with a mortgage or loan':'Owned: Mortgaged',\n",
    "    'Rented from council(local authority)':'Rented: Council',\n",
    "    'Rented from a housing association/registered social landlord':'Rented: HA or RSL',\n",
    "    'Rented from a private landlord or letting agency':'Rented: Private',\n",
    "    'Other':'Other Tenure Type'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "ten2001 = ten2001[ten2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "ten2001.drop(['lsoanm'], axis=1, inplace=True)\n",
    "\n",
    "# Save it\n",
    "ten2001.to_csv(os.path.join(src,'Tenure-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(ten2001, src, housing, 'Tenure-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ten2011 = pd.read_csv(os.path.join(src,'2011','ks402ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "ten2011.drop(['Owned','Social rented','Private rented'], axis=1, inplace=True)\n",
    "\n",
    "# More analysis-friendly column names\n",
    "ten2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All households':'total',\n",
    "    'Owned: Owned outright':'Owned: Outright',\n",
    "    'Owned: Owned with a mortgage or loan':'Owned: Mortgaged',\n",
    "    'Shared ownership (part owned and part rented)':'Shared ownership',\n",
    "    'Social rented: Rented from council (Local Authority)':'Rented: Council',\n",
    "    'Social rented: Other':'Rented: HA or RSL',\n",
    "    'Private rented: Private landlord or letting agency':'Rented: Private'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "ten2011.drop(['lsoanm'], axis=1, inplace=True)\n",
    "ten2011 = ten2011[ten2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "ten2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Aggregate to match 2001 data\n",
    "ten2011['Other Tenure Type'] = ten2011[['Private rented: Other','Living rent free']].sum(axis=1)\n",
    "ten2011.drop(['Private rented: Other','Living rent free'], axis=1, inplace=True)\n",
    "\n",
    "# Save it\n",
    "ten2011.to_csv(os.path.join(housing,'Tenure-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(ten2011.shape[0]) + \" rows of data.\")\n",
    "ten2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Household Composition\n",
    "\n",
    "Tables:\n",
    "- KS020\n",
    "- KS105EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "hhc2001 = pd.read_csv(os.path.join(src,'2001','ks020.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "hhc2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All households':'total'\n",
    "}, inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('One person household(?: - )?','1P: ',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('One family and no others(?: - )?','1F: ',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('One person household(?: - )?','1P: ',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('One family only(?: - )?','1F: ',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub(': married couple households(?: - )?','M: ',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub(': cohabiting couple households(?: - )?','C: ',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub(': Lone parent households(?: - )?','L: ',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('Other households(?: - )?','O: ',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('(?:All p|P)ensioners?','65+',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('All students','Students',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('with dependent children','Dependent children',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('no children','No children',x), inplace=True)\n",
    "hhc2001.rename(columns=lambda x: re.sub('all children non-dependent','All children non-dependent',x), inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "hhc2001 = hhc2001[hhc2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "hhc2001.drop(['lsoanm'], axis=1, inplace=True)\n",
    "\n",
    "# Save it\n",
    "hhc2001.to_csv(os.path.join(src,'Household Composition-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(hhc2001, src, household, 'Household Composition-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "hhc2011 = pd.read_csv(os.path.join(src,'2011','ks105ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "hhc2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Household composition':'total'\n",
    "}, inplace=True)\n",
    "hhc2011.rename(columns=lambda x: re.sub('One person household(?:\\: )?','1P: ',x), inplace=True)\n",
    "hhc2011.rename(columns=lambda x: re.sub('One family only(?:\\: )?','1F: ',x), inplace=True)\n",
    "hhc2011.rename(columns=lambda x: re.sub(': Married or same-sex civil partnership couple(?:\\: )?','M: ',x), inplace=True)\n",
    "hhc2011.rename(columns=lambda x: re.sub(': Cohabiting couple(?:\\: )?','C: ',x), inplace=True)\n",
    "hhc2011.rename(columns=lambda x: re.sub(': Lone parent(?:\\: )?','L: ',x), inplace=True)\n",
    "hhc2011.rename(columns=lambda x: re.sub('Other household types(?:\\: )?','O: ',x), inplace=True)\n",
    "hhc2011.rename(columns=lambda x: re.sub('A(?:ll a)?ged 65 and over','65+',x), inplace=True)\n",
    "hhc2011.rename(columns=lambda x: re.sub('All full-time students','Students',x), inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "hhc2011.drop(['lsoanm','One family household','1P: ','1FM: ','1FC: ','1FL: ','O: '], axis=1, inplace=True)\n",
    "hhc2011 = hhc2011[hhc2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "hhc2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Save it\n",
    "hhc2011.to_csv(os.path.join(household,'Household Composition-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(hhc2011.shape[0]) + \" rows of data.\")\n",
    "hhc2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Population Density\n",
    "\n",
    "Tables:\n",
    "- UV002\n",
    "- QS102EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "den2001 = pd.read_csv(os.path.join(src,'2001','uv002.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "den2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'Density (number of persons per hectare)':'Density'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "den2001 = den2001[den2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "den2001.drop(['lsoanm','All usual residents','Area Hectares'], axis=1, inplace=True)\n",
    "\n",
    "# Save it\n",
    "den2001.to_csv(os.path.join(src,'Density-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(den2001, src, housing, 'Density-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "den2011 = pd.read_csv(os.path.join(src,'2011','qs102ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "den2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'Density (number of persons per hectare)':'Density'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "den2011.drop(['lsoanm','All usual residents','Area Hectares'], axis=1, inplace=True)\n",
    "den2011 = den2011[den2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "den2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Save it\n",
    "den2011.to_csv(os.path.join(housing,'Density-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(den2011.shape[0]) + \" rows of data.\")\n",
    "den2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dependent Children\n",
    "\n",
    "Tables:\n",
    "- UV006\n",
    "- QS118EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dep2001 = pd.read_csv(os.path.join(src,'2001','uv006.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "dep2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All families in households':'total',\n",
    "    'No dependent children in family':'0D'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "dep2001 = dep2001[dep2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "dep2001.drop(['lsoanm'], axis=1, inplace=True)\n",
    "\n",
    "# Tidy up column names\n",
    "dep2001.rename(columns=lambda x: re.sub('One dependent child in family aged ?','1D: ',x), inplace=True)\n",
    "dep2001.rename(columns=lambda x: re.sub('Two dependent children in family; youngest aged','2D: ',x), inplace=True)\n",
    "\n",
    "# Save it\n",
    "dep2001.to_csv(os.path.join(src,'Dependent Children-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(dep2001, src, household, 'Dependent Children-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dep2011 = pd.read_csv(os.path.join(src,'2011','qs118ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "dep2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All families in households':'total',\n",
    "    'No dependent children in family':'0D'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these or they are incompatible with 2001 data\n",
    "dep2011.drop(['lsoanm',\n",
    "              'Three or more dependent children in family; youngest aged 0 to 4',\n",
    "              'Three or more dependent children in family; youngest aged 5 to 11',\n",
    "              'Three or more dependent children in family; youngest aged 12 to 18',\n",
    "              'Total dependent children'], axis=1, inplace=True)\n",
    "dep2011 = dep2011[dep2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "dep2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Tidy up column names\n",
    "dep2011.rename(columns=lambda x: re.sub('One dependent child in family aged ?','1D: ',x), inplace=True)\n",
    "dep2011.rename(columns=lambda x: re.sub('Two dependent children in family; youngest aged','2D: ',x), inplace=True)\n",
    "\n",
    "# Save it\n",
    "dep2011.to_csv(os.path.join(household,'Dependent Children-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(dep2011.shape[0]) + \" rows of data.\")\n",
    "dep2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Country of Birth\n",
    "\n",
    "Tables:\n",
    "- UV008\n",
    "- QS203EW\n",
    "\n",
    "These are hard to work out because the categories change a lot, but my best attempt at mapping between them is:\n",
    "- England\n",
    "- Scotland\n",
    "- Wales\n",
    "- **Rest of UK** (UK _minus_ the above)\n",
    "- EU 2001 Members\n",
    "- Poland\n",
    "- Turkey\n",
    "- **Rest of Europe** (Bits of Europe not covered above)\n",
    "- North Africa\n",
    "- South and Eastern Africa\n",
    "- **Rest of Africa** (Africa _minus_ the above)\n",
    "- Middle East\n",
    "- Far East / Eastern Asia\n",
    "- South Asia / Souther Asia\n",
    "- **Rest of Asia** (Asia _minus_ the above)\n",
    "\n",
    "For reasons that I _cannot_ work out I get a sum of groups that is greater than the total. Since every LSOA is affected the same way this shouldn't matter but clearly I am double-counting _someone_ in there."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cb2001 = pd.read_csv(os.path.join(src,'2001','uv008.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "cb2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Country of birth':'total',\n",
    "    'EU countries':'EU 2001 Members',\n",
    "    'Republic of Ireland':'Ireland'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "cb2001 = cb2001[cb2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Set index\n",
    "cb2001.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Aggregate for compatibility\n",
    "cb2001agg = cb2001.loc[:,['total', 'England']]\n",
    "\n",
    "cb2001agg['Rest of UK'] = cb2001.loc[:,'United Kingdom'] \\\n",
    "        - cb2001.loc[:,'England']\n",
    "\n",
    "cb2001agg['EU 2001 Members'] = cb2001.loc[:,'EU 2001 Members'] \\\n",
    "        + cb2001.loc[:,'Ireland']\n",
    "\n",
    "cb2001agg['Rest of Europe'] = cb2001.loc[:,'Europe'] \\\n",
    "        - (cb2001.loc[:,'United Kingdom'] \\\n",
    "           + cb2001.loc[:,'EU 2001 Members'] \\\n",
    "           + cb2001.loc[:,'Ireland'])\n",
    "        \n",
    "cb2001agg['Asia'] = cb2001.loc[:,'Asia']\n",
    "cb2001agg['Africa'] = cb2001.loc[:,'Africa']\n",
    "cb2001agg['Oceania'] = cb2001.loc[:,'Oceania']\n",
    "\n",
    "cb2001agg['Americas'] = cb2001[['North America','South America']].sum(axis=1)\n",
    "\n",
    "cb2001agg.reset_index(inplace=True, drop=False)\n",
    "    \n",
    "# Save it\n",
    "cb2001agg.to_csv(os.path.join(src,'Country of Birth-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(cb2001agg, src, household, 'Country of Birth-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cb2011 = pd.read_csv(os.path.join(src,'2011','qs203ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "cb2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Country of birth':'total',\n",
    "    'Europe: Total':'Europe',\n",
    "    'Europe: United Kingdom: Total':'United Kingdom',\n",
    "    'Africa: Total':'Africa',\n",
    "    'Middle East and Asia: Total':'Asia',\n",
    "    'The Americas and the Caribbean: Total':'Americas',\n",
    "    'Antarctica and Oceania: Total':'Oceania',\n",
    "    'Europe: Other Europe: EU countries: Member countries in March 2001: Total':'EU 2001 Members'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these or they are incompatible with 2001 data\n",
    "cb2011 = cb2011[cb2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "cb2011.rename(columns=lambda x: re.sub('^(?:Europe|Africa|Middle East and Asia|The Americas and the Caribbean|Antarctica and Oceania): ','',x), inplace=True)\n",
    "cb2011.rename(columns=lambda x: re.sub('^(?:Other Europe|United Kingdom): ','',x), inplace=True)\n",
    "cb2011.rename(columns=lambda x: re.sub(': Total$','',x), inplace=True)\n",
    "\n",
    "# Set the index\n",
    "cb2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "cb2011agg = cb2011.loc[:,['total', 'England']]\n",
    "\n",
    "cb2011agg['Rest of UK'] = cb2011.loc[:,'United Kingdom'] \\\n",
    "        - cb2011.loc[:,'England']\n",
    "\n",
    "cb2011agg['EU 2001 Members'] = cb2011.loc[:,'EU 2001 Members'] \\\n",
    "        + cb2011.loc[:,'Ireland']\n",
    "    \n",
    "cb2011agg['Rest of Europe'] = cb2011.loc[:,'Europe'] \\\n",
    "        - (cb2011.loc[:,'United Kingdom'] \\\n",
    "           + cb2011.loc[:,'EU 2001 Members'] \\\n",
    "           + cb2011.loc[:,'Ireland'])\n",
    "\n",
    "cb2011agg['Asia'] = cb2011.loc[:,'Asia']\n",
    "cb2011agg['Africa'] = cb2011.loc[:,'Africa']\n",
    "cb2011agg['Oceania'] = cb2011.loc[:,'Oceania']\n",
    "cb2011agg['Americas'] = cb2011.loc[:,'Americas']\n",
    "\n",
    "# Save it\n",
    "cb2011agg.to_csv(os.path.join(household,'Country of Birth-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(cb2011agg.shape[0]) + \" rows of data.\")\n",
    "cb2011agg.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## General Health\n",
    "\n",
    "Tables:\n",
    "- UV020\n",
    "- QS302EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gh2001 = pd.read_csv(os.path.join(src,'2001','uv020.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "gh2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'Fairly good health':'Fair health',\n",
    "    'Not good health':'Poor health'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "gh2001 = gh2001[gh2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "gh2001.drop(['lsoanm'], axis=1, inplace=True)\n",
    "\n",
    "# Save it\n",
    "gh2001.to_csv(os.path.join(src,'General Health-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(gh2001, src, household, 'General Health-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gh2011 = pd.read_csv(os.path.join(src,'2011','qs302ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "gh2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: General health':'total'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these or they are incompatible with 2001 data\n",
    "gh2011 = gh2011[gh2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Set the index\n",
    "gh2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "gh2011['Good health'] = gh2011[['Very good health','Good health']].sum(axis=1)\n",
    "gh2011['Poor health'] = gh2011[['Bad health','Very bad health']].sum(axis=1)\n",
    "\n",
    "gh2011.drop(['lsoanm','Very good health',\n",
    "             'Bad health','Very bad health'], axis=1, inplace=True)\n",
    "\n",
    "# Save it\n",
    "gh2011.to_csv(os.path.join(household,'General Health-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(gh2011.shape[0]) + \" rows of data.\")\n",
    "gh2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NS-SeC\n",
    "\n",
    "This is a tricky one since the categories are [not considered directly comparable](https://www.ons.gov.uk/employmentandlabourmarket/peopleinwork/employmentandemployeetypes/bulletins/keystatisticsandquickstatisticsforlocalauthoritiesintheunitedkingdom/2013-12-04) across Census years because of movement between classifications (e.g. an occupation that moves from being unskilled to skilled), but it's also a very useful composite view.\n",
    "\n",
    "Tables:\n",
    "- UV031\n",
    "- QS607EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "nssec2001 = pd.read_csv(os.path.join(src,'2001','uv031.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "nssec2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: NS-SeC':'total',\n",
    "    '2. Lower managerial, administrative and professional occupations':'2. Lower managerial and professional occupations' # Avoid commas in columns\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "nssec2001 = nssec2001[nssec2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "nssec2001.drop(['lsoanm','1. Higher managerial, administrative and professional occupations'], axis=1, inplace=True)\n",
    "nssec2001.drop([x for x in nssec2001.columns if x.startswith(\"L\")], axis=1, inplace=True)\n",
    "\n",
    "# Save it\n",
    "nssec2001.to_csv(os.path.join(src,'NS-SeC-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(nssec2001, src, work, 'NS-SeC-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "nssec2011 = pd.read_csv(os.path.join(src,'2011','qs607ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "nssec2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: NS-SeC':'total',\n",
    "    '2. Lower managerial, administrative and professional occupations':'2. Lower managerial and professional occupations' # Avoid commas in columns\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these or they are incompatible with 2001 data\n",
    "nssec2011 = nssec2011[nssec2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "nssec2011.drop(['lsoanm','1. Higher managerial, administrative and professional occupations'], axis=1, inplace=True)\n",
    "nssec2011.drop([x for x in nssec2011.columns if x.startswith(\"L\")], axis=1, inplace=True)\n",
    "\n",
    "# Set the index\n",
    "nssec2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Save it\n",
    "nssec2011.to_csv(os.path.join(work,'NS-SeC-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(nssec2011.shape[0]) + \" rows of data.\")\n",
    "nssec2011.sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Economic Activity\n",
    "\n",
    "Tables:\n",
    "- UV028\n",
    "- QS601EW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ea2001 = pd.read_csv(os.path.join(src,'2001','uv028.csv.gz'), \n",
    "                        header=5, skipfooter=4, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "ea2001.rename(columns={\n",
    "    'super output areas - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Economic activity':'total',\n",
    "    'Employee: Part-time':'PT Employee',\n",
    "    'Employee: Full-time':'FT Employee',\n",
    "    'Self-employed with employees: Part-time':'PT Self-Employed with Employees',\n",
    "    'Self-employed with employees: Full-time':'FT Self-Employed with Employees',\n",
    "    'Self-employed without employees: Part-time':'PT Self-Employed without Employees',\n",
    "    'Self-employed without employees: Full-time':'FT Self-Employed without Employees',\n",
    "    'Economically inactive: Total':'Economically inactive'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these\n",
    "ea2001 = ea2001[ea2001.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "ea2001.drop('lsoanm', inplace=True, axis=1)\n",
    "\n",
    "# Inactive less Retired and Looking after home or family\n",
    "ea2001['Economically inactive'] = ea2001.loc[:,'Economically inactive'] \\\n",
    "    - (ea2001.loc[:,'Retired'] + \n",
    "       ea2001.loc[:,'Looking after home or family'])\n",
    "\n",
    "# Save it\n",
    "ea2001.to_csv(os.path.join(src,'Economic Activity-2001.csv'), index=False)\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(ea2001, src, work, 'Economic Activity-2001.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ea2011 = pd.read_csv(os.path.join(src,'2011','qs601ew.csv.gz'), \n",
    "                        header=6, skipfooter=5, skip_blank_lines=True, engine='python', compression='gzip')#, na_values=\"..\")\n",
    "\n",
    "# More analysis-friendly column names\n",
    "ea2011.rename(columns=lambda x: re.sub('Economically (?:in)?active: ','',x), inplace=True)\n",
    "ea2011.rename(columns={\n",
    "    '2011 super output area - lower layer':'lsoanm',\n",
    "    'mnemonic':'lsoacd',\n",
    "    'All categories: Economic activity':'total',\n",
    "    'Employee: Part-time':'PT Employee',\n",
    "    'Employee: Full-time':'FT Employee',\n",
    "    'Self-employed with employees: Part-time':'PT Self-Employed with Employees',\n",
    "    'Self-employed with employees: Full-time':'FT Self-Employed with Employees',\n",
    "    'Self-employed without employees: Part-time':'PT Self-Employed without Employees',\n",
    "    'Self-employed without employees: Full-time':'FT Self-Employed without Employees',\n",
    "    'Total':'Economically inactive'\n",
    "}, inplace=True)\n",
    "\n",
    "# Don't need these or they are incompatible with 2001 data\n",
    "ea2011 = ea2011[ea2011.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "ea2011.drop(['lsoanm'], axis=1, inplace=True)\n",
    "\n",
    "# Inactive less Retired and Looking after home or family\n",
    "ea2001['Economically inactive'] = ea2001.loc[:,'Economically inactive'] \\\n",
    "    - (ea2001.loc[:,'Retired'] + \n",
    "       ea2001.loc[:,'Looking after home or family'])\n",
    "\n",
    "# Set the index\n",
    "ea2011.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# Save it\n",
    "ea2011.to_csv(os.path.join(work,'Economic Activity-2011.csv'), index=True)\n",
    "\n",
    "# Sanity check, should be 4835\n",
    "print(\"Have \" + str(ea2011.shape[0]) + \" rows of data.\")\n",
    "ea2011.sample(3, random_state=r_state)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ML Gentrification 3",
   "language": "python",
   "name": "mlgent"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}