data_processing_houses.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>additionalCharacteristics</th>\n",
       "      <th>area</th>\n",
       "      <th>catasto</th>\n",
       "      <th>climax</th>\n",
       "      <th>condition</th>\n",
       "      <th>constructionYear</th>\n",
       "      <th>energyClass</th>\n",
       "      <th>expensesCondominium</th>\n",
       "      <th>expensesHeating</th>\n",
       "      <th>floorNumber</th>\n",
       "      <th>...</th>\n",
       "      <th>idListing</th>\n",
       "      <th>lat</th>\n",
       "      <th>location</th>\n",
       "      <th>lon</th>\n",
       "      <th>name</th>\n",
       "      <th>placeType</th>\n",
       "      <th>price</th>\n",
       "      <th>propertyType</th>\n",
       "      <th>rooms</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Terrazzo,Cancello elettrico,Fibra ottica,Video...</td>\n",
       "      <td>82.0</td>\n",
       "      <td>Classe A/3, rendita € 620</td>\n",
       "      <td>Predisposizione impianto</td>\n",
       "      <td>Ottimo/Ristrutturato</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>A3</td>\n",
       "      <td>159/mese</td>\n",
       "      <td>10/anno</td>\n",
       "      <td>1° di 3 piani, con ascensore</td>\n",
       "      <td>...</td>\n",
       "      <td>BL427A02B - 02/05/2018</td>\n",
       "      <td>44.4874</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.3972</td>\n",
       "      <td>Quadrilocale via due Madonne, 47, Bologna</td>\n",
       "      <td>Appartamento</td>\n",
       "      <td>265.000</td>\n",
       "      <td>Classe immobile media</td>\n",
       "      <td>4 (2 camere da letto, 2 altro), 1 bagno, cucin...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Balcone,Cancello elettrico,Videocitofono,Impia...</td>\n",
       "      <td>160.0</td>\n",
       "      <td>Classe A/3, rendita € 1.800</td>\n",
       "      <td>Assente</td>\n",
       "      <td>Da ristrutturare</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>G</td>\n",
       "      <td>1/mese</td>\n",
       "      <td>1/anno</td>\n",
       "      <td>3° di 6 piani, con ascensore</td>\n",
       "      <td>...</td>\n",
       "      <td>Rif.V114S - 18/04/2018</td>\n",
       "      <td>44.4965</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.3381</td>\n",
       "      <td>Appartamento via San Gervasio, Bologna</td>\n",
       "      <td>Appartamento</td>\n",
       "      <td>430.000</td>\n",
       "      <td>Intera proprietà, classe immobile signorile</td>\n",
       "      <td>5 (3 camere da letto, 2 altro), 3 bagni, cucin...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Balcone,Terrazzo,Videocitofono,Porta blindata,...</td>\n",
       "      <td>92.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo, solo freddo</td>\n",
       "      <td>Nuovo / In costruzione</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>A</td>\n",
       "      <td>60/mese</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Piano terra di 14 piani, con ascensore</td>\n",
       "      <td>...</td>\n",
       "      <td>A.2.2 (1477622) - 10/05/2018</td>\n",
       "      <td>44.5102</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.3549</td>\n",
       "      <td>Trilocale via Donato Creti, Bologna</td>\n",
       "      <td>Appartamento</td>\n",
       "      <td>322.000</td>\n",
       "      <td>Intera proprietà, classe immobile signorile</td>\n",
       "      <td>3 (2 camere da letto, 1 altro), 2 bagni, cucin...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Balcone,Terrazzo,Porta blindata</td>\n",
       "      <td>110.0</td>\n",
       "      <td>Classe A/3, rendita € 258</td>\n",
       "      <td>Autonomo, solo freddo</td>\n",
       "      <td>Nuovo / In costruzione</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>A+</td>\n",
       "      <td>70/mese</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5° di 14 piani, con ascensore</td>\n",
       "      <td>...</td>\n",
       "      <td>5.1 (1650108) - 10/05/2018</td>\n",
       "      <td>44.5100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.3548</td>\n",
       "      <td>Quadrilocale via Donato Creti, Bologna</td>\n",
       "      <td>Appartamento</td>\n",
       "      <td>387.000</td>\n",
       "      <td>Intera proprietà, immobile di lusso</td>\n",
       "      <td>4 (2 camere da letto, 2 altro), 2 bagni, cucin...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Terrazzo,Idromassaggio,Fibra ottica,Videocitof...</td>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>Ottimo/Ristrutturato</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>130/mese</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Con ascensore</td>\n",
       "      <td>...</td>\n",
       "      <td>CE141 - 22/01/2018</td>\n",
       "      <td>44.4948</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.3004</td>\n",
       "      <td>Attico / Mansarda via Della Certosa adiacenze,...</td>\n",
       "      <td>Attico / Mansarda</td>\n",
       "      <td>720.000</td>\n",
       "      <td>Intera proprietà, classe immobile signorile</td>\n",
       "      <td>5+ (2 camere da letto, 4 altro), 2 bagni, cuci...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           additionalCharacteristics   area  \\\n",
       "0  Terrazzo,Cancello elettrico,Fibra ottica,Video...   82.0   \n",
       "1  Balcone,Cancello elettrico,Videocitofono,Impia...  160.0   \n",
       "2  Balcone,Terrazzo,Videocitofono,Porta blindata,...   92.0   \n",
       "3                    Balcone,Terrazzo,Porta blindata  110.0   \n",
       "4  Terrazzo,Idromassaggio,Fibra ottica,Videocitof...  150.0   \n",
       "\n",
       "                       catasto                    climax  \\\n",
       "0    Classe A/3, rendita € 620  Predisposizione impianto   \n",
       "1  Classe A/3, rendita € 1.800                   Assente   \n",
       "2                          NaN     Autonomo, solo freddo   \n",
       "3    Classe A/3, rendita € 258     Autonomo, solo freddo   \n",
       "4                          NaN                  Autonomo   \n",
       "\n",
       "                condition  constructionYear energyClass expensesCondominium  \\\n",
       "0    Ottimo/Ristrutturato            2012.0          A3            159/mese   \n",
       "1        Da ristrutturare            1970.0           G              1/mese   \n",
       "2  Nuovo / In costruzione            2017.0           A             60/mese   \n",
       "3  Nuovo / In costruzione            2017.0          A+             70/mese   \n",
       "4    Ottimo/Ristrutturato            2008.0         NaN            130/mese   \n",
       "\n",
       "  expensesHeating                             floorNumber ...  \\\n",
       "0         10/anno            1° di 3 piani, con ascensore ...   \n",
       "1          1/anno            3° di 6 piani, con ascensore ...   \n",
       "2             NaN  Piano terra di 14 piani, con ascensore ...   \n",
       "3             NaN           5° di 14 piani, con ascensore ...   \n",
       "4             NaN                           Con ascensore ...   \n",
       "\n",
       "                      idListing      lat location      lon  \\\n",
       "0        BL427A02B - 02/05/2018  44.4874      NaN  11.3972   \n",
       "1        Rif.V114S - 18/04/2018  44.4965      NaN  11.3381   \n",
       "2  A.2.2 (1477622) - 10/05/2018  44.5102      NaN  11.3549   \n",
       "3    5.1 (1650108) - 10/05/2018  44.5100      NaN  11.3548   \n",
       "4            CE141 - 22/01/2018  44.4948      NaN  11.3004   \n",
       "\n",
       "                                                name          placeType  \\\n",
       "0          Quadrilocale via due Madonne, 47, Bologna       Appartamento   \n",
       "1             Appartamento via San Gervasio, Bologna       Appartamento   \n",
       "2                Trilocale via Donato Creti, Bologna       Appartamento   \n",
       "3             Quadrilocale via Donato Creti, Bologna       Appartamento   \n",
       "4  Attico / Mansarda via Della Certosa adiacenze,...  Attico / Mansarda   \n",
       "\n",
       "     price                                 propertyType  \\\n",
       "0  265.000                        Classe immobile media   \n",
       "1  430.000  Intera proprietà, classe immobile signorile   \n",
       "2  322.000  Intera proprietà, classe immobile signorile   \n",
       "3  387.000          Intera proprietà, immobile di lusso   \n",
       "4  720.000  Intera proprietà, classe immobile signorile   \n",
       "\n",
       "                                               rooms url  \n",
       "0  4 (2 camere da letto, 2 altro), 1 bagno, cucin... NaN  \n",
       "1  5 (3 camere da letto, 2 altro), 3 bagni, cucin... NaN  \n",
       "2  3 (2 camere da letto, 1 altro), 2 bagni, cucin... NaN  \n",
       "3  4 (2 camere da letto, 2 altro), 2 bagni, cucin... NaN  \n",
       "4  5+ (2 camere da letto, 4 altro), 2 bagni, cuci... NaN  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities = [\n",
    "    'Bologna',\n",
    "    'Firenze',\n",
    "    'Genova',\n",
    "    'Milano',\n",
    "    'Napoli',\n",
    "    'Palermo',\n",
    "    'Torino',\n",
    "    'Roma',\n",
    "]\n",
    "\n",
    "list_ = []\n",
    "for city in cities:\n",
    "    filename = 'data/generated_files/immobiliare/immobiliare_{}_05_2018.csv'.format(city)\n",
    "    temp_df = pd.read_csv(filename, index_col=None, header=0)\n",
    "    list_.append(temp_df)\n",
    "df = pd.concat(list_)\n",
    "df = df.reset_index(drop=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['additionalCharacteristics', 'area', 'catasto', 'climax', 'condition',\n",
       "       'constructionYear', 'energyClass', 'expensesCondominium',\n",
       "       'expensesHeating', 'floorNumber', 'garage', 'heating', 'idListing',\n",
       "       'lat', 'location', 'lon', 'name', 'placeType', 'price', 'propertyType',\n",
       "       'rooms', 'url'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>area</th>\n",
       "      <th>constructionYear</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>81053.000000</td>\n",
       "      <td>57909.000000</td>\n",
       "      <td>79126.000000</td>\n",
       "      <td>79126.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>105.555686</td>\n",
       "      <td>1950.041203</td>\n",
       "      <td>43.022831</td>\n",
       "      <td>11.076091</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>60.639515</td>\n",
       "      <td>65.863681</td>\n",
       "      <td>2.013481</td>\n",
       "      <td>2.166132</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>16.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-34.397000</td>\n",
       "      <td>7.606200</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>69.000000</td>\n",
       "      <td>1950.000000</td>\n",
       "      <td>41.867800</td>\n",
       "      <td>9.142713</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>90.000000</td>\n",
       "      <td>1960.000000</td>\n",
       "      <td>41.969550</td>\n",
       "      <td>12.350950</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>125.000000</td>\n",
       "      <td>1970.000000</td>\n",
       "      <td>45.049100</td>\n",
       "      <td>12.524800</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>960.000000</td>\n",
       "      <td>2019.000000</td>\n",
       "      <td>45.533300</td>\n",
       "      <td>150.644000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               area  constructionYear           lat           lon  url\n",
       "count  81053.000000      57909.000000  79126.000000  79126.000000  0.0\n",
       "mean     105.555686       1950.041203     43.022831     11.076091  NaN\n",
       "std       60.639515         65.863681      2.013481      2.166132  NaN\n",
       "min       16.000000          1.000000    -34.397000      7.606200  NaN\n",
       "25%       69.000000       1950.000000     41.867800      9.142713  NaN\n",
       "50%       90.000000       1960.000000     41.969550     12.350950  NaN\n",
       "75%      125.000000       1970.000000     45.049100     12.524800  NaN\n",
       "max      960.000000       2019.000000     45.533300    150.644000  NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.dropna(subset=['area'])\n",
    "df['date_listing'] = pd.to_datetime(df['idListing'].str.split('-').str[-1].str.strip(), format='%d/%m/%Y', errors='ignore')\n",
    "df = df[df['price'] != 'richiesta']\n",
    "df = df[df['area'] > 15]\n",
    "df = df[df['placeType'].isin(('Appartamento', 'Attico / Mansarda', 'Casa indipendente', 'Loft / Open Space', 'Villetta a schiera'))]\n",
    "df = df[df['condition'] != 'Nuovo / In costruzione']\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>additionalCharacteristics</th>\n",
       "      <th>area</th>\n",
       "      <th>catasto</th>\n",
       "      <th>climax</th>\n",
       "      <th>condition</th>\n",
       "      <th>constructionYear</th>\n",
       "      <th>energyClass</th>\n",
       "      <th>expensesCondominium</th>\n",
       "      <th>expensesHeating</th>\n",
       "      <th>floorNumber</th>\n",
       "      <th>...</th>\n",
       "      <th>lat</th>\n",
       "      <th>location</th>\n",
       "      <th>lon</th>\n",
       "      <th>name</th>\n",
       "      <th>placeType</th>\n",
       "      <th>price</th>\n",
       "      <th>propertyType</th>\n",
       "      <th>rooms</th>\n",
       "      <th>url</th>\n",
       "      <th>date_listing</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_listing</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>52</td>\n",
       "      <td>53</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>50</td>\n",
       "      <td>25</td>\n",
       "      <td>49</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>52</td>\n",
       "      <td>...</td>\n",
       "      <td>53</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>46</td>\n",
       "      <td>52</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>141</td>\n",
       "      <td>150</td>\n",
       "      <td>2</td>\n",
       "      <td>72</td>\n",
       "      <td>131</td>\n",
       "      <td>85</td>\n",
       "      <td>137</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>138</td>\n",
       "      <td>...</td>\n",
       "      <td>147</td>\n",
       "      <td>3</td>\n",
       "      <td>147</td>\n",
       "      <td>150</td>\n",
       "      <td>150</td>\n",
       "      <td>150</td>\n",
       "      <td>131</td>\n",
       "      <td>145</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>751</td>\n",
       "      <td>796</td>\n",
       "      <td>244</td>\n",
       "      <td>462</td>\n",
       "      <td>739</td>\n",
       "      <td>467</td>\n",
       "      <td>682</td>\n",
       "      <td>366</td>\n",
       "      <td>52</td>\n",
       "      <td>747</td>\n",
       "      <td>...</td>\n",
       "      <td>764</td>\n",
       "      <td>32</td>\n",
       "      <td>764</td>\n",
       "      <td>796</td>\n",
       "      <td>796</td>\n",
       "      <td>796</td>\n",
       "      <td>704</td>\n",
       "      <td>782</td>\n",
       "      <td>0</td>\n",
       "      <td>796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>8313</td>\n",
       "      <td>8608</td>\n",
       "      <td>4624</td>\n",
       "      <td>6387</td>\n",
       "      <td>8012</td>\n",
       "      <td>6055</td>\n",
       "      <td>6934</td>\n",
       "      <td>5180</td>\n",
       "      <td>1276</td>\n",
       "      <td>8075</td>\n",
       "      <td>...</td>\n",
       "      <td>8260</td>\n",
       "      <td>348</td>\n",
       "      <td>8260</td>\n",
       "      <td>8608</td>\n",
       "      <td>8608</td>\n",
       "      <td>8608</td>\n",
       "      <td>8034</td>\n",
       "      <td>8546</td>\n",
       "      <td>0</td>\n",
       "      <td>8608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>68285</td>\n",
       "      <td>71425</td>\n",
       "      <td>42057</td>\n",
       "      <td>56909</td>\n",
       "      <td>65615</td>\n",
       "      <td>51262</td>\n",
       "      <td>58874</td>\n",
       "      <td>47925</td>\n",
       "      <td>12027</td>\n",
       "      <td>67133</td>\n",
       "      <td>...</td>\n",
       "      <td>69883</td>\n",
       "      <td>1542</td>\n",
       "      <td>69883</td>\n",
       "      <td>71425</td>\n",
       "      <td>71425</td>\n",
       "      <td>71425</td>\n",
       "      <td>66212</td>\n",
       "      <td>71227</td>\n",
       "      <td>0</td>\n",
       "      <td>71425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              additionalCharacteristics   area  catasto  climax  condition  \\\n",
       "date_listing                                                                 \n",
       "2012                                  6      6        0       6          6   \n",
       "2013                                 14     15        0      13         15   \n",
       "2014                                 52     53        0      35         50   \n",
       "2015                                141    150        2      72        131   \n",
       "2016                                751    796      244     462        739   \n",
       "2017                               8313   8608     4624    6387       8012   \n",
       "2018                              68285  71425    42057   56909      65615   \n",
       "\n",
       "              constructionYear  energyClass  expensesCondominium  \\\n",
       "date_listing                                                       \n",
       "2012                         6            6                    5   \n",
       "2013                         9           15                    7   \n",
       "2014                        25           49                   20   \n",
       "2015                        85          137                   72   \n",
       "2016                       467          682                  366   \n",
       "2017                      6055         6934                 5180   \n",
       "2018                     51262        58874                47925   \n",
       "\n",
       "              expensesHeating  floorNumber      ...         lat  location  \\\n",
       "date_listing                                    ...                         \n",
       "2012                        0            6      ...           6         0   \n",
       "2013                        0           14      ...          13         2   \n",
       "2014                        0           52      ...          53         0   \n",
       "2015                        0          138      ...         147         3   \n",
       "2016                       52          747      ...         764        32   \n",
       "2017                     1276         8075      ...        8260       348   \n",
       "2018                    12027        67133      ...       69883      1542   \n",
       "\n",
       "                lon   name  placeType  price  propertyType  rooms  url  \\\n",
       "date_listing                                                             \n",
       "2012              6      6          6      6             6      6    0   \n",
       "2013             13     15         15     15             9     15    0   \n",
       "2014             53     53         53     53            46     52    0   \n",
       "2015            147    150        150    150           131    145    0   \n",
       "2016            764    796        796    796           704    782    0   \n",
       "2017           8260   8608       8608   8608          8034   8546    0   \n",
       "2018          69883  71425      71425  71425         66212  71227    0   \n",
       "\n",
       "              date_listing  \n",
       "date_listing                \n",
       "2012                     6  \n",
       "2013                    15  \n",
       "2014                    53  \n",
       "2015                   150  \n",
       "2016                   796  \n",
       "2017                  8608  \n",
       "2018                 71425  \n",
       "\n",
       "[7 rows x 23 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(df['date_listing'].dt.year).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['date_listing'] >= datetime.datetime(2017,5,10)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:, 'price'] = df['price'].str.replace('.', '')\n",
    "df['price'] = df['price'].astype(np.float32)\n",
    "\n",
    "df = df[df['price'] > 20000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_property_class(x):\n",
    "    #'Diritto di superficie',\n",
    "    #'Diritto di superficie, classe immobile economica',\n",
    "    x = str(x)\n",
    "    f = x.split(',')\n",
    "    return f[-1].strip() if 'classe' in f[-1] or len(f) == 2 else np.nan\n",
    "\n",
    "def extract_property_type(x):\n",
    "    #'Diritto di superficie',\n",
    "    #'Diritto di superficie, classe immobile economica',\n",
    "    x = str(x)\n",
    "    f = x.split(',')\n",
    "    return f[0].strip()\n",
    "\n",
    "\n",
    "df['propertyType'] = df['propertyType'].str.lower()\n",
    "df['property_class'] = df['propertyType'].apply(lambda x: extract_property_class(x))\n",
    "df['propertyType'] = df['propertyType'].apply(lambda x: extract_property_type(x))\n",
    "\n",
    "df.loc[df['property_class'] == 'classe immobile economica', 'property_class'] = 0\n",
    "df.loc[df['property_class'] == 'classe immobile media', 'property_class'] = 1\n",
    "df.loc[df['property_class'] == 'classe immobile signorile', 'property_class'] = 2\n",
    "df.loc[df['property_class'] == 'immobile di lusso', 'property_class'] = 2\n",
    "\n",
    "df['property_type'] = np.nan\n",
    "df.loc[df['propertyType'] == 'nuda proprietà', 'property_type'] = 4\n",
    "df.loc[df['propertyType'] == 'usufrutto', 'property_type'] = 3\n",
    "df.loc[df['propertyType'] == 'parziale proprietà', 'property_type'] = 2\n",
    "df.loc[df['propertyType'] == 'diritto di superficie', 'property_type'] = 5\n",
    "df.loc[df['propertyType'] == 'intera proprietà', 'property_type'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df.property_type == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:, 'garage'] = df['garage'].apply(lambda x: 1 if 'Box' in str(x) else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_land_value(x):\n",
    "    x = str(x)\n",
    "    f = x.split(',')\n",
    "    \n",
    "    if 'rendita' in f[-1]:\n",
    "        return f[-1].split(' ')[-1]\n",
    "    \n",
    "    return np.nan\n",
    "\n",
    "df['land_value'] = df['catasto'].str.lower().apply(lambda x: process_land_value(x)).str.replace('.', '').astype(np.float32)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:, 'energyClass'] = df['energyClass'].str[:2].str.replace(',', '')\n",
    "set_energies = {\n",
    "    'A+':4, 'A':5, 'A1':3, 'A2':2, 'A3':1, 'A4':0, 'B':6, 'C':7, 'D':8, 'E':9, 'F':10, 'G':11, 'No': np.nan\n",
    "}\n",
    "for k,v in set_energies.items():\n",
    "    df.loc[df['energyClass'] == k, 'energyClass'] = v\n",
    "df['energyClass'] = df['energyClass'].apply(pd.to_numeric, errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>locali</th>\n",
       "      <th>camere</th>\n",
       "      <th>altro</th>\n",
       "      <th>bagni</th>\n",
       "      <th>cucina_a a vista</th>\n",
       "      <th>cucina_a abitabile</th>\n",
       "      <th>cucina_a angolo cottura</th>\n",
       "      <th>cucina_a semi abitabile</th>\n",
       "      <th>cucina_otto</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  locali camere altro bagni  cucina_a a vista  cucina_a abitabile  \\\n",
       "1      5      3     2     3                 0                   1   \n",
       "4      6      2     4     2                 0                   1   \n",
       "5      5      4     1     2                 0                   0   \n",
       "6      6      3     6     2                 0                   1   \n",
       "7      6      3     3     2                 0                   1   \n",
       "\n",
       "   cucina_a angolo cottura  cucina_a semi abitabile  cucina_otto  \n",
       "1                        0                        0            0  \n",
       "4                        0                        0            0  \n",
       "5                        0                        1            0  \n",
       "6                        0                        0            0  \n",
       "7                        0                        0            0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rooms = df.rooms.str.extract(r\"(?:(?P<locali>\\d+\\+?)(?: (?:local\\w(?:\\, )?)| \\())?(?:(?P<camere>\\d+\\+?)(?: cam[\\w ]+(?:\\, )?))?(?:(?P<altro>\\d+\\+?)(?: altr[\\w]+)\\)?(?:\\, )?)?(?:(?P<bagni>\\d+\\+?)(?: bagn[\\w]+(?:\\, )?))?(?:(?:[Cc]ucin)(?P<cucina>[\\w ]+))?\", expand=True)\n",
    "df_rooms = pd.get_dummies(df_rooms, columns=['cucina'])\n",
    "df_rooms.loc[df_rooms.locali.notnull() & df_rooms.locali.str.contains('+', na='', regex=False), 'locali'] = df_rooms.loc[df_rooms.locali.notnull() & df_rooms.locali.str.contains('+', na='', regex=False), 'locali'].str.replace('+', '').astype(np.float32)+1\n",
    "df_rooms.loc[df_rooms.bagni.notnull() & df_rooms.bagni.str.contains('+', na='', regex=False), 'bagni'] = df_rooms.loc[df_rooms.bagni.notnull() & df_rooms.bagni.str.contains('+', na='', regex=False), 'bagni'].str.replace('+', '').astype(np.float32)+1\n",
    "df_rooms.loc[df_rooms.altro.notnull() & df_rooms.altro.str.contains('+', na='', regex=False), 'altro'] = df_rooms.loc[df_rooms.altro.notnull() & df_rooms.altro.str.contains('+', na='', regex=False), 'altro'].str.replace('+', '').astype(np.float32)+1\n",
    "df = pd.concat((df, df_rooms), axis=1)\n",
    "\n",
    "df['camere'] = df['camere'].astype(np.float32)\n",
    "df = df[df['camere'] < 100]\n",
    "df_rooms.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.get_dummies(df, columns=['condition'])\n",
    "df = pd.get_dummies(df, columns=['placeType'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['additionalCharacteristics'] = df['additionalCharacteristics'].fillna(\"\")\n",
    "df['additionalCharacteristics'] = df['additionalCharacteristics'].str.split(',')\n",
    "df['additionalCharacteristics'] = df['additionalCharacteristics'].map(lambda x: ','.join(sorted(x))).str.split(',')\n",
    "\n",
    "characteristics = set()\n",
    "\n",
    "for row in df['additionalCharacteristics'].values:\n",
    "    characteristics = characteristics.union(set(row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Taverna',\n",
       " 'Infissi esterni in vetro / metallo',\n",
       " 'Portiere intera giornata',\n",
       " 'Portiere mezza giornata',\n",
       " 'Infissi esterni in vetro / pvc',\n",
       " 'Giardino privato',\n",
       " 'Impianto tv centralizzato',\n",
       " 'Infissi esterni in doppio vetro / metallo',\n",
       " 'Parzialmente arredato',\n",
       " 'Idromassaggio',\n",
       " 'Impianto tv singolo',\n",
       " 'Fibra ottica',\n",
       " 'Infissi esterni in doppio vetro / pvc',\n",
       " 'Porta blindata',\n",
       " 'Videocitofono',\n",
       " 'Impianto tv con parabola satellitare',\n",
       " 'Cantina',\n",
       " 'Campo da tennis',\n",
       " 'Infissi esterni in triplo vetro / pvc',\n",
       " 'Terrazzo',\n",
       " 'Piscina',\n",
       " 'Infissi esterni in triplo vetro / legno',\n",
       " 'Esposizione doppia',\n",
       " 'Giardino comune',\n",
       " 'Infissi esterni in doppio vetro / legno',\n",
       " 'Infissi esterni in vetro / legno',\n",
       " 'Impianto allarme',\n",
       " 'Esposizione esterna',\n",
       " 'Arredato',\n",
       " 'Esposizione interna',\n",
       " 'Mansarda',\n",
       " 'Camino',\n",
       " 'Cancello elettrico',\n",
       " 'Infissi esterni in triplo vetro / metallo',\n",
       " 'Balcone']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_columns = [x for x in characteristics if len(x) > 2]\n",
    "list_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "characteristics_table = {\n",
    "    'Infissi esterni in vetro / legno': 0,\n",
    "    'Infissi esterni in vetro / pvc': 1,\n",
    "    'Infissi esterni in vetro / metallo': 2,\n",
    "    'Infissi esterni in doppio vetro / legno': 3,\n",
    "    'Infissi esterni in doppio vetro / pvc': 4,\n",
    "    'Infissi esterni in doppio vetro / metallo': 5,\n",
    "    'Infissi esterni in triplo vetro / legno': 6,\n",
    "    'Infissi esterni in triplo vetro / pvc': 7,\n",
    "    'Infissi esterni in triplo vetro / metallo': 8,\n",
    "}\n",
    "\n",
    "portiere_table = {\n",
    "    'Portiere intera giornata':2,\n",
    "    'Portiere mezza giornata':1\n",
    "}\n",
    "\n",
    "esposizione_table = {\n",
    "    'Esposizione doppia':3,\n",
    "    'Esposizione esterna':2,\n",
    "    'Esposizione interna':1\n",
    "}\n",
    "\n",
    "tv_table = {\n",
    "    'Impianto tv con parabola satellitare':2,\n",
    "    'Impianto tv singolo': 1,\n",
    "    'Impianto tv centralizzato':0\n",
    "}\n",
    "\n",
    "arredato_table = {\n",
    "    'Arredato':2,\n",
    "    'Parzialmente arredato': 1\n",
    "}\n",
    "\n",
    "giardino_table = {\n",
    "    'Giardino comune': 1,\n",
    "    'Giardino privato': 2\n",
    "}\n",
    "\n",
    "cucina_table = {\n",
    "    'cucina_a angolo cottura': 1,\n",
    "    'cucina_otto': 1,\n",
    "    'cucina_a a vista': 2,\n",
    "    'cucina_a semi abitabile': 3,\n",
    "    'cucina_a abitabile': 4,\n",
    "}\n",
    "\n",
    "condition_table = {\n",
    "    #'condition_Nuovo / In costruzione': 0,\n",
    "    'condition_Da ristrutturare': 1,\n",
    "    'condition_Buono / Abitabile': 2,\n",
    "    'condition_Ottimo/Ristrutturato': 3\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>area</th>\n",
       "      <th>catasto</th>\n",
       "      <th>climax</th>\n",
       "      <th>constructionYear</th>\n",
       "      <th>energyClass</th>\n",
       "      <th>expensesCondominium</th>\n",
       "      <th>expensesHeating</th>\n",
       "      <th>floorNumber</th>\n",
       "      <th>garage</th>\n",
       "      <th>heating</th>\n",
       "      <th>...</th>\n",
       "      <th>infissi</th>\n",
       "      <th>portiere</th>\n",
       "      <th>giardino</th>\n",
       "      <th>arredato</th>\n",
       "      <th>esposizione</th>\n",
       "      <th>tv</th>\n",
       "      <th>kitchen</th>\n",
       "      <th>condition</th>\n",
       "      <th>class</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>160.0</td>\n",
       "      <td>Classe A/3, rendita € 1.800</td>\n",
       "      <td>Assente</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1/mese</td>\n",
       "      <td>1/anno</td>\n",
       "      <td>3° di 6 piani, con ascensore</td>\n",
       "      <td>0</td>\n",
       "      <td>Centralizzato, a radiatori, alimentato a metano</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>130/mese</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Con ascensore</td>\n",
       "      <td>0</td>\n",
       "      <td>Centralizzato</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>135.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo, freddo/caldo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70/mese</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3° di 3 piani</td>\n",
       "      <td>0</td>\n",
       "      <td>Autonomo, a radiatori, alimentato a gas</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>218.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125/mese</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1° di 3 piani</td>\n",
       "      <td>1</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>120.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250/mese</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ultimo di 6 piani, con ascensore</td>\n",
       "      <td>0</td>\n",
       "      <td>Centralizzato</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 86 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    area                      catasto                  climax  \\\n",
       "1  160.0  Classe A/3, rendita € 1.800                 Assente   \n",
       "4  150.0                          NaN                Autonomo   \n",
       "5  135.0                          NaN  Autonomo, freddo/caldo   \n",
       "6  218.0                          NaN                Autonomo   \n",
       "7  120.0                          NaN                Autonomo   \n",
       "\n",
       "   constructionYear  energyClass expensesCondominium expensesHeating  \\\n",
       "1            1970.0         11.0              1/mese          1/anno   \n",
       "4            2008.0          NaN            130/mese             NaN   \n",
       "5               NaN         10.0             70/mese             NaN   \n",
       "6            1600.0          NaN            125/mese             NaN   \n",
       "7            1962.0          NaN            250/mese             NaN   \n",
       "\n",
       "                        floorNumber  garage  \\\n",
       "1      3° di 6 piani, con ascensore       0   \n",
       "4                     Con ascensore       0   \n",
       "5                     3° di 3 piani       0   \n",
       "6                     1° di 3 piani       1   \n",
       "7  Ultimo di 6 piani, con ascensore       0   \n",
       "\n",
       "                                           heating ...  infissi  portiere  \\\n",
       "1  Centralizzato, a radiatori, alimentato a metano ...      NaN         0   \n",
       "4                                    Centralizzato ...      4.0         0   \n",
       "5          Autonomo, a radiatori, alimentato a gas ...      NaN         0   \n",
       "6                                         Autonomo ...      3.0         0   \n",
       "7                                    Centralizzato ...      NaN         2   \n",
       "\n",
       "  giardino  arredato esposizione   tv kitchen condition  class type  \n",
       "1        0         0         NaN  0.0     4.0       1.0    NaN  NaN  \n",
       "4        0         0         2.0  0.0     4.0       3.0    NaN  NaN  \n",
       "5        0         0         NaN  NaN     3.0       3.0    NaN  NaN  \n",
       "6        0         2         1.0  0.0     4.0       3.0    NaN  NaN  \n",
       "7        0         0         NaN  NaN     4.0       3.0    NaN  NaN  \n",
       "\n",
       "[5 rows x 86 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.drop('additionalCharacteristics', 1).join(\n",
    "    pd.get_dummies(\n",
    "        pd.DataFrame(df.additionalCharacteristics.tolist()).stack()\n",
    "    ).astype(int).sum(level=0)\n",
    ")\n",
    "        \n",
    "df['infissi'] = np.nan\n",
    "df['portiere'] = 0\n",
    "df['giardino'] = 0\n",
    "df['arredato'] = 0\n",
    "df['esposizione'] = np.nan\n",
    "df['tv'] = np.nan\n",
    "df['kitchen'] = np.nan\n",
    "df['condition'] = np.nan\n",
    "df['class'] = np.nan\n",
    "df['type'] = np.nan\n",
    "for x in characteristics_table.keys():\n",
    "    df.loc[df[x] == 1, 'infissi'] = characteristics_table[x]\n",
    "for x in portiere_table.keys():\n",
    "    df.loc[df[x] == 1, 'portiere'] = portiere_table[x]\n",
    "for x in giardino_table.keys():\n",
    "    df.loc[df[x] == 1, 'giardino'] = giardino_table[x]\n",
    "for x in arredato_table.keys():\n",
    "    df.loc[df[x] == 1, 'arredato'] = arredato_table[x]\n",
    "for x in esposizione_table.keys():\n",
    "    df.loc[df[x] == 1, 'esposizione'] = esposizione_table[x]\n",
    "for x in tv_table.keys():\n",
    "    df.loc[df[x] == 1, 'tv'] = tv_table[x]\n",
    "for x in condition_table.keys():\n",
    "    df.loc[df[x] == 1, 'condition'] = condition_table[x]\n",
    "    \n",
    "for x in cucina_table.keys():\n",
    "    df.loc[df[x] == 1, 'kitchen'] = cucina_table[x]\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>area</th>\n",
       "      <th>catasto</th>\n",
       "      <th>climax</th>\n",
       "      <th>constructionYear</th>\n",
       "      <th>energyClass</th>\n",
       "      <th>expensesCondominium</th>\n",
       "      <th>expensesHeating</th>\n",
       "      <th>floorNumber</th>\n",
       "      <th>garage</th>\n",
       "      <th>heating</th>\n",
       "      <th>...</th>\n",
       "      <th>infissi</th>\n",
       "      <th>portiere</th>\n",
       "      <th>giardino</th>\n",
       "      <th>arredato</th>\n",
       "      <th>esposizione</th>\n",
       "      <th>tv</th>\n",
       "      <th>kitchen</th>\n",
       "      <th>condition</th>\n",
       "      <th>class</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>160.0</td>\n",
       "      <td>Classe A/3, rendita € 1.800</td>\n",
       "      <td>Assente</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1/anno</td>\n",
       "      <td>3° di 6 piani, con ascensore</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Centralizzato, a radiatori, alimentato a metano</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>130</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Con ascensore</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Centralizzato</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>135.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo, freddo/caldo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3° di 3 piani</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Autonomo, a radiatori, alimentato a gas</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>218.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1° di 3 piani</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>120.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ultimo di 6 piani, con ascensore</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Centralizzato</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 86 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    area                      catasto                  climax  \\\n",
       "1  160.0  Classe A/3, rendita € 1.800                 Assente   \n",
       "4  150.0                          NaN                Autonomo   \n",
       "5  135.0                          NaN  Autonomo, freddo/caldo   \n",
       "6  218.0                          NaN                Autonomo   \n",
       "7  120.0                          NaN                Autonomo   \n",
       "\n",
       "   constructionYear  energyClass expensesCondominium expensesHeating  \\\n",
       "1            1970.0         11.0                   1          1/anno   \n",
       "4            2008.0          NaN                 130             NaN   \n",
       "5               NaN         10.0                  70             NaN   \n",
       "6            1600.0          NaN                 125             NaN   \n",
       "7            1962.0          NaN                 250             NaN   \n",
       "\n",
       "                        floorNumber  garage  \\\n",
       "1      3° di 6 piani, con ascensore     0.0   \n",
       "4                     Con ascensore     0.0   \n",
       "5                     3° di 3 piani     0.0   \n",
       "6                     1° di 3 piani     1.0   \n",
       "7  Ultimo di 6 piani, con ascensore     0.0   \n",
       "\n",
       "                                           heating ...  infissi  portiere  \\\n",
       "1  Centralizzato, a radiatori, alimentato a metano ...      NaN       0.0   \n",
       "4                                    Centralizzato ...      4.0       0.0   \n",
       "5          Autonomo, a radiatori, alimentato a gas ...      NaN       0.0   \n",
       "6                                         Autonomo ...      3.0       0.0   \n",
       "7                                    Centralizzato ...      NaN       2.0   \n",
       "\n",
       "  giardino  arredato esposizione   tv kitchen condition  class type  \n",
       "1      0.0       0.0         NaN  0.0     4.0       1.0    NaN  NaN  \n",
       "4      0.0       0.0         2.0  0.0     4.0       3.0    NaN  NaN  \n",
       "5      0.0       0.0         NaN  NaN     3.0       3.0    NaN  NaN  \n",
       "6      0.0       2.0         1.0  0.0     4.0       3.0    NaN  NaN  \n",
       "7      0.0       0.0         NaN  NaN     4.0       3.0    NaN  NaN  \n",
       "\n",
       "[5 rows x 86 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['expensesCondominium'].str.len() <= 1] = np.nan\n",
    "df.loc[df['expensesCondominium'].str.len() > 1, 'expensesCondominium'] = df[df['expensesCondominium'].str.len() > 1]['expensesCondominium'].str.split('/', expand=True)[0]\n",
    "df.loc[:, 'expensesCondominium'] = df['expensesCondominium'].str.replace('.', '')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "{'A radiatori',\n",
    " 'Alimentato a fotovoltaico',\n",
    " 'Alimentato a metano',\n",
    " 'Assente', 0\n",
    " 'Autonomo', 2\n",
    " 'Centralizzato'} 1\n",
    "'''\n",
    "\n",
    "df['newHeating'] = np.nan\n",
    "df.loc[(~df.heating.isnull()) & (df.heating.str.contains('Assente')), 'newHeating'] = 0\n",
    "df.loc[(~df.heating.isnull()) & (df.heating.str.contains('Assente')), 'Centralizzato'] = 1\n",
    "df.loc[(~df.heating.isnull()) & (df.heating.str.contains('Autonomo')), 'newHeating'] = 2\n",
    "df.loc[(~df.heating.isnull()) & (df.heating.str.contains('Alimentato a metano')), 'newHeating'] = 2\n",
    "df.loc[(~df.heating.isnull()) & (df.heating.str.contains('Alimentato a fotovoltaico')), 'newHeating'] = 2\n",
    "df.loc[(~df.heating.isnull()) & (df.heating.str.contains('A radiatori')), 'newHeating'] = 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Interrato\n",
    "# Seminterrato\n",
    "# Piano rialzato - Ammezzato\n",
    "# Piano terra\n",
    "# 1°\n",
    "# Su più livelli\n",
    "# altro\n",
    "\n",
    "# ascensore\n",
    "\n",
    "df['newFloorNumber'] = np.nan\n",
    "df.loc[(~df.floorNumber.isnull()), 'newFloorNumber'] = 6\n",
    "df.loc[(~df.floorNumber.isnull()) & (df.floorNumber.str.contains('Interrato')), 'newFloorNumber'] = 0\n",
    "df.loc[(~df.floorNumber.isnull()) & (df.floorNumber.str.contains('Seminterrato')), 'newFloorNumber'] = 1\n",
    "df.loc[(~df.floorNumber.isnull()) & ((df.floorNumber.str.contains('Piano rialzato')) | (df.floorNumber.str.contains('Ammezzato'))), 'newFloorNumber'] = 2\n",
    "df.loc[(~df.floorNumber.isnull()) & (df.floorNumber.str.contains('Piano terra')), 'newFloorNumber'] = 3\n",
    "df.loc[(~df.floorNumber.isnull()) & (df.floorNumber.str.contains('1°')), 'newFloorNumber'] = 4\n",
    "df.loc[(~df.floorNumber.isnull()) & (df.floorNumber.str.contains('livelli')), 'newFloorNumber'] = 5\n",
    "\n",
    "# ascensore\n",
    "df['ascensore'] = np.nan\n",
    "df.loc[(~df.floorNumber.isnull()) & (df.floorNumber.str.contains('con ascensore')), 'ascensore'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.rename(columns={'area': 'sqmt'})\n",
    "df['has_terrace'] = df[['Balcone', 'Terrazzo']].head(10).sum(1)\n",
    "    \n",
    "# Default values\n",
    "df.loc[df['Mansarda'].isnull(), 'Mansarda'] = 0\n",
    "df.loc[df['Idromassaggio'].isnull(), 'Idromassaggio'] = 0\n",
    "df.loc[df['Camino'].isnull(), 'Camino'] = 0\n",
    "df.loc[df['locali'].isnull(), 'locali'] = df.loc[df['locali'].isnull(), ['altro', 'camere', 'bagni']].fillna(0).sum(1) + (df.loc[df['locali'].isnull(), 'kitchen'].fillna(0) >= 3).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sqmt</th>\n",
       "      <th>constructionYear</th>\n",
       "      <th>energyClass</th>\n",
       "      <th>expensesCondominium</th>\n",
       "      <th>price</th>\n",
       "      <th>newFloorNumber</th>\n",
       "      <th>newHeating</th>\n",
       "      <th>portiere</th>\n",
       "      <th>infissi</th>\n",
       "      <th>giardino</th>\n",
       "      <th>...</th>\n",
       "      <th>placeType_Attico / Mansarda</th>\n",
       "      <th>placeType_Casa indipendente</th>\n",
       "      <th>placeType_Loft / Open Space</th>\n",
       "      <th>placeType_Villetta a schiera</th>\n",
       "      <th>property_class</th>\n",
       "      <th>property_type</th>\n",
       "      <th>locali</th>\n",
       "      <th>camere</th>\n",
       "      <th>altro</th>\n",
       "      <th>bagni</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>160.0</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>430000.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>150.0</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>130.0</td>\n",
       "      <td>720000.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>135.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>305000.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>218.0</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125.0</td>\n",
       "      <td>870000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>120.0</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250.0</td>\n",
       "      <td>445000.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    sqmt  constructionYear  energyClass  expensesCondominium     price  \\\n",
       "1  160.0            1970.0         11.0                  1.0  430000.0   \n",
       "4  150.0            2008.0          NaN                130.0  720000.0   \n",
       "5  135.0               NaN         10.0                 70.0  305000.0   \n",
       "6  218.0            1600.0          NaN                125.0  870000.0   \n",
       "7  120.0            1962.0          NaN                250.0  445000.0   \n",
       "\n",
       "   newFloorNumber  newHeating  portiere  infissi  giardino  ...    \\\n",
       "1             6.0         NaN       0.0      NaN       0.0  ...     \n",
       "4             6.0         NaN       0.0      4.0       0.0  ...     \n",
       "5             6.0         2.0       0.0      NaN       0.0  ...     \n",
       "6             4.0         2.0       0.0      3.0       0.0  ...     \n",
       "7             6.0         NaN       2.0      NaN       0.0  ...     \n",
       "\n",
       "   placeType_Attico / Mansarda  placeType_Casa indipendente  \\\n",
       "1                          0.0                          0.0   \n",
       "4                          1.0                          0.0   \n",
       "5                          1.0                          0.0   \n",
       "6                          0.0                          0.0   \n",
       "7                          0.0                          0.0   \n",
       "\n",
       "   placeType_Loft / Open Space  placeType_Villetta a schiera  property_class  \\\n",
       "1                          0.0                           0.0             2.0   \n",
       "4                          0.0                           0.0             2.0   \n",
       "5                          0.0                           0.0             NaN   \n",
       "6                          0.0                           0.0             2.0   \n",
       "7                          0.0                           0.0             1.0   \n",
       "\n",
       "   property_type  locali  camere  altro  bagni  \n",
       "1            1.0     5.0     3.0    2.0    3.0  \n",
       "4            1.0     6.0     2.0    4.0    2.0  \n",
       "5            1.0     5.0     4.0    1.0    2.0  \n",
       "6            1.0     6.0     3.0    6.0    2.0  \n",
       "7            1.0     6.0     3.0    3.0    2.0  \n",
       "\n",
       "[5 rows x 39 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = ['sqmt', \n",
    "           'constructionYear', 'energyClass', 'expensesCondominium', 'price',\n",
    "           'newFloorNumber', 'newHeating', 'portiere', 'infissi', 'giardino', 'tv', 'arredato', 'esposizione', 'kitchen',\n",
    "           'condition', 'land_value',\n",
    "           \n",
    "       'Piscina', \n",
    "       'Idromassaggio',\n",
    "       'Mansarda',\n",
    "       'Cantina',\n",
    "           'Camino',\n",
    "       'has_terrace', 'garage', \n",
    "          \n",
    "           'placeType_Appartamento', 'placeType_Attico / Mansarda',\n",
    "       'placeType_Casa indipendente', 'placeType_Loft / Open Space',\n",
    "       'placeType_Villetta a schiera', \n",
    "           'property_class', 'property_type',\n",
    "           \n",
    "           'locali', 'camere', 'altro', 'bagni', \n",
    "           \n",
    "          ]\n",
    "df[columns] = df[columns].astype(np.float32)\n",
    "df[columns].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sqmt</th>\n",
       "      <th>constructionYear</th>\n",
       "      <th>energyClass</th>\n",
       "      <th>expensesCondominium</th>\n",
       "      <th>price</th>\n",
       "      <th>newFloorNumber</th>\n",
       "      <th>newHeating</th>\n",
       "      <th>portiere</th>\n",
       "      <th>infissi</th>\n",
       "      <th>giardino</th>\n",
       "      <th>...</th>\n",
       "      <th>placeType_Attico / Mansarda</th>\n",
       "      <th>placeType_Casa indipendente</th>\n",
       "      <th>placeType_Loft / Open Space</th>\n",
       "      <th>placeType_Villetta a schiera</th>\n",
       "      <th>property_class</th>\n",
       "      <th>property_type</th>\n",
       "      <th>locali</th>\n",
       "      <th>camere</th>\n",
       "      <th>altro</th>\n",
       "      <th>bagni</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>60623.000000</td>\n",
       "      <td>48003.000000</td>\n",
       "      <td>51079.000000</td>\n",
       "      <td>4.263100e+04</td>\n",
       "      <td>6.062300e+04</td>\n",
       "      <td>58286.000000</td>\n",
       "      <td>32463.000000</td>\n",
       "      <td>60623.000000</td>\n",
       "      <td>27840.000000</td>\n",
       "      <td>60623.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>60623.000000</td>\n",
       "      <td>60623.000000</td>\n",
       "      <td>60623.000000</td>\n",
       "      <td>60623.000000</td>\n",
       "      <td>56845.000000</td>\n",
       "      <td>60623.0</td>\n",
       "      <td>60623.000000</td>\n",
       "      <td>60623.000000</td>\n",
       "      <td>56696.00000</td>\n",
       "      <td>60147.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>105.850037</td>\n",
       "      <td>1949.983398</td>\n",
       "      <td>10.369154</td>\n",
       "      <td>1.507150e+04</td>\n",
       "      <td>3.742299e+05</td>\n",
       "      <td>4.958206</td>\n",
       "      <td>1.811539</td>\n",
       "      <td>0.271992</td>\n",
       "      <td>2.567134</td>\n",
       "      <td>0.260792</td>\n",
       "      <td>...</td>\n",
       "      <td>0.045115</td>\n",
       "      <td>0.011316</td>\n",
       "      <td>0.008743</td>\n",
       "      <td>0.011860</td>\n",
       "      <td>1.296666</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.519638</td>\n",
       "      <td>2.165696</td>\n",
       "      <td>1.57355</td>\n",
       "      <td>1.514822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>58.931396</td>\n",
       "      <td>65.355530</td>\n",
       "      <td>1.301350</td>\n",
       "      <td>2.079448e+06</td>\n",
       "      <td>3.902872e+06</td>\n",
       "      <td>1.418648</td>\n",
       "      <td>0.584307</td>\n",
       "      <td>0.671953</td>\n",
       "      <td>1.939590</td>\n",
       "      <td>0.565916</td>\n",
       "      <td>...</td>\n",
       "      <td>0.207558</td>\n",
       "      <td>0.105773</td>\n",
       "      <td>0.093093</td>\n",
       "      <td>0.108258</td>\n",
       "      <td>0.624977</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.328797</td>\n",
       "      <td>1.176952</td>\n",
       "      <td>8.32677</td>\n",
       "      <td>0.714737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>16.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2.090000e+04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>70.000000</td>\n",
       "      <td>1950.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>5.000000e+01</td>\n",
       "      <td>1.450000e+05</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>90.000000</td>\n",
       "      <td>1960.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>2.400000e+05</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>127.000000</td>\n",
       "      <td>1970.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>1.320000e+02</td>\n",
       "      <td>3.980000e+05</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.00000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>960.000000</td>\n",
       "      <td>2019.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>3.237030e+08</td>\n",
       "      <td>6.750000e+08</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>84.000000</td>\n",
       "      <td>1960.00000</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               sqmt  constructionYear   energyClass  expensesCondominium  \\\n",
       "count  60623.000000      48003.000000  51079.000000         4.263100e+04   \n",
       "mean     105.850037       1949.983398     10.369154         1.507150e+04   \n",
       "std       58.931396         65.355530      1.301350         2.079448e+06   \n",
       "min       16.000000       1000.000000      0.000000         0.000000e+00   \n",
       "25%       70.000000       1950.000000     10.000000         5.000000e+01   \n",
       "50%       90.000000       1960.000000     11.000000         8.000000e+01   \n",
       "75%      127.000000       1970.000000     11.000000         1.320000e+02   \n",
       "max      960.000000       2019.000000     11.000000         3.237030e+08   \n",
       "\n",
       "              price  newFloorNumber    newHeating      portiere       infissi  \\\n",
       "count  6.062300e+04    58286.000000  32463.000000  60623.000000  27840.000000   \n",
       "mean   3.742299e+05        4.958206      1.811539      0.271992      2.567134   \n",
       "std    3.902872e+06        1.418648      0.584307      0.671953      1.939590   \n",
       "min    2.090000e+04        0.000000      0.000000      0.000000      0.000000   \n",
       "25%    1.450000e+05        4.000000      2.000000      0.000000      0.000000   \n",
       "50%    2.400000e+05        6.000000      2.000000      0.000000      3.000000   \n",
       "75%    3.980000e+05        6.000000      2.000000      0.000000      4.000000   \n",
       "max    6.750000e+08        6.000000      2.000000      2.000000      8.000000   \n",
       "\n",
       "           giardino      ...       placeType_Attico / Mansarda  \\\n",
       "count  60623.000000      ...                      60623.000000   \n",
       "mean       0.260792      ...                          0.045115   \n",
       "std        0.565916      ...                          0.207558   \n",
       "min        0.000000      ...                          0.000000   \n",
       "25%        0.000000      ...                          0.000000   \n",
       "50%        0.000000      ...                          0.000000   \n",
       "75%        0.000000      ...                          0.000000   \n",
       "max        2.000000      ...                          1.000000   \n",
       "\n",
       "       placeType_Casa indipendente  placeType_Loft / Open Space  \\\n",
       "count                 60623.000000                 60623.000000   \n",
       "mean                      0.011316                     0.008743   \n",
       "std                       0.105773                     0.093093   \n",
       "min                       0.000000                     0.000000   \n",
       "25%                       0.000000                     0.000000   \n",
       "50%                       0.000000                     0.000000   \n",
       "75%                       0.000000                     0.000000   \n",
       "max                       1.000000                     1.000000   \n",
       "\n",
       "       placeType_Villetta a schiera  property_class  property_type  \\\n",
       "count                  60623.000000    56845.000000        60623.0   \n",
       "mean                       0.011860        1.296666            1.0   \n",
       "std                        0.108258        0.624977            0.0   \n",
       "min                        0.000000        0.000000            1.0   \n",
       "25%                        0.000000        1.000000            1.0   \n",
       "50%                        0.000000        1.000000            1.0   \n",
       "75%                        0.000000        2.000000            1.0   \n",
       "max                        1.000000        2.000000            1.0   \n",
       "\n",
       "             locali        camere        altro         bagni  \n",
       "count  60623.000000  60623.000000  56696.00000  60147.000000  \n",
       "mean       3.519638      2.165696      1.57355      1.514822  \n",
       "std        1.328797      1.176952      8.32677      0.714737  \n",
       "min        1.000000      1.000000      1.00000      1.000000  \n",
       "25%        3.000000      1.000000      1.00000      1.000000  \n",
       "50%        3.000000      2.000000      1.00000      1.000000  \n",
       "75%        4.000000      3.000000      2.00000      2.000000  \n",
       "max       32.000000     84.000000   1960.00000      4.000000  \n",
       "\n",
       "[8 rows x 39 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[columns].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Attach sez2011"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sez2011</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0012720001226</td>\n",
       "      <td>POLYGON ((7.660425319648821 45.05038578651656,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0012720000091</td>\n",
       "      <td>POLYGON ((7.683635179963704 45.06923328757473,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0012720001837</td>\n",
       "      <td>POLYGON ((7.679657280480781 45.10092137770937,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0012720000309</td>\n",
       "      <td>POLYGON ((7.670415391208696 45.06782455187603,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0012720000398</td>\n",
       "      <td>POLYGON ((7.671571572791478 45.05805060715785,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         sez2011                                           geometry\n",
       "0  0012720001226  POLYGON ((7.660425319648821 45.05038578651656,...\n",
       "1  0012720000091  POLYGON ((7.683635179963704 45.06923328757473,...\n",
       "2  0012720001837  POLYGON ((7.679657280480781 45.10092137770937,...\n",
       "3  0012720000309  POLYGON ((7.670415391208696 45.06782455187603,...\n",
       "4  0012720000398  POLYGON ((7.671571572791478 45.05805060715785,..."
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_census = gpd.read_file(\"data/generated_files/dsaa_census_areas.shp\")\n",
    "gdf_census.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sqmt</th>\n",
       "      <th>catasto</th>\n",
       "      <th>climax</th>\n",
       "      <th>constructionYear</th>\n",
       "      <th>energyClass</th>\n",
       "      <th>expensesCondominium</th>\n",
       "      <th>expensesHeating</th>\n",
       "      <th>floorNumber</th>\n",
       "      <th>garage</th>\n",
       "      <th>heating</th>\n",
       "      <th>...</th>\n",
       "      <th>kitchen</th>\n",
       "      <th>condition</th>\n",
       "      <th>class</th>\n",
       "      <th>type</th>\n",
       "      <th>newHeating</th>\n",
       "      <th>Centralizzato</th>\n",
       "      <th>newFloorNumber</th>\n",
       "      <th>ascensore</th>\n",
       "      <th>has_terrace</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>160.0</td>\n",
       "      <td>Classe A/3, rendita € 1.800</td>\n",
       "      <td>Assente</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1/anno</td>\n",
       "      <td>3° di 6 piani, con ascensore</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Centralizzato, a radiatori, alimentato a metano</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>POINT (11.3381 44.4965)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>130.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Con ascensore</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Centralizzato</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>POINT (11.3004 44.4948)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>135.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo, freddo/caldo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3° di 3 piani</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Autonomo, a radiatori, alimentato a gas</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>POINT (11.38 44.47600000000001)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>218.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1° di 3 piani</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>POINT (11.3501 44.4901)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>120.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Autonomo</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ultimo di 6 piani, con ascensore</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Centralizzato</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>POINT (11.3304 44.498)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 90 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    sqmt                      catasto                  climax  \\\n",
       "1  160.0  Classe A/3, rendita € 1.800                 Assente   \n",
       "4  150.0                          NaN                Autonomo   \n",
       "5  135.0                          NaN  Autonomo, freddo/caldo   \n",
       "6  218.0                          NaN                Autonomo   \n",
       "7  120.0                          NaN                Autonomo   \n",
       "\n",
       "   constructionYear  energyClass  expensesCondominium expensesHeating  \\\n",
       "1            1970.0         11.0                  1.0          1/anno   \n",
       "4            2008.0          NaN                130.0             NaN   \n",
       "5               NaN         10.0                 70.0             NaN   \n",
       "6            1600.0          NaN                125.0             NaN   \n",
       "7            1962.0          NaN                250.0             NaN   \n",
       "\n",
       "                        floorNumber  garage  \\\n",
       "1      3° di 6 piani, con ascensore     0.0   \n",
       "4                     Con ascensore     0.0   \n",
       "5                     3° di 3 piani     0.0   \n",
       "6                     1° di 3 piani     1.0   \n",
       "7  Ultimo di 6 piani, con ascensore     0.0   \n",
       "\n",
       "                                           heating  \\\n",
       "1  Centralizzato, a radiatori, alimentato a metano   \n",
       "4                                    Centralizzato   \n",
       "5          Autonomo, a radiatori, alimentato a gas   \n",
       "6                                         Autonomo   \n",
       "7                                    Centralizzato   \n",
       "\n",
       "                ...                kitchen condition class  type newHeating  \\\n",
       "1               ...                    4.0       1.0   NaN   NaN        NaN   \n",
       "4               ...                    4.0       3.0   NaN   NaN        NaN   \n",
       "5               ...                    3.0       3.0   NaN   NaN        2.0   \n",
       "6               ...                    4.0       3.0   NaN   NaN        2.0   \n",
       "7               ...                    4.0       3.0   NaN   NaN        NaN   \n",
       "\n",
       "  Centralizzato  newFloorNumber ascensore  has_terrace  \\\n",
       "1           NaN             6.0       1.0          1.0   \n",
       "4           NaN             6.0       NaN          1.0   \n",
       "5           NaN             6.0       NaN          1.0   \n",
       "6           NaN             4.0       NaN          1.0   \n",
       "7           NaN             6.0       1.0          1.0   \n",
       "\n",
       "                          geometry  \n",
       "1          POINT (11.3381 44.4965)  \n",
       "4          POINT (11.3004 44.4948)  \n",
       "5  POINT (11.38 44.47600000000001)  \n",
       "6          POINT (11.3501 44.4901)  \n",
       "7           POINT (11.3304 44.498)  \n",
       "\n",
       "[5 rows x 90 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from shapely.geometry import Point\n",
    "\n",
    "geometry = [Point(xy) for xy in zip(df.lon, df.lat)]\n",
    "crs = {'init': 'epsg:4326'}\n",
    "gdf = gpd.GeoDataFrame(df.drop(['lon', 'lat'], axis=1), crs=crs, geometry=geometry)\n",
    "gdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/numpy/lib/function_base.py:2831: RuntimeWarning: invalid value encountered in ? (vectorized)\n",
      "  outputs = ufunc(*inputs)\n"
     ]
    }
   ],
   "source": [
    "df_joined = gpd.sjoin(gdf, gdf_census, how=\"inner\", op='within')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['sqmt', 'catasto', 'climax', 'constructionYear', 'energyClass',\n",
       "       'expensesCondominium', 'expensesHeating', 'floorNumber', 'garage',\n",
       "       'heating', 'idListing', 'location', 'name', 'price', 'propertyType',\n",
       "       'rooms', 'url', 'date_listing', 'property_class', 'property_type',\n",
       "       'land_value', 'locali', 'camere', 'altro', 'bagni', 'cucina_a a vista',\n",
       "       'cucina_a abitabile', 'cucina_a angolo cottura',\n",
       "       'cucina_a semi abitabile', 'cucina_otto', 'condition_Buono / Abitabile',\n",
       "       'condition_Da ristrutturare', 'condition_Ottimo/Ristrutturato',\n",
       "       'placeType_Appartamento', 'placeType_Attico / Mansarda',\n",
       "       'placeType_Casa indipendente', 'placeType_Loft / Open Space',\n",
       "       'placeType_Villetta a schiera', '', 'Arredato', 'Balcone', 'Camino',\n",
       "       'Campo da tennis', 'Cancello elettrico', 'Cantina',\n",
       "       'Esposizione doppia', 'Esposizione esterna', 'Esposizione interna',\n",
       "       'Fibra ottica', 'Giardino comune', 'Giardino privato', 'Idromassaggio',\n",
       "       'Impianto allarme', 'Impianto tv centralizzato',\n",
       "       'Impianto tv con parabola satellitare', 'Impianto tv singolo',\n",
       "       'Infissi esterni in doppio vetro / legno',\n",
       "       'Infissi esterni in doppio vetro / metallo',\n",
       "       'Infissi esterni in doppio vetro / pvc',\n",
       "       'Infissi esterni in triplo vetro / legno',\n",
       "       'Infissi esterni in triplo vetro / metallo',\n",
       "       'Infissi esterni in triplo vetro / pvc',\n",
       "       'Infissi esterni in vetro / legno',\n",
       "       'Infissi esterni in vetro / metallo', 'Infissi esterni in vetro / pvc',\n",
       "       'Mansarda', 'Parzialmente arredato', 'Piscina', 'Porta blindata',\n",
       "       'Portiere intera giornata', 'Portiere mezza giornata', 'Taverna',\n",
       "       'Terrazzo', 'Videocitofono', 'infissi', 'portiere', 'giardino',\n",
       "       'arredato', 'esposizione', 'tv', 'kitchen', 'condition', 'class',\n",
       "       'type', 'newHeating', 'Centralizzato', 'newFloorNumber', 'ascensore',\n",
       "       'has_terrace', 'geometry', 'index_right', 'sez2011'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_joined.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Attach OMI context"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"from os import listdir\\nfrom os.path import isfile, join\\n\\nomi_path = 'data/omi'\\nomi_files = [f for f in listdir(omi_path) if isfile(join(omi_path, f)) and '.csv' in f]\\n\\nlist_ = []\\nfor f in omi_files:\\n    filename = omi_path+'/'+f\\n    temp_df = pd.read_csv(filename, index_col=None, header=1, delimiter=';')\\n    list_.append(temp_df)\\nomi_df = pd.concat(list_)\\nomi_df = omi_df.reset_index(drop=True)\\n\\nomi_df['istat'] = omi_df['Comune_ISTAT'].astype(str).str[-5:]\\nomi_df = omi_df[(omi_df['Stato'] == 'NORMALE') & (omi_df['Descr_Tipologia'] == 'Abitazioni civili')]\\nomi_df['med_range'] = omi_df['Compr_max'] - omi_df['Compr_min']\\n\\nomi_df = omi_df[['istat', 'Fascia', 'med_range']].groupby(['istat', 'Fascia'], as_index=False).mean()\\n\\nomi_df = omi_df.pivot(index='istat', columns='Fascia', values='med_range')\\n\\nomi_df = omi_df[['B', 'C', 'D', 'E']]\\nomi_df.columns = ['omi_{}'.format(x) for x in omi_df.columns]\\n\\nomi_df.head()\\n\\ndf_joined['istat'] = df_joined.sez2011.str[1:6]\\ndf_joined = pd.merge(df_joined, omi_df, left_on='istat', right_index=True, how='left')\\n\""
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''from os import listdir\n",
    "from os.path import isfile, join\n",
    "\n",
    "omi_path = 'data/omi'\n",
    "omi_files = [f for f in listdir(omi_path) if isfile(join(omi_path, f)) and '.csv' in f]\n",
    "\n",
    "list_ = []\n",
    "for f in omi_files:\n",
    "    filename = omi_path+'/'+f\n",
    "    temp_df = pd.read_csv(filename, index_col=None, header=1, delimiter=';')\n",
    "    list_.append(temp_df)\n",
    "omi_df = pd.concat(list_)\n",
    "omi_df = omi_df.reset_index(drop=True)\n",
    "\n",
    "omi_df['istat'] = omi_df['Comune_ISTAT'].astype(str).str[-5:]\n",
    "omi_df = omi_df[(omi_df['Stato'] == 'NORMALE') & (omi_df['Descr_Tipologia'] == 'Abitazioni civili')]\n",
    "omi_df['med_range'] = omi_df['Compr_max'] - omi_df['Compr_min']\n",
    "\n",
    "omi_df = omi_df[['istat', 'Fascia', 'med_range']].groupby(['istat', 'Fascia'], as_index=False).mean()\n",
    "\n",
    "omi_df = omi_df.pivot(index='istat', columns='Fascia', values='med_range')\n",
    "\n",
    "omi_df = omi_df[['B', 'C', 'D', 'E']]\n",
    "omi_df.columns = ['omi_{}'.format(x) for x in omi_df.columns]\n",
    "\n",
    "omi_df.head()\n",
    "\n",
    "df_joined['istat'] = df_joined.sez2011.str[1:6]\n",
    "df_joined = pd.merge(df_joined, omi_df, left_on='istat', right_index=True, how='left')\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_joined = df_joined.set_index('sez2011')\n",
    "df_joined[columns + omi_df.columns.tolist()].to_parquet('data/generated_files/selling_houses.parquet')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}