Housing_Price_Prediction_in_Milan_(Italy)_through_Deep_Learning_via_immobiliare_it.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Housing Price Prediction in Milan (Italy) through Deep Learning via immobiliare.it.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "kp788-zijJ5_",
        "aL71vrUhJt3Q",
        "9_fW-Wbk0HRi",
        "pkpwkeWLvOFd",
        "oqEId5Nja8Wf",
        "pCSi3K9Jm58U"
      ],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/drive/1sDW7-GKvOuUDKlySrDvBpOft4YtGCphQ)"
      ],
      "metadata": {
        "id": "GDZlPmktdQdT"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wxX68uAbjv4j"
      },
      "source": [
        "# **Housing Price Prediction in Milan (Italy) through Deep Learning via immobiliare.it**\n",
        "\n",
        "* In **2020** there was a break in **Real Estate Market** because of by **Covid-19** emergency. This crisis caused *price freeze*, *dawnward trading* and *instability*.\n",
        "\n",
        "* From April 2020 to October in Italy **-0.4% of prices**, **-8.5% in Sardinia** after summer for few turists and **in Milan -2.2%** to then go up again of **+0.5%**.\n",
        "\n",
        "* Real Estate sales fell of **-19.5%**, specially in Milan with a collapse of **-12%**.\n",
        "\n",
        "* People paid more attention to green like *private gardens*, or balconies; to large surface in province houses, but in center of cities they search small houses for one person; to quality of services nearby and much more to *countryside* than to traffic *old towns*.\n",
        "\n",
        "* **Challenge**: predict houses prices in Milan through Deep Learning in this period of instability.\n",
        "\n",
        "* This project was created by **Andrea Bazerla** in 2021 for *Deep Learning* exam at **University of Ferrara**. It is licensed under MIT License, hosted in [GitHub](https://github.com/andreabazerla/real-estate) and described in [Medium](https://medium.com/@andreabazerla)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "L5DfD8WakIHP"
      },
      "source": [
        "## Settings\n",
        "\n",
        "Initial section to set some settings, import generic libraries useful for whole code and global scripts."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ub9J4KGqPTlV"
      },
      "source": [
        "# Generic libraries import\n",
        "\n",
        "from google.colab import files\n",
        "import time\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "import seaborn as sns\n",
        "import matplotlib.pyplot as plt\n",
        "from tqdm.notebook import tqdm"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "M2E31Y_ltpbT"
      },
      "source": [
        "# Pandas display options to show all rows and columns of Dataframes\n",
        "\n",
        "pd.set_option('display.max_rows', None)\n",
        "pd.set_option('display.max_columns', None)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mitDG-fzISZ9"
      },
      "source": [
        "def get_timestamp() -> str:\n",
        "  '''Methods to get current timestamp as a string'''\n",
        "\n",
        "  return str(int(time.time()))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dgVSQ5Jy_86F"
      },
      "source": [
        "# ATTENTION: global costants to start processes inside code\n",
        "\n",
        "URL_SCRAPING = False\n",
        "ADS_SCRAPING = False"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kp788-zijJ5_"
      },
      "source": [
        "## Web Scraping\n",
        "\n",
        "What is **Web Scraping**? It is a technique to extract data from a website automatically via HTTPS.\n",
        "\n",
        "The receiver of data is not an end-user, but a \"software\". \n",
        "\n",
        "Stages of the process: fetching() -> extracting() -> parsing() -> cleaning() -> storing()"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C8iiMOewxqKw"
      },
      "source": [
        "# Import libraries useful for Web Scraping\n",
        "\n",
        "import requests\n",
        "import logging\n",
        "from enum import Enum \n",
        "from random import uniform\n",
        "from bs4 import BeautifulSoup\n",
        "import urllib.parse"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MTs8ZzrGrMV7"
      },
      "source": [
        "# Support Enums to build the scraping URL\n",
        "\n",
        "class Contract(Enum):\n",
        "  '''Type of contract'''\n",
        "\n",
        "  SALE = 'vendita'\n",
        "  RENT = 'affitto'\n",
        "\n",
        "class Area(Enum):\n",
        "  '''Search area like a city'''\n",
        "\n",
        "  ROME = 'roma'\n",
        "  MILAN = 'milano'\n",
        "  VENICE = 'venezia'\n",
        "  TURIN = 'torino'\n",
        "  NAPLES = 'napoli'\n",
        "\n",
        "class Criterion(Enum):\n",
        "  '''Search criterion'''\n",
        "\n",
        "  RELEVANCE = 'rilevanza'\n",
        "  PRICE = 'prezzo'\n",
        "\n",
        "class Sort(Enum):\n",
        "  '''Sort type of search results'''\n",
        "\n",
        "  DESC = 'desc'\n",
        "  ASC = 'asc'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FHhqN9p1pp89"
      },
      "source": [
        "# Method to build scraping URL \n",
        "\n",
        "def build_scraping_url(website, contract, area, criterion=Criterion.RELEVANCE.value, sort=Sort.DESC):\n",
        "  '''Method to build scraping URL'''\n",
        "\n",
        "  vars = { 'criterio': criterion }\n",
        "\n",
        "  if criterion != Criterion.RELEVANCE.value:\n",
        "    vars['sort'] = sort\n",
        "\n",
        "  url = website + '/' + contract + '/' + area + '/?' + urllib.parse.urlencode(vars)\n",
        "  return url"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2qxLenVdvlLu"
      },
      "source": [
        "# Scraping URL parameters\n",
        "\n",
        "website = 'https://www.immobiliare.it'\n",
        "contract = Contract.SALE.value + '-case'\n",
        "area = Area.MILAN.value\n",
        "criterion = Criterion.RELEVANCE.value\n",
        "sort = Sort.DESC.value\n",
        "\n",
        "url = build_scraping_url(website, contract, area, criterion, sort)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rk7uBBiTp8SG"
      },
      "source": [
        "print(url)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4sDTxZLWoypo"
      },
      "source": [
        "# Method to sleep between HTTP requests\n",
        "\n",
        "sleep_min = 1\n",
        "sleep_max = 2\n",
        "\n",
        "def sleep():\n",
        "  '''Default method to sleep between HTTP requests\n",
        "\n",
        "  It returns uniform random time to sleep between sleep_min and sleep_max variables.\n",
        "  '''\n",
        "\n",
        "  time.sleep(uniform(sleep_min, sleep_max))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Hldwisi3Pj2k"
      },
      "source": [
        "### Ads URL Scraping\n",
        "\n",
        "In this section Web Scraping is closed to ad URLs."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ujp1eEaWqpda"
      },
      "source": [
        "def get_last_page(url):\n",
        "  '''Method that returns the last page number of scraping search'''\n",
        "\n",
        "  try:\n",
        "    response = requests.get(url)\n",
        "    beautiful_soup = BeautifulSoup(response.content, 'html.parser')\n",
        "  \n",
        "    ul_pagination = beautiful_soup.find('ul', class_ = 'pagination pagination__number')\n",
        "    li_list = ul_pagination.find_all('li')\n",
        "    last_page = int(li_list[-1].get_text().strip())\n",
        "  \n",
        "    return last_page\n",
        "  \n",
        "  except requests.exceptions.RequestException as e:\n",
        "    raise SystemExit(e)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6aAvqYAvLTwe"
      },
      "source": [
        "def get_url_list(url, first_page, last_page):\n",
        "  '''Method that return an ads url list between firts_page and last_page range.\n",
        "  \n",
        "  As you can see, between requests there's a sleep to avoid to break target server.\n",
        "  \n",
        "  '''\n",
        " \n",
        "  url_list = []\n",
        "  page_dropped_list = []\n",
        "    \n",
        "  for page in tqdm(range(first_page, last_page + 1)):\n",
        "    if (page > 1):\n",
        "      url = url + '&pag=' + str(page)\n",
        "    \n",
        "    try:\n",
        "      response = requests.get(url)\n",
        "      beautiful_soup = BeautifulSoup(response.content, 'html.parser')\n",
        "    \n",
        "      ads_list = beautiful_soup.find('ul', class_ = 'annunci-list')\n",
        "      ad_item_list = ads_list.find_all('div', class_ = 'listing-item_body--content')\n",
        "      for ad_item in ad_item_list:\n",
        "        a_list = ad_item.find_all('a')\n",
        "        for a in a_list:\n",
        "          url_list.append(a['href'])\n",
        "    \n",
        "    except Exception as e:\n",
        "      logging.exception(e)\n",
        "      page_dropped_list.append(page)      \n",
        "      pass\n",
        " \n",
        "    sleep()\n",
        " \n",
        "  if page_dropped_list:\n",
        "    print(page_dropped_list)\n",
        "  \n",
        "  return url_list"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ds-RxO8FRWJp"
      },
      "source": [
        "# Define first_page and last_page of Web Scraping\n",
        "\n",
        "first_page = 1\n",
        "last_page = get_last_page(url)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IrNGWfQU_tDs"
      },
      "source": [
        "# Start ad URLs Web Scraping (only if WEB_SCRAPING is True)\n",
        "\n",
        "if URL_SCRAPING:\n",
        "  url_list = get_url_list(url, first_page, last_page)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vRYgjfjwTaZD"
      },
      "source": [
        "# Create a Dataframe for ad URLs with Pandas \n",
        "\n",
        "df_url = pd.DataFrame({'url' : list(url_list)})\n",
        "\n",
        "df_url['id'] = range(1, len(df_url) + 1)\n",
        "df_url.set_index('id', inplace=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "J1-miVRuHolW"
      },
      "source": [
        "display(df_url.head(10))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lFTM1BuzcGNo"
      },
      "source": [
        "csv_url = 'url_' + str(first_page) + '_' + str(last_page) + '_' + get_timestamp() + '.csv'\n",
        "df_url.to_csv(csv_url)\n",
        "files.download(csv_url)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "66tYwTTjP4ly"
      },
      "source": [
        "### Ads Scraping "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nm0dZtFpG1FQ"
      },
      "source": [
        "import os"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ykpkKlkZogG3"
      },
      "source": [
        "def get_ad_title(soup):\n",
        "  '''Method that returns ad title'''\n",
        "\n",
        "  titleBlock__title = soup.find('span', class_ = 'im-titleBlock__title')\n",
        "  if titleBlock__title is not None:\n",
        "    return titleBlock__title.get_text()\n",
        "  else:\n",
        "    return ''"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nb-N84mK6Ipm"
      },
      "source": [
        "def get_ad_price(soup):\n",
        "  '''Method that returns ad price'''\n",
        "\n",
        "  mainFeatures__price = soup.find_all('li', class_ = 'im-mainFeatures__price')\n",
        "  if mainFeatures__price:\n",
        "    return mainFeatures__price[0].get_text().replace('\\n', '').strip()\n",
        "  else:\n",
        "    return ''"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WXwAGku2_5V1"
      },
      "source": [
        "def get_ad_main_features(soup):\n",
        "  '''Method that returns ad main features'''\n",
        "\n",
        "  main_features = {}\n",
        "  \n",
        "  mainFeatures = soup.find('div', class_ = 'im-mainFeatures')\n",
        "  \n",
        "  li_list = mainFeatures.find_all('li')\n",
        "  for li in li_list[1:]:\n",
        "    value = li.find('span', class_=\"im-mainFeatures__value\").get_text().replace('\\n', '').strip()\n",
        "    label = li.find('span', class_=\"im-mainFeatures__label\").get_text().replace('\\n', '').strip()\n",
        "    \n",
        "    # If data labels are plural, convert them to singular form\n",
        "    if (label == 'bagno' or label == 'bagni'):\n",
        "      label = 'bagni'\n",
        "    \n",
        "    if (label == 'locale' or label == 'locali'):\n",
        "      label = 'locali'\n",
        "    \n",
        "    main_features[label] = value\n",
        "  \n",
        "  return main_features"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QgHNdLhX23BI"
      },
      "source": [
        "def get_ad_description(soup):\n",
        "  '''Method that returns ad description'''\n",
        "\n",
        "  description__text = soup.find('div', class_ = 'im-description__text')\n",
        "  if description__text is not None:\n",
        "    return description__text.get_text()\n",
        "  else:\n",
        "    return ''"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GT0-tj0Geel2"
      },
      "source": [
        "def get_ad_locations(soup):\n",
        "  '''Method that returns ad locations like area, district and address if not empty'''\n",
        "  \n",
        "  # Legacy code turnaround\n",
        "  titleBlock__link = soup.find('a', class_ = 'im-titleBlock__link')\n",
        "  if titleBlock__link is None:\n",
        "    titleBlock__link = soup.find('h1', class_ = 'im-titleBlock__content')\n",
        "\n",
        "  location = titleBlock__link.find_all('span', class_ = 'im-location')\n",
        "  \n",
        "  try:\n",
        "    area = location[0].get_text().strip()\n",
        "  except IndexError:\n",
        "    area = ''\n",
        "  \n",
        "  try:\n",
        "    district = location[1].get_text().strip()\n",
        "  except IndexError:\n",
        "    district = ''\n",
        "\n",
        "  try:\n",
        "    address = location[2].get_text().strip()\n",
        "  except IndexError:\n",
        "    address = ''\n",
        "\n",
        "  return [area, district, address]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EhuVyk5h_Kfb"
      },
      "source": [
        "def get_ad_feature_list(soup):\n",
        "  '''Method that returns ad feature list in tag form as a set'''\n",
        "\n",
        "  features = {}\n",
        "  \n",
        "  features__list = soup.find_all(\"dl\", class_ = \"im-features__list\")\n",
        "  \n",
        "  for feature_block in features__list:\n",
        "    feature__title_list = feature_block.find_all('dt', class_ = 'im-features__title')\n",
        "  \n",
        "    for feature__title in feature__title_list:\n",
        "      feature__value = feature__title.findNext('dd')\n",
        "  \n",
        "      if ('im-features__tagContainer' in feature__value.get('class')):\n",
        "        features__tag_array = []\n",
        "\n",
        "        features__tag_list = soup.find_all('span', class_ = 'im-features__tag')\n",
        "        for feature__tag in features__tag_list:\n",
        "          features__tag_array.append(feature__tag.get_text().strip())\n",
        "  \n",
        "        features__tag_list_string = ','.join(features__tag_array)\n",
        "        feature__value_2 = features__tag_list_string\n",
        "  \n",
        "      else:\n",
        "        feature__value_2 = feature__value.get_text().strip()\n",
        "  \n",
        "      feature__title_2 = feature__title.get_text().strip()\n",
        "      features['f_' + feature__title_2] = feature__value_2\n",
        "  \n",
        "  return features"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pycQvNmVRlDT"
      },
      "source": [
        "def get_ad_single(url, soup):\n",
        "\n",
        "  ads_list = []\n",
        "\n",
        "  ad_data = {}\n",
        "\n",
        "  ad_data['url'] = url\n",
        "\n",
        "  # Get ad title\n",
        "  title = get_ad_title(soup);\n",
        "  ad_data['titolo'] = title\n",
        "\n",
        "  # Get ad price\n",
        "  price = get_ad_price(soup);\n",
        "  ad_data['prezzo'] = price\n",
        "\n",
        "  # Get ad main features\n",
        "  main_features = get_ad_main_features(soup)\n",
        "  if main_features:\n",
        "    ad_data.update(main_features)\n",
        "\n",
        "  # Get ad description\n",
        "  description = get_ad_description(soup);\n",
        "  ad_data['descrizione'] = description\n",
        "\n",
        "  # Get ad area, district and address\n",
        "  area, district, address = get_ad_locations(soup)\n",
        "  ad_data['area'] = area\n",
        "  ad_data['quartiere'] = district\n",
        "  ad_data['indirizzo'] = address\n",
        "\n",
        "  # Get ad tag features\n",
        "  feature_list = get_ad_feature_list(soup)\n",
        "  if feature_list:\n",
        "    ad_data.update(feature_list)\n",
        "\n",
        "  # Add to ad an hashcode based on its data\n",
        "  ad_data['hashcode'] = hash(frozenset(ad_data.items()))\n",
        "\n",
        "  ads_list.append(ad_data)\n",
        "\n",
        "  return ads_list"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K_dAprBDSunf"
      },
      "source": [
        "def get_ad_multi(url, soup):\n",
        "  ads_list = []\n",
        "\n",
        "  title = get_ad_title(soup)\n",
        "\n",
        "  area, district, address = get_ad_locations(soup)\n",
        "\n",
        "  main_features = get_ad_main_features(soup)\n",
        "\n",
        "  description = get_ad_description(soup)\n",
        "\n",
        "  feature_list = get_ad_feature_list(soup)\n",
        "\n",
        "  properties__list = soup.find('ul', class_ = 'im-properties__list')\n",
        "  properties__item_list = properties__list.find_all('li', class_ = 'im-properties__item')\n",
        "  for properties__item in properties__item_list:\n",
        "    ad_data = {}\n",
        "\n",
        "    ad_data['url'] = url\n",
        "\n",
        "    ad_data['titolo'] = title\n",
        "\n",
        "    ad_data['area'] = area\n",
        "    ad_data['quartiere'] = district\n",
        "    ad_data['indirizzo'] = address\n",
        "    \n",
        "    price = get_ad_price(properties__item)\n",
        "    ad_data['prezzo'] = price\n",
        "\n",
        "    ad_data['descrizione'] = description\n",
        "\n",
        "    sub_features = get_ad_main_features(properties__item)\n",
        "    if sub_features:\n",
        "      ad_data.update(sub_features)\n",
        "\n",
        "    title_2 = properties__item.find('p', class_ = 'nd-mediaObject__title')\n",
        "    if title_2 is not None:\n",
        "      ad_data['titolo_2'] = title_2.get_text().strip()\n",
        "\n",
        "    description_2 = properties__item.find('div', class_ = 'im-properties__content')\n",
        "    if description_2 is not None:\n",
        "      ad_data['descrizione_2'] = description_2.get_text()\n",
        "\n",
        "    if feature_list:\n",
        "      ad_data.update(feature_list)\n",
        "\n",
        "    ad_data['hashcode'] = hash(frozenset(ad_data.items()))\n",
        "\n",
        "    ads_list.append(ad_data)\n",
        "\n",
        "  return ads_list"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_06ZkJ_xRD7b"
      },
      "source": [
        "def get_ad(url):\n",
        "\n",
        "  ads_list = []\n",
        "  url_dropped_list = []\n",
        "\n",
        "  try:\n",
        "    response = requests.get(url)\n",
        "    if response:\n",
        "      soup = BeautifulSoup(response.content, \"html.parser\")\n",
        "\n",
        "      if 'p-' in url:\n",
        "        ad_data = get_ad_multi(url, soup)\n",
        "      else:\n",
        "        ad_data = get_ad_single(url, soup)\n",
        "\n",
        "      ads_list.extend(ad_data)\n",
        "\n",
        "  except Exception as e:\n",
        "    logging.exception(e)\n",
        "    url_dropped_list.append(url)\n",
        "    pass\n",
        "\n",
        "  return [ads_list, url_dropped_list]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4-evOLRfGpIJ"
      },
      "source": [
        "# Read ads URL from CSV file\n",
        "\n",
        "df_url = pd.read_csv('url_1_639_1620401663.csv')\n",
        "url_list = df_url['url'].to_list()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9DnnXijuFbWz"
      },
      "source": [
        "if ADS_SCRAPING:\n",
        "  df_ads = pd.DataFrame()\n",
        "\n",
        "  first_ad = 0\n",
        "\n",
        "  # Set last_ad to first_ad + 500 \n",
        "  last_ad = 5\n",
        "  #last_ad = len(url_list)\n",
        "  \n",
        "  ads_csv = 'ads_' + str(first_ad + 1) + '_' + str(last_ad) + '_' + get_timestamp() + '.csv'\n",
        "\n",
        "  ads_list = []\n",
        "  url_dropped_list = []\n",
        "  for i in tqdm(range(first_ad, last_ad)):\n",
        "\n",
        "    ad_data = get_ad(url_list[i])\n",
        "\n",
        "    if ad_data[1]:\n",
        "      url_dropped_list.extend(ad_data[1])\n",
        "\n",
        "    for ad in ad_data[0]:\n",
        "      ads_list.append(ad)\n",
        "    \n",
        "    sleep()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yPoTdvJhhopw"
      },
      "source": [
        "print(url_dropped_list)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bFYjBnI2bLnQ"
      },
      "source": [
        "df_ads = pd.DataFrame(ads_list)\n",
        "\n",
        "df_ads.fillna('', inplace=True)\n",
        "\n",
        "df_ads['id'] = range(1, len(df_ads) + 1)\n",
        "df_ads.set_index('id', inplace=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D11eWCH2Qe2w"
      },
      "source": [
        "display(df_ads)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2UlQn5RhpOn1"
      },
      "source": [
        "df_ads.to_csv(ads_csv)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8d2rVUAiJqLs"
      },
      "source": [
        "files.download(ads_csv)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oMfGVmeDevm3"
      },
      "source": [
        "ads_folder = 'ads'\n",
        "file_list = os.listdir(ads_folder)\n",
        "ads_files = [file for file in file_list if file.startswith('Ads')]\n",
        "ads_files.sort()\n",
        "\n",
        "df_files = [None] * len(ads_files)\n",
        "for idx, file in enumerate(ads_files):\n",
        "  df_files[idx] = pd.read_csv(os.path.join(ads_folder, file))\n",
        "\n",
        "df_final = pd.concat(df_files).drop_duplicates().reset_index(drop=True)\n",
        "\n",
        "ads_csv_final = 'Ads' + '_' + get_timestamp() + '.csv'\n",
        "df_final.to_csv(ads_csv_final, index=False)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a___P2QyGfBt"
      },
      "source": [
        "files.download(ads_csv_final)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aL71vrUhJt3Q"
      },
      "source": [
        "## Data Cleaning\n",
        "\n",
        "* Data Cleaning is the process of detecting and correcting (or removing) corrupt or inaccurate records from a record set, table, or database and refers to identifying incomplete, incorrect, inaccurate or irrelevant parts of the data and then replacing, modifying, or deleting the dirty or coarse data.\n",
        "\n",
        "* In this section from raw real estate data just web scraped in the previous section, you'll remove multiple real estate ads with few features, auction real estates, without price or other essential features, useless column, etc. \n",
        "\n",
        "* Extract important data from string via regular expressions\n",
        "\n",
        "* Conversion of prices in clean integers without decimal, string in lower case, list of features in one-encoding columns, etc.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PP3EbdceyAsp"
      },
      "source": [
        "# Import Data Cleaning libraries\n",
        "\n",
        "import re\n",
        "\n",
        "from sklearn.preprocessing import MultiLabelBinarizer\n",
        "\n",
        "from scipy.stats import zscore"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "USFywIMmjm7M"
      },
      "source": [
        "files.upload()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iWtia4clIqkX"
      },
      "source": [
        "# Read Ads from CSV file\n",
        "\n",
        "csv_ads = 'ads_1617101603.csv'\n",
        "df_ads = pd.read_csv(csv_ads, dtype=str)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jxXW4uzfs_Rw"
      },
      "source": [
        "print(df_ads.info())\n",
        "display(df_ads.describe().transpose())\n",
        "display(df_ads.head(10).T)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wFYdzCLnmILf"
      },
      "source": [
        "# Create an ID as index\n",
        "df_ads['id'] = range(1, len(df_ads) + 1)\n",
        "df_ads.set_index('id', inplace = True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZvhH2zgzmJy1"
      },
      "source": [
        "# Remove multiple Ads \n",
        "df_ads = df_ads[~df_ads['url'].str.contains('p-')]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mF2-MDZnmMUG"
      },
      "source": [
        "# Remove rows if the following columns are not empty\n",
        "index_names = df_ads[\n",
        "  (df_ads['f_offerta minima'].notnull())\n",
        "  | (df_ads['f_rialzo minimo'].notnull())\n",
        "  | (df_ads['f_Spesa prenota debito'].notnull())\n",
        "  | (df_ads['f_Contributo non dovuto'].notnull())\n",
        "  | (df_ads['f_Tribunale'].notnull())\n",
        "  | (df_ads['f_termine presentazione'].notnull())\n",
        "  | (df_ads['f_lotto numero'].notnull())\n",
        "  | (df_ads['f_Deposito cauzionale'].notnull())\n",
        "  | (df_ads['f_luogo vendita'].notnull())\n",
        "  | (df_ads['f_Luogo presentazione'].notnull())\n",
        "  | (df_ads['f_categoria'].notnull())\n",
        "  | (df_ads['f_Procedura'].notnull())\n",
        "  | (df_ads['f_numero procedura'].notnull())\n",
        "  | (df_ads['f_Delegato'].notnull())\n",
        "  | (df_ads['f_Giudice'].notnull())\n",
        "  | (df_ads['f_Custode'].notnull())\n",
        "  | (df_ads['f_Dati catastali'].notnull())\n",
        "  | (df_ads['f_Rialzo minimo in caso di gara'].notnull())\n",
        "  | (df_ads['f_Motivo esenzione'].notnull())\n",
        "  | (df_ads['f_note'].notnull())\n",
        "  | (df_ads['f_Rito'].notnull())\n",
        "  | (df_ads['f_Curatore'].notnull())\n",
        "  | (df_ads['f_Altri dati catastali'].notnull())\n",
        "  | (df_ads['f_Deposito conto spese'].notnull())\n",
        "  | (df_ads['f_Cauzione e spese'].notnull())\n",
        "  | (df_ads['f_Referente'].notnull())\n",
        "  | (df_ads['f_valore perizia'].notnull())\n",
        "  | (df_ads['f_Delegato alla vendita'].notnull())\n",
        "].index\n",
        "\n",
        "df_ads = df_ads.drop(index_names)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5gahWY_CmPSB"
      },
      "source": [
        "# Remove useless columns\n",
        "columns_useless = [\n",
        "  'url',\n",
        "  'area',\n",
        "  'descrizione',\n",
        "  'titolo_2',\n",
        "  'descrizione_2',\n",
        "  'f_superficie',\n",
        "  'f_prezzo',\n",
        "  'f_riferimento e Data annuncio',\n",
        "  'f_immobile garantito',\n",
        "  'f_contratto',\n",
        "  'f_unità',\n",
        "  'f_Data di inizio lavori e di consegna prevista',\n",
        "  'f_Indice prest. energetica rinnovabile',\n",
        "  'f_Prestazione energetica del fabbricato',\n",
        "  'f_disponibilità',\n",
        "  'f_certificazione energetica',\n",
        "  'f_numero immobili',\n",
        "  'f_aggiornato il',\n",
        "  'hashcode',\n",
        "  'data vendita',\n",
        "  'f_Tipo vendita',\n",
        "  'f_data vendita',\n",
        "  'f_offerta minima',\n",
        "  'f_rialzo minimo',\n",
        "  'f_Spesa prenota debito',\n",
        "  'f_Contributo non dovuto',\n",
        "  'f_Tribunale',\n",
        "  'f_termine presentazione',\n",
        "  'f_lotto numero',\n",
        "  'f_Deposito cauzionale',\n",
        "  'f_luogo vendita',\n",
        "  'f_Luogo presentazione',\n",
        "  'f_categoria',\n",
        "  'f_Procedura',\n",
        "  'f_numero procedura',\n",
        "  'f_Delegato',\n",
        "  'f_Giudice',\n",
        "  'f_Custode',\n",
        "  'f_Dati catastali',\n",
        "  'f_Rialzo minimo in caso di gara',\n",
        "  'f_Motivo esenzione',\n",
        "  'f_note',\n",
        "  'f_Rito',\n",
        "  'f_Curatore',\n",
        "  'f_Altri dati catastali',\n",
        "  'f_Deposito conto spese',\n",
        "  'f_Cauzione e spese',\n",
        "  'f_Referente',\n",
        "  'f_valore perizia',\n",
        "  'f_Delegato alla vendita'\n",
        "]\n",
        "\n",
        "df_ads = df_ads.drop(columns_useless, axis=1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ahWkCoUdmRTQ"
      },
      "source": [
        "# Remove auction ads that contains string 'da' in column 'prezzo'\n",
        "df_ads = df_ads[~df_ads['prezzo'].str.contains('da', na=False)]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0MrW_y7SmTJs"
      },
      "source": [
        "# Remove rows if their columns value is not popular\n",
        "columns_unique_treshold = df_ads[['quartiere']]\n",
        "df_ads = df_ads[columns_unique_treshold.replace(columns_unique_treshold.apply(pd.Series.value_counts)).gt(10).all(1)]\n",
        "\n",
        "columns_unique_treshold_2 = df_ads[['f_stato']]\n",
        "df_ads = df_ads[columns_unique_treshold_2.replace(columns_unique_treshold_2.apply(pd.Series.value_counts)).gt(10).all(1)]\n",
        "\n",
        "columns_unique_treshold_3 = df_ads[['f_Tipo proprietà']]\n",
        "df_ads = df_ads[columns_unique_treshold_3.replace(columns_unique_treshold_3.apply(pd.Series.value_counts)).gt(10).all(1)]\n",
        "\n",
        "columns_unique_treshold_4 = df_ads[['f_tipologia']]\n",
        "df_ads = df_ads[columns_unique_treshold_4.replace(columns_unique_treshold_4.apply(pd.Series.value_counts)).gt(10).all(1)]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Zp2BR1q1mVK9"
      },
      "source": [
        "# Extract and normalize type and class of proprieties\n",
        "df_ads = df_ads[df_ads['f_Tipo proprietà'].notna()]\n",
        "df_ads['c_Tipo proprietà'] = df_ads['f_Tipo proprietà'].str.extract(r\"(Intera proprietà|Multiproprietà|Usufrutto|Diritto di superficie|Parziale proprietà|Nuda proprietà)\", flags = re.IGNORECASE)\n",
        "df_ads['c_Classe proprietà'] = df_ads['f_Tipo proprietà'].str.extract(r\"(classe immobile economica|classe immobile media|classe immobile signorile|immobile di lusso)\", flags = re.IGNORECASE)\n",
        "df_ads['c_Tipo proprietà'] = df_ads['c_Tipo proprietà'].str.lower().str.strip()\n",
        "df_ads['c_Classe proprietà'] = df_ads['c_Classe proprietà'].str.lower().str.strip()\n",
        "df_ads = df_ads[df_ads['c_Tipo proprietà'].notna()]\n",
        "df_ads = df_ads[df_ads['c_Classe proprietà'].notna()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LYc9f0ePmf_c"
      },
      "source": [
        "# Normalize the following columns string value\n",
        "df_ads['f_tipologia'] = df_ads['f_tipologia'].str.lower().str.strip()\n",
        "df_ads = df_ads[df_ads['f_tipologia'].notna()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dXEQ45sHmhcm"
      },
      "source": [
        "df_ads['quartiere'] = df_ads['quartiere'].str.lower().str.strip()\n",
        "df_ads = df_ads[df_ads['quartiere'].notna()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2cjvrnGmmp5O"
      },
      "source": [
        "# Extract and normalize status of pripriety like new, to be restructured, etc.\n",
        "df_ads = df_ads[df_ads['f_stato'].notna()]\n",
        "df_ads['c_stato'] = df_ads['f_stato'].str.extract(r\"(Da ristrutturare|Nuovo \\/ In costruzione|Buono \\/ Abitabile|Ottimo \\/ Ristrutturato)\", flags = re.IGNORECASE)\n",
        "df_ads['c_stato'] = df_ads['f_stato'].str.lower().str.strip()\n",
        "df_ads = df_ads[df_ads['c_stato'].notna()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kX7rtbudmsb7"
      },
      "source": [
        "df_ads = df_ads[df_ads['f_Efficienza energetica'].notna()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1hb5XJsOmuA3"
      },
      "source": [
        "# Extract and normalize price of proprieties\n",
        "df_ads['prezzo'] = df_ads['prezzo'].replace('[\\€\\.]', '', regex=True).replace(',00', '')\n",
        "df_ads['prezzo'] = df_ads['prezzo'].str.extract(r'(\\d+)')\n",
        "df_ads = df_ads[df_ads['prezzo'].notna()]\n",
        "df_ads['prezzo'] = df_ads['prezzo'].astype(int)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VhS3xrp2mvQf"
      },
      "source": [
        "# Remove rows where z-score of proprieties price is astypically grater than 3 and lower than 10'000€\n",
        "prezzo_filtered = abs(zscore(df_ads['prezzo'])) < 3\n",
        "df_ads = df_ads[prezzo_filtered]\n",
        "df_ads = df_ads[df_ads['prezzo'] > 10000]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XHCwmfaVmwcw"
      },
      "source": [
        "# Extract surface area of proprieties\n",
        "df_ads['superficie'] = df_ads['superficie'].str.extract(r'(\\d+)')\n",
        "df_ads = df_ads[df_ads['superficie'].notna()]\n",
        "df_ads['superficie'] = df_ads['superficie'].astype(int)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IX4sqpXemxsV"
      },
      "source": [
        "# Extract number of bathrooms of proprieties\n",
        "df_ads['bagni'] = df_ads['bagni'].str.extract(r'(1|2|3\\+|3)')\n",
        "df_ads['bagni'] = df_ads['bagni'].fillna('0')\n",
        "df_ads['bagni'] = df_ads['bagni'].astype(str)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WqQuZ91BmzQL"
      },
      "source": [
        "# Extract number of rooms of proprieties\n",
        "df_ads['locali'] = df_ads['locali'].str.extract(r\"(1|2|3|4|5\\+|5)\")\n",
        "df_ads = df_ads[df_ads['locali'].notna()]\n",
        "df_ads['locali'] = df_ads['locali'].astype(str)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9AloCeE0m0Tj"
      },
      "source": [
        "# Extract number of bedrooms and others rooms of proprieties\n",
        "df_ads['c_camere da letto'] = df_ads['f_locali'].str.extract(r\"(\\d\\scamer[a,e] da letto)\", flags = re.IGNORECASE)\n",
        "df_ads['c_altri locali'] = df_ads['f_locali'].str.extract(r\"(\\d\\saltr[o,i])\", flags = re.IGNORECASE)\n",
        "df_ads = df_ads[df_ads['c_camere da letto'].notna()]\n",
        "df_ads = df_ads[df_ads['c_altri locali'].notna()]\n",
        "\n",
        "# Calculate total number of rooms\n",
        "df_ads['c_calcolo numero locali'] = df_ads['c_camere da letto'].str.extract(r\"(\\d)\").astype(int) + df_ads['c_altri locali'].str.extract(r\"(\\d)\").astype(int)\n",
        "df_ads['c_numero totale locali'] = df_ads['locali']\n",
        "\n",
        "# Proprieties that have number of rooms equals to 5+, replace it with the exact number of them\n",
        "df_ads.loc[df_ads['c_numero totale locali'] == '5+', 'c_numero totale locali'] = df_ads['c_calcolo numero locali']\n",
        "df_ads['c_numero totale locali'] = df_ads['c_numero totale locali'].astype(int)\n",
        "\n",
        "df_ads['c_camere da letto'] = df_ads['c_camere da letto'].str.extract(r\"(\\d)\").astype(int)\n",
        "df_ads['c_altri locali'] = df_ads['c_altri locali'].str.extract(r\"(\\d)\").astype(int)\n",
        "\n",
        "df_ads['c_numero totale locali'] = df_ads['c_numero totale locali'].astype(int)\n",
        "\n",
        "df_ads.drop(['c_calcolo numero locali'], axis=1, inplace=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vKvP6MI0nJeg"
      },
      "source": [
        "# Extract and normalize type of \n",
        "df_ads['c_tipo cucina'] = df_ads['f_locali'].str.extract(r\"(cucina abitabile|cucina a vista|cucina angolo cottura|cucina cucinotto|cucina semi abitabile)\", flags = re.IGNORECASE)\n",
        "df_ads['c_tipo cucina'] = df_ads['c_tipo cucina'].str.lower().str.strip().fillna('')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8tnzbikdnLB2"
      },
      "source": [
        "# Set if proprieties has a tennis court\n",
        "df_ads['c_campo da tennis'] = df_ads['f_locali'].str.contains(\"campo da tennis\").astype(int).astype(str)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IJGK4nFKnMlo"
      },
      "source": [
        "# Extract condominium fees and fill with '0' if value is empty\n",
        "df_ads['f_spese condominio'] = df_ads['f_spese condominio'].str.extract(r'(\\d+)')\n",
        "df_ads['f_spese condominio'] = df_ads['f_spese condominio'].fillna(0)\n",
        "df_ads['f_spese condominio'] = df_ads['f_spese condominio'].astype(int)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iGM9dY-7nRkN"
      },
      "source": [
        "# Extract floors number and remove rows with it grater than 44\n",
        "df_ads['f_totale piani edificio'] = df_ads['f_totale piani edificio'].str.extract(r'(\\d+)')\n",
        "df_ads = df_ads[df_ads['f_totale piani edificio'].notna()]\n",
        "df_ads['f_totale piani edificio'] = df_ads['f_totale piani edificio'].astype(int)\n",
        "df_ads = df_ads[(df_ads['f_totale piani edificio'] < 44)]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MQH17cxRnTFM"
      },
      "source": [
        "df_ads = df_ads[df_ads['f_anno di costruzione'].notna()]\n",
        "df_ads['f_anno di costruzione'] = df_ads['f_anno di costruzione'].astype(float).astype(int)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8T4xYvBGnUMu"
      },
      "source": [
        "# Split propriety features to convert them as a list\n",
        "df_ads['f_altre caratteristiche'] = df_ads['f_altre caratteristiche'].str.split(',')\n",
        "df_ads['f_altre caratteristiche'] = df_ads['f_altre caratteristiche'].fillna('')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9GwWjwI6nVYu"
      },
      "source": [
        "# Extract energy efficiency type\n",
        "df_ads = df_ads[df_ads['f_Efficienza energetica'].notna()]\n",
        "df_ads['f_Efficienza energetica tipo'] = df_ads['f_Efficienza energetica'].str.extract(r\"(^[A-Z][\\+]?[\\d]?)\", flags = re.IGNORECASE)\n",
        "df_ads['f_Efficienza energetica tipo'] = df_ads['f_Efficienza energetica tipo'].str.upper()\n",
        "df_ads['f_Efficienza energetica tipo'] = df_ads['f_Efficienza energetica tipo'].str.strip()\n",
        "df_ads = df_ads[df_ads['f_Efficienza energetica tipo'].notna()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DTMMOtYBnWwt"
      },
      "source": [
        "# Extract energy efficiency value\n",
        "df_ads['f_Efficienza energetica valore'] = df_ads['f_Efficienza energetica'].str.extract(r\"(\\d+[,]?\\d+)\", flags = re.IGNORECASE)\n",
        "df_ads['f_Efficienza energetica valore'] = df_ads['f_Efficienza energetica valore'].replace('[\\,]', '.', regex=True)\n",
        "df_ads['f_Efficienza energetica valore'] = df_ads['f_Efficienza energetica valore'].str.strip().astype(float)\n",
        "df_ads = df_ads[df_ads['f_Efficienza energetica valore'].notna()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bUKEoJzDnX-P"
      },
      "source": [
        "# Extract propriety address from title through a complex regex with all italian street denominations like bridge, highway, square, etc. and concat with ', milano' string \n",
        "regex_address = \"((?:alzaia|arco|autostrada|belvedere|calata|calle|cavalcavia|circonvallazione|corso|corte|cortile|discesa|foro|galleria|gradinata|larghetto|largo|litoranea|lungargine|lungofiume|lungolago|lungomare|lungoparco|lungotorrente|molo|parcheggio|passaggio|passeggiata|percorso ciclabile|percorso ciclopedonale|percorso pedonale|piazza|piazzale|piazzetta|pista ciclabile|ponte|raccordo|rampa|ripa|ronco|rotatoria|rotonda|salita|scalinata|scesa|sentiero|slargo|sottopasso|sovrappasso|spiazzo|strada|strada antica|strada comunale|strada consortile|strada nuova|strada panoramica|strada poderale|strada privata|strada provinciale|strada regionale|strada statale|strada vecchia|strada vicinale|stradella|stradello|stradone|tangenziale|traversa|traversa privata|via|via antica|via comunale|via nazionale|via nuova|via panoramica|via privata|via provinciale|via vecchia|viale|vialetto|vico|vico chiuso|vico cieco|vico privato|vicoletto|vicolo|vicolo chiuso|vicolo cieco|vicolo privato|viottolo)\\s+[\\d]*[\\u00c4-\\u00e4\\u00d6-\\u00f6-\\u00dc-\\u00fc-\\u00dfa-zA-Z-'\\s\\.]*[,\\s]*[\\d]+[\\w-]*)\"\n",
        "df_ads['indirizzo_2'] = df_ads['titolo'].str.extract(regex_address, flags = re.IGNORECASE)\n",
        "df_ads['indirizzo_2'] = df_ads['indirizzo_2'] + ', milano'\n",
        "df_ads['indirizzo_2'] = df_ads['indirizzo_2'].str.lower().str.strip()\n",
        "df_ads['indirizzo_2'] = df_ads['indirizzo_2'].fillna('')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Q_GBSHmPnZXG"
      },
      "source": [
        "# Extract number of paking spaces and split in two columns based on their type like internal/external\n",
        "df_ads['g_garage/box'] = df_ads['f_Posti Auto'].str.extract(r\"(\\d\\sin garage\\/box)\", flags = re.IGNORECASE)\n",
        "df_ads['e_all\\'esterno'] = df_ads['f_Posti Auto'].str.extract(r\"(\\d\\sall'esterno)\", flags = re.IGNORECASE)\n",
        "df_ads['g_garage/box'] = df_ads['g_garage/box'].str.extract(r'(\\d+)')\n",
        "df_ads['e_all\\'esterno'] = df_ads['e_all\\'esterno'].str.extract(r'(\\d+)')\n",
        "df_ads['g_garage/box'] = df_ads['g_garage/box'].fillna(0)\n",
        "df_ads['e_all\\'esterno'] = df_ads['e_all\\'esterno'].fillna(0)\n",
        "df_ads['g_garage/box'] = df_ads['g_garage/box'].astype(int)\n",
        "df_ads['e_all\\'esterno'] = df_ads['e_all\\'esterno'].astype(int)\n",
        "df_ads['c_garage number'] = df_ads['g_garage/box'] + df_ads['e_all\\'esterno']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ljQt9pLgncPE"
      },
      "source": [
        "# Set to '1' if there is a lift '0' otherwise\n",
        "df_ads['f_ascensore'] = df_ads['f_piano'].apply(lambda x: '1' if (pd.notna(x) and 'con ascensore' in x) else '0')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4vEqiviZndpm"
      },
      "source": [
        "# Set to '1' if there is an access for disabled people '0' otherwise\n",
        "df_ads['f_disabili'] = df_ads['f_piano'].apply(lambda x: '1' if (pd.notna(x) and 'con accesso disabili' in x) else '0')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mBJuprp3nfGe"
      },
      "source": [
        "# Extract air conditioning system like autonomous, centralised, etc.\n",
        "df_ads['c_Climatizzazione impianto'] = df_ads['f_Climatizzazione'].str.extract(r\"(Autonomo|Centralizzato|Predisposizione impianto)\", flags = re.IGNORECASE)\n",
        "df_ads['c_Climatizzazione impianto'] = df_ads['c_Climatizzazione impianto'].str.lower().str.strip().fillna('')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rtFd_yh4ngLv"
      },
      "source": [
        "# Extract air conditioning type like cold, hot, cold/hot\n",
        "df_ads['c_Climatizzazione tipo'] = df_ads['f_Climatizzazione'].str.extract(r\"(freddo/caldo|freddo|caldo)\", flags = re.IGNORECASE)\n",
        "df_ads['c_Climatizzazione tipo'] = df_ads['c_Climatizzazione tipo'].str.lower().str.strip().fillna('')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eJBmkks-nhmb"
      },
      "source": [
        "# Extract heating system like autonomous or centralised\n",
        "df_ads['c_riscaldamento impianto'] = df_ads['f_riscaldamento'].str.extract(r\"(Centralizzato|Autonomo)\", flags = re.IGNORECASE)\n",
        "df_ads['c_riscaldamento impianto'] = df_ads['c_riscaldamento impianto'].str.lower().str.strip().fillna('')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zHDeI0eJni6t"
      },
      "source": [
        "# Extract heating type like air, stove, etc.\n",
        "df_ads['c_riscaldamento tipo'] = df_ads['f_riscaldamento'].str.extract(r\"(a pavimento|a radiatori|ad aria|a stufa)\", flags = re.IGNORECASE)\n",
        "df_ads['c_riscaldamento tipo'] = df_ads['c_riscaldamento tipo'].str.lower().str.strip().fillna('')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vXp4dJcrnkPn"
      },
      "source": [
        "# Extract heating supply like gasoline, gas, pellet, etc.\n",
        "df_ads['c_riscaldamento alimentazione'] = df_ads['f_riscaldamento'].str.extract(r\"(alimentato a metano|alimentato a gasolio|alimentato a gas|alimentato a pompa di calore|alimentato a gpl|alimentato a teleriscaldamento|alimentazione elettrica|alimentato a fotovoltaico|alimentato a solare|alimentato a pellet)\", flags = re.IGNORECASE)\n",
        "df_ads['c_riscaldamento alimentazione'] = df_ads['c_riscaldamento alimentazione'].str.lower().str.strip().fillna('')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dyLq3x08nmUd"
      },
      "source": [
        "# Convert propriety feature list in one-hot encoding columns with string 'c_' as prefixer\n",
        "mlb = MultiLabelBinarizer()\n",
        "df_ads = df_ads.join(\n",
        "  pd.DataFrame(mlb.fit_transform(df_ads.pop('f_altre caratteristiche')),\n",
        "  columns=mlb.classes_,\n",
        "  index=df_ads.index).add_prefix('c_'))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sEZQul7anpPk"
      },
      "source": [
        "# Convert features columns in string type\n",
        "feature_columns = [\n",
        "  'c_Armadio a muro',\n",
        "  'c_Arredato',\n",
        "  'c_Balcone',\n",
        "  'c_Caminetto',\n",
        "  'c_Cancello elettrico',\n",
        "  'c_Cantina',\n",
        "  'c_Esposizione doppia',\n",
        "  'c_Esposizione esterna',\n",
        "  'c_Esposizione interna',\n",
        "  'c_Fibra ottica',\n",
        "  'c_Giardino comune',\n",
        "  'c_Giardino privato',\n",
        "  'c_Idromassaggio',\n",
        "  'c_Impianto di allarme',\n",
        "  'c_Impianto tv centralizzato',\n",
        "  'c_Impianto tv con parabola satellitare',\n",
        "  'c_Impianto tv singolo',\n",
        "  'c_Infissi esterni in doppio vetro / PVC',\n",
        "  'c_Infissi esterni in doppio vetro / legno',\n",
        "  'c_Infissi esterni in doppio vetro / metallo',\n",
        "  'c_Infissi esterni in triplo vetro / PVC',\n",
        "  'c_Infissi esterni in triplo vetro / legno',\n",
        "  'c_Infissi esterni in triplo vetro / metallo',\n",
        "  'c_Infissi esterni in vetro / PVC',\n",
        "  'c_Infissi esterni in vetro / legno',\n",
        "  'c_Infissi esterni in vetro / metallo',\n",
        "  'c_Mansarda',\n",
        "  'c_Parzialmente Arredato',\n",
        "  'c_Piscina',\n",
        "  'c_Porta blindata',\n",
        "  'c_Portiere intera giornata',\n",
        "  'c_Portiere mezza giornata',\n",
        "  'c_Reception',\n",
        "  'c_Solo Cucina Arredata',\n",
        "  'c_Taverna',\n",
        "  'c_Terrazza',\n",
        "  'c_VideoCitofono'\n",
        "]\n",
        "\n",
        "df_ads[feature_columns] = df_ads[feature_columns].astype(str)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3w_PoCC_gs9t"
      },
      "source": [
        "# Remove columns not longer useful\n",
        "df_ads = df_ads.drop('titolo', axis=1)\n",
        "df_ads = df_ads.drop('indirizzo', axis=1)\n",
        "df_ads = df_ads.drop('f_locali', axis=1)\n",
        "df_ads = df_ads.drop('f_piano', axis=1)\n",
        "df_ads = df_ads.drop('f_Tipo proprietà', axis=1)\n",
        "df_ads = df_ads.drop('f_tipologia', axis=1)\n",
        "df_ads = df_ads.drop('f_stato', axis=1)\n",
        "df_ads = df_ads.drop('f_Climatizzazione', axis=1)\n",
        "df_ads = df_ads.drop('f_riscaldamento', axis=1)\n",
        "df_ads = df_ads.drop('f_Posti Auto', axis=1)\n",
        "df_ads = df_ads.drop('f_Efficienza energetica', axis=1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tydS4Hg5dQ5_"
      },
      "source": [
        "display(df_ads.info())\n",
        "display(df_ads.describe().transpose())\n",
        "display(df_ads.head(10).T)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5gAH6CfdaQ9G"
      },
      "source": [
        "csv_ads_clean = 'ads_clean_' + get_timestamp() + '.csv'\n",
        "df_ads.to_csv(csv_ads_clean)\n",
        "files.download(csv_ads_clean)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9_fW-Wbk0HRi"
      },
      "source": [
        "## Data Enrichment\n",
        "\n",
        "* **Data Enrichment** is the process to include third-party data from external sources in an existent databases with new few features to enchance quality for modelling, describing and predicting.\n",
        "\n",
        "* In this section you'll use **Geocoder** library to convert real estate addresses to geographical coordinates. With these you can calculate then distance between houses and top attractions. \n",
        "\n",
        "* In that case we only consider **Duomo** of top attractions placed in coordinates 45°27'50.8\"N 9°11'30.8\"E, that is in the center of city because Milan is a concentric city so it could be enough for our analysis."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0G3lJkVdG6H7"
      },
      "source": [
        "### Geocoder"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "U3VmPckNxCXo"
      },
      "source": [
        "import math\n",
        "\n",
        "from geopy import distance\n",
        "from geopy.geocoders import Nominatim\n",
        "from geopy.extra.rate_limiter import RateLimiter"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "60rJhPVkuDws"
      },
      "source": [
        "files.upload()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vBgOVZbxuDw2"
      },
      "source": [
        "# Import ads clean CSV file\n",
        "\n",
        "csv_ads_clean = 'ads_clean_1619166011.csv'\n",
        "df_ads_geo = pd.read_csv(csv_ads_clean)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Uss1PKBJWksz"
      },
      "source": [
        "geolocator = Nominatim(user_agent='myGeocoder')\n",
        "geocode = RateLimiter(geolocator.geocode, min_delay_seconds=1)\n",
        "\n",
        "tqdm.pandas()\n",
        "\n",
        "df_ads_geo = df_ads.copy()\n",
        "\n",
        "df_ads_geo['geocode_address'] = df_ads_geo['indirizzo_2'].progress_apply(geocode)\n",
        "df_ads_geo['geocode_point'] = df_ads_geo['geocode_address'].apply(lambda loc: tuple(loc.point) if loc else None)\n",
        "df_ads_geo[['latitude', 'longitude', 'altitude']] = pd.DataFrame(df_ads_geo['geocode_point'].tolist(), index=df_ads_geo.index)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AHqoyb2UZgdI"
      },
      "source": [
        "# Remove useless column about altitude because all are equal to 0\n",
        "df_ads_geo.drop('altitude', axis=1, inplace=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "omI48zUwbJMg"
      },
      "source": [
        "print(len(df_ads_geo.index))\n",
        "print(df_ads_geo['geocode_point'].isna().sum())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uuNg__5CecgV"
      },
      "source": [
        "# Remove rows where geocode_point is NaN\n",
        "df_ads_geo = df_ads_geo[df_ads_geo['geocode_point'].notna()]\n",
        "df_ads_geo = df_ads_geo[df_ads_geo['latitude'].notna()]\n",
        "df_ads_geo = df_ads_geo[df_ads_geo['longitude'].notna()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-_xQZdUh5rkr"
      },
      "source": [
        "# The following coordinates are Milan city center borders in all directions\n",
        "\n",
        "milan_n = 45.5390\n",
        "milan_e = 9.2927\n",
        "milan_s = 45.3762\n",
        "milan_w = 9.0651\n",
        "\n",
        "# Set to NaN real estate propriety outside borders\n",
        "df_ads_geo.loc[(df_ads_geo['latitude'] < milan_s) | (df_ads_geo['latitude'] > milan_n) | (df_ads_geo['longitude'] < milan_w) | (df_ads_geo['longitude'] > milan_e), ['latitude', 'longitude']] = np.nan"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "b0ifxAoowwDR"
      },
      "source": [
        "# Geographical coordinates of \"Duomo di Milano\" in center of Milan city.\n",
        "DUOMO_DI_MILANO = (45.4641, 9.1919)\n",
        "\n",
        "def calc_distance(from_loc, to_lat, to_long):\n",
        "  '''Method that calculates and returns distance between two places in km'''\n",
        "\n",
        "  if not math.isnan(to_lat) and not math.isnan(to_long):\n",
        "    return distance.distance(from_loc, (to_lat, to_long)).km\n",
        "  else:\n",
        "    pass\n",
        "\n",
        "def get_distance(df, column_name, from_location):\n",
        "  '''Method that returns distance in km between two places\n",
        "  \n",
        "  df: DataFrame of Pandas as input\n",
        "  column_name: name of the new column of distance calculated\n",
        "  from_location: tuple in the form (X, Y) where X and Y are geographical coordinates of the attraction\n",
        "  \n",
        "  '''\n",
        "\n",
        "  df[column_name] = df.apply(lambda row: calc_distance(from_location, row.latitude, row.longitude), axis=1)\n",
        "  return df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7-FsJWaffHmh"
      },
      "source": [
        "df_ads_geo = get_distance(df_ads_geo, 'distance_duomo', DUOMO_DI_MILANO)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x_V0c6knfQwk"
      },
      "source": [
        "print(df_ads_geo['distance_duomo'].isna().sum())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5D_yrM8qY5An"
      },
      "source": [
        "df_ads_geo.info()\n",
        "display(df_ads_geo.describe().transpose())\n",
        "display(df_ads_geo.head(5).T)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "n6bIOZNMY5Ay"
      },
      "source": [
        "csv_geo = 'ads_geocode_' + get_timestamp() + '.csv'\n",
        "df_ads_geo.to_csv(csv_geo)\n",
        "files.download(csv_geo)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pkpwkeWLvOFd"
      },
      "source": [
        "## Exploratory Data Analysis (EDA)\n",
        "\n",
        "* In statistics, **Exploratory Data Analysis (EDA)** is an approach of analyzing data sets to summarize their main characteristics, often using statistical graphics and other data visualization methods.\n",
        "\n",
        "* Use [Dython](http://shakedzy.xyz/dython/) library to visualize **Pearson correlation matrix** between all real estate features: if a cell is nearly to 0 the two features are not correlated; instead, if it is near to 1 or -1 they're very high correlated. <br>\n",
        "Price will be our main column to focus attention: as you can see, it is very correlated with surface area of house, number of bathroom, number of rooms, district, distance from center of city but this least negatively. <br>\n",
        "Then we filter features with absolute correlation value grater that 0.1 to exclude useless features.\n",
        "\n",
        "* "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UfZ6tyatFGYf"
      },
      "source": [
        "!pip install dython\n",
        "\n",
        "import dython\n",
        "from dython.nominal import correlation_ratio\n",
        "from dython.nominal import associations\n",
        "\n",
        "import folium\n",
        "from folium.plugins import HeatMap"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ToriAx-cW3Sk"
      },
      "source": [
        "files.upload()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bB3Q4k5tW3Sm"
      },
      "source": [
        "csv_eda = 'ads_geocode_1620485970.csv'\n",
        "df_eda = pd.read_csv(csv_eda)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dktaBa2IW3So"
      },
      "source": [
        "display(df_eda.info())\n",
        "display(df_eda.describe().transpose())\n",
        "display(df_eda.head(5).T)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "88EQMeAVJ9MY"
      },
      "source": [
        "# Filter columns\n",
        "\n",
        "columns_filtered = [\n",
        "  #'id',\n",
        "  'prezzo',\n",
        "  'quartiere',\n",
        "  'superficie',\n",
        "  'bagni',\n",
        "  #'piano',\n",
        "  'locali',\n",
        "  'f_totale piani edificio',\n",
        "  'f_spese condominio',\n",
        "  'f_anno di costruzione',\n",
        "  'c_Tipo proprietà',\n",
        "  'c_Classe proprietà',\n",
        "  'c_stato',\n",
        "  'c_camere da letto',\n",
        "  'c_altri locali',\n",
        "  'c_numero totale locali',\n",
        "  'c_tipo cucina',\n",
        "  'c_campo da tennis',\n",
        "  'f_Efficienza energetica tipo',\n",
        "  'f_Efficienza energetica valore',\n",
        "  #'indirizzo_2',\n",
        "  'g_garage/box',\n",
        "  \"e_all'esterno\",\n",
        "  'c_garage number',\n",
        "  'f_ascensore',\n",
        "  'f_disabili',\n",
        "  'c_Climatizzazione impianto',\n",
        "  'c_Climatizzazione tipo',\n",
        "  'c_riscaldamento impianto',\n",
        "  'c_riscaldamento tipo',\n",
        "  'c_riscaldamento alimentazione',\n",
        "  'c_Armadio a muro',\n",
        "  'c_Arredato',\n",
        "  'c_Balcone',\n",
        "  'c_Caminetto',\n",
        "  'c_Cancello elettrico',\n",
        "  'c_Cantina',\n",
        "  'c_Esposizione doppia',\n",
        "  'c_Esposizione esterna',\n",
        "  'c_Esposizione interna',\n",
        "  'c_Fibra ottica',\n",
        "  'c_Giardino comune',\n",
        "  'c_Giardino privato',\n",
        "  'c_Idromassaggio',\n",
        "  'c_Impianto di allarme',\n",
        "  'c_Impianto tv centralizzato',\n",
        "  'c_Impianto tv con parabola satellitare',\n",
        "  'c_Impianto tv singolo',\n",
        "  'c_Infissi esterni in doppio vetro / PVC',\n",
        "  'c_Infissi esterni in doppio vetro / legno',\n",
        "  'c_Infissi esterni in doppio vetro / metallo',\n",
        "  'c_Infissi esterni in triplo vetro / PVC',\n",
        "  'c_Infissi esterni in triplo vetro / legno',\n",
        "  'c_Infissi esterni in triplo vetro / metallo',\n",
        "  'c_Infissi esterni in vetro / PVC',\n",
        "  'c_Infissi esterni in vetro / legno',\n",
        "  'c_Infissi esterni in vetro / metallo',\n",
        "  'c_Mansarda',\n",
        "  'c_Parzialmente Arredato',\n",
        "  'c_Piscina',\n",
        "  'c_Porta blindata',\n",
        "  'c_Portiere intera giornata',\n",
        "  'c_Portiere mezza giornata',\n",
        "  'c_Reception',\n",
        "  'c_Solo Cucina Arredata',\n",
        "  'c_Taverna',\n",
        "  'c_Terrazza',\n",
        "  'c_VideoCitofono',\n",
        "  #'geocode_address',\n",
        "  #'geocode_point',\n",
        "  'latitude',\n",
        "  'longitude',\n",
        "  #'distance_duomo'\n",
        "]\n",
        "\n",
        "columns_geo = [\n",
        "  'distance_duomo'\n",
        "]\n",
        "\n",
        "# Merge columns to keep\n",
        "columns_filtered = columns_filtered + columns_geo \n",
        "\n",
        "# Nominal columns\n",
        "columns_nominal = [\n",
        "  #'id',\n",
        "  'quartiere',\n",
        "  #'prezzo',\n",
        "  #'superficie',\n",
        "  'bagni',\n",
        "  #'piano',\n",
        "  'locali',\n",
        "  #'f_totale piani edificio',\n",
        "  #'f_spese condominio',\n",
        "  #'f_anno di costruzione',\n",
        "  'c_Tipo proprietà',\n",
        "  'c_Classe proprietà',\n",
        "  'c_stato',\n",
        "  #'c_camere da letto',\n",
        "  #'c_altri locali',\n",
        "  #'c_numero totale locali',\n",
        "  'c_tipo cucina',\n",
        "  'c_campo da tennis',\n",
        "  'f_Efficienza energetica tipo',\n",
        "  #'f_Efficienza energetica valore',\n",
        "  #'indirizzo_2',\n",
        "  #'g_garage/box',\n",
        "  #\"e_all'esterno\",\n",
        "  #'c_garage number',\n",
        "  'f_ascensore',\n",
        "  'f_disabili',\n",
        "  'c_Climatizzazione impianto',\n",
        "  'c_Climatizzazione tipo',\n",
        "  'c_riscaldamento impianto',\n",
        "  'c_riscaldamento tipo',\n",
        "  'c_riscaldamento alimentazione',\n",
        "  'c_Armadio a muro',\n",
        "  'c_Arredato',\n",
        "  'c_Balcone',\n",
        "  'c_Caminetto',\n",
        "  'c_Cancello elettrico',\n",
        "  'c_Cantina',\n",
        "  'c_Esposizione doppia',\n",
        "  'c_Esposizione esterna',\n",
        "  'c_Esposizione interna',\n",
        "  'c_Fibra ottica',\n",
        "  'c_Giardino comune',\n",
        "  'c_Giardino privato',\n",
        "  'c_Idromassaggio',\n",
        "  'c_Impianto di allarme',\n",
        "  'c_Impianto tv centralizzato',\n",
        "  'c_Impianto tv con parabola satellitare',\n",
        "  'c_Impianto tv singolo',\n",
        "  'c_Infissi esterni in doppio vetro / PVC',\n",
        "  'c_Infissi esterni in doppio vetro / legno',\n",
        "  'c_Infissi esterni in doppio vetro / metallo',\n",
        "  'c_Infissi esterni in triplo vetro / PVC',\n",
        "  'c_Infissi esterni in triplo vetro / legno',\n",
        "  'c_Infissi esterni in triplo vetro / metallo',\n",
        "  'c_Infissi esterni in vetro / PVC',\n",
        "  'c_Infissi esterni in vetro / legno',\n",
        "  'c_Infissi esterni in vetro / metallo',\n",
        "  'c_Mansarda',\n",
        "  'c_Parzialmente Arredato',\n",
        "  'c_Piscina',\n",
        "  'c_Porta blindata',\n",
        "  'c_Portiere intera giornata',\n",
        "  'c_Portiere mezza giornata',\n",
        "  'c_Reception',\n",
        "  'c_Solo Cucina Arredata',\n",
        "  'c_Taverna',\n",
        "  'c_Terrazza',\n",
        "  'c_VideoCitofono',\n",
        "  #'geocode_address',\n",
        "  #'geocode_point',\n",
        "  #'latitude',\n",
        "  #'longitude',\n",
        "  #'distance_duomo'\n",
        "]\n",
        "\n",
        "df_eda = df_eda[columns_filtered]\n",
        "\n",
        "associations_dictionary = associations(df_eda, nan_strategy='replace', nan_replace_value='', nominal_columns=columns_nominal, figsize=(50, 50), cmap='seismic', mark_columns=False)\n",
        "\n",
        "associations_corr = associations_dictionary['corr']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CaTzDnoviJNU"
      },
      "source": [
        "# Select columns to drop because lower than correlation threshold\n",
        "\n",
        "corr_threshold = 0.1\n",
        "column_to_drop = associations_corr.columns[abs(associations_corr['prezzo']) > corr_threshold].tolist()\n",
        "column_to_drop"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ecH_2IaUJLp1"
      },
      "source": [
        "# Histograms\n",
        "\n",
        "df_eda.hist(figsize = (50, 50))\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8LC1Jq5zSvDw"
      },
      "source": [
        "# Scatterplots\n",
        "\n",
        "fig = plt.figure(figsize=(25, 15))\n",
        "\n",
        "fig.add_subplot(2, 2, 1)\n",
        "locali_order = ['5+', '5', '4', '3', '2', '1']\n",
        "sb.scatterplot(x='superficie', y='prezzo', data=df_eda, hue=\"locali\", hue_order=locali_order, palette='hot')\n",
        "\n",
        "fig.add_subplot(2, 2, 2)\n",
        "eet_order = ['A4', 'A3', 'A2', 'A1', 'A+', 'A', 'B', 'C', 'D', 'E', 'F', 'G']\n",
        "sb.scatterplot(x='f_spese condominio', y='prezzo', data=df_eda, hue='f_Efficienza energetica tipo', hue_order=eet_order, palette='copper')\n",
        "\n",
        "fig.add_subplot(2, 2, 3)\n",
        "eet_order = ['A4', 'A3', 'A2', 'A1', 'A+', 'A', 'B', 'C', 'D', 'E', 'F', 'G']\n",
        "sb.scatterplot(x='f_anno di costruzione', y='prezzo', data=df_eda, hue='f_Efficienza energetica tipo', hue_order=eet_order, palette='copper')\n",
        "\n",
        "fig.add_subplot(2, 2, 4)\n",
        "bagni_order = ['3+', '3', '2', '1', '0']\n",
        "sb.scatterplot(x='c_numero totale locali', y='prezzo', data=df_eda, hue='bagni', hue_order=bagni_order, palette='copper')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ph1wPtLpFe0J"
      },
      "source": [
        "# Histogram of prices\n",
        "\n",
        "fig_dims = (25, 10)\n",
        "fig, ax = plt.subplots(figsize=fig_dims)\n",
        "\n",
        "sb.histplot(df_eda['prezzo'], ax=ax, binwidth=10000, kde=True)\n",
        "\n",
        "plt.title('Prezzo')\n",
        "\n",
        "plt.xlabel('Prezzo')\n",
        "plt.ylabel('Frequency')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2JW6B6ccB9Jg"
      },
      "source": [
        "# Boxplot of prices\n",
        "\n",
        "fig_dims = (25, 10)\n",
        "fig, ax = plt.subplots(figsize=fig_dims)\n",
        "\n",
        "sb.boxplot(x=df_eda['prezzo'], ax=ax)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CijWi2Pj8M7-"
      },
      "source": [
        "# Show real estate in a heatmap based on their geographical coordinates and weighted on their price\n",
        "\n",
        "def generateBaseMap(default_location=[45.4641, 9.1919], default_zoom_start=12):\n",
        "  base_map = folium.Map(location=default_location, control_scale=True, zoom_start=default_zoom_start)\n",
        "  return base_map\n",
        "\n",
        "base_map = generateBaseMap()\n",
        "HeatMap(data=df_eda[['latitude', 'longitude', 'prezzo']].groupby(['latitude', 'longitude']).sum().reset_index().values.tolist(), radius=17, max_zoom=5, max_val=df_eda['prezzo'].max()).add_to(base_map)\n",
        "base_map"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "z38bhe1xKDwO"
      },
      "source": [
        "## Artificial Neural Network"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Q7bzRGJiKONV"
      },
      "source": [
        "from tensorflow import keras\n",
        "from keras.models import Sequential\n",
        "from keras.layers import Dense, Activation, InputLayer, BatchNormalization, Dropout\n",
        "from keras.models import load_model\n",
        "from keras.callbacks import EarlyStopping\n",
        "from keras.callbacks import LearningRateScheduler\n",
        "\n",
        "from sklearn.model_selection import train_test_split\n",
        "from sklearn.compose import ColumnTransformer\n",
        "from sklearn.preprocessing import StandardScaler\n",
        "from sklearn import metrics\n",
        "from sklearn import feature_selection"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oqEId5Nja8Wf"
      },
      "source": [
        "### One-Hot Encoding"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sAowODQYazE6"
      },
      "source": [
        "files.upload()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DC27mfDQciCK"
      },
      "source": [
        "csv_ads_hot = 'ads_geocode_1620485970.csv'\n",
        "df_ads_hot = pd.read_csv(csv_ads_hot)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RONt23TB_vW7"
      },
      "source": [
        "columns_filtered = [\n",
        "  'prezzo',\n",
        "  'quartiere',\n",
        "  'superficie',\n",
        "  'bagni',\n",
        "  'locali',\n",
        "  'f_totale piani edificio',\n",
        "  'f_spese condominio',\n",
        "  'c_Classe proprietà',\n",
        "  'c_stato',\n",
        "  'c_camere da letto',\n",
        "  'c_altri locali',\n",
        "  'c_numero totale locali',\n",
        "  'c_tipo cucina',\n",
        "  'f_Efficienza energetica tipo',\n",
        "  'g_garage/box',\n",
        "  \"e_all'esterno\",\n",
        "  'c_garage number',\n",
        "  'f_ascensore',\n",
        "  'c_Climatizzazione impianto',\n",
        "  'c_Climatizzazione tipo',\n",
        "  'c_riscaldamento tipo',\n",
        "  'c_riscaldamento alimentazione',\n",
        "  'c_Armadio a muro',\n",
        "  'c_Caminetto',\n",
        "  'c_Esposizione doppia',\n",
        "  'c_Esposizione interna',\n",
        "  'c_Idromassaggio',\n",
        "  'c_Impianto di allarme',\n",
        "  'c_Infissi esterni in doppio vetro / PVC',\n",
        "  'c_Infissi esterni in doppio vetro / legno',\n",
        "  'c_Infissi esterni in triplo vetro / metallo',\n",
        "  'c_Piscina',\n",
        "  'c_Porta blindata',\n",
        "  'c_Portiere intera giornata',\n",
        "  'c_Terrazza',\n",
        "  'c_VideoCitofono',\n",
        "  'distance_duomo'\n",
        "]\n",
        "\n",
        "df_ads_hot = df_ads_hot.filter(items=columns_filtered)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-3SJ8SePgnNe"
      },
      "source": [
        "list(df_ads_hot.columns)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vGI1TySZKxa5"
      },
      "source": [
        "dummies_dict = {\n",
        "  ('quartiere', 'q'),\n",
        "  ('bagni', 'b'),\n",
        "  ('locali', 'l'),\n",
        "  ('c_Classe proprietà', 'cp'),\n",
        "  ('c_stato', 's'),\n",
        "  ('c_tipo cucina', 'tc'),\n",
        "  ('f_Efficienza energetica tipo', 'eet'),\n",
        "  ('c_Climatizzazione impianto', 'ci'),\n",
        "  ('c_Climatizzazione tipo', 'ct'),\n",
        "  ('c_riscaldamento tipo', 'rt'),\n",
        "  ('c_riscaldamento alimentazione', 'ra')\n",
        "}\n",
        "\n",
        "def dummies(df, dummies_dict):\n",
        "  for column in dummies_dict:\n",
        "    df = pd.concat([df, pd.get_dummies(df[column[0]], prefix=column[1])], axis=1)\n",
        "    df.drop([column[0]], axis=1, inplace=True)\n",
        "  return df\n",
        "\n",
        "df_ads_hot = dummies(df_ads_hot, dummies_dict)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wmWVvIM6t_P5"
      },
      "source": [
        "print(df_ads_hot.info())\n",
        "display(df_ads_hot.describe().transpose())\n",
        "display(df_ads_hot.head(10).T)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lcf_Qmtvd2v_"
      },
      "source": [
        "csv_ads_hot = 'ads_hot_' + get_timestamp() + '.csv'\n",
        "df_ads_hot.to_csv(csv_ads_hot, index=False)\n",
        "files.download(csv_ads_hot)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7sFiJjLRbC5_"
      },
      "source": [
        "### Configuration"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Sz9IaKQfnhJo"
      },
      "source": [
        "files.upload()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PosLc-Qsg7na"
      },
      "source": [
        "csv_ads_hot = 'ads_hot_1620489950.csv'\n",
        "df_ads_hot = pd.read_csv(csv_ads_hot)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6xhfID4A6Ig7"
      },
      "source": [
        "# Convert all data to numeric types\n",
        "df_ads_hot = df_ads_hot.apply(pd.to_numeric)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "t833wdFaWrPV"
      },
      "source": [
        "print(df_ads_hot.info())\n",
        "display(df_ads_hot.describe().transpose())\n",
        "display(df_ads_hot.head(10).T)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pi5kWKUD-DDN"
      },
      "source": [
        "# Optional: filter further data\n",
        "\n",
        "print(len(df_ads_hot.index))\n",
        "\n",
        "df_ads_hot = df_ads_hot[df_ads_hot['cp_classe immobile signorile'] == 1]\n",
        "df_ads_hot = df_ads_hot[df_ads_hot['superficie'] < 200]\n",
        "df_ads_hot = df_ads_hot[df_ads_hot['b_3+'] != 1.0]\n",
        "df_ads_hot = df_ads_hot[df_ads_hot['c_numero totale locali'] < 6]\n",
        "df_ads_hot = df_ads_hot[df_ads_hot['l_5+'] != 1.0]\n",
        "\n",
        "print(len(df_ads_hot.index))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "879slw5uIB5C"
      },
      "source": [
        "# Drop all rows with at least a NaN value\n",
        "\n",
        "print(df_ads_hot.shape)\n",
        "\n",
        "df_ads_hot = df_ads_hot.dropna()\n",
        "\n",
        "print(df_ads_hot.shape)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rlrXtjhOFaXg"
      },
      "source": [
        "X = df_ads_hot.drop('prezzo', axis=1)\n",
        "y = df_ads_hot[['prezzo']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VurUEU2EllLJ"
      },
      "source": [
        "X_train, X_val_test, y_train, y_val_test = train_test_split(X, y, test_size=0.2)\n",
        "X_val, X_test, y_val, y_test = train_test_split(X_val_test, y_val_test, test_size=0.5)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vXCg5lq2WQge"
      },
      "source": [
        "# Standardize all columns\n",
        "\n",
        "# Columns with continuos values\n",
        "columns_standard = [\n",
        "  'superficie',\n",
        "  'f_totale piani edificio',\n",
        "  'f_spese condominio',\n",
        "  'c_camere da letto',\n",
        "  'c_altri locali',\n",
        "  'c_numero totale locali',\n",
        "  'g_garage/box',\n",
        "  'e_all\\'esterno',\n",
        "  'c_garage number',\n",
        "  'distance_duomo',\n",
        "]\n",
        "\n",
        "ct_data = ColumnTransformer(transformers = [('ct_data', StandardScaler(), columns_standard)], remainder ='passthrough')\n",
        "ct_target = StandardScaler()\n",
        "\n",
        "# Fit standardization ONLY on training set and then apply to validation and test sets.\n",
        "X_train_scaled = ct_data.fit_transform(X_train)\n",
        "y_train_scaled = ct_target.fit_transform(y_train)\n",
        "\n",
        "X_val_scaled = ct_data.transform(X_val)\n",
        "y_val_scaled = ct_target.transform(y_val)\n",
        "\n",
        "X_test_scaled = ct_data.transform(X_test)\n",
        "y_test_scaled = ct_target.transform(y_test)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pCSi3K9Jm58U"
      },
      "source": [
        "#### Curse of dimensionality\n",
        "\n",
        "* Dimensionality-reduction methods are used to remove features in datasets at the expense of accuracy, but reaching semplicity to make much more easier to analyze them by machine learning algorithms.\n",
        "\n",
        "* Principal Component Analysis (PCA): combine features in fewer feature and then remove useless of them."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VgKk8REIgDZJ"
      },
      "source": [
        "# Principal Component Analysis (PCA)\n",
        "\n",
        "pca = PCA(0.90)\n",
        "\n",
        "X_train_scaled = pca.fit_transform(X_train_scaled)\n",
        "X_val_scaled = pca.transform(X_val_scaled)\n",
        "X_test_scaled = pca.transform(X_test_scaled)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8DI6SBc-ZH28"
      },
      "source": [
        "# Select best K features\n",
        "\n",
        "k_best = SelectKBest(score_func=f_classif, k=100)\n",
        "\n",
        "fit = k_best.fit(X_train, y_train)\n",
        "\n",
        "univariate_features = fit.transform(X_train)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hRoB5Or4ImP9"
      },
      "source": [
        "# Variance Threshold\n",
        "\n",
        "print(X_train_scaled.shape)\n",
        "\n",
        "variance_treshold = 0.001\n",
        "variance_threshold = feature_selection.VarianceThreshold(threshold=variance_treshold)\n",
        "\n",
        "X_train_scaled = variance_threshold.fit_transform(X_train_scaled)\n",
        "X_val_scaled = variance_threshold.transform(X_val_scaled)\n",
        "X_test_scaled = variance_threshold.transform(X_test_scaled)\n",
        "\n",
        "print(X_train_scaled.shape)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I619I812klhi"
      },
      "source": [
        "### Tuning\n",
        "\n",
        "* [Keras Tuner](https://keras-team.github.io/keras-tuner/) is a open-source library that allow you to tune Hyperparameters of your Neural Network. <br>\n",
        "How many **layers** do you need? How many **neurons** per layer is needed? What type of **activation functions** fit well?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pVcz9MHLZKGD"
      },
      "source": [
        "!pip install h5py\n",
        "!pip install -U keras-tuner\n",
        "!pip install kerastuner-tensorboard-logger\n",
        "\n",
        "import tensorflow as tf\n",
        "\n",
        "import kerastuner as kt\n",
        "from kerastuner.tuners import Hyperband\n",
        "from kerastuner import HyperModel\n",
        "\n",
        "from sklearn.feature_selection import SelectKBest, f_classif\n",
        "from sklearn.decomposition import PCA\n",
        "\n",
        "from kerastuner_tensorboard_logger import (\n",
        "  TensorBoardLogger,\n",
        "  setup_tb\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9kMgUtn4uxK8"
      },
      "source": [
        "# Subclass of HyperModel\n",
        "\n",
        "class MyHyperModel(HyperModel):\n",
        "\n",
        "  def build(self, hp):\n",
        "    model = Sequential()\n",
        "    \n",
        "    # Input Layer\n",
        "    model.add(InputLayer((X_train_scaled.shape[1],)))\n",
        "    \n",
        "    # Define a random quantity of layers between min_value and max_value inclused\n",
        "    num_layers = hp.Int('num_layers', min_value=1, max_value=10, step=1)\n",
        "    for i in range(num_layers):\n",
        "\n",
        "      # If current selected layer is the first set dropout max value to 0.2, \n",
        "      # instead for all hidden layers to 0.5\n",
        "      dropout_max_value = 0.5\n",
        "      if i == 0:\n",
        "        dropout_max_value = 0.2\n",
        "\n",
        "      # Dense Layer\n",
        "      model.add(\n",
        "        Dense(\n",
        "            units=hp.Int(\n",
        "              'units_' + str(i + 1),\n",
        "              min_value=8,\n",
        "              max_value=256,\n",
        "              default=64,\n",
        "              step=8),\n",
        "              kernel_initializer='normal'\n",
        "        )\n",
        "      )\n",
        "\n",
        "      # Batch Normalization\n",
        "      model.add(BatchNormalization())\n",
        "      \n",
        "      # Activation function\n",
        "      model.add(Activation(hp.Choice(\n",
        "                    'activation_' + str(i + 1),\n",
        "                    values=['relu', 'tanh', 'sigmoid', 'linear'],\n",
        "                    default='relu')))\n",
        "      # Dropout\n",
        "      model.add(\n",
        "        Dropout(\n",
        "          rate=hp.Float(\n",
        "            'dropout_' + str(i + 1),\n",
        "            min_value=0,\n",
        "            max_value=dropout_max_value,\n",
        "            step=0.1,\n",
        "            default=0\n",
        "          )\n",
        "        )\n",
        "      )\n",
        "\n",
        "    # Last dense layer composed of only one neuron because it returns just a\n",
        "    # single value: the price predicted\n",
        "    model.add(Dense(1, kernel_initializer='normal', activation='linear'))\n",
        "    \n",
        "    # Adam optimizer and its learning rate range\n",
        "    adam = keras.optimizers.Adam(\n",
        "        learning_rate=hp.Float(\n",
        "            'learning_rate',\n",
        "            min_value=1e-5,\n",
        "            max_value=1e-3,\n",
        "            sampling='LOG',\n",
        "            default=1e-3)\n",
        "        )\n",
        "    \n",
        "    # Compile the model defined with optimizer and metric\n",
        "    model.compile(loss='mean_squared_error', optimizer=adam, metrics=['mean_squared_error'])\n",
        "\n",
        "    return model\n",
        "\n",
        "hypermodel = MyHyperModel()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ObL0-WaNv82F"
      },
      "source": [
        "keras_tuner_directory = 'keras_tuner_' + get_timestamp()\n",
        "keras_tuner_project_name = 'real_estate_' + get_timestamp()\n",
        "\n",
        "'''\n",
        "hyperparameters = HyperParameters()\n",
        "\n",
        "hyperparameters.Float(\n",
        "  'learning_rate',\n",
        "  min_value=1e-5,\n",
        "  max_value=1e-3,\n",
        "  sampling='LOG',\n",
        "  default=1e-3\n",
        ")\n",
        "\n",
        "hyperparameters.Int('num_layers', 2, 3)\n",
        "'''\n",
        "\n",
        "tuner = Hyperband(\n",
        "  hypermodel=hypermodel,\n",
        "  #hyperparameters=hyperparameters,\n",
        "  objective='val_loss',\n",
        "  max_epochs=100,\n",
        "  logger=TensorBoardLogger(\n",
        "    metrics=[\"val_loss\"], logdir=\"logs/hparams\"\n",
        "  ),\n",
        "  directory=keras_tuner_directory,\n",
        "  project_name=keras_tuner_project_name\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NST857ik9ZTS"
      },
      "source": [
        "tuner.search_space_summary()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AoL1VhSp1KhB"
      },
      "source": [
        "# Set Early Stopping to stop training until validation error not increse\n",
        "es = EarlyStopping(monitor='val_loss', mode='min', verbose=1, patience=25)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2kik1vI3PlP4"
      },
      "source": [
        "# Before start tuning process remove /logs folder\n",
        "!rm -rf ./logs/"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6C6hQD6a_8GK"
      },
      "source": [
        "# Starts tuning process\n",
        "\n",
        "tuner.search(\n",
        "  X_train_scaled,\n",
        "  y_train_scaled,\n",
        "  epochs=200,\n",
        "  validation_data=(X_val_scaled, y_val_scaled),\n",
        "  callbacks=[es]\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cNbZkwAd-wzv"
      },
      "source": [
        "# Get tuner summary results\n",
        "tuner.results_summary(1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bJFWyk-efPiU"
      },
      "source": [
        "# Get best Hyperparameters found\n",
        "best_hyperparameters = tuner.get_best_hyperparameters(num_trials=1)[0]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DXk2kWX9feEo"
      },
      "source": [
        "# Set model with best Hyprparameters\n",
        "model = tuner.hypermodel.build(best_hyperparameters)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zI1RYGYTcXpg"
      },
      "source": [
        "# Get best Learning Rate found\n",
        "# ATTENTION: save this manually because it is outside model architecture\n",
        "learning_rate = best_hyperparameters['learning_rate']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AuaBPnyRpUlk"
      },
      "source": [
        "# Save best model architecture found through Keras Tuner\n",
        "keras_tuner_model = 'keras_tuner_model_' + get_timestamp() + '.h5'\n",
        "model.save(keras_tuner_model)\n",
        "files.download(keras_tuner_model)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QuMqlKET1yX5"
      },
      "source": [
        "### Cross-Validation\n",
        "\n",
        "Cross-validation is a method used to enchance skill of a model.\n",
        "\n",
        "K-Fold method has a single parameter k that refers to the number of groups that a given data sample is to be split into - usually k=10.\n",
        "\n",
        "1. Shuffle the dataset randomly\n",
        "2. Split the dataset into k groups\n",
        "3. For each unique group:<br>\n",
        "  a. Take the group as a hold out or test data set<br>\n",
        "  b. Take the remaining groups as a training data set<br>\n",
        "  c. Fit a model on the training set and evaluate it on the test set<br>\n",
        "  d. Retain the evaluation score and discard the model<br>\n",
        "4. Summarize the skill of the model using the sample of model evaluation scores\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dHbIVTFLh_U0"
      },
      "source": [
        "from keras import backend\n",
        "\n",
        "import tensorflow as tf\n",
        "\n",
        "import datetime\n",
        "\n",
        "from sklearn.model_selection import KFold\n",
        "\n",
        "#!pip install h5py\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Jui8sizQiXhW"
      },
      "source": [
        "!rm -rf ./logs/"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zErpTYzqNjSR"
      },
      "source": [
        "csv_ads_hot = 'ads_hot_1620489950.csv'\n",
        "df_ads_hot = pd.read_csv(csv_ads_hot)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jDZfsge6NjSW"
      },
      "source": [
        "df_ads_hot = df_ads_hot.apply(pd.to_numeric)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RMRkMm9ANjSW"
      },
      "source": [
        "X = df_ads_hot.drop('prezzo', axis=1)\n",
        "y = df_ads_hot[['prezzo']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B0ShEHevGY1c"
      },
      "source": [
        "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VUHgBBCfjTfh"
      },
      "source": [
        "columns_standard = [\n",
        "  'superficie',\n",
        "  'f_totale piani edificio',\n",
        "  'f_spese condominio',\n",
        "  'c_camere da letto',\n",
        "  'c_altri locali',\n",
        "  'c_numero totale locali',\n",
        "  'g_garage/box',\n",
        "  'e_all\\'esterno',\n",
        "  'c_garage number',\n",
        "  'distance_duomo',\n",
        "]\n",
        "\n",
        "ct_data = ColumnTransformer(transformers = [('ct_data', StandardScaler(), columns_standard)], remainder ='passthrough')\n",
        "ct_target = StandardScaler()\n",
        "\n",
        "X_train_scaled = ct_data.fit_transform(X_train)\n",
        "y_train_scaled = ct_target.fit_transform(y_train)\n",
        "\n",
        "X_test_scaled = ct_data.transform(X_test)\n",
        "y_test_scaled = ct_target.transform(y_test)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mwhskOmPA6aB"
      },
      "source": [
        "n_splits = 10\n",
        "k_fold = KFold(n_splits=n_splits, shuffle=True)\n",
        "val_losses = []\n",
        "\n",
        "i = 0\n",
        "history = [None] * n_splits\n",
        "\n",
        "for train_indices, val_indices in k_fold.split(X):\n",
        "\n",
        "  X_train_cv, X_val_cv = X.iloc[list(train_indices)], X.iloc[list(val_indices)]\n",
        "  y_train_cv, y_val_cv = y.iloc[list(train_indices)], y.iloc[list(val_indices)]\n",
        "\n",
        "  columns_standard = [\n",
        "    'superficie',\n",
        "    'f_totale piani edificio',\n",
        "    'f_spese condominio',\n",
        "    'c_camere da letto',\n",
        "    'c_altri locali',\n",
        "    'c_numero totale locali',\n",
        "    'g_garage/box',\n",
        "    'e_all\\'esterno',\n",
        "    'c_garage number',\n",
        "    'distance_duomo',\n",
        "  ]\n",
        "\n",
        "  ct_data = ColumnTransformer(transformers = [('ct_data', StandardScaler(), columns_standard)], remainder ='passthrough')\n",
        "  ct_target = StandardScaler()\n",
        "\n",
        "  X_train_scaled_cv = ct_data.fit_transform(X_train_cv)\n",
        "  y_train_scaled_cv = ct_target.fit_transform(y_train_cv)\n",
        "\n",
        "  X_val_scaled = ct_data.transform(X_val_cv)\n",
        "  y_val_scaled = ct_target.transform(y_val_cv)\n",
        "\n",
        "  log_dir = \"logs/fit/\" + datetime.datetime.now().strftime(\"%Y%m%d-%H%M%S\")\n",
        "  tensorboard_callback = tf.keras.callbacks.TensorBoard(log_dir=log_dir, histogram_freq=1)\n",
        "\n",
        "  if 'model' in locals():\n",
        "    del model\n",
        "\n",
        "  model = load_model('keras_tuner_model_1620511303.h5')\n",
        "  model_config = model.get_config()\n",
        "  model = Sequential.from_config(model_config)\n",
        "  \n",
        "  adam = keras.optimizers.Adam()\n",
        "  model.compile(loss='mean_squared_error', optimizer=adam, metrics=['mean_squared_error'])\n",
        "\n",
        "  model_checkpoint = tf.keras.callbacks.ModelCheckpoint('cross_validation_model' + '.h5', monitor='mean_squared_error', verbose=1, save_best_only=True)\n",
        "\n",
        "  callbacks_list = [tensorboard_callback, model_checkpoint]\n",
        "  history[i] = model.fit(X_train_scaled_cv, y_train_scaled_cv, batch_size=32, epochs=100, callbacks=callbacks_list)\n",
        "\n",
        "  i += 1\n",
        "  \n",
        "  val_losses.append(model.evaluate(X_val_scaled, y_val_scaled))\n",
        "\n",
        "  backend.clear_session()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kyb9Yo_1w-gD"
      },
      "source": [
        "print('Scores from each iteration = ', val_losses)\n",
        "print('Average K-Fold Score = ' , np.mean(val_losses))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "f--w1IPDe9tk"
      },
      "source": [
        "fig = plt.figure(figsize=(25, 15))\n",
        "\n",
        "for idx, h in enumerate(history):  \n",
        "  plt.plot(h.epoch, np.array(h.history['mean_squared_error']), label='Train Loss ' + str(idx))\n",
        "\n",
        "plt.xlabel('Epochs')\n",
        "plt.ylabel('Mean Squared Error')\n",
        "plt.legend()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "G2VN4wESQj4w"
      },
      "source": [
        "### Training"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zwqPBFGmUqt6"
      },
      "source": [
        "csv_ads_hot = 'ads_hot_1620489950.csv'\n",
        "df_ads_hot = pd.read_csv(csv_ads_hot)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dHCJ7J7JUquK"
      },
      "source": [
        "df_ads_hot = df_ads_hot.apply(pd.to_numeric)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "p33hzRyWUquM"
      },
      "source": [
        "X = df_ads_hot.drop('prezzo', axis=1)\n",
        "y = df_ads_hot[['prezzo']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IcPgy8GQUquN"
      },
      "source": [
        "X_train, X_val_test, y_train, y_val_test = train_test_split(X, y, test_size=0.3)\n",
        "X_val, X_test, y_val, y_test = train_test_split(X_val_test, y_val_test, test_size=0.15)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eQbAQQ0yUquO"
      },
      "source": [
        "columns_standard = [\n",
        "  'superficie',\n",
        "  'f_totale piani edificio',\n",
        "  'f_spese condominio',\n",
        "  'c_camere da letto',\n",
        "  'c_altri locali',\n",
        "  'c_numero totale locali',\n",
        "  'g_garage/box',\n",
        "  'e_all\\'esterno',\n",
        "  'c_garage number',\n",
        "  'distance_duomo',\n",
        "]\n",
        "\n",
        "ct_data = ColumnTransformer(transformers = [('ct_data', StandardScaler(), columns_standard)], remainder ='passthrough')\n",
        "ct_target = StandardScaler()\n",
        "\n",
        "X_train_scaled = ct_data.fit_transform(X_train)\n",
        "y_train_scaled = ct_target.fit_transform(y_train)\n",
        "\n",
        "X_val_scaled = ct_data.transform(X_val)\n",
        "y_val_scaled = ct_target.transform(y_val)\n",
        "\n",
        "X_test_scaled = ct_data.transform(X_test)\n",
        "y_test_scaled = ct_target.transform(y_test)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GC7GjzDkQjLU"
      },
      "source": [
        "model = load_model('keras_tuner_model_1620511303.h5')\n",
        "model_config = model.get_config()\n",
        "model = Sequential.from_config(model_config)\n",
        "\n",
        "adam = keras.optimizers.Adam()\n",
        "model.compile(loss='mean_squared_error', optimizer=adam, metrics=['mean_squared_error'])\n",
        "\n",
        "early_stopping = EarlyStopping(monitor='val_loss', mode='min', verbose=1, patience=25)\n",
        "history = model.fit(X_train_scaled, y_train_scaled, batch_size=32, epochs=1000, validation_data=(X_val_scaled, y_val_scaled), callbacks=early_stopping)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1OH6QGfJVo0T"
      },
      "source": [
        "fig = plt.figure(figsize=(25, 15))\n",
        "\n",
        "plt.plot(history.epoch, np.array(history.history['mean_squared_error']), label='Train Loss')\n",
        "plt.plot(history.epoch, np.array(history.history['val_mean_squared_error']), label = 'Val Loss')\n",
        "\n",
        "plt.xlabel('Epochs')\n",
        "plt.ylabel('Mean Squared Error')\n",
        "plt.legend()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zf0PP-ln-650"
      },
      "source": [
        "### Evaluation"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wxao6mKMn6aS"
      },
      "source": [
        "model.evaluate(X_test_scaled, y_test_scaled)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VM-H2kDFoclV"
      },
      "source": [
        "y_pred = model.predict(X_test_scaled)\n",
        "y_pred = pd.Series(map(lambda x: x[0], y_pred))\n",
        "y_pred_inverse = ct_target.inverse_transform(y_pred)\n",
        " \n",
        "y_test_inverse = pd.Series(map(lambda x: x[0], y_test_scaled))\n",
        "y_test_inverse = ct_target.inverse_transform(y_test_inverse)\n",
        " \n",
        "df_ads_pred = pd.DataFrame({ 'Actual': y_test_inverse, 'Predicted': y_pred_inverse }).astype('int64')\n",
        "df_ads_pred['Error'] = abs(df_ads_pred['Actual'] - df_ads_pred['Predicted'])\n",
        "df_ads_pred['%'] = (df_ads_pred['Error'] * 100 / df_ads_pred['Actual'])\n",
        " \n",
        "df_ads_pred = df_ads_pred.sort_values(by=['%'])\n",
        " \n",
        "print('Error (mean) = ' + str(int(round(df_ads_pred['Error'].mean()))) + '€')\n",
        "print('Error (%) = ' + str(int(round(df_ads_pred['%'].mean()))) + '%')\n",
        "\n",
        "print('Error (mean/price) = ' + str(int(round((df_ads_pred['Error'] * df_ads_pred['Actual'].apply(lambda x: (1/x))).sum() / df_ads_pred['Actual'].apply(lambda x: (1/x)).sum()))) + '€')\n",
        "print('Error (%/price) = ' + str(int(round((df_ads_pred['%'] * df_ads_pred['Actual'].apply(lambda x: 1/x)).sum() / df_ads_pred['Actual'].apply(lambda x: 1/x).sum()))) + '%')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UhxCw_0Q_Fdj"
      },
      "source": [
        "print('MAE:', metrics.mean_absolute_error(y_test_inverse, y_pred_inverse))\n",
        "print('MSE:', metrics.mean_squared_error(y_test_inverse, y_pred_inverse))\n",
        "print('RMSE:', np.sqrt(metrics.mean_squared_error(y_test_inverse, y_pred_inverse)))\n",
        "print('VarScore:', metrics.explained_variance_score(y_test_inverse, y_pred_inverse))\n",
        " \n",
        "plt.figure(figsize=(12, 12))\n",
        "plt.scatter(y_test_inverse, y_pred_inverse)\n",
        " \n",
        "plt.plot(y_test_inverse, y_test_inverse, 'r')\n",
        " \n",
        "plt.xlabel('Actual price [€]')\n",
        "plt.ylabel('Predicted price [€]')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qb7QixXL_yeh"
      },
      "source": [
        "# Histogram of error\n",
        "\n",
        "fig_dims = (25, 10)\n",
        "fig, ax = plt.subplots(figsize=fig_dims)\n",
        " \n",
        "residuals = (y_test_inverse - y_pred_inverse)\n",
        "sns.histplot(data=residuals, binwidth=10000, ax=ax, kde=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GTklt37w5yhM"
      },
      "source": [
        "### [Tensorboard](https://www.tensorflow.org/tensorboard)\n",
        "\n",
        "Tensorboard: TensorFlow's visualization toolkit <br>\n",
        "TensorBoard provides the visualization and tooling needed for machine learning experimentation:\n",
        "* Tracking and visualizing metrics such as loss and accuracy\n",
        "* Visualizing the model graph (ops and layers)\n",
        "* Viewing histograms of weights, biases, or other tensors as they change over time\n",
        "* Projecting embeddings to a lower dimensional space\n",
        "* Displaying images, text, and audio data\n",
        "* Profiling TensorFlow programs\n",
        "\n",
        "[TensorBoard.dev](https://tensorboard.dev/) lets you easily host, track, and share your experiment results."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C8mKfTsU53Hv"
      },
      "source": [
        "# Load the TensorBoard notebook extension\n",
        "%load_ext tensorboard"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OsIM-sbWJige"
      },
      "source": [
        "# Load logs in Tensorboard\n",
        "%tensorboard --logdir logs"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zsEKeXX-qFeD"
      },
      "source": [
        "# ZIP logs folder\n",
        "!zip -r /content/logs /content/logs"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gwwbYeLdqM70"
      },
      "source": [
        "# Download logs.zip\n",
        "files.download(\"/content/logs.zip\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2jtcSW7JDHgx"
      },
      "source": [
        "# Script to upload data on Tensorboard.dev\n",
        "#tensorboard dev upload --logdir logs --name \"Real Estate\" --description \"Housing Price Prediction in Milan (Italy) through Deep Learning via immobiliare.it.ipynb\""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Tsx2pEmD6yuv"
      },
      "source": [
        "# Script to delete TensorBoard\n",
        "#tensorboard dev delete --experiment_id yTg4I7WmSjKk7frAC820Ow"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "z5EJSqlnEHzT"
      },
      "source": [
        "# Tensorboard.dev URL\n",
        "https://tensorboard.dev/experiment/tGja2XRHQvebBPV4zw0yww/"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}