notebooks/04_Melbourne_households.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "diagnostic-round",
   "metadata": {},
   "source": [
    "## Melbourne households"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "spread-vegetarian",
   "metadata": {},
   "source": [
    "Collect the number of households and population in each postcodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "frequent-surveillance",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "twenty-agency",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Load population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "pacific-wheel",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698, 141)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Place of residence\n",
    "poa_residence = pd.read_csv(\"../data/raw/2016 Census GCP Postal Areas for VIC/2016Census_G03_VIC_POA.csv\", low_memory=False)\n",
    "poa_residence.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "powered-oracle",
   "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>POA_CODE_2016</th>\n",
       "      <th>Count_home_Census_Nt_0_14_yr</th>\n",
       "      <th>Count_hom_Census_Nt_15_24_yr</th>\n",
       "      <th>Count_hom_Census_Nt_25_34_yr</th>\n",
       "      <th>Count_hom_Census_Nt_35_44_yr</th>\n",
       "      <th>Count_hom_Census_Nt_45_54_yr</th>\n",
       "      <th>Count_hom_Census_Nt_55_64_yr</th>\n",
       "      <th>Count_hom_Census_Nt_65_74_yr</th>\n",
       "      <th>Count_hom_Census_Nt_75_84_yr</th>\n",
       "      <th>Count_hom_Census_Nt_85ov</th>\n",
       "      <th>...</th>\n",
       "      <th>Total_0_14_yr</th>\n",
       "      <th>Total_15_24_yr</th>\n",
       "      <th>Total_25_34_yr</th>\n",
       "      <th>Total_35_44_yr</th>\n",
       "      <th>Total_45_54_yr</th>\n",
       "      <th>Total_55_64_yr</th>\n",
       "      <th>Total_65_74_yr</th>\n",
       "      <th>Total_75_84_yr</th>\n",
       "      <th>Total_85ov</th>\n",
       "      <th>Total_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POA3000</td>\n",
       "      <td>1183</td>\n",
       "      <td>15064</td>\n",
       "      <td>13255</td>\n",
       "      <td>3437</td>\n",
       "      <td>1683</td>\n",
       "      <td>1188</td>\n",
       "      <td>671</td>\n",
       "      <td>179</td>\n",
       "      <td>69</td>\n",
       "      <td>...</td>\n",
       "      <td>1495</td>\n",
       "      <td>16540</td>\n",
       "      <td>15606</td>\n",
       "      <td>5657</td>\n",
       "      <td>4342</td>\n",
       "      <td>3378</td>\n",
       "      <td>1691</td>\n",
       "      <td>396</td>\n",
       "      <td>96</td>\n",
       "      <td>49192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POA3002</td>\n",
       "      <td>284</td>\n",
       "      <td>398</td>\n",
       "      <td>1341</td>\n",
       "      <td>694</td>\n",
       "      <td>541</td>\n",
       "      <td>614</td>\n",
       "      <td>494</td>\n",
       "      <td>171</td>\n",
       "      <td>136</td>\n",
       "      <td>...</td>\n",
       "      <td>375</td>\n",
       "      <td>516</td>\n",
       "      <td>1616</td>\n",
       "      <td>913</td>\n",
       "      <td>779</td>\n",
       "      <td>867</td>\n",
       "      <td>683</td>\n",
       "      <td>262</td>\n",
       "      <td>180</td>\n",
       "      <td>6196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>POA3003</td>\n",
       "      <td>389</td>\n",
       "      <td>1076</td>\n",
       "      <td>2127</td>\n",
       "      <td>815</td>\n",
       "      <td>465</td>\n",
       "      <td>253</td>\n",
       "      <td>152</td>\n",
       "      <td>51</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>399</td>\n",
       "      <td>1117</td>\n",
       "      <td>2207</td>\n",
       "      <td>853</td>\n",
       "      <td>496</td>\n",
       "      <td>281</td>\n",
       "      <td>165</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>5588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>POA3004</td>\n",
       "      <td>553</td>\n",
       "      <td>1032</td>\n",
       "      <td>2697</td>\n",
       "      <td>1447</td>\n",
       "      <td>923</td>\n",
       "      <td>889</td>\n",
       "      <td>791</td>\n",
       "      <td>295</td>\n",
       "      <td>235</td>\n",
       "      <td>...</td>\n",
       "      <td>593</td>\n",
       "      <td>1173</td>\n",
       "      <td>3011</td>\n",
       "      <td>1756</td>\n",
       "      <td>1281</td>\n",
       "      <td>1224</td>\n",
       "      <td>1044</td>\n",
       "      <td>442</td>\n",
       "      <td>341</td>\n",
       "      <td>10861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>POA3005</td>\n",
       "      <td>45</td>\n",
       "      <td>74</td>\n",
       "      <td>173</td>\n",
       "      <td>88</td>\n",
       "      <td>59</td>\n",
       "      <td>38</td>\n",
       "      <td>26</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>51</td>\n",
       "      <td>92</td>\n",
       "      <td>237</td>\n",
       "      <td>183</td>\n",
       "      <td>139</td>\n",
       "      <td>114</td>\n",
       "      <td>48</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>879</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 141 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  POA_CODE_2016  Count_home_Census_Nt_0_14_yr  Count_hom_Census_Nt_15_24_yr  \\\n",
       "0       POA3000                          1183                         15064   \n",
       "1       POA3002                           284                           398   \n",
       "2       POA3003                           389                          1076   \n",
       "3       POA3004                           553                          1032   \n",
       "4       POA3005                            45                            74   \n",
       "\n",
       "   Count_hom_Census_Nt_25_34_yr  Count_hom_Census_Nt_35_44_yr  \\\n",
       "0                         13255                          3437   \n",
       "1                          1341                           694   \n",
       "2                          2127                           815   \n",
       "3                          2697                          1447   \n",
       "4                           173                            88   \n",
       "\n",
       "   Count_hom_Census_Nt_45_54_yr  Count_hom_Census_Nt_55_64_yr  \\\n",
       "0                          1683                          1188   \n",
       "1                           541                           614   \n",
       "2                           465                           253   \n",
       "3                           923                           889   \n",
       "4                            59                            38   \n",
       "\n",
       "   Count_hom_Census_Nt_65_74_yr  Count_hom_Census_Nt_75_84_yr  \\\n",
       "0                           671                           179   \n",
       "1                           494                           171   \n",
       "2                           152                            51   \n",
       "3                           791                           295   \n",
       "4                            26                             5   \n",
       "\n",
       "   Count_hom_Census_Nt_85ov  ...  Total_0_14_yr  Total_15_24_yr  \\\n",
       "0                        69  ...           1495           16540   \n",
       "1                       136  ...            375             516   \n",
       "2                        12  ...            399            1117   \n",
       "3                       235  ...            593            1173   \n",
       "4                         0  ...             51              92   \n",
       "\n",
       "   Total_25_34_yr  Total_35_44_yr  Total_45_54_yr  Total_55_64_yr  \\\n",
       "0           15606            5657            4342            3378   \n",
       "1            1616             913             779             867   \n",
       "2            2207             853             496             281   \n",
       "3            3011            1756            1281            1224   \n",
       "4             237             183             139             114   \n",
       "\n",
       "   Total_65_74_yr  Total_75_84_yr  Total_85ov  Total_Total  \n",
       "0            1691             396          96        49192  \n",
       "1             683             262         180         6196  \n",
       "2             165              60          12         5588  \n",
       "3            1044             442         341        10861  \n",
       "4              48              10           0          879  \n",
       "\n",
       "[5 rows x 141 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_residence.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "removable-wellington",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "698"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_residence.POA_CODE_2016.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "coastal-sapphire",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['POA_CODE_2016', 'Count_home_Census_Nt_0_14_yr',\n",
       "       'Count_hom_Census_Nt_15_24_yr', 'Count_hom_Census_Nt_25_34_yr',\n",
       "       'Count_hom_Census_Nt_35_44_yr', 'Count_hom_Census_Nt_45_54_yr',\n",
       "       'Count_hom_Census_Nt_55_64_yr', 'Count_hom_Census_Nt_65_74_yr',\n",
       "       'Count_hom_Census_Nt_75_84_yr', 'Count_hom_Census_Nt_85ov',\n",
       "       ...\n",
       "       'Total_0_14_yr', 'Total_15_24_yr', 'Total_25_34_yr', 'Total_35_44_yr',\n",
       "       'Total_45_54_yr', 'Total_55_64_yr', 'Total_65_74_yr', 'Total_75_84_yr',\n",
       "       'Total_85ov', 'Total_Total'],\n",
       "      dtype='object', length=141)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_residence.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "recorded-fitting",
   "metadata": {},
   "outputs": [],
   "source": [
    "poa_residence = poa_residence[[\"POA_CODE_2016\", \"Total_Total\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "dated-circular",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698, 47)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Family composition\n",
    "poa_family = pd.read_csv(\"../data/raw/2016 Census GCP Postal Areas for VIC/2016Census_G25_VIC_POA.csv\", low_memory=False)\n",
    "poa_family.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "emotional-skiing",
   "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>POA_CODE_2016</th>\n",
       "      <th>CF_no_children_F</th>\n",
       "      <th>CF_no_children_P</th>\n",
       "      <th>CF_ChU15_a_DSs_a_NdCh_F</th>\n",
       "      <th>CF_ChU15_a_DSs_a_NdCh_P</th>\n",
       "      <th>CF_ChU15_a_DSs_a_no_NdCh_F</th>\n",
       "      <th>CF_ChU15_a_DSs_a_no_NdCh_P</th>\n",
       "      <th>CF_ChU15_a_no_DSs_a_NdCh_F</th>\n",
       "      <th>CF_ChU15_a_no_DSs_a_NdCh_P</th>\n",
       "      <th>CF_ChU15_a_no_DSs_a_no_NdCh_F</th>\n",
       "      <th>...</th>\n",
       "      <th>OPF_no_ChU15_no_DSs_a_NdCh_F</th>\n",
       "      <th>OPF_no_ChU15_no_DSs_a_NdCh_P</th>\n",
       "      <th>OPF_no_ChU15_a_Total_F</th>\n",
       "      <th>OPF_no_ChU15_a_Total_P</th>\n",
       "      <th>OPF_Total_F</th>\n",
       "      <th>OPF_Total_P</th>\n",
       "      <th>Other_family_F</th>\n",
       "      <th>Other_family_P</th>\n",
       "      <th>Total_F</th>\n",
       "      <th>Total_P</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POA3000</td>\n",
       "      <td>3640</td>\n",
       "      <td>7189</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>89</td>\n",
       "      <td>5</td>\n",
       "      <td>32</td>\n",
       "      <td>678</td>\n",
       "      <td>...</td>\n",
       "      <td>121</td>\n",
       "      <td>252</td>\n",
       "      <td>276</td>\n",
       "      <td>622</td>\n",
       "      <td>420</td>\n",
       "      <td>970</td>\n",
       "      <td>883</td>\n",
       "      <td>1858</td>\n",
       "      <td>5804</td>\n",
       "      <td>12790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POA3002</td>\n",
       "      <td>749</td>\n",
       "      <td>1450</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>141</td>\n",
       "      <td>...</td>\n",
       "      <td>26</td>\n",
       "      <td>56</td>\n",
       "      <td>38</td>\n",
       "      <td>78</td>\n",
       "      <td>62</td>\n",
       "      <td>140</td>\n",
       "      <td>29</td>\n",
       "      <td>59</td>\n",
       "      <td>1107</td>\n",
       "      <td>2532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>POA3003</td>\n",
       "      <td>678</td>\n",
       "      <td>1333</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>19</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>190</td>\n",
       "      <td>...</td>\n",
       "      <td>36</td>\n",
       "      <td>68</td>\n",
       "      <td>46</td>\n",
       "      <td>104</td>\n",
       "      <td>76</td>\n",
       "      <td>186</td>\n",
       "      <td>71</td>\n",
       "      <td>146</td>\n",
       "      <td>1087</td>\n",
       "      <td>2606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>POA3004</td>\n",
       "      <td>1510</td>\n",
       "      <td>2936</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>17</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>298</td>\n",
       "      <td>...</td>\n",
       "      <td>64</td>\n",
       "      <td>120</td>\n",
       "      <td>118</td>\n",
       "      <td>245</td>\n",
       "      <td>188</td>\n",
       "      <td>399</td>\n",
       "      <td>95</td>\n",
       "      <td>189</td>\n",
       "      <td>2223</td>\n",
       "      <td>4932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>POA3005</td>\n",
       "      <td>60</td>\n",
       "      <td>107</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>7</td>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>99</td>\n",
       "      <td>232</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  POA_CODE_2016  CF_no_children_F  CF_no_children_P  CF_ChU15_a_DSs_a_NdCh_F  \\\n",
       "0       POA3000              3640              7189                        0   \n",
       "1       POA3002               749              1450                        0   \n",
       "2       POA3003               678              1333                        0   \n",
       "3       POA3004              1510              2936                        0   \n",
       "4       POA3005                60               107                        0   \n",
       "\n",
       "   CF_ChU15_a_DSs_a_NdCh_P  CF_ChU15_a_DSs_a_no_NdCh_F  \\\n",
       "0                        3                          22   \n",
       "1                        0                          18   \n",
       "2                        7                          19   \n",
       "3                        8                          17   \n",
       "4                        0                           0   \n",
       "\n",
       "   CF_ChU15_a_DSs_a_no_NdCh_P  CF_ChU15_a_no_DSs_a_NdCh_F  \\\n",
       "0                          89                           5   \n",
       "1                          70                           0   \n",
       "2                          75                           0   \n",
       "3                          81                           0   \n",
       "4                           4                           0   \n",
       "\n",
       "   CF_ChU15_a_no_DSs_a_NdCh_P  CF_ChU15_a_no_DSs_a_no_NdCh_F  ...  \\\n",
       "0                          32                            678  ...   \n",
       "1                           5                            141  ...   \n",
       "2                           0                            190  ...   \n",
       "3                           3                            298  ...   \n",
       "4                           0                             25  ...   \n",
       "\n",
       "   OPF_no_ChU15_no_DSs_a_NdCh_F  OPF_no_ChU15_no_DSs_a_NdCh_P  \\\n",
       "0                           121                           252   \n",
       "1                            26                            56   \n",
       "2                            36                            68   \n",
       "3                            64                           120   \n",
       "4                             8                             8   \n",
       "\n",
       "   OPF_no_ChU15_a_Total_F  OPF_no_ChU15_a_Total_P  OPF_Total_F  OPF_Total_P  \\\n",
       "0                     276                     622          420          970   \n",
       "1                      38                      78           62          140   \n",
       "2                      46                     104           76          186   \n",
       "3                     118                     245          188          399   \n",
       "4                       4                      17            7           23   \n",
       "\n",
       "   Other_family_F  Other_family_P  Total_F  Total_P  \n",
       "0             883            1858     5804    12790  \n",
       "1              29              59     1107     2532  \n",
       "2              71             146     1087     2606  \n",
       "3              95             189     2223     4932  \n",
       "4               4              12       99      232  \n",
       "\n",
       "[5 rows x 47 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_family.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "prime-broadcasting",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['POA_CODE_2016', 'CF_no_children_F', 'CF_no_children_P',\n",
       "       'CF_ChU15_a_DSs_a_NdCh_F', 'CF_ChU15_a_DSs_a_NdCh_P',\n",
       "       'CF_ChU15_a_DSs_a_no_NdCh_F', 'CF_ChU15_a_DSs_a_no_NdCh_P',\n",
       "       'CF_ChU15_a_no_DSs_a_NdCh_F', 'CF_ChU15_a_no_DSs_a_NdCh_P',\n",
       "       'CF_ChU15_a_no_DSs_a_no_NdCh_F', 'CF_ChU15_a_no_DSs_a_no_NdCh_P',\n",
       "       'CF_ChU15_a_Total_F', 'CF_ChU15_a_Total_P',\n",
       "       'CF_no_ChU15_a_DSs_a_NdCh_F', 'CF_no_ChU15_a_DSs_a_NdCh_P',\n",
       "       'CF_no_ChU15_a_DSs_a_no_NdCh_F', 'CF_no_ChU15_a_DSs_a_no_NdCh_P',\n",
       "       'CF_no_ChU15_a_no_DSs_a_NdCh_F', 'CF_no_ChU15_a_no_DSs_a_NdCh_P',\n",
       "       'CF_no_ChU15_a_Total_F', 'CF_no_ChU15_a_Total_P', 'CF_Total_F',\n",
       "       'CF_Total_P', 'OPF_ChU15_a_DSs_a_NdCh_F', 'OPF_ChU15_a_DSs_a_NdCh_P',\n",
       "       'OPF_ChU15_a_DSs_a_no_NdCh_F', 'OPF_ChU15_a_DSs_a_no_NdCh_P',\n",
       "       'OPF_ChU15_a_no_DSs_a_NdCh_F', 'OPF_ChU15_a_no_DSs_a_NdCh_P',\n",
       "       'OPF_ChU15_a_no_DSs_no_NdCh_F', 'OPF_ChU15_a_no_DSs_no_NdCh_P',\n",
       "       'OPF_ChU15_a_Total_F', 'OPF_ChU15_a_Total_P',\n",
       "       'OPF_no_ChU15_a_DSs_a_NdCh_F', 'OPF_no_ChU15_a_DSs_a_NdCh_P',\n",
       "       'OPF_no_ChU15_a_DSs_no_NdCh_F', 'OPF_no_ChU15_a_DSs_no_NdCh_P',\n",
       "       'OPF_no_ChU15_no_DSs_a_NdCh_F', 'OPF_no_ChU15_no_DSs_a_NdCh_P',\n",
       "       'OPF_no_ChU15_a_Total_F', 'OPF_no_ChU15_a_Total_P', 'OPF_Total_F',\n",
       "       'OPF_Total_P', 'Other_family_F', 'Other_family_P', 'Total_F',\n",
       "       'Total_P'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_family.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "incident-blond",
   "metadata": {},
   "outputs": [],
   "source": [
    "poa_family = poa_family[[\"POA_CODE_2016\", \"Total_F\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "external-young",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698, 3)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_family = poa_family.merge(poa_residence, how=\"left\", left_on=\"POA_CODE_2016\", right_on=\"POA_CODE_2016\")\n",
    "poa_family.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "identified-ecology",
   "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>POA_CODE_2016</th>\n",
       "      <th>Total_F</th>\n",
       "      <th>Total_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>POA3000</td>\n",
       "      <td>5804</td>\n",
       "      <td>49192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>POA3002</td>\n",
       "      <td>1107</td>\n",
       "      <td>6196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>POA3003</td>\n",
       "      <td>1087</td>\n",
       "      <td>5588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>POA3004</td>\n",
       "      <td>2223</td>\n",
       "      <td>10861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>POA3005</td>\n",
       "      <td>99</td>\n",
       "      <td>879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>POA3006</td>\n",
       "      <td>3965</td>\n",
       "      <td>22958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>POA3008</td>\n",
       "      <td>2452</td>\n",
       "      <td>11612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>POA3010</td>\n",
       "      <td>11</td>\n",
       "      <td>1914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>POA3011</td>\n",
       "      <td>4832</td>\n",
       "      <td>21598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>POA3012</td>\n",
       "      <td>6479</td>\n",
       "      <td>26007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  POA_CODE_2016  Total_F  Total_Total\n",
       "0       POA3000     5804        49192\n",
       "1       POA3002     1107         6196\n",
       "2       POA3003     1087         5588\n",
       "3       POA3004     2223        10861\n",
       "4       POA3005       99          879\n",
       "5       POA3006     3965        22958\n",
       "6       POA3008     2452        11612\n",
       "7       POA3010       11         1914\n",
       "8       POA3011     4832        21598\n",
       "9       POA3012     6479        26007"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_family.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "whole-certificate",
   "metadata": {},
   "outputs": [],
   "source": [
    "poa_family.rename(columns={\"POA_CODE_2016\":\"POA_CODE16\",\"Total_F\":\"Total Households\", \"Total_Total\": \"Population\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "behavioral-tomorrow",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove POA in the name of POA_CODE\n",
    "poa_family.POA_CODE16 = poa_family.POA_CODE16.apply(lambda x: x[-4:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "addressed-method",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      698.000000\n",
       "mean      2195.598854\n",
       "std       3333.703804\n",
       "min          0.000000\n",
       "25%        153.500000\n",
       "50%        655.500000\n",
       "75%       3208.500000\n",
       "max      26769.000000\n",
       "Name: Total Households, dtype: float64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_family[\"Total Households\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "tired-secret",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5, 3)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_family[poa_family[\"Total Households\"] == 0].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "textile-grocery",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      698.000000\n",
       "mean      8416.386819\n",
       "std      12711.413727\n",
       "min          0.000000\n",
       "25%        584.000000\n",
       "50%       2497.000000\n",
       "75%      12174.250000\n",
       "max      99565.000000\n",
       "Name: Population, dtype: float64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_family[\"Population\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "recorded-married",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 3)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poa_family[poa_family[\"Population\"] == 0].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "civic-ability",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "increased-weight",
   "metadata": {},
   "outputs": [],
   "source": [
    "poa_family.to_csv(\"../data/processed/poa_households.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "vertical-annex",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}