code/mm_geofactor.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Geoencoding of Social Housing dataset\n",
    "\n",
    "This notebook provides geocoding for the dataset provided by MM where all public houses owned by Comune di Milano are reported. The pdf can be retried at this [link](https://casa.mmspa.eu/wps/portal/pon/case/home/patrimonio/alloggi/!ut/p/z1/04_Sj9CPykssy0xPLMnMz0vMAfIjo8zifQ0sjdwtTIx83f0NzQ0c_SzcDEKCfIz8_c30w1EVWHiYGRkEeod5-AUEBxkYOhvrRxGj3wAHcDQgTj8eBVH4jQ_Xj8JnhYW3OboCC3N3oM6w4GBD08BAAzc3YwwFmGFAyBUFuaGhEQaZnumOiooA8-SPZw!!/dz/d5/L2dBISEvZ0FBIS9nQSEh/)\n",
    "\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from tabula.io import read_pdf\n",
    "from functools import reduce\n",
    "from geopy.geocoders import Nominatim\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "Importing and converting the dataset for pdf"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [],
   "source": [
    "pdf_list=read_pdf('../datasets/elenco.pdf', pages='all')"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "outputs": [
    {
     "data": {
      "text/plain": "         _id  CODICE_VIA  NUMERO LETTERA BARRA BARRA2 NUMEROCOMPLETO  \\\n0          1           1      15     NaN          N06          15N06   \n1          2           1      15     NaN   NaN    N05          15N05   \n2          3           1      17     NaN   NaN    N02          17N02   \n3          4           1      17     NaN   NaN    N04          17N04   \n4          5           1      13     NaN   NaN    N08          13N08   \n...      ...         ...     ...     ...   ...    ...            ...   \n63324  63325        9001       5     NaN   NaN    NaN              5   \n63325  63326        9001       7     NaN   NaN    NaN              7   \n63326  63327        9001       7     NaN   NaN    P01           7P01   \n63327  63328        9001       9     NaN   NaN    NaN              9   \n63328  63329        9002       1     NaN   NaN    NaN              1   \n\n       MUNICIPIO  RESIDENZIALE    STATOCIVICO  ...  ANNO_SOPPRESSIONE  \\\n0              1           0.0  Iter in corso  ...                  0   \n1              1           0.0  Iter in corso  ...                  0   \n2              1           0.0  Iter in corso  ...                  0   \n3              1           0.0  Iter in corso  ...                  0   \n4              1           0.0  Iter in corso  ...                  0   \n...          ...           ...            ...  ...                ...   \n63324          8           0.0  Iter in corso  ...                  0   \n63325          8           0.0  Iter in corso  ...                  0   \n63326          8           0.0  Iter in corso  ...                  0   \n63327          8           0.0  Iter in corso  ...                  0   \n63328          8           0.0  Iter in corso  ...                  0   \n\n                DESCRITTIVO                ANNCSU          OPENSTREETMAP  \\\n0                 DEL DUOMO             DEL DUOMO              DEL DUOMO   \n1                 DEL DUOMO             DEL DUOMO              DEL DUOMO   \n2                 DEL DUOMO             DEL DUOMO              DEL DUOMO   \n3                 DEL DUOMO             DEL DUOMO              DEL DUOMO   \n4                 DEL DUOMO             DEL DUOMO              DEL DUOMO   \n...                     ...                   ...                    ...   \n63324             EXPO 2015             EXPO 2015  EXPO DUEMILAEQUINDICI   \n63325             EXPO 2015             EXPO 2015  EXPO DUEMILAEQUINDICI   \n63326             EXPO 2015             EXPO 2015  EXPO DUEMILAEQUINDICI   \n63327             EXPO 2015             EXPO 2015  EXPO DUEMILAEQUINDICI   \n63328  RITA LEVI-MONTALCINI  RITA LEVI-MONTALCINI   RITA LEVI-MONTALCINI   \n\n      PROGANNCSU ID_NIL              NIL LONG_WGS84  LAT_WGS84  \\\n0            NaN    1.0            DUOMO   9.188402  45.464450   \n1            NaN    1.0            DUOMO   9.188401  45.464425   \n2            NaN    1.0            DUOMO   9.188401  45.464507   \n3            NaN    1.0            DUOMO   9.188404  45.464620   \n4            NaN    1.0            DUOMO   9.188402  45.464135   \n...          ...    ...              ...        ...        ...   \n63324        NaN   73.0  CASCINA MERLATA   9.106231  45.520534   \n63325        NaN   73.0  CASCINA MERLATA   9.106361  45.521008   \n63326        NaN   73.0  CASCINA MERLATA   9.106510  45.521164   \n63327        NaN   73.0  CASCINA MERLATA   9.106836  45.521303   \n63328        NaN   73.0  CASCINA MERLATA   9.106345  45.520029   \n\n                                      Location  \n0               (45.4644496019, 9.18840177503)  \n1            (45.4644249493, 9.18840120369999)  \n2            (45.4645074643, 9.18840120369999)  \n3            (45.4646199845, 9.18840357973999)  \n4            (45.4641348954, 9.18840239216999)  \n...                                        ...  \n63324  (45.520533963355554, 9.106230762519976)  \n63325   (45.52100771095243, 9.106360718677317)  \n63326    (45.52116436208506, 9.10650981676278)  \n63327    (45.52130273064841, 9.10683571224355)  \n63328   (45.52002853927067, 9.106345407794322)  \n\n[63329 rows x 38 columns]",
      "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>_id</th>\n      <th>CODICE_VIA</th>\n      <th>NUMERO</th>\n      <th>LETTERA</th>\n      <th>BARRA</th>\n      <th>BARRA2</th>\n      <th>NUMEROCOMPLETO</th>\n      <th>MUNICIPIO</th>\n      <th>RESIDENZIALE</th>\n      <th>STATOCIVICO</th>\n      <th>...</th>\n      <th>ANNO_SOPPRESSIONE</th>\n      <th>DESCRITTIVO</th>\n      <th>ANNCSU</th>\n      <th>OPENSTREETMAP</th>\n      <th>PROGANNCSU</th>\n      <th>ID_NIL</th>\n      <th>NIL</th>\n      <th>LONG_WGS84</th>\n      <th>LAT_WGS84</th>\n      <th>Location</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>1</td>\n      <td>15</td>\n      <td>NaN</td>\n      <td></td>\n      <td>N06</td>\n      <td>15N06</td>\n      <td>1</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>NaN</td>\n      <td>1.0</td>\n      <td>DUOMO</td>\n      <td>9.188402</td>\n      <td>45.464450</td>\n      <td>(45.4644496019, 9.18840177503)</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>1</td>\n      <td>15</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>N05</td>\n      <td>15N05</td>\n      <td>1</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>NaN</td>\n      <td>1.0</td>\n      <td>DUOMO</td>\n      <td>9.188401</td>\n      <td>45.464425</td>\n      <td>(45.4644249493, 9.18840120369999)</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>1</td>\n      <td>17</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>N02</td>\n      <td>17N02</td>\n      <td>1</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>NaN</td>\n      <td>1.0</td>\n      <td>DUOMO</td>\n      <td>9.188401</td>\n      <td>45.464507</td>\n      <td>(45.4645074643, 9.18840120369999)</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>1</td>\n      <td>17</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>N04</td>\n      <td>17N04</td>\n      <td>1</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>NaN</td>\n      <td>1.0</td>\n      <td>DUOMO</td>\n      <td>9.188404</td>\n      <td>45.464620</td>\n      <td>(45.4646199845, 9.18840357973999)</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>1</td>\n      <td>13</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>N08</td>\n      <td>13N08</td>\n      <td>1</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>DEL DUOMO</td>\n      <td>NaN</td>\n      <td>1.0</td>\n      <td>DUOMO</td>\n      <td>9.188402</td>\n      <td>45.464135</td>\n      <td>(45.4641348954, 9.18840239216999)</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>63324</th>\n      <td>63325</td>\n      <td>9001</td>\n      <td>5</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>5</td>\n      <td>8</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>EXPO 2015</td>\n      <td>EXPO 2015</td>\n      <td>EXPO DUEMILAEQUINDICI</td>\n      <td>NaN</td>\n      <td>73.0</td>\n      <td>CASCINA MERLATA</td>\n      <td>9.106231</td>\n      <td>45.520534</td>\n      <td>(45.520533963355554, 9.106230762519976)</td>\n    </tr>\n    <tr>\n      <th>63325</th>\n      <td>63326</td>\n      <td>9001</td>\n      <td>7</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>8</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>EXPO 2015</td>\n      <td>EXPO 2015</td>\n      <td>EXPO DUEMILAEQUINDICI</td>\n      <td>NaN</td>\n      <td>73.0</td>\n      <td>CASCINA MERLATA</td>\n      <td>9.106361</td>\n      <td>45.521008</td>\n      <td>(45.52100771095243, 9.106360718677317)</td>\n    </tr>\n    <tr>\n      <th>63326</th>\n      <td>63327</td>\n      <td>9001</td>\n      <td>7</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>P01</td>\n      <td>7P01</td>\n      <td>8</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>EXPO 2015</td>\n      <td>EXPO 2015</td>\n      <td>EXPO DUEMILAEQUINDICI</td>\n      <td>NaN</td>\n      <td>73.0</td>\n      <td>CASCINA MERLATA</td>\n      <td>9.106510</td>\n      <td>45.521164</td>\n      <td>(45.52116436208506, 9.10650981676278)</td>\n    </tr>\n    <tr>\n      <th>63327</th>\n      <td>63328</td>\n      <td>9001</td>\n      <td>9</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>9</td>\n      <td>8</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>EXPO 2015</td>\n      <td>EXPO 2015</td>\n      <td>EXPO DUEMILAEQUINDICI</td>\n      <td>NaN</td>\n      <td>73.0</td>\n      <td>CASCINA MERLATA</td>\n      <td>9.106836</td>\n      <td>45.521303</td>\n      <td>(45.52130273064841, 9.10683571224355)</td>\n    </tr>\n    <tr>\n      <th>63328</th>\n      <td>63329</td>\n      <td>9002</td>\n      <td>1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>8</td>\n      <td>0.0</td>\n      <td>Iter in corso</td>\n      <td>...</td>\n      <td>0</td>\n      <td>RITA LEVI-MONTALCINI</td>\n      <td>RITA LEVI-MONTALCINI</td>\n      <td>RITA LEVI-MONTALCINI</td>\n      <td>NaN</td>\n      <td>73.0</td>\n      <td>CASCINA MERLATA</td>\n      <td>9.106345</td>\n      <td>45.520029</td>\n      <td>(45.52002853927067, 9.106345407794322)</td>\n    </tr>\n  </tbody>\n</table>\n<p>63329 rows × 38 columns</p>\n</div>"
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indirizzi_df=pd.read_csv('../datasets/indirizzi.csv')\n",
    "indirizzi_df"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "   CODICE COMPLESSO DENOMINAZIONE COMPLESSO  CODICE EDIFICIO  \\\n0            100017                   PONTI         10001708   \n1            100017                   PONTI         10001709   \n2            100017                   PONTI         10001710   \n3            100017                   PONTI         10001711   \n4            100017                   PONTI         10001712   \n\n      DENOMINAZIONE EDIFICIO         INDIRIZZO CIVICO  COMUNE CONDOMINIO  \n0  DEL TURCHINO 20 C-D (VIA)  VIA DEL TURCHINO     20  MILANO        NaN  \n1  DEL TURCHINO 20 E-F (VIA)  VIA DEL TURCHINO     20  MILANO        NaN  \n2    DEL TURCHINO 20 G (VIA)  VIA DEL TURCHINO     20  MILANO        NaN  \n3    DEL TURCHINO 20 H (VIA)  VIA DEL TURCHINO     20  MILANO        NaN  \n4    DEL TURCHINO 22 A (VIA)  VIA DEL TURCHINO     22  MILANO        NaN  ",
      "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>CODICE COMPLESSO</th>\n      <th>DENOMINAZIONE COMPLESSO</th>\n      <th>CODICE EDIFICIO</th>\n      <th>DENOMINAZIONE EDIFICIO</th>\n      <th>INDIRIZZO</th>\n      <th>CIVICO</th>\n      <th>COMUNE</th>\n      <th>CONDOMINIO</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001708</td>\n      <td>DEL TURCHINO 20 C-D (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001709</td>\n      <td>DEL TURCHINO 20 E-F (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001710</td>\n      <td>DEL TURCHINO 20 G (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001711</td>\n      <td>DEL TURCHINO 20 H (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001712</td>\n      <td>DEL TURCHINO 22 A (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>22</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(pdf_list)\n",
    "pdf_list[1].columns\n",
    "df=pd.concat(pdf_list[1:13], axis=0)\n",
    "df.columns=['CODICE COMPLESSO', 'DENOMINAZIONE COMPLESSO', 'CODICE EDIFICIO',\n",
    "            'DENOMINAZIONE EDIFICIO', 'INDIRIZZO', 'CIVICO', 'COMUNE', 'CONDOMINIO']\n",
    "df.head()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "980"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "   CODICE COMPLESSO DENOMINAZIONE COMPLESSO  CODICE EDIFICIO  \\\n0            100017                   PONTI         10001708   \n1            100017                   PONTI         10001709   \n2            100017                   PONTI         10001710   \n3            100017                   PONTI         10001711   \n4            100017                   PONTI         10001712   \n\n      DENOMINAZIONE EDIFICIO         INDIRIZZO CIVICO  COMUNE CONDOMINIO  \\\n0  DEL TURCHINO 20 C-D (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n1  DEL TURCHINO 20 E-F (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n2    DEL TURCHINO 20 G (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n3    DEL TURCHINO 20 H (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n4    DEL TURCHINO 22 A (VIA)  VIA DEL TURCHINO     22  MILANO        NaN   \n\n                 full_address  \n0  VIA DEL TURCHINO 20 MILANO  \n1  VIA DEL TURCHINO 20 MILANO  \n2  VIA DEL TURCHINO 20 MILANO  \n3  VIA DEL TURCHINO 20 MILANO  \n4  VIA DEL TURCHINO 22 MILANO  ",
      "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>CODICE COMPLESSO</th>\n      <th>DENOMINAZIONE COMPLESSO</th>\n      <th>CODICE EDIFICIO</th>\n      <th>DENOMINAZIONE EDIFICIO</th>\n      <th>INDIRIZZO</th>\n      <th>CIVICO</th>\n      <th>COMUNE</th>\n      <th>CONDOMINIO</th>\n      <th>full_address</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001708</td>\n      <td>DEL TURCHINO 20 C-D (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001709</td>\n      <td>DEL TURCHINO 20 E-F (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001710</td>\n      <td>DEL TURCHINO 20 G (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001711</td>\n      <td>DEL TURCHINO 20 H (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001712</td>\n      <td>DEL TURCHINO 22 A (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>22</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 22 MILANO</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['full_address']=df['INDIRIZZO']+\" \"+df['CIVICO'].astype(str)+\" \"+df['COMUNE']\n",
    "df.head()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Geoeocnoding\n",
    "\n",
    "We geoencode the dataset using nomatim (that uses OSM data)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "outputs": [],
   "source": [
    "geolocator = Nominatim(user_agent=\"myApp\")\n",
    "\n",
    "def geo_lat(lat):\n",
    "    try:\n",
    "        return geolocator.geocode(lat).latitude\n",
    "    except:\n",
    "        return np.nan\n",
    "\n",
    "def geo_long(long):\n",
    "    try:\n",
    "        return geolocator.geocode(long).longitude\n",
    "    except:\n",
    "        return np.nan"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 2.08 s\n",
      "Wall time: 16min 22s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df['lat']=df['full_address'].apply(lambda x: geo_lat(x))\n",
    "df['lon']=df['full_address'].apply(lambda x: geo_long(x))"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Not found addresses"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [
    {
     "data": {
      "text/plain": "116"
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.lat.isna().sum()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "outputs": [
    {
     "data": {
      "text/plain": "    CODICE COMPLESSO DENOMINAZIONE COMPLESSO  CODICE EDIFICIO  \\\n84            100027                  LAMBRO         10002701   \n40            100040        STAMIRA D'ANCONA         10004001   \n41            100040        STAMIRA D'ANCONA         10004002   \n69            100046            CHIESA ROSSA         10004623   \n21            100048                ANTONINI         10004801   \n..               ...                     ...              ...   \n69            510237               DE ANDRE'         51023704   \n70            510237               DE ANDRE'         51023705   \n79            510386          FRATELLI BOZZI         51038601   \n87            510388           CIVITAVECCHIA         51038806   \n90            510391               DE LEMENE         51039101   \n\n                  DENOMINAZIONE EDIFICIO                 INDIRIZZO  \\\n84         UCELLI DI NEMI GUIDO 58 (VIA)  VIA UCELLI DI NEMI GUIDO   \n40           DEI VALTORTA 57 A-B-C (VIA)          VIA DEI VALTORTA   \n41           DEI VALTORTA 57 D-E-F (VIA)          VIA DEI VALTORTA   \n69          S. ABBONDIO 19-21-23-25 VIA)         VIA SANT'ABBONDIO   \n21               NEGRI ADA 2 A-B-C (VIA)             VIA NEGRI ADA   \n..                                   ...                       ...   \n69          DE ANDRE' 12 (VIA) - TORRE 3    VIA DE ANDRE' FABRIZIO   \n70          DE ANDRE' 12 (VIA) - TORRE 4    VIA DE ANDRE' FABRIZIO   \n79               FRATELLI BOZZI 23 (VIA)           VIA F.LLI BOZZI   \n87  CIVITAVECCHIA, 100-104 (AUTORIMESSA)         VIA CIVITAVECCHIA   \n90                    DE LEMENE 55 (VIA)   VIA DE LEMENE FRANCESCO   \n\n         CIVICO  COMUNE CONDOMINIO                          full_address  lat  \\\n84           58  MILANO        NaN    VIA UCELLI DI NEMI GUIDO 58 MILANO  NaN   \n40           57  MILANO         SI            VIA DEI VALTORTA 57 MILANO  NaN   \n41           57  MILANO         SI            VIA DEI VALTORTA 57 MILANO  NaN   \n69   19-21-23-2  MILANO         SI   VIA SANT'ABBONDIO 19-21-23-2 MILANO  NaN   \n21            2  MILANO         SI                VIA NEGRI ADA 2 MILANO  NaN   \n..          ...     ...        ...                                   ...  ...   \n69           12  MILANO        NaN      VIA DE ANDRE' FABRIZIO 12 MILANO  NaN   \n70           12  MILANO        NaN      VIA DE ANDRE' FABRIZIO 12 MILANO  NaN   \n79           23  MILANO        NaN             VIA F.LLI BOZZI 23 MILANO  NaN   \n87  100,102,104  MILANO        NaN  VIA CIVITAVECCHIA 100,102,104 MILANO  NaN   \n90           55  MILANO        NaN     VIA DE LEMENE FRANCESCO 55 MILANO  NaN   \n\n    lon  \n84  NaN  \n40  NaN  \n41  NaN  \n69  NaN  \n21  NaN  \n..  ...  \n69  NaN  \n70  NaN  \n79  NaN  \n87  NaN  \n90  NaN  \n\n[117 rows x 11 columns]",
      "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>CODICE COMPLESSO</th>\n      <th>DENOMINAZIONE COMPLESSO</th>\n      <th>CODICE EDIFICIO</th>\n      <th>DENOMINAZIONE EDIFICIO</th>\n      <th>INDIRIZZO</th>\n      <th>CIVICO</th>\n      <th>COMUNE</th>\n      <th>CONDOMINIO</th>\n      <th>full_address</th>\n      <th>lat</th>\n      <th>lon</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>84</th>\n      <td>100027</td>\n      <td>LAMBRO</td>\n      <td>10002701</td>\n      <td>UCELLI DI NEMI GUIDO 58 (VIA)</td>\n      <td>VIA UCELLI DI NEMI GUIDO</td>\n      <td>58</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA UCELLI DI NEMI GUIDO 58 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>40</th>\n      <td>100040</td>\n      <td>STAMIRA D'ANCONA</td>\n      <td>10004001</td>\n      <td>DEI VALTORTA 57 A-B-C (VIA)</td>\n      <td>VIA DEI VALTORTA</td>\n      <td>57</td>\n      <td>MILANO</td>\n      <td>SI</td>\n      <td>VIA DEI VALTORTA 57 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>41</th>\n      <td>100040</td>\n      <td>STAMIRA D'ANCONA</td>\n      <td>10004002</td>\n      <td>DEI VALTORTA 57 D-E-F (VIA)</td>\n      <td>VIA DEI VALTORTA</td>\n      <td>57</td>\n      <td>MILANO</td>\n      <td>SI</td>\n      <td>VIA DEI VALTORTA 57 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>69</th>\n      <td>100046</td>\n      <td>CHIESA ROSSA</td>\n      <td>10004623</td>\n      <td>S. ABBONDIO 19-21-23-25 VIA)</td>\n      <td>VIA SANT'ABBONDIO</td>\n      <td>19-21-23-2</td>\n      <td>MILANO</td>\n      <td>SI</td>\n      <td>VIA SANT'ABBONDIO 19-21-23-2 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>21</th>\n      <td>100048</td>\n      <td>ANTONINI</td>\n      <td>10004801</td>\n      <td>NEGRI ADA 2 A-B-C (VIA)</td>\n      <td>VIA NEGRI ADA</td>\n      <td>2</td>\n      <td>MILANO</td>\n      <td>SI</td>\n      <td>VIA NEGRI ADA 2 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>69</th>\n      <td>510237</td>\n      <td>DE ANDRE'</td>\n      <td>51023704</td>\n      <td>DE ANDRE' 12 (VIA) - TORRE 3</td>\n      <td>VIA DE ANDRE' FABRIZIO</td>\n      <td>12</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DE ANDRE' FABRIZIO 12 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>70</th>\n      <td>510237</td>\n      <td>DE ANDRE'</td>\n      <td>51023705</td>\n      <td>DE ANDRE' 12 (VIA) - TORRE 4</td>\n      <td>VIA DE ANDRE' FABRIZIO</td>\n      <td>12</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DE ANDRE' FABRIZIO 12 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>79</th>\n      <td>510386</td>\n      <td>FRATELLI BOZZI</td>\n      <td>51038601</td>\n      <td>FRATELLI BOZZI 23 (VIA)</td>\n      <td>VIA F.LLI BOZZI</td>\n      <td>23</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA F.LLI BOZZI 23 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>87</th>\n      <td>510388</td>\n      <td>CIVITAVECCHIA</td>\n      <td>51038806</td>\n      <td>CIVITAVECCHIA, 100-104 (AUTORIMESSA)</td>\n      <td>VIA CIVITAVECCHIA</td>\n      <td>100,102,104</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA CIVITAVECCHIA 100,102,104 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>90</th>\n      <td>510391</td>\n      <td>DE LEMENE</td>\n      <td>51039101</td>\n      <td>DE LEMENE 55 (VIA)</td>\n      <td>VIA DE LEMENE FRANCESCO</td>\n      <td>55</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DE LEMENE FRANCESCO 55 MILANO</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>117 rows × 11 columns</p>\n</div>"
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_notfound=df[df.lat.isna()]\n",
    "df_notfound"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\iodio\\AppData\\Local\\Temp\\ipykernel_13404\\3291439383.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  indirizzi_df['INDIRIZZO']=indirizzi_df['TIPO'].str.upper()+\" \"+indirizzi_df['DENOMINAZIONE']+\" \"+indirizzi_df['NUMERO'].astype(str)\n"
     ]
    }
   ],
   "source": [
    "indirizzi_df=indirizzi_df[['NUMERO','TIPO','DENOMINAZIONE','LONG_WGS84', 'LAT_WGS84']]\n",
    "indirizzi_df['INDIRIZZO']=indirizzi_df['TIPO'].str.upper()+\" \"+indirizzi_df['DENOMINAZIONE']+\" \"+indirizzi_df['NUMERO'].astype(str)\n",
    "indirizzi_df=indirizzi_df[['INDIRIZZO','LONG_WGS84', 'LAT_WGS84']]\n",
    "indirizzi_df=indirizzi_df.drop_duplicates(subset='INDIRIZZO')"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\iodio\\AppData\\Local\\Temp\\ipykernel_13404\\2296340904.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_notfound['INDIRIZZO-N']=df_notfound['INDIRIZZO']+\" \"+df_notfound[\"CIVICO\"].astype(str)\n"
     ]
    }
   ],
   "source": [
    "df_notfound['INDIRIZZO-N']=df_notfound['INDIRIZZO']+\" \"+df_notfound[\"CIVICO\"].astype(str)\n",
    "df_notfound=pd.merge(indirizzi_df, df_notfound, how='right', left_on=['INDIRIZZO'],suffixes=('_ind',''), right_on=['INDIRIZZO-N'], indicator=True)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'LONG_WGS84'",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mKeyError\u001B[0m                                  Traceback (most recent call last)",
      "File \u001B[1;32mc:\\users\\iodio\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\pandas\\core\\indexes\\base.py:3803\u001B[0m, in \u001B[0;36mIndex.get_loc\u001B[1;34m(self, key, method, tolerance)\u001B[0m\n\u001B[0;32m   3802\u001B[0m \u001B[38;5;28;01mtry\u001B[39;00m:\n\u001B[1;32m-> 3803\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_engine\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mget_loc\u001B[49m\u001B[43m(\u001B[49m\u001B[43mcasted_key\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m   3804\u001B[0m \u001B[38;5;28;01mexcept\u001B[39;00m \u001B[38;5;167;01mKeyError\u001B[39;00m \u001B[38;5;28;01mas\u001B[39;00m err:\n",
      "File \u001B[1;32mc:\\users\\iodio\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\pandas\\_libs\\index.pyx:138\u001B[0m, in \u001B[0;36mpandas._libs.index.IndexEngine.get_loc\u001B[1;34m()\u001B[0m\n",
      "File \u001B[1;32mc:\\users\\iodio\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\pandas\\_libs\\index.pyx:165\u001B[0m, in \u001B[0;36mpandas._libs.index.IndexEngine.get_loc\u001B[1;34m()\u001B[0m\n",
      "File \u001B[1;32mpandas\\_libs\\hashtable_class_helper.pxi:5745\u001B[0m, in \u001B[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001B[1;34m()\u001B[0m\n",
      "File \u001B[1;32mpandas\\_libs\\hashtable_class_helper.pxi:5753\u001B[0m, in \u001B[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001B[1;34m()\u001B[0m\n",
      "\u001B[1;31mKeyError\u001B[0m: 'LONG_WGS84'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001B[1;31mKeyError\u001B[0m                                  Traceback (most recent call last)",
      "Cell \u001B[1;32mIn [134], line 1\u001B[0m\n\u001B[1;32m----> 1\u001B[0m df_notfound[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mlon\u001B[39m\u001B[38;5;124m'\u001B[39m]\u001B[38;5;241m=\u001B[39m\u001B[43mdf_notfound\u001B[49m\u001B[43m[\u001B[49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[38;5;124;43mLONG_WGS84\u001B[39;49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[43m]\u001B[49m\n\u001B[0;32m      2\u001B[0m df_notfound[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mlat\u001B[39m\u001B[38;5;124m'\u001B[39m]\u001B[38;5;241m=\u001B[39mdf_notfound[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mLAT_WGS84\u001B[39m\u001B[38;5;124m'\u001B[39m]\n\u001B[0;32m      3\u001B[0m df_notfound\u001B[38;5;241m=\u001B[39mdf_notfound\u001B[38;5;241m.\u001B[39mdrop([\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mINDIRIZZO_ind\u001B[39m\u001B[38;5;124m'\u001B[39m,\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mLONG_WGS84\u001B[39m\u001B[38;5;124m'\u001B[39m,\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mLAT_WGS84\u001B[39m\u001B[38;5;124m'\u001B[39m,\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mINDIRIZZO-N\u001B[39m\u001B[38;5;124m'\u001B[39m,\u001B[38;5;124m'\u001B[39m\u001B[38;5;124m_merge\u001B[39m\u001B[38;5;124m'\u001B[39m], axis\u001B[38;5;241m=\u001B[39m\u001B[38;5;241m1\u001B[39m)\n",
      "File \u001B[1;32mc:\\users\\iodio\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\pandas\\core\\frame.py:3804\u001B[0m, in \u001B[0;36mDataFrame.__getitem__\u001B[1;34m(self, key)\u001B[0m\n\u001B[0;32m   3802\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mcolumns\u001B[38;5;241m.\u001B[39mnlevels \u001B[38;5;241m>\u001B[39m \u001B[38;5;241m1\u001B[39m:\n\u001B[0;32m   3803\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_getitem_multilevel(key)\n\u001B[1;32m-> 3804\u001B[0m indexer \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mcolumns\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mget_loc\u001B[49m\u001B[43m(\u001B[49m\u001B[43mkey\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m   3805\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m is_integer(indexer):\n\u001B[0;32m   3806\u001B[0m     indexer \u001B[38;5;241m=\u001B[39m [indexer]\n",
      "File \u001B[1;32mc:\\users\\iodio\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\pandas\\core\\indexes\\base.py:3805\u001B[0m, in \u001B[0;36mIndex.get_loc\u001B[1;34m(self, key, method, tolerance)\u001B[0m\n\u001B[0;32m   3803\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_engine\u001B[38;5;241m.\u001B[39mget_loc(casted_key)\n\u001B[0;32m   3804\u001B[0m \u001B[38;5;28;01mexcept\u001B[39;00m \u001B[38;5;167;01mKeyError\u001B[39;00m \u001B[38;5;28;01mas\u001B[39;00m err:\n\u001B[1;32m-> 3805\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mKeyError\u001B[39;00m(key) \u001B[38;5;28;01mfrom\u001B[39;00m \u001B[38;5;21;01merr\u001B[39;00m\n\u001B[0;32m   3806\u001B[0m \u001B[38;5;28;01mexcept\u001B[39;00m \u001B[38;5;167;01mTypeError\u001B[39;00m:\n\u001B[0;32m   3807\u001B[0m     \u001B[38;5;66;03m# If we have a listlike key, _check_indexing_error will raise\u001B[39;00m\n\u001B[0;32m   3808\u001B[0m     \u001B[38;5;66;03m#  InvalidIndexError. Otherwise we fall through and re-raise\u001B[39;00m\n\u001B[0;32m   3809\u001B[0m     \u001B[38;5;66;03m#  the TypeError.\u001B[39;00m\n\u001B[0;32m   3810\u001B[0m     \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_check_indexing_error(key)\n",
      "\u001B[1;31mKeyError\u001B[0m: 'LONG_WGS84'"
     ]
    }
   ],
   "source": [
    "df_notfound['lon']=df_notfound['LONG_WGS84']\n",
    "df_notfound['lat']=df_notfound['LAT_WGS84']\n",
    "df_notfound=df_notfound.drop(['INDIRIZZO_ind','LONG_WGS84','LAT_WGS84','INDIRIZZO-N','_merge'], axis=1)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "outputs": [
    {
     "data": {
      "text/plain": "     CODICE COMPLESSO DENOMINAZIONE COMPLESSO  CODICE EDIFICIO  \\\n0              100017                   PONTI         10001708   \n1              100017                   PONTI         10001709   \n2              100017                   PONTI         10001710   \n3              100017                   PONTI         10001711   \n4              100017                   PONTI         10001712   \n..                ...                     ...              ...   \n975            510394              SENIGALLIA         51039405   \n976            510394              SENIGALLIA         51039406   \n977            510394              SENIGALLIA         51039407   \n978            510394              SENIGALLIA         51039408   \n979            510394              SENIGALLIA         51039410   \n\n        DENOMINAZIONE EDIFICIO         INDIRIZZO CIVICO  COMUNE CONDOMINIO  \\\n0    DEL TURCHINO 20 C-D (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n1    DEL TURCHINO 20 E-F (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n2      DEL TURCHINO 20 G (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n3      DEL TURCHINO 20 H (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n4      DEL TURCHINO 22 A (VIA)  VIA DEL TURCHINO     22  MILANO        NaN   \n..                         ...               ...    ...     ...        ...   \n975       SENIGALLIA - CORPO F    VIA SENIGALLIA   60/G  MILANO        NaN   \n976      SENIGALLIA - CORPO P1    VIA SENIGALLIA     60  MILANO        NaN   \n977      SENIGALLIA - CORPO P2    VIA SENIGALLIA     60  MILANO        NaN   \n978      SENIGALLIA - CORPO P3    VIA SENIGALLIA     60  MILANO        NaN   \n979        POSTI AUTO SCOPERTI    VIA SENIGALLIA     60  MILANO        NaN   \n\n                   full_address        lat       lon  latnf  lonnf  latnf  \\\n0    VIA DEL TURCHINO 20 MILANO  45.451893  9.228053    NaN    NaN    NaN   \n1    VIA DEL TURCHINO 20 MILANO  45.451893  9.228053    NaN    NaN    NaN   \n2    VIA DEL TURCHINO 20 MILANO  45.451893  9.228053    NaN    NaN    NaN   \n3    VIA DEL TURCHINO 20 MILANO  45.451893  9.228053    NaN    NaN    NaN   \n4    VIA DEL TURCHINO 22 MILANO  45.451893  9.228053    NaN    NaN    NaN   \n..                          ...        ...       ...    ...    ...    ...   \n975  VIA SENIGALLIA 60/G MILANO  45.532143  9.168970    NaN    NaN    NaN   \n976    VIA SENIGALLIA 60 MILANO  45.532143  9.168970    NaN    NaN    NaN   \n977    VIA SENIGALLIA 60 MILANO  45.532143  9.168970    NaN    NaN    NaN   \n978    VIA SENIGALLIA 60 MILANO  45.532143  9.168970    NaN    NaN    NaN   \n979    VIA SENIGALLIA 60 MILANO  45.532143  9.168970    NaN    NaN    NaN   \n\n     lonnf  lat_nf  lon_nf  \n0      NaN     NaN     NaN  \n1      NaN     NaN     NaN  \n2      NaN     NaN     NaN  \n3      NaN     NaN     NaN  \n4      NaN     NaN     NaN  \n..     ...     ...     ...  \n975    NaN     NaN     NaN  \n976    NaN     NaN     NaN  \n977    NaN     NaN     NaN  \n978    NaN     NaN     NaN  \n979    NaN     NaN     NaN  \n\n[980 rows x 17 columns]",
      "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>CODICE COMPLESSO</th>\n      <th>DENOMINAZIONE COMPLESSO</th>\n      <th>CODICE EDIFICIO</th>\n      <th>DENOMINAZIONE EDIFICIO</th>\n      <th>INDIRIZZO</th>\n      <th>CIVICO</th>\n      <th>COMUNE</th>\n      <th>CONDOMINIO</th>\n      <th>full_address</th>\n      <th>lat</th>\n      <th>lon</th>\n      <th>latnf</th>\n      <th>lonnf</th>\n      <th>latnf</th>\n      <th>lonnf</th>\n      <th>lat_nf</th>\n      <th>lon_nf</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001708</td>\n      <td>DEL TURCHINO 20 C-D (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001709</td>\n      <td>DEL TURCHINO 20 E-F (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001710</td>\n      <td>DEL TURCHINO 20 G (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001711</td>\n      <td>DEL TURCHINO 20 H (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001712</td>\n      <td>DEL TURCHINO 22 A (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>22</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 22 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>975</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039405</td>\n      <td>SENIGALLIA - CORPO F</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60/G</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60/G MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>976</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039406</td>\n      <td>SENIGALLIA - CORPO P1</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60 MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>977</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039407</td>\n      <td>SENIGALLIA - CORPO P2</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60 MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>978</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039408</td>\n      <td>SENIGALLIA - CORPO P3</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60 MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>979</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039410</td>\n      <td>POSTI AUTO SCOPERTI</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60 MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>980 rows × 17 columns</p>\n</div>"
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_notfound=df_notfound[df_notfound['lat'].isna()==False]\n",
    "df_notfound=df_notfound[['CODICE EDIFICIO','lat','lon']]\n",
    "df=df.merge(df_notfound, on='CODICE EDIFICIO', how='left', suffixes=('', '_nf'))\n",
    "df.head()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "outputs": [],
   "source": [
    "df['lat'] = df['lat'].fillna(df['lat_nf'])\n",
    "df['lon'] = df['lon'].fillna(df['lon_nf'])\n",
    "df=df.iloc[:, 0:11]"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "outputs": [
    {
     "data": {
      "text/plain": "     CODICE COMPLESSO DENOMINAZIONE COMPLESSO  CODICE EDIFICIO  \\\n0              100017                   PONTI         10001708   \n1              100017                   PONTI         10001709   \n2              100017                   PONTI         10001710   \n3              100017                   PONTI         10001711   \n4              100017                   PONTI         10001712   \n..                ...                     ...              ...   \n975            510394              SENIGALLIA         51039405   \n976            510394              SENIGALLIA         51039406   \n977            510394              SENIGALLIA         51039407   \n978            510394              SENIGALLIA         51039408   \n979            510394              SENIGALLIA         51039410   \n\n        DENOMINAZIONE EDIFICIO         INDIRIZZO CIVICO  COMUNE CONDOMINIO  \\\n0    DEL TURCHINO 20 C-D (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n1    DEL TURCHINO 20 E-F (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n2      DEL TURCHINO 20 G (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n3      DEL TURCHINO 20 H (VIA)  VIA DEL TURCHINO     20  MILANO        NaN   \n4      DEL TURCHINO 22 A (VIA)  VIA DEL TURCHINO     22  MILANO        NaN   \n..                         ...               ...    ...     ...        ...   \n975       SENIGALLIA - CORPO F    VIA SENIGALLIA   60/G  MILANO        NaN   \n976      SENIGALLIA - CORPO P1    VIA SENIGALLIA     60  MILANO        NaN   \n977      SENIGALLIA - CORPO P2    VIA SENIGALLIA     60  MILANO        NaN   \n978      SENIGALLIA - CORPO P3    VIA SENIGALLIA     60  MILANO        NaN   \n979        POSTI AUTO SCOPERTI    VIA SENIGALLIA     60  MILANO        NaN   \n\n                   full_address        lat       lon  \n0    VIA DEL TURCHINO 20 MILANO  45.451893  9.228053  \n1    VIA DEL TURCHINO 20 MILANO  45.451893  9.228053  \n2    VIA DEL TURCHINO 20 MILANO  45.451893  9.228053  \n3    VIA DEL TURCHINO 20 MILANO  45.451893  9.228053  \n4    VIA DEL TURCHINO 22 MILANO  45.451893  9.228053  \n..                          ...        ...       ...  \n975  VIA SENIGALLIA 60/G MILANO  45.532143  9.168970  \n976    VIA SENIGALLIA 60 MILANO  45.532143  9.168970  \n977    VIA SENIGALLIA 60 MILANO  45.532143  9.168970  \n978    VIA SENIGALLIA 60 MILANO  45.532143  9.168970  \n979    VIA SENIGALLIA 60 MILANO  45.532143  9.168970  \n\n[980 rows x 11 columns]",
      "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>CODICE COMPLESSO</th>\n      <th>DENOMINAZIONE COMPLESSO</th>\n      <th>CODICE EDIFICIO</th>\n      <th>DENOMINAZIONE EDIFICIO</th>\n      <th>INDIRIZZO</th>\n      <th>CIVICO</th>\n      <th>COMUNE</th>\n      <th>CONDOMINIO</th>\n      <th>full_address</th>\n      <th>lat</th>\n      <th>lon</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001708</td>\n      <td>DEL TURCHINO 20 C-D (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001709</td>\n      <td>DEL TURCHINO 20 E-F (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001710</td>\n      <td>DEL TURCHINO 20 G (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001711</td>\n      <td>DEL TURCHINO 20 H (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>20</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 20 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>100017</td>\n      <td>PONTI</td>\n      <td>10001712</td>\n      <td>DEL TURCHINO 22 A (VIA)</td>\n      <td>VIA DEL TURCHINO</td>\n      <td>22</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA DEL TURCHINO 22 MILANO</td>\n      <td>45.451893</td>\n      <td>9.228053</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>975</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039405</td>\n      <td>SENIGALLIA - CORPO F</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60/G</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60/G MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n    </tr>\n    <tr>\n      <th>976</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039406</td>\n      <td>SENIGALLIA - CORPO P1</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60 MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n    </tr>\n    <tr>\n      <th>977</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039407</td>\n      <td>SENIGALLIA - CORPO P2</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60 MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n    </tr>\n    <tr>\n      <th>978</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039408</td>\n      <td>SENIGALLIA - CORPO P3</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60 MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n    </tr>\n    <tr>\n      <th>979</th>\n      <td>510394</td>\n      <td>SENIGALLIA</td>\n      <td>51039410</td>\n      <td>POSTI AUTO SCOPERTI</td>\n      <td>VIA SENIGALLIA</td>\n      <td>60</td>\n      <td>MILANO</td>\n      <td>NaN</td>\n      <td>VIA SENIGALLIA 60 MILANO</td>\n      <td>45.532143</td>\n      <td>9.168970</td>\n    </tr>\n  </tbody>\n</table>\n<p>980 rows × 11 columns</p>\n</div>"
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "outputs": [],
   "source": [
    "df.to_csv('outputs/mm_dataset.csv')"
   ],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}