housing_data_aggregator.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "from pathFunc import dbPath, dataDir\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#housing DF\n",
    "file_path = dataDir().joinpath('Copy of hou01tables2021.xlsx') \n",
    "housing_df_2c = pd.read_excel(file_path, sheet_name=['2c'])['2c']\n",
    "housing_df_2c = housing_df_2c.replace('c', 0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "housing_df_3c = pd.read_excel(file_path, sheet_name=['2c'])['2c']\n",
    "housing_df_3c = housing_df_2c.replace('c', 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>LSOA code</th>\n",
       "      <th>Area Name</th>\n",
       "      <th>Detached whole house or bungalow</th>\n",
       "      <th>Semi-detached whole house or bungalow</th>\n",
       "      <th>Terraced (including end-terrace) whole house or bungalow</th>\n",
       "      <th>Flat, maisonette or apartment</th>\n",
       "      <th>A caravan or other mobile or temporary structure</th>\n",
       "      <th>Proportion of indep</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E01000001</td>\n",
       "      <td>City of London 001A</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>1060</td>\n",
       "      <td>0</td>\n",
       "      <td>0.013953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E01000002</td>\n",
       "      <td>City of London 001B</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>1145</td>\n",
       "      <td>0</td>\n",
       "      <td>0.025532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E01000003</td>\n",
       "      <td>City of London 001C</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1280</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E01000005</td>\n",
       "      <td>City of London 001E</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>615</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E01000006</td>\n",
       "      <td>Barking and Dagenham 016A</td>\n",
       "      <td>35</td>\n",
       "      <td>90</td>\n",
       "      <td>300</td>\n",
       "      <td>155</td>\n",
       "      <td>0</td>\n",
       "      <td>0.732759</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   LSOA code                  Area Name  Detached whole house or bungalow  \\\n",
       "0  E01000001        City of London 001A                                 0   \n",
       "1  E01000002        City of London 001B                                 0   \n",
       "2  E01000003        City of London 001C                                 0   \n",
       "3  E01000005        City of London 001E                                 0   \n",
       "4  E01000006  Barking and Dagenham 016A                                35   \n",
       "\n",
       "   Semi-detached whole house or bungalow  \\\n",
       "0                                      0   \n",
       "1                                      0   \n",
       "2                                      0   \n",
       "3                                      0   \n",
       "4                                     90   \n",
       "\n",
       "   Terraced (including end-terrace) whole house or bungalow  \\\n",
       "0                                                 15          \n",
       "1                                                 30          \n",
       "2                                                  0          \n",
       "3                                                  0          \n",
       "4                                                300          \n",
       "\n",
       "   Flat, maisonette or apartment  \\\n",
       "0                           1060   \n",
       "1                           1145   \n",
       "2                           1280   \n",
       "3                            615   \n",
       "4                            155   \n",
       "\n",
       "   A caravan or other mobile or temporary structure  Proportion of indep  \n",
       "0                                                 0             0.013953  \n",
       "1                                                 0             0.025532  \n",
       "2                                                 0             0.000000  \n",
       "3                                                 0             0.000000  \n",
       "4                                                 0             0.732759  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "housing_df_2c['Proportion of indep']=(housing_df_2c['Detached whole house or bungalow']+housing_df_2c['Semi-detached whole house or bungalow']+housing_df_2c['Terraced (including end-terrace) whole house or bungalow'])/(housing_df_2c['Flat, maisonette or apartment']+housing_df_2c['Detached whole house or bungalow']+housing_df_2c['Semi-detached whole house or bungalow']+housing_df_2c['Terraced (including end-terrace) whole house or bungalow'])\n",
    "#adding col for ratio\n",
    "housing_df_2c=housing_df_2c.rename(columns={'Area Code': 'LSOA code'})\n",
    "housing_df_2c.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>LSOA code</th>\n",
       "      <th>Area Name</th>\n",
       "      <th>Owned or shared ownership</th>\n",
       "      <th>Private Rented or lives rent free</th>\n",
       "      <th>Social Rented</th>\n",
       "      <th>Proportion social</th>\n",
       "      <th>Proportion rented</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E01000001</td>\n",
       "      <td>City of London 001A</td>\n",
       "      <td>485</td>\n",
       "      <td>330</td>\n",
       "      <td>20</td>\n",
       "      <td>0.023952</td>\n",
       "      <td>0.419162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E01000002</td>\n",
       "      <td>City of London 001B</td>\n",
       "      <td>435</td>\n",
       "      <td>365</td>\n",
       "      <td>25</td>\n",
       "      <td>0.030303</td>\n",
       "      <td>0.472727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E01000003</td>\n",
       "      <td>City of London 001C</td>\n",
       "      <td>380</td>\n",
       "      <td>335</td>\n",
       "      <td>300</td>\n",
       "      <td>0.295567</td>\n",
       "      <td>0.625616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E01000005</td>\n",
       "      <td>City of London 001E</td>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>335</td>\n",
       "      <td>0.697917</td>\n",
       "      <td>0.916667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E01000006</td>\n",
       "      <td>Barking and Dagenham 016A</td>\n",
       "      <td>275</td>\n",
       "      <td>255</td>\n",
       "      <td>25</td>\n",
       "      <td>0.045045</td>\n",
       "      <td>0.504505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35667</th>\n",
       "      <td>W01002036</td>\n",
       "      <td>Vale of Glamorgan 005G</td>\n",
       "      <td>455</td>\n",
       "      <td>205</td>\n",
       "      <td>110</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.409091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35668</th>\n",
       "      <td>W01002037</td>\n",
       "      <td>Vale of Glamorgan 005H</td>\n",
       "      <td>340</td>\n",
       "      <td>185</td>\n",
       "      <td>40</td>\n",
       "      <td>0.070796</td>\n",
       "      <td>0.398230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35669</th>\n",
       "      <td>W01002038</td>\n",
       "      <td>Vale of Glamorgan 014G</td>\n",
       "      <td>410</td>\n",
       "      <td>90</td>\n",
       "      <td>80</td>\n",
       "      <td>0.137931</td>\n",
       "      <td>0.293103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35670</th>\n",
       "      <td>W01002039</td>\n",
       "      <td>Vale of Glamorgan 014H</td>\n",
       "      <td>535</td>\n",
       "      <td>55</td>\n",
       "      <td>80</td>\n",
       "      <td>0.119403</td>\n",
       "      <td>0.201493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35671</th>\n",
       "      <td>W01002040</td>\n",
       "      <td>Vale of Glamorgan 015F</td>\n",
       "      <td>650</td>\n",
       "      <td>135</td>\n",
       "      <td>120</td>\n",
       "      <td>0.132597</td>\n",
       "      <td>0.281768</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>35672 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       LSOA code                  Area Name  Owned or shared ownership  \\\n",
       "0      E01000001        City of London 001A                        485   \n",
       "1      E01000002        City of London 001B                        435   \n",
       "2      E01000003        City of London 001C                        380   \n",
       "3      E01000005        City of London 001E                         40   \n",
       "4      E01000006  Barking and Dagenham 016A                        275   \n",
       "...          ...                        ...                        ...   \n",
       "35667  W01002036     Vale of Glamorgan 005G                        455   \n",
       "35668  W01002037     Vale of Glamorgan 005H                        340   \n",
       "35669  W01002038     Vale of Glamorgan 014G                        410   \n",
       "35670  W01002039     Vale of Glamorgan 014H                        535   \n",
       "35671  W01002040     Vale of Glamorgan 015F                        650   \n",
       "\n",
       "       Private Rented or lives rent free  Social Rented  Proportion social  \\\n",
       "0                                    330             20           0.023952   \n",
       "1                                    365             25           0.030303   \n",
       "2                                    335            300           0.295567   \n",
       "3                                    105            335           0.697917   \n",
       "4                                    255             25           0.045045   \n",
       "...                                  ...            ...                ...   \n",
       "35667                                205            110           0.142857   \n",
       "35668                                185             40           0.070796   \n",
       "35669                                 90             80           0.137931   \n",
       "35670                                 55             80           0.119403   \n",
       "35671                                135            120           0.132597   \n",
       "\n",
       "       Proportion rented  \n",
       "0               0.419162  \n",
       "1               0.472727  \n",
       "2               0.625616  \n",
       "3               0.916667  \n",
       "4               0.504505  \n",
       "...                  ...  \n",
       "35667           0.409091  \n",
       "35668           0.398230  \n",
       "35669           0.293103  \n",
       "35670           0.201493  \n",
       "35671           0.281768  \n",
       "\n",
       "[35672 rows x 7 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "housing_df_3c = pd.read_excel(file_path, sheet_name=['3c'])['3c']\n",
    "housing_df_3c = housing_df_3c.replace('c', 0)\n",
    "housing_df_3c=housing_df_3c.rename(columns={'Area Code': 'LSOA code'})\n",
    "housing_df_3c['Proportion social']=housing_df_3c['Social Rented']/(housing_df_3c['Social Rented']+housing_df_3c['Private Rented or lives rent free']+housing_df_3c['Owned or shared ownership'])\n",
    "housing_df_3c['Proportion rented']=(housing_df_3c['Social Rented']+housing_df_3c['Private Rented or lives rent free'])/(housing_df_3c['Social Rented']+housing_df_3c['Private Rented or lives rent free']+housing_df_3c['Owned or shared ownership'])\n",
    "housing_df_3c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>LSOA code</th>\n",
       "      <th>Area Name</th>\n",
       "      <th>Tenure</th>\n",
       "      <th>Detached whole house or bungalow</th>\n",
       "      <th>Semi-detached whole house or bungalow</th>\n",
       "      <th>Terraced (including end-terrace) whole house or bungalow</th>\n",
       "      <th>Flat, maisonette or apartment</th>\n",
       "      <th>A caravan or other mobile or temporary structure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E01000001</td>\n",
       "      <td>City of London 001A</td>\n",
       "      <td>Owned or shared ownership</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>475</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E01000001</td>\n",
       "      <td>City of London 001A</td>\n",
       "      <td>Social Rented</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E01000001</td>\n",
       "      <td>City of London 001A</td>\n",
       "      <td>Private Rented or lives rent free</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>325</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E01000002</td>\n",
       "      <td>City of London 001B</td>\n",
       "      <td>Owned or shared ownership</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>410</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E01000002</td>\n",
       "      <td>City of London 001B</td>\n",
       "      <td>Social Rented</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107011</th>\n",
       "      <td>W01002039</td>\n",
       "      <td>Vale of Glamorgan 014H</td>\n",
       "      <td>Social Rented</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>25</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107012</th>\n",
       "      <td>W01002039</td>\n",
       "      <td>Vale of Glamorgan 014H</td>\n",
       "      <td>Private Rented or lives rent free</td>\n",
       "      <td>35</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107013</th>\n",
       "      <td>W01002040</td>\n",
       "      <td>Vale of Glamorgan 015F</td>\n",
       "      <td>Owned or shared ownership</td>\n",
       "      <td>45</td>\n",
       "      <td>160</td>\n",
       "      <td>350</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107014</th>\n",
       "      <td>W01002040</td>\n",
       "      <td>Vale of Glamorgan 015F</td>\n",
       "      <td>Social Rented</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>40</td>\n",
       "      <td>55</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107015</th>\n",
       "      <td>W01002040</td>\n",
       "      <td>Vale of Glamorgan 015F</td>\n",
       "      <td>Private Rented or lives rent free</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>107016 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        LSOA code               Area Name                             Tenure  \\\n",
       "0       E01000001     City of London 001A          Owned or shared ownership   \n",
       "1       E01000001     City of London 001A                      Social Rented   \n",
       "2       E01000001     City of London 001A  Private Rented or lives rent free   \n",
       "3       E01000002     City of London 001B          Owned or shared ownership   \n",
       "4       E01000002     City of London 001B                      Social Rented   \n",
       "...           ...                     ...                                ...   \n",
       "107011  W01002039  Vale of Glamorgan 014H                      Social Rented   \n",
       "107012  W01002039  Vale of Glamorgan 014H  Private Rented or lives rent free   \n",
       "107013  W01002040  Vale of Glamorgan 015F          Owned or shared ownership   \n",
       "107014  W01002040  Vale of Glamorgan 015F                      Social Rented   \n",
       "107015  W01002040  Vale of Glamorgan 015F  Private Rented or lives rent free   \n",
       "\n",
       "        Detached whole house or bungalow  \\\n",
       "0                                      0   \n",
       "1                                      0   \n",
       "2                                      0   \n",
       "3                                      0   \n",
       "4                                      0   \n",
       "...                                  ...   \n",
       "107011                                 0   \n",
       "107012                                35   \n",
       "107013                                45   \n",
       "107014                                 0   \n",
       "107015                                 0   \n",
       "\n",
       "        Semi-detached whole house or bungalow  \\\n",
       "0                                           0   \n",
       "1                                           0   \n",
       "2                                           0   \n",
       "3                                           0   \n",
       "4                                           0   \n",
       "...                                       ...   \n",
       "107011                                     35   \n",
       "107012                                     15   \n",
       "107013                                    160   \n",
       "107014                                     20   \n",
       "107015                                     20   \n",
       "\n",
       "        Terraced (including end-terrace) whole house or bungalow  \\\n",
       "0                                                       0          \n",
       "1                                                       0          \n",
       "2                                                       0          \n",
       "3                                                      20          \n",
       "4                                                       0          \n",
       "...                                                   ...          \n",
       "107011                                                 25          \n",
       "107012                                                  0          \n",
       "107013                                                350          \n",
       "107014                                                 40          \n",
       "107015                                                 55          \n",
       "\n",
       "        Flat, maisonette or apartment  \\\n",
       "0                                 475   \n",
       "1                                  20   \n",
       "2                                 325   \n",
       "3                                 410   \n",
       "4                                  25   \n",
       "...                               ...   \n",
       "107011                             20   \n",
       "107012                              0   \n",
       "107013                             90   \n",
       "107014                             55   \n",
       "107015                             55   \n",
       "\n",
       "        A caravan or other mobile or temporary structure  \n",
       "0                                                      0  \n",
       "1                                                      0  \n",
       "2                                                      0  \n",
       "3                                                      0  \n",
       "4                                                      0  \n",
       "...                                                  ...  \n",
       "107011                                                 0  \n",
       "107012                                                 0  \n",
       "107013                                                 0  \n",
       "107014                                                 0  \n",
       "107015                                                 0  \n",
       "\n",
       "[107016 rows x 8 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "housing_df_4c = pd.read_excel(file_path, sheet_name=['4c'])['4c']\n",
    "housing_df_4c = housing_df_4c.replace('c', 0)\n",
    "housing_df_4c=housing_df_4c.rename(columns={'Area Code': 'LSOA code'})\n",
    "housing_df_4c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>LSOA code</th>\n",
       "      <th>Area Name</th>\n",
       "      <th>1 bedroom</th>\n",
       "      <th>2 bedrooms</th>\n",
       "      <th>3 bedrooms</th>\n",
       "      <th>4 or more bedrooms</th>\n",
       "      <th>Missing</th>\n",
       "      <th>Proportion small house</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E01000001</td>\n",
       "      <td>City of London 001A</td>\n",
       "      <td>375</td>\n",
       "      <td>280</td>\n",
       "      <td>140</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0.779762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E01000002</td>\n",
       "      <td>City of London 001B</td>\n",
       "      <td>405</td>\n",
       "      <td>265</td>\n",
       "      <td>100</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>0.817073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E01000003</td>\n",
       "      <td>City of London 001C</td>\n",
       "      <td>650</td>\n",
       "      <td>300</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.940594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E01000005</td>\n",
       "      <td>City of London 001E</td>\n",
       "      <td>240</td>\n",
       "      <td>135</td>\n",
       "      <td>95</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.797872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E01000006</td>\n",
       "      <td>Barking and Dagenham 016A</td>\n",
       "      <td>95</td>\n",
       "      <td>70</td>\n",
       "      <td>255</td>\n",
       "      <td>130</td>\n",
       "      <td>0</td>\n",
       "      <td>0.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35667</th>\n",
       "      <td>W01002036</td>\n",
       "      <td>Vale of Glamorgan 005G</td>\n",
       "      <td>95</td>\n",
       "      <td>370</td>\n",
       "      <td>200</td>\n",
       "      <td>105</td>\n",
       "      <td>0</td>\n",
       "      <td>0.603896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35668</th>\n",
       "      <td>W01002037</td>\n",
       "      <td>Vale of Glamorgan 005H</td>\n",
       "      <td>105</td>\n",
       "      <td>215</td>\n",
       "      <td>155</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0.566372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35669</th>\n",
       "      <td>W01002038</td>\n",
       "      <td>Vale of Glamorgan 014G</td>\n",
       "      <td>30</td>\n",
       "      <td>210</td>\n",
       "      <td>230</td>\n",
       "      <td>110</td>\n",
       "      <td>0</td>\n",
       "      <td>0.413793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35670</th>\n",
       "      <td>W01002039</td>\n",
       "      <td>Vale of Glamorgan 014H</td>\n",
       "      <td>20</td>\n",
       "      <td>85</td>\n",
       "      <td>150</td>\n",
       "      <td>410</td>\n",
       "      <td>0</td>\n",
       "      <td>0.157895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35671</th>\n",
       "      <td>W01002040</td>\n",
       "      <td>Vale of Glamorgan 015F</td>\n",
       "      <td>80</td>\n",
       "      <td>375</td>\n",
       "      <td>295</td>\n",
       "      <td>150</td>\n",
       "      <td>0</td>\n",
       "      <td>0.505556</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>35672 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       LSOA code                  Area Name  1 bedroom  2 bedrooms  \\\n",
       "0      E01000001        City of London 001A        375         280   \n",
       "1      E01000002        City of London 001B        405         265   \n",
       "2      E01000003        City of London 001C        650         300   \n",
       "3      E01000005        City of London 001E        240         135   \n",
       "4      E01000006  Barking and Dagenham 016A         95          70   \n",
       "...          ...                        ...        ...         ...   \n",
       "35667  W01002036     Vale of Glamorgan 005G         95         370   \n",
       "35668  W01002037     Vale of Glamorgan 005H        105         215   \n",
       "35669  W01002038     Vale of Glamorgan 014G         30         210   \n",
       "35670  W01002039     Vale of Glamorgan 014H         20          85   \n",
       "35671  W01002040     Vale of Glamorgan 015F         80         375   \n",
       "\n",
       "       3 bedrooms  4 or more bedrooms  Missing  Proportion small house  \n",
       "0             140                  45        0                0.779762  \n",
       "1             100                  50        0                0.817073  \n",
       "2              60                   0        0                0.940594  \n",
       "3              95                   0        0                0.797872  \n",
       "4             255                 130        0                0.300000  \n",
       "...           ...                 ...      ...                     ...  \n",
       "35667         200                 105        0                0.603896  \n",
       "35668         155                  90        0                0.566372  \n",
       "35669         230                 110        0                0.413793  \n",
       "35670         150                 410        0                0.157895  \n",
       "35671         295                 150        0                0.505556  \n",
       "\n",
       "[35672 rows x 8 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "housing_df_7c = pd.read_excel(file_path, sheet_name=['7c'])['7c']\n",
    "housing_df_7c = housing_df_7c.replace('c', 0)\n",
    "housing_df_7c=housing_df_7c.rename(columns={'Area Code': 'LSOA code'})\n",
    "housing_df_7c['Proportion small house']=(housing_df_7c['1 bedroom']+housing_df_7c['2 bedrooms'])/(housing_df_7c['1 bedroom']+housing_df_7c['2 bedrooms']+housing_df_7c['3 bedrooms']+housing_df_7c['4 or more bedrooms'])\n",
    "housing_df_7c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>LSOA code</th>\n",
       "      <th>LSOA name</th>\n",
       "      <th>Owned or shared ownership</th>\n",
       "      <th>Private Rented or lives rent free</th>\n",
       "      <th>Social Rented</th>\n",
       "      <th>Proportion social</th>\n",
       "      <th>Proportion rented</th>\n",
       "      <th>Detached whole house or bungalow</th>\n",
       "      <th>Semi-detached whole house or bungalow</th>\n",
       "      <th>Terraced (including end-terrace) whole house or bungalow</th>\n",
       "      <th>Flat, maisonette or apartment</th>\n",
       "      <th>A caravan or other mobile or temporary structure</th>\n",
       "      <th>Proportion of indep</th>\n",
       "      <th>1 bedroom</th>\n",
       "      <th>2 bedrooms</th>\n",
       "      <th>3 bedrooms</th>\n",
       "      <th>4 or more bedrooms</th>\n",
       "      <th>Missing</th>\n",
       "      <th>Proportion small house</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E01000001</td>\n",
       "      <td>City of London 001A</td>\n",
       "      <td>485</td>\n",
       "      <td>330</td>\n",
       "      <td>20</td>\n",
       "      <td>0.023952</td>\n",
       "      <td>0.419162</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>1060</td>\n",
       "      <td>0</td>\n",
       "      <td>0.013953</td>\n",
       "      <td>375</td>\n",
       "      <td>280</td>\n",
       "      <td>140</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0.779762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E01000002</td>\n",
       "      <td>City of London 001B</td>\n",
       "      <td>435</td>\n",
       "      <td>365</td>\n",
       "      <td>25</td>\n",
       "      <td>0.030303</td>\n",
       "      <td>0.472727</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>1145</td>\n",
       "      <td>0</td>\n",
       "      <td>0.025532</td>\n",
       "      <td>405</td>\n",
       "      <td>265</td>\n",
       "      <td>100</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>0.817073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E01000003</td>\n",
       "      <td>City of London 001C</td>\n",
       "      <td>380</td>\n",
       "      <td>335</td>\n",
       "      <td>300</td>\n",
       "      <td>0.295567</td>\n",
       "      <td>0.625616</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1280</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>650</td>\n",
       "      <td>300</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.940594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E01000005</td>\n",
       "      <td>City of London 001E</td>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>335</td>\n",
       "      <td>0.697917</td>\n",
       "      <td>0.916667</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>615</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>240</td>\n",
       "      <td>135</td>\n",
       "      <td>95</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.797872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E01000006</td>\n",
       "      <td>Barking and Dagenham 016A</td>\n",
       "      <td>275</td>\n",
       "      <td>255</td>\n",
       "      <td>25</td>\n",
       "      <td>0.045045</td>\n",
       "      <td>0.504505</td>\n",
       "      <td>35</td>\n",
       "      <td>90</td>\n",
       "      <td>300</td>\n",
       "      <td>155</td>\n",
       "      <td>0</td>\n",
       "      <td>0.732759</td>\n",
       "      <td>95</td>\n",
       "      <td>70</td>\n",
       "      <td>255</td>\n",
       "      <td>130</td>\n",
       "      <td>0</td>\n",
       "      <td>0.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35667</th>\n",
       "      <td>W01002036</td>\n",
       "      <td>Vale of Glamorgan 005G</td>\n",
       "      <td>455</td>\n",
       "      <td>205</td>\n",
       "      <td>110</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.409091</td>\n",
       "      <td>25</td>\n",
       "      <td>55</td>\n",
       "      <td>300</td>\n",
       "      <td>455</td>\n",
       "      <td>0</td>\n",
       "      <td>0.455090</td>\n",
       "      <td>95</td>\n",
       "      <td>370</td>\n",
       "      <td>200</td>\n",
       "      <td>105</td>\n",
       "      <td>0</td>\n",
       "      <td>0.603896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35668</th>\n",
       "      <td>W01002037</td>\n",
       "      <td>Vale of Glamorgan 005H</td>\n",
       "      <td>340</td>\n",
       "      <td>185</td>\n",
       "      <td>40</td>\n",
       "      <td>0.070796</td>\n",
       "      <td>0.398230</td>\n",
       "      <td>0</td>\n",
       "      <td>55</td>\n",
       "      <td>345</td>\n",
       "      <td>215</td>\n",
       "      <td>10</td>\n",
       "      <td>0.650407</td>\n",
       "      <td>105</td>\n",
       "      <td>215</td>\n",
       "      <td>155</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0.566372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35669</th>\n",
       "      <td>W01002038</td>\n",
       "      <td>Vale of Glamorgan 014G</td>\n",
       "      <td>410</td>\n",
       "      <td>90</td>\n",
       "      <td>80</td>\n",
       "      <td>0.137931</td>\n",
       "      <td>0.293103</td>\n",
       "      <td>120</td>\n",
       "      <td>210</td>\n",
       "      <td>130</td>\n",
       "      <td>145</td>\n",
       "      <td>0</td>\n",
       "      <td>0.760331</td>\n",
       "      <td>30</td>\n",
       "      <td>210</td>\n",
       "      <td>230</td>\n",
       "      <td>110</td>\n",
       "      <td>0</td>\n",
       "      <td>0.413793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35670</th>\n",
       "      <td>W01002039</td>\n",
       "      <td>Vale of Glamorgan 014H</td>\n",
       "      <td>535</td>\n",
       "      <td>55</td>\n",
       "      <td>80</td>\n",
       "      <td>0.119403</td>\n",
       "      <td>0.201493</td>\n",
       "      <td>455</td>\n",
       "      <td>115</td>\n",
       "      <td>90</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0.963504</td>\n",
       "      <td>20</td>\n",
       "      <td>85</td>\n",
       "      <td>150</td>\n",
       "      <td>410</td>\n",
       "      <td>0</td>\n",
       "      <td>0.157895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35671</th>\n",
       "      <td>W01002040</td>\n",
       "      <td>Vale of Glamorgan 015F</td>\n",
       "      <td>650</td>\n",
       "      <td>135</td>\n",
       "      <td>120</td>\n",
       "      <td>0.132597</td>\n",
       "      <td>0.281768</td>\n",
       "      <td>50</td>\n",
       "      <td>205</td>\n",
       "      <td>460</td>\n",
       "      <td>235</td>\n",
       "      <td>0</td>\n",
       "      <td>0.752632</td>\n",
       "      <td>80</td>\n",
       "      <td>375</td>\n",
       "      <td>295</td>\n",
       "      <td>150</td>\n",
       "      <td>0</td>\n",
       "      <td>0.505556</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>35672 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       LSOA code                  LSOA name  Owned or shared ownership  \\\n",
       "0      E01000001        City of London 001A                        485   \n",
       "1      E01000002        City of London 001B                        435   \n",
       "2      E01000003        City of London 001C                        380   \n",
       "3      E01000005        City of London 001E                         40   \n",
       "4      E01000006  Barking and Dagenham 016A                        275   \n",
       "...          ...                        ...                        ...   \n",
       "35667  W01002036     Vale of Glamorgan 005G                        455   \n",
       "35668  W01002037     Vale of Glamorgan 005H                        340   \n",
       "35669  W01002038     Vale of Glamorgan 014G                        410   \n",
       "35670  W01002039     Vale of Glamorgan 014H                        535   \n",
       "35671  W01002040     Vale of Glamorgan 015F                        650   \n",
       "\n",
       "       Private Rented or lives rent free  Social Rented  Proportion social  \\\n",
       "0                                    330             20           0.023952   \n",
       "1                                    365             25           0.030303   \n",
       "2                                    335            300           0.295567   \n",
       "3                                    105            335           0.697917   \n",
       "4                                    255             25           0.045045   \n",
       "...                                  ...            ...                ...   \n",
       "35667                                205            110           0.142857   \n",
       "35668                                185             40           0.070796   \n",
       "35669                                 90             80           0.137931   \n",
       "35670                                 55             80           0.119403   \n",
       "35671                                135            120           0.132597   \n",
       "\n",
       "       Proportion rented  Detached whole house or bungalow  \\\n",
       "0               0.419162                                 0   \n",
       "1               0.472727                                 0   \n",
       "2               0.625616                                 0   \n",
       "3               0.916667                                 0   \n",
       "4               0.504505                                35   \n",
       "...                  ...                               ...   \n",
       "35667           0.409091                                25   \n",
       "35668           0.398230                                 0   \n",
       "35669           0.293103                               120   \n",
       "35670           0.201493                               455   \n",
       "35671           0.281768                                50   \n",
       "\n",
       "       Semi-detached whole house or bungalow  \\\n",
       "0                                          0   \n",
       "1                                          0   \n",
       "2                                          0   \n",
       "3                                          0   \n",
       "4                                         90   \n",
       "...                                      ...   \n",
       "35667                                     55   \n",
       "35668                                     55   \n",
       "35669                                    210   \n",
       "35670                                    115   \n",
       "35671                                    205   \n",
       "\n",
       "       Terraced (including end-terrace) whole house or bungalow  \\\n",
       "0                                                     15          \n",
       "1                                                     30          \n",
       "2                                                      0          \n",
       "3                                                      0          \n",
       "4                                                    300          \n",
       "...                                                  ...          \n",
       "35667                                                300          \n",
       "35668                                                345          \n",
       "35669                                                130          \n",
       "35670                                                 90          \n",
       "35671                                                460          \n",
       "\n",
       "       Flat, maisonette or apartment  \\\n",
       "0                               1060   \n",
       "1                               1145   \n",
       "2                               1280   \n",
       "3                                615   \n",
       "4                                155   \n",
       "...                              ...   \n",
       "35667                            455   \n",
       "35668                            215   \n",
       "35669                            145   \n",
       "35670                             25   \n",
       "35671                            235   \n",
       "\n",
       "       A caravan or other mobile or temporary structure  Proportion of indep  \\\n",
       "0                                                     0             0.013953   \n",
       "1                                                     0             0.025532   \n",
       "2                                                     0             0.000000   \n",
       "3                                                     0             0.000000   \n",
       "4                                                     0             0.732759   \n",
       "...                                                 ...                  ...   \n",
       "35667                                                 0             0.455090   \n",
       "35668                                                10             0.650407   \n",
       "35669                                                 0             0.760331   \n",
       "35670                                                 0             0.963504   \n",
       "35671                                                 0             0.752632   \n",
       "\n",
       "       1 bedroom  2 bedrooms  3 bedrooms  4 or more bedrooms  Missing  \\\n",
       "0            375         280         140                  45        0   \n",
       "1            405         265         100                  50        0   \n",
       "2            650         300          60                   0        0   \n",
       "3            240         135          95                   0        0   \n",
       "4             95          70         255                 130        0   \n",
       "...          ...         ...         ...                 ...      ...   \n",
       "35667         95         370         200                 105        0   \n",
       "35668        105         215         155                  90        0   \n",
       "35669         30         210         230                 110        0   \n",
       "35670         20          85         150                 410        0   \n",
       "35671         80         375         295                 150        0   \n",
       "\n",
       "       Proportion small house  \n",
       "0                    0.779762  \n",
       "1                    0.817073  \n",
       "2                    0.940594  \n",
       "3                    0.797872  \n",
       "4                    0.300000  \n",
       "...                       ...  \n",
       "35667                0.603896  \n",
       "35668                0.566372  \n",
       "35669                0.413793  \n",
       "35670                0.157895  \n",
       "35671                0.505556  \n",
       "\n",
       "[35672 rows x 19 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df_temp = pd.merge(housing_df_3c, housing_df_2c, on=['LSOA code', 'Area Name'], how='outer')\n",
    "merged_df = pd.merge(merged_df_temp, housing_df_7c, on=['LSOA code', 'Area Name'], how='outer')\n",
    "merged_df=merged_df.rename({\"Area Name\":\"LSOA name\"}, axis=1)\n",
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df.to_excel(dataDir().joinpath('housing_combined.xlsx'), index=False)"
   ]
  }
 ],
 "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.10.11"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}