Copy of Location, Prices and Upcoming Areas LAST.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "8e6b057a",
   "metadata": {
    "executionInfo": {
     "elapsed": 245,
     "status": "ok",
     "timestamp": 1678703229356,
     "user": {
      "displayName": "Diego Lorenzo",
      "userId": "13240690789513449880"
     },
     "user_tz": 0
    },
    "id": "8e6b057a"
   },
   "outputs": [],
   "source": [
    "# All UK locations have an area code (e.g. Westminster E09000033, Birmingham E08000025). This unique ID will help merge\n",
    "# files and geolocate data. \n",
    "\n",
    "# Import dependencies\n",
    "import pandas as pd # library for data analsysis\n",
    "import scipy as st # library to handle mathematical functions\n",
    "\n",
    "import matplotlib as plt\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore') \n",
    "\n",
    "# library to handle JSON files\n",
    "import json \n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "228c977b",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 380
    },
    "executionInfo": {
     "elapsed": 6,
     "status": "error",
     "timestamp": 1678703229820,
     "user": {
      "displayName": "Diego Lorenzo",
      "userId": "13240690789513449880"
     },
     "user_tz": 0
    },
    "id": "228c977b",
    "outputId": "8183d954-9105-4832-f5d3-c5d4715ce610"
   },
   "outputs": [],
   "source": [
    "# Data files\n",
    "mean_salary_and_population_percode = \"housing_in_london_yearly_variables.csv\"\n",
    "average_house_price_percode = \"UK-HPI-full-file-2022-12.csv\"\n",
    "\n",
    "mean_salary_and_population_percode_df = pd.read_csv(mean_salary_and_population_percode)\n",
    "average_house_price_percode_df = pd.read_csv (average_house_price_percode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "f0039c9a",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 217
    },
    "executionInfo": {
     "elapsed": 6,
     "status": "error",
     "timestamp": 1678703231566,
     "user": {
      "displayName": "Diego Lorenzo",
      "userId": "13240690789513449880"
     },
     "user_tz": 0
    },
    "id": "f0039c9a",
    "outputId": "35b43d9a-70c0-4013-cb02-47dce2aaee11",
    "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>AreaCode</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>date</th>\n",
       "      <th>median_salary</th>\n",
       "      <th>life_satisfaction</th>\n",
       "      <th>mean_salary</th>\n",
       "      <th>recycling_pct</th>\n",
       "      <th>population_size</th>\n",
       "      <th>number_of_jobs</th>\n",
       "      <th>area_size</th>\n",
       "      <th>no_of_houses</th>\n",
       "      <th>borough_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E09000001</td>\n",
       "      <td>city of london</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>33020.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48922.0</td>\n",
       "      <td>0</td>\n",
       "      <td>6581.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E09000002</td>\n",
       "      <td>barking and dagenham</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>21480.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23620.0</td>\n",
       "      <td>3</td>\n",
       "      <td>162444.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E09000003</td>\n",
       "      <td>barnet</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>19568.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23128.0</td>\n",
       "      <td>8</td>\n",
       "      <td>313469.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E09000004</td>\n",
       "      <td>bexley</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>18621.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21386.0</td>\n",
       "      <td>18</td>\n",
       "      <td>217458.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E09000005</td>\n",
       "      <td>brent</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>18532.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20911.0</td>\n",
       "      <td>6</td>\n",
       "      <td>260317.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1066</th>\n",
       "      <td>K03000001</td>\n",
       "      <td>great britain</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>30446.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37603.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067</th>\n",
       "      <td>K04000001</td>\n",
       "      <td>england and wales</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>30500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37865.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1068</th>\n",
       "      <td>N92000002</td>\n",
       "      <td>northern ireland</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>27434.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32083.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1069</th>\n",
       "      <td>S92000003</td>\n",
       "      <td>scotland</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34916.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1070</th>\n",
       "      <td>W92000004</td>\n",
       "      <td>wales</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>27500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31251.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1071 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AreaCode            RegionName        date  median_salary  \\\n",
       "0     E09000001        city of london  01/12/1999        33020.0   \n",
       "1     E09000002  barking and dagenham  01/12/1999        21480.0   \n",
       "2     E09000003                barnet  01/12/1999        19568.0   \n",
       "3     E09000004                bexley  01/12/1999        18621.0   \n",
       "4     E09000005                 brent  01/12/1999        18532.0   \n",
       "...         ...                   ...         ...            ...   \n",
       "1066  K03000001         great britain  01/12/2019        30446.0   \n",
       "1067  K04000001     england and wales  01/12/2019        30500.0   \n",
       "1068  N92000002      northern ireland  01/12/2019        27434.0   \n",
       "1069  S92000003              scotland  01/12/2019        30000.0   \n",
       "1070  W92000004                 wales  01/12/2019        27500.0   \n",
       "\n",
       "      life_satisfaction  mean_salary recycling_pct  population_size  \\\n",
       "0                   NaN      48922.0             0           6581.0   \n",
       "1                   NaN      23620.0             3         162444.0   \n",
       "2                   NaN      23128.0             8         313469.0   \n",
       "3                   NaN      21386.0            18         217458.0   \n",
       "4                   NaN      20911.0             6         260317.0   \n",
       "...                 ...          ...           ...              ...   \n",
       "1066                NaN      37603.0           NaN              NaN   \n",
       "1067                NaN      37865.0           NaN              NaN   \n",
       "1068                NaN      32083.0           NaN              NaN   \n",
       "1069                NaN      34916.0           NaN              NaN   \n",
       "1070                NaN      31251.0           NaN              NaN   \n",
       "\n",
       "      number_of_jobs  area_size  no_of_houses  borough_flag  \n",
       "0                NaN        NaN           NaN             1  \n",
       "1                NaN        NaN           NaN             1  \n",
       "2                NaN        NaN           NaN             1  \n",
       "3                NaN        NaN           NaN             1  \n",
       "4                NaN        NaN           NaN             1  \n",
       "...              ...        ...           ...           ...  \n",
       "1066             NaN        NaN           NaN             0  \n",
       "1067             NaN        NaN           NaN             0  \n",
       "1068             NaN        NaN           NaN             0  \n",
       "1069             NaN        NaN           NaN             0  \n",
       "1070             NaN        NaN           NaN             0  \n",
       "\n",
       "[1071 rows x 12 columns]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Data cleaning: column name change (from 'code' to 'AreaCode' for both)\n",
    "mean_salary_and_population_percode_df2 = mean_salary_and_population_percode_df.rename(columns={\"code\":\"AreaCode\",\\\n",
    "                                                                                               \"area\":\"RegionName\"})\n",
    "mean_salary_and_population_percode_df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "27cab2cb",
   "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>AreaCode</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>date</th>\n",
       "      <th>mean_salary</th>\n",
       "      <th>population_size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>E09000001</td>\n",
       "      <td>city of london</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E09000002</td>\n",
       "      <td>barking and dagenham</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>23620.0</td>\n",
       "      <td>162444.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E09000003</td>\n",
       "      <td>barnet</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>23128.0</td>\n",
       "      <td>313469.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>E09000004</td>\n",
       "      <td>bexley</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>21386.0</td>\n",
       "      <td>217458.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E09000005</td>\n",
       "      <td>brent</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>20911.0</td>\n",
       "      <td>260317.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1066</th>\n",
       "      <td>K03000001</td>\n",
       "      <td>great britain</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>37603.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067</th>\n",
       "      <td>K04000001</td>\n",
       "      <td>england and wales</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>37865.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1068</th>\n",
       "      <td>N92000002</td>\n",
       "      <td>northern ireland</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>32083.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1069</th>\n",
       "      <td>S92000003</td>\n",
       "      <td>scotland</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>34916.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1070</th>\n",
       "      <td>W92000004</td>\n",
       "      <td>wales</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>31251.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1071 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       AreaCode            RegionName        date  mean_salary  \\\n",
       "0     E09000001        city of london  01/12/1999      48922.0   \n",
       "1     E09000002  barking and dagenham  01/12/1999      23620.0   \n",
       "2     E09000003                barnet  01/12/1999      23128.0   \n",
       "3     E09000004                bexley  01/12/1999      21386.0   \n",
       "4     E09000005                 brent  01/12/1999      20911.0   \n",
       "...         ...                   ...         ...          ...   \n",
       "1066  K03000001         great britain  01/12/2019      37603.0   \n",
       "1067  K04000001     england and wales  01/12/2019      37865.0   \n",
       "1068  N92000002      northern ireland  01/12/2019      32083.0   \n",
       "1069  S92000003              scotland  01/12/2019      34916.0   \n",
       "1070  W92000004                 wales  01/12/2019      31251.0   \n",
       "\n",
       "      population_size  \n",
       "0              6581.0  \n",
       "1            162444.0  \n",
       "2            313469.0  \n",
       "3            217458.0  \n",
       "4            260317.0  \n",
       "...               ...  \n",
       "1066              NaN  \n",
       "1067              NaN  \n",
       "1068              NaN  \n",
       "1069              NaN  \n",
       "1070              NaN  \n",
       "\n",
       "[1071 rows x 5 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'mean salary and population' - Data cleaning: exclude unnecesary columns\n",
    "mean_salary_and_population_percode_df3 = mean_salary_and_population_percode_df2[[\"AreaCode\",\"RegionName\",\"date\",\\\n",
    "                                                                                 \"mean_salary\",\"population_size\"]]\n",
    "mean_salary_and_population_percode_df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "b62ce9b4",
   "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>RegionName</th>\n",
       "      <th>date</th>\n",
       "      <th>mean_salary</th>\n",
       "      <th>population_size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>city of london</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000002</th>\n",
       "      <td>barking and dagenham</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>23620.0</td>\n",
       "      <td>162444.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000003</th>\n",
       "      <td>barnet</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>23128.0</td>\n",
       "      <td>313469.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000004</th>\n",
       "      <td>bexley</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>21386.0</td>\n",
       "      <td>217458.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000005</th>\n",
       "      <td>brent</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>20911.0</td>\n",
       "      <td>260317.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K03000001</th>\n",
       "      <td>great britain</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>37603.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K04000001</th>\n",
       "      <td>england and wales</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>37865.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N92000002</th>\n",
       "      <td>northern ireland</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>32083.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S92000003</th>\n",
       "      <td>scotland</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>34916.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>W92000004</th>\n",
       "      <td>wales</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>31251.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1071 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     RegionName        date  mean_salary  population_size\n",
       "AreaCode                                                                 \n",
       "E09000001        city of london  01/12/1999      48922.0           6581.0\n",
       "E09000002  barking and dagenham  01/12/1999      23620.0         162444.0\n",
       "E09000003                barnet  01/12/1999      23128.0         313469.0\n",
       "E09000004                bexley  01/12/1999      21386.0         217458.0\n",
       "E09000005                 brent  01/12/1999      20911.0         260317.0\n",
       "...                         ...         ...          ...              ...\n",
       "K03000001         great britain  01/12/2019      37603.0              NaN\n",
       "K04000001     england and wales  01/12/2019      37865.0              NaN\n",
       "N92000002      northern ireland  01/12/2019      32083.0              NaN\n",
       "S92000003              scotland  01/12/2019      34916.0              NaN\n",
       "W92000004                 wales  01/12/2019      31251.0              NaN\n",
       "\n",
       "[1071 rows x 4 columns]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'mean salary and population' - Index: Set area code as Index\n",
    "mean_salary_and_population_percode_df4 = mean_salary_and_population_percode_df3.set_index(\"AreaCode\")\n",
    "mean_salary_and_population_percode_df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "c45499fb",
   "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>RegionName</th>\n",
       "      <th>date</th>\n",
       "      <th>mean_salary</th>\n",
       "      <th>population_size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>city of london</td>\n",
       "      <td>01/12/1999</td>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>city of london</td>\n",
       "      <td>01/12/2000</td>\n",
       "      <td>52203.0</td>\n",
       "      <td>7014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>city of london</td>\n",
       "      <td>01/12/2001</td>\n",
       "      <td>62819.0</td>\n",
       "      <td>7359.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>city of london</td>\n",
       "      <td>01/12/2002</td>\n",
       "      <td>56450.0</td>\n",
       "      <td>7280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>city of london</td>\n",
       "      <td>01/12/2003</td>\n",
       "      <td>64272.0</td>\n",
       "      <td>7115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>westminster</td>\n",
       "      <td>01/12/2015</td>\n",
       "      <td>53316.0</td>\n",
       "      <td>242299.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>westminster</td>\n",
       "      <td>01/12/2016</td>\n",
       "      <td>53888.0</td>\n",
       "      <td>247614.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>westminster</td>\n",
       "      <td>01/12/2017</td>\n",
       "      <td>59878.0</td>\n",
       "      <td>244796.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>westminster</td>\n",
       "      <td>01/12/2018</td>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>westminster</td>\n",
       "      <td>01/12/2019</td>\n",
       "      <td>60010.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>693 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               RegionName        date  mean_salary  population_size\n",
       "AreaCode                                                           \n",
       "E09000001  city of london  01/12/1999      48922.0           6581.0\n",
       "E09000001  city of london  01/12/2000      52203.0           7014.0\n",
       "E09000001  city of london  01/12/2001      62819.0           7359.0\n",
       "E09000001  city of london  01/12/2002      56450.0           7280.0\n",
       "E09000001  city of london  01/12/2003      64272.0           7115.0\n",
       "...                   ...         ...          ...              ...\n",
       "E09000033     westminster  01/12/2015      53316.0         242299.0\n",
       "E09000033     westminster  01/12/2016      53888.0         247614.0\n",
       "E09000033     westminster  01/12/2017      59878.0         244796.0\n",
       "E09000033     westminster  01/12/2018      63792.0         255324.0\n",
       "E09000033     westminster  01/12/2019      60010.0              NaN\n",
       "\n",
       "[693 rows x 4 columns]"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'mean salary and population' - Data cleaning: exclude unnecesary rows\n",
    "mean_salary_and_population_percode_df5 = mean_salary_and_population_percode_df4.loc[[\"E09000001\",\"E09000002\",\"E09000003\",\"E09000004\",\"E09000005\",\"E09000006\",\\\n",
    "                                             \"E09000007\",\"E09000008\",\"E09000009\",\"E09000010\",\"E09000011\",\"E09000012\",\\\n",
    "                                             \"E09000013\",\"E09000014\",\"E09000015\",\"E09000016\",\"E09000017\",\"E09000018\",\\\n",
    "                                             \"E09000019\",\"E09000020\",\"E09000021\",\"E09000022\",\"E09000023\",\"E09000024\",\\\n",
    "                                             \"E09000025\",\"E09000026\",\"E09000027\",\"E09000028\",\"E09000029\",\"E09000030\",\\\n",
    "                                             \"E09000031\",\"E09000032\",\"E09000033\"]]\n",
    "mean_salary_and_population_percode_df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "a07665c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RegionName         693\n",
       "date               693\n",
       "mean_salary        686\n",
       "population_size    660\n",
       "dtype: int64"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'mean salary and population' - Data Cleaning: list columns names and identify rows with missing info\n",
    "mean_salary_and_population_percode_df5.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "7f06b41f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RegionName         654\n",
       "date               654\n",
       "mean_salary        654\n",
       "population_size    654\n",
       "dtype: int64"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  'mean salary and population' - Data Cleaning: drop rows with missing info\n",
    "mean_salary_and_population_percode_df5 = mean_salary_and_population_percode_df5.dropna(how='any')\n",
    "mean_salary_and_population_percode_df5.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "2484f524",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RegionName          object\n",
       "date                object\n",
       "mean_salary        float64\n",
       "population_size    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  'mean salary and population' - Data Cleaning: check data type under column 'date'is in fact as date\n",
    "mean_salary_and_population_percode_df5.dtypes\n",
    "# note that 'date' shows as object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "cfde1320",
   "metadata": {},
   "outputs": [],
   "source": [
    "#  'mean salary and population' - Data Cleaning: convert the 'date' columns which is an object to datetime\n",
    "mean_salary_and_population_percode_df5['date'] = pd.to_datetime(mean_salary_and_population_percode_df5['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "98655b30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RegionName                 object\n",
       "date               datetime64[ns]\n",
       "mean_salary               float64\n",
       "population_size           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# note that 'date' shows as datetime\n",
    "mean_salary_and_population_percode_df5.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "e32537e9",
   "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></th>\n",
       "      <th>mean_salary</th>\n",
       "      <th>population_size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000001</th>\n",
       "      <th>1999-01-12</th>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-12</th>\n",
       "      <td>52203.0</td>\n",
       "      <td>7014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-01-12</th>\n",
       "      <td>62819.0</td>\n",
       "      <td>7359.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002-01-12</th>\n",
       "      <td>56450.0</td>\n",
       "      <td>7280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-01-12</th>\n",
       "      <td>64272.0</td>\n",
       "      <td>7115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000033</th>\n",
       "      <th>2014-01-12</th>\n",
       "      <td>51099.0</td>\n",
       "      <td>233292.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-12</th>\n",
       "      <td>53316.0</td>\n",
       "      <td>242299.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-12</th>\n",
       "      <td>53888.0</td>\n",
       "      <td>247614.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-12</th>\n",
       "      <td>59878.0</td>\n",
       "      <td>244796.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-12</th>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>654 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      mean_salary  population_size\n",
       "AreaCode  date                                    \n",
       "E09000001 1999-01-12      48922.0           6581.0\n",
       "          2000-01-12      52203.0           7014.0\n",
       "          2001-01-12      62819.0           7359.0\n",
       "          2002-01-12      56450.0           7280.0\n",
       "          2003-01-12      64272.0           7115.0\n",
       "...                           ...              ...\n",
       "E09000033 2014-01-12      51099.0         233292.0\n",
       "          2015-01-12      53316.0         242299.0\n",
       "          2016-01-12      53888.0         247614.0\n",
       "          2017-01-12      59878.0         244796.0\n",
       "          2018-01-12      63792.0         255324.0\n",
       "\n",
       "[654 rows x 2 columns]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'mean salary and population': Group by year???????????????????????????\n",
    "mean_salary_and_population_percode_df5.groupby(['AreaCode','date']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "deee24cd",
   "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></th>\n",
       "      <th>mean_salary</th>\n",
       "      <th>population_size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000001</th>\n",
       "      <th>1999</th>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>52203.0</td>\n",
       "      <td>7014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>62819.0</td>\n",
       "      <td>7359.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>56450.0</td>\n",
       "      <td>7280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>64272.0</td>\n",
       "      <td>7115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000033</th>\n",
       "      <th>2014</th>\n",
       "      <td>51099.0</td>\n",
       "      <td>233292.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>53316.0</td>\n",
       "      <td>242299.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>53888.0</td>\n",
       "      <td>247614.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>59878.0</td>\n",
       "      <td>244796.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>654 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                mean_salary  population_size\n",
       "AreaCode  Year                              \n",
       "E09000001 1999      48922.0           6581.0\n",
       "          2000      52203.0           7014.0\n",
       "          2001      62819.0           7359.0\n",
       "          2002      56450.0           7280.0\n",
       "          2003      64272.0           7115.0\n",
       "...                     ...              ...\n",
       "E09000033 2014      51099.0         233292.0\n",
       "          2015      53316.0         242299.0\n",
       "          2016      53888.0         247614.0\n",
       "          2017      59878.0         244796.0\n",
       "          2018      63792.0         255324.0\n",
       "\n",
       "[654 rows x 2 columns]"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_salary_and_population_percode_df6 = mean_salary_and_population_percode_df5[['date', 'mean_salary', 'population_size']].copy()\n",
    "\n",
    "mean_salary_and_population_percode_df6['Year'] = mean_salary_and_population_percode_df6['date'].dt.year\n",
    "mean_salary_and_population_percode_df6\n",
    "\n",
    "mean_salary_and_population_percode_df7 = mean_salary_and_population_percode_df6[['Year','mean_salary', 'population_size']].copy()\n",
    "\n",
    "mean_salary_and_population_percode_df7.groupby(['AreaCode','Year']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4979aa1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d727496",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "79032aeb",
   "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>Date</th>\n",
       "      <th>AreaCode</th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01/01/2004</td>\n",
       "      <td>S12000034</td>\n",
       "      <td>81693.66964</td>\n",
       "      <td>388.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01/02/2004</td>\n",
       "      <td>S12000034</td>\n",
       "      <td>81678.76231</td>\n",
       "      <td>326.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01/03/2004</td>\n",
       "      <td>S12000034</td>\n",
       "      <td>83525.09702</td>\n",
       "      <td>453.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01/04/2004</td>\n",
       "      <td>S12000034</td>\n",
       "      <td>84333.67900</td>\n",
       "      <td>571.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01/05/2004</td>\n",
       "      <td>S12000034</td>\n",
       "      <td>86379.95396</td>\n",
       "      <td>502.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139876</th>\n",
       "      <td>01/08/2022</td>\n",
       "      <td>E12000003</td>\n",
       "      <td>210137.56000</td>\n",
       "      <td>6045.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139877</th>\n",
       "      <td>01/09/2022</td>\n",
       "      <td>E12000003</td>\n",
       "      <td>212196.46930</td>\n",
       "      <td>5748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139878</th>\n",
       "      <td>01/10/2022</td>\n",
       "      <td>E12000003</td>\n",
       "      <td>212847.74490</td>\n",
       "      <td>5139.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139879</th>\n",
       "      <td>01/11/2022</td>\n",
       "      <td>E12000003</td>\n",
       "      <td>213389.07510</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139880</th>\n",
       "      <td>01/12/2022</td>\n",
       "      <td>E12000003</td>\n",
       "      <td>214772.92560</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>139881 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              Date   AreaCode  AveragePrice  SalesVolume\n",
       "0       01/01/2004  S12000034   81693.66964        388.0\n",
       "1       01/02/2004  S12000034   81678.76231        326.0\n",
       "2       01/03/2004  S12000034   83525.09702        453.0\n",
       "3       01/04/2004  S12000034   84333.67900        571.0\n",
       "4       01/05/2004  S12000034   86379.95396        502.0\n",
       "...            ...        ...           ...          ...\n",
       "139876  01/08/2022  E12000003  210137.56000       6045.0\n",
       "139877  01/09/2022  E12000003  212196.46930       5748.0\n",
       "139878  01/10/2022  E12000003  212847.74490       5139.0\n",
       "139879  01/11/2022  E12000003  213389.07510          NaN\n",
       "139880  01/12/2022  E12000003  214772.92560          NaN\n",
       "\n",
       "[139881 rows x 4 columns]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'average house price' - Data cleaning: exclude unnecesary columns\n",
    "average_house_price_percode_df2 = average_house_price_percode_df[[\"Date\",\"AreaCode\",\"AveragePrice\",\\\n",
    "                                                                  \"SalesVolume\"]]\n",
    "average_house_price_percode_df2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "19ede20c",
   "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>Date</th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S12000034</th>\n",
       "      <td>01/01/2004</td>\n",
       "      <td>81693.66964</td>\n",
       "      <td>388.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S12000034</th>\n",
       "      <td>01/02/2004</td>\n",
       "      <td>81678.76231</td>\n",
       "      <td>326.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S12000034</th>\n",
       "      <td>01/03/2004</td>\n",
       "      <td>83525.09702</td>\n",
       "      <td>453.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S12000034</th>\n",
       "      <td>01/04/2004</td>\n",
       "      <td>84333.67900</td>\n",
       "      <td>571.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S12000034</th>\n",
       "      <td>01/05/2004</td>\n",
       "      <td>86379.95396</td>\n",
       "      <td>502.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E12000003</th>\n",
       "      <td>01/08/2022</td>\n",
       "      <td>210137.56000</td>\n",
       "      <td>6045.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E12000003</th>\n",
       "      <td>01/09/2022</td>\n",
       "      <td>212196.46930</td>\n",
       "      <td>5748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E12000003</th>\n",
       "      <td>01/10/2022</td>\n",
       "      <td>212847.74490</td>\n",
       "      <td>5139.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E12000003</th>\n",
       "      <td>01/11/2022</td>\n",
       "      <td>213389.07510</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E12000003</th>\n",
       "      <td>01/12/2022</td>\n",
       "      <td>214772.92560</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>139881 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Date  AveragePrice  SalesVolume\n",
       "AreaCode                                        \n",
       "S12000034  01/01/2004   81693.66964        388.0\n",
       "S12000034  01/02/2004   81678.76231        326.0\n",
       "S12000034  01/03/2004   83525.09702        453.0\n",
       "S12000034  01/04/2004   84333.67900        571.0\n",
       "S12000034  01/05/2004   86379.95396        502.0\n",
       "...               ...           ...          ...\n",
       "E12000003  01/08/2022  210137.56000       6045.0\n",
       "E12000003  01/09/2022  212196.46930       5748.0\n",
       "E12000003  01/10/2022  212847.74490       5139.0\n",
       "E12000003  01/11/2022  213389.07510          NaN\n",
       "E12000003  01/12/2022  214772.92560          NaN\n",
       "\n",
       "[139881 rows x 3 columns]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'average house price' - Index: Set area code as Index\n",
    "average_house_price_percode_df3  = average_house_price_percode_df2.set_index(\"AreaCode\")\n",
    "average_house_price_percode_df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "7c493112",
   "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>Date</th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/01/1995</td>\n",
       "      <td>91448.98487</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/02/1995</td>\n",
       "      <td>82202.77314</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/03/1995</td>\n",
       "      <td>79120.70256</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/04/1995</td>\n",
       "      <td>77101.20804</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/05/1995</td>\n",
       "      <td>84409.14932</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/08/2022</td>\n",
       "      <td>962929.98090</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/09/2022</td>\n",
       "      <td>929921.82360</td>\n",
       "      <td>162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/10/2022</td>\n",
       "      <td>928549.81200</td>\n",
       "      <td>157.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/11/2022</td>\n",
       "      <td>909744.62800</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/12/2022</td>\n",
       "      <td>910286.63570</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11088 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Date  AveragePrice  SalesVolume\n",
       "AreaCode                                        \n",
       "E09000001  01/01/1995   91448.98487         17.0\n",
       "E09000001  01/02/1995   82202.77314          7.0\n",
       "E09000001  01/03/1995   79120.70256         14.0\n",
       "E09000001  01/04/1995   77101.20804          7.0\n",
       "E09000001  01/05/1995   84409.14932         10.0\n",
       "...               ...           ...          ...\n",
       "E09000033  01/08/2022  962929.98090        178.0\n",
       "E09000033  01/09/2022  929921.82360        162.0\n",
       "E09000033  01/10/2022  928549.81200        157.0\n",
       "E09000033  01/11/2022  909744.62800          NaN\n",
       "E09000033  01/12/2022  910286.63570          NaN\n",
       "\n",
       "[11088 rows x 3 columns]"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'average house price' - Data cleaning: exclude unnecesary rows\n",
    "average_house_price_percode_df4 = average_house_price_percode_df3.loc[[\"E09000001\",\"E09000002\",\"E09000003\",\"E09000004\",\"E09000005\",\"E09000006\",\\\n",
    "                                             \"E09000007\",\"E09000008\",\"E09000009\",\"E09000010\",\"E09000011\",\"E09000012\",\\\n",
    "                                             \"E09000013\",\"E09000014\",\"E09000015\",\"E09000016\",\"E09000017\",\"E09000018\",\\\n",
    "                                             \"E09000019\",\"E09000020\",\"E09000021\",\"E09000022\",\"E09000023\",\"E09000024\",\\\n",
    "                                             \"E09000025\",\"E09000026\",\"E09000027\",\"E09000028\",\"E09000029\",\"E09000030\",\\\n",
    "                                             \"E09000031\",\"E09000032\",\"E09000033\"]]\n",
    "average_house_price_percode_df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "529c6a28",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date            11088\n",
       "AveragePrice    11088\n",
       "SalesVolume     11022\n",
       "dtype: int64"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'average house price' - Data Cleaning: list columns names and and identify rows with missing info\n",
    "average_house_price_percode_df4.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "9b961b63",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date            11022\n",
       "AveragePrice    11022\n",
       "SalesVolume     11022\n",
       "dtype: int64"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'average house price' - Data Cleaning: drop rows with missing info\n",
    "average_house_price_percode_df4 = average_house_price_percode_df4.dropna(how='any')\n",
    "average_house_price_percode_df4.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "67bb07f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date             object\n",
       "AveragePrice    float64\n",
       "SalesVolume     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  'average house price' - Data Cleaning: check data type under column 'date'is in fact as date\n",
    "average_house_price_percode_df4.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "90eb51dc",
   "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>Date</th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/01/1995</td>\n",
       "      <td>91448.98487</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/02/1995</td>\n",
       "      <td>82202.77314</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/03/1995</td>\n",
       "      <td>79120.70256</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/04/1995</td>\n",
       "      <td>77101.20804</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>01/05/1995</td>\n",
       "      <td>84409.14932</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/06/2022</td>\n",
       "      <td>971858.37280</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/07/2022</td>\n",
       "      <td>956176.16800</td>\n",
       "      <td>190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/08/2022</td>\n",
       "      <td>962929.98090</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/09/2022</td>\n",
       "      <td>929921.82360</td>\n",
       "      <td>162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>01/10/2022</td>\n",
       "      <td>928549.81200</td>\n",
       "      <td>157.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11022 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Date  AveragePrice  SalesVolume\n",
       "AreaCode                                        \n",
       "E09000001  01/01/1995   91448.98487         17.0\n",
       "E09000001  01/02/1995   82202.77314          7.0\n",
       "E09000001  01/03/1995   79120.70256         14.0\n",
       "E09000001  01/04/1995   77101.20804          7.0\n",
       "E09000001  01/05/1995   84409.14932         10.0\n",
       "...               ...           ...          ...\n",
       "E09000033  01/06/2022  971858.37280        178.0\n",
       "E09000033  01/07/2022  956176.16800        190.0\n",
       "E09000033  01/08/2022  962929.98090        178.0\n",
       "E09000033  01/09/2022  929921.82360        162.0\n",
       "E09000033  01/10/2022  928549.81200        157.0\n",
       "\n",
       "[11022 rows x 3 columns]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "average_house_price_percode_df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "41e37c3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#  'average house price' - Data Cleaning: convert the 'date' columns which is an object to datetime\n",
    "average_house_price_percode_df4['Date'] = pd.to_datetime(average_house_price_percode_df4['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "4f6ea49c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date            datetime64[ns]\n",
       "AveragePrice           float64\n",
       "SalesVolume            float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# note that 'Date' shows as datetime\n",
    "average_house_price_percode_df4.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "0db0e0fb",
   "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>Date</th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>91448.98487</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-02</td>\n",
       "      <td>82202.77314</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-03</td>\n",
       "      <td>79120.70256</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-04</td>\n",
       "      <td>77101.20804</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-05</td>\n",
       "      <td>84409.14932</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-06</td>\n",
       "      <td>971858.37280</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-07</td>\n",
       "      <td>956176.16800</td>\n",
       "      <td>190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-08</td>\n",
       "      <td>962929.98090</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-09</td>\n",
       "      <td>929921.82360</td>\n",
       "      <td>162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-10</td>\n",
       "      <td>928549.81200</td>\n",
       "      <td>157.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11022 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                Date  AveragePrice  SalesVolume\n",
       "AreaCode                                       \n",
       "E09000001 1995-01-01   91448.98487         17.0\n",
       "E09000001 1995-01-02   82202.77314          7.0\n",
       "E09000001 1995-01-03   79120.70256         14.0\n",
       "E09000001 1995-01-04   77101.20804          7.0\n",
       "E09000001 1995-01-05   84409.14932         10.0\n",
       "...              ...           ...          ...\n",
       "E09000033 2022-01-06  971858.37280        178.0\n",
       "E09000033 2022-01-07  956176.16800        190.0\n",
       "E09000033 2022-01-08  962929.98090        178.0\n",
       "E09000033 2022-01-09  929921.82360        162.0\n",
       "E09000033 2022-01-10  928549.81200        157.0\n",
       "\n",
       "[11022 rows x 3 columns]"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "average_house_price_percode_df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "d0e73f13",
   "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>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1995</th>\n",
       "      <td>3.280194e+07</td>\n",
       "      <td>106850.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>3.424804e+07</td>\n",
       "      <td>132953.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>3.916473e+07</td>\n",
       "      <td>154343.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998</th>\n",
       "      <td>4.482914e+07</td>\n",
       "      <td>145942.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>5.053808e+07</td>\n",
       "      <td>169076.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>6.245691e+07</td>\n",
       "      <td>149269.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>7.003040e+07</td>\n",
       "      <td>162744.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>8.089315e+07</td>\n",
       "      <td>173993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>9.006482e+07</td>\n",
       "      <td>153784.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>9.764308e+07</td>\n",
       "      <td>163797.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>1.008809e+08</td>\n",
       "      <td>136836.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>1.078648e+08</td>\n",
       "      <td>171450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>1.248679e+08</td>\n",
       "      <td>165571.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>1.265221e+08</td>\n",
       "      <td>80921.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>1.138948e+08</td>\n",
       "      <td>75231.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>1.271847e+08</td>\n",
       "      <td>91933.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>1.311592e+08</td>\n",
       "      <td>90041.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>1.384040e+08</td>\n",
       "      <td>93859.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>1.506755e+08</td>\n",
       "      <td>111973.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>1.762528e+08</td>\n",
       "      <td>121949.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>1.915399e+08</td>\n",
       "      <td>116572.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>2.066547e+08</td>\n",
       "      <td>104758.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>2.132504e+08</td>\n",
       "      <td>96009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>2.112305e+08</td>\n",
       "      <td>89875.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>2.070772e+08</td>\n",
       "      <td>86218.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>2.126461e+08</td>\n",
       "      <td>74812.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>2.187872e+08</td>\n",
       "      <td>107962.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>1.916859e+08</td>\n",
       "      <td>66106.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      AveragePrice  SalesVolume\n",
       "Date                           \n",
       "1995  3.280194e+07     106850.0\n",
       "1996  3.424804e+07     132953.0\n",
       "1997  3.916473e+07     154343.0\n",
       "1998  4.482914e+07     145942.0\n",
       "1999  5.053808e+07     169076.0\n",
       "2000  6.245691e+07     149269.0\n",
       "2001  7.003040e+07     162744.0\n",
       "2002  8.089315e+07     173993.0\n",
       "2003  9.006482e+07     153784.0\n",
       "2004  9.764308e+07     163797.0\n",
       "2005  1.008809e+08     136836.0\n",
       "2006  1.078648e+08     171450.0\n",
       "2007  1.248679e+08     165571.0\n",
       "2008  1.265221e+08      80921.0\n",
       "2009  1.138948e+08      75231.0\n",
       "2010  1.271847e+08      91933.0\n",
       "2011  1.311592e+08      90041.0\n",
       "2012  1.384040e+08      93859.0\n",
       "2013  1.506755e+08     111973.0\n",
       "2014  1.762528e+08     121949.0\n",
       "2015  1.915399e+08     116572.0\n",
       "2016  2.066547e+08     104758.0\n",
       "2017  2.132504e+08      96009.0\n",
       "2018  2.112305e+08      89875.0\n",
       "2019  2.070772e+08      86218.0\n",
       "2020  2.126461e+08      74812.0\n",
       "2021  2.187872e+08     107962.0\n",
       "2022  1.916859e+08      66106.0"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'average house price': Group by year???????????????????????????\n",
    "average_house_price_percode_df4.groupby([average_house_price_percode_df4['Date'].dt.year]).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "9f8e6b2a",
   "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></th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000001</th>\n",
       "      <th>1995-01-01</th>\n",
       "      <td>91448.98487</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995-01-02</th>\n",
       "      <td>82202.77314</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995-01-03</th>\n",
       "      <td>79120.70256</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995-01-04</th>\n",
       "      <td>77101.20804</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995-01-05</th>\n",
       "      <td>84409.14932</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000033</th>\n",
       "      <th>2022-01-06</th>\n",
       "      <td>971858.37280</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-07</th>\n",
       "      <td>956176.16800</td>\n",
       "      <td>190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-08</th>\n",
       "      <td>962929.98090</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-09</th>\n",
       "      <td>929921.82360</td>\n",
       "      <td>162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-10</th>\n",
       "      <td>928549.81200</td>\n",
       "      <td>157.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11022 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      AveragePrice  SalesVolume\n",
       "AreaCode  Date                                 \n",
       "E09000001 1995-01-01   91448.98487         17.0\n",
       "          1995-01-02   82202.77314          7.0\n",
       "          1995-01-03   79120.70256         14.0\n",
       "          1995-01-04   77101.20804          7.0\n",
       "          1995-01-05   84409.14932         10.0\n",
       "...                            ...          ...\n",
       "E09000033 2022-01-06  971858.37280        178.0\n",
       "          2022-01-07  956176.16800        190.0\n",
       "          2022-01-08  962929.98090        178.0\n",
       "          2022-01-09  929921.82360        162.0\n",
       "          2022-01-10  928549.81200        157.0\n",
       "\n",
       "[11022 rows x 2 columns]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'average house price': Group by year???????????????????????????\n",
    "average_house_price_percode_df4.groupby(['AreaCode','Date']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "9eca7981",
   "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></th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000001</th>\n",
       "      <th>1995</th>\n",
       "      <td>1.189020e+06</td>\n",
       "      <td>159.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>1.267813e+06</td>\n",
       "      <td>282.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>1.405361e+06</td>\n",
       "      <td>315.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998</th>\n",
       "      <td>1.776383e+06</td>\n",
       "      <td>424.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>2.055601e+06</td>\n",
       "      <td>536.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000033</th>\n",
       "      <th>2018</th>\n",
       "      <td>1.223840e+07</td>\n",
       "      <td>2088.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>1.143475e+07</td>\n",
       "      <td>2123.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>1.154054e+07</td>\n",
       "      <td>1803.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>1.153687e+07</td>\n",
       "      <td>2400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>9.892464e+06</td>\n",
       "      <td>1769.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>924 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                AveragePrice  SalesVolume\n",
       "AreaCode  Year                           \n",
       "E09000001 1995  1.189020e+06        159.0\n",
       "          1996  1.267813e+06        282.0\n",
       "          1997  1.405361e+06        315.0\n",
       "          1998  1.776383e+06        424.0\n",
       "          1999  2.055601e+06        536.0\n",
       "...                      ...          ...\n",
       "E09000033 2018  1.223840e+07       2088.0\n",
       "          2019  1.143475e+07       2123.0\n",
       "          2020  1.154054e+07       1803.0\n",
       "          2021  1.153687e+07       2400.0\n",
       "          2022  9.892464e+06       1769.0\n",
       "\n",
       "[924 rows x 2 columns]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "average_house_price_percode_df5 = average_house_price_percode_df4[['Date', 'AveragePrice', 'SalesVolume']].copy()\n",
    "\n",
    "average_house_price_percode_df5['Year'] = average_house_price_percode_df5['Date'].dt.year\n",
    "average_house_price_percode_df5\n",
    "\n",
    "average_house_price_percode_df6 = average_house_price_percode_df5[['Year','AveragePrice', 'SalesVolume']].copy()\n",
    "\n",
    "average_house_price_percode_df6.groupby(['AreaCode','Year']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "474463b7",
   "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></th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000001</th>\n",
       "      <th>1995</th>\n",
       "      <td>9.908501e+04</td>\n",
       "      <td>13.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>1.056511e+05</td>\n",
       "      <td>23.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>1.171134e+05</td>\n",
       "      <td>26.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998</th>\n",
       "      <td>1.480319e+05</td>\n",
       "      <td>35.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>1.713001e+05</td>\n",
       "      <td>44.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000033</th>\n",
       "      <th>2018</th>\n",
       "      <td>1.019866e+06</td>\n",
       "      <td>174.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>9.528957e+05</td>\n",
       "      <td>176.916667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>9.617118e+05</td>\n",
       "      <td>150.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>9.614057e+05</td>\n",
       "      <td>200.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>9.892464e+05</td>\n",
       "      <td>176.900000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>924 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                AveragePrice  SalesVolume\n",
       "AreaCode  Year                           \n",
       "E09000001 1995  9.908501e+04    13.250000\n",
       "          1996  1.056511e+05    23.500000\n",
       "          1997  1.171134e+05    26.250000\n",
       "          1998  1.480319e+05    35.333333\n",
       "          1999  1.713001e+05    44.666667\n",
       "...                      ...          ...\n",
       "E09000033 2018  1.019866e+06   174.000000\n",
       "          2019  9.528957e+05   176.916667\n",
       "          2020  9.617118e+05   150.250000\n",
       "          2021  9.614057e+05   200.000000\n",
       "          2022  9.892464e+05   176.900000\n",
       "\n",
       "[924 rows x 2 columns]"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "average_house_price_percode_df6.groupby(['AreaCode','Year']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "bbb1f3fd",
   "metadata": {
    "executionInfo": {
     "elapsed": 14,
     "status": "aborted",
     "timestamp": 1678703178166,
     "user": {
      "displayName": "Diego Lorenzo",
      "userId": "13240690789513449880"
     },
     "user_tz": 0
    },
    "id": "bbb1f3fd"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year_x</th>\n",
       "      <th>mean_salary</th>\n",
       "      <th>population_size</th>\n",
       "      <th>Year_y</th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1999</td>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "      <td>1995</td>\n",
       "      <td>91448.98487</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1999</td>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "      <td>1995</td>\n",
       "      <td>82202.77314</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1999</td>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "      <td>1995</td>\n",
       "      <td>79120.70256</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1999</td>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "      <td>1995</td>\n",
       "      <td>77101.20804</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1999</td>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "      <td>1995</td>\n",
       "      <td>84409.14932</td>\n",
       "      <td>10.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2018</td>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>971858.37280</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2018</td>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>956176.16800</td>\n",
       "      <td>190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2018</td>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>962929.98090</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2018</td>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>929921.82360</td>\n",
       "      <td>162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2018</td>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>928549.81200</td>\n",
       "      <td>157.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>218436 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Year_x  mean_salary  population_size  Year_y  AveragePrice  \\\n",
       "AreaCode                                                                \n",
       "E09000001    1999      48922.0           6581.0    1995   91448.98487   \n",
       "E09000001    1999      48922.0           6581.0    1995   82202.77314   \n",
       "E09000001    1999      48922.0           6581.0    1995   79120.70256   \n",
       "E09000001    1999      48922.0           6581.0    1995   77101.20804   \n",
       "E09000001    1999      48922.0           6581.0    1995   84409.14932   \n",
       "...           ...          ...              ...     ...           ...   \n",
       "E09000033    2018      63792.0         255324.0    2022  971858.37280   \n",
       "E09000033    2018      63792.0         255324.0    2022  956176.16800   \n",
       "E09000033    2018      63792.0         255324.0    2022  962929.98090   \n",
       "E09000033    2018      63792.0         255324.0    2022  929921.82360   \n",
       "E09000033    2018      63792.0         255324.0    2022  928549.81200   \n",
       "\n",
       "           SalesVolume  \n",
       "AreaCode                \n",
       "E09000001         17.0  \n",
       "E09000001          7.0  \n",
       "E09000001         14.0  \n",
       "E09000001          7.0  \n",
       "E09000001         10.0  \n",
       "...                ...  \n",
       "E09000033        178.0  \n",
       "E09000033        190.0  \n",
       "E09000033        178.0  \n",
       "E09000033        162.0  \n",
       "E09000033        157.0  \n",
       "\n",
       "[218436 rows x 6 columns]"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Combine the data into a single dataset ?????????????????\n",
    "housedata_df = pd.merge(mean_salary_and_population_percode_df7,average_house_price_percode_df6,on='AreaCode')\n",
    "housedata_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "f2e164c8",
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (2093077261.py, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"C:\\Users\\diern\\AppData\\Local\\Temp\\ipykernel_22108\\2093077261.py\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m    full_data_ group = housedata_df.groupby(['AreaCode','Year']).mean()\u001b[0m\n\u001b[1;37m               ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "full_data_ group = housedata_df.groupby(['AreaCode','Year']).mean()\n",
    "df7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "c590558e",
   "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></th>\n",
       "      <th>mean_salary</th>\n",
       "      <th>population_size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000001</th>\n",
       "      <th>1999</th>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>52203.0</td>\n",
       "      <td>7014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>62819.0</td>\n",
       "      <td>7359.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>56450.0</td>\n",
       "      <td>7280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>64272.0</td>\n",
       "      <td>7115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000033</th>\n",
       "      <th>2014</th>\n",
       "      <td>51099.0</td>\n",
       "      <td>233292.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>53316.0</td>\n",
       "      <td>242299.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>53888.0</td>\n",
       "      <td>247614.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>59878.0</td>\n",
       "      <td>244796.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>654 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                mean_salary  population_size\n",
       "AreaCode  Year                              \n",
       "E09000001 1999      48922.0           6581.0\n",
       "          2000      52203.0           7014.0\n",
       "          2001      62819.0           7359.0\n",
       "          2002      56450.0           7280.0\n",
       "          2003      64272.0           7115.0\n",
       "...                     ...              ...\n",
       "E09000033 2014      51099.0         233292.0\n",
       "          2015      53316.0         242299.0\n",
       "          2016      53888.0         247614.0\n",
       "          2017      59878.0         244796.0\n",
       "          2018      63792.0         255324.0\n",
       "\n",
       "[654 rows x 2 columns]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df7_new = mean_salary_and_population_percode_df7.groupby([\"AreaCode\", \"Year\"]).mean()\n",
    "df7_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "e2e6626f",
   "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></th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000001</th>\n",
       "      <th>1995</th>\n",
       "      <td>9.908501e+04</td>\n",
       "      <td>13.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>1.056511e+05</td>\n",
       "      <td>23.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>1.171134e+05</td>\n",
       "      <td>26.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998</th>\n",
       "      <td>1.480319e+05</td>\n",
       "      <td>35.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>1.713001e+05</td>\n",
       "      <td>44.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000033</th>\n",
       "      <th>2018</th>\n",
       "      <td>1.019866e+06</td>\n",
       "      <td>174.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>9.528957e+05</td>\n",
       "      <td>176.916667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>9.617118e+05</td>\n",
       "      <td>150.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>9.614057e+05</td>\n",
       "      <td>200.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>9.892464e+05</td>\n",
       "      <td>176.900000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>924 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                AveragePrice  SalesVolume\n",
       "AreaCode  Year                           \n",
       "E09000001 1995  9.908501e+04    13.250000\n",
       "          1996  1.056511e+05    23.500000\n",
       "          1997  1.171134e+05    26.250000\n",
       "          1998  1.480319e+05    35.333333\n",
       "          1999  1.713001e+05    44.666667\n",
       "...                      ...          ...\n",
       "E09000033 2018  1.019866e+06   174.000000\n",
       "          2019  9.528957e+05   176.916667\n",
       "          2020  9.617118e+05   150.250000\n",
       "          2021  9.614057e+05   200.000000\n",
       "          2022  9.892464e+05   176.900000\n",
       "\n",
       "[924 rows x 2 columns]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df6_new = average_house_price_percode_df6.groupby([\"AreaCode\", \"Year\"]).mean()\n",
    "df6_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "9061e57b",
   "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></th>\n",
       "      <th>mean_salary</th>\n",
       "      <th>population_size</th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000001</th>\n",
       "      <th>1999</th>\n",
       "      <td>48922.0</td>\n",
       "      <td>6581.0</td>\n",
       "      <td>1.713001e+05</td>\n",
       "      <td>44.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>52203.0</td>\n",
       "      <td>7014.0</td>\n",
       "      <td>2.186790e+05</td>\n",
       "      <td>35.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>62819.0</td>\n",
       "      <td>7359.0</td>\n",
       "      <td>2.506256e+05</td>\n",
       "      <td>30.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>56450.0</td>\n",
       "      <td>7280.0</td>\n",
       "      <td>2.696322e+05</td>\n",
       "      <td>32.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>64272.0</td>\n",
       "      <td>7115.0</td>\n",
       "      <td>2.708115e+05</td>\n",
       "      <td>40.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E09000033</th>\n",
       "      <th>2014</th>\n",
       "      <td>51099.0</td>\n",
       "      <td>233292.0</td>\n",
       "      <td>9.227022e+05</td>\n",
       "      <td>303.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>53316.0</td>\n",
       "      <td>242299.0</td>\n",
       "      <td>9.646431e+05</td>\n",
       "      <td>243.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>53888.0</td>\n",
       "      <td>247614.0</td>\n",
       "      <td>9.955434e+05</td>\n",
       "      <td>218.916667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>59878.0</td>\n",
       "      <td>244796.0</td>\n",
       "      <td>1.064772e+06</td>\n",
       "      <td>213.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>63792.0</td>\n",
       "      <td>255324.0</td>\n",
       "      <td>1.019866e+06</td>\n",
       "      <td>174.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>654 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                mean_salary  population_size  AveragePrice  SalesVolume\n",
       "AreaCode  Year                                                         \n",
       "E09000001 1999      48922.0           6581.0  1.713001e+05    44.666667\n",
       "          2000      52203.0           7014.0  2.186790e+05    35.666667\n",
       "          2001      62819.0           7359.0  2.506256e+05    30.583333\n",
       "          2002      56450.0           7280.0  2.696322e+05    32.666667\n",
       "          2003      64272.0           7115.0  2.708115e+05    40.000000\n",
       "...                     ...              ...           ...          ...\n",
       "E09000033 2014      51099.0         233292.0  9.227022e+05   303.333333\n",
       "          2015      53316.0         242299.0  9.646431e+05   243.833333\n",
       "          2016      53888.0         247614.0  9.955434e+05   218.916667\n",
       "          2017      59878.0         244796.0  1.064772e+06   213.083333\n",
       "          2018      63792.0         255324.0  1.019866e+06   174.000000\n",
       "\n",
       "[654 rows x 4 columns]"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "housedata_df = pd.merge(df7_new,df6_new,on=['AreaCode', 'Year'])\n",
    "housedata_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "44eb8258",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "mean_salary        float64\n",
       "population_size    float64\n",
       "AveragePrice       float64\n",
       "SalesVolume        float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "housedata_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "334765c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AreaCode   Year\n",
       "E09000001  1999     56711.932135\n",
       "           2000     69482.923052\n",
       "           2001     80208.534444\n",
       "           2002     83348.727488\n",
       "           2003     85559.612723\n",
       "                       ...      \n",
       "E09000033  1998     95488.766638\n",
       "           2019    476536.325433\n",
       "           2020    480931.042025\n",
       "           2021    480802.872058\n",
       "           2022    494711.653950\n",
       "Length: 924, dtype: float64"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "housedata_df2 = pd.concat([df7_new,df6_new], axis=1).mean(axis=1)\n",
    "housedata_df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "54d33e7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "housedata_df.to_csv('out.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85dd3dc3",
   "metadata": {
    "id": "85dd3dc3"
   },
   "outputs": [],
   "source": [
    "# Group by borough\n",
    "#london_boroughs_df2.groupby('date').average()\n",
    "#london_boroughs_groupbyyear_df2 = london_boroughs_groupbyyear_df.sum()\n",
    "\n",
    "#Year, average prices in London and mean salary - group by year pandas\n",
    "#grouped = df.groupby('year').agg({'average_price': 'mean', 'mean_salary': 'mean'})\n",
    "\n",
    "# Group per year: 'average house price' from 12 months to year\n",
    "#average_house_price_percode_df4.groupby(average_house_price_percode_df4.Date.dt.year)['AveragePrice'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "ab88f564",
   "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>Date</th>\n",
       "      <th>AveragePrice</th>\n",
       "      <th>SalesVolume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AreaCode</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>91448.98487</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-02</td>\n",
       "      <td>82202.77314</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-03</td>\n",
       "      <td>79120.70256</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-04</td>\n",
       "      <td>77101.20804</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000001</th>\n",
       "      <td>1995-01-05</td>\n",
       "      <td>84409.14932</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-06</td>\n",
       "      <td>971858.37280</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-07</td>\n",
       "      <td>956176.16800</td>\n",
       "      <td>190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-08</td>\n",
       "      <td>962929.98090</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-09</td>\n",
       "      <td>929921.82360</td>\n",
       "      <td>162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E09000033</th>\n",
       "      <td>2022-01-10</td>\n",
       "      <td>928549.81200</td>\n",
       "      <td>157.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11022 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                Date  AveragePrice  SalesVolume\n",
       "AreaCode                                       \n",
       "E09000001 1995-01-01   91448.98487         17.0\n",
       "E09000001 1995-01-02   82202.77314          7.0\n",
       "E09000001 1995-01-03   79120.70256         14.0\n",
       "E09000001 1995-01-04   77101.20804          7.0\n",
       "E09000001 1995-01-05   84409.14932         10.0\n",
       "...              ...           ...          ...\n",
       "E09000033 2022-01-06  971858.37280        178.0\n",
       "E09000033 2022-01-07  956176.16800        190.0\n",
       "E09000033 2022-01-08  962929.98090        178.0\n",
       "E09000033 2022-01-09  929921.82360        162.0\n",
       "E09000033 2022-01-10  928549.81200        157.0\n",
       "\n",
       "[11022 rows x 3 columns]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SANDBOX\n",
    "average_house_price_percode_df4"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}