05-Processing of Modelling Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Processing of Inputs to Model\n",
    "\n",
    "The code in this section is concerned with pre-processing the predictor data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "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 os\n",
    "import re\n",
    "\n",
    "lkp        = os.path.join('data','lkp')\n",
    "canonical  = os.path.join('data','canonical')\n",
    "analytical = os.path.join('data','analytical')\n",
    "travel     = os.path.join(canonical,'travel')\n",
    "household  = os.path.join(canonical,'households')\n",
    "housing    = os.path.join(canonical,'housing')\n",
    "greenspace = os.path.join(canonical,'greenspace')\n",
    "dwellings  = os.path.join(canonical,'dwellings')\n",
    "incomes    = os.path.join(canonical,'incomes')\n",
    "work       = os.path.join(canonical,'work')\n",
    "scores     = os.path.join(canonical,'scores')\n",
    "\n",
    "for d in [lkp,analytical,canonical,greenspace,dwellings,travel,household,housing,work,scores]:\n",
    "    if not os.path.exists(d):\n",
    "        os.makedirs(d)"
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "## Selecting Variables for Inclusion\n",
    "\n",
    "Offers a fairly simple way to group variables together into classes (households, housing, etc.) and include/exclude them from the analysis. \n",
    "\n",
    "<span style=\"color:red;font-weight:bolder\">Note that the four variables used in the scoring process are automatically included in the next notebook.</span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sources = {\n",
    "    'households' : {\n",
    "        'age_struct' :'Age Structure',\n",
    "        'birth_ctry' :'Country of Birth',\n",
    "        'dep_child'  :'Dependent Children',\n",
    "        'ethnic'     :'Ethnicity',\n",
    "        'gen_hlth'   :'General Health',\n",
    "        'hh_comp'    :'Household Composition',\n",
    "        'mrtl_sts'   :'Marital Status',\n",
    "        'rel'        :'Religion'\n",
    "    },\n",
    "    'housing' : {\n",
    "    #    'price'      :'Values', # Included in next notebook\n",
    "        'hsng_tnr'   :'Tenure',\n",
    "        'density'    :'Density',\n",
    "    },\n",
    "    'work' : {\n",
    "        'nssec'      :'NS-Sec',\n",
    "        'hrs_wrkd'   :'Hours Worked',\n",
    "        'industry'   :'Industry',\n",
    "        'active'     :'Economic Activity'\n",
    "    #    'occ'        :'Occupations', # Included in next notebook\n",
    "    #    'quals'      :'Qualifications', # Included in next notebook\n",
    "    #    'inc'        :'Income' # Included in next notebook\n",
    "    },\n",
    "    'travel' : {\n",
    "        'crs_vns'    :'Cars and Vans',\n",
    "        'mode'       :'TTW',\n",
    "        'dist_to_dt' :'Travel Time To Bank',\n",
    "        'zone_data'  :'Fare Zone'\n",
    "    },\n",
    "    'dwellings' : {\n",
    "        'acc_type'   :'Type',\n",
    "        'dwelling_age_data':'Age'\n",
    "    },\n",
    "    'greenspace' : {\n",
    "        'os_data'    :'Share',\n",
    "        'os_acc_data':'Access'\n",
    "    }\n",
    "}\n",
    "\n",
    "# Variables below will not be included in the \n",
    "# 'convert to percentages' process defined in the\n",
    "# function below. Including this here as _must_ \n",
    "# be configured together with DoD above.\n",
    "not_to_pct = [\n",
    "    'density',\n",
    "    'dist_to_dt',\n",
    "    'zone_data',\n",
    "    'os_data',\n",
    "    'os_acc_data',\n",
    "    'trans',\n",
    "    'inc',\n",
    "    'price',\n",
    "    'scores'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Convert raw data in datasets to percentages --\n",
    "# need for some variables, but not all. \n",
    "def to_share(df):\n",
    "    \n",
    "    df.rename(columns={'TOTAL':'total','Total':'total'}, inplace=True)\n",
    "    \n",
    "    if 'total' not in df.columns.values: \n",
    "        df.loc[:,'total'] = df.sum(axis=1)  #  Total number of people\n",
    "    \n",
    "    pred_data = pd.DataFrame()\n",
    "    \n",
    "    for n in df.columns.tolist():\n",
    "        #print(\"Converting \" + n)\n",
    "        pred_data.loc[:,n] = (df.loc[:,n] / df.loc[:,'total'])\n",
    "    \n",
    "    pred_data.drop(['total'], axis=1, inplace=True)\n",
    "    \n",
    "    pred_data.describe()\n",
    "    \n",
    "    return pred_data\n",
    "\n",
    "# Load data sets for each Census year based on schema above\n",
    "def load_data(year, template):\n",
    "    datasets = {}\n",
    "    for group in template.keys():\n",
    "        print(\"Dataset group: \" + group)\n",
    "        for ds in template[group].keys():\n",
    "            print(\"\\tLoading dataset: \" + ds)\n",
    "            \n",
    "            # Tentative path\n",
    "            tpath = os.path.join('data','canonical',group,template[group][ds])\n",
    "            \n",
    "            #print(\"\\t\\tChecking for: \" + \"-\".join([tpath,str(year)])+\".csv\")\n",
    "            \n",
    "            # Load the data set\n",
    "            if os.path.isfile(\"-\".join([tpath,str(year)])+\".csv\"):\n",
    "                print(\"\\t\\tFound: \" + \"-\".join([tpath,str(year)])+\".csv\")\n",
    "                datasets[ds] = pd.read_csv(\"-\".join([tpath,str(year)])+\".csv\")\n",
    "                \n",
    "            elif os.path.isfile(tpath+\".csv\"):\n",
    "                print(\"\\t\\tFound: \" + tpath+\".csv\")\n",
    "                datasets[ds] = pd.read_csv(tpath+\".csv\")\n",
    "                \n",
    "            else:\n",
    "                print(\"==> Couldn't find data for: \" + template[group][ds] + \" <==\")\n",
    "                print(\"Tried: \" + \"-\".join([tpath,str(year)]) + \"; \" + tpath+\".csv\")\n",
    "            \n",
    "            if datasets[ds].index.name != 'lsoacd':\n",
    "                datasets[ds].set_index('lsoacd', inplace=True) #  predictor variables only\n",
    "            \n",
    "            if ds not in not_to_pct:\n",
    "                datasets[ds] = to_share(datasets[ds])\n",
    "            else:\n",
    "                print(\"\\t\\tNot converting to percent.\")\n",
    "                \n",
    "    return datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(\"Loading 2001 datasets...\")\n",
    "datasets_01 = load_data(2001, sources)\n",
    "\n",
    "print(\"Loading 2011 datasets...\")\n",
    "datasets_11 = load_data(2011, sources)\n",
    "\n",
    "sets = {\n",
    "    '01':  datasets_01,\n",
    "    '11':  datasets_11\n",
    "}\n",
    "\n",
    "print(\"Done.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "main_datasets_dict = dict()\n",
    "\n",
    "for year, dataset in sets.items():\n",
    "    #  Create combined dataset\n",
    "    main_dataset = pd.DataFrame(index=sets['01']['acc_type'].index) # Initialise the df\n",
    "    for key, value in iter(sorted(dataset.items())):\n",
    "        if key is not 'scores':\n",
    "            print(\"Merging \" + key + \" on to dataset.\")\n",
    "            main_dataset = main_dataset.merge(value, left_index=True, right_index=True, how='left')\n",
    "    \n",
    "    main_datasets_dict[year] = main_dataset\n",
    "    \n",
    "    #  Check for missing values\n",
    "    print(\"Missing values (if any) to be filled:\")\n",
    "    for c in main_dataset.columns[main_dataset.isnull().any()]:\n",
    "        print(\"\\t\" + c + \" has \" + str(main_dataset[c].isnull().sum()) + \" null values.\")\n",
    "        print(\"\\t\\t\" + \", \".join(main_dataset[main_dataset[c].isnull()].index.values))\n",
    "    \n",
    "    main_dataset.fillna(0, inplace=True)\n",
    "    \n",
    "    print(\"Main dataset built for 20\" + year + \".\")\n",
    "    print(\" \")\n",
    "\n",
    "main_datasets_dict['01'].rename(columns=lambda x: re.sub(' 2001$','',x), inplace=True)\n",
    "main_datasets_dict['11'].rename(columns=lambda x: re.sub(' 2011$','',x), inplace=True)\n",
    "\n",
    "print(\"2001 Shape: \" + str(main_datasets_dict['01'].shape))\n",
    "print(\"2011 Shape: \" + str(main_datasets_dict['11'].shape))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sanity Check\n",
    "\n",
    "We should have no difference (an empty result set) at this point since the data sets should have been fully aligned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "s11 = set(main_datasets_dict['11'].columns)\n",
    "s01 = set(main_datasets_dict['01'].columns)\n",
    "print(\"2011 variables diff'd against 2001 variables: \" + str(s11.difference(s01)))\n",
    "print(\"2001 variables diff'd against 2011 variables: \" + str(s01.difference(s11)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "main_datasets_dict['11'].sample(3, random_state=r_state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# This may take some time as there is quite a bit of data!\n",
    "for key, value in main_datasets_dict.items():\n",
    "    print(\"Saving data for 20\" + key)\n",
    "    value.to_csv(os.path.join(analytical,'Predictor-20'+key+'-Data.csv.gz'), compression='gzip', index=True)\n",
    "\n",
    "print(\"Done.\")"
   ]
  }
 ],
 "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
}