Project - LA Dataset Creation .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "10df8a85",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.chdir('D:\\Birkbeck\\MSc Data Science\\Project\\Data sets\\Selected datasets')\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import glob\n",
    "\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.width', None)\n",
    "pd.set_option('display.max_colwidth', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d236f89f",
   "metadata": {},
   "source": [
    "### Creating Dataset with 'Static' values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0c2b4d8",
   "metadata": {},
   "source": [
    "### Size of Area in Hectares & Size of Green Belt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fdab5502",
   "metadata": {},
   "outputs": [],
   "source": [
    "area = pd.read_csv('live table green belt and total area.csv', header = 3)\n",
    "#area.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6139a190",
   "metadata": {},
   "outputs": [],
   "source": [
    "area = area.drop(columns = ['Unnamed: 5',\n",
    "                            'Unnamed: 6', \n",
    "                            'Unnamed: 7',\n",
    "                            'Unnamed: 8',\n",
    "                            'Percentage of total land area designated as Green Belt'])\n",
    "area = area[area['ONS code'].str.contains('E0') == True]\n",
    "\n",
    "area = area.rename(columns = {'ONS code': 'la_code',\n",
    "                             'Local authority': 'la_name',\n",
    "                             'Area of land designated as Green Belt': 'g_belt_area',\n",
    "                             'Total area as at 31 December 2020 2': 'total_area'})\n",
    "area['g_belt_area'] = area['g_belt_area'].replace({'-':0})\n",
    "\n",
    "area['g_belt_area'] = area['g_belt_area'].replace({',': ''}, regex=True).astype('int64')\n",
    "area['total_area'] = area['total_area'].replace({',': ''}, regex=True).astype('int64')\n",
    "\n",
    "# area.info()\n",
    "# area.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d889dc5b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>la_code</th>\n",
       "      <th>la_name</th>\n",
       "      <th>g_belt_area</th>\n",
       "      <th>total_area</th>\n",
       "      <th>year</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E07000223</td>\n",
       "      <td>Adur</td>\n",
       "      <td>0</td>\n",
       "      <td>4210</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E07000026</td>\n",
       "      <td>Allerdale</td>\n",
       "      <td>0</td>\n",
       "      <td>125830</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E07000032</td>\n",
       "      <td>Amber Valley</td>\n",
       "      <td>8650</td>\n",
       "      <td>26540</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E07000224</td>\n",
       "      <td>Arun</td>\n",
       "      <td>0</td>\n",
       "      <td>22100</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E07000170</td>\n",
       "      <td>Ashfield</td>\n",
       "      <td>4520</td>\n",
       "      <td>10960</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>E07000105</td>\n",
       "      <td>Ashford</td>\n",
       "      <td>0</td>\n",
       "      <td>58060</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>E07000200</td>\n",
       "      <td>Babergh</td>\n",
       "      <td>0</td>\n",
       "      <td>59510</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>E09000002</td>\n",
       "      <td>Barking and Dagenham</td>\n",
       "      <td>530</td>\n",
       "      <td>3610</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>E09000003</td>\n",
       "      <td>Barnet</td>\n",
       "      <td>2380</td>\n",
       "      <td>8680</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>E08000016</td>\n",
       "      <td>Barnsley</td>\n",
       "      <td>22410</td>\n",
       "      <td>32910</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>2001</td>\n",
       "      <td>2002</td>\n",
       "      <td>2003</td>\n",
       "      <td>2004</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2019</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     la_code               la_name  g_belt_area  total_area  year  2000  2001  \\\n",
       "0  E07000223                  Adur            0        4210  2000  2000  2001   \n",
       "1  E07000026             Allerdale            0      125830  2000  2000  2001   \n",
       "2  E07000032          Amber Valley         8650       26540  2000  2000  2001   \n",
       "3  E07000224                  Arun            0       22100  2000  2000  2001   \n",
       "4  E07000170              Ashfield         4520       10960  2000  2000  2001   \n",
       "5  E07000105               Ashford            0       58060  2000  2000  2001   \n",
       "6  E07000200               Babergh            0       59510  2000  2000  2001   \n",
       "7  E09000002  Barking and Dagenham          530        3610  2000  2000  2001   \n",
       "8  E09000003                Barnet         2380        8680  2000  2000  2001   \n",
       "9  E08000016              Barnsley        22410       32910  2000  2000  2001   \n",
       "\n",
       "   2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  \\\n",
       "0  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "1  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "2  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "3  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "4  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "5  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "6  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "7  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "8  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "9  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013   \n",
       "\n",
       "   2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "0  2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "1  2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "2  2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "3  2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "4  2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "5  2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "6  2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "7  2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "8  2014  2015  2016  2017  2018  2019  2020  2021  \n",
       "9  2014  2015  2016  2017  2018  2019  2020  2021  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# need to add years to be the same for all\n",
    "for year in range(2000,2022):\n",
    "    area[year] = year\n",
    "\n",
    "area.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2f12eb30",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Michelle Bark\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3437: FutureWarning: This dataframe has a column name that matches the 'value_name' column name of the resulting Dataframe. In the future this will raise an error, please set the 'value_name' parameter of DataFrame.melt to a unique name.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>la_code</th>\n",
       "      <th>la_name</th>\n",
       "      <th>g_belt_area</th>\n",
       "      <th>total_area</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E07000223</td>\n",
       "      <td>Adur</td>\n",
       "      <td>0</td>\n",
       "      <td>4210</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E07000026</td>\n",
       "      <td>Allerdale</td>\n",
       "      <td>0</td>\n",
       "      <td>125830</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E07000032</td>\n",
       "      <td>Amber Valley</td>\n",
       "      <td>8650</td>\n",
       "      <td>26540</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E07000224</td>\n",
       "      <td>Arun</td>\n",
       "      <td>0</td>\n",
       "      <td>22100</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E07000170</td>\n",
       "      <td>Ashfield</td>\n",
       "      <td>4520</td>\n",
       "      <td>10960</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     la_code       la_name  g_belt_area  total_area  year\n",
       "0  E07000223          Adur            0        4210  2000\n",
       "1  E07000026     Allerdale            0      125830  2000\n",
       "2  E07000032  Amber Valley         8650       26540  2000\n",
       "3  E07000224          Arun            0       22100  2000\n",
       "4  E07000170      Ashfield         4520       10960  2000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "area = pd.melt(area, id_vars = ['la_code', 'la_name', 'g_belt_area','total_area'], value_name = 'year')\n",
    "area = area.drop(columns = 'variable')\n",
    "area['la_name'] = area['la_name'].str.strip()\n",
    "area['la_code'] = area['la_code'].str.strip()\n",
    "area.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5bff4654",
   "metadata": {},
   "outputs": [],
   "source": [
    "move_year = area.pop('year')\n",
    "area.insert(2,'year',move_year)\n",
    "#area.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5c376f45",
   "metadata": {},
   "outputs": [],
   "source": [
    "la_file = area"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4352380a",
   "metadata": {},
   "source": [
    "### Adding RPI by year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0c18e294",
   "metadata": {},
   "outputs": [],
   "source": [
    "rpi = pd.read_csv('annual all items rpi.csv', header = 7)\n",
    "#rpi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "05957e8a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#only need from 2000 to 2021\n",
    "rpi = rpi.drop(rpi.index[35:])\n",
    "rpi = rpi.drop(rpi.index[:13])\n",
    "rpi = rpi.rename(columns ={'Important notes': 'year','Unnamed: 1': 'rpi'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "74689469",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create list of codes and names to be able to add\n",
    "code_lst = la_file['la_code'].unique().tolist()\n",
    "name_lst = la_file['la_name'].unique().tolist()\n",
    "#print(code_lst)\n",
    "#print(name_lst)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d4a3bec",
   "metadata": {},
   "outputs": [],
   "source": [
    "count = 0\n",
    "for code in code_lst:\n",
    "    ref = name_lst[count]\n",
    "    rpi[ref] = code\n",
    "    count+=1\n",
    "rpi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "94bb9c5c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6908 entries, 0 to 6907\n",
      "Data columns (total 4 columns):\n",
      " #   Column   Non-Null Count  Dtype  \n",
      "---  ------   --------------  -----  \n",
      " 0   year     6908 non-null   int64  \n",
      " 1   rpi      6908 non-null   float64\n",
      " 2   la_name  6908 non-null   object \n",
      " 3   la_code  6908 non-null   object \n",
      "dtypes: float64(1), int64(1), object(2)\n",
      "memory usage: 216.0+ KB\n"
     ]
    }
   ],
   "source": [
    "rpi = pd.melt(rpi, id_vars = ['year', 'rpi'], var_name = 'la_name', value_name = 'la_code')\n",
    "rpi['year'] = rpi['year'].astype('int64')\n",
    "rpi['rpi'] = rpi['rpi'].astype('float')\n",
    "rpi.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "09d20416",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>rpi</th>\n",
       "      <th>la_name</th>\n",
       "      <th>la_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000</td>\n",
       "      <td>170.3</td>\n",
       "      <td>Adur</td>\n",
       "      <td>E07000223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001</td>\n",
       "      <td>173.3</td>\n",
       "      <td>Adur</td>\n",
       "      <td>E07000223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2002</td>\n",
       "      <td>176.2</td>\n",
       "      <td>Adur</td>\n",
       "      <td>E07000223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2003</td>\n",
       "      <td>181.3</td>\n",
       "      <td>Adur</td>\n",
       "      <td>E07000223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>186.7</td>\n",
       "      <td>Adur</td>\n",
       "      <td>E07000223</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year    rpi la_name    la_code\n",
       "0  2000  170.3    Adur  E07000223\n",
       "1  2001  173.3    Adur  E07000223\n",
       "2  2002  176.2    Adur  E07000223\n",
       "3  2003  181.3    Adur  E07000223\n",
       "4  2004  186.7    Adur  E07000223"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rpi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "18f97512",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 6908 entries, 0 to 6907\n",
      "Data columns (total 6 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   la_code      6908 non-null   object \n",
      " 1   la_name      6908 non-null   object \n",
      " 2   year         6908 non-null   int64  \n",
      " 3   g_belt_area  6908 non-null   int64  \n",
      " 4   total_area   6908 non-null   int64  \n",
      " 5   rpi          6908 non-null   float64\n",
      "dtypes: float64(1), int64(3), object(2)\n",
      "memory usage: 377.8+ KB\n"
     ]
    }
   ],
   "source": [
    "la_file = la_file.merge(rpi)\n",
    "la_file.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a9b7355",
   "metadata": {},
   "source": [
    "### Adding BoE Base rate/Interest Rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c6de678d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Uses file created in england dataset with all years min/max\n",
    "int_rate = pd.read_csv('pd_int_rate.csv')\n",
    "int_rate = int_rate.drop(columns = 'Unnamed: 0')\n",
    "#int_rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "936620ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# now add area names and codes for each to add to static info\n",
    "count = 0\n",
    "for code in code_lst:\n",
    "    ref = name_lst[count]\n",
    "    int_rate[ref] = code\n",
    "    count+=1\n",
    "    \n",
    "int_rate = pd.melt(int_rate, id_vars = ['year', 'max', 'min'], var_name = 'la_name', value_name = 'la_code')\n",
    "int_rate = int_rate.rename(columns = {'max': 'int_rate_max',\n",
    "                                     'min': 'int_rate_min'})\n",
    "#int_rate.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "27814b99",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 6908 entries, 0 to 6907\n",
      "Data columns (total 8 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   la_code       6908 non-null   object \n",
      " 1   la_name       6908 non-null   object \n",
      " 2   year          6908 non-null   int64  \n",
      " 3   g_belt_area   6908 non-null   int64  \n",
      " 4   total_area    6908 non-null   int64  \n",
      " 5   rpi           6908 non-null   float64\n",
      " 6   int_rate_max  6908 non-null   float64\n",
      " 7   int_rate_min  6908 non-null   float64\n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 485.7+ KB\n"
     ]
    }
   ],
   "source": [
    "la_file = la_file.merge(int_rate)\n",
    "la_file.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42432fc6",
   "metadata": {},
   "source": [
    "### Adding: Median Income"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "68fa81f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region code</th>\n",
       "      <th>Region name</th>\n",
       "      <th>Code</th>\n",
       "      <th>Name</th>\n",
       "      <th>1997</th>\n",
       "      <th>1998</th>\n",
       "      <th>1999</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>15,747</td>\n",
       "      <td>16,149</td>\n",
       "      <td>16,121</td>\n",
       "      <td>15,743</td>\n",
       "      <td>17,264</td>\n",
       "      <td>18,251</td>\n",
       "      <td>17,153</td>\n",
       "      <td>17,258</td>\n",
       "      <td>20,850</td>\n",
       "      <td>20,478</td>\n",
       "      <td>19,913</td>\n",
       "      <td>21,047</td>\n",
       "      <td>21,298</td>\n",
       "      <td>23,762</td>\n",
       "      <td>23,430</td>\n",
       "      <td>24,741</td>\n",
       "      <td>27,220</td>\n",
       "      <td>25,143</td>\n",
       "      <td>26,445</td>\n",
       "      <td>26,112</td>\n",
       "      <td>25,638</td>\n",
       "      <td>25,729</td>\n",
       "      <td>26,010</td>\n",
       "      <td>28,093</td>\n",
       "      <td>28,378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>15,914</td>\n",
       "      <td>15,319</td>\n",
       "      <td>16,073</td>\n",
       "      <td>16,513</td>\n",
       "      <td>17,273</td>\n",
       "      <td>19,252</td>\n",
       "      <td>17,999</td>\n",
       "      <td>18,635</td>\n",
       "      <td>18,706</td>\n",
       "      <td>20,189</td>\n",
       "      <td>20,495</td>\n",
       "      <td>21,301</td>\n",
       "      <td>22,593</td>\n",
       "      <td>21,628</td>\n",
       "      <td>21,932</td>\n",
       "      <td>24,669</td>\n",
       "      <td>24,133</td>\n",
       "      <td>23,971</td>\n",
       "      <td>24,751</td>\n",
       "      <td>25,722</td>\n",
       "      <td>25,626</td>\n",
       "      <td>25,170</td>\n",
       "      <td>26,203</td>\n",
       "      <td>27,005</td>\n",
       "      <td>25,968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000003</td>\n",
       "      <td>Redcar and Cleveland</td>\n",
       "      <td>19,206</td>\n",
       "      <td>18,747</td>\n",
       "      <td>21,274</td>\n",
       "      <td>22,135</td>\n",
       "      <td>21,763</td>\n",
       "      <td>20,806</td>\n",
       "      <td>20,554</td>\n",
       "      <td>23,903</td>\n",
       "      <td>25,049</td>\n",
       "      <td>22,897</td>\n",
       "      <td>24,176</td>\n",
       "      <td>25,340</td>\n",
       "      <td>25,415</td>\n",
       "      <td>24,156</td>\n",
       "      <td>25,412</td>\n",
       "      <td>23,684</td>\n",
       "      <td>25,295</td>\n",
       "      <td>26,323</td>\n",
       "      <td>26,792</td>\n",
       "      <td>24,496</td>\n",
       "      <td>25,456</td>\n",
       "      <td>26,170</td>\n",
       "      <td>25,846</td>\n",
       "      <td>26,220</td>\n",
       "      <td>24,975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000004</td>\n",
       "      <td>Stockton-on-Tees</td>\n",
       "      <td>16,838</td>\n",
       "      <td>17,062</td>\n",
       "      <td>17,199</td>\n",
       "      <td>18,147</td>\n",
       "      <td>19,186</td>\n",
       "      <td>18,392</td>\n",
       "      <td>18,656</td>\n",
       "      <td>20,660</td>\n",
       "      <td>21,390</td>\n",
       "      <td>21,684</td>\n",
       "      <td>22,766</td>\n",
       "      <td>22,859</td>\n",
       "      <td>24,156</td>\n",
       "      <td>23,563</td>\n",
       "      <td>23,829</td>\n",
       "      <td>23,408</td>\n",
       "      <td>24,141</td>\n",
       "      <td>25,096</td>\n",
       "      <td>25,345</td>\n",
       "      <td>26,884</td>\n",
       "      <td>25,258</td>\n",
       "      <td>26,540</td>\n",
       "      <td>29,049</td>\n",
       "      <td>30,531</td>\n",
       "      <td>28,609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000005</td>\n",
       "      <td>Darlington</td>\n",
       "      <td>15,424</td>\n",
       "      <td>14,453</td>\n",
       "      <td>15,915</td>\n",
       "      <td>16,043</td>\n",
       "      <td>17,220</td>\n",
       "      <td>18,568</td>\n",
       "      <td>18,015</td>\n",
       "      <td>18,264</td>\n",
       "      <td>18,504</td>\n",
       "      <td>19,913</td>\n",
       "      <td>22,633</td>\n",
       "      <td>22,556</td>\n",
       "      <td>22,630</td>\n",
       "      <td>22,259</td>\n",
       "      <td>23,363</td>\n",
       "      <td>24,149</td>\n",
       "      <td>21,288</td>\n",
       "      <td>25,502</td>\n",
       "      <td>26,570</td>\n",
       "      <td>27,009</td>\n",
       "      <td>25,712</td>\n",
       "      <td>26,043</td>\n",
       "      <td>27,888</td>\n",
       "      <td>28,045</td>\n",
       "      <td>28,323</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region code Region name       Code                  Name    1997    1998  \\\n",
       "0   E12000001  North East  E06000001            Hartlepool  15,747  16,149   \n",
       "1   E12000001  North East  E06000002         Middlesbrough  15,914  15,319   \n",
       "2   E12000001  North East  E06000003  Redcar and Cleveland  19,206  18,747   \n",
       "3   E12000001  North East  E06000004      Stockton-on-Tees  16,838  17,062   \n",
       "4   E12000001  North East  E06000005            Darlington  15,424  14,453   \n",
       "\n",
       "     1999    2000    2001    2002    2003    2004    2005    2006    2007  \\\n",
       "0  16,121  15,743  17,264  18,251  17,153  17,258  20,850  20,478  19,913   \n",
       "1  16,073  16,513  17,273  19,252  17,999  18,635  18,706  20,189  20,495   \n",
       "2  21,274  22,135  21,763  20,806  20,554  23,903  25,049  22,897  24,176   \n",
       "3  17,199  18,147  19,186  18,392  18,656  20,660  21,390  21,684  22,766   \n",
       "4  15,915  16,043  17,220  18,568  18,015  18,264  18,504  19,913  22,633   \n",
       "\n",
       "     2008    2009    2010    2011    2012    2013    2014    2015    2016  \\\n",
       "0  21,047  21,298  23,762  23,430  24,741  27,220  25,143  26,445  26,112   \n",
       "1  21,301  22,593  21,628  21,932  24,669  24,133  23,971  24,751  25,722   \n",
       "2  25,340  25,415  24,156  25,412  23,684  25,295  26,323  26,792  24,496   \n",
       "3  22,859  24,156  23,563  23,829  23,408  24,141  25,096  25,345  26,884   \n",
       "4  22,556  22,630  22,259  23,363  24,149  21,288  25,502  26,570  27,009   \n",
       "\n",
       "     2017    2018    2019    2020    2021  \n",
       "0  25,638  25,729  26,010  28,093  28,378  \n",
       "1  25,626  25,170  26,203  27,005  25,968  \n",
       "2  25,456  26,170  25,846  26,220  24,975  \n",
       "3  25,258  26,540  29,049  30,531  28,609  \n",
       "4  25,712  26,043  27,888  28,045  28,323  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "med_income = pd.read_csv('5b Median Income by LA.csv', header = 1)\n",
    "med_income.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5ff57fa5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>la_code</th>\n",
       "      <th>la_name</th>\n",
       "      <th>year</th>\n",
       "      <th>median_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>1997</td>\n",
       "      <td>15747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>1997</td>\n",
       "      <td>15914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E06000003</td>\n",
       "      <td>Redcar and Cleveland</td>\n",
       "      <td>1997</td>\n",
       "      <td>19206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000004</td>\n",
       "      <td>Stockton-on-Tees</td>\n",
       "      <td>1997</td>\n",
       "      <td>16838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000005</td>\n",
       "      <td>Darlington</td>\n",
       "      <td>1997</td>\n",
       "      <td>15424</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     la_code               la_name  year  median_income\n",
       "0  E06000001            Hartlepool  1997          15747\n",
       "1  E06000002         Middlesbrough  1997          15914\n",
       "2  E06000003  Redcar and Cleveland  1997          19206\n",
       "3  E06000004      Stockton-on-Tees  1997          16838\n",
       "4  E06000005            Darlington  1997          15424"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "med_income = med_income.drop(columns = ['Region code', 'Region name'])\n",
    "med_income = med_income.rename(columns = {'Code': 'la_code', 'Name':'la_name'})\n",
    "med_income = pd.melt(med_income, id_vars= ['la_code','la_name'], var_name = \"year\",value_name = 'median_income')\n",
    "med_income = med_income.replace(\":\", np.nan)\n",
    "med_income.dropna(inplace =True)\n",
    "med_income['median_income']  = med_income['median_income'].replace({',': ''}, regex=True).astype('int64')\n",
    "med_income['la_name'] = med_income['la_name'].str.strip()\n",
    "med_income['la_code'] = med_income['la_code'].str.strip()\n",
    "med_income['year'] = med_income['year'].astype('int64')\n",
    "med_income.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3fc54cd7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 6657 entries, 0 to 6656\n",
      "Data columns (total 9 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   la_code        6657 non-null   object \n",
      " 1   la_name        6657 non-null   object \n",
      " 2   year           6657 non-null   int64  \n",
      " 3   g_belt_area    6657 non-null   int64  \n",
      " 4   total_area     6657 non-null   int64  \n",
      " 5   rpi            6657 non-null   float64\n",
      " 6   int_rate_max   6657 non-null   float64\n",
      " 7   int_rate_min   6657 non-null   float64\n",
      " 8   median_income  6657 non-null   int64  \n",
      "dtypes: float64(3), int64(4), object(2)\n",
      "memory usage: 520.1+ KB\n"
     ]
    }
   ],
   "source": [
    "la_file = la_file.merge(med_income)\n",
    "la_file.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbeda512",
   "metadata": {},
   "source": [
    "### Functions for processing HPSSA files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fa0ee51e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#removing unwanted columns and renaming september data to only the year.\n",
    "def rename_cols(x):\n",
    "    if 'Year ending Dec' in x or 'Year ending Mar' in x or 'Year ending Jun' in x:\n",
    "        return 'Remove'\n",
    "    if 'Year ending Sep' in x:\n",
    "        return x.split(' ')[3]\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "b54cd8c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def file_processing(file,prop_type, measure):\n",
    "    create_file = pd.read_csv(file, header = 6)\n",
    "    create_file = create_file.drop(columns = ['Region/Country code', 'Region/Country name'])\n",
    "    create_file = create_file.rename(columns = \n",
    "                                   {'Local authority code ': 'la_code', \n",
    "                                    'Local authority name':'la_name'})\n",
    "    create_file = create_file.rename(columns = rename_cols).drop(columns = 'Remove').dropna()\n",
    "    col_header = prop_type+'_'+measure\n",
    "    create_file = pd.melt(create_file, id_vars= ['la_code', 'la_name'],\n",
    "       var_name = \"year\",value_name = col_header )\n",
    "    \n",
    "    create_file = create_file.replace(\":\", np.nan)\n",
    "    create_file.dropna(inplace =True)\n",
    "    \n",
    "\n",
    "    create_file[col_header]  = create_file[col_header].replace({',': ''}, regex=True).astype('int64')\n",
    "    create_file['year'] = create_file['year'].astype('int64')\n",
    "    create_file['la_name'] = create_file['la_name'].str.strip()\n",
    "    create_file['la_code'] = create_file['la_code'].str.strip()\n",
    "    return create_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "2663ebaf",
   "metadata": {},
   "outputs": [],
   "source": [
    "def merge_set(main_data_file,df_list):\n",
    "    new_a = pd.merge(df_list[0],df_list[1])\n",
    "    new_b = pd.merge(df_list[2],df_list[3])\n",
    "    new_c = pd.merge(new_a, new_b)\n",
    "    main_data_file = main_data_file.merge(new_c)\n",
    "    return main_data_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "fb54c985",
   "metadata": {},
   "outputs": [],
   "source": [
    "med_new_build_all = file_processing('hpssa 10 2a median new build prices.csv', 'all', 'new_sls_price')\n",
    "med_new_build_det = file_processing('hpssa 10 2b median new build detached prices.csv', 'det', 'new_sls_price')\n",
    "med_new_build_semi = file_processing('hpssa 10 2c median new build semi prices.csv','semi', 'new_sls_price')\n",
    "med_new_build_terr = file_processing('hpssa 10 2d median new build terrace prices.csv','terr', 'new_sls_price')\n",
    "med_new_build_flat = file_processing('hpssa 10 2e median new build flat prices.csv', 'flat','new_sls_price')\n",
    "\n",
    "la_file = la_file.merge(med_new_build_all)\n",
    "la_file = merge_set(la_file,[med_new_build_det, med_new_build_semi, med_new_build_terr, med_new_build_flat])\n",
    "\n",
    "#la_file.head()                                "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9694c7e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "med_exist_all = file_processing('hpssa 11 2a median price exisiting.csv', 'all', 'exist_sls_price')\n",
    "med_exist_det = file_processing('hpssa 11 2b median price exisiting detached.csv', 'det', 'exist_sls_price')\n",
    "med_exist_semi = file_processing('hpssa 11 2c median price exisiting semi.csv','semi', 'exist_sls_price')\n",
    "med_exist_terr = file_processing('hpssa 11 2d median price exisiting terrace.csv','terr', 'exist_sls_price')\n",
    "med_exist_flat = file_processing('hpssa 11 2e median price exisiting flat.csv', 'flat','exist_sls_price')\n",
    "\n",
    "la_file  = la_file .merge(med_exist_all)\n",
    "la_file = merge_set(la_file ,[med_exist_det, med_exist_semi, med_exist_terr, med_exist_flat])\n",
    "#la_file.head()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "32fb8f6d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4902 entries, 0 to 4901\n",
      "Data columns (total 19 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   la_code               4902 non-null   object \n",
      " 1   la_name               4902 non-null   object \n",
      " 2   year                  4902 non-null   int64  \n",
      " 3   g_belt_area           4902 non-null   int64  \n",
      " 4   total_area            4902 non-null   int64  \n",
      " 5   rpi                   4902 non-null   float64\n",
      " 6   int_rate_max          4902 non-null   float64\n",
      " 7   int_rate_min          4902 non-null   float64\n",
      " 8   median_income         4902 non-null   int64  \n",
      " 9   all_new_sls_price     4902 non-null   int64  \n",
      " 10  det_new_sls_price     4902 non-null   int64  \n",
      " 11  semi_new_sls_price    4902 non-null   int64  \n",
      " 12  terr_new_sls_price    4902 non-null   int64  \n",
      " 13  flat_new_sls_price    4902 non-null   int64  \n",
      " 14  all_exist_sls_price   4902 non-null   int64  \n",
      " 15  det_exist_sls_price   4902 non-null   int64  \n",
      " 16  semi_exist_sls_price  4902 non-null   int64  \n",
      " 17  terr_exist_sls_price  4902 non-null   int64  \n",
      " 18  flat_exist_sls_price  4902 non-null   int64  \n",
      "dtypes: float64(3), int64(14), object(2)\n",
      "memory usage: 765.9+ KB\n"
     ]
    }
   ],
   "source": [
    "la_file.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "5b20975f",
   "metadata": {},
   "outputs": [],
   "source": [
    "med_all = file_processing('hpssa 9 2a median prices.csv', 'all', 'sls_price')\n",
    "med_det = file_processing('hpssa 9 2b median detached prices.csv', 'det', 'sls_price')\n",
    "med_semi = file_processing('hpssa 9 2c median semi prices.csv','semi', 'sls_price')\n",
    "med_terr = file_processing('hpssa 9 2d median terrace prices.csv','terr', 'sls_price')\n",
    "med_flat = file_processing('hpssa 9 2e median flat prices.csv', 'flat','sls_price')\n",
    "\n",
    "la_file  = la_file .merge(med_all)\n",
    "la_file  = merge_set(la_file ,[med_det, med_semi, med_terr, med_flat])\n",
    "#la_file.info()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "df875acf",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_new_build_all = file_processing('hpssa 7 2a num new build sls.csv', 'all', 'new_sls_num')\n",
    "num_new_build_det = file_processing('hpssa 7 2b num new build detached sls.csv', 'det', 'new_sls_num')\n",
    "num_new_build_semi = file_processing('hpssa 7 2c num new build semi sls.csv','semi', 'new_sls_num')\n",
    "num_new_build_terr = file_processing('hpssa 7 2d num new build terrace sls.csv','terr', 'new_sls_num')\n",
    "num_new_build_flat = file_processing('hpssa 7 2e num new build flat sls.csv', 'flat','new_sls_num')\n",
    "\n",
    "la_file = la_file.merge(num_new_build_all)\n",
    "la_file = merge_set(la_file,[num_new_build_det, num_new_build_semi, num_new_build_terr, num_new_build_flat])\n",
    "#la_file.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7a380338",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_exist_all = file_processing('hpssa 8 2a num existing sls.csv', 'all', 'exist_sls_num')\n",
    "num_exist_det = file_processing('hpssa 8 2b num existing detached sls.csv', 'det', 'exist_sls_num')\n",
    "num_exist_semi = file_processing('hpssa 8 2c num existing semi sls.csv','semi', 'exist_sls_num')\n",
    "num_exist_terr = file_processing('hpssa 8 2d num existing terrace sls.csv','terr', 'exist_sls_num')\n",
    "num_exist_flat = file_processing('hpssa 8 2e num existing flat sls.csv', 'flat','exist_sls_num')\n",
    "\n",
    "la_file = la_file.merge(num_exist_all)\n",
    "la_file = merge_set(la_file,[num_exist_det,num_exist_semi, num_exist_terr, num_exist_flat])\n",
    "#la_file.info()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "5c6581f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_all = file_processing('hpssa 6 2a num sales all.csv', 'all', 'sls_num')\n",
    "num_det = file_processing('hpssa 6 2b num sales detached.csv', 'det', 'sls_num')\n",
    "num_semi = file_processing('hpssa 6 2c num sales semi.csv','semi', 'sls_num')\n",
    "num_terr = file_processing('hpssa 6 2d num sales terrace.csv','terr', 'sls_num')\n",
    "num_flat = file_processing('hpssa 6 2e num sales flat.csv', 'flat','sls_num')\n",
    "\n",
    "la_file = la_file.merge(num_all)\n",
    "la_file = merge_set(la_file,[num_det, num_semi, num_terr, num_flat])\n",
    "#la_file.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "7e580ac7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>la_code</th>\n",
       "      <th>la_name</th>\n",
       "      <th>year</th>\n",
       "      <th>g_belt_area</th>\n",
       "      <th>total_area</th>\n",
       "      <th>rpi</th>\n",
       "      <th>int_rate_max</th>\n",
       "      <th>int_rate_min</th>\n",
       "      <th>median_income</th>\n",
       "      <th>all_new_sls_price</th>\n",
       "      <th>det_new_sls_price</th>\n",
       "      <th>semi_new_sls_price</th>\n",
       "      <th>terr_new_sls_price</th>\n",
       "      <th>flat_new_sls_price</th>\n",
       "      <th>all_exist_sls_price</th>\n",
       "      <th>det_exist_sls_price</th>\n",
       "      <th>semi_exist_sls_price</th>\n",
       "      <th>terr_exist_sls_price</th>\n",
       "      <th>flat_exist_sls_price</th>\n",
       "      <th>all_sls_price</th>\n",
       "      <th>det_sls_price</th>\n",
       "      <th>semi_sls_price</th>\n",
       "      <th>terr_sls_price</th>\n",
       "      <th>flat_sls_price</th>\n",
       "      <th>all_new_sls_num</th>\n",
       "      <th>det_new_sls_num</th>\n",
       "      <th>semi_new_sls_num</th>\n",
       "      <th>terr_new_sls_num</th>\n",
       "      <th>flat_new_sls_num</th>\n",
       "      <th>all_exist_sls_num</th>\n",
       "      <th>det_exist_sls_num</th>\n",
       "      <th>semi_exist_sls_num</th>\n",
       "      <th>terr_exist_sls_num</th>\n",
       "      <th>flat_exist_sls_num</th>\n",
       "      <th>all_sls_num</th>\n",
       "      <th>det_sls_num</th>\n",
       "      <th>semi_sls_num</th>\n",
       "      <th>terr_sls_num</th>\n",
       "      <th>flat_sls_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4897</th>\n",
       "      <td>E06000054</td>\n",
       "      <td>Wiltshire</td>\n",
       "      <td>2021</td>\n",
       "      <td>6910</td>\n",
       "      <td>325530</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>29069</td>\n",
       "      <td>319995</td>\n",
       "      <td>387000</td>\n",
       "      <td>272000</td>\n",
       "      <td>234995</td>\n",
       "      <td>284500</td>\n",
       "      <td>300000</td>\n",
       "      <td>425000</td>\n",
       "      <td>274000</td>\n",
       "      <td>235000</td>\n",
       "      <td>152000</td>\n",
       "      <td>300000</td>\n",
       "      <td>420000</td>\n",
       "      <td>273000</td>\n",
       "      <td>235000</td>\n",
       "      <td>155000</td>\n",
       "      <td>784</td>\n",
       "      <td>387</td>\n",
       "      <td>246</td>\n",
       "      <td>111</td>\n",
       "      <td>40</td>\n",
       "      <td>8992</td>\n",
       "      <td>3454</td>\n",
       "      <td>2323</td>\n",
       "      <td>2440</td>\n",
       "      <td>775</td>\n",
       "      <td>9776</td>\n",
       "      <td>3841</td>\n",
       "      <td>2569</td>\n",
       "      <td>2551</td>\n",
       "      <td>815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4898</th>\n",
       "      <td>E07000094</td>\n",
       "      <td>Winchester</td>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>66100</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>33601</td>\n",
       "      <td>382500</td>\n",
       "      <td>472473</td>\n",
       "      <td>349000</td>\n",
       "      <td>341473</td>\n",
       "      <td>745000</td>\n",
       "      <td>475000</td>\n",
       "      <td>675000</td>\n",
       "      <td>412500</td>\n",
       "      <td>378265</td>\n",
       "      <td>234250</td>\n",
       "      <td>470500</td>\n",
       "      <td>665000</td>\n",
       "      <td>400000</td>\n",
       "      <td>370000</td>\n",
       "      <td>237250</td>\n",
       "      <td>155</td>\n",
       "      <td>58</td>\n",
       "      <td>41</td>\n",
       "      <td>40</td>\n",
       "      <td>16</td>\n",
       "      <td>2308</td>\n",
       "      <td>923</td>\n",
       "      <td>533</td>\n",
       "      <td>582</td>\n",
       "      <td>270</td>\n",
       "      <td>2463</td>\n",
       "      <td>981</td>\n",
       "      <td>574</td>\n",
       "      <td>622</td>\n",
       "      <td>286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4899</th>\n",
       "      <td>E06000041</td>\n",
       "      <td>Wokingham</td>\n",
       "      <td>2021</td>\n",
       "      <td>2900</td>\n",
       "      <td>17900</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>38014</td>\n",
       "      <td>440000</td>\n",
       "      <td>565530</td>\n",
       "      <td>430000</td>\n",
       "      <td>425000</td>\n",
       "      <td>279000</td>\n",
       "      <td>450000</td>\n",
       "      <td>595000</td>\n",
       "      <td>425000</td>\n",
       "      <td>350000</td>\n",
       "      <td>230000</td>\n",
       "      <td>445975</td>\n",
       "      <td>590000</td>\n",
       "      <td>425000</td>\n",
       "      <td>355000</td>\n",
       "      <td>236250</td>\n",
       "      <td>319</td>\n",
       "      <td>124</td>\n",
       "      <td>117</td>\n",
       "      <td>46</td>\n",
       "      <td>32</td>\n",
       "      <td>3129</td>\n",
       "      <td>1273</td>\n",
       "      <td>881</td>\n",
       "      <td>663</td>\n",
       "      <td>312</td>\n",
       "      <td>3448</td>\n",
       "      <td>1397</td>\n",
       "      <td>998</td>\n",
       "      <td>709</td>\n",
       "      <td>344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4900</th>\n",
       "      <td>E07000238</td>\n",
       "      <td>Wychavon</td>\n",
       "      <td>2021</td>\n",
       "      <td>8860</td>\n",
       "      <td>66350</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>28865</td>\n",
       "      <td>342000</td>\n",
       "      <td>364750</td>\n",
       "      <td>255000</td>\n",
       "      <td>252500</td>\n",
       "      <td>369500</td>\n",
       "      <td>297250</td>\n",
       "      <td>407500</td>\n",
       "      <td>252500</td>\n",
       "      <td>217200</td>\n",
       "      <td>130000</td>\n",
       "      <td>302500</td>\n",
       "      <td>400000</td>\n",
       "      <td>252500</td>\n",
       "      <td>219750</td>\n",
       "      <td>137000</td>\n",
       "      <td>207</td>\n",
       "      <td>116</td>\n",
       "      <td>54</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>2270</td>\n",
       "      <td>1075</td>\n",
       "      <td>609</td>\n",
       "      <td>458</td>\n",
       "      <td>128</td>\n",
       "      <td>2477</td>\n",
       "      <td>1191</td>\n",
       "      <td>663</td>\n",
       "      <td>480</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4901</th>\n",
       "      <td>E06000014</td>\n",
       "      <td>York</td>\n",
       "      <td>2021</td>\n",
       "      <td>22410</td>\n",
       "      <td>27190</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>32490</td>\n",
       "      <td>350000</td>\n",
       "      <td>420000</td>\n",
       "      <td>332745</td>\n",
       "      <td>285000</td>\n",
       "      <td>324995</td>\n",
       "      <td>272000</td>\n",
       "      <td>395000</td>\n",
       "      <td>266000</td>\n",
       "      <td>245000</td>\n",
       "      <td>175000</td>\n",
       "      <td>275000</td>\n",
       "      <td>395000</td>\n",
       "      <td>268200</td>\n",
       "      <td>246000</td>\n",
       "      <td>180000</td>\n",
       "      <td>119</td>\n",
       "      <td>28</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>65</td>\n",
       "      <td>3681</td>\n",
       "      <td>831</td>\n",
       "      <td>1209</td>\n",
       "      <td>1167</td>\n",
       "      <td>474</td>\n",
       "      <td>3800</td>\n",
       "      <td>859</td>\n",
       "      <td>1227</td>\n",
       "      <td>1175</td>\n",
       "      <td>539</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        la_code     la_name  year  g_belt_area  total_area    rpi  \\\n",
       "4897  E06000054   Wiltshire  2021         6910      325530  305.0   \n",
       "4898  E07000094  Winchester  2021            0       66100  305.0   \n",
       "4899  E06000041   Wokingham  2021         2900       17900  305.0   \n",
       "4900  E07000238    Wychavon  2021         8860       66350  305.0   \n",
       "4901  E06000014        York  2021        22410       27190  305.0   \n",
       "\n",
       "      int_rate_max  int_rate_min  median_income  all_new_sls_price  \\\n",
       "4897          0.25          0.25          29069             319995   \n",
       "4898          0.25          0.25          33601             382500   \n",
       "4899          0.25          0.25          38014             440000   \n",
       "4900          0.25          0.25          28865             342000   \n",
       "4901          0.25          0.25          32490             350000   \n",
       "\n",
       "      det_new_sls_price  semi_new_sls_price  terr_new_sls_price  \\\n",
       "4897             387000              272000              234995   \n",
       "4898             472473              349000              341473   \n",
       "4899             565530              430000              425000   \n",
       "4900             364750              255000              252500   \n",
       "4901             420000              332745              285000   \n",
       "\n",
       "      flat_new_sls_price  all_exist_sls_price  det_exist_sls_price  \\\n",
       "4897              284500               300000               425000   \n",
       "4898              745000               475000               675000   \n",
       "4899              279000               450000               595000   \n",
       "4900              369500               297250               407500   \n",
       "4901              324995               272000               395000   \n",
       "\n",
       "      semi_exist_sls_price  terr_exist_sls_price  flat_exist_sls_price  \\\n",
       "4897                274000                235000                152000   \n",
       "4898                412500                378265                234250   \n",
       "4899                425000                350000                230000   \n",
       "4900                252500                217200                130000   \n",
       "4901                266000                245000                175000   \n",
       "\n",
       "      all_sls_price  det_sls_price  semi_sls_price  terr_sls_price  \\\n",
       "4897         300000         420000          273000          235000   \n",
       "4898         470500         665000          400000          370000   \n",
       "4899         445975         590000          425000          355000   \n",
       "4900         302500         400000          252500          219750   \n",
       "4901         275000         395000          268200          246000   \n",
       "\n",
       "      flat_sls_price  all_new_sls_num  det_new_sls_num  semi_new_sls_num  \\\n",
       "4897          155000              784              387               246   \n",
       "4898          237250              155               58                41   \n",
       "4899          236250              319              124               117   \n",
       "4900          137000              207              116                54   \n",
       "4901          180000              119               28                18   \n",
       "\n",
       "      terr_new_sls_num  flat_new_sls_num  all_exist_sls_num  \\\n",
       "4897               111                40               8992   \n",
       "4898                40                16               2308   \n",
       "4899                46                32               3129   \n",
       "4900                22                15               2270   \n",
       "4901                 8                65               3681   \n",
       "\n",
       "      det_exist_sls_num  semi_exist_sls_num  terr_exist_sls_num  \\\n",
       "4897               3454                2323                2440   \n",
       "4898                923                 533                 582   \n",
       "4899               1273                 881                 663   \n",
       "4900               1075                 609                 458   \n",
       "4901                831                1209                1167   \n",
       "\n",
       "      flat_exist_sls_num  all_sls_num  det_sls_num  semi_sls_num  \\\n",
       "4897                 775         9776         3841          2569   \n",
       "4898                 270         2463          981           574   \n",
       "4899                 312         3448         1397           998   \n",
       "4900                 128         2477         1191           663   \n",
       "4901                 474         3800          859          1227   \n",
       "\n",
       "      terr_sls_num  flat_sls_num  \n",
       "4897          2551           815  \n",
       "4898           622           286  \n",
       "4899           709           344  \n",
       "4900           480           143  \n",
       "4901          1175           539  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "la_file.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8a4ad00",
   "metadata": {},
   "source": [
    "### Adding: Population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b7ba1227",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2021 data has been added manually to the csv using the 2021 census data\\nMissing areas are filled with 0, these are NI and scotland which are not being used.'"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_la = pd.read_csv('pop by la.csv', header = 1)\n",
    "pop_la.head()\n",
    "\n",
    "'''2021 data has been added manually to the csv using the 2021 census data\n",
    "Missing areas are filled with 0, these are NI and scotland which are not being used.'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "a99e522d",
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_la = pop_la.drop(columns = 'ITL1 Region')\n",
    "pop_la = pop_la.rename(columns = {'LA code': 'la_code','LA name': 'la_name'})\n",
    "pop_la = pd.melt(pop_la, id_vars = ['la_code', 'la_name'], var_name = 'year', value_name = 'population')\n",
    "pop_la['population']  = pop_la['population'].replace({',': ''}, regex=True).astype('int64')\n",
    "pop_la['year'] = pop_la['year'].astype('int64')\n",
    "pop_la['la_code'] = pop_la['la_code'].str.strip()\n",
    "pop_la['la_name'] = pop_la['la_name'].str.strip()\n",
    "#pop_la.head()   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "dc57282a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4902 entries, 0 to 4901\n",
      "Data columns (total 40 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   la_code               4902 non-null   object \n",
      " 1   la_name               4902 non-null   object \n",
      " 2   year                  4902 non-null   int64  \n",
      " 3   g_belt_area           4902 non-null   int64  \n",
      " 4   total_area            4902 non-null   int64  \n",
      " 5   rpi                   4902 non-null   float64\n",
      " 6   int_rate_max          4902 non-null   float64\n",
      " 7   int_rate_min          4902 non-null   float64\n",
      " 8   median_income         4902 non-null   int64  \n",
      " 9   all_new_sls_price     4902 non-null   int64  \n",
      " 10  det_new_sls_price     4902 non-null   int64  \n",
      " 11  semi_new_sls_price    4902 non-null   int64  \n",
      " 12  terr_new_sls_price    4902 non-null   int64  \n",
      " 13  flat_new_sls_price    4902 non-null   int64  \n",
      " 14  all_exist_sls_price   4902 non-null   int64  \n",
      " 15  det_exist_sls_price   4902 non-null   int64  \n",
      " 16  semi_exist_sls_price  4902 non-null   int64  \n",
      " 17  terr_exist_sls_price  4902 non-null   int64  \n",
      " 18  flat_exist_sls_price  4902 non-null   int64  \n",
      " 19  all_sls_price         4902 non-null   int64  \n",
      " 20  det_sls_price         4902 non-null   int64  \n",
      " 21  semi_sls_price        4902 non-null   int64  \n",
      " 22  terr_sls_price        4902 non-null   int64  \n",
      " 23  flat_sls_price        4902 non-null   int64  \n",
      " 24  all_new_sls_num       4902 non-null   int64  \n",
      " 25  det_new_sls_num       4902 non-null   int64  \n",
      " 26  semi_new_sls_num      4902 non-null   int64  \n",
      " 27  terr_new_sls_num      4902 non-null   int64  \n",
      " 28  flat_new_sls_num      4902 non-null   int64  \n",
      " 29  all_exist_sls_num     4902 non-null   int64  \n",
      " 30  det_exist_sls_num     4902 non-null   int64  \n",
      " 31  semi_exist_sls_num    4902 non-null   int64  \n",
      " 32  terr_exist_sls_num    4902 non-null   int64  \n",
      " 33  flat_exist_sls_num    4902 non-null   int64  \n",
      " 34  all_sls_num           4902 non-null   int64  \n",
      " 35  det_sls_num           4902 non-null   int64  \n",
      " 36  semi_sls_num          4902 non-null   int64  \n",
      " 37  terr_sls_num          4902 non-null   int64  \n",
      " 38  flat_sls_num          4902 non-null   int64  \n",
      " 39  population            4902 non-null   int64  \n",
      "dtypes: float64(3), int64(35), object(2)\n",
      "memory usage: 1.5+ MB\n"
     ]
    }
   ],
   "source": [
    "la_file = la_file.merge(pop_la)\n",
    "la_file.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f4a7228",
   "metadata": {},
   "source": [
    "Note: lost some rows - checked that it's not because of bad refs to la_code and la_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "0ab9ecdf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>la_code</th>\n",
       "      <th>la_name</th>\n",
       "      <th>year</th>\n",
       "      <th>g_belt_area</th>\n",
       "      <th>total_area</th>\n",
       "      <th>rpi</th>\n",
       "      <th>int_rate_max</th>\n",
       "      <th>int_rate_min</th>\n",
       "      <th>median_income</th>\n",
       "      <th>all_new_sls_price</th>\n",
       "      <th>det_new_sls_price</th>\n",
       "      <th>semi_new_sls_price</th>\n",
       "      <th>terr_new_sls_price</th>\n",
       "      <th>flat_new_sls_price</th>\n",
       "      <th>all_exist_sls_price</th>\n",
       "      <th>det_exist_sls_price</th>\n",
       "      <th>semi_exist_sls_price</th>\n",
       "      <th>terr_exist_sls_price</th>\n",
       "      <th>flat_exist_sls_price</th>\n",
       "      <th>all_sls_price</th>\n",
       "      <th>det_sls_price</th>\n",
       "      <th>semi_sls_price</th>\n",
       "      <th>terr_sls_price</th>\n",
       "      <th>flat_sls_price</th>\n",
       "      <th>all_new_sls_num</th>\n",
       "      <th>det_new_sls_num</th>\n",
       "      <th>semi_new_sls_num</th>\n",
       "      <th>terr_new_sls_num</th>\n",
       "      <th>flat_new_sls_num</th>\n",
       "      <th>all_exist_sls_num</th>\n",
       "      <th>det_exist_sls_num</th>\n",
       "      <th>semi_exist_sls_num</th>\n",
       "      <th>terr_exist_sls_num</th>\n",
       "      <th>flat_exist_sls_num</th>\n",
       "      <th>all_sls_num</th>\n",
       "      <th>det_sls_num</th>\n",
       "      <th>semi_sls_num</th>\n",
       "      <th>terr_sls_num</th>\n",
       "      <th>flat_sls_num</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4897</th>\n",
       "      <td>E06000054</td>\n",
       "      <td>Wiltshire</td>\n",
       "      <td>2021</td>\n",
       "      <td>6910</td>\n",
       "      <td>325530</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>29069</td>\n",
       "      <td>319995</td>\n",
       "      <td>387000</td>\n",
       "      <td>272000</td>\n",
       "      <td>234995</td>\n",
       "      <td>284500</td>\n",
       "      <td>300000</td>\n",
       "      <td>425000</td>\n",
       "      <td>274000</td>\n",
       "      <td>235000</td>\n",
       "      <td>152000</td>\n",
       "      <td>300000</td>\n",
       "      <td>420000</td>\n",
       "      <td>273000</td>\n",
       "      <td>235000</td>\n",
       "      <td>155000</td>\n",
       "      <td>784</td>\n",
       "      <td>387</td>\n",
       "      <td>246</td>\n",
       "      <td>111</td>\n",
       "      <td>40</td>\n",
       "      <td>8992</td>\n",
       "      <td>3454</td>\n",
       "      <td>2323</td>\n",
       "      <td>2440</td>\n",
       "      <td>775</td>\n",
       "      <td>9776</td>\n",
       "      <td>3841</td>\n",
       "      <td>2569</td>\n",
       "      <td>2551</td>\n",
       "      <td>815</td>\n",
       "      <td>510400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4898</th>\n",
       "      <td>E07000094</td>\n",
       "      <td>Winchester</td>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>66100</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>33601</td>\n",
       "      <td>382500</td>\n",
       "      <td>472473</td>\n",
       "      <td>349000</td>\n",
       "      <td>341473</td>\n",
       "      <td>745000</td>\n",
       "      <td>475000</td>\n",
       "      <td>675000</td>\n",
       "      <td>412500</td>\n",
       "      <td>378265</td>\n",
       "      <td>234250</td>\n",
       "      <td>470500</td>\n",
       "      <td>665000</td>\n",
       "      <td>400000</td>\n",
       "      <td>370000</td>\n",
       "      <td>237250</td>\n",
       "      <td>155</td>\n",
       "      <td>58</td>\n",
       "      <td>41</td>\n",
       "      <td>40</td>\n",
       "      <td>16</td>\n",
       "      <td>2308</td>\n",
       "      <td>923</td>\n",
       "      <td>533</td>\n",
       "      <td>582</td>\n",
       "      <td>270</td>\n",
       "      <td>2463</td>\n",
       "      <td>981</td>\n",
       "      <td>574</td>\n",
       "      <td>622</td>\n",
       "      <td>286</td>\n",
       "      <td>127500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4899</th>\n",
       "      <td>E06000041</td>\n",
       "      <td>Wokingham</td>\n",
       "      <td>2021</td>\n",
       "      <td>2900</td>\n",
       "      <td>17900</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>38014</td>\n",
       "      <td>440000</td>\n",
       "      <td>565530</td>\n",
       "      <td>430000</td>\n",
       "      <td>425000</td>\n",
       "      <td>279000</td>\n",
       "      <td>450000</td>\n",
       "      <td>595000</td>\n",
       "      <td>425000</td>\n",
       "      <td>350000</td>\n",
       "      <td>230000</td>\n",
       "      <td>445975</td>\n",
       "      <td>590000</td>\n",
       "      <td>425000</td>\n",
       "      <td>355000</td>\n",
       "      <td>236250</td>\n",
       "      <td>319</td>\n",
       "      <td>124</td>\n",
       "      <td>117</td>\n",
       "      <td>46</td>\n",
       "      <td>32</td>\n",
       "      <td>3129</td>\n",
       "      <td>1273</td>\n",
       "      <td>881</td>\n",
       "      <td>663</td>\n",
       "      <td>312</td>\n",
       "      <td>3448</td>\n",
       "      <td>1397</td>\n",
       "      <td>998</td>\n",
       "      <td>709</td>\n",
       "      <td>344</td>\n",
       "      <td>177500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4900</th>\n",
       "      <td>E07000238</td>\n",
       "      <td>Wychavon</td>\n",
       "      <td>2021</td>\n",
       "      <td>8860</td>\n",
       "      <td>66350</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>28865</td>\n",
       "      <td>342000</td>\n",
       "      <td>364750</td>\n",
       "      <td>255000</td>\n",
       "      <td>252500</td>\n",
       "      <td>369500</td>\n",
       "      <td>297250</td>\n",
       "      <td>407500</td>\n",
       "      <td>252500</td>\n",
       "      <td>217200</td>\n",
       "      <td>130000</td>\n",
       "      <td>302500</td>\n",
       "      <td>400000</td>\n",
       "      <td>252500</td>\n",
       "      <td>219750</td>\n",
       "      <td>137000</td>\n",
       "      <td>207</td>\n",
       "      <td>116</td>\n",
       "      <td>54</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>2270</td>\n",
       "      <td>1075</td>\n",
       "      <td>609</td>\n",
       "      <td>458</td>\n",
       "      <td>128</td>\n",
       "      <td>2477</td>\n",
       "      <td>1191</td>\n",
       "      <td>663</td>\n",
       "      <td>480</td>\n",
       "      <td>143</td>\n",
       "      <td>132500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4901</th>\n",
       "      <td>E06000014</td>\n",
       "      <td>York</td>\n",
       "      <td>2021</td>\n",
       "      <td>22410</td>\n",
       "      <td>27190</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>32490</td>\n",
       "      <td>350000</td>\n",
       "      <td>420000</td>\n",
       "      <td>332745</td>\n",
       "      <td>285000</td>\n",
       "      <td>324995</td>\n",
       "      <td>272000</td>\n",
       "      <td>395000</td>\n",
       "      <td>266000</td>\n",
       "      <td>245000</td>\n",
       "      <td>175000</td>\n",
       "      <td>275000</td>\n",
       "      <td>395000</td>\n",
       "      <td>268200</td>\n",
       "      <td>246000</td>\n",
       "      <td>180000</td>\n",
       "      <td>119</td>\n",
       "      <td>28</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>65</td>\n",
       "      <td>3681</td>\n",
       "      <td>831</td>\n",
       "      <td>1209</td>\n",
       "      <td>1167</td>\n",
       "      <td>474</td>\n",
       "      <td>3800</td>\n",
       "      <td>859</td>\n",
       "      <td>1227</td>\n",
       "      <td>1175</td>\n",
       "      <td>539</td>\n",
       "      <td>202800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        la_code     la_name  year  g_belt_area  total_area    rpi  \\\n",
       "4897  E06000054   Wiltshire  2021         6910      325530  305.0   \n",
       "4898  E07000094  Winchester  2021            0       66100  305.0   \n",
       "4899  E06000041   Wokingham  2021         2900       17900  305.0   \n",
       "4900  E07000238    Wychavon  2021         8860       66350  305.0   \n",
       "4901  E06000014        York  2021        22410       27190  305.0   \n",
       "\n",
       "      int_rate_max  int_rate_min  median_income  all_new_sls_price  \\\n",
       "4897          0.25          0.25          29069             319995   \n",
       "4898          0.25          0.25          33601             382500   \n",
       "4899          0.25          0.25          38014             440000   \n",
       "4900          0.25          0.25          28865             342000   \n",
       "4901          0.25          0.25          32490             350000   \n",
       "\n",
       "      det_new_sls_price  semi_new_sls_price  terr_new_sls_price  \\\n",
       "4897             387000              272000              234995   \n",
       "4898             472473              349000              341473   \n",
       "4899             565530              430000              425000   \n",
       "4900             364750              255000              252500   \n",
       "4901             420000              332745              285000   \n",
       "\n",
       "      flat_new_sls_price  all_exist_sls_price  det_exist_sls_price  \\\n",
       "4897              284500               300000               425000   \n",
       "4898              745000               475000               675000   \n",
       "4899              279000               450000               595000   \n",
       "4900              369500               297250               407500   \n",
       "4901              324995               272000               395000   \n",
       "\n",
       "      semi_exist_sls_price  terr_exist_sls_price  flat_exist_sls_price  \\\n",
       "4897                274000                235000                152000   \n",
       "4898                412500                378265                234250   \n",
       "4899                425000                350000                230000   \n",
       "4900                252500                217200                130000   \n",
       "4901                266000                245000                175000   \n",
       "\n",
       "      all_sls_price  det_sls_price  semi_sls_price  terr_sls_price  \\\n",
       "4897         300000         420000          273000          235000   \n",
       "4898         470500         665000          400000          370000   \n",
       "4899         445975         590000          425000          355000   \n",
       "4900         302500         400000          252500          219750   \n",
       "4901         275000         395000          268200          246000   \n",
       "\n",
       "      flat_sls_price  all_new_sls_num  det_new_sls_num  semi_new_sls_num  \\\n",
       "4897          155000              784              387               246   \n",
       "4898          237250              155               58                41   \n",
       "4899          236250              319              124               117   \n",
       "4900          137000              207              116                54   \n",
       "4901          180000              119               28                18   \n",
       "\n",
       "      terr_new_sls_num  flat_new_sls_num  all_exist_sls_num  \\\n",
       "4897               111                40               8992   \n",
       "4898                40                16               2308   \n",
       "4899                46                32               3129   \n",
       "4900                22                15               2270   \n",
       "4901                 8                65               3681   \n",
       "\n",
       "      det_exist_sls_num  semi_exist_sls_num  terr_exist_sls_num  \\\n",
       "4897               3454                2323                2440   \n",
       "4898                923                 533                 582   \n",
       "4899               1273                 881                 663   \n",
       "4900               1075                 609                 458   \n",
       "4901                831                1209                1167   \n",
       "\n",
       "      flat_exist_sls_num  all_sls_num  det_sls_num  semi_sls_num  \\\n",
       "4897                 775         9776         3841          2569   \n",
       "4898                 270         2463          981           574   \n",
       "4899                 312         3448         1397           998   \n",
       "4900                 128         2477         1191           663   \n",
       "4901                 474         3800          859          1227   \n",
       "\n",
       "      terr_sls_num  flat_sls_num  population  \n",
       "4897          2551           815      510400  \n",
       "4898           622           286      127500  \n",
       "4899           709           344      177500  \n",
       "4900           480           143      132500  \n",
       "4901          1175           539      202800  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "la_file.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "144596e5",
   "metadata": {},
   "source": [
    "### Adding: Household Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "346980c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CODE</th>\n",
       "      <th>AREA NAME</th>\n",
       "      <th>AGE GROUP</th>\n",
       "      <th>HOUSEHOLD TYPE</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>2023</th>\n",
       "      <th>2024</th>\n",
       "      <th>2025</th>\n",
       "      <th>2026</th>\n",
       "      <th>2027</th>\n",
       "      <th>2028</th>\n",
       "      <th>2029</th>\n",
       "      <th>2030</th>\n",
       "      <th>2031</th>\n",
       "      <th>2032</th>\n",
       "      <th>2033</th>\n",
       "      <th>2034</th>\n",
       "      <th>2035</th>\n",
       "      <th>2036</th>\n",
       "      <th>2037</th>\n",
       "      <th>2038</th>\n",
       "      <th>2039</th>\n",
       "      <th>2040</th>\n",
       "      <th>2041</th>\n",
       "      <th>2042</th>\n",
       "      <th>2043</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E92000001</td>\n",
       "      <td>England</td>\n",
       "      <td>16-19</td>\n",
       "      <td>One person households: Male</td>\n",
       "      <td>19566.011690</td>\n",
       "      <td>19810.083870</td>\n",
       "      <td>19856.808470</td>\n",
       "      <td>19814.291230</td>\n",
       "      <td>19524.052160</td>\n",
       "      <td>19533.359330</td>\n",
       "      <td>19673.367530</td>\n",
       "      <td>19455.333340</td>\n",
       "      <td>19232.919300</td>\n",
       "      <td>18997.929960</td>\n",
       "      <td>18223.858800</td>\n",
       "      <td>17720.087410</td>\n",
       "      <td>17328.954580</td>\n",
       "      <td>16912.998820</td>\n",
       "      <td>16625.527700</td>\n",
       "      <td>16197.129210</td>\n",
       "      <td>15606.341790</td>\n",
       "      <td>15112.578790</td>\n",
       "      <td>14700.926690</td>\n",
       "      <td>14524.160110</td>\n",
       "      <td>14519.340550</td>\n",
       "      <td>15000.654000</td>\n",
       "      <td>15507.038040</td>\n",
       "      <td>16034.483140</td>\n",
       "      <td>16452.290110</td>\n",
       "      <td>16763.991060</td>\n",
       "      <td>17065.027870</td>\n",
       "      <td>17334.755050</td>\n",
       "      <td>17524.223330</td>\n",
       "      <td>17554.090370</td>\n",
       "      <td>17477.643110</td>\n",
       "      <td>17326.918050</td>\n",
       "      <td>17180.826080</td>\n",
       "      <td>16996.586010</td>\n",
       "      <td>16671.965810</td>\n",
       "      <td>16334.979840</td>\n",
       "      <td>16073.692800</td>\n",
       "      <td>15898.027230</td>\n",
       "      <td>15846.324030</td>\n",
       "      <td>15777.126770</td>\n",
       "      <td>15728.800740</td>\n",
       "      <td>15704.780040</td>\n",
       "      <td>15693.239300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E92000001</td>\n",
       "      <td>England</td>\n",
       "      <td>16-19</td>\n",
       "      <td>One person households: Female</td>\n",
       "      <td>22469.415990</td>\n",
       "      <td>22440.586930</td>\n",
       "      <td>22653.577300</td>\n",
       "      <td>22867.484870</td>\n",
       "      <td>22858.524350</td>\n",
       "      <td>22502.335780</td>\n",
       "      <td>22442.590570</td>\n",
       "      <td>22219.994410</td>\n",
       "      <td>21918.766890</td>\n",
       "      <td>21460.190380</td>\n",
       "      <td>20299.416410</td>\n",
       "      <td>19516.264910</td>\n",
       "      <td>19012.579530</td>\n",
       "      <td>18512.062340</td>\n",
       "      <td>18154.698620</td>\n",
       "      <td>17673.516820</td>\n",
       "      <td>17041.041870</td>\n",
       "      <td>16471.727740</td>\n",
       "      <td>15988.333420</td>\n",
       "      <td>15800.004640</td>\n",
       "      <td>15762.620680</td>\n",
       "      <td>16301.864040</td>\n",
       "      <td>16846.653930</td>\n",
       "      <td>17420.782570</td>\n",
       "      <td>17886.222970</td>\n",
       "      <td>18239.519380</td>\n",
       "      <td>18597.022170</td>\n",
       "      <td>18909.107280</td>\n",
       "      <td>19099.550910</td>\n",
       "      <td>19119.890430</td>\n",
       "      <td>19022.983140</td>\n",
       "      <td>18839.212230</td>\n",
       "      <td>18688.783370</td>\n",
       "      <td>18481.094930</td>\n",
       "      <td>18145.068160</td>\n",
       "      <td>17788.909720</td>\n",
       "      <td>17512.495040</td>\n",
       "      <td>17335.226430</td>\n",
       "      <td>17275.491550</td>\n",
       "      <td>17196.055660</td>\n",
       "      <td>17139.634410</td>\n",
       "      <td>17110.248870</td>\n",
       "      <td>17094.658820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E92000001</td>\n",
       "      <td>England</td>\n",
       "      <td>16-19</td>\n",
       "      <td>Households with one dependent child</td>\n",
       "      <td>24438.364330</td>\n",
       "      <td>24612.193070</td>\n",
       "      <td>24971.972270</td>\n",
       "      <td>25315.211610</td>\n",
       "      <td>25351.737280</td>\n",
       "      <td>25190.598320</td>\n",
       "      <td>25375.984970</td>\n",
       "      <td>25328.096950</td>\n",
       "      <td>25180.931460</td>\n",
       "      <td>24843.888770</td>\n",
       "      <td>23715.748340</td>\n",
       "      <td>22972.667450</td>\n",
       "      <td>22532.432730</td>\n",
       "      <td>22064.419010</td>\n",
       "      <td>21741.548290</td>\n",
       "      <td>21251.636480</td>\n",
       "      <td>20559.726570</td>\n",
       "      <td>19935.976000</td>\n",
       "      <td>19371.270950</td>\n",
       "      <td>19179.527640</td>\n",
       "      <td>19177.705990</td>\n",
       "      <td>19819.898760</td>\n",
       "      <td>20461.025710</td>\n",
       "      <td>21136.882380</td>\n",
       "      <td>21667.766230</td>\n",
       "      <td>22064.910610</td>\n",
       "      <td>22463.430050</td>\n",
       "      <td>22792.693330</td>\n",
       "      <td>22974.976290</td>\n",
       "      <td>22948.626450</td>\n",
       "      <td>22777.413910</td>\n",
       "      <td>22506.800250</td>\n",
       "      <td>22269.231920</td>\n",
       "      <td>21980.069260</td>\n",
       "      <td>21552.978170</td>\n",
       "      <td>21107.266150</td>\n",
       "      <td>20768.530690</td>\n",
       "      <td>20564.993670</td>\n",
       "      <td>20522.975940</td>\n",
       "      <td>20462.487890</td>\n",
       "      <td>20428.944070</td>\n",
       "      <td>20425.226870</td>\n",
       "      <td>20439.401700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E92000001</td>\n",
       "      <td>England</td>\n",
       "      <td>16-19</td>\n",
       "      <td>Households with two dependent children</td>\n",
       "      <td>3756.336149</td>\n",
       "      <td>3765.193249</td>\n",
       "      <td>3802.747246</td>\n",
       "      <td>3837.299982</td>\n",
       "      <td>3825.947566</td>\n",
       "      <td>3801.614831</td>\n",
       "      <td>3827.394309</td>\n",
       "      <td>3813.971082</td>\n",
       "      <td>3787.672142</td>\n",
       "      <td>3736.107155</td>\n",
       "      <td>3567.469592</td>\n",
       "      <td>3456.156216</td>\n",
       "      <td>3388.786679</td>\n",
       "      <td>3317.074034</td>\n",
       "      <td>3268.147400</td>\n",
       "      <td>3193.996014</td>\n",
       "      <td>3090.479676</td>\n",
       "      <td>2997.832930</td>\n",
       "      <td>2914.875974</td>\n",
       "      <td>2888.106135</td>\n",
       "      <td>2891.219176</td>\n",
       "      <td>2988.701682</td>\n",
       "      <td>3086.418875</td>\n",
       "      <td>3189.364221</td>\n",
       "      <td>3270.365687</td>\n",
       "      <td>3331.174654</td>\n",
       "      <td>3392.179137</td>\n",
       "      <td>3442.393530</td>\n",
       "      <td>3470.377358</td>\n",
       "      <td>3467.170460</td>\n",
       "      <td>3442.144297</td>\n",
       "      <td>3401.968607</td>\n",
       "      <td>3366.248196</td>\n",
       "      <td>3322.624935</td>\n",
       "      <td>3257.957664</td>\n",
       "      <td>3190.290486</td>\n",
       "      <td>3138.847693</td>\n",
       "      <td>3108.017951</td>\n",
       "      <td>3101.803090</td>\n",
       "      <td>3092.786476</td>\n",
       "      <td>3087.721737</td>\n",
       "      <td>3087.211950</td>\n",
       "      <td>3089.392277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E92000001</td>\n",
       "      <td>England</td>\n",
       "      <td>16-19</td>\n",
       "      <td>Households with three or more dependent children</td>\n",
       "      <td>1160.096879</td>\n",
       "      <td>1120.597166</td>\n",
       "      <td>1092.320386</td>\n",
       "      <td>1063.486012</td>\n",
       "      <td>1023.250942</td>\n",
       "      <td>991.193379</td>\n",
       "      <td>972.368993</td>\n",
       "      <td>942.620142</td>\n",
       "      <td>912.609919</td>\n",
       "      <td>880.006852</td>\n",
       "      <td>823.308903</td>\n",
       "      <td>782.134630</td>\n",
       "      <td>751.889136</td>\n",
       "      <td>722.493790</td>\n",
       "      <td>699.692686</td>\n",
       "      <td>672.910565</td>\n",
       "      <td>641.263470</td>\n",
       "      <td>613.698308</td>\n",
       "      <td>589.284020</td>\n",
       "      <td>577.231192</td>\n",
       "      <td>571.912060</td>\n",
       "      <td>590.986482</td>\n",
       "      <td>610.144986</td>\n",
       "      <td>630.477900</td>\n",
       "      <td>646.453068</td>\n",
       "      <td>658.345964</td>\n",
       "      <td>670.259688</td>\n",
       "      <td>680.067864</td>\n",
       "      <td>685.494755</td>\n",
       "      <td>684.799378</td>\n",
       "      <td>679.694730</td>\n",
       "      <td>671.453154</td>\n",
       "      <td>664.082508</td>\n",
       "      <td>655.240120</td>\n",
       "      <td>642.297042</td>\n",
       "      <td>628.862925</td>\n",
       "      <td>618.603471</td>\n",
       "      <td>612.491781</td>\n",
       "      <td>611.420447</td>\n",
       "      <td>609.829765</td>\n",
       "      <td>609.006652</td>\n",
       "      <td>609.036026</td>\n",
       "      <td>609.585483</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        CODE AREA NAME AGE GROUP  \\\n",
       "0  E92000001   England     16-19   \n",
       "1  E92000001   England     16-19   \n",
       "2  E92000001   England     16-19   \n",
       "3  E92000001   England     16-19   \n",
       "4  E92000001   England     16-19   \n",
       "\n",
       "                                     HOUSEHOLD TYPE          2001  \\\n",
       "0                       One person households: Male  19566.011690   \n",
       "1                     One person households: Female  22469.415990   \n",
       "2               Households with one dependent child  24438.364330   \n",
       "3            Households with two dependent children   3756.336149   \n",
       "4  Households with three or more dependent children   1160.096879   \n",
       "\n",
       "           2002          2003          2004          2005          2006  \\\n",
       "0  19810.083870  19856.808470  19814.291230  19524.052160  19533.359330   \n",
       "1  22440.586930  22653.577300  22867.484870  22858.524350  22502.335780   \n",
       "2  24612.193070  24971.972270  25315.211610  25351.737280  25190.598320   \n",
       "3   3765.193249   3802.747246   3837.299982   3825.947566   3801.614831   \n",
       "4   1120.597166   1092.320386   1063.486012   1023.250942    991.193379   \n",
       "\n",
       "           2007          2008          2009          2010          2011  \\\n",
       "0  19673.367530  19455.333340  19232.919300  18997.929960  18223.858800   \n",
       "1  22442.590570  22219.994410  21918.766890  21460.190380  20299.416410   \n",
       "2  25375.984970  25328.096950  25180.931460  24843.888770  23715.748340   \n",
       "3   3827.394309   3813.971082   3787.672142   3736.107155   3567.469592   \n",
       "4    972.368993    942.620142    912.609919    880.006852    823.308903   \n",
       "\n",
       "           2012          2013          2014          2015          2016  \\\n",
       "0  17720.087410  17328.954580  16912.998820  16625.527700  16197.129210   \n",
       "1  19516.264910  19012.579530  18512.062340  18154.698620  17673.516820   \n",
       "2  22972.667450  22532.432730  22064.419010  21741.548290  21251.636480   \n",
       "3   3456.156216   3388.786679   3317.074034   3268.147400   3193.996014   \n",
       "4    782.134630    751.889136    722.493790    699.692686    672.910565   \n",
       "\n",
       "           2017          2018          2019          2020          2021  \\\n",
       "0  15606.341790  15112.578790  14700.926690  14524.160110  14519.340550   \n",
       "1  17041.041870  16471.727740  15988.333420  15800.004640  15762.620680   \n",
       "2  20559.726570  19935.976000  19371.270950  19179.527640  19177.705990   \n",
       "3   3090.479676   2997.832930   2914.875974   2888.106135   2891.219176   \n",
       "4    641.263470    613.698308    589.284020    577.231192    571.912060   \n",
       "\n",
       "           2022          2023          2024          2025          2026  \\\n",
       "0  15000.654000  15507.038040  16034.483140  16452.290110  16763.991060   \n",
       "1  16301.864040  16846.653930  17420.782570  17886.222970  18239.519380   \n",
       "2  19819.898760  20461.025710  21136.882380  21667.766230  22064.910610   \n",
       "3   2988.701682   3086.418875   3189.364221   3270.365687   3331.174654   \n",
       "4    590.986482    610.144986    630.477900    646.453068    658.345964   \n",
       "\n",
       "           2027          2028          2029          2030          2031  \\\n",
       "0  17065.027870  17334.755050  17524.223330  17554.090370  17477.643110   \n",
       "1  18597.022170  18909.107280  19099.550910  19119.890430  19022.983140   \n",
       "2  22463.430050  22792.693330  22974.976290  22948.626450  22777.413910   \n",
       "3   3392.179137   3442.393530   3470.377358   3467.170460   3442.144297   \n",
       "4    670.259688    680.067864    685.494755    684.799378    679.694730   \n",
       "\n",
       "           2032          2033          2034          2035          2036  \\\n",
       "0  17326.918050  17180.826080  16996.586010  16671.965810  16334.979840   \n",
       "1  18839.212230  18688.783370  18481.094930  18145.068160  17788.909720   \n",
       "2  22506.800250  22269.231920  21980.069260  21552.978170  21107.266150   \n",
       "3   3401.968607   3366.248196   3322.624935   3257.957664   3190.290486   \n",
       "4    671.453154    664.082508    655.240120    642.297042    628.862925   \n",
       "\n",
       "           2037          2038          2039          2040          2041  \\\n",
       "0  16073.692800  15898.027230  15846.324030  15777.126770  15728.800740   \n",
       "1  17512.495040  17335.226430  17275.491550  17196.055660  17139.634410   \n",
       "2  20768.530690  20564.993670  20522.975940  20462.487890  20428.944070   \n",
       "3   3138.847693   3108.017951   3101.803090   3092.786476   3087.721737   \n",
       "4    618.603471    612.491781    611.420447    609.829765    609.006652   \n",
       "\n",
       "           2042          2043  \n",
       "0  15704.780040  15693.239300  \n",
       "1  17110.248870  17094.658820  \n",
       "2  20425.226870  20439.401700  \n",
       "3   3087.211950   3089.392277  \n",
       "4    609.036026    609.585483  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_hh_type = pd.read_csv('Stage 2 projected households - Principal (type).csv')\n",
    "num_hh_type.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "0962e57a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#only need row data where 'all ages' in age group column\n",
    "num_hh_type = num_hh_type.drop(num_hh_type[num_hh_type['AGE GROUP'] != 'All ages'].index)\n",
    "\n",
    "# Year goes up to 2043. Don't need past 2021\n",
    "num_hh_type = num_hh_type.drop(num_hh_type.iloc[: , 25:48], axis = 1)\n",
    "\n",
    "# rearrange layout so that years in rows and household types are columns.\n",
    "# no longer need the age group column\n",
    "num_hh_type = pd.melt(num_hh_type, id_vars= ['CODE', 'AREA NAME', 'AGE GROUP', 'HOUSEHOLD TYPE'],\n",
    "       var_name = \"year\",value_name = 'num_hh' )\n",
    "\n",
    "num_hh_type = num_hh_type.pivot(columns = 'HOUSEHOLD TYPE', index = ['CODE',\n",
    "                                                                    'AREA NAME',\n",
    "                                                                    'AGE GROUP',\n",
    "                                                                    'year'], values = 'num_hh').fillna(0).reset_index()  \n",
    "\n",
    "#delete age group & rename others\n",
    "num_hh_type = num_hh_type.drop(columns = ['AGE GROUP']).rename(columns = {'CODE': 'la_code',\n",
    "                                                                         'AREA NAME' : 'la_name',\n",
    "                                                                         'Households with one dependent child': 'num_hh_1dp',\n",
    "                                                                         'Households with three or more dependent children': 'num_hh_>2dp',\n",
    "                                                                         'Households with two dependent children': 'num_hh_2dp',\n",
    "                                                                         'One person households: Female': 'num_hh_1f',\n",
    "                                                                         'One person households: Male': 'num_hh_1m',\n",
    "                                                                         'Other households with two or more adults': 'num_hh_oth',\n",
    "                                                                         'Total': 'num_hh_tot'})\n",
    "pop_la['la_code'] = pop_la['la_code'].str.strip()\n",
    "pop_la['la_name'] = pop_la['la_name'].str.strip()\n",
    "num_hh_type['year'] = num_hh_type['year'].astype('int64')\n",
    "#num_hh_type.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "3204ca75",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7056 entries, 0 to 7055\n",
      "Data columns (total 10 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   la_code      7056 non-null   object \n",
      " 1   la_name      7056 non-null   object \n",
      " 2   year         7056 non-null   int64  \n",
      " 3   num_hh_1dp   7056 non-null   float64\n",
      " 4   num_hh_>2dp  7056 non-null   float64\n",
      " 5   num_hh_2dp   7056 non-null   float64\n",
      " 6   num_hh_1f    7056 non-null   float64\n",
      " 7   num_hh_1m    7056 non-null   float64\n",
      " 8   num_hh_oth   7056 non-null   float64\n",
      " 9   num_hh_tot   7056 non-null   float64\n",
      "dtypes: float64(7), int64(1), object(2)\n",
      "memory usage: 551.4+ KB\n"
     ]
    }
   ],
   "source": [
    "num_hh_type.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "69f9dd6d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4606 entries, 0 to 4605\n",
      "Data columns (total 47 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   la_code               4606 non-null   object \n",
      " 1   la_name               4606 non-null   object \n",
      " 2   year                  4606 non-null   int64  \n",
      " 3   g_belt_area           4606 non-null   int64  \n",
      " 4   total_area            4606 non-null   int64  \n",
      " 5   rpi                   4606 non-null   float64\n",
      " 6   int_rate_max          4606 non-null   float64\n",
      " 7   int_rate_min          4606 non-null   float64\n",
      " 8   median_income         4606 non-null   int64  \n",
      " 9   all_new_sls_price     4606 non-null   int64  \n",
      " 10  det_new_sls_price     4606 non-null   int64  \n",
      " 11  semi_new_sls_price    4606 non-null   int64  \n",
      " 12  terr_new_sls_price    4606 non-null   int64  \n",
      " 13  flat_new_sls_price    4606 non-null   int64  \n",
      " 14  all_exist_sls_price   4606 non-null   int64  \n",
      " 15  det_exist_sls_price   4606 non-null   int64  \n",
      " 16  semi_exist_sls_price  4606 non-null   int64  \n",
      " 17  terr_exist_sls_price  4606 non-null   int64  \n",
      " 18  flat_exist_sls_price  4606 non-null   int64  \n",
      " 19  all_sls_price         4606 non-null   int64  \n",
      " 20  det_sls_price         4606 non-null   int64  \n",
      " 21  semi_sls_price        4606 non-null   int64  \n",
      " 22  terr_sls_price        4606 non-null   int64  \n",
      " 23  flat_sls_price        4606 non-null   int64  \n",
      " 24  all_new_sls_num       4606 non-null   int64  \n",
      " 25  det_new_sls_num       4606 non-null   int64  \n",
      " 26  semi_new_sls_num      4606 non-null   int64  \n",
      " 27  terr_new_sls_num      4606 non-null   int64  \n",
      " 28  flat_new_sls_num      4606 non-null   int64  \n",
      " 29  all_exist_sls_num     4606 non-null   int64  \n",
      " 30  det_exist_sls_num     4606 non-null   int64  \n",
      " 31  semi_exist_sls_num    4606 non-null   int64  \n",
      " 32  terr_exist_sls_num    4606 non-null   int64  \n",
      " 33  flat_exist_sls_num    4606 non-null   int64  \n",
      " 34  all_sls_num           4606 non-null   int64  \n",
      " 35  det_sls_num           4606 non-null   int64  \n",
      " 36  semi_sls_num          4606 non-null   int64  \n",
      " 37  terr_sls_num          4606 non-null   int64  \n",
      " 38  flat_sls_num          4606 non-null   int64  \n",
      " 39  population            4606 non-null   int64  \n",
      " 40  num_hh_1dp            4606 non-null   float64\n",
      " 41  num_hh_>2dp           4606 non-null   float64\n",
      " 42  num_hh_2dp            4606 non-null   float64\n",
      " 43  num_hh_1f             4606 non-null   float64\n",
      " 44  num_hh_1m             4606 non-null   float64\n",
      " 45  num_hh_oth            4606 non-null   float64\n",
      " 46  num_hh_tot            4606 non-null   float64\n",
      "dtypes: float64(10), int64(35), object(2)\n",
      "memory usage: 1.7+ MB\n"
     ]
    }
   ],
   "source": [
    "la_file = la_file.merge(num_hh_type)\n",
    "la_file.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f29e901",
   "metadata": {},
   "source": [
    "### Adding: GDP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "6dca92d5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ITL1 Region</th>\n",
       "      <th>LA code</th>\n",
       "      <th>LA name</th>\n",
       "      <th>1998</th>\n",
       "      <th>1999</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>North East</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>915</td>\n",
       "      <td>968</td>\n",
       "      <td>988</td>\n",
       "      <td>999</td>\n",
       "      <td>1,054</td>\n",
       "      <td>1,114</td>\n",
       "      <td>1,159</td>\n",
       "      <td>1,205</td>\n",
       "      <td>1,262</td>\n",
       "      <td>1,326</td>\n",
       "      <td>1,351</td>\n",
       "      <td>1,325</td>\n",
       "      <td>1,357</td>\n",
       "      <td>1,411</td>\n",
       "      <td>1,416</td>\n",
       "      <td>1,461</td>\n",
       "      <td>1,448</td>\n",
       "      <td>1,543</td>\n",
       "      <td>1,654</td>\n",
       "      <td>1,675</td>\n",
       "      <td>1,674</td>\n",
       "      <td>1,775</td>\n",
       "      <td>1,757</td>\n",
       "      <td>1733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>North East</td>\n",
       "      <td>E06000004</td>\n",
       "      <td>Stockton-on-Tees</td>\n",
       "      <td>2,875</td>\n",
       "      <td>3,013</td>\n",
       "      <td>3,104</td>\n",
       "      <td>3,153</td>\n",
       "      <td>3,323</td>\n",
       "      <td>3,567</td>\n",
       "      <td>3,825</td>\n",
       "      <td>3,948</td>\n",
       "      <td>4,267</td>\n",
       "      <td>4,488</td>\n",
       "      <td>4,541</td>\n",
       "      <td>4,693</td>\n",
       "      <td>4,699</td>\n",
       "      <td>4,745</td>\n",
       "      <td>4,741</td>\n",
       "      <td>4,918</td>\n",
       "      <td>5,085</td>\n",
       "      <td>5,363</td>\n",
       "      <td>5,482</td>\n",
       "      <td>5,364</td>\n",
       "      <td>5,522</td>\n",
       "      <td>5,898</td>\n",
       "      <td>5,610</td>\n",
       "      <td>5759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>North East</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>1,700</td>\n",
       "      <td>1,771</td>\n",
       "      <td>1,822</td>\n",
       "      <td>1,843</td>\n",
       "      <td>1,912</td>\n",
       "      <td>2,031</td>\n",
       "      <td>2,184</td>\n",
       "      <td>2,332</td>\n",
       "      <td>2,472</td>\n",
       "      <td>2,545</td>\n",
       "      <td>2,613</td>\n",
       "      <td>2,645</td>\n",
       "      <td>2,621</td>\n",
       "      <td>2,702</td>\n",
       "      <td>2,777</td>\n",
       "      <td>2,770</td>\n",
       "      <td>2,860</td>\n",
       "      <td>2,999</td>\n",
       "      <td>3,143</td>\n",
       "      <td>3,231</td>\n",
       "      <td>3,449</td>\n",
       "      <td>3,464</td>\n",
       "      <td>3,295</td>\n",
       "      <td>3382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>North East</td>\n",
       "      <td>E06000003</td>\n",
       "      <td>Redcar and Cleveland</td>\n",
       "      <td>1,463</td>\n",
       "      <td>1,491</td>\n",
       "      <td>1,531</td>\n",
       "      <td>1,516</td>\n",
       "      <td>1,560</td>\n",
       "      <td>1,721</td>\n",
       "      <td>1,752</td>\n",
       "      <td>1,799</td>\n",
       "      <td>1,830</td>\n",
       "      <td>1,850</td>\n",
       "      <td>1,954</td>\n",
       "      <td>1,927</td>\n",
       "      <td>2,010</td>\n",
       "      <td>2,048</td>\n",
       "      <td>2,072</td>\n",
       "      <td>2,073</td>\n",
       "      <td>2,146</td>\n",
       "      <td>2,223</td>\n",
       "      <td>2,174</td>\n",
       "      <td>2,043</td>\n",
       "      <td>2,125</td>\n",
       "      <td>2,233</td>\n",
       "      <td>2,182</td>\n",
       "      <td>2180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>North East</td>\n",
       "      <td>E06000005</td>\n",
       "      <td>Darlington</td>\n",
       "      <td>1,693</td>\n",
       "      <td>1,739</td>\n",
       "      <td>1,873</td>\n",
       "      <td>1,982</td>\n",
       "      <td>2,112</td>\n",
       "      <td>2,251</td>\n",
       "      <td>2,402</td>\n",
       "      <td>2,393</td>\n",
       "      <td>2,462</td>\n",
       "      <td>2,423</td>\n",
       "      <td>2,542</td>\n",
       "      <td>2,419</td>\n",
       "      <td>2,493</td>\n",
       "      <td>2,593</td>\n",
       "      <td>2,566</td>\n",
       "      <td>2,595</td>\n",
       "      <td>2,912</td>\n",
       "      <td>3,048</td>\n",
       "      <td>2,924</td>\n",
       "      <td>2,911</td>\n",
       "      <td>2,932</td>\n",
       "      <td>2,975</td>\n",
       "      <td>2,870</td>\n",
       "      <td>2905</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ITL1 Region    LA code               LA name   1998   1999   2000   2001  \\\n",
       "0  North East  E06000001            Hartlepool    915    968    988    999   \n",
       "1  North East  E06000004      Stockton-on-Tees  2,875  3,013  3,104  3,153   \n",
       "2  North East  E06000002         Middlesbrough  1,700  1,771  1,822  1,843   \n",
       "3  North East  E06000003  Redcar and Cleveland  1,463  1,491  1,531  1,516   \n",
       "4  North East  E06000005            Darlington  1,693  1,739  1,873  1,982   \n",
       "\n",
       "    2002   2003   2004   2005   2006   2007   2008   2009   2010   2011  \\\n",
       "0  1,054  1,114  1,159  1,205  1,262  1,326  1,351  1,325  1,357  1,411   \n",
       "1  3,323  3,567  3,825  3,948  4,267  4,488  4,541  4,693  4,699  4,745   \n",
       "2  1,912  2,031  2,184  2,332  2,472  2,545  2,613  2,645  2,621  2,702   \n",
       "3  1,560  1,721  1,752  1,799  1,830  1,850  1,954  1,927  2,010  2,048   \n",
       "4  2,112  2,251  2,402  2,393  2,462  2,423  2,542  2,419  2,493  2,593   \n",
       "\n",
       "    2012   2013   2014   2015   2016   2017   2018   2019   2020  2021  \n",
       "0  1,416  1,461  1,448  1,543  1,654  1,675  1,674  1,775  1,757  1733  \n",
       "1  4,741  4,918  5,085  5,363  5,482  5,364  5,522  5,898  5,610  5759  \n",
       "2  2,777  2,770  2,860  2,999  3,143  3,231  3,449  3,464  3,295  3382  \n",
       "3  2,072  2,073  2,146  2,223  2,174  2,043  2,125  2,233  2,182  2180  \n",
       "4  2,566  2,595  2,912  3,048  2,924  2,911  2,932  2,975  2,870  2905  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdp_la = pd.read_csv('gdp by la.csv', header = 1)\n",
    "gdp_la.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "0be6cca4",
   "metadata": {},
   "outputs": [],
   "source": [
    "gdp_la = gdp_la.drop(columns = 'ITL1 Region')\n",
    "gdp_la = gdp_la.rename(columns = {'LA code': 'la_code','LA name': 'la_name'})\n",
    "gdp_la = pd.melt(gdp_la, id_vars = ['la_code', 'la_name'], var_name = 'year', value_name = 'gdp')\n",
    "gdp_la['gdp']  = gdp_la['gdp'].replace({',': ''}, regex=True).astype('int64')\n",
    "gdp_la['year'] = gdp_la['year'].astype('int64')\n",
    "gdp_la['la_code'] = gdp_la['la_code'].str.strip()\n",
    "gdp_la['la_name'] = gdp_la['la_name'].str.strip()\n",
    "#gdp_la.head()          "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "60b9c801",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>la_code</th>\n",
       "      <th>la_name</th>\n",
       "      <th>year</th>\n",
       "      <th>gdp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8971</th>\n",
       "      <td>S12000026</td>\n",
       "      <td>Scottish Borders</td>\n",
       "      <td>2021</td>\n",
       "      <td>2765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8972</th>\n",
       "      <td>S12000006</td>\n",
       "      <td>Dumfries and Galloway</td>\n",
       "      <td>2021</td>\n",
       "      <td>4142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8973</th>\n",
       "      <td>S12000008</td>\n",
       "      <td>East Ayrshire</td>\n",
       "      <td>2021</td>\n",
       "      <td>2267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8974</th>\n",
       "      <td>S12000028</td>\n",
       "      <td>South Ayrshire</td>\n",
       "      <td>2021</td>\n",
       "      <td>2837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8975</th>\n",
       "      <td>S12000029</td>\n",
       "      <td>South Lanarkshire</td>\n",
       "      <td>2021</td>\n",
       "      <td>7840</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        la_code                la_name  year   gdp\n",
       "8971  S12000026       Scottish Borders  2021  2765\n",
       "8972  S12000006  Dumfries and Galloway  2021  4142\n",
       "8973  S12000008          East Ayrshire  2021  2267\n",
       "8974  S12000028         South Ayrshire  2021  2837\n",
       "8975  S12000029      South Lanarkshire  2021  7840"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#gdp_la.info()\n",
    "gdp_la.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "89e19dea",
   "metadata": {},
   "outputs": [],
   "source": [
    "la_file = la_file.merge(gdp_la)\n",
    "#la_file.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e68755d4",
   "metadata": {},
   "source": [
    "### Adding: Dwelling Stock"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "ea824e26",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Current\\nONS code</th>\n",
       "      <th>Lower and Single Tier Authority Data</th>\n",
       "      <th>1994</th>\n",
       "      <th>1995</th>\n",
       "      <th>1996</th>\n",
       "      <th>1997</th>\n",
       "      <th>1998</th>\n",
       "      <th>1999</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>England</td>\n",
       "      <td>E92000001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3,666,190</td>\n",
       "      <td>3,564,590</td>\n",
       "      <td>3,469,594</td>\n",
       "      <td>3,400,635</td>\n",
       "      <td>3,309,244</td>\n",
       "      <td>3,177,507</td>\n",
       "      <td>3,011,995</td>\n",
       "      <td>2,811,931</td>\n",
       "      <td>2,706,036</td>\n",
       "      <td>2,456,917</td>\n",
       "      <td>2,334,631</td>\n",
       "      <td>2,165,526</td>\n",
       "      <td>2,087,456</td>\n",
       "      <td>1,987,343</td>\n",
       "      <td>1,870,366</td>\n",
       "      <td>1,819,696</td>\n",
       "      <td>1,786,427</td>\n",
       "      <td>1,725,912</td>\n",
       "      <td>1,692,625</td>\n",
       "      <td>1,681,782</td>\n",
       "      <td>1,668,683</td>\n",
       "      <td>1,643,256</td>\n",
       "      <td>1,612,325</td>\n",
       "      <td>1,601,550</td>\n",
       "      <td>1,592,151</td>\n",
       "      <td>1,587,164</td>\n",
       "      <td>1,582,946</td>\n",
       "      <td>1,580,811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>North East</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>313,662</td>\n",
       "      <td>307,647</td>\n",
       "      <td>302,331</td>\n",
       "      <td>297,541</td>\n",
       "      <td>292,031</td>\n",
       "      <td>286,355</td>\n",
       "      <td>276,290</td>\n",
       "      <td>233,241</td>\n",
       "      <td>226,593</td>\n",
       "      <td>207,311</td>\n",
       "      <td>189,679</td>\n",
       "      <td>169,761</td>\n",
       "      <td>165,141</td>\n",
       "      <td>154,263</td>\n",
       "      <td>139,643</td>\n",
       "      <td>129,316</td>\n",
       "      <td>128,157</td>\n",
       "      <td>116,196</td>\n",
       "      <td>115,983</td>\n",
       "      <td>112,833</td>\n",
       "      <td>112,004</td>\n",
       "      <td>112,054</td>\n",
       "      <td>92,623</td>\n",
       "      <td>91,790</td>\n",
       "      <td>91,141</td>\n",
       "      <td>90,053</td>\n",
       "      <td>89,524</td>\n",
       "      <td>89,223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0 Current\\nONS code Lower and Single Tier Authority Data  \\\n",
       "0         NaN               NaN                                  NaN   \n",
       "1     England         E92000001                                  NaN   \n",
       "2         NaN               NaN                                  NaN   \n",
       "3  North East         E12000001                                  NaN   \n",
       "4         NaN               NaN                                  NaN   \n",
       "\n",
       "        1994       1995       1996       1997       1998       1999  \\\n",
       "0        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "1  3,666,190  3,564,590  3,469,594  3,400,635  3,309,244  3,177,507   \n",
       "2        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "3    313,662    307,647    302,331    297,541    292,031    286,355   \n",
       "4        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "\n",
       "        2000       2001       2002       2003       2004       2005  \\\n",
       "0        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "1  3,011,995  2,811,931  2,706,036  2,456,917  2,334,631  2,165,526   \n",
       "2        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "3    276,290    233,241    226,593    207,311    189,679    169,761   \n",
       "4        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "\n",
       "        2006       2007       2008       2009       2010       2011  \\\n",
       "0        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "1  2,087,456  1,987,343  1,870,366  1,819,696  1,786,427  1,725,912   \n",
       "2        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "3    165,141    154,263    139,643    129,316    128,157    116,196   \n",
       "4        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "\n",
       "        2012       2013       2014       2015       2016       2017  \\\n",
       "0        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "1  1,692,625  1,681,782  1,668,683  1,643,256  1,612,325  1,601,550   \n",
       "2        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "3    115,983    112,833    112,004    112,054     92,623     91,790   \n",
       "4        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "\n",
       "        2018       2019       2020       2021  \n",
       "0        NaN        NaN        NaN        NaN  \n",
       "1  1,592,151  1,587,164  1,582,946  1,580,811  \n",
       "2        NaN        NaN        NaN        NaN  \n",
       "3     91,141     90,053     89,524     89,223  \n",
       "4        NaN        NaN        NaN        NaN  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "la_stock = pd.read_csv('Live_Table_116 - LA dwelling stock 94-21.csv', header =3)\n",
    "la_stock.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "01465376",
   "metadata": {},
   "outputs": [],
   "source": [
    "la_stock = la_stock.drop(la_stock.index[0:5])\n",
    "la_stock = la_stock.drop(columns =['Unnamed: 0'])\n",
    "la_stock = la_stock.replace('..', np.nan)\n",
    "la_stock = la_stock.rename(columns = {'Current\\nONS code': 'la_code',\n",
    "                                     'Lower and Single Tier Authority Data': 'la_name'})\n",
    "la_stock = pd.melt(la_stock, id_vars= ['la_code', 'la_name'], var_name = \"year\",value_name = 'dwell_stock')\n",
    "la_stock = la_stock.replace(\"..\", 0)\n",
    "la_stock.dropna(inplace =True)\n",
    "la_stock['dwell_stock']  = la_stock['dwell_stock'].replace({',': ''}, regex=True).astype('float')\n",
    "la_stock['year'] = la_stock['year'].astype('int64')\n",
    "la_stock['la_code'] =la_stock['la_code'].str.strip()\n",
    "la_stock['la_name'] =la_stock['la_name'].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "ac45f4d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 9555 entries, 0 to 11598\n",
      "Data columns (total 4 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   la_code      9555 non-null   object \n",
      " 1   la_name      9555 non-null   object \n",
      " 2   year         9555 non-null   int64  \n",
      " 3   dwell_stock  9555 non-null   float64\n",
      "dtypes: float64(1), int64(1), object(2)\n",
      "memory usage: 373.2+ KB\n"
     ]
    }
   ],
   "source": [
    "la_stock.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "e7636d02",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4520 entries, 0 to 4519\n",
      "Data columns (total 49 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   la_code               4520 non-null   object \n",
      " 1   la_name               4520 non-null   object \n",
      " 2   year                  4520 non-null   int64  \n",
      " 3   g_belt_area           4520 non-null   int64  \n",
      " 4   total_area            4520 non-null   int64  \n",
      " 5   rpi                   4520 non-null   float64\n",
      " 6   int_rate_max          4520 non-null   float64\n",
      " 7   int_rate_min          4520 non-null   float64\n",
      " 8   median_income         4520 non-null   int64  \n",
      " 9   all_new_sls_price     4520 non-null   int64  \n",
      " 10  det_new_sls_price     4520 non-null   int64  \n",
      " 11  semi_new_sls_price    4520 non-null   int64  \n",
      " 12  terr_new_sls_price    4520 non-null   int64  \n",
      " 13  flat_new_sls_price    4520 non-null   int64  \n",
      " 14  all_exist_sls_price   4520 non-null   int64  \n",
      " 15  det_exist_sls_price   4520 non-null   int64  \n",
      " 16  semi_exist_sls_price  4520 non-null   int64  \n",
      " 17  terr_exist_sls_price  4520 non-null   int64  \n",
      " 18  flat_exist_sls_price  4520 non-null   int64  \n",
      " 19  all_sls_price         4520 non-null   int64  \n",
      " 20  det_sls_price         4520 non-null   int64  \n",
      " 21  semi_sls_price        4520 non-null   int64  \n",
      " 22  terr_sls_price        4520 non-null   int64  \n",
      " 23  flat_sls_price        4520 non-null   int64  \n",
      " 24  all_new_sls_num       4520 non-null   int64  \n",
      " 25  det_new_sls_num       4520 non-null   int64  \n",
      " 26  semi_new_sls_num      4520 non-null   int64  \n",
      " 27  terr_new_sls_num      4520 non-null   int64  \n",
      " 28  flat_new_sls_num      4520 non-null   int64  \n",
      " 29  all_exist_sls_num     4520 non-null   int64  \n",
      " 30  det_exist_sls_num     4520 non-null   int64  \n",
      " 31  semi_exist_sls_num    4520 non-null   int64  \n",
      " 32  terr_exist_sls_num    4520 non-null   int64  \n",
      " 33  flat_exist_sls_num    4520 non-null   int64  \n",
      " 34  all_sls_num           4520 non-null   int64  \n",
      " 35  det_sls_num           4520 non-null   int64  \n",
      " 36  semi_sls_num          4520 non-null   int64  \n",
      " 37  terr_sls_num          4520 non-null   int64  \n",
      " 38  flat_sls_num          4520 non-null   int64  \n",
      " 39  population            4520 non-null   int64  \n",
      " 40  num_hh_1dp            4520 non-null   float64\n",
      " 41  num_hh_>2dp           4520 non-null   float64\n",
      " 42  num_hh_2dp            4520 non-null   float64\n",
      " 43  num_hh_1f             4520 non-null   float64\n",
      " 44  num_hh_1m             4520 non-null   float64\n",
      " 45  num_hh_oth            4520 non-null   float64\n",
      " 46  num_hh_tot            4520 non-null   float64\n",
      " 47  gdp                   4520 non-null   int64  \n",
      " 48  dwell_stock           4520 non-null   float64\n",
      "dtypes: float64(11), int64(36), object(2)\n",
      "memory usage: 1.7+ MB\n"
     ]
    }
   ],
   "source": [
    "la_file = la_file.merge(la_stock)\n",
    "la_file.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f413c1d",
   "metadata": {},
   "source": [
    "### Adding: Net Additions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "df3284d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>DCLG code</th>\n",
       "      <th>Former\\nONS code</th>\n",
       "      <th>Current\\nONS code</th>\n",
       "      <th>Met and Shire County Totals</th>\n",
       "      <th>Lower and Single Tier Authority Data</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>2001-02</th>\n",
       "      <th>2002-03</th>\n",
       "      <th>2003-04</th>\n",
       "      <th>2004-05</th>\n",
       "      <th>2005-06</th>\n",
       "      <th>2006-07</th>\n",
       "      <th>2007-08</th>\n",
       "      <th>2008-09</th>\n",
       "      <th>2009-10</th>\n",
       "      <th>2010-11</th>\n",
       "      <th>2011-12</th>\n",
       "      <th>2012-13</th>\n",
       "      <th>2013-14</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2015-16</th>\n",
       "      <th>2016-17</th>\n",
       "      <th>2017-18</th>\n",
       "      <th>2018-19</th>\n",
       "      <th>2019-20R</th>\n",
       "      <th>2020-21</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>England</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E92000001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>146,704</td>\n",
       "      <td>159,875</td>\n",
       "      <td>170,969</td>\n",
       "      <td>185,553</td>\n",
       "      <td>202,653</td>\n",
       "      <td>214,936</td>\n",
       "      <td>223,534</td>\n",
       "      <td>182,767</td>\n",
       "      <td>144,870</td>\n",
       "      <td>137,394</td>\n",
       "      <td>134,896</td>\n",
       "      <td>124,722</td>\n",
       "      <td>136,605</td>\n",
       "      <td>170,693</td>\n",
       "      <td>189,645</td>\n",
       "      <td>217,345</td>\n",
       "      <td>222,281</td>\n",
       "      <td>241,877</td>\n",
       "      <td>242,702</td>\n",
       "      <td>216,489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Unitary Authorities</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25,553</td>\n",
       "      <td>27,553</td>\n",
       "      <td>28,081</td>\n",
       "      <td>31,389</td>\n",
       "      <td>34,403</td>\n",
       "      <td>37,389</td>\n",
       "      <td>37,529</td>\n",
       "      <td>30,152</td>\n",
       "      <td>33,758</td>\n",
       "      <td>34,154</td>\n",
       "      <td>32,431</td>\n",
       "      <td>31,151</td>\n",
       "      <td>32,404</td>\n",
       "      <td>41,791</td>\n",
       "      <td>43,971</td>\n",
       "      <td>49,340</td>\n",
       "      <td>54,315</td>\n",
       "      <td>54,899</td>\n",
       "      <td>55,048</td>\n",
       "      <td>50,655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19</td>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Unnamed: 0 DCLG code Former\\nONS code Current\\nONS code  \\\n",
       "0                  NaN       NaN              NaN               NaN   \n",
       "1              England       NaN              NaN         E92000001   \n",
       "2                  NaN       NaN              NaN               NaN   \n",
       "3  Unitary Authorities       NaN              NaN               NaN   \n",
       "4                  NaN       NaN              NaN               NaN   \n",
       "\n",
       "  Met and Shire County Totals Lower and Single Tier Authority Data  \\\n",
       "0                         NaN                                  NaN   \n",
       "1                         NaN                                  NaN   \n",
       "2                         NaN                                  NaN   \n",
       "3                         NaN                                  NaN   \n",
       "4                         NaN                                  NaN   \n",
       "\n",
       "   Unnamed: 6    2001-02    2002-03    2003-04    2004-05    2005-06  \\\n",
       "0         NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "1         NaN   146,704    159,875    170,969    185,553    202,653    \n",
       "2         NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "3         NaN    25,553     27,553     28,081     31,389     34,403    \n",
       "4         NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "\n",
       "     2006-07    2007-08    2008-09    2009-10    2010-11    2011-12  \\\n",
       "0        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "1   214,936    223,534    182,767    144,870    137,394    134,896    \n",
       "2        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "3    37,389     37,529     30,152     33,758     34,154     32,431    \n",
       "4        NaN        NaN        NaN        NaN        NaN        NaN   \n",
       "\n",
       "     2012-13    2013-14    2014-15    2015-16  2016-17  2017-18  2018-19  \\\n",
       "0        NaN        NaN        NaN        NaN      NaN      NaN      NaN   \n",
       "1   124,722    136,605    170,693    189,645   217,345  222,281  241,877   \n",
       "2        NaN        NaN        NaN        NaN      NaN      NaN      NaN   \n",
       "3    31,151     32,404     41,791     43,971    49,340   54,315   54,899   \n",
       "4        NaN        NaN        NaN        NaN       19       20       21   \n",
       "\n",
       "  2019-20R  2020-21  \n",
       "0      NaN      NaN  \n",
       "1  242,702  216,489  \n",
       "2      NaN      NaN  \n",
       "3   55,048   50,655  \n",
       "4       22       23  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "net_addt = pd.read_csv('Live_Table_122 - net additions by LA.csv', header = 3)\n",
    "net_addt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "1b00886b",
   "metadata": {},
   "outputs": [],
   "source": [
    "net_addt = net_addt.drop(net_addt.index[0:5])\n",
    "net_addt = net_addt.drop(columns = ['Unnamed: 0','DCLG code', 'Former\\nONS code', 'Met and Shire County Totals', 'Unnamed: 6'])\n",
    "\n",
    "\n",
    "#create list of years for dict to change to singular year\n",
    "net_addt_col_lst = net_addt.columns.values.tolist()\n",
    "net_addt_col_lst = net_addt_col_lst[2:]\n",
    "net_addt_col_lst\n",
    "\n",
    "d = {}\n",
    "yr = 2002\n",
    "for i in net_addt_col_lst:\n",
    "    d[i] = yr\n",
    "    yr+=1    \n",
    "    \n",
    "net_addt = net_addt.rename(columns = d)   \n",
    "\n",
    "# This file has different descriptions for some la_names - suffixed with _ua. \n",
    "# Therefore named differently so not used in merge, only la_code used for the merge\n",
    "# need to remove after merge\n",
    "net_addt = net_addt.rename(columns = {'Current\\nONS code': 'la_code',\n",
    "                                     'Lower and Single Tier Authority Data': 'la_name_ua'})\n",
    "net_addt = pd.melt(net_addt, id_vars= ['la_code', 'la_name_ua'], var_name = \"year\",value_name = 'net_additions').fillna(0)\n",
    "\n",
    "# there are two rows that contain some kind of note. remove these\n",
    "# must be a better way...cannot seem to convert to int without removing them. astype doesn't have coerce as \n",
    "#argument to be able to convert errors to Nan.\n",
    "\n",
    "net_addt = net_addt.drop(net_addt[net_addt['net_additions'] == 'Latest update'].index)\n",
    "net_addt = net_addt.drop(net_addt[net_addt['net_additions'] == 'Next update'].index)\n",
    "net_addt = net_addt.drop(net_addt[net_addt['net_additions'] == '25-Nov-2021'].index)\n",
    "net_addt = net_addt.drop(net_addt[net_addt['net_additions'] == 'Nov 2022'].index)\n",
    "net_addt = net_addt.drop(net_addt[net_addt['net_additions'] == '..'].index)   \n",
    "\n",
    "net_addt['net_additions'] = net_addt['net_additions'].replace({',': ''}, regex=True).astype('int64')\n",
    "\n",
    "net_addt['year'] = net_addt['year'].astype('int64')\n",
    "net_addt['la_code'] =net_addt['la_code'].str.strip()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "d2c67aa5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012,\n",
       "       2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021], dtype=int64)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "net_addt['year'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "06fef3aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "#net_addt.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "0aa98088",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4314 entries, 0 to 4313\n",
      "Data columns (total 50 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   la_code               4314 non-null   object \n",
      " 1   la_name               4314 non-null   object \n",
      " 2   year                  4314 non-null   int64  \n",
      " 3   g_belt_area           4314 non-null   int64  \n",
      " 4   total_area            4314 non-null   int64  \n",
      " 5   rpi                   4314 non-null   float64\n",
      " 6   int_rate_max          4314 non-null   float64\n",
      " 7   int_rate_min          4314 non-null   float64\n",
      " 8   median_income         4314 non-null   int64  \n",
      " 9   all_new_sls_price     4314 non-null   int64  \n",
      " 10  det_new_sls_price     4314 non-null   int64  \n",
      " 11  semi_new_sls_price    4314 non-null   int64  \n",
      " 12  terr_new_sls_price    4314 non-null   int64  \n",
      " 13  flat_new_sls_price    4314 non-null   int64  \n",
      " 14  all_exist_sls_price   4314 non-null   int64  \n",
      " 15  det_exist_sls_price   4314 non-null   int64  \n",
      " 16  semi_exist_sls_price  4314 non-null   int64  \n",
      " 17  terr_exist_sls_price  4314 non-null   int64  \n",
      " 18  flat_exist_sls_price  4314 non-null   int64  \n",
      " 19  all_sls_price         4314 non-null   int64  \n",
      " 20  det_sls_price         4314 non-null   int64  \n",
      " 21  semi_sls_price        4314 non-null   int64  \n",
      " 22  terr_sls_price        4314 non-null   int64  \n",
      " 23  flat_sls_price        4314 non-null   int64  \n",
      " 24  all_new_sls_num       4314 non-null   int64  \n",
      " 25  det_new_sls_num       4314 non-null   int64  \n",
      " 26  semi_new_sls_num      4314 non-null   int64  \n",
      " 27  terr_new_sls_num      4314 non-null   int64  \n",
      " 28  flat_new_sls_num      4314 non-null   int64  \n",
      " 29  all_exist_sls_num     4314 non-null   int64  \n",
      " 30  det_exist_sls_num     4314 non-null   int64  \n",
      " 31  semi_exist_sls_num    4314 non-null   int64  \n",
      " 32  terr_exist_sls_num    4314 non-null   int64  \n",
      " 33  flat_exist_sls_num    4314 non-null   int64  \n",
      " 34  all_sls_num           4314 non-null   int64  \n",
      " 35  det_sls_num           4314 non-null   int64  \n",
      " 36  semi_sls_num          4314 non-null   int64  \n",
      " 37  terr_sls_num          4314 non-null   int64  \n",
      " 38  flat_sls_num          4314 non-null   int64  \n",
      " 39  population            4314 non-null   int64  \n",
      " 40  num_hh_1dp            4314 non-null   float64\n",
      " 41  num_hh_>2dp           4314 non-null   float64\n",
      " 42  num_hh_2dp            4314 non-null   float64\n",
      " 43  num_hh_1f             4314 non-null   float64\n",
      " 44  num_hh_1m             4314 non-null   float64\n",
      " 45  num_hh_oth            4314 non-null   float64\n",
      " 46  num_hh_tot            4314 non-null   float64\n",
      " 47  gdp                   4314 non-null   int64  \n",
      " 48  dwell_stock           4314 non-null   float64\n",
      " 49  net_additions         4314 non-null   int64  \n",
      "dtypes: float64(11), int64(37), object(2)\n",
      "memory usage: 1.7+ MB\n"
     ]
    }
   ],
   "source": [
    "la_file = la_file.merge(net_addt)\n",
    "la_file = la_file.drop(columns = 'la_name_ua')\n",
    "la_file.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "aa04ca96",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>la_code</th>\n",
       "      <th>la_name</th>\n",
       "      <th>year</th>\n",
       "      <th>g_belt_area</th>\n",
       "      <th>total_area</th>\n",
       "      <th>rpi</th>\n",
       "      <th>int_rate_max</th>\n",
       "      <th>int_rate_min</th>\n",
       "      <th>median_income</th>\n",
       "      <th>all_new_sls_price</th>\n",
       "      <th>det_new_sls_price</th>\n",
       "      <th>semi_new_sls_price</th>\n",
       "      <th>terr_new_sls_price</th>\n",
       "      <th>flat_new_sls_price</th>\n",
       "      <th>all_exist_sls_price</th>\n",
       "      <th>det_exist_sls_price</th>\n",
       "      <th>semi_exist_sls_price</th>\n",
       "      <th>terr_exist_sls_price</th>\n",
       "      <th>flat_exist_sls_price</th>\n",
       "      <th>all_sls_price</th>\n",
       "      <th>det_sls_price</th>\n",
       "      <th>semi_sls_price</th>\n",
       "      <th>terr_sls_price</th>\n",
       "      <th>flat_sls_price</th>\n",
       "      <th>all_new_sls_num</th>\n",
       "      <th>det_new_sls_num</th>\n",
       "      <th>semi_new_sls_num</th>\n",
       "      <th>terr_new_sls_num</th>\n",
       "      <th>flat_new_sls_num</th>\n",
       "      <th>all_exist_sls_num</th>\n",
       "      <th>det_exist_sls_num</th>\n",
       "      <th>semi_exist_sls_num</th>\n",
       "      <th>terr_exist_sls_num</th>\n",
       "      <th>flat_exist_sls_num</th>\n",
       "      <th>all_sls_num</th>\n",
       "      <th>det_sls_num</th>\n",
       "      <th>semi_sls_num</th>\n",
       "      <th>terr_sls_num</th>\n",
       "      <th>flat_sls_num</th>\n",
       "      <th>population</th>\n",
       "      <th>num_hh_1dp</th>\n",
       "      <th>num_hh_&gt;2dp</th>\n",
       "      <th>num_hh_2dp</th>\n",
       "      <th>num_hh_1f</th>\n",
       "      <th>num_hh_1m</th>\n",
       "      <th>num_hh_oth</th>\n",
       "      <th>num_hh_tot</th>\n",
       "      <th>gdp</th>\n",
       "      <th>dwell_stock</th>\n",
       "      <th>net_additions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4309</th>\n",
       "      <td>E06000054</td>\n",
       "      <td>Wiltshire</td>\n",
       "      <td>2021</td>\n",
       "      <td>6910</td>\n",
       "      <td>325530</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>29069</td>\n",
       "      <td>319995</td>\n",
       "      <td>387000</td>\n",
       "      <td>272000</td>\n",
       "      <td>234995</td>\n",
       "      <td>284500</td>\n",
       "      <td>300000</td>\n",
       "      <td>425000</td>\n",
       "      <td>274000</td>\n",
       "      <td>235000</td>\n",
       "      <td>152000</td>\n",
       "      <td>300000</td>\n",
       "      <td>420000</td>\n",
       "      <td>273000</td>\n",
       "      <td>235000</td>\n",
       "      <td>155000</td>\n",
       "      <td>784</td>\n",
       "      <td>387</td>\n",
       "      <td>246</td>\n",
       "      <td>111</td>\n",
       "      <td>40</td>\n",
       "      <td>8992</td>\n",
       "      <td>3454</td>\n",
       "      <td>2323</td>\n",
       "      <td>2440</td>\n",
       "      <td>775</td>\n",
       "      <td>9776</td>\n",
       "      <td>3841</td>\n",
       "      <td>2569</td>\n",
       "      <td>2551</td>\n",
       "      <td>815</td>\n",
       "      <td>510400</td>\n",
       "      <td>27529.944330</td>\n",
       "      <td>9144.090624</td>\n",
       "      <td>24156.774210</td>\n",
       "      <td>33613.031770</td>\n",
       "      <td>26084.193350</td>\n",
       "      <td>95427.74348</td>\n",
       "      <td>215955.77780</td>\n",
       "      <td>13123</td>\n",
       "      <td>5335.0</td>\n",
       "      <td>2846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4310</th>\n",
       "      <td>E07000094</td>\n",
       "      <td>Winchester</td>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>66100</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>33601</td>\n",
       "      <td>382500</td>\n",
       "      <td>472473</td>\n",
       "      <td>349000</td>\n",
       "      <td>341473</td>\n",
       "      <td>745000</td>\n",
       "      <td>475000</td>\n",
       "      <td>675000</td>\n",
       "      <td>412500</td>\n",
       "      <td>378265</td>\n",
       "      <td>234250</td>\n",
       "      <td>470500</td>\n",
       "      <td>665000</td>\n",
       "      <td>400000</td>\n",
       "      <td>370000</td>\n",
       "      <td>237250</td>\n",
       "      <td>155</td>\n",
       "      <td>58</td>\n",
       "      <td>41</td>\n",
       "      <td>40</td>\n",
       "      <td>16</td>\n",
       "      <td>2308</td>\n",
       "      <td>923</td>\n",
       "      <td>533</td>\n",
       "      <td>582</td>\n",
       "      <td>270</td>\n",
       "      <td>2463</td>\n",
       "      <td>981</td>\n",
       "      <td>574</td>\n",
       "      <td>622</td>\n",
       "      <td>286</td>\n",
       "      <td>127500</td>\n",
       "      <td>5926.207018</td>\n",
       "      <td>2201.551867</td>\n",
       "      <td>5788.467309</td>\n",
       "      <td>8163.926710</td>\n",
       "      <td>6045.905833</td>\n",
       "      <td>22725.37194</td>\n",
       "      <td>50851.43068</td>\n",
       "      <td>5953</td>\n",
       "      <td>5117.0</td>\n",
       "      <td>804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4311</th>\n",
       "      <td>E06000041</td>\n",
       "      <td>Wokingham</td>\n",
       "      <td>2021</td>\n",
       "      <td>2900</td>\n",
       "      <td>17900</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>38014</td>\n",
       "      <td>440000</td>\n",
       "      <td>565530</td>\n",
       "      <td>430000</td>\n",
       "      <td>425000</td>\n",
       "      <td>279000</td>\n",
       "      <td>450000</td>\n",
       "      <td>595000</td>\n",
       "      <td>425000</td>\n",
       "      <td>350000</td>\n",
       "      <td>230000</td>\n",
       "      <td>445975</td>\n",
       "      <td>590000</td>\n",
       "      <td>425000</td>\n",
       "      <td>355000</td>\n",
       "      <td>236250</td>\n",
       "      <td>319</td>\n",
       "      <td>124</td>\n",
       "      <td>117</td>\n",
       "      <td>46</td>\n",
       "      <td>32</td>\n",
       "      <td>3129</td>\n",
       "      <td>1273</td>\n",
       "      <td>881</td>\n",
       "      <td>663</td>\n",
       "      <td>312</td>\n",
       "      <td>3448</td>\n",
       "      <td>1397</td>\n",
       "      <td>998</td>\n",
       "      <td>709</td>\n",
       "      <td>344</td>\n",
       "      <td>177500</td>\n",
       "      <td>9435.057511</td>\n",
       "      <td>2765.600972</td>\n",
       "      <td>9610.106201</td>\n",
       "      <td>9370.879657</td>\n",
       "      <td>7060.580500</td>\n",
       "      <td>29136.29719</td>\n",
       "      <td>67378.52203</td>\n",
       "      <td>8544</td>\n",
       "      <td>2663.0</td>\n",
       "      <td>1129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4312</th>\n",
       "      <td>E07000238</td>\n",
       "      <td>Wychavon</td>\n",
       "      <td>2021</td>\n",
       "      <td>8860</td>\n",
       "      <td>66350</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>28865</td>\n",
       "      <td>342000</td>\n",
       "      <td>364750</td>\n",
       "      <td>255000</td>\n",
       "      <td>252500</td>\n",
       "      <td>369500</td>\n",
       "      <td>297250</td>\n",
       "      <td>407500</td>\n",
       "      <td>252500</td>\n",
       "      <td>217200</td>\n",
       "      <td>130000</td>\n",
       "      <td>302500</td>\n",
       "      <td>400000</td>\n",
       "      <td>252500</td>\n",
       "      <td>219750</td>\n",
       "      <td>137000</td>\n",
       "      <td>207</td>\n",
       "      <td>116</td>\n",
       "      <td>54</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>2270</td>\n",
       "      <td>1075</td>\n",
       "      <td>609</td>\n",
       "      <td>458</td>\n",
       "      <td>128</td>\n",
       "      <td>2477</td>\n",
       "      <td>1191</td>\n",
       "      <td>663</td>\n",
       "      <td>480</td>\n",
       "      <td>143</td>\n",
       "      <td>132500</td>\n",
       "      <td>6720.508791</td>\n",
       "      <td>1695.917715</td>\n",
       "      <td>5903.666073</td>\n",
       "      <td>8182.339119</td>\n",
       "      <td>6899.943500</td>\n",
       "      <td>27087.69718</td>\n",
       "      <td>56490.07238</td>\n",
       "      <td>3474</td>\n",
       "      <td>6.0</td>\n",
       "      <td>591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4313</th>\n",
       "      <td>E06000014</td>\n",
       "      <td>York</td>\n",
       "      <td>2021</td>\n",
       "      <td>22410</td>\n",
       "      <td>27190</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>32490</td>\n",
       "      <td>350000</td>\n",
       "      <td>420000</td>\n",
       "      <td>332745</td>\n",
       "      <td>285000</td>\n",
       "      <td>324995</td>\n",
       "      <td>272000</td>\n",
       "      <td>395000</td>\n",
       "      <td>266000</td>\n",
       "      <td>245000</td>\n",
       "      <td>175000</td>\n",
       "      <td>275000</td>\n",
       "      <td>395000</td>\n",
       "      <td>268200</td>\n",
       "      <td>246000</td>\n",
       "      <td>180000</td>\n",
       "      <td>119</td>\n",
       "      <td>28</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>65</td>\n",
       "      <td>3681</td>\n",
       "      <td>831</td>\n",
       "      <td>1209</td>\n",
       "      <td>1167</td>\n",
       "      <td>474</td>\n",
       "      <td>3800</td>\n",
       "      <td>859</td>\n",
       "      <td>1227</td>\n",
       "      <td>1175</td>\n",
       "      <td>539</td>\n",
       "      <td>202800</td>\n",
       "      <td>11058.879810</td>\n",
       "      <td>2525.976051</td>\n",
       "      <td>7466.497741</td>\n",
       "      <td>14185.253680</td>\n",
       "      <td>12707.300200</td>\n",
       "      <td>39631.93014</td>\n",
       "      <td>87575.83763</td>\n",
       "      <td>6954</td>\n",
       "      <td>7527.0</td>\n",
       "      <td>622</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        la_code     la_name  year  g_belt_area  total_area    rpi  \\\n",
       "4309  E06000054   Wiltshire  2021         6910      325530  305.0   \n",
       "4310  E07000094  Winchester  2021            0       66100  305.0   \n",
       "4311  E06000041   Wokingham  2021         2900       17900  305.0   \n",
       "4312  E07000238    Wychavon  2021         8860       66350  305.0   \n",
       "4313  E06000014        York  2021        22410       27190  305.0   \n",
       "\n",
       "      int_rate_max  int_rate_min  median_income  all_new_sls_price  \\\n",
       "4309          0.25          0.25          29069             319995   \n",
       "4310          0.25          0.25          33601             382500   \n",
       "4311          0.25          0.25          38014             440000   \n",
       "4312          0.25          0.25          28865             342000   \n",
       "4313          0.25          0.25          32490             350000   \n",
       "\n",
       "      det_new_sls_price  semi_new_sls_price  terr_new_sls_price  \\\n",
       "4309             387000              272000              234995   \n",
       "4310             472473              349000              341473   \n",
       "4311             565530              430000              425000   \n",
       "4312             364750              255000              252500   \n",
       "4313             420000              332745              285000   \n",
       "\n",
       "      flat_new_sls_price  all_exist_sls_price  det_exist_sls_price  \\\n",
       "4309              284500               300000               425000   \n",
       "4310              745000               475000               675000   \n",
       "4311              279000               450000               595000   \n",
       "4312              369500               297250               407500   \n",
       "4313              324995               272000               395000   \n",
       "\n",
       "      semi_exist_sls_price  terr_exist_sls_price  flat_exist_sls_price  \\\n",
       "4309                274000                235000                152000   \n",
       "4310                412500                378265                234250   \n",
       "4311                425000                350000                230000   \n",
       "4312                252500                217200                130000   \n",
       "4313                266000                245000                175000   \n",
       "\n",
       "      all_sls_price  det_sls_price  semi_sls_price  terr_sls_price  \\\n",
       "4309         300000         420000          273000          235000   \n",
       "4310         470500         665000          400000          370000   \n",
       "4311         445975         590000          425000          355000   \n",
       "4312         302500         400000          252500          219750   \n",
       "4313         275000         395000          268200          246000   \n",
       "\n",
       "      flat_sls_price  all_new_sls_num  det_new_sls_num  semi_new_sls_num  \\\n",
       "4309          155000              784              387               246   \n",
       "4310          237250              155               58                41   \n",
       "4311          236250              319              124               117   \n",
       "4312          137000              207              116                54   \n",
       "4313          180000              119               28                18   \n",
       "\n",
       "      terr_new_sls_num  flat_new_sls_num  all_exist_sls_num  \\\n",
       "4309               111                40               8992   \n",
       "4310                40                16               2308   \n",
       "4311                46                32               3129   \n",
       "4312                22                15               2270   \n",
       "4313                 8                65               3681   \n",
       "\n",
       "      det_exist_sls_num  semi_exist_sls_num  terr_exist_sls_num  \\\n",
       "4309               3454                2323                2440   \n",
       "4310                923                 533                 582   \n",
       "4311               1273                 881                 663   \n",
       "4312               1075                 609                 458   \n",
       "4313                831                1209                1167   \n",
       "\n",
       "      flat_exist_sls_num  all_sls_num  det_sls_num  semi_sls_num  \\\n",
       "4309                 775         9776         3841          2569   \n",
       "4310                 270         2463          981           574   \n",
       "4311                 312         3448         1397           998   \n",
       "4312                 128         2477         1191           663   \n",
       "4313                 474         3800          859          1227   \n",
       "\n",
       "      terr_sls_num  flat_sls_num  population    num_hh_1dp  num_hh_>2dp  \\\n",
       "4309          2551           815      510400  27529.944330  9144.090624   \n",
       "4310           622           286      127500   5926.207018  2201.551867   \n",
       "4311           709           344      177500   9435.057511  2765.600972   \n",
       "4312           480           143      132500   6720.508791  1695.917715   \n",
       "4313          1175           539      202800  11058.879810  2525.976051   \n",
       "\n",
       "        num_hh_2dp     num_hh_1f     num_hh_1m   num_hh_oth    num_hh_tot  \\\n",
       "4309  24156.774210  33613.031770  26084.193350  95427.74348  215955.77780   \n",
       "4310   5788.467309   8163.926710   6045.905833  22725.37194   50851.43068   \n",
       "4311   9610.106201   9370.879657   7060.580500  29136.29719   67378.52203   \n",
       "4312   5903.666073   8182.339119   6899.943500  27087.69718   56490.07238   \n",
       "4313   7466.497741  14185.253680  12707.300200  39631.93014   87575.83763   \n",
       "\n",
       "        gdp  dwell_stock  net_additions  \n",
       "4309  13123       5335.0           2846  \n",
       "4310   5953       5117.0            804  \n",
       "4311   8544       2663.0           1129  \n",
       "4312   3474          6.0            591  \n",
       "4313   6954       7527.0            622  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "la_file.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "801c9a3f",
   "metadata": {},
   "source": [
    "### Adding: Number of Households on Housing Waiting List"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "52c320eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "on_wait_lst = pd.read_csv('LT600 - No households on housing waiting list by LA.csv', header = 3)\n",
    "#on_wait_lst.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "f328ec1e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14735 entries, 0 to 14734\n",
      "Data columns (total 4 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   la_code       13230 non-null  object \n",
      " 1   la_name_      12950 non-null  object \n",
      " 2   year          14735 non-null  int64  \n",
      " 3   num_wait_lst  12301 non-null  float64\n",
      "dtypes: float64(1), int64(1), object(2)\n",
      "memory usage: 460.6+ KB\n"
     ]
    }
   ],
   "source": [
    "on_wait_lst = on_wait_lst.drop(on_wait_lst.index[0:5])\n",
    "on_wait_lst = on_wait_lst.drop(columns =['Unnamed: 0'])\n",
    "on_wait_lst = on_wait_lst.replace('..', np.nan)\n",
    "on_wait_lst = on_wait_lst.replace('.', np.nan)\n",
    "\n",
    "#There is inconsistency with la_names versus other files. And some additional codes, a few missing\n",
    "#Don't use la_name for merge. label differently and remove\n",
    "on_wait_lst = on_wait_lst.rename(columns = {'Current\\nONS code': 'la_code',\n",
    "                                     'Lower and Single Tier Authority Data': 'la_name_'})\n",
    "on_wait_lst = pd.melt(on_wait_lst, id_vars= ['la_code', 'la_name_'], var_name = \"year\",value_name = 'num_wait_lst')\n",
    "on_wait_lst['num_wait_lst']  = on_wait_lst['num_wait_lst'].replace({',': ''}, regex=True).astype('float')\n",
    "on_wait_lst['year'] = on_wait_lst['year'].astype('int64')\n",
    "on_wait_lst['la_code'] = on_wait_lst['la_code'].str.strip()\n",
    "\n",
    "\n",
    "on_wait_lst.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "c6529b8f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4314 entries, 0 to 4313\n",
      "Data columns (total 51 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   la_code               4314 non-null   object \n",
      " 1   la_name               4314 non-null   object \n",
      " 2   year                  4314 non-null   int64  \n",
      " 3   g_belt_area           4314 non-null   int64  \n",
      " 4   total_area            4314 non-null   int64  \n",
      " 5   rpi                   4314 non-null   float64\n",
      " 6   int_rate_max          4314 non-null   float64\n",
      " 7   int_rate_min          4314 non-null   float64\n",
      " 8   median_income         4314 non-null   int64  \n",
      " 9   all_new_sls_price     4314 non-null   int64  \n",
      " 10  det_new_sls_price     4314 non-null   int64  \n",
      " 11  semi_new_sls_price    4314 non-null   int64  \n",
      " 12  terr_new_sls_price    4314 non-null   int64  \n",
      " 13  flat_new_sls_price    4314 non-null   int64  \n",
      " 14  all_exist_sls_price   4314 non-null   int64  \n",
      " 15  det_exist_sls_price   4314 non-null   int64  \n",
      " 16  semi_exist_sls_price  4314 non-null   int64  \n",
      " 17  terr_exist_sls_price  4314 non-null   int64  \n",
      " 18  flat_exist_sls_price  4314 non-null   int64  \n",
      " 19  all_sls_price         4314 non-null   int64  \n",
      " 20  det_sls_price         4314 non-null   int64  \n",
      " 21  semi_sls_price        4314 non-null   int64  \n",
      " 22  terr_sls_price        4314 non-null   int64  \n",
      " 23  flat_sls_price        4314 non-null   int64  \n",
      " 24  all_new_sls_num       4314 non-null   int64  \n",
      " 25  det_new_sls_num       4314 non-null   int64  \n",
      " 26  semi_new_sls_num      4314 non-null   int64  \n",
      " 27  terr_new_sls_num      4314 non-null   int64  \n",
      " 28  flat_new_sls_num      4314 non-null   int64  \n",
      " 29  all_exist_sls_num     4314 non-null   int64  \n",
      " 30  det_exist_sls_num     4314 non-null   int64  \n",
      " 31  semi_exist_sls_num    4314 non-null   int64  \n",
      " 32  terr_exist_sls_num    4314 non-null   int64  \n",
      " 33  flat_exist_sls_num    4314 non-null   int64  \n",
      " 34  all_sls_num           4314 non-null   int64  \n",
      " 35  det_sls_num           4314 non-null   int64  \n",
      " 36  semi_sls_num          4314 non-null   int64  \n",
      " 37  terr_sls_num          4314 non-null   int64  \n",
      " 38  flat_sls_num          4314 non-null   int64  \n",
      " 39  population            4314 non-null   int64  \n",
      " 40  num_hh_1dp            4314 non-null   float64\n",
      " 41  num_hh_>2dp           4314 non-null   float64\n",
      " 42  num_hh_2dp            4314 non-null   float64\n",
      " 43  num_hh_1f             4314 non-null   float64\n",
      " 44  num_hh_1m             4314 non-null   float64\n",
      " 45  num_hh_oth            4314 non-null   float64\n",
      " 46  num_hh_tot            4314 non-null   float64\n",
      " 47  gdp                   4314 non-null   int64  \n",
      " 48  dwell_stock           4314 non-null   float64\n",
      " 49  net_additions         4314 non-null   int64  \n",
      " 50  num_wait_lst          4306 non-null   float64\n",
      "dtypes: float64(12), int64(37), object(2)\n",
      "memory usage: 1.7+ MB\n"
     ]
    }
   ],
   "source": [
    "la_file = la_file.merge(on_wait_lst)\n",
    "la_file = la_file.drop(columns = 'la_name_')\n",
    "la_file.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ca1fdca",
   "metadata": {},
   "source": [
    "### Adding: Affordable Supply"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "98e7f9ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>LA code</th>\n",
       "      <th>LA name</th>\n",
       "      <th>District code</th>\n",
       "      <th>District name</th>\n",
       "      <th>Metropolitan code</th>\n",
       "      <th>Metropolitan name</th>\n",
       "      <th>Region code</th>\n",
       "      <th>Region name</th>\n",
       "      <th>LA code 202021</th>\n",
       "      <th>LA name 202021</th>\n",
       "      <th>LA type 202021</th>\n",
       "      <th>Year</th>\n",
       "      <th>Tenure</th>\n",
       "      <th>Completions</th>\n",
       "      <th>s106</th>\n",
       "      <th>Type</th>\n",
       "      <th>Provider</th>\n",
       "      <th>LT1000</th>\n",
       "      <th>Units</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>E06</td>\n",
       "      <td>1991-92</td>\n",
       "      <td>Affordable Home Ownership</td>\n",
       "      <td>Completion</td>\n",
       "      <td>N</td>\n",
       "      <td>AQ</td>\n",
       "      <td>RP</td>\n",
       "      <td>Private Registered Provider HE/GLA funded</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>E06</td>\n",
       "      <td>1991-92</td>\n",
       "      <td>Social Rent</td>\n",
       "      <td>Completion</td>\n",
       "      <td>N</td>\n",
       "      <td>NB</td>\n",
       "      <td>LA</td>\n",
       "      <td>Local Authority HE/GLA funded</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>E06</td>\n",
       "      <td>1991-92</td>\n",
       "      <td>Social Rent</td>\n",
       "      <td>Completion</td>\n",
       "      <td>N</td>\n",
       "      <td>NB</td>\n",
       "      <td>RP</td>\n",
       "      <td>Private Registered Provider HE/GLA funded</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000001</td>\n",
       "      <td>Hartlepool</td>\n",
       "      <td>E06</td>\n",
       "      <td>1991-92</td>\n",
       "      <td>Social Rent</td>\n",
       "      <td>Completion</td>\n",
       "      <td>N</td>\n",
       "      <td>AQ</td>\n",
       "      <td>RP</td>\n",
       "      <td>Private Registered Provider HE/GLA funded</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E06000002</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E12000001</td>\n",
       "      <td>North East</td>\n",
       "      <td>E06000002</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>E06</td>\n",
       "      <td>1991-92</td>\n",
       "      <td>Affordable Home Ownership</td>\n",
       "      <td>Completion</td>\n",
       "      <td>N</td>\n",
       "      <td>NB</td>\n",
       "      <td>RP</td>\n",
       "      <td>Private Registered Provider HE/GLA funded</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     LA code        LA name District code District name Metropolitan code  \\\n",
       "0  E06000001     Hartlepool           NaN           NaN               NaN   \n",
       "1  E06000001     Hartlepool           NaN           NaN               NaN   \n",
       "2  E06000001     Hartlepool           NaN           NaN               NaN   \n",
       "3  E06000001     Hartlepool           NaN           NaN               NaN   \n",
       "4  E06000002  Middlesbrough           NaN           NaN               NaN   \n",
       "\n",
       "  Metropolitan name Region code Region name LA code 202021 LA name 202021  \\\n",
       "0               NaN   E12000001  North East      E06000001     Hartlepool   \n",
       "1               NaN   E12000001  North East      E06000001     Hartlepool   \n",
       "2               NaN   E12000001  North East      E06000001     Hartlepool   \n",
       "3               NaN   E12000001  North East      E06000001     Hartlepool   \n",
       "4               NaN   E12000001  North East      E06000002  Middlesbrough   \n",
       "\n",
       "  LA type 202021     Year                     Tenure Completions s106 Type  \\\n",
       "0            E06  1991-92  Affordable Home Ownership  Completion    N   AQ   \n",
       "1            E06  1991-92                Social Rent  Completion    N   NB   \n",
       "2            E06  1991-92                Social Rent  Completion    N   NB   \n",
       "3            E06  1991-92                Social Rent  Completion    N   AQ   \n",
       "4            E06  1991-92  Affordable Home Ownership  Completion    N   NB   \n",
       "\n",
       "  Provider                                     LT1000  Units  \n",
       "0       RP  Private Registered Provider HE/GLA funded     13  \n",
       "1       LA              Local Authority HE/GLA funded     72  \n",
       "2       RP  Private Registered Provider HE/GLA funded     57  \n",
       "3       RP  Private Registered Provider HE/GLA funded      5  \n",
       "4       RP  Private Registered Provider HE/GLA funded     18  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "afford_supply = pd.read_csv('live table 1011 data - affordable housing supply 91-21.csv')\n",
    "afford_supply.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "8a6d78e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['LA code',\n",
       " 'LA name',\n",
       " 'District code',\n",
       " 'District name',\n",
       " 'Metropolitan code',\n",
       " 'Metropolitan name',\n",
       " 'Region code',\n",
       " 'Region name',\n",
       " 'LA code 202021',\n",
       " 'LA name 202021',\n",
       " 'LA type 202021',\n",
       " 'Year',\n",
       " 'Tenure',\n",
       " 'Completions',\n",
       " 's106',\n",
       " 'Type',\n",
       " 'Provider',\n",
       " 'LT1000',\n",
       " 'Units']"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a number of features are not required. Remove the columns\n",
    "afford_col_list = afford_supply.columns.values.tolist()\n",
    "afford_col_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "32332a5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "afford_supply = afford_supply.drop(columns = [ 'District code',\n",
    "                                              'District name',\n",
    "                                              'Metropolitan code',\n",
    "                                              'Metropolitan name', \n",
    "                                              'Region code',\n",
    "                                              'Region name',\n",
    "                                              'LA code 202021',\n",
    "                                              'LA name 202021',\n",
    "                                              'LA type 202021', \n",
    "                                              'Type',\n",
    "                                              'Provider',\n",
    "                                              'LT1000',\n",
    "                                              's106'])\n",
    "\n",
    "# only require completions, as starts not actually contributing to housing needs.\n",
    "afford_supply = afford_supply.drop(afford_supply[afford_supply['Completions'] == 'Start'].index)\n",
    "\n",
    "# change year to singular number\n",
    "def yr_func(x):\n",
    "    return int(x[:4])+1\n",
    "    \n",
    "afford_supply['Year'] = afford_supply['Year'].apply(yr_func)\n",
    "\n",
    "afford_supply = afford_supply.groupby(['LA code', \n",
    "                                       'LA name',\n",
    "                                       'Year',\n",
    "                                       'Tenure'])['Units'].sum().reset_index()\n",
    "\n",
    "#for tidy data, need tenure to be a feature/column and no of units to be the measure for each type of tenure\n",
    "afford_supply = afford_supply.pivot(columns = 'Tenure', index = ['LA code', \n",
    "                                                                 'LA name',\n",
    "                                                                 'Year'], values = 'Units').fillna(0).reset_index() \n",
    "afford_supply = afford_supply.rename(columns = {'Affordable Home Ownership': 'aff_own',\n",
    "                                               'Affordable Rent': 'aff_rent',\n",
    "                                               'Intermediate Rent': 'aff_int_rent',\n",
    "                                               'London Affordable Rent': 'aff_lon_rent',\n",
    "                                               'Shared Ownership': 'aff_sh_own',\n",
    "                                               'Social Rent': 'aff_soc_rent',\n",
    "                                               'Unknown': 'aff_unkwn',\n",
    "                                               'LA code': 'la_code',\n",
    "                                               'LA name':'la_name_',\n",
    "                                               'Year': 'year'})\n",
    "\n",
    "on_wait_lst['la_code'] = on_wait_lst['la_code'].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "bb6a4129",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10205 entries, 0 to 10204\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   la_code       10205 non-null  object \n",
      " 1   la_name_      10205 non-null  object \n",
      " 2   year          10205 non-null  int64  \n",
      " 3   aff_own       10205 non-null  float64\n",
      " 4   aff_rent      10205 non-null  float64\n",
      " 5   aff_int_rent  10205 non-null  float64\n",
      " 6   aff_lon_rent  10205 non-null  float64\n",
      " 7   aff_sh_own    10205 non-null  float64\n",
      " 8   aff_soc_rent  10205 non-null  float64\n",
      " 9   aff_unkwn     10205 non-null  float64\n",
      "dtypes: float64(7), int64(1), object(2)\n",
      "memory usage: 797.4+ KB\n"
     ]
    }
   ],
   "source": [
    "afford_supply.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "5a1a0a57",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4291 entries, 0 to 4290\n",
      "Data columns (total 58 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   la_code               4291 non-null   object \n",
      " 1   la_name               4291 non-null   object \n",
      " 2   year                  4291 non-null   int64  \n",
      " 3   g_belt_area           4291 non-null   int64  \n",
      " 4   total_area            4291 non-null   int64  \n",
      " 5   rpi                   4291 non-null   float64\n",
      " 6   int_rate_max          4291 non-null   float64\n",
      " 7   int_rate_min          4291 non-null   float64\n",
      " 8   median_income         4291 non-null   int64  \n",
      " 9   all_new_sls_price     4291 non-null   int64  \n",
      " 10  det_new_sls_price     4291 non-null   int64  \n",
      " 11  semi_new_sls_price    4291 non-null   int64  \n",
      " 12  terr_new_sls_price    4291 non-null   int64  \n",
      " 13  flat_new_sls_price    4291 non-null   int64  \n",
      " 14  all_exist_sls_price   4291 non-null   int64  \n",
      " 15  det_exist_sls_price   4291 non-null   int64  \n",
      " 16  semi_exist_sls_price  4291 non-null   int64  \n",
      " 17  terr_exist_sls_price  4291 non-null   int64  \n",
      " 18  flat_exist_sls_price  4291 non-null   int64  \n",
      " 19  all_sls_price         4291 non-null   int64  \n",
      " 20  det_sls_price         4291 non-null   int64  \n",
      " 21  semi_sls_price        4291 non-null   int64  \n",
      " 22  terr_sls_price        4291 non-null   int64  \n",
      " 23  flat_sls_price        4291 non-null   int64  \n",
      " 24  all_new_sls_num       4291 non-null   int64  \n",
      " 25  det_new_sls_num       4291 non-null   int64  \n",
      " 26  semi_new_sls_num      4291 non-null   int64  \n",
      " 27  terr_new_sls_num      4291 non-null   int64  \n",
      " 28  flat_new_sls_num      4291 non-null   int64  \n",
      " 29  all_exist_sls_num     4291 non-null   int64  \n",
      " 30  det_exist_sls_num     4291 non-null   int64  \n",
      " 31  semi_exist_sls_num    4291 non-null   int64  \n",
      " 32  terr_exist_sls_num    4291 non-null   int64  \n",
      " 33  flat_exist_sls_num    4291 non-null   int64  \n",
      " 34  all_sls_num           4291 non-null   int64  \n",
      " 35  det_sls_num           4291 non-null   int64  \n",
      " 36  semi_sls_num          4291 non-null   int64  \n",
      " 37  terr_sls_num          4291 non-null   int64  \n",
      " 38  flat_sls_num          4291 non-null   int64  \n",
      " 39  population            4291 non-null   int64  \n",
      " 40  num_hh_1dp            4291 non-null   float64\n",
      " 41  num_hh_>2dp           4291 non-null   float64\n",
      " 42  num_hh_2dp            4291 non-null   float64\n",
      " 43  num_hh_1f             4291 non-null   float64\n",
      " 44  num_hh_1m             4291 non-null   float64\n",
      " 45  num_hh_oth            4291 non-null   float64\n",
      " 46  num_hh_tot            4291 non-null   float64\n",
      " 47  gdp                   4291 non-null   int64  \n",
      " 48  dwell_stock           4291 non-null   float64\n",
      " 49  net_additions         4291 non-null   int64  \n",
      " 50  num_wait_lst          4284 non-null   float64\n",
      " 51  aff_own               4291 non-null   float64\n",
      " 52  aff_rent              4291 non-null   float64\n",
      " 53  aff_int_rent          4291 non-null   float64\n",
      " 54  aff_lon_rent          4291 non-null   float64\n",
      " 55  aff_sh_own            4291 non-null   float64\n",
      " 56  aff_soc_rent          4291 non-null   float64\n",
      " 57  aff_unkwn             4291 non-null   float64\n",
      "dtypes: float64(19), int64(37), object(2)\n",
      "memory usage: 1.9+ MB\n"
     ]
    }
   ],
   "source": [
    "la_file = la_file.merge(afford_supply)\n",
    "la_file = la_file.drop(columns = 'la_name_')\n",
    "la_file.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "3710e0e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>la_code</th>\n",
       "      <th>la_name</th>\n",
       "      <th>year</th>\n",
       "      <th>g_belt_area</th>\n",
       "      <th>total_area</th>\n",
       "      <th>rpi</th>\n",
       "      <th>int_rate_max</th>\n",
       "      <th>int_rate_min</th>\n",
       "      <th>median_income</th>\n",
       "      <th>all_new_sls_price</th>\n",
       "      <th>det_new_sls_price</th>\n",
       "      <th>semi_new_sls_price</th>\n",
       "      <th>terr_new_sls_price</th>\n",
       "      <th>flat_new_sls_price</th>\n",
       "      <th>all_exist_sls_price</th>\n",
       "      <th>det_exist_sls_price</th>\n",
       "      <th>semi_exist_sls_price</th>\n",
       "      <th>terr_exist_sls_price</th>\n",
       "      <th>flat_exist_sls_price</th>\n",
       "      <th>all_sls_price</th>\n",
       "      <th>det_sls_price</th>\n",
       "      <th>semi_sls_price</th>\n",
       "      <th>terr_sls_price</th>\n",
       "      <th>flat_sls_price</th>\n",
       "      <th>all_new_sls_num</th>\n",
       "      <th>det_new_sls_num</th>\n",
       "      <th>semi_new_sls_num</th>\n",
       "      <th>terr_new_sls_num</th>\n",
       "      <th>flat_new_sls_num</th>\n",
       "      <th>all_exist_sls_num</th>\n",
       "      <th>det_exist_sls_num</th>\n",
       "      <th>semi_exist_sls_num</th>\n",
       "      <th>terr_exist_sls_num</th>\n",
       "      <th>flat_exist_sls_num</th>\n",
       "      <th>all_sls_num</th>\n",
       "      <th>det_sls_num</th>\n",
       "      <th>semi_sls_num</th>\n",
       "      <th>terr_sls_num</th>\n",
       "      <th>flat_sls_num</th>\n",
       "      <th>population</th>\n",
       "      <th>num_hh_1dp</th>\n",
       "      <th>num_hh_&gt;2dp</th>\n",
       "      <th>num_hh_2dp</th>\n",
       "      <th>num_hh_1f</th>\n",
       "      <th>num_hh_1m</th>\n",
       "      <th>num_hh_oth</th>\n",
       "      <th>num_hh_tot</th>\n",
       "      <th>gdp</th>\n",
       "      <th>dwell_stock</th>\n",
       "      <th>net_additions</th>\n",
       "      <th>num_wait_lst</th>\n",
       "      <th>aff_own</th>\n",
       "      <th>aff_rent</th>\n",
       "      <th>aff_int_rent</th>\n",
       "      <th>aff_lon_rent</th>\n",
       "      <th>aff_sh_own</th>\n",
       "      <th>aff_soc_rent</th>\n",
       "      <th>aff_unkwn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4286</th>\n",
       "      <td>E06000054</td>\n",
       "      <td>Wiltshire</td>\n",
       "      <td>2021</td>\n",
       "      <td>6910</td>\n",
       "      <td>325530</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>29069</td>\n",
       "      <td>319995</td>\n",
       "      <td>387000</td>\n",
       "      <td>272000</td>\n",
       "      <td>234995</td>\n",
       "      <td>284500</td>\n",
       "      <td>300000</td>\n",
       "      <td>425000</td>\n",
       "      <td>274000</td>\n",
       "      <td>235000</td>\n",
       "      <td>152000</td>\n",
       "      <td>300000</td>\n",
       "      <td>420000</td>\n",
       "      <td>273000</td>\n",
       "      <td>235000</td>\n",
       "      <td>155000</td>\n",
       "      <td>784</td>\n",
       "      <td>387</td>\n",
       "      <td>246</td>\n",
       "      <td>111</td>\n",
       "      <td>40</td>\n",
       "      <td>8992</td>\n",
       "      <td>3454</td>\n",
       "      <td>2323</td>\n",
       "      <td>2440</td>\n",
       "      <td>775</td>\n",
       "      <td>9776</td>\n",
       "      <td>3841</td>\n",
       "      <td>2569</td>\n",
       "      <td>2551</td>\n",
       "      <td>815</td>\n",
       "      <td>510400</td>\n",
       "      <td>27529.944330</td>\n",
       "      <td>9144.090624</td>\n",
       "      <td>24156.774210</td>\n",
       "      <td>33613.031770</td>\n",
       "      <td>26084.193350</td>\n",
       "      <td>95427.74348</td>\n",
       "      <td>215955.77780</td>\n",
       "      <td>13123</td>\n",
       "      <td>5335.0</td>\n",
       "      <td>2846</td>\n",
       "      <td>3947.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>351.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4287</th>\n",
       "      <td>E07000094</td>\n",
       "      <td>Winchester</td>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>66100</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>33601</td>\n",
       "      <td>382500</td>\n",
       "      <td>472473</td>\n",
       "      <td>349000</td>\n",
       "      <td>341473</td>\n",
       "      <td>745000</td>\n",
       "      <td>475000</td>\n",
       "      <td>675000</td>\n",
       "      <td>412500</td>\n",
       "      <td>378265</td>\n",
       "      <td>234250</td>\n",
       "      <td>470500</td>\n",
       "      <td>665000</td>\n",
       "      <td>400000</td>\n",
       "      <td>370000</td>\n",
       "      <td>237250</td>\n",
       "      <td>155</td>\n",
       "      <td>58</td>\n",
       "      <td>41</td>\n",
       "      <td>40</td>\n",
       "      <td>16</td>\n",
       "      <td>2308</td>\n",
       "      <td>923</td>\n",
       "      <td>533</td>\n",
       "      <td>582</td>\n",
       "      <td>270</td>\n",
       "      <td>2463</td>\n",
       "      <td>981</td>\n",
       "      <td>574</td>\n",
       "      <td>622</td>\n",
       "      <td>286</td>\n",
       "      <td>127500</td>\n",
       "      <td>5926.207018</td>\n",
       "      <td>2201.551867</td>\n",
       "      <td>5788.467309</td>\n",
       "      <td>8163.926710</td>\n",
       "      <td>6045.905833</td>\n",
       "      <td>22725.37194</td>\n",
       "      <td>50851.43068</td>\n",
       "      <td>5953</td>\n",
       "      <td>5117.0</td>\n",
       "      <td>804</td>\n",
       "      <td>1452.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4288</th>\n",
       "      <td>E06000041</td>\n",
       "      <td>Wokingham</td>\n",
       "      <td>2021</td>\n",
       "      <td>2900</td>\n",
       "      <td>17900</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>38014</td>\n",
       "      <td>440000</td>\n",
       "      <td>565530</td>\n",
       "      <td>430000</td>\n",
       "      <td>425000</td>\n",
       "      <td>279000</td>\n",
       "      <td>450000</td>\n",
       "      <td>595000</td>\n",
       "      <td>425000</td>\n",
       "      <td>350000</td>\n",
       "      <td>230000</td>\n",
       "      <td>445975</td>\n",
       "      <td>590000</td>\n",
       "      <td>425000</td>\n",
       "      <td>355000</td>\n",
       "      <td>236250</td>\n",
       "      <td>319</td>\n",
       "      <td>124</td>\n",
       "      <td>117</td>\n",
       "      <td>46</td>\n",
       "      <td>32</td>\n",
       "      <td>3129</td>\n",
       "      <td>1273</td>\n",
       "      <td>881</td>\n",
       "      <td>663</td>\n",
       "      <td>312</td>\n",
       "      <td>3448</td>\n",
       "      <td>1397</td>\n",
       "      <td>998</td>\n",
       "      <td>709</td>\n",
       "      <td>344</td>\n",
       "      <td>177500</td>\n",
       "      <td>9435.057511</td>\n",
       "      <td>2765.600972</td>\n",
       "      <td>9610.106201</td>\n",
       "      <td>9370.879657</td>\n",
       "      <td>7060.580500</td>\n",
       "      <td>29136.29719</td>\n",
       "      <td>67378.52203</td>\n",
       "      <td>8544</td>\n",
       "      <td>2663.0</td>\n",
       "      <td>1129</td>\n",
       "      <td>2086.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4289</th>\n",
       "      <td>E07000238</td>\n",
       "      <td>Wychavon</td>\n",
       "      <td>2021</td>\n",
       "      <td>8860</td>\n",
       "      <td>66350</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>28865</td>\n",
       "      <td>342000</td>\n",
       "      <td>364750</td>\n",
       "      <td>255000</td>\n",
       "      <td>252500</td>\n",
       "      <td>369500</td>\n",
       "      <td>297250</td>\n",
       "      <td>407500</td>\n",
       "      <td>252500</td>\n",
       "      <td>217200</td>\n",
       "      <td>130000</td>\n",
       "      <td>302500</td>\n",
       "      <td>400000</td>\n",
       "      <td>252500</td>\n",
       "      <td>219750</td>\n",
       "      <td>137000</td>\n",
       "      <td>207</td>\n",
       "      <td>116</td>\n",
       "      <td>54</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>2270</td>\n",
       "      <td>1075</td>\n",
       "      <td>609</td>\n",
       "      <td>458</td>\n",
       "      <td>128</td>\n",
       "      <td>2477</td>\n",
       "      <td>1191</td>\n",
       "      <td>663</td>\n",
       "      <td>480</td>\n",
       "      <td>143</td>\n",
       "      <td>132500</td>\n",
       "      <td>6720.508791</td>\n",
       "      <td>1695.917715</td>\n",
       "      <td>5903.666073</td>\n",
       "      <td>8182.339119</td>\n",
       "      <td>6899.943500</td>\n",
       "      <td>27087.69718</td>\n",
       "      <td>56490.07238</td>\n",
       "      <td>3474</td>\n",
       "      <td>6.0</td>\n",
       "      <td>591</td>\n",
       "      <td>1242.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>147.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4290</th>\n",
       "      <td>E06000014</td>\n",
       "      <td>York</td>\n",
       "      <td>2021</td>\n",
       "      <td>22410</td>\n",
       "      <td>27190</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>32490</td>\n",
       "      <td>350000</td>\n",
       "      <td>420000</td>\n",
       "      <td>332745</td>\n",
       "      <td>285000</td>\n",
       "      <td>324995</td>\n",
       "      <td>272000</td>\n",
       "      <td>395000</td>\n",
       "      <td>266000</td>\n",
       "      <td>245000</td>\n",
       "      <td>175000</td>\n",
       "      <td>275000</td>\n",
       "      <td>395000</td>\n",
       "      <td>268200</td>\n",
       "      <td>246000</td>\n",
       "      <td>180000</td>\n",
       "      <td>119</td>\n",
       "      <td>28</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>65</td>\n",
       "      <td>3681</td>\n",
       "      <td>831</td>\n",
       "      <td>1209</td>\n",
       "      <td>1167</td>\n",
       "      <td>474</td>\n",
       "      <td>3800</td>\n",
       "      <td>859</td>\n",
       "      <td>1227</td>\n",
       "      <td>1175</td>\n",
       "      <td>539</td>\n",
       "      <td>202800</td>\n",
       "      <td>11058.879810</td>\n",
       "      <td>2525.976051</td>\n",
       "      <td>7466.497741</td>\n",
       "      <td>14185.253680</td>\n",
       "      <td>12707.300200</td>\n",
       "      <td>39631.93014</td>\n",
       "      <td>87575.83763</td>\n",
       "      <td>6954</td>\n",
       "      <td>7527.0</td>\n",
       "      <td>622</td>\n",
       "      <td>1738.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        la_code     la_name  year  g_belt_area  total_area    rpi  \\\n",
       "4286  E06000054   Wiltshire  2021         6910      325530  305.0   \n",
       "4287  E07000094  Winchester  2021            0       66100  305.0   \n",
       "4288  E06000041   Wokingham  2021         2900       17900  305.0   \n",
       "4289  E07000238    Wychavon  2021         8860       66350  305.0   \n",
       "4290  E06000014        York  2021        22410       27190  305.0   \n",
       "\n",
       "      int_rate_max  int_rate_min  median_income  all_new_sls_price  \\\n",
       "4286          0.25          0.25          29069             319995   \n",
       "4287          0.25          0.25          33601             382500   \n",
       "4288          0.25          0.25          38014             440000   \n",
       "4289          0.25          0.25          28865             342000   \n",
       "4290          0.25          0.25          32490             350000   \n",
       "\n",
       "      det_new_sls_price  semi_new_sls_price  terr_new_sls_price  \\\n",
       "4286             387000              272000              234995   \n",
       "4287             472473              349000              341473   \n",
       "4288             565530              430000              425000   \n",
       "4289             364750              255000              252500   \n",
       "4290             420000              332745              285000   \n",
       "\n",
       "      flat_new_sls_price  all_exist_sls_price  det_exist_sls_price  \\\n",
       "4286              284500               300000               425000   \n",
       "4287              745000               475000               675000   \n",
       "4288              279000               450000               595000   \n",
       "4289              369500               297250               407500   \n",
       "4290              324995               272000               395000   \n",
       "\n",
       "      semi_exist_sls_price  terr_exist_sls_price  flat_exist_sls_price  \\\n",
       "4286                274000                235000                152000   \n",
       "4287                412500                378265                234250   \n",
       "4288                425000                350000                230000   \n",
       "4289                252500                217200                130000   \n",
       "4290                266000                245000                175000   \n",
       "\n",
       "      all_sls_price  det_sls_price  semi_sls_price  terr_sls_price  \\\n",
       "4286         300000         420000          273000          235000   \n",
       "4287         470500         665000          400000          370000   \n",
       "4288         445975         590000          425000          355000   \n",
       "4289         302500         400000          252500          219750   \n",
       "4290         275000         395000          268200          246000   \n",
       "\n",
       "      flat_sls_price  all_new_sls_num  det_new_sls_num  semi_new_sls_num  \\\n",
       "4286          155000              784              387               246   \n",
       "4287          237250              155               58                41   \n",
       "4288          236250              319              124               117   \n",
       "4289          137000              207              116                54   \n",
       "4290          180000              119               28                18   \n",
       "\n",
       "      terr_new_sls_num  flat_new_sls_num  all_exist_sls_num  \\\n",
       "4286               111                40               8992   \n",
       "4287                40                16               2308   \n",
       "4288                46                32               3129   \n",
       "4289                22                15               2270   \n",
       "4290                 8                65               3681   \n",
       "\n",
       "      det_exist_sls_num  semi_exist_sls_num  terr_exist_sls_num  \\\n",
       "4286               3454                2323                2440   \n",
       "4287                923                 533                 582   \n",
       "4288               1273                 881                 663   \n",
       "4289               1075                 609                 458   \n",
       "4290                831                1209                1167   \n",
       "\n",
       "      flat_exist_sls_num  all_sls_num  det_sls_num  semi_sls_num  \\\n",
       "4286                 775         9776         3841          2569   \n",
       "4287                 270         2463          981           574   \n",
       "4288                 312         3448         1397           998   \n",
       "4289                 128         2477         1191           663   \n",
       "4290                 474         3800          859          1227   \n",
       "\n",
       "      terr_sls_num  flat_sls_num  population    num_hh_1dp  num_hh_>2dp  \\\n",
       "4286          2551           815      510400  27529.944330  9144.090624   \n",
       "4287           622           286      127500   5926.207018  2201.551867   \n",
       "4288           709           344      177500   9435.057511  2765.600972   \n",
       "4289           480           143      132500   6720.508791  1695.917715   \n",
       "4290          1175           539      202800  11058.879810  2525.976051   \n",
       "\n",
       "        num_hh_2dp     num_hh_1f     num_hh_1m   num_hh_oth    num_hh_tot  \\\n",
       "4286  24156.774210  33613.031770  26084.193350  95427.74348  215955.77780   \n",
       "4287   5788.467309   8163.926710   6045.905833  22725.37194   50851.43068   \n",
       "4288   9610.106201   9370.879657   7060.580500  29136.29719   67378.52203   \n",
       "4289   5903.666073   8182.339119   6899.943500  27087.69718   56490.07238   \n",
       "4290   7466.497741  14185.253680  12707.300200  39631.93014   87575.83763   \n",
       "\n",
       "        gdp  dwell_stock  net_additions  num_wait_lst  aff_own  aff_rent  \\\n",
       "4286  13123       5335.0           2846        3947.0     10.0     351.0   \n",
       "4287   5953       5117.0            804        1452.0      0.0      30.0   \n",
       "4288   8544       2663.0           1129        2086.0      0.0      17.0   \n",
       "4289   3474          6.0            591        1242.0      6.0      95.0   \n",
       "4290   6954       7527.0            622        1738.0      6.0      57.0   \n",
       "\n",
       "      aff_int_rent  aff_lon_rent  aff_sh_own  aff_soc_rent  aff_unkwn  \n",
       "4286           0.0           0.0       297.0          61.0        0.0  \n",
       "4287           0.0           0.0        62.0          26.0        0.0  \n",
       "4288           0.0           0.0       193.0          92.0        0.0  \n",
       "4289           0.0           0.0        83.0         147.0        0.0  \n",
       "4290           0.0           0.0        28.0          73.0        0.0  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "la_file.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "0e51e8da",
   "metadata": {},
   "outputs": [],
   "source": [
    "la_file.to_csv('la_file.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "ccd18215",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012,\n",
       "       2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021], dtype=int64)"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "la_file['year'].unique()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}