data-for-graph.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pandas\n",
    "import pandas as pd\n",
    "# SQL Alchemy\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import create_engine, inspect\n",
    "import numpy as np\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.orm import Session\n",
    "\n",
    "from sqlalchemy import Column, Float, Integer, String\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "Base = declarative_base()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create Engine for census data\n",
    "census_database_path = \"SQL/SGU.sqlite\"\n",
    "engine = create_engine(f\"sqlite:///{census_database_path}\")\n",
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['income', 'installation', 'output', 'rebate', 'sgu', 'state', 'suburbs']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables = inspector.get_table_names()\n",
    "tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "installation = pd.read_sql_query('select * from \"installation\"', con=conn)\n",
    "output = pd.read_sql_query('select * from \"output\"', con=conn)\n",
    "suburbs = pd.read_sql_query('select * from \"suburbs\"', con=conn)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "installation.columns = ['postcode','i2001','i2002','i2003','i2004','i2005','i2006','i2007','i2008','i2009','i2010','i2011','i2012','i2013','i2014','i2015','i2016','i2017','i2018','i2019','i2020','i2021','ins_avg','ins_total']"
   ]
  },
  {
   "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>postcode</th>\n",
       "      <th>i2001</th>\n",
       "      <th>i2002</th>\n",
       "      <th>i2003</th>\n",
       "      <th>i2004</th>\n",
       "      <th>i2005</th>\n",
       "      <th>i2006</th>\n",
       "      <th>i2007</th>\n",
       "      <th>i2008</th>\n",
       "      <th>i2009</th>\n",
       "      <th>...</th>\n",
       "      <th>i2014</th>\n",
       "      <th>i2015</th>\n",
       "      <th>i2016</th>\n",
       "      <th>i2017</th>\n",
       "      <th>i2018</th>\n",
       "      <th>i2019</th>\n",
       "      <th>i2020</th>\n",
       "      <th>i2021</th>\n",
       "      <th>ins_avg</th>\n",
       "      <th>ins_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.190476</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.047619</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>800</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.190476</td>\n",
       "      <td>109.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>801</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.380952</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>803</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.095238</td>\n",
       "      <td>2.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",
       "      <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>2797</th>\n",
       "      <td>7469</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.761905</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2798</th>\n",
       "      <td>7470</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.047619</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2799</th>\n",
       "      <td>7802</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.047619</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2800</th>\n",
       "      <td>8576</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.095238</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2801</th>\n",
       "      <td>9729</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.047619</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2802 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      postcode  i2001  i2002  i2003  i2004  i2005  i2006  i2007  i2008  i2009  \\\n",
       "0            0    0.0    2.0    0.0    1.0    1.0    0.0    0.0    0.0    0.0   \n",
       "1          200    0.0    0.0    0.0    1.0    0.0    0.0    0.0    0.0    0.0   \n",
       "2          800    0.0    0.0    0.0    0.0    1.0    0.0    2.0    4.0    0.0   \n",
       "3          801    0.0    0.0    0.0    2.0    0.0    0.0    0.0    0.0    0.0   \n",
       "4          803    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "...        ...    ...    ...    ...    ...    ...    ...    ...    ...    ...   \n",
       "2797      7469    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    1.0   \n",
       "2798      7470    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    1.0   \n",
       "2799      7802    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    1.0   \n",
       "2800      8576    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "2801      9729    0.0    0.0    0.0    0.0    1.0    0.0    0.0    0.0    0.0   \n",
       "\n",
       "      ...  i2014  i2015  i2016  i2017  i2018  i2019  i2020  i2021   ins_avg  \\\n",
       "0     ...    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0  0.190476   \n",
       "1     ...    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0  0.047619   \n",
       "2     ...    6.0    4.0    6.0    5.0   11.0   40.0   11.0    3.0  5.190476   \n",
       "3     ...    0.0    1.0    1.0    1.0    1.0    0.0    1.0    0.0  0.380952   \n",
       "4     ...    0.0    1.0    0.0    1.0    0.0    0.0    0.0    0.0  0.095238   \n",
       "...   ...    ...    ...    ...    ...    ...    ...    ...    ...       ...   \n",
       "2797  ...    3.0    7.0    2.0    0.0    0.0    1.0    0.0    2.0  1.761905   \n",
       "2798  ...    4.0    1.0    4.0    0.0    1.0    7.0    8.0    0.0  2.047619   \n",
       "2799  ...    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0  0.047619   \n",
       "2800  ...    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0  0.095238   \n",
       "2801  ...    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0  0.047619   \n",
       "\n",
       "      ins_total  \n",
       "0           4.0  \n",
       "1           1.0  \n",
       "2         109.0  \n",
       "3           8.0  \n",
       "4           2.0  \n",
       "...         ...  \n",
       "2797       37.0  \n",
       "2798       43.0  \n",
       "2799        1.0  \n",
       "2800        2.0  \n",
       "2801        1.0  \n",
       "\n",
       "[2802 rows x 24 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "installation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "output.columns = ['postcode','o2001','o2002','o2003','o2004','o2005','o2006','o2007','o2008','o2009','o2010','o2011','o2012','o2013','o2014','o2015','o2016','o2017','o2018','o2019','o2020','o2021','out_avg','out_total']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "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>postcode</th>\n",
       "      <th>o2001</th>\n",
       "      <th>o2002</th>\n",
       "      <th>o2003</th>\n",
       "      <th>o2004</th>\n",
       "      <th>o2005</th>\n",
       "      <th>o2006</th>\n",
       "      <th>o2007</th>\n",
       "      <th>o2008</th>\n",
       "      <th>o2009</th>\n",
       "      <th>...</th>\n",
       "      <th>o2014</th>\n",
       "      <th>o2015</th>\n",
       "      <th>o2016</th>\n",
       "      <th>o2017</th>\n",
       "      <th>o2018</th>\n",
       "      <th>o2019</th>\n",
       "      <th>o2020</th>\n",
       "      <th>o2021</th>\n",
       "      <th>out_avg</th>\n",
       "      <th>out_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.58</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.98</td>\n",
       "      <td>0.900</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.212381</td>\n",
       "      <td>4.460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.08</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.003810</td>\n",
       "      <td>0.080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>800</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.160</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.2</td>\n",
       "      <td>15.38</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>41.045</td>\n",
       "      <td>133.49</td>\n",
       "      <td>208.070</td>\n",
       "      <td>281.890</td>\n",
       "      <td>527.74</td>\n",
       "      <td>682.480</td>\n",
       "      <td>526.04</td>\n",
       "      <td>48.49</td>\n",
       "      <td>123.599048</td>\n",
       "      <td>2595.580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>801</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.44</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.00</td>\n",
       "      <td>66.708</td>\n",
       "      <td>24.840</td>\n",
       "      <td>31.86</td>\n",
       "      <td>0.000</td>\n",
       "      <td>99.99</td>\n",
       "      <td>0.00</td>\n",
       "      <td>11.183714</td>\n",
       "      <td>234.858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>803</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.12</td>\n",
       "      <td>0.000</td>\n",
       "      <td>5.565</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.556429</td>\n",
       "      <td>11.685</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2797</th>\n",
       "      <td>7469</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.05</td>\n",
       "      <td>...</td>\n",
       "      <td>14.500</td>\n",
       "      <td>28.50</td>\n",
       "      <td>6.750</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.050</td>\n",
       "      <td>0.00</td>\n",
       "      <td>13.32</td>\n",
       "      <td>6.607857</td>\n",
       "      <td>138.765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2798</th>\n",
       "      <td>7470</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.02</td>\n",
       "      <td>...</td>\n",
       "      <td>17.000</td>\n",
       "      <td>5.00</td>\n",
       "      <td>9.500</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.30</td>\n",
       "      <td>42.605</td>\n",
       "      <td>46.78</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.202714</td>\n",
       "      <td>193.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2799</th>\n",
       "      <td>7802</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.45</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.116667</td>\n",
       "      <td>2.450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2800</th>\n",
       "      <td>8576</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.325714</td>\n",
       "      <td>6.840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2801</th>\n",
       "      <td>9729</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.875</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.041667</td>\n",
       "      <td>0.875</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2802 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      postcode  o2001  o2002  o2003  o2004  o2005  o2006  o2007  o2008  o2009  \\\n",
       "0            0    0.0   2.58    0.0   0.98  0.900    0.0    0.0   0.00   0.00   \n",
       "1          200    0.0   0.00    0.0   0.08  0.000    0.0    0.0   0.00   0.00   \n",
       "2          800    0.0   0.00    0.0   0.00  0.160    0.0   19.2  15.38   0.00   \n",
       "3          801    0.0   0.00    0.0   3.44  0.000    0.0    0.0   0.00   0.00   \n",
       "4          803    0.0   0.00    0.0   0.00  0.000    0.0    0.0   0.00   0.00   \n",
       "...        ...    ...    ...    ...    ...    ...    ...    ...    ...    ...   \n",
       "2797      7469    0.0   0.00    0.0   0.00  0.000    0.0    0.0   0.00   1.05   \n",
       "2798      7470    0.0   0.00    0.0   0.00  0.000    0.0    0.0   0.00   1.02   \n",
       "2799      7802    0.0   0.00    0.0   0.00  0.000    0.0    0.0   0.00   2.45   \n",
       "2800      8576    0.0   0.00    0.0   0.00  0.000    0.0    0.0   0.00   0.00   \n",
       "2801      9729    0.0   0.00    0.0   0.00  0.875    0.0    0.0   0.00   0.00   \n",
       "\n",
       "      ...   o2014   o2015    o2016    o2017   o2018    o2019   o2020  o2021  \\\n",
       "0     ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "1     ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "2     ...  41.045  133.49  208.070  281.890  527.74  682.480  526.04  48.49   \n",
       "3     ...   0.000    6.00   66.708   24.840   31.86    0.000   99.99   0.00   \n",
       "4     ...   0.000    6.12    0.000    5.565    0.00    0.000    0.00   0.00   \n",
       "...   ...     ...     ...      ...      ...     ...      ...     ...    ...   \n",
       "2797  ...  14.500   28.50    6.750    0.000    0.00    6.050    0.00  13.32   \n",
       "2798  ...  17.000    5.00    9.500    0.000    6.30   42.605   46.78   0.00   \n",
       "2799  ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "2800  ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "2801  ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "\n",
       "         out_avg  out_total  \n",
       "0       0.212381      4.460  \n",
       "1       0.003810      0.080  \n",
       "2     123.599048   2595.580  \n",
       "3      11.183714    234.858  \n",
       "4       0.556429     11.685  \n",
       "...          ...        ...  \n",
       "2797    6.607857    138.765  \n",
       "2798    9.202714    193.257  \n",
       "2799    0.116667      2.450  \n",
       "2800    0.325714      6.840  \n",
       "2801    0.041667      0.875  \n",
       "\n",
       "[2802 rows x 24 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": false
   },
   "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>postcode</th>\n",
       "      <th>i2001</th>\n",
       "      <th>i2002</th>\n",
       "      <th>i2003</th>\n",
       "      <th>i2004</th>\n",
       "      <th>i2005</th>\n",
       "      <th>i2006</th>\n",
       "      <th>i2007</th>\n",
       "      <th>i2008</th>\n",
       "      <th>i2009</th>\n",
       "      <th>...</th>\n",
       "      <th>o2014</th>\n",
       "      <th>o2015</th>\n",
       "      <th>o2016</th>\n",
       "      <th>o2017</th>\n",
       "      <th>o2018</th>\n",
       "      <th>o2019</th>\n",
       "      <th>o2020</th>\n",
       "      <th>o2021</th>\n",
       "      <th>out_avg</th>\n",
       "      <th>out_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.212381</td>\n",
       "      <td>4.460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.003810</td>\n",
       "      <td>0.080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>800</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>41.045</td>\n",
       "      <td>133.49</td>\n",
       "      <td>208.070</td>\n",
       "      <td>281.890</td>\n",
       "      <td>527.74</td>\n",
       "      <td>682.480</td>\n",
       "      <td>526.04</td>\n",
       "      <td>48.49</td>\n",
       "      <td>123.599048</td>\n",
       "      <td>2595.580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>801</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.00</td>\n",
       "      <td>66.708</td>\n",
       "      <td>24.840</td>\n",
       "      <td>31.86</td>\n",
       "      <td>0.000</td>\n",
       "      <td>99.99</td>\n",
       "      <td>0.00</td>\n",
       "      <td>11.183714</td>\n",
       "      <td>234.858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>803</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.12</td>\n",
       "      <td>0.000</td>\n",
       "      <td>5.565</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.556429</td>\n",
       "      <td>11.685</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2797</th>\n",
       "      <td>7469</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>14.500</td>\n",
       "      <td>28.50</td>\n",
       "      <td>6.750</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.050</td>\n",
       "      <td>0.00</td>\n",
       "      <td>13.32</td>\n",
       "      <td>6.607857</td>\n",
       "      <td>138.765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2798</th>\n",
       "      <td>7470</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>17.000</td>\n",
       "      <td>5.00</td>\n",
       "      <td>9.500</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.30</td>\n",
       "      <td>42.605</td>\n",
       "      <td>46.78</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.202714</td>\n",
       "      <td>193.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2799</th>\n",
       "      <td>7802</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.116667</td>\n",
       "      <td>2.450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2800</th>\n",
       "      <td>8576</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.325714</td>\n",
       "      <td>6.840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2801</th>\n",
       "      <td>9729</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.041667</td>\n",
       "      <td>0.875</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2802 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      postcode  i2001  i2002  i2003  i2004  i2005  i2006  i2007  i2008  i2009  \\\n",
       "0            0    0.0    2.0    0.0    1.0    1.0    0.0    0.0    0.0    0.0   \n",
       "1          200    0.0    0.0    0.0    1.0    0.0    0.0    0.0    0.0    0.0   \n",
       "2          800    0.0    0.0    0.0    0.0    1.0    0.0    2.0    4.0    0.0   \n",
       "3          801    0.0    0.0    0.0    2.0    0.0    0.0    0.0    0.0    0.0   \n",
       "4          803    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "...        ...    ...    ...    ...    ...    ...    ...    ...    ...    ...   \n",
       "2797      7469    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    1.0   \n",
       "2798      7470    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    1.0   \n",
       "2799      7802    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    1.0   \n",
       "2800      8576    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "2801      9729    0.0    0.0    0.0    0.0    1.0    0.0    0.0    0.0    0.0   \n",
       "\n",
       "      ...   o2014   o2015    o2016    o2017   o2018    o2019   o2020  o2021  \\\n",
       "0     ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "1     ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "2     ...  41.045  133.49  208.070  281.890  527.74  682.480  526.04  48.49   \n",
       "3     ...   0.000    6.00   66.708   24.840   31.86    0.000   99.99   0.00   \n",
       "4     ...   0.000    6.12    0.000    5.565    0.00    0.000    0.00   0.00   \n",
       "...   ...     ...     ...      ...      ...     ...      ...     ...    ...   \n",
       "2797  ...  14.500   28.50    6.750    0.000    0.00    6.050    0.00  13.32   \n",
       "2798  ...  17.000    5.00    9.500    0.000    6.30   42.605   46.78   0.00   \n",
       "2799  ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "2800  ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "2801  ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "\n",
       "         out_avg  out_total  \n",
       "0       0.212381      4.460  \n",
       "1       0.003810      0.080  \n",
       "2     123.599048   2595.580  \n",
       "3      11.183714    234.858  \n",
       "4       0.556429     11.685  \n",
       "...          ...        ...  \n",
       "2797    6.607857    138.765  \n",
       "2798    9.202714    193.257  \n",
       "2799    0.116667      2.450  \n",
       "2800    0.325714      6.840  \n",
       "2801    0.041667      0.875  \n",
       "\n",
       "[2802 rows x 47 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_data = installation.merge(output, how='inner', on='postcode')\n",
    "merge_data.sort_values(by=['postcode'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>postcode</th>\n",
       "      <th>i2001</th>\n",
       "      <th>i2002</th>\n",
       "      <th>i2003</th>\n",
       "      <th>i2004</th>\n",
       "      <th>i2005</th>\n",
       "      <th>i2006</th>\n",
       "      <th>i2007</th>\n",
       "      <th>i2008</th>\n",
       "      <th>i2009</th>\n",
       "      <th>...</th>\n",
       "      <th>o2014</th>\n",
       "      <th>o2015</th>\n",
       "      <th>o2016</th>\n",
       "      <th>o2017</th>\n",
       "      <th>o2018</th>\n",
       "      <th>o2019</th>\n",
       "      <th>o2020</th>\n",
       "      <th>o2021</th>\n",
       "      <th>out_avg</th>\n",
       "      <th>out_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.212381</td>\n",
       "      <td>4.460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.003810</td>\n",
       "      <td>0.080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>800</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>41.045</td>\n",
       "      <td>133.49</td>\n",
       "      <td>208.070</td>\n",
       "      <td>281.890</td>\n",
       "      <td>527.74</td>\n",
       "      <td>682.480</td>\n",
       "      <td>526.04</td>\n",
       "      <td>48.49</td>\n",
       "      <td>123.599048</td>\n",
       "      <td>2595.580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>801</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.00</td>\n",
       "      <td>66.708</td>\n",
       "      <td>24.840</td>\n",
       "      <td>31.86</td>\n",
       "      <td>0.000</td>\n",
       "      <td>99.99</td>\n",
       "      <td>0.00</td>\n",
       "      <td>11.183714</td>\n",
       "      <td>234.858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>803</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.12</td>\n",
       "      <td>0.000</td>\n",
       "      <td>5.565</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.556429</td>\n",
       "      <td>11.685</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2797</th>\n",
       "      <td>7469</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>14.500</td>\n",
       "      <td>28.50</td>\n",
       "      <td>6.750</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.050</td>\n",
       "      <td>0.00</td>\n",
       "      <td>13.32</td>\n",
       "      <td>6.607857</td>\n",
       "      <td>138.765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2798</th>\n",
       "      <td>7470</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>17.000</td>\n",
       "      <td>5.00</td>\n",
       "      <td>9.500</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.30</td>\n",
       "      <td>42.605</td>\n",
       "      <td>46.78</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.202714</td>\n",
       "      <td>193.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2799</th>\n",
       "      <td>7802</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.116667</td>\n",
       "      <td>2.450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2800</th>\n",
       "      <td>8576</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.325714</td>\n",
       "      <td>6.840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2801</th>\n",
       "      <td>9729</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.041667</td>\n",
       "      <td>0.875</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2802 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      postcode  i2001  i2002  i2003  i2004  i2005  i2006  i2007  i2008  i2009  \\\n",
       "0            0    0.0    2.0    0.0    1.0    1.0    0.0    0.0    0.0    0.0   \n",
       "1          200    0.0    0.0    0.0    1.0    0.0    0.0    0.0    0.0    0.0   \n",
       "2          800    0.0    0.0    0.0    0.0    1.0    0.0    2.0    4.0    0.0   \n",
       "3          801    0.0    0.0    0.0    2.0    0.0    0.0    0.0    0.0    0.0   \n",
       "4          803    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "...        ...    ...    ...    ...    ...    ...    ...    ...    ...    ...   \n",
       "2797      7469    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    1.0   \n",
       "2798      7470    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    1.0   \n",
       "2799      7802    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    1.0   \n",
       "2800      8576    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "2801      9729    0.0    0.0    0.0    0.0    1.0    0.0    0.0    0.0    0.0   \n",
       "\n",
       "      ...   o2014   o2015    o2016    o2017   o2018    o2019   o2020  o2021  \\\n",
       "0     ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "1     ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "2     ...  41.045  133.49  208.070  281.890  527.74  682.480  526.04  48.49   \n",
       "3     ...   0.000    6.00   66.708   24.840   31.86    0.000   99.99   0.00   \n",
       "4     ...   0.000    6.12    0.000    5.565    0.00    0.000    0.00   0.00   \n",
       "...   ...     ...     ...      ...      ...     ...      ...     ...    ...   \n",
       "2797  ...  14.500   28.50    6.750    0.000    0.00    6.050    0.00  13.32   \n",
       "2798  ...  17.000    5.00    9.500    0.000    6.30   42.605   46.78   0.00   \n",
       "2799  ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "2800  ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "2801  ...   0.000    0.00    0.000    0.000    0.00    0.000    0.00   0.00   \n",
       "\n",
       "         out_avg  out_total  \n",
       "0       0.212381      4.460  \n",
       "1       0.003810      0.080  \n",
       "2     123.599048   2595.580  \n",
       "3      11.183714    234.858  \n",
       "4       0.556429     11.685  \n",
       "...          ...        ...  \n",
       "2797    6.607857    138.765  \n",
       "2798    9.202714    193.257  \n",
       "2799    0.116667      2.450  \n",
       "2800    0.325714      6.840  \n",
       "2801    0.041667      0.875  \n",
       "\n",
       "[2802 rows x 47 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_data.drop_duplicates( \"postcode\" , keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": false
   },
   "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>postcode</th>\n",
       "      <th>installations</th>\n",
       "      <th>output</th>\n",
       "      <th>suburb_id</th>\n",
       "      <th>suburb</th>\n",
       "      <th>state</th>\n",
       "      <th>long</th>\n",
       "      <th>lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>200</td>\n",
       "      <td>1</td>\n",
       "      <td>0.080</td>\n",
       "      <td>230</td>\n",
       "      <td>ANU</td>\n",
       "      <td>ACT</td>\n",
       "      <td>149.119000</td>\n",
       "      <td>-35.277700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17811</th>\n",
       "      <td>200</td>\n",
       "      <td>1</td>\n",
       "      <td>0.080</td>\n",
       "      <td>21820</td>\n",
       "      <td>Australian National University</td>\n",
       "      <td>ACT</td>\n",
       "      <td>149.118900</td>\n",
       "      <td>-35.277700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>800</td>\n",
       "      <td>109</td>\n",
       "      <td>2595.580</td>\n",
       "      <td>232</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "      <td>130.836680</td>\n",
       "      <td>-12.458684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>801</td>\n",
       "      <td>8</td>\n",
       "      <td>234.858</td>\n",
       "      <td>233</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "      <td>130.836680</td>\n",
       "      <td>-12.458684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>804</td>\n",
       "      <td>15</td>\n",
       "      <td>78.310</td>\n",
       "      <td>234</td>\n",
       "      <td>PARAP</td>\n",
       "      <td>NT</td>\n",
       "      <td>130.873315</td>\n",
       "      <td>-12.428017</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>16036</th>\n",
       "      <td>822</td>\n",
       "      <td>880</td>\n",
       "      <td>9240.174</td>\n",
       "      <td>20026</td>\n",
       "      <td>MINJILANG</td>\n",
       "      <td>NT</td>\n",
       "      <td>132.572481</td>\n",
       "      <td>-11.144706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16035</th>\n",
       "      <td>822</td>\n",
       "      <td>880</td>\n",
       "      <td>9240.174</td>\n",
       "      <td>20025</td>\n",
       "      <td>MILYAKBURRA</td>\n",
       "      <td>NT</td>\n",
       "      <td>136.202001</td>\n",
       "      <td>-13.779349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16034</th>\n",
       "      <td>822</td>\n",
       "      <td>880</td>\n",
       "      <td>9240.174</td>\n",
       "      <td>20024</td>\n",
       "      <td>MILINGIMBI</td>\n",
       "      <td>NT</td>\n",
       "      <td>134.917294</td>\n",
       "      <td>-12.103440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16033</th>\n",
       "      <td>822</td>\n",
       "      <td>880</td>\n",
       "      <td>9240.174</td>\n",
       "      <td>20023</td>\n",
       "      <td>MICKETT CREEK</td>\n",
       "      <td>NT</td>\n",
       "      <td>130.951697</td>\n",
       "      <td>-12.379529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16032</th>\n",
       "      <td>822</td>\n",
       "      <td>880</td>\n",
       "      <td>9240.174</td>\n",
       "      <td>20022</td>\n",
       "      <td>MARGARET RIVER</td>\n",
       "      <td>NT</td>\n",
       "      <td>131.357405</td>\n",
       "      <td>-13.199321</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       postcode  installations    output  suburb_id  \\\n",
       "190         200              1     0.080        230   \n",
       "17811       200              1     0.080      21820   \n",
       "191         800            109  2595.580        232   \n",
       "192         801              8   234.858        233   \n",
       "193         804             15    78.310        234   \n",
       "...         ...            ...       ...        ...   \n",
       "16036       822            880  9240.174      20026   \n",
       "16035       822            880  9240.174      20025   \n",
       "16034       822            880  9240.174      20024   \n",
       "16033       822            880  9240.174      20023   \n",
       "16032       822            880  9240.174      20022   \n",
       "\n",
       "                               suburb state        long        lat  \n",
       "190                               ANU   ACT  149.119000 -35.277700  \n",
       "17811  Australian National University   ACT  149.118900 -35.277700  \n",
       "191                            DARWIN    NT  130.836680 -12.458684  \n",
       "192                            DARWIN    NT  130.836680 -12.458684  \n",
       "193                             PARAP    NT  130.873315 -12.428017  \n",
       "...                               ...   ...         ...        ...  \n",
       "16036                       MINJILANG    NT  132.572481 -11.144706  \n",
       "16035                     MILYAKBURRA    NT  136.202001 -13.779349  \n",
       "16034                      MILINGIMBI    NT  134.917294 -12.103440  \n",
       "16033                   MICKETT CREEK    NT  130.951697 -12.379529  \n",
       "16032                  MARGARET RIVER    NT  131.357405 -13.199321  \n",
       "\n",
       "[100 rows x 8 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "suburbs.sort_values(by=['postcode']).head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "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>postcode</th>\n",
       "      <th>suburb</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>200</td>\n",
       "      <td>ANU</td>\n",
       "      <td>ACT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17811</th>\n",
       "      <td>200</td>\n",
       "      <td>Australian National University</td>\n",
       "      <td>ACT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>800</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>801</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>804</td>\n",
       "      <td>PARAP</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10713</th>\n",
       "      <td>7469</td>\n",
       "      <td>GRANVILLE HARBOUR</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10714</th>\n",
       "      <td>7469</td>\n",
       "      <td>RENISON BELL</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10715</th>\n",
       "      <td>7469</td>\n",
       "      <td>TRIAL HARBOUR</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10716</th>\n",
       "      <td>7469</td>\n",
       "      <td>ZEEHAN</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10717</th>\n",
       "      <td>7470</td>\n",
       "      <td>ROSEBERY</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17868 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       postcode                          suburb state\n",
       "190         200                             ANU   ACT\n",
       "17811       200  Australian National University   ACT\n",
       "191         800                          DARWIN    NT\n",
       "192         801                          DARWIN    NT\n",
       "193         804                           PARAP    NT\n",
       "...         ...                             ...   ...\n",
       "10713      7469               GRANVILLE HARBOUR   TAS\n",
       "10714      7469                    RENISON BELL   TAS\n",
       "10715      7469                   TRIAL HARBOUR   TAS\n",
       "10716      7469                          ZEEHAN   TAS\n",
       "10717      7470                        ROSEBERY   TAS\n",
       "\n",
       "[17868 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "suburbs.sort_values(by=['postcode'])\n",
    "filter_suburb = suburbs.iloc[:,[0,4,5]]\n",
    "filter_suburb=filter_suburb.sort_values(by=['postcode'])\n",
    "filter_suburb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>postcode</th>\n",
       "      <th>suburb</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>200</td>\n",
       "      <td>ANU</td>\n",
       "      <td>ACT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17811</th>\n",
       "      <td>200</td>\n",
       "      <td>Australian National University</td>\n",
       "      <td>ACT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>800</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>801</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>804</td>\n",
       "      <td>PARAP</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10713</th>\n",
       "      <td>7469</td>\n",
       "      <td>GRANVILLE HARBOUR</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10714</th>\n",
       "      <td>7469</td>\n",
       "      <td>RENISON BELL</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10715</th>\n",
       "      <td>7469</td>\n",
       "      <td>TRIAL HARBOUR</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10716</th>\n",
       "      <td>7469</td>\n",
       "      <td>ZEEHAN</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10717</th>\n",
       "      <td>7470</td>\n",
       "      <td>ROSEBERY</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17868 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       postcode                          suburb state\n",
       "190         200                             ANU   ACT\n",
       "17811       200  Australian National University   ACT\n",
       "191         800                          DARWIN    NT\n",
       "192         801                          DARWIN    NT\n",
       "193         804                           PARAP    NT\n",
       "...         ...                             ...   ...\n",
       "10713      7469               GRANVILLE HARBOUR   TAS\n",
       "10714      7469                    RENISON BELL   TAS\n",
       "10715      7469                   TRIAL HARBOUR   TAS\n",
       "10716      7469                          ZEEHAN   TAS\n",
       "10717      7470                        ROSEBERY   TAS\n",
       "\n",
       "[17868 rows x 3 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_suburb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>postcode</th>\n",
       "      <th>suburb</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>200</td>\n",
       "      <td>ANU</td>\n",
       "      <td>ACT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>800</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>801</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>804</td>\n",
       "      <td>PARAP</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>810</td>\n",
       "      <td>TIWI</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10708</th>\n",
       "      <td>7466</td>\n",
       "      <td>GORMANSTON</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10709</th>\n",
       "      <td>7467</td>\n",
       "      <td>LAKE MARGARET</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10712</th>\n",
       "      <td>7468</td>\n",
       "      <td>STRAHAN</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10713</th>\n",
       "      <td>7469</td>\n",
       "      <td>GRANVILLE HARBOUR</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10717</th>\n",
       "      <td>7470</td>\n",
       "      <td>ROSEBERY</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2784 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       postcode             suburb state\n",
       "190         200                ANU   ACT\n",
       "191         800             DARWIN    NT\n",
       "192         801             DARWIN    NT\n",
       "193         804              PARAP    NT\n",
       "204         810               TIWI    NT\n",
       "...         ...                ...   ...\n",
       "10708      7466         GORMANSTON   TAS\n",
       "10709      7467      LAKE MARGARET   TAS\n",
       "10712      7468            STRAHAN   TAS\n",
       "10713      7469  GRANVILLE HARBOUR   TAS\n",
       "10717      7470           ROSEBERY   TAS\n",
       "\n",
       "[2784 rows x 3 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_suburb = filter_suburb.drop_duplicates( \"postcode\" , keep='first')\n",
    "filter_suburb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "49"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data = merge_data.merge(filter_suburb, how='right', on='postcode')\n",
    "len(final_data.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>postcode</th>\n",
       "      <th>ins_avg</th>\n",
       "      <th>ins_total</th>\n",
       "      <th>out_avg</th>\n",
       "      <th>out_total</th>\n",
       "      <th>suburb</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200</td>\n",
       "      <td>0.047619</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.003810</td>\n",
       "      <td>0.080</td>\n",
       "      <td>ANU</td>\n",
       "      <td>ACT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>800</td>\n",
       "      <td>5.190476</td>\n",
       "      <td>109.0</td>\n",
       "      <td>123.599048</td>\n",
       "      <td>2595.580</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>801</td>\n",
       "      <td>0.380952</td>\n",
       "      <td>8.0</td>\n",
       "      <td>11.183714</td>\n",
       "      <td>234.858</td>\n",
       "      <td>DARWIN</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>804</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>15.0</td>\n",
       "      <td>3.729048</td>\n",
       "      <td>78.310</td>\n",
       "      <td>PARAP</td>\n",
       "      <td>NT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>810</td>\n",
       "      <td>156.857143</td>\n",
       "      <td>3294.0</td>\n",
       "      <td>1010.445048</td>\n",
       "      <td>21219.346</td>\n",
       "      <td>TIWI</td>\n",
       "      <td>NT</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>2779</th>\n",
       "      <td>7466</td>\n",
       "      <td>0.190476</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.494286</td>\n",
       "      <td>10.380</td>\n",
       "      <td>GORMANSTON</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2780</th>\n",
       "      <td>7467</td>\n",
       "      <td>3.619048</td>\n",
       "      <td>76.0</td>\n",
       "      <td>18.403333</td>\n",
       "      <td>386.470</td>\n",
       "      <td>LAKE MARGARET</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781</th>\n",
       "      <td>7468</td>\n",
       "      <td>1.619048</td>\n",
       "      <td>34.0</td>\n",
       "      <td>9.756905</td>\n",
       "      <td>204.895</td>\n",
       "      <td>STRAHAN</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2782</th>\n",
       "      <td>7469</td>\n",
       "      <td>1.761905</td>\n",
       "      <td>37.0</td>\n",
       "      <td>6.607857</td>\n",
       "      <td>138.765</td>\n",
       "      <td>GRANVILLE HARBOUR</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2783</th>\n",
       "      <td>7470</td>\n",
       "      <td>2.047619</td>\n",
       "      <td>43.0</td>\n",
       "      <td>9.202714</td>\n",
       "      <td>193.257</td>\n",
       "      <td>ROSEBERY</td>\n",
       "      <td>TAS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2784 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      postcode     ins_avg  ins_total      out_avg  out_total  \\\n",
       "0          200    0.047619        1.0     0.003810      0.080   \n",
       "1          800    5.190476      109.0   123.599048   2595.580   \n",
       "2          801    0.380952        8.0    11.183714    234.858   \n",
       "3          804    0.714286       15.0     3.729048     78.310   \n",
       "4          810  156.857143     3294.0  1010.445048  21219.346   \n",
       "...        ...         ...        ...          ...        ...   \n",
       "2779      7466    0.190476        4.0     0.494286     10.380   \n",
       "2780      7467    3.619048       76.0    18.403333    386.470   \n",
       "2781      7468    1.619048       34.0     9.756905    204.895   \n",
       "2782      7469    1.761905       37.0     6.607857    138.765   \n",
       "2783      7470    2.047619       43.0     9.202714    193.257   \n",
       "\n",
       "                 suburb state  \n",
       "0                   ANU   ACT  \n",
       "1                DARWIN    NT  \n",
       "2                DARWIN    NT  \n",
       "3                 PARAP    NT  \n",
       "4                  TIWI    NT  \n",
       "...                 ...   ...  \n",
       "2779         GORMANSTON   TAS  \n",
       "2780      LAKE MARGARET   TAS  \n",
       "2781            STRAHAN   TAS  \n",
       "2782  GRANVILLE HARBOUR   TAS  \n",
       "2783           ROSEBERY   TAS  \n",
       "\n",
       "[2784 rows x 7 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdata = final_data.iloc[:,[0,22,23,45,46,47,48]]\n",
    "rdata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# converted_df = rdata.astype(str)\n",
    "# converted_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# converted_df.to_csv('rdata.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Rdata(Base):\n",
    "    __tablename__ = 'rdata'\n",
    "    postcode = Column(Integer, primary_key=True)\n",
    "    ins_avg = Column(Float)\n",
    "    ins_total = Column(Float)\n",
    "    out_avg = Column(Float)\n",
    "    out_total = Column(Float)\n",
    "    suburb = Column(String(255))\n",
    "    state = Column(String(255))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the tables within the database\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "rdata.to_sql('rdata', engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reflect the database tables\n",
    "Base = automap_base()\n",
    "Base.prepare(engine, reflect=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['income',\n",
       " 'installation',\n",
       " 'output',\n",
       " 'rdata',\n",
       " 'rebate',\n",
       " 'sgu',\n",
       " 'state',\n",
       " 'suburbs']"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print all of the classes mapped to the Base\n",
    "Base.classes.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign the output class to a variable called `Output`\n",
    "Install = Base.classes.installation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a session\n",
    "session = Session(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(200, 0.047619047619047616, 1.0, 0.0038095238095238095, 0.08, 'ANU', 'ACT')\n",
      "(800, 5.190476190476191, 109.0, 123.59904761904761, 2595.58, 'DARWIN', 'NT')\n",
      "(801, 0.38095238095238093, 8.0, 11.183714285714286, 234.858, 'DARWIN', 'NT')\n",
      "(804, 0.7142857142857143, 15.0, 3.72904761904762, 78.31000000000002, 'PARAP', 'NT')\n",
      "(810, 156.85714285714286, 3294.0, 1010.4450476190475, 21219.345999999998, 'TIWI', 'NT')\n",
      "(811, 0.09523809523809523, 2.0, 0.42857142857142855, 9.0, 'CASUARINA', 'NT')\n",
      "(812, 86.23809523809524, 1811.0, 529.1911904761905, 11113.015000000001, 'WULAGI', 'NT')\n",
      "(813, 0.3333333333333333, 7.0, 4.073761904761904, 85.54899999999998, 'SANDERSON', 'NT')\n",
      "(814, 0.19047619047619047, 4.0, 0.8685714285714287, 18.240000000000002, 'NIGHTCLIFF', 'NT')\n",
      "(820, 52.23809523809524, 1097.0, 544.8573333333334, 11442.004, 'BAGOT', 'NT')\n",
      "(821, 0.3333333333333333, 7.0, 7.443, 156.303, 'WINNELLIE', 'NT')\n",
      "(822, 41.904761904761905, 880.0, 440.00828571428576, 9240.174, 'SOUTHPORT', 'NT')\n",
      "(828, 11.476190476190476, 241.0, 166.77338095238096, 3502.241, 'BERRIMAH', 'NT')\n",
      "(829, 1.7142857142857142, 36.0, 42.42285714285714, 890.8799999999999, 'PINELANDS', 'NT')\n",
      "(830, 92.47619047619048, 1942.0, 586.6249523809524, 12319.124000000002, 'DURACK', 'NT')\n",
      "(831, 0.42857142857142855, 9.0, 1.856190476190476, 38.98, 'PALMERSTON', 'NT')\n",
      "(832, 107.38095238095238, 2255.0, 642.1968571428572, 13486.134, 'GUNN', 'NT')\n",
      "(834, 11.142857142857142, 234.0, 81.1372380952381, 1703.882, 'VIRGINIA', 'NT')\n",
      "(835, 23.476190476190474, 493.0, 155.48514285714285, 3265.1879999999996, 'HOWARD SPRINGS', 'NT')\n",
      "(836, 55.666666666666664, 1169.0, 383.2414761904762, 8048.071, 'HUMPTY DOO', 'NT')\n"
     ]
    }
   ],
   "source": [
    "# Use the session to query Suburbs table\n",
    "for row in session.query(Rdata.postcode, Rdata.ins_avg, Rdata.ins_total, Rdata.out_avg, Rdata.out_total, Rdata.suburb, Rdata.state).limit(20).all():\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:PythonData]",
   "language": "python",
   "name": "conda-env-PythonData-py"
  },
  "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.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}