02-Scoring Data Download and Prep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scoring Data Downloading & Preparation\n",
    "\n",
    "This notebook focusses on the 4 dimensions used for creating the scores for 2001 and 2011, and that we will try to predict for 2021."
   ]
  },
  {
   "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 pysal as ps\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 scipy.stats import gmean\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",
    "housing   = os.path.join(canonical,'housing')\n",
    "household = os.path.join(canonical,'households')\n",
    "work      = os.path.join(canonical,'work')\n",
    "\n",
    "for d in [canonical,converted,housing,household,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(\"\\t2004: \" + 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",
    "    \"\"\"\n",
    "    Converts 2001 data to be compatible with 2011 data -- \n",
    "    this is to deal with the boundary changes that happen\n",
    "    at each Census.\n",
    "    \"\"\"\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))\n",
    "    return\n",
    "\n",
    "def get_neighbours(ns, col):\n",
    "    \"\"\"\n",
    "    Find neighbours of a given LSOA.\n",
    "    \"\"\"\n",
    "    neighbours = []\n",
    "    for n in ns.keys():\n",
    "        #print(str(n) + \" -> \" + str(col[n][0][1]))\n",
    "        neighbours.append(col[n][0][1]) # Not elegant, but column name changes with year\n",
    "    return neighbours\n",
    "\n",
    "def get_gmean_from_neighbours(ns, prices):\n",
    "    \"\"\"\n",
    "    Find geometric mean of an LSOAs _neighbours'_ property transactions.\n",
    "    \"\"\"\n",
    "    print(\"\\tSearching for: \" + \", \".join(map(str, ns)))\n",
    "    medians = prices.loc[prices.index.isin(ns),'Median Property Price'].values\n",
    "    print(\"\\tFound median prices: \" + \", \".join(map(str, medians)))\n",
    "    return round(gmean(medians[np.logical_not(np.isnan(medians))]), -1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Modelled LSOA Household Income"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "print(\"Getting LSOA Household Income estimates from London Data Store...\")\n",
    "print(\"Note: this has already been converted to use LSOA 2011 codes!\")\n",
    "url  = ('https://files.datapress.com/london/dataset/'\n",
    "        'household-income-estimates-small-areas/'\n",
    "        'modelled-household-income-estimates-lsoa.csv')\n",
    "\n",
    "# Retrieve it\n",
    "hhi  = pd.read_csv(url, encoding='latin-1')\n",
    "\n",
    "# Rename key cols\n",
    "hhi.rename(columns={'Code':'lsoacd'}, inplace=True)\n",
    "\n",
    "hhi.set_index('lsoacd', inplace=True)\n",
    "\n",
    "# And break them down into subsets\n",
    "hhi2001 = hhi.loc[:,['Median 2001/02']]\n",
    "hhi2011 = hhi.loc[:,['Median 2011/12']]\n",
    "\n",
    "# Rename the columns\n",
    "print(\"Renaming...\")\n",
    "hhi2001.rename(columns=lambda x: x.replace(' 2001/02', ' Income'), inplace=True)\n",
    "hhi2011.rename(columns=lambda x: x.replace(' 2011/12', ' Income'), inplace=True)\n",
    "\n",
    "# Convert to numeric\n",
    "print(\"Converting to numeric data types...\")\n",
    "for df in [hhi2001, hhi2011]:\n",
    "    df.loc[:,('Median Income')] = pd.to_numeric(df.loc[:,'Median Income'].str.replace(\"\\D+\",\"\"), errors='coerce')\n",
    "\n",
    "# And save to CSV\n",
    "hhi2001.to_csv(os.path.join(work,'Income-2001.csv'), index=True, header=True, encoding='utf-8')\n",
    "hhi2011.to_csv(os.path.join(work,'Income-2011.csv'), index=True, header=True, encoding='utf-8')\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(hhi2001.shape[0]) + \" rows of data.\")\n",
    "print(\"   Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Median Housing & Sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(\"Getting LSOA Housing Value estimates from London Data Store...\")\n",
    "print(\"Note: this has already been converted to use LSOA 2011 codes!\")\n",
    "url  = ('https://files.datapress.com/london/dataset/'\n",
    "        'average-house-prices-ward-lsoa-msoa/' \n",
    "        '2016-07-06T14:34:00/house-prices-LSOAs.csv')\n",
    "\n",
    "# Retrieve it\n",
    "hhv  = pd.read_csv(url, na_values=\".\", encoding='latin-1')\n",
    "\n",
    "# Simplify column names\n",
    "hhv.rename(columns={\n",
    "        'Lower Super Output Area':'lsoacd',\n",
    "        'Names':'Name',\n",
    "        'Census 2011 dwellings':'Dwellings_2011'}, inplace=True)\n",
    "\n",
    "# Set the index\n",
    "hhv.set_index('lsoacd', inplace=True)\n",
    "hhv.rename(columns=lambda x: re.sub('-',' ',re.sub('(?:\\\\([^\\\\)]+\\\\))','',x)), inplace=True)\n",
    "\n",
    "\n",
    "# And break them down into subsets\n",
    "hhv2001 = hhv.loc[:,['Median 2001','Sales 1995',\n",
    "               'Sales 1996', 'Sales 1997', \n",
    "               'Sales 1998', 'Sales 1999',\n",
    "               'Sales 2000', 'Sales 2001']]\n",
    "hhv2011 = hhv.loc[:,['Median 2011','Sales 2005', \n",
    "               'Sales 2006', 'Sales 2007',\n",
    "               'Sales 2008', 'Sales 2009', \n",
    "               'Sales 2010', 'Sales 2011']]\n",
    "\n",
    "# Rename keys for consistency\n",
    "hhv2001.rename(columns={'Median 2001':'Median Property Price'}, inplace=True)\n",
    "hhv2011.rename(columns={'Median 2011':'Median Property Price'}, inplace=True)\n",
    "\n",
    "# Remove underscores\n",
    "hhv2001.rename(columns=lambda x: x.replace('_',''), inplace=True)\n",
    "hhv2011.rename(columns=lambda x: x.replace('_',''), inplace=True)\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(hhv2001.shape[0]) + \" rows of data.\")\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dealing with NaNs\n",
    "\n",
    "It should be only the house price data that has NaNs -- I can't be 100% certain, but I'd assume that this is because there were no transactions in these LSOAs that year (and they don't -- and shouldn't -- fill in missing data by looking back at previous years) so there was nothing to report, or because those LSOAs didn't exist and they've not done a good job of back-filling with real data. \n",
    "\n",
    "We don't want to simply drop these areas from the analysis since they'll create gaps in our reuslts for no particularly good reason. Looking to the raw Land Registry data and then trying to work out the most representative range of nearby values would work but represents a huge amount of effort for relatively little return. Consequently, the most effective solution appears to me to take the geometric mean of the surrounding medians as a 'best guess' as to what values in the LSOA might be. The geometric mean is more robust to outliers and so should cope fairly well in those areas where there is a steep price gradient. But to make life easy you'll see below what values were used in each calculation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# This data has already been mapped on to \n",
    "# 2011 LSOA boundaries... For some reason the\n",
    "# ones from the GLA Data Store don't work, but\n",
    "# the full one available from the OS do. \n",
    "qw       = ps.weights.Queen.from_shapefile(\n",
    "                os.path.join('data','shp','LSOA-Weights.shp')) # Weights/Adjacency\n",
    "fh       = ps.open(\n",
    "                os.path.join('data','shp','LSOA-Weights.dbf'))\n",
    "cds      = fh.by_col['lsoa11cd'] # LSOA 2011 Census code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(\"2001...\")\n",
    "nan01 = hhv2001[hhv2001['Median Property Price'].isnull()].index.values\n",
    "print(\"\\tLooking for neighbours of \" + str(len(nan01)) + \" areas without house prices.\")\n",
    "\n",
    "for z in nan01:\n",
    "    print(\"Finding neighbours for \" + z + \"(id: \" + str(cds.index(z)) + \")\")\n",
    "    neighbours01 = get_neighbours(qw[ cds.index(z) ], fh)\n",
    "    \n",
    "    m = get_gmean_from_neighbours(neighbours01, hhv2001)\n",
    "    print(\"\\t\" + z + \" has been assigned geometric mean of neighbours: \" + str(m))\n",
    "    hhv2001.loc[z,'Median Property Price'] = m\n",
    "print(\" \")\n",
    "\n",
    "print(\"2011...\")\n",
    "nan11 = hhv2011[hhv2011['Median Property Price'].isnull()].index.values\n",
    "print(\"\\tLooking for neighbours of \" + str(len(nan11)) + \" areas without house prices.\")\n",
    "\n",
    "for z in nan11:\n",
    "    print(\"Finding neighbours for \" + z)\n",
    "    neighbours11 = get_neighbours(qw[ cds.index(z) ], fh)\n",
    "    \n",
    "    m = get_gmean_from_neighbours(neighbours11, hhv2011)\n",
    "    print(\"\\t\" + z + \" has been assigned geometric mean of neighbours: \" + str(m))\n",
    "    hhv2011.loc[z,'Median Property Price'] = m\n",
    "print(\" \")\n",
    "\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# This should have been pulled from real data\n",
    "hhv2001[hhv2001.index=='E01001510']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# This should have been assigned from the geometric mean of neighbours calculation\n",
    "hhv2011[hhv2011.index=='E01001510']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# And save to CSV\n",
    "hhv2001.loc[:,['Median Property Price']].to_csv(os.path.join(housing,'Values-2001.csv'), index=True, header=True, encoding='utf-8')\n",
    "hhv2011.loc[:,['Median Property Price']].to_csv(os.path.join(housing,'Values-2011.csv'), index=True, header=True, encoding='utf-8')\n",
    "\n",
    "# Probably not useful but worked out just in case\n",
    "# the rate of transactions in the runup to the \n",
    "# Census year is a useful indicator.\n",
    "hhv2001.loc[:,['Sales 1995','Sales 1996', 'Sales 1997', 'Sales 1998', 'Sales 1999',\n",
    "               'Sales 2000', 'Sales 2001']].to_csv(os.path.join(housing,'Transactions-2001.csv'), index=True, header=True, encoding='utf-8')\n",
    "hhv2011.loc[:,['Sales 2005', 'Sales 2006', 'Sales 2007','Sales 2008', 'Sales 2009', \n",
    "               'Sales 2010', 'Sales 2011']].to_csv(os.path.join(housing,'Transactions-2011.csv'), index=True, header=True, encoding='utf-8')\n",
    "\n",
    "# Sanity check\n",
    "print(\"Have \" + str(hhv2001.shape[0]) + \" rows of data.\")\n",
    "print(\"   Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Occupations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2001 Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(\"Processing 2001 Occupations data from Nomis Table KS012a...\")\n",
    "print(\"Note: this needs to be converted to LSOA 2011 codes using GeoConvert!\")\n",
    "\n",
    "# Load the data from the KS012a table\n",
    "occ_01 = pd.read_csv(os.path.join(src,\"2001\",\"ks012a.csv.gz\"),\n",
    "                      header=5, skip_blank_lines=True, compression='gzip')\n",
    "\n",
    "# Rename the columns to something easier to work with\n",
    "occ_01.rename(columns=lambda x: re.sub(\"^\\d+\\. \",\"\",x), inplace=True)\n",
    "occ_01.rename(columns={\n",
    "    'mnemonic':'lsoacd', \n",
    "    'super output areas - lower layer':'LSOANM', \n",
    "    'All categories: Occupation':'Total',\n",
    "    'Managers and senior officials':'Managerial',\n",
    "    'Professional occupations':'Professional',\n",
    "    'Associate professional and technical occupations':'Technical',\n",
    "    'Administrative and secretarial occupations':'Administrative',\n",
    "    'Skilled trades occupations':'Skilled',\n",
    "    'Personal service occupations':'Personal Service',\n",
    "    'Sales and customer service occupations':'Customer Service',\n",
    "    'Process, plant and machine operatives':'Operators',\n",
    "    'Elementary occupations':'Elementary'\n",
    "}, inplace=True)\n",
    "\n",
    "# Select only those rows that are in the London 2001 LSOA list\n",
    "occ_01 = occ_01.loc[occ_01.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Drop the columns we're not interested in\n",
    "occ_01.drop('LSOANM', axis=1, inplace=True)\n",
    "\n",
    "occ_01.to_csv(os.path.join(src,\"Occupations-2001.csv\"), index=False, header=True, encoding='utf-8')\n",
    "\n",
    "# Sanity check\n",
    "print(\"Wrote \" + str(occ_01.shape[0]) + \" rows to output file.\")\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(occ_01, src, work, 'Occupations-2001.csv')\n",
    "\n",
    "# Sanity check\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2011 Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(\"Processing 2011 Occupations data from Nomis Table KS610EW...\")\n",
    "print(\"Note: this does not need to be converted.\")\n",
    "\n",
    "# Load the data from the KS610EW table\n",
    "occ_11 = pd.read_csv(os.path.join(src,\"2011\",\"ks610ew.csv.gz\"),\n",
    "                      header=7, skip_blank_lines=True, compression='gzip')\n",
    "\n",
    "# Rename the columns to something easier to work with\n",
    "occ_11.rename(columns=lambda x: re.sub(\"^\\d+\\. \",\"\",x), inplace=True)\n",
    "occ_11.rename(columns={\n",
    "    'mnemonic':'lsoacd', \n",
    "    '2011 super output area - lower layer':'LSOANM', \n",
    "    'All categories: Occupation':'Total',\n",
    "    'Managers, directors and senior officials':'Managerial',\n",
    "    'Professional occupations':'Professional',\n",
    "    'Associate professional and technical occupations':'Technical',\n",
    "    'Administrative and secretarial occupations':'Administrative',\n",
    "    'Skilled trades occupations':'Skilled',\n",
    "    'Caring, leisure and other service occupations':'Personal Service',\n",
    "    'Sales and customer service occupations':'Customer Service',\n",
    "    'Process plant and machine operatives':'Operators',\n",
    "    'Elementary occupations':'Elementary'\n",
    "}, inplace=True)\n",
    "\n",
    "# Select only those rows that are in the London 2011 LSOA list\n",
    "occ_11 = occ_11.loc[occ_11.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Drop the columns we're not interested in\n",
    "occ_11.drop('LSOANM', axis=1, inplace=True)\n",
    "occ_11.to_csv(os.path.join(work,\"Occupations-2011.csv\"), index=False, header=True, encoding='utf-8')\n",
    "\n",
    "# Sanity check\n",
    "print(\"Wrote \" + str(occ_11.shape[0]) + \" rows to output file.\")\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Qualifications"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2001 Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(\"Processing 2001 Qualifications data from Nomis Table KS013...\")\n",
    "print(\"Note: this needs to be converted to LSOA 2011 codes using GeoConvert!\")\n",
    "\n",
    "# Load the data from the KS013 table\n",
    "quals_01 = pd.read_csv(os.path.join(src,\"2001\",\"ks013.csv.gz\"),\n",
    "                      header=5, skip_blank_lines=True, compression='gzip')\n",
    "\n",
    "# Rename the columns to something easier to work with\n",
    "quals_01.rename(columns=lambda x: re.sub(\"(?:Highest level of qualification: )(.+) qualifications\",\"\\\\1\",x), inplace=True)\n",
    "quals_01.rename(columns=lambda x: re.sub(\"(?:Full-time students: Age 18 to 74: Economically )(?:active: )?(.+)\",\"Students: \\\\1\",x), inplace=True)\n",
    "quals_01.rename(columns={\n",
    "    'mnemonic':'lsoacd', \n",
    "    'super output areas - lower layer':'LSOANM', \n",
    "    'All people aged 16-74':'Total'}, inplace=True)\n",
    "\n",
    "# Select only those rows that are in the London 2001 LSOA list\n",
    "quals_01 = quals_01.loc[quals_01.lsoacd.isin(ldn2004.lsoacd.values)]\n",
    "\n",
    "# Drop the columns we're not interested in\n",
    "quals_01.drop('LSOANM', axis=1, inplace=True)\n",
    "\n",
    "quals_01.to_csv(os.path.join(src,\"Qualifications-2001.csv\"), index=False, header=True, encoding='utf-8')\n",
    "\n",
    "# Sanity check\n",
    "print(\"Wrote \" + str(quals_01.shape[0]) + \" rows to output file.\")\n",
    "\n",
    "# convert_to_2011(df,src,dest,nm)\n",
    "convert_to_2011(quals_01, src, work, 'Qualifications-2001.csv')\n",
    "\n",
    "# Sanity check\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2011 Data\n",
    "\n",
    "Note that we don't make use of the 'Apprenticeship' column as it has no equivalent in the 2001 data and we need a comparable base."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(\"Processing 2011 Qualifications data from Nomis Table KS501EW...\")\n",
    "print(\"Note: this does not need to be converted.\")\n",
    "\n",
    "# Load the data from the KS501EW table\n",
    "quals_11 = pd.read_csv(os.path.join(src,\"2011\",\"ks501ew.csv.gz\"),\n",
    "                      header=6, skip_blank_lines=True, compression='gzip')\n",
    "\n",
    "# Rename the columns to something easier to work with\n",
    "quals_11.rename(columns=lambda x: re.sub(\"(?:Highest level of qualification: )(.+) qualifications\",\"\\\\1\",x), inplace=True)\n",
    "quals_11.rename(columns=lambda x: re.sub(\"(?:Full-time students: Age 18 to 74: Economically )(?:active: )?(.+)\",\"Students: \\\\1\",x), inplace=True)\n",
    "quals_11.rename(columns={'mnemonic':'lsoacd', '2011 super output area - lower layer':'LSOANM', 'All categories: Highest level of qualification':'Total'}, inplace=True)\n",
    "\n",
    "# Select only those rows that are in the London 2011 LSOA list\n",
    "quals_11 = quals_11.loc[quals_11.lsoacd.isin(ldn2011.lsoacd.values)]\n",
    "\n",
    "# Drop the columns we're not interested in -- although it\n",
    "# would be nice to keep the Apprenticeship data we can't\n",
    "# seemingly compare it to the 2001 data. As far as I can tell\n",
    "# this is because the question was new in 2011, so presumably\n",
    "# respondents in 2001 would have been folded into one of the \n",
    "# 'lower' qualifications brackets. For a brief analysis, see\n",
    "# https://www.ons.gov.uk/employmentandlabourmarket/peopleinwork/employmentandemployeetypes/articles/qualificationsandlabourmarketparticipationinenglandandwales/2014-06-18\n",
    "quals_11.drop(['LSOANM','Highest level of qualification: Apprenticeship'], axis=1, inplace=True)\n",
    "\n",
    "quals_11.to_csv(os.path.join(work,\"Qualifications-2011.csv\"), index=False, header=True, encoding='utf-8')\n",
    "\n",
    "# Sanity check\n",
    "print(\"Wrote \" + str(quals_11.shape[0]) + \" rows to output file.\")\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Rents\n",
    "\n",
    "There is some generic data on rents that might be useful, but unfortunately no one seems to have statistics as far back as 2001 -- the earliest I could find dated to 2014 and the VOA specifically recommends against trying to compare across years with much of their data:\n",
    "\n",
    "- [VOA Home Page @ National Archives](http://webarchive.nationalarchives.gov.uk/20141002130950/http://www.voa.gov.uk/corporate/index.html)\n",
    "- [Private Rental Market Stats @ National Archives](http://webarchive.nationalarchives.gov.uk/20141002135606/http://www.voa.gov.uk/corporate/statisticalReleases/110929_PrivateResidentialRentalMarketStatistics.html)\n",
    "- [General VOA Stats Page @ National Archives](http://webarchive.nationalarchives.gov.uk/20141002132258/http://www.voa.gov.uk/corporate/publications/statistics.html)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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
}