nsw_land_sales_exploratory.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "NSW_LAND_VALUE_FILE_PATH = \"./data/NSW_Land_Sales_Data\"\n",
    "CSV_FORMAT = \"*.csv\"\n",
    "\n",
    "LV_2019 = \"data/NSW_Land_Sales_Data/LV_20190801/088_LAND_VALUE_DATA_20190801.csv\"\n",
    "LV_2024 = \"data/NSW_Land_Sales_Data/LV_20240301/088_LAND_VALUE_DATA_20240301.csv\"\n",
    "LV2019_df = pd.read_csv(LV_2019)\n",
    "LV2024_df = pd.read_csv(LV_2024)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10382 entries, 0 to 10381\n",
      "Data columns (total 35 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   DISTRICT CODE         10382 non-null  int64  \n",
      " 1   DISTRICT NAME         10382 non-null  object \n",
      " 2   PROPERTY ID           10382 non-null  int64  \n",
      " 3   PROPERTY TYPE         10382 non-null  object \n",
      " 4   PROPERTY NAME         291 non-null    object \n",
      " 5   UNIT NUMBER           43 non-null     object \n",
      " 6   HOUSE NUMBER          10066 non-null  object \n",
      " 7   STREET NAME           10381 non-null  object \n",
      " 8   SUBURB NAME           10382 non-null  object \n",
      " 9   POSTCODE              10382 non-null  int64  \n",
      " 10  PROPERTY DESCRIPTION  10365 non-null  object \n",
      " 11  ZONE CODE             10382 non-null  object \n",
      " 12  AREA                  10335 non-null  float64\n",
      " 13  AREA TYPE             10335 non-null  object \n",
      " 14  BASE DATE 1           10382 non-null  object \n",
      " 15  LAND VALUE 1          10382 non-null  int64  \n",
      " 16  AUTHORITY 1           10382 non-null  object \n",
      " 17  BASIS 1               10382 non-null  object \n",
      " 18  BASE DATE 2           10382 non-null  object \n",
      " 19  LAND VALUE 2          10334 non-null  float64\n",
      " 20  AUTHORITY 2           10334 non-null  object \n",
      " 21  BASIS 2               10334 non-null  object \n",
      " 22  BASE DATE 3           10382 non-null  object \n",
      " 23  LAND VALUE 3          10382 non-null  int64  \n",
      " 24  AUTHORITY 3           10382 non-null  object \n",
      " 25  BASIS 3               10382 non-null  object \n",
      " 26  BASE DATE 4           10382 non-null  object \n",
      " 27  LAND VALUE 4          10218 non-null  float64\n",
      " 28  AUTHORITY 4           10218 non-null  object \n",
      " 29  BASIS 4               10218 non-null  object \n",
      " 30  BASE DATE 5           10382 non-null  object \n",
      " 31  LAND VALUE 5          10162 non-null  float64\n",
      " 32  AUTHORITY 5           10162 non-null  object \n",
      " 33  BASIS 5               10162 non-null  object \n",
      " 34  Unnamed: 34           0 non-null      float64\n",
      "dtypes: float64(5), int64(5), object(25)\n",
      "memory usage: 2.8+ MB\n",
      "DISTRICT CODE               1\n",
      "DISTRICT NAME               1\n",
      "PROPERTY ID             10382\n",
      "PROPERTY TYPE               2\n",
      "PROPERTY NAME             199\n",
      "UNIT NUMBER                16\n",
      "HOUSE NUMBER              936\n",
      "STREET NAME               443\n",
      "SUBURB NAME                15\n",
      "POSTCODE                    6\n",
      "PROPERTY DESCRIPTION    10268\n",
      "ZONE CODE                  15\n",
      "AREA                     4479\n",
      "AREA TYPE                   2\n",
      "BASE DATE 1                 1\n",
      "LAND VALUE 1             1488\n",
      "AUTHORITY 1                 6\n",
      "BASIS 1                     7\n",
      "BASE DATE 2                 1\n",
      "LAND VALUE 2             1492\n",
      "AUTHORITY 2                 6\n",
      "BASIS 2                     7\n",
      "BASE DATE 3                 1\n",
      "LAND VALUE 3             1686\n",
      "AUTHORITY 3                 7\n",
      "BASIS 3                     8\n",
      "BASE DATE 4                 1\n",
      "LAND VALUE 4             1533\n",
      "AUTHORITY 4                 7\n",
      "BASIS 4                     7\n",
      "BASE DATE 5                 1\n",
      "LAND VALUE 5             1496\n",
      "AUTHORITY 5                 8\n",
      "BASIS 5                     7\n",
      "Unnamed: 34                 0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "LV2019_df.info()\n",
    "print(LV2019_df.nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def concatAddress(row: pd.Series):\n",
    "    return f'{row[\"UNIT NUMBER\"] + \"/\" if not pd.isnull(row[\"UNIT NUMBER\"]) else \"\"} {row[\"HOUSE NUMBER\"] if not pd.isnull(row[\"HOUSE NUMBER\"]) else \"\"} {row[\"STREET NAME\"]}, {row[\"SUBURB NAME\"]}'\n",
    "\n",
    "def mergeAllLandValue(row: pd.Series):\n",
    "    maxRecord = 5\n",
    "    result = {}\n",
    "    for i in range(1,maxRecord + 1):\n",
    "        landValueColumn = f'LAND VALUE {i}'\n",
    "        if landValueColumn in row:\n",
    "            result[row[f'BASE DATE {i}'].split('/')[2]] = row[f'LAND VALUE {i}']\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "address_df = LV2019_df.apply(concatAddress, axis=1)\n",
    "landValue_df = LV2019_df.apply(mergeAllLandValue, axis=1)\n",
    "LV2019_df[\"ADDRESS\"] = address_df\n",
    "LV2019_df[\"LAND VALUE\"] = landValue_df\n",
    "filtered_2019_df = LV2019_df[[\"PROPERTY ID\", \"PROPERTY DESCRIPTION\", \"ADDRESS\", \"POSTCODE\", \"DISTRICT NAME\", \"DISTRICT CODE\", \"LAND VALUE\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>PROPERTY ID</th>\n",
       "      <th>PROPERTY DESCRIPTION</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>POSTCODE</th>\n",
       "      <th>DISTRICT NAME</th>\n",
       "      <th>DISTRICT CODE</th>\n",
       "      <th>LAND VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>768879</td>\n",
       "      <td>X/403084</td>\n",
       "      <td>28 ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 1500000, '2017': 1370000.0, '2016': 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>768880</td>\n",
       "      <td>Y/403084</td>\n",
       "      <td>26 ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 1970000, '2017': 1520000.0, '2016': 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>768881</td>\n",
       "      <td>A/437985</td>\n",
       "      <td>26A ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 1500000, '2017': 1370000.0, '2016': 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>768886</td>\n",
       "      <td>UNNUMBERED LOT</td>\n",
       "      <td>ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 817000, '2017': 803000.0, '2016': 774...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>768887</td>\n",
       "      <td>1/873687</td>\n",
       "      <td>41 ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 817000, '2017': 803000.0, '2016': 774...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10377</th>\n",
       "      <td>3918239</td>\n",
       "      <td>2/1216746</td>\n",
       "      <td>10 ATCHISON ST, ST LEONARDS</td>\n",
       "      <td>2065</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 459121, '2017': 423000.0, '2016': 396...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10378</th>\n",
       "      <td>3918240</td>\n",
       "      <td>3/1216746</td>\n",
       "      <td>10 ATCHISON ST, ST LEONARDS</td>\n",
       "      <td>2065</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 3263092, '2017': 3010000.0, '2016': 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10379</th>\n",
       "      <td>3917253</td>\n",
       "      <td>101/1195502</td>\n",
       "      <td>1 BLIGH ST, KIRRIBILLI</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 1070000, '2017': 986000.0, '2016': 93...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10380</th>\n",
       "      <td>3917254</td>\n",
       "      <td>102/1195502</td>\n",
       "      <td>3 BLIGH ST, KIRRIBILLI</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 924000, '2017': 851000.0, '2016': 802...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10381</th>\n",
       "      <td>3926642</td>\n",
       "      <td>30, 39/4313 NSW Maritime 20378_1</td>\n",
       "      <td>15 ELAMANG AVE, KIRRIBILLI</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2018': 7380000, '2017': 6300000.0, '2016': 6...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10382 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       PROPERTY ID              PROPERTY DESCRIPTION  \\\n",
       "0           768879                          X/403084   \n",
       "1           768880                          Y/403084   \n",
       "2           768881                          A/437985   \n",
       "3           768886                    UNNUMBERED LOT   \n",
       "4           768887                          1/873687   \n",
       "...            ...                               ...   \n",
       "10377      3918239                         2/1216746   \n",
       "10378      3918240                         3/1216746   \n",
       "10379      3917253                       101/1195502   \n",
       "10380      3917254                       102/1195502   \n",
       "10381      3926642  30, 39/4313 NSW Maritime 20378_1   \n",
       "\n",
       "                               ADDRESS  POSTCODE DISTRICT NAME  DISTRICT CODE  \\\n",
       "0        28 ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "1        26 ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "2       26A ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "3           ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "4        41 ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "...                                ...       ...           ...            ...   \n",
       "10377      10 ATCHISON ST, ST LEONARDS      2065  NORTH SYDNEY             88   \n",
       "10378      10 ATCHISON ST, ST LEONARDS      2065  NORTH SYDNEY             88   \n",
       "10379           1 BLIGH ST, KIRRIBILLI      2061  NORTH SYDNEY             88   \n",
       "10380           3 BLIGH ST, KIRRIBILLI      2061  NORTH SYDNEY             88   \n",
       "10381       15 ELAMANG AVE, KIRRIBILLI      2061  NORTH SYDNEY             88   \n",
       "\n",
       "                                              LAND VALUE  \n",
       "0      {'2018': 1500000, '2017': 1370000.0, '2016': 1...  \n",
       "1      {'2018': 1970000, '2017': 1520000.0, '2016': 1...  \n",
       "2      {'2018': 1500000, '2017': 1370000.0, '2016': 1...  \n",
       "3      {'2018': 817000, '2017': 803000.0, '2016': 774...  \n",
       "4      {'2018': 817000, '2017': 803000.0, '2016': 774...  \n",
       "...                                                  ...  \n",
       "10377  {'2018': 459121, '2017': 423000.0, '2016': 396...  \n",
       "10378  {'2018': 3263092, '2017': 3010000.0, '2016': 2...  \n",
       "10379  {'2018': 1070000, '2017': 986000.0, '2016': 93...  \n",
       "10380  {'2018': 924000, '2017': 851000.0, '2016': 802...  \n",
       "10381  {'2018': 7380000, '2017': 6300000.0, '2016': 6...  \n",
       "\n",
       "[10382 rows x 7 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_2019_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "address_df = LV2024_df.apply(concatAddress, axis=1)\n",
    "landValue_df = LV2024_df.apply(mergeAllLandValue, axis=1)\n",
    "LV2024_df[\"ADDRESS\"] = address_df\n",
    "LV2024_df[\"LAND VALUE\"] = landValue_df\n",
    "filtered_2024_df = LV2024_df[[\"PROPERTY ID\", \"PROPERTY DESCRIPTION\", \"ADDRESS\", \"POSTCODE\", \"DISTRICT NAME\", \"DISTRICT CODE\", \"LAND VALUE\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>PROPERTY ID</th>\n",
       "      <th>PROPERTY DESCRIPTION</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>POSTCODE</th>\n",
       "      <th>DISTRICT NAME</th>\n",
       "      <th>DISTRICT CODE</th>\n",
       "      <th>LAND VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>768879</td>\n",
       "      <td>X/403084</td>\n",
       "      <td>28 ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 2430000, '2022': 1820000, '2021': 154...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>768880</td>\n",
       "      <td>Y/403084</td>\n",
       "      <td>26 ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 3190000, '2022': 2390000, '2021': 203...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>768881</td>\n",
       "      <td>A/437985</td>\n",
       "      <td>26A ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 2430000, '2022': 1820000, '2021': 154...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>768886</td>\n",
       "      <td>UNNUMBERED LOT</td>\n",
       "      <td>ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 813000, '2022': 829000, '2021': 82300...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>768887</td>\n",
       "      <td>1/873687</td>\n",
       "      <td>41 ALFRED ST S, MILSONS POINT</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 813000, '2022': 829000, '2021': 82300...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10330</th>\n",
       "      <td>4293162</td>\n",
       "      <td>1, 2, CP/SP101263</td>\n",
       "      <td>68 BENELONG RD, CREMORNE</td>\n",
       "      <td>2090</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 4540000, '2022': 4730000, '2021': 394...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10331</th>\n",
       "      <td>4256732</td>\n",
       "      <td>1/1258791</td>\n",
       "      <td>398 PACIFIC HWY, CROWS NEST</td>\n",
       "      <td>2065</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 9790000, '2022': 8980000, '2021': 804...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10332</th>\n",
       "      <td>4258180</td>\n",
       "      <td>1, 2, 3, 4, CP/SP100545</td>\n",
       "      <td>106 KIRRIBILLI AVE, KIRRIBILLI</td>\n",
       "      <td>2061</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 6710000, '2022': 5470000, '2021': 455...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10333</th>\n",
       "      <td>4283360</td>\n",
       "      <td>1/633536</td>\n",
       "      <td>WEST ST, NORTH SYDNEY</td>\n",
       "      <td>2060</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 20900, '2022': 18200, '2021': 15400, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10334</th>\n",
       "      <td>4283494</td>\n",
       "      <td>1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,...</td>\n",
       "      <td>45 ATCHISON ST, CROWS NEST</td>\n",
       "      <td>2065</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88</td>\n",
       "      <td>{'2023': 18700000, '2022': 19100000, '2021': 1...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10335 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       PROPERTY ID                               PROPERTY DESCRIPTION  \\\n",
       "0           768879                                           X/403084   \n",
       "1           768880                                           Y/403084   \n",
       "2           768881                                           A/437985   \n",
       "3           768886                                     UNNUMBERED LOT   \n",
       "4           768887                                           1/873687   \n",
       "...            ...                                                ...   \n",
       "10330      4293162                                  1, 2, CP/SP101263   \n",
       "10331      4256732                                          1/1258791   \n",
       "10332      4258180                            1, 2, 3, 4, CP/SP100545   \n",
       "10333      4283360                                           1/633536   \n",
       "10334      4283494  1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,...   \n",
       "\n",
       "                               ADDRESS  POSTCODE DISTRICT NAME  DISTRICT CODE  \\\n",
       "0        28 ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "1        26 ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "2       26A ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "3           ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "4        41 ALFRED ST S, MILSONS POINT      2061  NORTH SYDNEY             88   \n",
       "...                                ...       ...           ...            ...   \n",
       "10330         68 BENELONG RD, CREMORNE      2090  NORTH SYDNEY             88   \n",
       "10331      398 PACIFIC HWY, CROWS NEST      2065  NORTH SYDNEY             88   \n",
       "10332   106 KIRRIBILLI AVE, KIRRIBILLI      2061  NORTH SYDNEY             88   \n",
       "10333            WEST ST, NORTH SYDNEY      2060  NORTH SYDNEY             88   \n",
       "10334       45 ATCHISON ST, CROWS NEST      2065  NORTH SYDNEY             88   \n",
       "\n",
       "                                              LAND VALUE  \n",
       "0      {'2023': 2430000, '2022': 1820000, '2021': 154...  \n",
       "1      {'2023': 3190000, '2022': 2390000, '2021': 203...  \n",
       "2      {'2023': 2430000, '2022': 1820000, '2021': 154...  \n",
       "3      {'2023': 813000, '2022': 829000, '2021': 82300...  \n",
       "4      {'2023': 813000, '2022': 829000, '2021': 82300...  \n",
       "...                                                  ...  \n",
       "10330  {'2023': 4540000, '2022': 4730000, '2021': 394...  \n",
       "10331  {'2023': 9790000, '2022': 8980000, '2021': 804...  \n",
       "10332  {'2023': 6710000, '2022': 5470000, '2021': 455...  \n",
       "10333  {'2023': 20900, '2022': 18200, '2021': 15400, ...  \n",
       "10334  {'2023': 18700000, '2022': 19100000, '2021': 1...  \n",
       "\n",
       "[10335 rows x 7 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_2024_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(filtered_2024_df, filtered_2019_df, on=\"PROPERTY ID\", how=\"outer\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>PROPERTY ID</th>\n",
       "      <th>PROPERTY DESCRIPTION_x</th>\n",
       "      <th>ADDRESS_x</th>\n",
       "      <th>POSTCODE_x</th>\n",
       "      <th>DISTRICT NAME_x</th>\n",
       "      <th>DISTRICT CODE_x</th>\n",
       "      <th>LAND VALUE_x</th>\n",
       "      <th>PROPERTY DESCRIPTION_y</th>\n",
       "      <th>ADDRESS_y</th>\n",
       "      <th>POSTCODE_y</th>\n",
       "      <th>DISTRICT NAME_y</th>\n",
       "      <th>DISTRICT CODE_y</th>\n",
       "      <th>LAND VALUE_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>767884</td>\n",
       "      <td>1/555908</td>\n",
       "      <td>13 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 18800, '2022': 18500, '2021': 11800, ...</td>\n",
       "      <td>1/555908</td>\n",
       "      <td>13 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2018': 10900, '2017': 9550.0, '2016': 8700, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>767885</td>\n",
       "      <td>1/973695</td>\n",
       "      <td>15 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 2250000, '2022': 2340000, '2021': 195...</td>\n",
       "      <td>1/973695</td>\n",
       "      <td>15 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2018': 1500000, '2017': 1310000.0, '2016': 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>767886</td>\n",
       "      <td>1/973696</td>\n",
       "      <td>17 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 2160000, '2022': 2240000, '2021': 186...</td>\n",
       "      <td>1/973696</td>\n",
       "      <td>17 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2018': 1440000, '2017': 1260000.0, '2016': 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>767887</td>\n",
       "      <td>14/3102</td>\n",
       "      <td>19 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 2560000, '2022': 2660000, '2021': 221...</td>\n",
       "      <td>14/3102</td>\n",
       "      <td>19 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2018': 1690000, '2017': 1480000.0, '2016': 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>767892</td>\n",
       "      <td>1, 2, 3, 4, 5, 6, 7, 8, 9, 10, CP/SP52968</td>\n",
       "      <td>33 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 5550000, '2022': 5900000, '2021': 492...</td>\n",
       "      <td>1, 2, 3, 4, 5, 6, 7, 8, 9, 10, CP/SP52968</td>\n",
       "      <td>33 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2018': 3940000, '2017': 3420000.0, '2016': 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10591</th>\n",
       "      <td>4516516</td>\n",
       "      <td>1, 2, 3, 4, CP/SP101673</td>\n",
       "      <td>182 KURRABA RD, KURRABA POINT</td>\n",
       "      <td>2089.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 7850000, '2022': 7990000, '2021': 646...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10592</th>\n",
       "      <td>4516553</td>\n",
       "      <td>PT 1/1279891 Of the part formerly in Lots 1-6 ...</td>\n",
       "      <td>79-81 SINCLAIR ST, WOLLSTONECRAFT</td>\n",
       "      <td>2065.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 9000000, '2022': 8800000, '2021': 800...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10593</th>\n",
       "      <td>4518198</td>\n",
       "      <td>1, 2, 3, 4, 5, 6, 7, 8, CP/SP104001</td>\n",
       "      <td>14 SPRUSON ST, NEUTRAL BAY</td>\n",
       "      <td>2089.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 3770000, '2022': 4020000, '2021': 341...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10594</th>\n",
       "      <td>4518499</td>\n",
       "      <td>1/591045</td>\n",
       "      <td>107 HIGH ST, NORTH SYDNEY</td>\n",
       "      <td>2060.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 13450000, '2022': 13650000, '2021': 1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10595</th>\n",
       "      <td>4519113</td>\n",
       "      <td>1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,...</td>\n",
       "      <td>63 CARTER ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 11500000, '2022': 12300000, '2021': 1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10596 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       PROPERTY ID                             PROPERTY DESCRIPTION_x  \\\n",
       "0           767884                                           1/555908   \n",
       "1           767885                                           1/973695   \n",
       "2           767886                                           1/973696   \n",
       "3           767887                                            14/3102   \n",
       "4           767892          1, 2, 3, 4, 5, 6, 7, 8, 9, 10, CP/SP52968   \n",
       "...            ...                                                ...   \n",
       "10591      4516516                            1, 2, 3, 4, CP/SP101673   \n",
       "10592      4516553  PT 1/1279891 Of the part formerly in Lots 1-6 ...   \n",
       "10593      4518198                1, 2, 3, 4, 5, 6, 7, 8, CP/SP104001   \n",
       "10594      4518499                                           1/591045   \n",
       "10595      4519113  1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,...   \n",
       "\n",
       "                                ADDRESS_x  POSTCODE_x DISTRICT NAME_x  \\\n",
       "0                  13 ABBOTT ST, CAMMERAY      2062.0    NORTH SYDNEY   \n",
       "1                  15 ABBOTT ST, CAMMERAY      2062.0    NORTH SYDNEY   \n",
       "2                  17 ABBOTT ST, CAMMERAY      2062.0    NORTH SYDNEY   \n",
       "3                  19 ABBOTT ST, CAMMERAY      2062.0    NORTH SYDNEY   \n",
       "4                  33 ABBOTT ST, CAMMERAY      2062.0    NORTH SYDNEY   \n",
       "...                                   ...         ...             ...   \n",
       "10591       182 KURRABA RD, KURRABA POINT      2089.0    NORTH SYDNEY   \n",
       "10592   79-81 SINCLAIR ST, WOLLSTONECRAFT      2065.0    NORTH SYDNEY   \n",
       "10593          14 SPRUSON ST, NEUTRAL BAY      2089.0    NORTH SYDNEY   \n",
       "10594           107 HIGH ST, NORTH SYDNEY      2060.0    NORTH SYDNEY   \n",
       "10595              63 CARTER ST, CAMMERAY      2062.0    NORTH SYDNEY   \n",
       "\n",
       "       DISTRICT CODE_x                                       LAND VALUE_x  \\\n",
       "0                 88.0  {'2023': 18800, '2022': 18500, '2021': 11800, ...   \n",
       "1                 88.0  {'2023': 2250000, '2022': 2340000, '2021': 195...   \n",
       "2                 88.0  {'2023': 2160000, '2022': 2240000, '2021': 186...   \n",
       "3                 88.0  {'2023': 2560000, '2022': 2660000, '2021': 221...   \n",
       "4                 88.0  {'2023': 5550000, '2022': 5900000, '2021': 492...   \n",
       "...                ...                                                ...   \n",
       "10591             88.0  {'2023': 7850000, '2022': 7990000, '2021': 646...   \n",
       "10592             88.0  {'2023': 9000000, '2022': 8800000, '2021': 800...   \n",
       "10593             88.0  {'2023': 3770000, '2022': 4020000, '2021': 341...   \n",
       "10594             88.0  {'2023': 13450000, '2022': 13650000, '2021': 1...   \n",
       "10595             88.0  {'2023': 11500000, '2022': 12300000, '2021': 1...   \n",
       "\n",
       "                          PROPERTY DESCRIPTION_y                ADDRESS_y  \\\n",
       "0                                       1/555908   13 ABBOTT ST, CAMMERAY   \n",
       "1                                       1/973695   15 ABBOTT ST, CAMMERAY   \n",
       "2                                       1/973696   17 ABBOTT ST, CAMMERAY   \n",
       "3                                        14/3102   19 ABBOTT ST, CAMMERAY   \n",
       "4      1, 2, 3, 4, 5, 6, 7, 8, 9, 10, CP/SP52968   33 ABBOTT ST, CAMMERAY   \n",
       "...                                          ...                      ...   \n",
       "10591                                        NaN                      NaN   \n",
       "10592                                        NaN                      NaN   \n",
       "10593                                        NaN                      NaN   \n",
       "10594                                        NaN                      NaN   \n",
       "10595                                        NaN                      NaN   \n",
       "\n",
       "       POSTCODE_y DISTRICT NAME_y  DISTRICT CODE_y  \\\n",
       "0          2062.0    NORTH SYDNEY             88.0   \n",
       "1          2062.0    NORTH SYDNEY             88.0   \n",
       "2          2062.0    NORTH SYDNEY             88.0   \n",
       "3          2062.0    NORTH SYDNEY             88.0   \n",
       "4          2062.0    NORTH SYDNEY             88.0   \n",
       "...           ...             ...              ...   \n",
       "10591         NaN             NaN              NaN   \n",
       "10592         NaN             NaN              NaN   \n",
       "10593         NaN             NaN              NaN   \n",
       "10594         NaN             NaN              NaN   \n",
       "10595         NaN             NaN              NaN   \n",
       "\n",
       "                                            LAND VALUE_y  \n",
       "0      {'2018': 10900, '2017': 9550.0, '2016': 8700, ...  \n",
       "1      {'2018': 1500000, '2017': 1310000.0, '2016': 1...  \n",
       "2      {'2018': 1440000, '2017': 1260000.0, '2016': 1...  \n",
       "3      {'2018': 1690000, '2017': 1480000.0, '2016': 1...  \n",
       "4      {'2018': 3940000, '2017': 3420000.0, '2016': 3...  \n",
       "...                                                  ...  \n",
       "10591                                                NaN  \n",
       "10592                                                NaN  \n",
       "10593                                                NaN  \n",
       "10594                                                NaN  \n",
       "10595                                                NaN  \n",
       "\n",
       "[10596 rows x 13 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PROPERTY ID', 'PROPERTY DESCRIPTION_x', 'ADDRESS_x', 'POSTCODE_x',\n",
       "       'DISTRICT NAME_x', 'DISTRICT CODE_x', 'LAND VALUE_x',\n",
       "       'PROPERTY DESCRIPTION_y', 'ADDRESS_y', 'POSTCODE_y', 'DISTRICT NAME_y',\n",
       "       'DISTRICT CODE_y', 'LAND VALUE_y', 'Land Value', 'Land VALUE'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "def processMergedRows(row: pd.Series):\n",
    "    columns = [\"PROPERTY DESCRIPTION\", \"ADDRESS\", \"POSTCODE\", \"DISTRICT NAME\", \"DISTRICT CODE\", \"LAND VALUE\"]\n",
    "    for column in columns:\n",
    "        if column == \"LAND VALUE\":\n",
    "            if not pd.isnull(row[\"LAND VALUE_y\"]) and not pd.isnull(row[\"LAND VALUE_x\"]):\n",
    "                row[\"LAND VALUE_x\"].update(row[\"LAND VALUE_y\"])        \n",
    "            elif not pd.isnull(row[\"LAND VALUE_y\"]) and pd.isnull(row[\"LAND VALUE_x\"]):\n",
    "                row[\"LAND VALUE_x\"] = row[\"LAND VALUE_y\"]\n",
    "        else:\n",
    "            if pd.isnull(row[f'{column}_x']) and not pd.isnull(row[f'{column}_y']):\n",
    "                row[f'{column}_x'] = row[f'{column}_y']\n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>PROPERTY ID</th>\n",
       "      <th>PROPERTY DESCRIPTION</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>POSTCODE</th>\n",
       "      <th>DISTRICT NAME</th>\n",
       "      <th>DISTRICT CODE</th>\n",
       "      <th>LAND VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>767884</td>\n",
       "      <td>1/555908</td>\n",
       "      <td>13 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 18800, '2022': 18500, '2021': 11800, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>767885</td>\n",
       "      <td>1/973695</td>\n",
       "      <td>15 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 2250000, '2022': 2340000, '2021': 195...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>767886</td>\n",
       "      <td>1/973696</td>\n",
       "      <td>17 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 2160000, '2022': 2240000, '2021': 186...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>767887</td>\n",
       "      <td>14/3102</td>\n",
       "      <td>19 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 2560000, '2022': 2660000, '2021': 221...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>767892</td>\n",
       "      <td>1, 2, 3, 4, 5, 6, 7, 8, 9, 10, CP/SP52968</td>\n",
       "      <td>33 ABBOTT ST, CAMMERAY</td>\n",
       "      <td>2062.0</td>\n",
       "      <td>NORTH SYDNEY</td>\n",
       "      <td>88.0</td>\n",
       "      <td>{'2023': 5550000, '2022': 5900000, '2021': 492...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PROPERTY ID                       PROPERTY DESCRIPTION  \\\n",
       "0       767884                                   1/555908   \n",
       "1       767885                                   1/973695   \n",
       "2       767886                                   1/973696   \n",
       "3       767887                                    14/3102   \n",
       "4       767892  1, 2, 3, 4, 5, 6, 7, 8, 9, 10, CP/SP52968   \n",
       "\n",
       "                   ADDRESS  POSTCODE DISTRICT NAME  DISTRICT CODE  \\\n",
       "0   13 ABBOTT ST, CAMMERAY    2062.0  NORTH SYDNEY           88.0   \n",
       "1   15 ABBOTT ST, CAMMERAY    2062.0  NORTH SYDNEY           88.0   \n",
       "2   17 ABBOTT ST, CAMMERAY    2062.0  NORTH SYDNEY           88.0   \n",
       "3   19 ABBOTT ST, CAMMERAY    2062.0  NORTH SYDNEY           88.0   \n",
       "4   33 ABBOTT ST, CAMMERAY    2062.0  NORTH SYDNEY           88.0   \n",
       "\n",
       "                                          LAND VALUE  \n",
       "0  {'2023': 18800, '2022': 18500, '2021': 11800, ...  \n",
       "1  {'2023': 2250000, '2022': 2340000, '2021': 195...  \n",
       "2  {'2023': 2160000, '2022': 2240000, '2021': 186...  \n",
       "3  {'2023': 2560000, '2022': 2660000, '2021': 221...  \n",
       "4  {'2023': 5550000, '2022': 5900000, '2021': 492...  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mergedLand_df = merged_df.apply(processMergedRows, axis=1)\n",
    "\n",
    "mergedLand_df = mergedLand_df.rename(columns={\"PROPERTY DESCRIPTION_x\": \"PROPERTY DESCRIPTION\", \"ADDRESS_x\": \"ADDRESS\", \"POSTCODE_x\": \"POSTCODE\",\n",
    "       \"DISTRICT NAME_x\": \"DISTRICT NAME\", \"DISTRICT CODE_x\": \"DISTRICT CODE\"})\n",
    "\n",
    "mergedLand_df = mergedLand_df[[\"PROPERTY ID\", \"PROPERTY DESCRIPTION\", \"ADDRESS\", \"POSTCODE\", \"DISTRICT NAME\", \"DISTRICT CODE\", \"LAND VALUE\"]]\n",
    "mergedLand_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10596 entries, 0 to 10595\n",
      "Data columns (total 7 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   PROPERTY ID           10596 non-null  int64  \n",
      " 1   PROPERTY DESCRIPTION  10580 non-null  object \n",
      " 2   ADDRESS               10596 non-null  object \n",
      " 3   POSTCODE              10596 non-null  float64\n",
      " 4   DISTRICT NAME         10596 non-null  object \n",
      " 5   DISTRICT CODE         10596 non-null  float64\n",
      " 6   LAND VALUE            10596 non-null  object \n",
      "dtypes: float64(2), int64(1), object(4)\n",
      "memory usage: 579.6+ KB\n"
     ]
    }
   ],
   "source": [
    "mergedLand_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "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.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}