Realtor_Final-Template.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1 - Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests \n",
    "import numpy as np\n",
    "import pandas as pd   \n",
    "import sqlalchemy\n",
    "from tqdm import tqdm\n",
    "\n",
    "\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 2 - Requests & CURL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "headers = {\n",
    "    'authority': 'api2.realtor.ca',\n",
    "    'sec-ch-ua': '^\\\\^Chromium^\\\\^;v=^\\\\^92^\\\\^, ^\\\\^',\n",
    "    'accept': '*/*',\n",
    "    'sec-ch-ua-mobile': '?0',\n",
    "    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36',\n",
    "    'content-type': 'application/x-www-form-urlencoded; charset=UTF-8',\n",
    "    'origin': 'https://www.realtor.ca',\n",
    "    'sec-fetch-site': 'same-site',\n",
    "    'sec-fetch-mode': 'cors',\n",
    "    'sec-fetch-dest': 'empty',\n",
    "    'referer': 'https://www.realtor.ca/',\n",
    "    'accept-language': 'en-US,en;q=0.9,de-DE;q=0.8,de;q=0.7',\n",
    "    'cookie': 'visid_incap_2269415=pjB3DImWSy2DSncBmJhxAdFrDWEAAAAAQUIPAAAAAABjsqrQxZ4W1Wnwofq2RdhC; gig_bootstrap_3_mrQiIl6ov44s2X3j6NGWVZ9SDDtplqV7WgdcyEpGYnYxl7ygDWPQHqQqtpSiUfko=gigya-pr_ver4; visid_incap_2271082=AcUYRjx+RSySjRa8AwANG+RrDWEAAAAAQUIPAAAAAABz1Fko5zVbdsXy74RazGys; nlbi_2269415=Em8eFcjFeXL+7IQPkG5lugAAAADPg2wjkaj3uQSIwXxK4Cjd; incap_ses_1343_2269415=kd1PX+Cf0BhALs9dmkujEkxgDmEAAAAAUvek0Memst9B9aq/AcYj4w==; incap_ses_259_2269415=jnQudMVXKnK+XGpiLCeYA01gDmEAAAAA8A3p2AjZwv8Q1Zh2MaB8mw==; ASP.NET_SessionId=glpnlclmpz50wotgeqehu4jr; nlbi_2271082=SfEKDy6GbC4XWhkacbDG1QAAAABjVV7rSMCr/IMzx8nZxG8I; incap_ses_259_2271082=va17EetpSBS+ZGpiLCeYA1ZgDmEAAAAASC01QSktKpD62+V0kmI30Q==; nlbi_2269415_2147483646=O/A+S2g5U0ZQz+i0kG5lugAAAACHpPSMoaIU04ye5O/9KPg+; reese84=3:6um7YNgYz8C3YlgtKUywaQ==:g+sd2OuCy9k/8A6ZrTnAIF0PEOiOQ63KvC3AXenvcxFEX0tefMKwOLinaH3sBe/qSd1Cp39gxrnEZYzCTwjirA1giIrHZ8AIBFzOQdgS8IIvgLiyqca5AQJkn/uTUMBfI67AIEa9R76y02XdWOv9J1oL38idnsS5t2D64eVSOuDMaiGKk081I7fKqpKfzTgN6GipRG45Jep9y19J4ypRkAtUvTa5X1XqYMDfeDsll8RBGCk9C8G51Zn9oYLl1P3Q2m26tXINwMyLMqk9rZUiDF2a1rRmZVyAgWgd9VOfoqTkEwSLl5xasgi0kWm4+G4abFGR1F4ivO+aX0d5aLc6g8/krH+S2COydjRF2KyToWVZZpNXYrIDOxVt0CW4Tyh55k3GyrlePPKMKA0WKoklfZh7jWYwkYYix4c/CjMvpEA=:fI0KfucP5l7cl5S+11F0HubnNJi8hSNbWALShpW3Lcw=',\n",
    "}\n",
    "\n",
    "data = {\n",
    "  # 'LatitudeMax': '45.53758',\n",
    "  # 'LongitudeMax': '-75.24660',\n",
    "  # 'LatitudeMin': '44.96273',\n",
    "  # 'LongitudeMin': '-76.35392',\n",
    "\n",
    "  ## Downtown vancouver\n",
    "  'LatitudeMax': '49.309615', # to find https://www.latlong.net/\n",
    "  'LongitudeMax': '-123.137405',\n",
    "\n",
    "  'LatitudeMin': '49.267156',\n",
    "  'LongitudeMin': '-123.112559',\n",
    "\n",
    "  'Sort': '6-D',\n",
    "  'PropertyTypeGroupID': '1',\n",
    "  'PropertySearchTypeId': '1',\n",
    "  'TransactionTypeId': '2',\n",
    "  'Currency': 'CAD',\n",
    "  'RecordsPerPage': '12',\n",
    "  'ApplicationId': '1',\n",
    "  'CultureId': '1',\n",
    "  'Version': '7.0',\n",
    "  'CurrentPage': '1'\n",
    "}\n",
    "\n",
    "session = requests.Session()\n",
    "session.headers.update(headers)\n",
    "session.params.update(data)\n",
    "# session.headers\n",
    "# session.params\n",
    "\n",
    "response = session.post('https://api2.realtor.ca/Listing.svc/PropertySearch_Post', headers=headers, data=data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 3 - Check Status Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert response.status_code == 200"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 4 - Create Json Object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "response_json = response.json()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 5 - Output Keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['ErrorCode', 'Paging', 'Results', 'Pins', 'GroupingLevel'])"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response_json.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 6 - Find your Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Address\n",
    "- Bedrooms\n",
    "- Bathrooms\n",
    "- Agent Name\n",
    "- Area Code\n",
    "- Phone Number\n",
    "- Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(response_json['Results'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['Id', 'MlsNumber', 'PublicRemarks', 'Building', 'Individual', 'Property', 'Business', 'Land', 'AlternateURL', 'PostalCode', 'RelativeDetailsURL', 'StatusId', 'PhotoChangeDateUTC', 'HasNewImageUpdate', 'Distance', 'RelativeURLEn', 'RelativeURLFr', 'Media', 'InsertedDateUTC', 'TimeOnRealtor'])"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# starting point\n",
    "result_items = response_json['Results']\n",
    "result_items[0].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "## view single record\n",
    "# for i in result_items[0].items():\n",
    "#     print(f'{i[0]}\\t {i[1]}')\n",
    "\n",
    "## check distribution of data \n",
    "# for i in result_items:\n",
    "#     i['Building']\n",
    "#     i['Property']['Type']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Pinnacle of Homer located at centre of Yaletown. This 2 level townhouse features 3 bedroom & 3 bathroom. Perfect for living or investment. It offers full recreation facilities including indoor pool, gym & club house. Steps to Robson St, BC Place, shopping, great dining & easy access to transit. (26551471)'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sales pitch\n",
    "result_items[0]['PublicRemarks']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'3'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'Row / Townhouse'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'Exercise Centre, Guest Suite, Recreation Centre'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#bedrooms\n",
    "result_items[0]['Building']['Bedrooms']\n",
    "\n",
    "# bathrooms\n",
    "result_items[0]['Building']['BathroomTotal']\n",
    "\n",
    "# Type\n",
    "result_items[0]['Building']['Type']\n",
    "\n",
    "# Ammenities\n",
    "result_items[0]['Building']['Ammenities']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'929 HOMER STREET|Vancouver, British Columbia V6B2W6'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'$1,158,000'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'1158000'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'Single Family'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# address\n",
    "result_items[0]['Property']['Address']['AddressText']\n",
    "\n",
    "# price\n",
    "result_items[0]['Property']['Price']\n",
    "result_items[0]['Property']['PriceUnformattedValue']\n",
    "\n",
    "# Type\n",
    "result_items[0]['Property']['Type']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Frank Zheng Chen'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'778'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'989-1081'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# agent name\n",
    "result_items[0]['Individual'][0]['Name']\n",
    "\n",
    "# area code\n",
    "result_items[0]['Individual'][0]['Phones'][0]['AreaCode']\n",
    "\n",
    "# phone number\n",
    "result_items[0]['Individual'][0]['Phones'][0]['PhoneNumber']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'V6B2W6'"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# PostalCode\n",
    "result_items[0]['PostalCode']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 7 - Put everything together - Loop through results and append data inside a list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def slave(result):\n",
    "    # public_remarks\n",
    "    try:\n",
    "        public_remarks.append(result['PublicRemarks'])\n",
    "    except:\n",
    "        public_remarks.append('') \n",
    "    \n",
    "    # bedrooms\n",
    "    try:\n",
    "        bedrooms.append(result['Building']['Bedrooms'])\n",
    "    except:\n",
    "        bedrooms.append('')\n",
    "    \n",
    "    # bathrooms\n",
    "    try:\n",
    "        bathrooms.append(result['Building']['BathroomTotal'])\n",
    "    except:\n",
    "        bathrooms.append('')\n",
    "        \n",
    "    # building_type\n",
    "    try:\n",
    "        building_type.append(result['Building']['Type'])\n",
    "    except:\n",
    "        building_type.append('')\n",
    "\n",
    "    # ammenities\n",
    "    try:\n",
    "        ammenities.append(result['Building']['Ammenities'])\n",
    "    except:\n",
    "        ammenities.append('')\n",
    "\n",
    "    # address\n",
    "    try:\n",
    "        address.append(result['Property']['Address']['AddressText'])\n",
    "    except:\n",
    "        address.append('')\n",
    "    \n",
    "    # price\n",
    "    try:\n",
    "        price.append(result['Property']['PriceUnformattedValue']) # Price \n",
    "    except:\n",
    "        price.append('')\n",
    "\n",
    "    # property_type\n",
    "    try:\n",
    "        property_type.append(result['Property']['Type']) # Price \n",
    "    except:\n",
    "        property_type.append('')\n",
    "\n",
    "    # agent name\n",
    "    try:\n",
    "        agent_name.append(result['Individual'][0]['Name'])\n",
    "    except:\n",
    "        agent_name.append('')\n",
    "    \n",
    "    # area code\n",
    "    try:\n",
    "        area_code.append(result['Individual'][0]['Phones'][0]['AreaCode'])\n",
    "    except:\n",
    "        area_code.append('')\n",
    "    \n",
    "    # phone number\n",
    "    try:\n",
    "        phone_number.append(result['Individual'][0]['Phones'][0]['PhoneNumber'])\n",
    "    except:\n",
    "        phone_number.append('')\n",
    "    \n",
    "    # postal_code\n",
    "    try:\n",
    "        postal_code.append(result['PostalCode'])\n",
    "    except:\n",
    "        postal_code.append('') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "public_remarks = [] \n",
    "\n",
    "bedrooms = []\n",
    "bathrooms = []\n",
    "building_type = []\n",
    "ammenities = []\n",
    "\n",
    "address = []\n",
    "price = []\n",
    "property_type = []\n",
    "\n",
    "agent_name = []\n",
    "area_code = []\n",
    "phone_number = []\n",
    "\n",
    "postal_code = []\n",
    "\n",
    "for result in result_items:\n",
    "    slave(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 8 - Pandas Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>public_remarks</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>building_type</th>\n",
       "      <th>ammenities</th>\n",
       "      <th>address</th>\n",
       "      <th>price</th>\n",
       "      <th>property_type</th>\n",
       "      <th>Agent Name</th>\n",
       "      <th>Area Code</th>\n",
       "      <th>Phone Number</th>\n",
       "      <th>postal_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pinnacle of Homer located at centre of Yaletow...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Row / Townhouse</td>\n",
       "      <td>Exercise Centre, Guest Suite, Recreation Centre</td>\n",
       "      <td>929 HOMER STREET|Vancouver, British Columbia V...</td>\n",
       "      <td>1158000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Frank Zheng Chen</td>\n",
       "      <td>778</td>\n",
       "      <td>989-1081</td>\n",
       "      <td>V6B2W6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SPECTACULAR OCEAN VIEWS! Nestled on a private ...</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>House</td>\n",
       "      <td></td>\n",
       "      <td>14 BENSON DRIVE|Port Moody, British Columbia V...</td>\n",
       "      <td>1688000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Janice Mazzone</td>\n",
       "      <td>604</td>\n",
       "      <td>250-7858</td>\n",
       "      <td>V3H3P6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fantastic NW views of Harbour, Bridge and moun...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre</td>\n",
       "      <td>1904 1616 BAYSHORE DRIVE|Vancouver, British Co...</td>\n",
       "      <td>2198000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Navid Behroyan</td>\n",
       "      <td>778</td>\n",
       "      <td>882-6443</td>\n",
       "      <td>V6G3L1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Welcome to The Residence at the West! Located ...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Daycare, Exercise Centre, Laundry - In Suite</td>\n",
       "      <td>220 1783 MANITOBA STREET|Vancouver, British Co...</td>\n",
       "      <td>798800</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Iman Moghadam</td>\n",
       "      <td>604</td>\n",
       "      <td>721-6209</td>\n",
       "      <td>V5Y0K1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Koret Lofts is a historic heritage conversion ...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Laundry - In Suite</td>\n",
       "      <td>405 55 E CORDOVA STREET|Vancouver, British Col...</td>\n",
       "      <td>849000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Winston Chan</td>\n",
       "      <td>604</td>\n",
       "      <td>767-3221</td>\n",
       "      <td>V6A0A5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Welcome to the ARC by Concord, the newest addi...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre, Recreation Centre</td>\n",
       "      <td>1881 87 NELSON STREET|Vancouver, British Colum...</td>\n",
       "      <td>748000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Iman Moghadam</td>\n",
       "      <td>604</td>\n",
       "      <td>721-6209</td>\n",
       "      <td>V6Z0E8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Contemporary craftsman-style 5 bedroom home by...</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>House</td>\n",
       "      <td></td>\n",
       "      <td>1445 STRAWLINE HILL STREET|Coquitlam, British ...</td>\n",
       "      <td>2880000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Kensen Gao</td>\n",
       "      <td>778</td>\n",
       "      <td>986-7823</td>\n",
       "      <td>V3E0L6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Solid Westridge family home, over 9000sf corne...</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>House</td>\n",
       "      <td>Laundry - In Suite</td>\n",
       "      <td>7204 RIDGE DRIVE|Burnaby, British Columbia V5B1B5</td>\n",
       "      <td>2649000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Cedric Wong</td>\n",
       "      <td>778</td>\n",
       "      <td>238-3305</td>\n",
       "      <td>V5B1B5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Stunning &amp; Luxurious penthouse floor unit in O...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre</td>\n",
       "      <td>2010 68 SMITHE STREET|Vancouver, British Colum...</td>\n",
       "      <td>828800</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Glen Hao</td>\n",
       "      <td>778</td>\n",
       "      <td>869-5257</td>\n",
       "      <td>V6B0P4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>The Bentley. Large 905 square ft , 2 bedroom &amp;...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre, Laundry - In Suite</td>\n",
       "      <td>202 295 GUILDFORD WAY|Port Moody, British Colu...</td>\n",
       "      <td>599988</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Sarah Askarian</td>\n",
       "      <td>604</td>\n",
       "      <td>716-6821</td>\n",
       "      <td>V3H5N3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>R17//Woodridge/Looking for a large town lot in...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>House</td>\n",
       "      <td></td>\n",
       "      <td>15 HUPPE Street|Woodridge, Manitoba R0A2N0</td>\n",
       "      <td>209000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Rose Schroeder</td>\n",
       "      <td>204</td>\n",
       "      <td>346-2505</td>\n",
       "      <td>R0A2N0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>The Oxford is a boutique collection of 9 townh...</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Row / Townhouse</td>\n",
       "      <td></td>\n",
       "      <td>1 1750 SALISBURY AVENUE|Port Coquitlam, Britis...</td>\n",
       "      <td>1099900</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Charanvir Sanghera</td>\n",
       "      <td>604</td>\n",
       "      <td>500-7797</td>\n",
       "      <td>V0V0V0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       public_remarks bathrooms bedrooms  \\\n",
       "0   Pinnacle of Homer located at centre of Yaletow...         3        3   \n",
       "1   SPECTACULAR OCEAN VIEWS! Nestled on a private ...         3        4   \n",
       "2   Fantastic NW views of Harbour, Bridge and moun...         2        2   \n",
       "3   Welcome to The Residence at the West! Located ...         1        1   \n",
       "4   Koret Lofts is a historic heritage conversion ...         1        0   \n",
       "5   Welcome to the ARC by Concord, the newest addi...         1        1   \n",
       "6   Contemporary craftsman-style 5 bedroom home by...         5        5   \n",
       "7   Solid Westridge family home, over 9000sf corne...         6       10   \n",
       "8   Stunning & Luxurious penthouse floor unit in O...         1        1   \n",
       "9   The Bentley. Large 905 square ft , 2 bedroom &...         1        1   \n",
       "10  R17//Woodridge/Looking for a large town lot in...         1        2   \n",
       "11  The Oxford is a boutique collection of 9 townh...         4        4   \n",
       "\n",
       "      building_type                                       ammenities  \\\n",
       "0   Row / Townhouse  Exercise Centre, Guest Suite, Recreation Centre   \n",
       "1             House                                                    \n",
       "2         Apartment                                  Exercise Centre   \n",
       "3         Apartment     Daycare, Exercise Centre, Laundry - In Suite   \n",
       "4         Apartment                               Laundry - In Suite   \n",
       "5         Apartment               Exercise Centre, Recreation Centre   \n",
       "6             House                                                    \n",
       "7             House                               Laundry - In Suite   \n",
       "8         Apartment                                  Exercise Centre   \n",
       "9         Apartment              Exercise Centre, Laundry - In Suite   \n",
       "10            House                                                    \n",
       "11  Row / Townhouse                                                    \n",
       "\n",
       "                                              address    price  property_type  \\\n",
       "0   929 HOMER STREET|Vancouver, British Columbia V...  1158000  Single Family   \n",
       "1   14 BENSON DRIVE|Port Moody, British Columbia V...  1688000  Single Family   \n",
       "2   1904 1616 BAYSHORE DRIVE|Vancouver, British Co...  2198000  Single Family   \n",
       "3   220 1783 MANITOBA STREET|Vancouver, British Co...   798800  Single Family   \n",
       "4   405 55 E CORDOVA STREET|Vancouver, British Col...   849000  Single Family   \n",
       "5   1881 87 NELSON STREET|Vancouver, British Colum...   748000  Single Family   \n",
       "6   1445 STRAWLINE HILL STREET|Coquitlam, British ...  2880000  Single Family   \n",
       "7   7204 RIDGE DRIVE|Burnaby, British Columbia V5B1B5  2649000  Single Family   \n",
       "8   2010 68 SMITHE STREET|Vancouver, British Colum...   828800  Single Family   \n",
       "9   202 295 GUILDFORD WAY|Port Moody, British Colu...   599988  Single Family   \n",
       "10         15 HUPPE Street|Woodridge, Manitoba R0A2N0   209000  Single Family   \n",
       "11  1 1750 SALISBURY AVENUE|Port Coquitlam, Britis...  1099900  Single Family   \n",
       "\n",
       "            Agent Name Area Code Phone Number postal_code  \n",
       "0     Frank Zheng Chen       778     989-1081      V6B2W6  \n",
       "1       Janice Mazzone       604     250-7858      V3H3P6  \n",
       "2       Navid Behroyan       778     882-6443      V6G3L1  \n",
       "3        Iman Moghadam       604     721-6209      V5Y0K1  \n",
       "4         Winston Chan       604     767-3221      V6A0A5  \n",
       "5        Iman Moghadam       604     721-6209      V6Z0E8  \n",
       "6           Kensen Gao       778     986-7823      V3E0L6  \n",
       "7          Cedric Wong       778     238-3305      V5B1B5  \n",
       "8             Glen Hao       778     869-5257      V6B0P4  \n",
       "9       Sarah Askarian       604     716-6821      V3H5N3  \n",
       "10      Rose Schroeder       204     346-2505      R0A2N0  \n",
       "11  Charanvir Sanghera       604     500-7797      V0V0V0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names = {\"public_remarks\": public_remarks, \n",
    "        'bathrooms':bathrooms, 'bedrooms':bedrooms, 'building_type':building_type, 'ammenities':ammenities, \n",
    "        'address':address, 'price':price, 'property_type':property_type, \n",
    "        'Agent Name':agent_name, 'Area Code': area_code, 'Phone Number': phone_number, \n",
    "        'postal_code':postal_code}\n",
    "\n",
    "df_realtor = pd.DataFrame(names)\n",
    "df_realtor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 10 - Multiple Pages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "public_remarks = [] \n",
    "\n",
    "bedrooms = []\n",
    "bathrooms = []\n",
    "building_type = []\n",
    "ammenities = []\n",
    "\n",
    "address = []\n",
    "price = []\n",
    "property_type = []\n",
    "\n",
    "agent_name = []\n",
    "area_code = []\n",
    "phone_number = []\n",
    "\n",
    "postal_code = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\tpage 10/200\n",
      "\tpage 20/200\n",
      "\tpage 30/200\n",
      "\tpage 40/200\n",
      "\tpage 50/200\n",
      "\tpage 60/200\n",
      "There appears to be an error.\n",
      "response_json\n",
      " {'ErrorCode': {'Id': 400, 'Description': 'The request was invalid. - Invalid RecordsPerPage', 'LogId': '', 'ProductName': '! Push to PR-Stage - Realtor API (Maintenance)^32050 [Wednesday, December 1, 2021 2:55:01 PM]', 'Version': '1.0.8005.26913'}, 'Paging': {'RecordsPerPage': 0, 'CurrentPage': 0, 'TotalRecords': 0, 'MaxRecords': 0, 'TotalPages': 0, 'RecordsShowing': 0, 'Pins': 0}, 'Results': [], 'Pins': []}\n"
     ]
    }
   ],
   "source": [
    "start =  1\n",
    "nump_pages = 200 # auto stop implemented\n",
    "for i in range(start,nump_pages+1):\n",
    "    headers = {\n",
    "        'authority': 'api2.realtor.ca',\n",
    "        'sec-ch-ua': '^\\\\^Chromium^\\\\^;v=^\\\\^92^\\\\^, ^\\\\^',\n",
    "        'accept': '*/*',\n",
    "        'sec-ch-ua-mobile': '?0',\n",
    "        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36',\n",
    "        'content-type': 'application/x-www-form-urlencoded; charset=UTF-8',\n",
    "        'origin': 'https://www.realtor.ca',\n",
    "        'sec-fetch-site': 'same-site',\n",
    "        'sec-fetch-mode': 'cors',\n",
    "        'sec-fetch-dest': 'empty',\n",
    "        'referer': 'https://www.realtor.ca/',\n",
    "        'accept-language': 'en-US,en;q=0.9,de-DE;q=0.8,de;q=0.7',\n",
    "        'cookie': 'visid_incap_2269415=pjB3DImWSy2DSncBmJhxAdFrDWEAAAAAQUIPAAAAAABjsqrQxZ4W1Wnwofq2RdhC; gig_bootstrap_3_mrQiIl6ov44s2X3j6NGWVZ9SDDtplqV7WgdcyEpGYnYxl7ygDWPQHqQqtpSiUfko=gigya-pr_ver4; visid_incap_2271082=AcUYRjx+RSySjRa8AwANG+RrDWEAAAAAQUIPAAAAAABz1Fko5zVbdsXy74RazGys; nlbi_2269415=Em8eFcjFeXL+7IQPkG5lugAAAADPg2wjkaj3uQSIwXxK4Cjd; incap_ses_1343_2269415=kd1PX+Cf0BhALs9dmkujEkxgDmEAAAAAUvek0Memst9B9aq/AcYj4w==; incap_ses_259_2269415=jnQudMVXKnK+XGpiLCeYA01gDmEAAAAA8A3p2AjZwv8Q1Zh2MaB8mw==; ASP.NET_SessionId=glpnlclmpz50wotgeqehu4jr; nlbi_2271082=SfEKDy6GbC4XWhkacbDG1QAAAABjVV7rSMCr/IMzx8nZxG8I; incap_ses_259_2271082=va17EetpSBS+ZGpiLCeYA1ZgDmEAAAAASC01QSktKpD62+V0kmI30Q==; nlbi_2269415_2147483646=O/A+S2g5U0ZQz+i0kG5lugAAAACHpPSMoaIU04ye5O/9KPg+; reese84=3:6um7YNgYz8C3YlgtKUywaQ==:g+sd2OuCy9k/8A6ZrTnAIF0PEOiOQ63KvC3AXenvcxFEX0tefMKwOLinaH3sBe/qSd1Cp39gxrnEZYzCTwjirA1giIrHZ8AIBFzOQdgS8IIvgLiyqca5AQJkn/uTUMBfI67AIEa9R76y02XdWOv9J1oL38idnsS5t2D64eVSOuDMaiGKk081I7fKqpKfzTgN6GipRG45Jep9y19J4ypRkAtUvTa5X1XqYMDfeDsll8RBGCk9C8G51Zn9oYLl1P3Q2m26tXINwMyLMqk9rZUiDF2a1rRmZVyAgWgd9VOfoqTkEwSLl5xasgi0kWm4+G4abFGR1F4ivO+aX0d5aLc6g8/krH+S2COydjRF2KyToWVZZpNXYrIDOxVt0CW4Tyh55k3GyrlePPKMKA0WKoklfZh7jWYwkYYix4c/CjMvpEA=:fI0KfucP5l7cl5S+11F0HubnNJi8hSNbWALShpW3Lcw=',\n",
    "    }\n",
    "\n",
    "    data = {\n",
    "\n",
    "    ## to find https://www.latlong.net/\n",
    "    ## Downtown \n",
    "    #   'LatitudeMax': '49.309615', \n",
    "    #   'LongitudeMax': '-123.137405',\n",
    "\n",
    "    #   'LatitudeMin': '49.267156',\n",
    "    #   'LongitudeMin': '-123.112559',\n",
    "\n",
    "    ## Brent wood\n",
    "    #   'LatitudeMax': '49.267004', \n",
    "    #   'LongitudeMax': '-123.019320',\n",
    "\n",
    "    #   'LatitudeMin': '49.259033',\n",
    "    #   'LongitudeMin': '-122.981433',\n",
    "\n",
    "    ## West end\n",
    "    #   'LatitudeMax': '49.275757', \n",
    "    #   'LongitudeMax': '-123.222846',\n",
    "\n",
    "    #   'LatitudeMin': '49.234559',\n",
    "    #   'LongitudeMin': '-123.156123',\n",
    "\n",
    "\n",
    "    ## West end\n",
    "      'LatitudeMax': '49.275757', \n",
    "      'LongitudeMax': '-123.222846',\n",
    "\n",
    "      'LatitudeMin': '49.234559',\n",
    "      'LongitudeMin': '-123.156123',\n",
    "\n",
    "      'Sort': '6-D',\n",
    "      'PropertyTypeGroupID': '1',\n",
    "      'PropertySearchTypeId': '1',\n",
    "      'TransactionTypeId': '2',\n",
    "      'Currency': 'CAD',\n",
    "      'RecordsPerPage': '12',\n",
    "      'ApplicationId': '1',\n",
    "      'CultureId': '1',\n",
    "      'Version': '7.0',\n",
    "      'CurrentPage': str(i)\n",
    "    }\n",
    "\n",
    "    # get next page\n",
    "    session.params.update(data)\n",
    "    response = session.post('https://api2.realtor.ca/Listing.svc/PropertySearch_Post', headers=headers, data=data) # like Nas said, this shit is poison. , headers=headers, data=data\n",
    "    # response = requests.post('https://api2.realtor.ca/Listing.svc/PropertySearch_Post')\n",
    "\n",
    "    # json object\n",
    "    response_json = response.json()\n",
    "\n",
    "    # starting point\n",
    "    result_items = response_json['Results']\n",
    "\n",
    "    for result in result_items:\n",
    "        slave(result)\n",
    "\n",
    "           \n",
    "    if i%10==0: \n",
    "        print(f'\\tpage {i}/{nump_pages}')\n",
    "        if response.json()['ErrorCode']['Id'] != 200:\n",
    "            print('There appears to be an error.')\n",
    "            print(f'response_json\\n {response_json}')\n",
    "            if i <= 99: print(\"a small area is good for depth of history, however, if you dont reach the 100 page limit, your may wish to consider a larger area.\")\n",
    "            break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "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>public_remarks</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>building_type</th>\n",
       "      <th>ammenities</th>\n",
       "      <th>address</th>\n",
       "      <th>price</th>\n",
       "      <th>property_type</th>\n",
       "      <th>agent Name</th>\n",
       "      <th>area Code</th>\n",
       "      <th>phone Number</th>\n",
       "      <th>postal_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>STUNNING condo at The Legacy by BOSA! Skytrain...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre, Laundry - In Suite</td>\n",
       "      <td>1801 5611 GORING STREET|Burnaby, British Colum...</td>\n",
       "      <td>878000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Oleg Tsaryov</td>\n",
       "      <td>604</td>\n",
       "      <td>719-4490</td>\n",
       "      <td>V5B0A3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Prime Location and steps away from the Lake. A...</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>House</td>\n",
       "      <td></td>\n",
       "      <td>9690 Eanen Ave|Port Alberni, British Columbia ...</td>\n",
       "      <td>2000000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Bobby Toor</td>\n",
       "      <td>250</td>\n",
       "      <td>228-8667</td>\n",
       "      <td>V9Y9C6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Brand new 2 bedrooms 2 bathrooms in CIRRUS in ...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre, Laundry - In Suite, Recreatio...</td>\n",
       "      <td>1704 2085 SKYLINE COURT|Burnaby, British Colum...</td>\n",
       "      <td>1099000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>David Duan</td>\n",
       "      <td>778</td>\n",
       "      <td>952-6577</td>\n",
       "      <td>V5C0M6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RENOVATED CORNER unit right in the heart of Po...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Laundry - In Suite</td>\n",
       "      <td>203 2378 WILSON AVENUE|Port Coquitlam, British...</td>\n",
       "      <td>499900</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Russ Macnab</td>\n",
       "      <td>604</td>\n",
       "      <td>763-8196</td>\n",
       "      <td>V3C1Z6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>567 CLARKE &amp; COMO by Marcon... the newest addi...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre, Guest Suite, Laundry - In Sui...</td>\n",
       "      <td>2307 567 CLARKE ROAD|Coquitlam, British Columb...</td>\n",
       "      <td>599900</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Paul Liberatore</td>\n",
       "      <td>604</td>\n",
       "      <td>788-0463</td>\n",
       "      <td>V3J3X4</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>1601</th>\n",
       "      <td>This cute two bedroom home situated in the qui...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>House</td>\n",
       "      <td></td>\n",
       "      <td>110 7th AVE|Alameda, Saskatchewan S0C0A0</td>\n",
       "      <td>78000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Dana (Dana) Krienke</td>\n",
       "      <td>306</td>\n",
       "      <td>485-7474</td>\n",
       "      <td>S0C0A0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1602</th>\n",
       "      <td>One of a Kind!  SPECTACULAR 629 SF ROOF TOP DE...</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Row / Townhouse</td>\n",
       "      <td>Laundry - In Suite</td>\n",
       "      <td>102 3349 DUNBAR STREET|Vancouver, British Colu...</td>\n",
       "      <td>1250000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Robert Lee</td>\n",
       "      <td>604</td>\n",
       "      <td>908-4522</td>\n",
       "      <td>V6S2B9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1603</th>\n",
       "      <td>UNOBSTRUCTED PANORAMIC VIEWS OF FALSE CREEK AT...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre, Laundry - In Suite, Recreatio...</td>\n",
       "      <td>2802 1199 MARINASIDE CRESCENT|Vancouver, Briti...</td>\n",
       "      <td>1999900</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Steve Triantafillou</td>\n",
       "      <td>604</td>\n",
       "      <td>779-8590</td>\n",
       "      <td>V6Z2Y2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1604</th>\n",
       "      <td>Why buy coach house, laneway, duplex or whiche...</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>House</td>\n",
       "      <td></td>\n",
       "      <td>2833 W 18TH AVENUE|Vancouver, British Columbia...</td>\n",
       "      <td>2588000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Henry Wu</td>\n",
       "      <td>778</td>\n",
       "      <td>238-8182</td>\n",
       "      <td>V6L1B7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1605</th>\n",
       "      <td>Stunning Modern Home in Ultra-Popular Location...</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>House</td>\n",
       "      <td></td>\n",
       "      <td>3920 GRANDVIEW DRIVE|Castlegar, British Columb...</td>\n",
       "      <td>759900</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>SIMON LAURIE</td>\n",
       "      <td>250</td>\n",
       "      <td>365-1585</td>\n",
       "      <td>V1N4X9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1606 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         public_remarks bathrooms bedrooms  \\\n",
       "0     STUNNING condo at The Legacy by BOSA! Skytrain...         2        2   \n",
       "1     Prime Location and steps away from the Lake. A...         4        5   \n",
       "2     Brand new 2 bedrooms 2 bathrooms in CIRRUS in ...         2        2   \n",
       "3     RENOVATED CORNER unit right in the heart of Po...         2        2   \n",
       "4     567 CLARKE & COMO by Marcon... the newest addi...         1        1   \n",
       "...                                                 ...       ...      ...   \n",
       "1601  This cute two bedroom home situated in the qui...         1        2   \n",
       "1602  One of a Kind!  SPECTACULAR 629 SF ROOF TOP DE...         2        3   \n",
       "1603  UNOBSTRUCTED PANORAMIC VIEWS OF FALSE CREEK AT...         2        2   \n",
       "1604  Why buy coach house, laneway, duplex or whiche...         2        3   \n",
       "1605  Stunning Modern Home in Ultra-Popular Location...         2        3   \n",
       "\n",
       "        building_type                                         ammenities  \\\n",
       "0           Apartment                Exercise Centre, Laundry - In Suite   \n",
       "1               House                                                      \n",
       "2           Apartment  Exercise Centre, Laundry - In Suite, Recreatio...   \n",
       "3           Apartment                                 Laundry - In Suite   \n",
       "4           Apartment  Exercise Centre, Guest Suite, Laundry - In Sui...   \n",
       "...               ...                                                ...   \n",
       "1601            House                                                      \n",
       "1602  Row / Townhouse                                 Laundry - In Suite   \n",
       "1603        Apartment  Exercise Centre, Laundry - In Suite, Recreatio...   \n",
       "1604            House                                                      \n",
       "1605            House                                                      \n",
       "\n",
       "                                                address    price  \\\n",
       "0     1801 5611 GORING STREET|Burnaby, British Colum...   878000   \n",
       "1     9690 Eanen Ave|Port Alberni, British Columbia ...  2000000   \n",
       "2     1704 2085 SKYLINE COURT|Burnaby, British Colum...  1099000   \n",
       "3     203 2378 WILSON AVENUE|Port Coquitlam, British...   499900   \n",
       "4     2307 567 CLARKE ROAD|Coquitlam, British Columb...   599900   \n",
       "...                                                 ...      ...   \n",
       "1601           110 7th AVE|Alameda, Saskatchewan S0C0A0    78000   \n",
       "1602  102 3349 DUNBAR STREET|Vancouver, British Colu...  1250000   \n",
       "1603  2802 1199 MARINASIDE CRESCENT|Vancouver, Briti...  1999900   \n",
       "1604  2833 W 18TH AVENUE|Vancouver, British Columbia...  2588000   \n",
       "1605  3920 GRANDVIEW DRIVE|Castlegar, British Columb...   759900   \n",
       "\n",
       "      property_type           agent Name area Code phone Number postal_code  \n",
       "0     Single Family         Oleg Tsaryov       604     719-4490      V5B0A3  \n",
       "1     Single Family           Bobby Toor       250     228-8667      V9Y9C6  \n",
       "2     Single Family           David Duan       778     952-6577      V5C0M6  \n",
       "3     Single Family          Russ Macnab       604     763-8196      V3C1Z6  \n",
       "4     Single Family      Paul Liberatore       604     788-0463      V3J3X4  \n",
       "...             ...                  ...       ...          ...         ...  \n",
       "1601  Single Family  Dana (Dana) Krienke       306     485-7474      S0C0A0  \n",
       "1602  Single Family           Robert Lee       604     908-4522      V6S2B9  \n",
       "1603  Single Family  Steve Triantafillou       604     779-8590      V6Z2Y2  \n",
       "1604  Single Family             Henry Wu       778     238-8182      V6L1B7  \n",
       "1605  Single Family         SIMON LAURIE       250     365-1585      V1N4X9  \n",
       "\n",
       "[1606 rows x 12 columns]"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names = {\"public_remarks\": public_remarks, \n",
    "        'bathrooms':bathrooms, 'bedrooms':bedrooms, 'building_type':building_type, 'ammenities':ammenities, \n",
    "        'address':address, 'price':price, 'property_type':property_type, \n",
    "        'agent Name':agent_name, 'area Code': area_code, 'phone Number': phone_number, \n",
    "        'postal_code':postal_code}\n",
    "df_realtor = pd.DataFrame(names)\n",
    "df_realtor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1298, 12)"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(1288,)"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "2988 BURNS ROAD|Port Coquitlam, British Columbia V3C3G7        4\n",
       "1394 SEYMOUR STREET|Vancouver, British Columbia V6B3P3         3\n",
       "4177 W 11TH AVENUE|Vancouver, British Columbia V6R2L5          3\n",
       "203 2075 E 1ST AVENUE|Vancouver, British Columbia V5N1B6       3\n",
       "3103 2311 BETA AVENUE|Burnaby, British Columbia V5C0M1         3\n",
       "                                                              ..\n",
       "Lot 11 Redden Rd|Nanoose Bay, British Columbia V9P9H4          1\n",
       "277 Veterans Drive|Cormack, Newfoundland & Labrador A9A2R9     1\n",
       "208 1189 WESTWOOD STREET|Coquitlam, British Columbia V3B7P5    1\n",
       "206 438 GAMMA AVENUE|Burnaby, British Columbia V5C0J7          1\n",
       "2833 W 18TH AVENUE|Vancouver, British Columbia V6L1B7          1\n",
       "Name: address, Length: 1287, dtype: int64"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_realtor.drop_duplicates().shape\n",
    "np.unique(df_realtor.address + df_realtor.price).shape\n",
    "df_realtor.address.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 9 - Store results in Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A past save exists, so we will append to it\n",
      "past_df.shape: (2900, 15)\n"
     ]
    }
   ],
   "source": [
    "import os \n",
    "# check if file exists\n",
    "if os.path.exists('realtor.csv'):\n",
    "    print(f'A past save exists, so we will append to it')\n",
    "    past_df = pd.read_csv('realtor.csv', index_col=0)\n",
    "    past_df = pd.concat([past_df, df_realtor], ignore_index=True)\n",
    "    past_df.drop_duplicates(inplace=True)\n",
    "    past_df.to_csv('realtor.csv', index=True)\n",
    "    past_df.to_excel('realtor.xlsx', index=True)\n",
    "    print(f'past_df.shape: {past_df.shape}')\n",
    "else:\n",
    "    df_realtor.to_excel('realtor.xlsx', index=True)\n",
    "    df_realtor.to_csv('realtor.csv', index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 10 - Store in PostgreSQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # create sqlalchemy engine\n",
    "# engine = sqlalchemy.create_engine('postgresql://postgresql:12345@localhost:5432')\n",
    "# df_realtor.to_sql('realtor_table', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "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>public_remarks</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>building_type</th>\n",
       "      <th>ammenities</th>\n",
       "      <th>address</th>\n",
       "      <th>price</th>\n",
       "      <th>property_type</th>\n",
       "      <th>Agent Name</th>\n",
       "      <th>Area Code</th>\n",
       "      <th>Phone Number</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>agent Name</th>\n",
       "      <th>area Code</th>\n",
       "      <th>phone Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pinnacle of Homer located at centre of Yaletow...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Row / Townhouse</td>\n",
       "      <td>Exercise Centre, Guest Suite, Recreation Centre</td>\n",
       "      <td>929 HOMER STREET|Vancouver, British Columbia V...</td>\n",
       "      <td>1158000.0</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Frank Zheng Chen</td>\n",
       "      <td>778.0</td>\n",
       "      <td>989-1081</td>\n",
       "      <td>V6B2W6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SPECTACULAR OCEAN VIEWS! Nestled on a private ...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>House</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14 BENSON DRIVE|Port Moody, British Columbia V...</td>\n",
       "      <td>1688000.0</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Janice Mazzone</td>\n",
       "      <td>604.0</td>\n",
       "      <td>250-7858</td>\n",
       "      <td>V3H3P6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Welcome to The Residence at the West! Located ...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Daycare, Exercise Centre, Laundry - In Suite</td>\n",
       "      <td>220 1783 MANITOBA STREET|Vancouver, British Co...</td>\n",
       "      <td>798800.0</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Iman Moghadam</td>\n",
       "      <td>604.0</td>\n",
       "      <td>721-6209</td>\n",
       "      <td>V5Y0K1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fantastic NW views of Harbour, Bridge and moun...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre</td>\n",
       "      <td>1904 1616 BAYSHORE DRIVE|Vancouver, British Co...</td>\n",
       "      <td>2198000.0</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Navid Behroyan</td>\n",
       "      <td>778.0</td>\n",
       "      <td>882-6443</td>\n",
       "      <td>V6G3L1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Koret Lofts is a historic heritage conversion ...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Laundry - In Suite</td>\n",
       "      <td>405 55 E CORDOVA STREET|Vancouver, British Col...</td>\n",
       "      <td>849000.0</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>Winston Chan</td>\n",
       "      <td>604.0</td>\n",
       "      <td>767-3221</td>\n",
       "      <td>V6A0A5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4182</th>\n",
       "      <td>This 2 bed corner home checks everything off y...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre, Guest Suite, Laundry - In Suite</td>\n",
       "      <td>404 1045 AUSTIN AVENUE|Coquitlam, British Colu...</td>\n",
       "      <td>989900</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>V0V0V0</td>\n",
       "      <td>Nathalie Jabour</td>\n",
       "      <td>778</td>\n",
       "      <td>859-5999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4183</th>\n",
       "      <td>This 2 bed corner home checks everything off y...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Exercise Centre, Guest Suite, Laundry - In Suite</td>\n",
       "      <td>1105 1045 AUSTIN AVENUE|Coquitlam, British Col...</td>\n",
       "      <td>899900</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>V0V0V0</td>\n",
       "      <td>Nathalie Jabour</td>\n",
       "      <td>778</td>\n",
       "      <td>859-5999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4187</th>\n",
       "      <td>Welcome to the Foothills! This Master Planned ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Lot 20 Broad Ridge Pl|Lantzville, British Colu...</td>\n",
       "      <td>480000</td>\n",
       "      <td>Vacant Land</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Z9Z9Z9</td>\n",
       "      <td>Derek Gillette</td>\n",
       "      <td>250</td>\n",
       "      <td>585-3322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4189</th>\n",
       "      <td>One of a Kind!  SPECTACULAR 629 SF ROOF TOP DE...</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Row / Townhouse</td>\n",
       "      <td>Laundry - In Suite</td>\n",
       "      <td>102 3349 DUNBAR STREET|Vancouver, British Colu...</td>\n",
       "      <td>1250000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>V6S2B9</td>\n",
       "      <td>Robert Lee</td>\n",
       "      <td>604</td>\n",
       "      <td>908-4522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4191</th>\n",
       "      <td>Why buy coach house, laneway, duplex or whiche...</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>House</td>\n",
       "      <td></td>\n",
       "      <td>2833 W 18TH AVENUE|Vancouver, British Columbia...</td>\n",
       "      <td>2588000</td>\n",
       "      <td>Single Family</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>V6L1B7</td>\n",
       "      <td>Henry Wu</td>\n",
       "      <td>778</td>\n",
       "      <td>238-8182</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2900 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         public_remarks bathrooms bedrooms  \\\n",
       "0     Pinnacle of Homer located at centre of Yaletow...       3.0        3   \n",
       "1     SPECTACULAR OCEAN VIEWS! Nestled on a private ...       3.0        4   \n",
       "2     Welcome to The Residence at the West! Located ...       1.0        1   \n",
       "3     Fantastic NW views of Harbour, Bridge and moun...       2.0        2   \n",
       "4     Koret Lofts is a historic heritage conversion ...       1.0        0   \n",
       "...                                                 ...       ...      ...   \n",
       "4182  This 2 bed corner home checks everything off y...         2        2   \n",
       "4183  This 2 bed corner home checks everything off y...         2        2   \n",
       "4187  Welcome to the Foothills! This Master Planned ...         0        0   \n",
       "4189  One of a Kind!  SPECTACULAR 629 SF ROOF TOP DE...         2        3   \n",
       "4191  Why buy coach house, laneway, duplex or whiche...         2        3   \n",
       "\n",
       "        building_type                                        ammenities  \\\n",
       "0     Row / Townhouse   Exercise Centre, Guest Suite, Recreation Centre   \n",
       "1               House                                               NaN   \n",
       "2           Apartment      Daycare, Exercise Centre, Laundry - In Suite   \n",
       "3           Apartment                                   Exercise Centre   \n",
       "4           Apartment                                Laundry - In Suite   \n",
       "...               ...                                               ...   \n",
       "4182        Apartment  Exercise Centre, Guest Suite, Laundry - In Suite   \n",
       "4183        Apartment  Exercise Centre, Guest Suite, Laundry - In Suite   \n",
       "4187                                                                      \n",
       "4189  Row / Townhouse                                Laundry - In Suite   \n",
       "4191            House                                                     \n",
       "\n",
       "                                                address      price  \\\n",
       "0     929 HOMER STREET|Vancouver, British Columbia V...  1158000.0   \n",
       "1     14 BENSON DRIVE|Port Moody, British Columbia V...  1688000.0   \n",
       "2     220 1783 MANITOBA STREET|Vancouver, British Co...   798800.0   \n",
       "3     1904 1616 BAYSHORE DRIVE|Vancouver, British Co...  2198000.0   \n",
       "4     405 55 E CORDOVA STREET|Vancouver, British Col...   849000.0   \n",
       "...                                                 ...        ...   \n",
       "4182  404 1045 AUSTIN AVENUE|Coquitlam, British Colu...     989900   \n",
       "4183  1105 1045 AUSTIN AVENUE|Coquitlam, British Col...     899900   \n",
       "4187  Lot 20 Broad Ridge Pl|Lantzville, British Colu...     480000   \n",
       "4189  102 3349 DUNBAR STREET|Vancouver, British Colu...    1250000   \n",
       "4191  2833 W 18TH AVENUE|Vancouver, British Columbia...    2588000   \n",
       "\n",
       "      property_type        Agent Name  Area Code Phone Number postal_code  \\\n",
       "0     Single Family  Frank Zheng Chen      778.0     989-1081      V6B2W6   \n",
       "1     Single Family    Janice Mazzone      604.0     250-7858      V3H3P6   \n",
       "2     Single Family     Iman Moghadam      604.0     721-6209      V5Y0K1   \n",
       "3     Single Family    Navid Behroyan      778.0     882-6443      V6G3L1   \n",
       "4     Single Family      Winston Chan      604.0     767-3221      V6A0A5   \n",
       "...             ...               ...        ...          ...         ...   \n",
       "4182  Single Family               NaN        NaN          NaN      V0V0V0   \n",
       "4183  Single Family               NaN        NaN          NaN      V0V0V0   \n",
       "4187    Vacant Land               NaN        NaN          NaN      Z9Z9Z9   \n",
       "4189  Single Family               NaN        NaN          NaN      V6S2B9   \n",
       "4191  Single Family               NaN        NaN          NaN      V6L1B7   \n",
       "\n",
       "           agent Name area Code phone Number  \n",
       "0                 NaN       NaN          NaN  \n",
       "1                 NaN       NaN          NaN  \n",
       "2                 NaN       NaN          NaN  \n",
       "3                 NaN       NaN          NaN  \n",
       "4                 NaN       NaN          NaN  \n",
       "...               ...       ...          ...  \n",
       "4182  Nathalie Jabour       778     859-5999  \n",
       "4183  Nathalie Jabour       778     859-5999  \n",
       "4187   Derek Gillette       250     585-3322  \n",
       "4189       Robert Lee       604     908-4522  \n",
       "4191         Henry Wu       778     238-8182  \n",
       "\n",
       "[2900 rows x 15 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "past_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.10.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}