Preprocessing_house_transactions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1 style=\"text-align: center;\">Pre-processing the real estate transaction records</h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<em><strong>Note:</strong><br>\n",
    "This notebook is part of a project about the French counties and house prices. Please read the <a href=\" https://github.com/Ashish-3/House-prices-in-France/blob/master/Readme.md\">readme</a> file for more information:</em>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook we will pre-process the real estate transaction, and extract information about house prices in France. This will data later be used during the data analysis step."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "#show all the columns and rows of a data\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "\n",
    "# Matplotlib and associated plotting modules\n",
    "import matplotlib.cm as cm\n",
    "import matplotlib.colors as colors\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "import time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data succesfuly loaded!\n"
     ]
    }
   ],
   "source": [
    "# Import CSV data\n",
    "val_immo= pd.read_csv(r'data/valeursfoncieres-2019.txt', sep=\"|\", dtype=str)\n",
    "val_immo= val_immo.append(pd.read_csv(r'data/valeursfoncieres-2018.txt', sep=\"|\", dtype=str))\n",
    "val_immo= val_immo.append(pd.read_csv(r'data/valeursfoncieres-2017.txt', sep=\"|\", dtype=str))\n",
    "val_immo= val_immo.append(pd.read_csv(r'data/valeursfoncieres-2016.txt', sep=\"|\", dtype=str))\n",
    "val_immo= val_immo.append(pd.read_csv(r'data/valeursfoncieres-2015.txt', sep=\"|\", dtype=str))\n",
    "\n",
    "print('Data succesfuly loaded!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(14880473, 43)\n"
     ]
    },
    {
     "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>Code service CH</th>\n",
       "      <th>Reference document</th>\n",
       "      <th>1 Articles CGI</th>\n",
       "      <th>2 Articles CGI</th>\n",
       "      <th>3 Articles CGI</th>\n",
       "      <th>4 Articles CGI</th>\n",
       "      <th>5 Articles CGI</th>\n",
       "      <th>No disposition</th>\n",
       "      <th>Date mutation</th>\n",
       "      <th>Nature mutation</th>\n",
       "      <th>Valeur fonciere</th>\n",
       "      <th>No voie</th>\n",
       "      <th>B/T/Q</th>\n",
       "      <th>Type de voie</th>\n",
       "      <th>Code voie</th>\n",
       "      <th>Voie</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Commune</th>\n",
       "      <th>Code departement</th>\n",
       "      <th>Code commune</th>\n",
       "      <th>Prefixe de section</th>\n",
       "      <th>Section</th>\n",
       "      <th>No plan</th>\n",
       "      <th>No Volume</th>\n",
       "      <th>1er lot</th>\n",
       "      <th>Surface Carrez du 1er lot</th>\n",
       "      <th>2eme lot</th>\n",
       "      <th>Surface Carrez du 2eme lot</th>\n",
       "      <th>3eme lot</th>\n",
       "      <th>Surface Carrez du 3eme lot</th>\n",
       "      <th>4eme lot</th>\n",
       "      <th>Surface Carrez du 4eme lot</th>\n",
       "      <th>5eme lot</th>\n",
       "      <th>Surface Carrez du 5eme lot</th>\n",
       "      <th>Nombre de lots</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Type local</th>\n",
       "      <th>Identifiant local</th>\n",
       "      <th>Surface reelle bati</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Nature culture</th>\n",
       "      <th>Nature culture speciale</th>\n",
       "      <th>Surface terrain</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\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",
       "      <td>NaN</td>\n",
       "      <td>000001</td>\n",
       "      <td>04/01/2019</td>\n",
       "      <td>Vente</td>\n",
       "      <td>37220,00</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RUE</td>\n",
       "      <td>2730</td>\n",
       "      <td>DE MONTHOLON</td>\n",
       "      <td>1000</td>\n",
       "      <td>BOURG-EN-BRESSE</td>\n",
       "      <td>01</td>\n",
       "      <td>53</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AI</td>\n",
       "      <td>298</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>19,27</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Appartement</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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",
       "      <td>NaN</td>\n",
       "      <td>000001</td>\n",
       "      <td>04/01/2019</td>\n",
       "      <td>Vente</td>\n",
       "      <td>185100,00</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RUE</td>\n",
       "      <td>1650</td>\n",
       "      <td>GEN DELESTRAINT</td>\n",
       "      <td>1000</td>\n",
       "      <td>BOURG-EN-BRESSE</td>\n",
       "      <td>01</td>\n",
       "      <td>53</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AM</td>\n",
       "      <td>95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>137</td>\n",
       "      <td>NaN</td>\n",
       "      <td>154</td>\n",
       "      <td>61,51</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",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Appartement</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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",
       "      <td>NaN</td>\n",
       "      <td>000001</td>\n",
       "      <td>04/01/2019</td>\n",
       "      <td>Vente</td>\n",
       "      <td>185100,00</td>\n",
       "      <td>22</td>\n",
       "      <td>B</td>\n",
       "      <td>RUE</td>\n",
       "      <td>1650</td>\n",
       "      <td>GEN DELESTRAINT</td>\n",
       "      <td>1000</td>\n",
       "      <td>BOURG-EN-BRESSE</td>\n",
       "      <td>01</td>\n",
       "      <td>53</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AM</td>\n",
       "      <td>95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Dépendance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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",
       "      <td>NaN</td>\n",
       "      <td>000001</td>\n",
       "      <td>08/01/2019</td>\n",
       "      <td>Vente</td>\n",
       "      <td>209000,00</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RUE</td>\n",
       "      <td>0043</td>\n",
       "      <td>DES CHAMPAGNES</td>\n",
       "      <td>1160</td>\n",
       "      <td>PRIAY</td>\n",
       "      <td>01</td>\n",
       "      <td>314</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E</td>\n",
       "      <td>1676</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Maison</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>4</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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",
       "      <td>NaN</td>\n",
       "      <td>000001</td>\n",
       "      <td>07/01/2019</td>\n",
       "      <td>Vente</td>\n",
       "      <td>134900,00</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LOT</td>\n",
       "      <td>A003</td>\n",
       "      <td>LE BIOLAY</td>\n",
       "      <td>1370</td>\n",
       "      <td>SAINT-ETIENNE-DU-BOIS</td>\n",
       "      <td>01</td>\n",
       "      <td>350</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AA</td>\n",
       "      <td>11</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Maison</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101</td>\n",
       "      <td>5</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>490</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Code service CH Reference document 1 Articles CGI 2 Articles CGI  \\\n",
       "0             NaN                NaN            NaN            NaN   \n",
       "1             NaN                NaN            NaN            NaN   \n",
       "2             NaN                NaN            NaN            NaN   \n",
       "3             NaN                NaN            NaN            NaN   \n",
       "4             NaN                NaN            NaN            NaN   \n",
       "\n",
       "  3 Articles CGI 4 Articles CGI 5 Articles CGI No disposition Date mutation  \\\n",
       "0            NaN            NaN            NaN         000001    04/01/2019   \n",
       "1            NaN            NaN            NaN         000001    04/01/2019   \n",
       "2            NaN            NaN            NaN         000001    04/01/2019   \n",
       "3            NaN            NaN            NaN         000001    08/01/2019   \n",
       "4            NaN            NaN            NaN         000001    07/01/2019   \n",
       "\n",
       "  Nature mutation Valeur fonciere No voie B/T/Q Type de voie Code voie  \\\n",
       "0           Vente        37220,00      26   NaN          RUE      2730   \n",
       "1           Vente       185100,00      22   NaN          RUE      1650   \n",
       "2           Vente       185100,00      22     B          RUE      1650   \n",
       "3           Vente       209000,00       3   NaN          RUE      0043   \n",
       "4           Vente       134900,00       5   NaN          LOT      A003   \n",
       "\n",
       "              Voie Code postal                Commune Code departement  \\\n",
       "0     DE MONTHOLON        1000        BOURG-EN-BRESSE               01   \n",
       "1  GEN DELESTRAINT        1000        BOURG-EN-BRESSE               01   \n",
       "2  GEN DELESTRAINT        1000        BOURG-EN-BRESSE               01   \n",
       "3   DES CHAMPAGNES        1160                  PRIAY               01   \n",
       "4        LE BIOLAY        1370  SAINT-ETIENNE-DU-BOIS               01   \n",
       "\n",
       "  Code commune Prefixe de section Section No plan No Volume 1er lot  \\\n",
       "0           53                NaN      AI     298       NaN       8   \n",
       "1           53                NaN      AM      95       NaN     137   \n",
       "2           53                NaN      AM      95       NaN       7   \n",
       "3          314                NaN       E    1676       NaN     NaN   \n",
       "4          350                NaN      AA      11       NaN     NaN   \n",
       "\n",
       "  Surface Carrez du 1er lot 2eme lot Surface Carrez du 2eme lot 3eme lot  \\\n",
       "0                     19,27      NaN                        NaN      NaN   \n",
       "1                       NaN      154                      61,51      NaN   \n",
       "2                       NaN      NaN                        NaN      NaN   \n",
       "3                       NaN      NaN                        NaN      NaN   \n",
       "4                       NaN      NaN                        NaN      NaN   \n",
       "\n",
       "  Surface Carrez du 3eme lot 4eme lot Surface Carrez du 4eme lot 5eme lot  \\\n",
       "0                        NaN      NaN                        NaN      NaN   \n",
       "1                        NaN      NaN                        NaN      NaN   \n",
       "2                        NaN      NaN                        NaN      NaN   \n",
       "3                        NaN      NaN                        NaN      NaN   \n",
       "4                        NaN      NaN                        NaN      NaN   \n",
       "\n",
       "  Surface Carrez du 5eme lot Nombre de lots Code type local   Type local  \\\n",
       "0                        NaN              1               2  Appartement   \n",
       "1                        NaN              2               2  Appartement   \n",
       "2                        NaN              1               3   Dépendance   \n",
       "3                        NaN              0               1       Maison   \n",
       "4                        NaN              0               1       Maison   \n",
       "\n",
       "  Identifiant local Surface reelle bati Nombre pieces principales  \\\n",
       "0               NaN                  20                         1   \n",
       "1               NaN                  62                         3   \n",
       "2               NaN                   0                         0   \n",
       "3               NaN                  90                         4   \n",
       "4               NaN                 101                         5   \n",
       "\n",
       "  Nature culture Nature culture speciale Surface terrain  \n",
       "0            NaN                     NaN             NaN  \n",
       "1            NaN                     NaN             NaN  \n",
       "2            NaN                     NaN             NaN  \n",
       "3              S                     NaN             940  \n",
       "4              S                     NaN             490  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(val_immo.shape)\n",
    "val_immo.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The DataFrame has 14880473 rows in total"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data pre-processing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Preliminairy data cleanning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Temps de traitement : 0.06196451187133789 secondes pour  72 lignes\n",
      "Temps nécessaire pour traiter 14900000 lignes : 3.5620031901347784 heures\n",
      "(72, 17)\n"
     ]
    },
    {
     "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>Valeur fonciere</th>\n",
       "      <th>Voie</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Commune</th>\n",
       "      <th>Code departement</th>\n",
       "      <th>Surface Carrez du 1er lot</th>\n",
       "      <th>Surface Carrez du 2eme lot</th>\n",
       "      <th>Surface Carrez du 3eme lot</th>\n",
       "      <th>Surface Carrez du 4eme lot</th>\n",
       "      <th>Surface Carrez du 5eme lot</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Type local</th>\n",
       "      <th>Surface reelle bati</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Nature culture</th>\n",
       "      <th>Nature culture speciale</th>\n",
       "      <th>Surface terrain</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37220.0</td>\n",
       "      <td>DE MONTHOLON</td>\n",
       "      <td>1000</td>\n",
       "      <td>BOURG-EN-BRESSE</td>\n",
       "      <td>01</td>\n",
       "      <td>19,27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>Appartement</td>\n",
       "      <td>20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>185100.0</td>\n",
       "      <td>GEN DELESTRAINT</td>\n",
       "      <td>1000</td>\n",
       "      <td>BOURG-EN-BRESSE</td>\n",
       "      <td>01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>61,51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>Appartement</td>\n",
       "      <td>62</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Valeur fonciere             Voie Code postal          Commune  \\\n",
       "0          37220.0     DE MONTHOLON        1000  BOURG-EN-BRESSE   \n",
       "1         185100.0  GEN DELESTRAINT        1000  BOURG-EN-BRESSE   \n",
       "\n",
       "  Code departement Surface Carrez du 1er lot Surface Carrez du 2eme lot  \\\n",
       "0               01                     19,27                        NaN   \n",
       "1               01                       NaN                      61,51   \n",
       "\n",
       "  Surface Carrez du 3eme lot Surface Carrez du 4eme lot  \\\n",
       "0                        NaN                        NaN   \n",
       "1                        NaN                        NaN   \n",
       "\n",
       "  Surface Carrez du 5eme lot Code type local   Type local Surface reelle bati  \\\n",
       "0                        NaN               2  Appartement                  20   \n",
       "1                        NaN               2  Appartement                  62   \n",
       "\n",
       "   Nombre pieces principales Nature culture Nature culture speciale  \\\n",
       "0                        1.0            NaN                     NaN   \n",
       "1                        3.0            NaN                     NaN   \n",
       "\n",
       "   Surface terrain  \n",
       "0              NaN  \n",
       "1              NaN  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Dropping empty columns\n",
    "val_immo.drop(['Code service CH', 'Reference document', '1 Articles CGI', '2 Articles CGI', '3 Articles CGI', '4 Articles CGI','5 Articles CGI', 'Identifiant local'], axis=1, inplace=True)\n",
    "\n",
    "# Dropping other unuseful columns\n",
    "val_immo.drop(['No disposition', 'B/T/Q', 'Prefixe de section', 'Section', 'No plan', 'No Volume','1er lot','2eme lot','3eme lot','4eme lot','5eme lot','Date mutation', 'No voie', 'Type de voie' ,'Code voie', 'Nombre de lots', 'Code commune'], axis=1, inplace=True)\n",
    "\n",
    "# Keeping only the transactions that concerns sales (\"vente\" in french)\n",
    "# it represent more than 90% of the dataset\n",
    "# Dropping the 'Nature mutation' columns after filtering the sales\n",
    "val_immo=val_immo[val_immo['Nature mutation']=='Vente']\n",
    "val_immo.drop(['Nature mutation'], axis=1, inplace=True)\n",
    "\n",
    "# Drop the transaction  where the type of real estate column is left empty\n",
    "val_immo.dropna(subset=[\"Code type local\"], axis=0, inplace=True)\n",
    "\n",
    "# Converting \"Nombre pieces principales\" (amount of rooms) into integer\n",
    "val_immo.loc[:,'Nombre pieces principales']=val_immo.loc[:,'Nombre pieces principales'].astype(float)\n",
    "\n",
    "# Converting \"Valeur fonciere\" (real estate value) into float\n",
    "val_immo.loc[:,'Valeur fonciere']=val_immo.loc[:,'Valeur fonciere'].replace(\",\",\".\", regex=True)\n",
    "val_immo.loc[:,'Valeur fonciere']=val_immo.loc[:,'Valeur fonciere'].astype(float)\n",
    "\n",
    "# Converting \"Surface terrain\" (land surface) into float\n",
    "val_immo.loc[:,'Surface terrain']=val_immo.loc[:,'Surface terrain'].replace(\",\",\".\", regex=True)\n",
    "val_immo.loc[:,'Surface terrain']=val_immo.loc[:,'Surface terrain'].astype(float)\n",
    "\n",
    "# Reset the index of the dataset\n",
    "val_immo.reset_index(drop=True, inplace=True)\n",
    "\n",
    "\n",
    "end = time.time()\n",
    "temps=end - start\n",
    "total=14900000\n",
    "print('Temps de traitement :',temps, 'secondes pour ', val_immo.shape[0], 'lignes')\n",
    "print('Temps nécessaire pour traiter', total, 'lignes :', (temps*total)/(val_immo.shape[0]*60*60) ,'heures')\n",
    "\n",
    "print(val_immo.shape)\n",
    "val_immo.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "######  Getting the surfaces of the transactions right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Temps de traitement : 120.93264579772949 secondes pour  7549422 lignes\n",
      "Temps nécessaire pour traiter 14900000 lignes : 0.06630001396082484 heures\n",
      "(7549422, 13)\n"
     ]
    },
    {
     "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>Valeur fonciere</th>\n",
       "      <th>Voie</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Commune</th>\n",
       "      <th>Code departement</th>\n",
       "      <th>Code commune</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Type local</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Nature culture</th>\n",
       "      <th>Nature culture speciale</th>\n",
       "      <th>Surface terrain</th>\n",
       "      <th>Surface Carrez</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37220.0</td>\n",
       "      <td>DE MONTHOLON</td>\n",
       "      <td>1000</td>\n",
       "      <td>BOURG-EN-BRESSE</td>\n",
       "      <td>01</td>\n",
       "      <td>53</td>\n",
       "      <td>2</td>\n",
       "      <td>Appartement</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>185100.0</td>\n",
       "      <td>GEN DELESTRAINT</td>\n",
       "      <td>1000</td>\n",
       "      <td>BOURG-EN-BRESSE</td>\n",
       "      <td>01</td>\n",
       "      <td>53</td>\n",
       "      <td>2</td>\n",
       "      <td>Appartement</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>185100.0</td>\n",
       "      <td>GEN DELESTRAINT</td>\n",
       "      <td>1000</td>\n",
       "      <td>BOURG-EN-BRESSE</td>\n",
       "      <td>01</td>\n",
       "      <td>53</td>\n",
       "      <td>3</td>\n",
       "      <td>Dépendance</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Valeur fonciere             Voie Code postal          Commune  \\\n",
       "0          37220.0     DE MONTHOLON        1000  BOURG-EN-BRESSE   \n",
       "1         185100.0  GEN DELESTRAINT        1000  BOURG-EN-BRESSE   \n",
       "2         185100.0  GEN DELESTRAINT        1000  BOURG-EN-BRESSE   \n",
       "\n",
       "  Code departement Code commune Code type local   Type local  \\\n",
       "0               01           53               2  Appartement   \n",
       "1               01           53               2  Appartement   \n",
       "2               01           53               3   Dépendance   \n",
       "\n",
       "   Nombre pieces principales Nature culture Nature culture speciale  \\\n",
       "0                        1.0            NaN                     NaN   \n",
       "1                        3.0            NaN                     NaN   \n",
       "2                        0.0            NaN                     NaN   \n",
       "\n",
       "   Surface terrain  Surface Carrez  \n",
       "0              NaN           19.27  \n",
       "1              NaN           62.00  \n",
       "2              NaN            0.00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Summarizing the total of \"Surface Carrez\" (living area) into one column and dropping the others\n",
    "# Using numpy for faster processing\n",
    "\n",
    "surface_carrez=np.char.replace(val_immo[\"Surface Carrez du 1er lot\"].to_numpy().astype(np.str),\",\",\".\").astype(float)\n",
    "+np.char.replace(val_immo[\"Surface Carrez du 2eme lot\"].to_numpy().astype(np.str),\",\",\".\").astype(float)\n",
    "+np.char.replace(val_immo[\"Surface Carrez du 3eme lot\"].to_numpy().astype(np.str),\",\",\".\").astype(float)\n",
    "+np.char.replace(val_immo[\"Surface Carrez du 4eme lot\"].to_numpy().astype(np.str),\",\",\".\").astype(float)\n",
    "+np.char.replace(val_immo[\"Surface Carrez du 5eme lot\"].to_numpy().astype(np.str),\",\",\".\").astype(float)\n",
    "\n",
    "\n",
    "\n",
    "# Sometime the \"surface Carrez\"(living area) is empty\n",
    "# We can replace the empty fields by the \"Surface reel bati\" (building surface area)\n",
    "surface_bati= np.char.replace(val_immo[\"Surface reelle bati\"].to_numpy().astype(np.str),\",\",\".\").astype(float)\n",
    "for i in range(0,surface_carrez.shape[0]) :\n",
    "    if surface_carrez[i]==0 or np.isnan(surface_carrez[i]):\n",
    "        surface_carrez[i]=surface_bati[i]\n",
    "val_immo[\"Surface Carrez\"]=surface_carrez\n",
    "\n",
    "\n",
    "# Drop the columns used to create the column 'Surface Carrez'\n",
    "val_immo.drop([ 'Surface Carrez du 1er lot','Surface Carrez du 2eme lot','Surface Carrez du 3eme lot','Surface Carrez du 4eme lot','Surface Carrez du 5eme lot'], axis=1, inplace=True)\n",
    "val_immo.drop([ 'Surface reelle bati'], axis=1, inplace=True)\n",
    "\n",
    "end = time.time()\n",
    "temps=end - start\n",
    "total=14900000\n",
    "print('Temps de traitement :',temps, 'secondes pour ', val_immo.shape[0], 'lignes')\n",
    "print('Temps nécessaire pour traiter', total, 'lignes :', (temps*total)/(val_immo.shape[0]*60*60) ,'heures')\n",
    "\n",
    "print(val_immo.shape)\n",
    "val_immo.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "######  Removing duplicates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's remove : \n",
    "     - transactions with outbuildings (\"Dépendance\") \n",
    "     - transactions that may have been recorded twice\n",
    "     - or mutliple buildings that were recorded in seperate transactions but were bought together (in bulk purchase)\n",
    "They all share the same characteristics : \n",
    "      - they have two rows with the excat same transaction price ('Valeur fonciere') next to each other\n",
    " Let's tag those transactions and remove them from the dataset.\n",
    " For faster computing we will use numpy insted of pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Temps de traitement : 5.526273727416992 secondes pour 7549422 lignes\n",
      "Nombre de duplicatat trouvé : 3000698\n",
      "Temps nécessaire pour traiter 14900000 lignes : 0.0030297197490569178 heures\n",
      "3000699 lines dropped\n",
      "(4548723, 13)\n"
     ]
    },
    {
     "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>Valeur fonciere</th>\n",
       "      <th>Voie</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Commune</th>\n",
       "      <th>Code departement</th>\n",
       "      <th>Code commune</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Type local</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Nature culture</th>\n",
       "      <th>Nature culture speciale</th>\n",
       "      <th>Surface terrain</th>\n",
       "      <th>Surface Carrez</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37220.0</td>\n",
       "      <td>DE MONTHOLON</td>\n",
       "      <td>1000</td>\n",
       "      <td>BOURG-EN-BRESSE</td>\n",
       "      <td>01</td>\n",
       "      <td>53</td>\n",
       "      <td>2</td>\n",
       "      <td>Appartement</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>209000.0</td>\n",
       "      <td>DES CHAMPAGNES</td>\n",
       "      <td>1160</td>\n",
       "      <td>PRIAY</td>\n",
       "      <td>01</td>\n",
       "      <td>314</td>\n",
       "      <td>1</td>\n",
       "      <td>Maison</td>\n",
       "      <td>4.0</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>940.0</td>\n",
       "      <td>90.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>134900.0</td>\n",
       "      <td>LE BIOLAY</td>\n",
       "      <td>1370</td>\n",
       "      <td>SAINT-ETIENNE-DU-BOIS</td>\n",
       "      <td>01</td>\n",
       "      <td>350</td>\n",
       "      <td>1</td>\n",
       "      <td>Maison</td>\n",
       "      <td>5.0</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>490.0</td>\n",
       "      <td>101.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Valeur fonciere            Voie Code postal                Commune  \\\n",
       "0          37220.0    DE MONTHOLON        1000        BOURG-EN-BRESSE   \n",
       "1         209000.0  DES CHAMPAGNES        1160                  PRIAY   \n",
       "2         134900.0       LE BIOLAY        1370  SAINT-ETIENNE-DU-BOIS   \n",
       "\n",
       "  Code departement Code commune Code type local   Type local  \\\n",
       "0               01           53               2  Appartement   \n",
       "1               01          314               1       Maison   \n",
       "2               01          350               1       Maison   \n",
       "\n",
       "   Nombre pieces principales Nature culture Nature culture speciale  \\\n",
       "0                        1.0            NaN                     NaN   \n",
       "1                        4.0              S                     NaN   \n",
       "2                        5.0              S                     NaN   \n",
       "\n",
       "   Surface terrain  Surface Carrez  \n",
       "0              NaN           19.27  \n",
       "1            940.0           90.00  \n",
       "2            490.0          101.00  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# inatializing the variables with 'marker' the np array used to tag the rows to be removed\n",
    "fonciere=val_immo['Valeur fonciere'].to_numpy()\n",
    "marker=np.full((fonciere.shape[0]), False)\n",
    "\n",
    "# using a loop to tag the transactions\n",
    "counter = 0\n",
    "start = time.time()\n",
    "for i in range (0,fonciere.shape[0]-1) :\n",
    "    if fonciere[i]==fonciere[i+1]:\n",
    "        marker[1]=True\n",
    "        marker[i+1]=True\n",
    "        counter = counter + 1\n",
    "end = time.time()\n",
    "temps=end - start\n",
    "total=14900000\n",
    "print('Temps de traitement :',temps, 'secondes', 'pour', fonciere.shape[0], 'lignes')\n",
    "print('Nombre de duplicatat trouvé :', counter)\n",
    "print('Temps nécessaire pour traiter', total, 'lignes :', (temps*total)/(val_immo.shape[0]*60*60) ,'heures')\n",
    "\n",
    "# dropping the transactions\n",
    "dropped=val_immo.shape[0]\n",
    "val_immo.drop(np.where(marker==True)[0], axis=0, inplace=True)\n",
    "dropped=dropped-val_immo.shape[0]\n",
    "print(dropped, 'lines dropped')\n",
    "\n",
    "# Reset the index of the dataset after dropping some rows\n",
    "val_immo.reset_index(drop=True, inplace=True)\n",
    "\n",
    "print(val_immo.shape)\n",
    "val_immo.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### For this sutdy : let's study only houses transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see what are the proportions of houses (maison) and flats (appartement) do we have in the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " Pourcentage maison 0.4858381132462891 \n",
      " Pourcentage appartement 0.29528001595172976\n"
     ]
    }
   ],
   "source": [
    "print('\\n','Pourcentage maison',val_immo[val_immo['Code type local']=='1'].shape[0]/val_immo.shape[0],\n",
    "      '\\n',\n",
    "      'Pourcentage appartement',val_immo[val_immo['Code type local']=='2'].shape[0]/val_immo.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2209943, 13)\n"
     ]
    },
    {
     "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>Valeur fonciere</th>\n",
       "      <th>Voie</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Commune</th>\n",
       "      <th>Code departement</th>\n",
       "      <th>Code commune</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Type local</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Nature culture</th>\n",
       "      <th>Nature culture speciale</th>\n",
       "      <th>Surface terrain</th>\n",
       "      <th>Surface Carrez</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>209000.0</td>\n",
       "      <td>DES CHAMPAGNES</td>\n",
       "      <td>1160</td>\n",
       "      <td>PRIAY</td>\n",
       "      <td>01</td>\n",
       "      <td>314</td>\n",
       "      <td>1</td>\n",
       "      <td>Maison</td>\n",
       "      <td>4.0</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>940.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>134900.0</td>\n",
       "      <td>LE BIOLAY</td>\n",
       "      <td>1370</td>\n",
       "      <td>SAINT-ETIENNE-DU-BOIS</td>\n",
       "      <td>01</td>\n",
       "      <td>350</td>\n",
       "      <td>1</td>\n",
       "      <td>Maison</td>\n",
       "      <td>5.0</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>490.0</td>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>192000.0</td>\n",
       "      <td>DES LIBELLULES</td>\n",
       "      <td>1340</td>\n",
       "      <td>ATTIGNAT</td>\n",
       "      <td>01</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>Maison</td>\n",
       "      <td>4.0</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>708.0</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Valeur fonciere            Voie Code postal                Commune  \\\n",
       "0         209000.0  DES CHAMPAGNES        1160                  PRIAY   \n",
       "1         134900.0       LE BIOLAY        1370  SAINT-ETIENNE-DU-BOIS   \n",
       "2         192000.0  DES LIBELLULES        1340               ATTIGNAT   \n",
       "\n",
       "  Code departement Code commune Code type local Type local  \\\n",
       "0               01          314               1     Maison   \n",
       "1               01          350               1     Maison   \n",
       "2               01           24               1     Maison   \n",
       "\n",
       "   Nombre pieces principales Nature culture Nature culture speciale  \\\n",
       "0                        4.0              S                     NaN   \n",
       "1                        5.0              S                     NaN   \n",
       "2                        4.0              S                     NaN   \n",
       "\n",
       "   Surface terrain  Surface Carrez  \n",
       "0            940.0            90.0  \n",
       "1            490.0           101.0  \n",
       "2            708.0            88.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We will focus our study only on houses, therfor we will drop all  other transaction type\n",
    "val_immo = val_immo[ val_immo['Code type local']=='1' ]\n",
    "val_immo.reset_index(drop=True, inplace=True)\n",
    "\n",
    "print(val_immo.shape)\n",
    "val_immo.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2209943, 13)\n"
     ]
    },
    {
     "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>Valeur fonciere</th>\n",
       "      <th>Voie</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Commune</th>\n",
       "      <th>Code departement</th>\n",
       "      <th>Code commune</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Type local</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Nature culture</th>\n",
       "      <th>Nature culture speciale</th>\n",
       "      <th>Surface terrain</th>\n",
       "      <th>Surface Carrez</th>\n",
       "      <th>Prix_m2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.200766e+06</td>\n",
       "      <td>2209161</td>\n",
       "      <td>2209003</td>\n",
       "      <td>2209196</td>\n",
       "      <td>2209943</td>\n",
       "      <td>2209943</td>\n",
       "      <td>2209943</td>\n",
       "      <td>2209943</td>\n",
       "      <td>2.209914e+06</td>\n",
       "      <td>2106595</td>\n",
       "      <td>41088</td>\n",
       "      <td>2.106595e+06</td>\n",
       "      <td>2.209914e+06</td>\n",
       "      <td>2.200737e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>NaN</td>\n",
       "      <td>383945</td>\n",
       "      <td>5863</td>\n",
       "      <td>33168</td>\n",
       "      <td>97</td>\n",
       "      <td>908</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>NaN</td>\n",
       "      <td>GRANDE RUE</td>\n",
       "      <td>59200</td>\n",
       "      <td>NANTES</td>\n",
       "      <td>59</td>\n",
       "      <td>63</td>\n",
       "      <td>1</td>\n",
       "      <td>Maison</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>POTAG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>NaN</td>\n",
       "      <td>13046</td>\n",
       "      <td>4438</td>\n",
       "      <td>6303</td>\n",
       "      <td>113686</td>\n",
       "      <td>13460</td>\n",
       "      <td>2209943</td>\n",
       "      <td>2209943</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1879834</td>\n",
       "      <td>18317</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2.162659e+05</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",
       "      <td>NaN</td>\n",
       "      <td>4.209123e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.200137e+02</td>\n",
       "      <td>1.007709e+02</td>\n",
       "      <td>2.229404e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.965079e+06</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",
       "      <td>NaN</td>\n",
       "      <td>1.468628e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.681187e+03</td>\n",
       "      <td>4.570260e+01</td>\n",
       "      <td>1.841632e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</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",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>9.200000e-01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.059900e+05</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",
       "      <td>NaN</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.600000e+02</td>\n",
       "      <td>7.500000e+01</td>\n",
       "      <td>1.187500e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.680000e+05</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",
       "      <td>NaN</td>\n",
       "      <td>4.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.000000e+02</td>\n",
       "      <td>9.300000e+01</td>\n",
       "      <td>1.777778e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2.550000e+05</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",
       "      <td>NaN</td>\n",
       "      <td>5.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.800000e+02</td>\n",
       "      <td>1.200000e+02</td>\n",
       "      <td>2.607407e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.086000e+09</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",
       "      <td>NaN</td>\n",
       "      <td>1.120000e+02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.286391e+06</td>\n",
       "      <td>9.500000e+03</td>\n",
       "      <td>1.804124e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Valeur fonciere        Voie Code postal  Commune Code departement  \\\n",
       "count      2.200766e+06     2209161     2209003  2209196          2209943   \n",
       "unique              NaN      383945        5863    33168               97   \n",
       "top                 NaN  GRANDE RUE       59200   NANTES               59   \n",
       "freq                NaN       13046        4438     6303           113686   \n",
       "mean       2.162659e+05         NaN         NaN      NaN              NaN   \n",
       "std        1.965079e+06         NaN         NaN      NaN              NaN   \n",
       "min        0.000000e+00         NaN         NaN      NaN              NaN   \n",
       "25%        1.059900e+05         NaN         NaN      NaN              NaN   \n",
       "50%        1.680000e+05         NaN         NaN      NaN              NaN   \n",
       "75%        2.550000e+05         NaN         NaN      NaN              NaN   \n",
       "max        2.086000e+09         NaN         NaN      NaN              NaN   \n",
       "\n",
       "       Code commune Code type local Type local  Nombre pieces principales  \\\n",
       "count       2209943         2209943    2209943               2.209914e+06   \n",
       "unique          908               1          1                        NaN   \n",
       "top              63               1     Maison                        NaN   \n",
       "freq          13460         2209943    2209943                        NaN   \n",
       "mean            NaN             NaN        NaN               4.209123e+00   \n",
       "std             NaN             NaN        NaN               1.468628e+00   \n",
       "min             NaN             NaN        NaN               0.000000e+00   \n",
       "25%             NaN             NaN        NaN               3.000000e+00   \n",
       "50%             NaN             NaN        NaN               4.000000e+00   \n",
       "75%             NaN             NaN        NaN               5.000000e+00   \n",
       "max             NaN             NaN        NaN               1.120000e+02   \n",
       "\n",
       "       Nature culture Nature culture speciale  Surface terrain  \\\n",
       "count         2106595                   41088     2.106595e+06   \n",
       "unique             27                     100              NaN   \n",
       "top                 S                   POTAG              NaN   \n",
       "freq          1879834                   18317              NaN   \n",
       "mean              NaN                     NaN     7.200137e+02   \n",
       "std               NaN                     NaN     3.681187e+03   \n",
       "min               NaN                     NaN     0.000000e+00   \n",
       "25%               NaN                     NaN     2.600000e+02   \n",
       "50%               NaN                     NaN     5.000000e+02   \n",
       "75%               NaN                     NaN     7.800000e+02   \n",
       "max               NaN                     NaN     2.286391e+06   \n",
       "\n",
       "        Surface Carrez       Prix_m2  \n",
       "count     2.209914e+06  2.200737e+06  \n",
       "unique             NaN           NaN  \n",
       "top                NaN           NaN  \n",
       "freq               NaN           NaN  \n",
       "mean      1.007709e+02  2.229404e+03  \n",
       "std       4.570260e+01  1.841632e+04  \n",
       "min       9.200000e-01  0.000000e+00  \n",
       "25%       7.500000e+01  1.187500e+03  \n",
       "50%       9.300000e+01  1.777778e+03  \n",
       "75%       1.200000e+02  2.607407e+03  \n",
       "max       9.500000e+03  1.804124e+07  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's add a price per meter square column and get a quick description of the DataFrame\n",
    "print(val_immo.shape)\n",
    "val_immo[\"Prix_m2\"]=val_immo['Valeur fonciere']/val_immo['Surface Carrez']\n",
    "val_immo.describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's have a quick idea of how big are most of the house :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The \"Surface terrain\" where we reach the 85th quantile is : 1000.0 m2 \n",
      " The \"Surface Carrez\" where we reach the 95th quantile is : 198.0 m2 \n",
      " The \"Nombre pieces principales\" where we reach the 99,95th quantile is : 13.0 m2\n"
     ]
    }
   ],
   "source": [
    "print('The \"Surface terrain\" where we reach the 85th quantile is :',\n",
    "      val_immo['Surface terrain'].quantile(q=0.85),'m2',\n",
    "      '\\n',\n",
    "      'The \"Surface Carrez\" where we reach the 95th quantile is :',\n",
    "      val_immo['Surface Carrez'].quantile(q=0.97),'m2',\n",
    "      '\\n',\n",
    "     'The \"Nombre pieces principales\" where we reach the 99,95th quantile is :',\n",
    "      val_immo['Nombre pieces principales'].quantile(q=0.9995),'m2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Therefor, for this study let's remove the outliers by removing houses with :\n",
    "    - a living area bigger than 200 m2 \n",
    "    - a land surface bigger than 1000 m2\n",
    "    - more than 10 rooms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1775051, 14)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Slicing the data\n",
    "val_immo=val_immo[ (val_immo['Surface Carrez']<=200) &\n",
    "                  (val_immo['Surface terrain']<=1000) &\n",
    "                 (val_immo['Nombre pieces principales']<=10)]\n",
    "\n",
    "# Reset the index of the dataset\n",
    "val_immo.reset_index(drop=True, inplace=True)\n",
    "\n",
    "val_immo.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3063: DtypeWarning: Columns (4) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "#val_immo.to_csv( r'val_immo.csv', index=False)\n",
    "val_immo=pd.read_csv( r'val_immo.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "sns.distplot(val_immo['Surface Carrez'] )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "sns.regplot(x='Surface terrain', \n",
    "            y='Valeur fonciere', \n",
    "            data=val_immo.head(10000)[val_immo.head(10000)['Valeur fonciere']<300000], \n",
    "            color='green', \n",
    "            scatter_kws={'s': 1})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summarising the house sales per Communes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's summarise the transaction per Communes in a DataFrame called com_immo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>Commune</th>\n",
       "      <th>Valeur fonciere</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Code commune</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Surface terrain</th>\n",
       "      <th>Surface Carrez</th>\n",
       "      <th>Prix_m2</th>\n",
       "      <th>N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAST</td>\n",
       "      <td>129665.200000</td>\n",
       "      <td>64460.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.600000</td>\n",
       "      <td>608.400000</td>\n",
       "      <td>118.600000</td>\n",
       "      <td>1075.662291</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABAINVILLE</td>\n",
       "      <td>63735.164286</td>\n",
       "      <td>55130.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.857143</td>\n",
       "      <td>396.142857</td>\n",
       "      <td>88.285714</td>\n",
       "      <td>682.972083</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABANCOURT</td>\n",
       "      <td>120205.960952</td>\n",
       "      <td>59902.666667</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.095238</td>\n",
       "      <td>553.095238</td>\n",
       "      <td>90.904762</td>\n",
       "      <td>1379.239892</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ABAUCOURT</td>\n",
       "      <td>133970.214286</td>\n",
       "      <td>54610.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.857143</td>\n",
       "      <td>441.571429</td>\n",
       "      <td>109.071429</td>\n",
       "      <td>1360.566532</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAUCOURT-HAUTECOURT</td>\n",
       "      <td>84500.000000</td>\n",
       "      <td>55400.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>696.000000</td>\n",
       "      <td>142.000000</td>\n",
       "      <td>609.778226</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Commune  Valeur fonciere   Code postal  Code commune  \\\n",
       "0                  AAST    129665.200000  64460.000000           1.0   \n",
       "1            ABAINVILLE     63735.164286  55130.000000           1.0   \n",
       "2             ABANCOURT    120205.960952  59902.666667           1.0   \n",
       "3             ABAUCOURT    133970.214286  54610.000000           1.0   \n",
       "4  ABAUCOURT-HAUTECOURT     84500.000000  55400.000000           2.0   \n",
       "\n",
       "   Code type local  Nombre pieces principales  Surface terrain  \\\n",
       "0              1.0                   4.600000       608.400000   \n",
       "1              1.0                   3.857143       396.142857   \n",
       "2              1.0                   4.095238       553.095238   \n",
       "3              1.0                   3.857143       441.571429   \n",
       "4              1.0                   6.000000       696.000000   \n",
       "\n",
       "   Surface Carrez      Prix_m2   N  \n",
       "0      118.600000  1075.662291   5  \n",
       "1       88.285714   682.972083  14  \n",
       "2       90.904762  1379.239892  42  \n",
       "3      109.071429  1360.566532  14  \n",
       "4      142.000000   609.778226   2  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculating the mean values per Commune\n",
    "com_immo=val_immo.groupby('Commune').mean()\n",
    "\n",
    "\n",
    "# Storing the number of observations per Commune in a column called 'N'\n",
    "com_immo['N']=val_immo.groupby('Commune').size()\n",
    "\n",
    "com_immo.reset_index(inplace=True)\n",
    "com_immo.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Let's try to merge our house prices (com_immo) to other info about Communes (all_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We load the data have about age, revenues and geography  for the Communes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "scrolled": true
   },
   "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>Code INSEE</th>\n",
       "      <th>Code Postal</th>\n",
       "      <th>Commune</th>\n",
       "      <th>Code Département</th>\n",
       "      <th>Code Région</th>\n",
       "      <th>Superficie_km2</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>age_mean</th>\n",
       "      <th>age_std</th>\n",
       "      <th>age_median</th>\n",
       "      <th>population</th>\n",
       "      <th>merge1</th>\n",
       "      <th>Fhousehold</th>\n",
       "      <th>person_p_Fhousehold</th>\n",
       "      <th>revenue_median</th>\n",
       "      <th>revenue_inequalities</th>\n",
       "      <th>merge2</th>\n",
       "      <th>density</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59276</td>\n",
       "      <td>59287</td>\n",
       "      <td>GUESNAIN</td>\n",
       "      <td>59</td>\n",
       "      <td>31.0</td>\n",
       "      <td>4.05</td>\n",
       "      <td>50.348346</td>\n",
       "      <td>3.148167</td>\n",
       "      <td>40.851860</td>\n",
       "      <td>24.642605</td>\n",
       "      <td>41.0</td>\n",
       "      <td>4651.0</td>\n",
       "      <td>both</td>\n",
       "      <td>1884.0</td>\n",
       "      <td>4624.0</td>\n",
       "      <td>17270.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>both</td>\n",
       "      <td>1148.395062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>88128</td>\n",
       "      <td>88210</td>\n",
       "      <td>DENIPAIRE</td>\n",
       "      <td>88</td>\n",
       "      <td>41.0</td>\n",
       "      <td>7.02</td>\n",
       "      <td>48.339817</td>\n",
       "      <td>6.961893</td>\n",
       "      <td>43.832158</td>\n",
       "      <td>22.810009</td>\n",
       "      <td>48.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>both</td>\n",
       "      <td>108.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>21720.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>35.042735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>57538</td>\n",
       "      <td>57170</td>\n",
       "      <td>PETTONCOURT</td>\n",
       "      <td>57</td>\n",
       "      <td>41.0</td>\n",
       "      <td>4.93</td>\n",
       "      <td>48.788126</td>\n",
       "      <td>6.411069</td>\n",
       "      <td>40.315972</td>\n",
       "      <td>23.739636</td>\n",
       "      <td>41.0</td>\n",
       "      <td>288.0</td>\n",
       "      <td>both</td>\n",
       "      <td>109.0</td>\n",
       "      <td>298.0</td>\n",
       "      <td>20970.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>58.417850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>54459</td>\n",
       "      <td>54630</td>\n",
       "      <td>RICHARDMENIL</td>\n",
       "      <td>54</td>\n",
       "      <td>41.0</td>\n",
       "      <td>7.07</td>\n",
       "      <td>48.595865</td>\n",
       "      <td>6.176145</td>\n",
       "      <td>46.335881</td>\n",
       "      <td>23.049189</td>\n",
       "      <td>51.0</td>\n",
       "      <td>2358.0</td>\n",
       "      <td>both</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>2395.0</td>\n",
       "      <td>25960.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>both</td>\n",
       "      <td>333.521924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35022</td>\n",
       "      <td>35190</td>\n",
       "      <td>BECHEREL</td>\n",
       "      <td>35</td>\n",
       "      <td>53.0</td>\n",
       "      <td>0.55</td>\n",
       "      <td>48.296509</td>\n",
       "      <td>-1.942824</td>\n",
       "      <td>41.722078</td>\n",
       "      <td>25.144570</td>\n",
       "      <td>42.0</td>\n",
       "      <td>673.0</td>\n",
       "      <td>both</td>\n",
       "      <td>293.0</td>\n",
       "      <td>632.0</td>\n",
       "      <td>19260.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>1223.636364</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Code INSEE Code Postal       Commune Code Département  Code Région  \\\n",
       "0      59276       59287      GUESNAIN               59         31.0   \n",
       "1      88128       88210     DENIPAIRE               88         41.0   \n",
       "2      57538       57170   PETTONCOURT               57         41.0   \n",
       "3      54459       54630  RICHARDMENIL               54         41.0   \n",
       "4      35022       35190      BECHEREL               35         53.0   \n",
       "\n",
       "   Superficie_km2        lat       lng   age_mean    age_std  age_median  \\\n",
       "0            4.05  50.348346  3.148167  40.851860  24.642605        41.0   \n",
       "1            7.02  48.339817  6.961893  43.832158  22.810009        48.0   \n",
       "2            4.93  48.788126  6.411069  40.315972  23.739636        41.0   \n",
       "3            7.07  48.595865  6.176145  46.335881  23.049189        51.0   \n",
       "4            0.55  48.296509 -1.942824  41.722078  25.144570        42.0   \n",
       "\n",
       "   population merge1  Fhousehold  person_p_Fhousehold  revenue_median  \\\n",
       "0      4651.0   both      1884.0               4624.0         17270.0   \n",
       "1       246.0   both       108.0                246.0         21720.0   \n",
       "2       288.0   both       109.0                298.0         20970.0   \n",
       "3      2358.0   both      1020.0               2395.0         25960.0   \n",
       "4       673.0   both       293.0                632.0         19260.0   \n",
       "\n",
       "   revenue_inequalities merge2      density  \n",
       "0                   2.6   both  1148.395062  \n",
       "1                   NaN   both    35.042735  \n",
       "2                   NaN   both    58.417850  \n",
       "3                   2.4   both   333.521924  \n",
       "4                   NaN   both  1223.636364  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data=pd.read_csv(r'all_data.csv')\n",
    "all_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  First try to merge the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " As the the real estate data doesn't have any columns stating the Code INSEE of the Communes, the only way of merge the data is by using Commune names as the merging key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge=pd.merge(all_data,com_immo,on='Commune', how='outer', indicator=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code INSEE</th>\n",
       "      <th>Code Postal</th>\n",
       "      <th>Commune</th>\n",
       "      <th>Code Commune</th>\n",
       "      <th>Superficie_km2</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>age_mean</th>\n",
       "      <th>age_std</th>\n",
       "      <th>age_median</th>\n",
       "      <th>population</th>\n",
       "      <th>merge1</th>\n",
       "      <th>Fhousehold</th>\n",
       "      <th>person_p_Fhousehold</th>\n",
       "      <th>revenue_median</th>\n",
       "      <th>revenue_inequalities</th>\n",
       "      <th>merge2</th>\n",
       "      <th>density</th>\n",
       "      <th>Valeur fonciere</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Surface terrain</th>\n",
       "      <th>Surface Carrez</th>\n",
       "      <th>Prix_m2</th>\n",
       "      <th>N</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59276</td>\n",
       "      <td>59287</td>\n",
       "      <td>GUESNAIN</td>\n",
       "      <td>276.0</td>\n",
       "      <td>4.05</td>\n",
       "      <td>50.348346</td>\n",
       "      <td>3.148167</td>\n",
       "      <td>40.851860</td>\n",
       "      <td>24.642605</td>\n",
       "      <td>41.0</td>\n",
       "      <td>4651.0</td>\n",
       "      <td>both</td>\n",
       "      <td>1884.0</td>\n",
       "      <td>4624.0</td>\n",
       "      <td>17270.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>both</td>\n",
       "      <td>1148.395062</td>\n",
       "      <td>126111.978261</td>\n",
       "      <td>4.391304</td>\n",
       "      <td>410.869565</td>\n",
       "      <td>93.391304</td>\n",
       "      <td>1368.677765</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>88128</td>\n",
       "      <td>88210</td>\n",
       "      <td>DENIPAIRE</td>\n",
       "      <td>128.0</td>\n",
       "      <td>7.02</td>\n",
       "      <td>48.339817</td>\n",
       "      <td>6.961893</td>\n",
       "      <td>43.832158</td>\n",
       "      <td>22.810009</td>\n",
       "      <td>48.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>both</td>\n",
       "      <td>108.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>21720.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>35.042735</td>\n",
       "      <td>98260.000000</td>\n",
       "      <td>4.400000</td>\n",
       "      <td>456.400000</td>\n",
       "      <td>99.200000</td>\n",
       "      <td>1002.946560</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>57538</td>\n",
       "      <td>57170</td>\n",
       "      <td>PETTONCOURT</td>\n",
       "      <td>538.0</td>\n",
       "      <td>4.93</td>\n",
       "      <td>48.788126</td>\n",
       "      <td>6.411069</td>\n",
       "      <td>40.315972</td>\n",
       "      <td>23.739636</td>\n",
       "      <td>41.0</td>\n",
       "      <td>288.0</td>\n",
       "      <td>both</td>\n",
       "      <td>109.0</td>\n",
       "      <td>298.0</td>\n",
       "      <td>20970.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>58.417850</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",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>54459</td>\n",
       "      <td>54630</td>\n",
       "      <td>RICHARDMENIL</td>\n",
       "      <td>459.0</td>\n",
       "      <td>7.07</td>\n",
       "      <td>48.595865</td>\n",
       "      <td>6.176145</td>\n",
       "      <td>46.335881</td>\n",
       "      <td>23.049189</td>\n",
       "      <td>51.0</td>\n",
       "      <td>2358.0</td>\n",
       "      <td>both</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>2395.0</td>\n",
       "      <td>25960.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>both</td>\n",
       "      <td>333.521924</td>\n",
       "      <td>201593.181818</td>\n",
       "      <td>4.738636</td>\n",
       "      <td>493.113636</td>\n",
       "      <td>107.909091</td>\n",
       "      <td>1924.344829</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35022</td>\n",
       "      <td>35190</td>\n",
       "      <td>BECHEREL</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.55</td>\n",
       "      <td>48.296509</td>\n",
       "      <td>-1.942824</td>\n",
       "      <td>41.722078</td>\n",
       "      <td>25.144570</td>\n",
       "      <td>42.0</td>\n",
       "      <td>673.0</td>\n",
       "      <td>both</td>\n",
       "      <td>293.0</td>\n",
       "      <td>632.0</td>\n",
       "      <td>19260.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>1223.636364</td>\n",
       "      <td>142893.235294</td>\n",
       "      <td>4.823529</td>\n",
       "      <td>327.500000</td>\n",
       "      <td>117.735294</td>\n",
       "      <td>1239.205157</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Code INSEE Code Postal       Commune  Code Commune  Superficie_km2  \\\n",
       "0      59276       59287      GUESNAIN         276.0            4.05   \n",
       "1      88128       88210     DENIPAIRE         128.0            7.02   \n",
       "2      57538       57170   PETTONCOURT         538.0            4.93   \n",
       "3      54459       54630  RICHARDMENIL         459.0            7.07   \n",
       "4      35022       35190      BECHEREL          22.0            0.55   \n",
       "\n",
       "         lat       lng   age_mean    age_std  age_median  population merge1  \\\n",
       "0  50.348346  3.148167  40.851860  24.642605        41.0      4651.0   both   \n",
       "1  48.339817  6.961893  43.832158  22.810009        48.0       246.0   both   \n",
       "2  48.788126  6.411069  40.315972  23.739636        41.0       288.0   both   \n",
       "3  48.595865  6.176145  46.335881  23.049189        51.0      2358.0   both   \n",
       "4  48.296509 -1.942824  41.722078  25.144570        42.0       673.0   both   \n",
       "\n",
       "   Fhousehold  person_p_Fhousehold  revenue_median  revenue_inequalities  \\\n",
       "0      1884.0               4624.0         17270.0                   2.6   \n",
       "1       108.0                246.0         21720.0                   NaN   \n",
       "2       109.0                298.0         20970.0                   NaN   \n",
       "3      1020.0               2395.0         25960.0                   2.4   \n",
       "4       293.0                632.0         19260.0                   NaN   \n",
       "\n",
       "  merge2      density  Valeur fonciere  Nombre pieces principales  \\\n",
       "0   both  1148.395062    126111.978261                   4.391304   \n",
       "1   both    35.042735     98260.000000                   4.400000   \n",
       "2   both    58.417850              NaN                        NaN   \n",
       "3   both   333.521924    201593.181818                   4.738636   \n",
       "4   both  1223.636364    142893.235294                   4.823529   \n",
       "\n",
       "   Surface terrain  Surface Carrez      Prix_m2   N     _merge  \n",
       "0       410.869565       93.391304  1368.677765 NaN       both  \n",
       "1       456.400000       99.200000  1002.946560 NaN       both  \n",
       "2              NaN             NaN          NaN NaN  left_only  \n",
       "3       493.113636      107.909091  1924.344829 NaN       both  \n",
       "4       327.500000      117.735294  1239.205157 NaN       both  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " Rows in all_data 36748 \n",
      " Rows in com_immo 32700 \n",
      " Rows successfuly merged : 29143 \n",
      " Rows in all_data but not in com_immo : 7605 \n",
      " Rows in com_immo but not in all_data : 6138\n"
     ]
    }
   ],
   "source": [
    "print('\\n','Rows in all_data', all_data.shape[0], '\\n',\n",
    "      'Rows in com_immo', com_immo.shape[0], '\\n',\n",
    "    'Rows successfuly merged :', merge[merge['_merge']=='both'].shape[0],\n",
    "      '\\n',\n",
    "    'Rows in all_data but not in com_immo :', merge[merge['_merge']=='left_only'].shape[0],\n",
    "      '\\n',\n",
    "    'Rows in com_immo but not in all_data :', merge[merge['_merge']=='right_only'].shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We couldn't merge around 6100 rows of com_immo into the dataframe all_data (which is supposedly more comprehensive). \n",
    "<br><br>\n",
    "Many rows could not merge because they do not have the exact same way of spelling/writing the Commune name.\n",
    "For instance the Commune 'ABBANS-DESSOUS' is written like that 'ABBANS DESSOUS' in com_immo, or the commune 'L'ABERGEMENT-SAINTE-COLOMBE' is written 'ABERGEMENT STE COLOMBE L'. <br> Let's try to correct the Commune names of the rows that couldn't be merged."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Correcting the name of Communes in com_immo before trying to merge the data again"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To correct the names of the Commune first we will create 2 lists :\n",
    "- a list called list_inaccurate_label to gather all Communes names from the rows of com_immo that couldn't be merged. \n",
    "- a list called list_of_choices to gather all Communes names from the rows of all_data that couldn't be matched. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " Size of list_inaccurate_label 6138 \n",
      " Size of list_of_choices 7605\n",
      "['ABBANS DESSOUS', 'ABBANS DESSUS', 'ABBEVILLE ST LUCIEN', 'ABERGEMENT STE COLOMBE L', 'ABERGEMENT-DE-CUISERY (L )']\n",
      "['PETTONCOURT', 'SAINTE-MARIE-DE-VATIMESNIL', \"COLLONGES-AU-MONT-D'OR\", 'SICKERT', \"LES ROCHES-L'EVEQUE\"]\n"
     ]
    }
   ],
   "source": [
    "list_inaccurate_label=list(merge.Commune[merge['_merge']=='right_only'])\n",
    "list_of_choices=list(merge.Commune[merge['_merge']=='left_only'])\n",
    "print('\\n','Size of list_inaccurate_label' , len(list_inaccurate_label),'\\n', 'Size of list_of_choices', len(list_of_choices))\n",
    "print(list_inaccurate_label[0:5])\n",
    "print(list_of_choices[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use a library called fuzzywuzzy that will allow us to match: the names of Communes in com_immo, to the came of Commune in  all_data. This library will return a score indicating how similar they names are. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "from fuzzywuzzy import fuzz\n",
    "from fuzzywuzzy import process"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creating a dataframe called correction that shows the inaccurate and accurate labels of Communes next to their matching score "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Temps de traitement : 652.6506822109222 secondes\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "correction=[]\n",
    "for innaccurate_label in list_inaccurate_label :\n",
    "    output=process.extractOne(innaccurate_label, list_of_choices, scorer=fuzz.token_sort_ratio)\n",
    "    correction.append([output[1], innaccurate_label, output[0]])\n",
    "correction\n",
    "correction=pd.DataFrame(correction, columns=['score', 'inaccurate_label', 'corrected_label'])\n",
    "\n",
    "end = time.time()\n",
    "temps=end - start\n",
    "print('Temps de traitement :',temps, 'secondes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6138, 3)\n"
     ]
    },
    {
     "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>score</th>\n",
       "      <th>inaccurate_label</th>\n",
       "      <th>corrected_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>ABBANS DESSOUS</td>\n",
       "      <td>ABBANS-DESSOUS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>ABBANS DESSUS</td>\n",
       "      <td>ABBANS-DESSUS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>93</td>\n",
       "      <td>ABBEVILLE ST LUCIEN</td>\n",
       "      <td>ABBEVILLE-SAINT-LUCIEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>94</td>\n",
       "      <td>ABERGEMENT STE COLOMBE L</td>\n",
       "      <td>L'ABERGEMENT-SAINTE-COLOMBE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100</td>\n",
       "      <td>ABERGEMENT-DE-CUISERY (L )</td>\n",
       "      <td>L'ABERGEMENT-DE-CUISERY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   score            inaccurate_label              corrected_label\n",
       "0    100              ABBANS DESSOUS               ABBANS-DESSOUS\n",
       "1    100               ABBANS DESSUS                ABBANS-DESSUS\n",
       "2     93         ABBEVILLE ST LUCIEN       ABBEVILLE-SAINT-LUCIEN\n",
       "3     94    ABERGEMENT STE COLOMBE L  L'ABERGEMENT-SAINTE-COLOMBE\n",
       "4    100  ABERGEMENT-DE-CUISERY (L )      L'ABERGEMENT-DE-CUISERY"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#correction.to_csv(r'correction.csv', index=False)\n",
    "correction=pd.read_csv(r'correction.csv')\n",
    "print(correction.shape)\n",
    "correction.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Manually correcting the name of some boroughs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The name of the boroughs in the cities of Lyon, Marseille and Paris are too different and can't be matched with fuzzywuzzy, we will match them manually. To do so, I have created a csv file calle cities_boroughs with the accurrate labeling of the boroughs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(45, 3)\n"
     ]
    },
    {
     "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>score</th>\n",
       "      <th>inaccurate_label</th>\n",
       "      <th>corrected_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>PARIS 01</td>\n",
       "      <td>PARIS-1ER-ARRONDISSEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>PARIS 02</td>\n",
       "      <td>PARIS-2E-ARRONDISSEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100</td>\n",
       "      <td>PARIS 03</td>\n",
       "      <td>PARIS-3E-ARRONDISSEMENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   score inaccurate_label           corrected_label\n",
       "0    100         PARIS 01  PARIS-1ER-ARRONDISSEMENT\n",
       "1    100         PARIS 02   PARIS-2E-ARRONDISSEMENT\n",
       "2    100         PARIS 03   PARIS-3E-ARRONDISSEMENT"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load the manually matched labels of the boroughs\n",
    "cities_boroughs=pd.read_csv(r'cities_boroughs.csv')\n",
    "print(cities_boroughs.shape)\n",
    "cities_boroughs.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6183, 3)\n"
     ]
    }
   ],
   "source": [
    "# Merging the loaded labels\n",
    "correction=correction.append(cities_boroughs)\n",
    "print(correction.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4608, 3)\n"
     ]
    },
    {
     "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>score</th>\n",
       "      <th>inaccurate_label</th>\n",
       "      <th>corrected_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>93</td>\n",
       "      <td>ST GERMAIN LA POTERIE</td>\n",
       "      <td>SAINT-GERMAIN-LA-POTERIE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>93</td>\n",
       "      <td>ST MARTIN DE GURSON</td>\n",
       "      <td>SAINT-MARTIN-DE-GURSON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>93</td>\n",
       "      <td>CHANTENAY-ST-IMBERT</td>\n",
       "      <td>CHANTENAY-SAINT-IMBERT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   score       inaccurate_label           corrected_label\n",
       "0     93  ST GERMAIN LA POTERIE  SAINT-GERMAIN-LA-POTERIE\n",
       "1     93    ST MARTIN DE GURSON    SAINT-MARTIN-DE-GURSON\n",
       "2     93    CHANTENAY-ST-IMBERT    CHANTENAY-SAINT-IMBERT"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Keeping only matched labels with an score above 92\n",
    "correction=correction[correction.score>92].sort_values('score', )\n",
    "correction.reset_index(drop=True, inplace=True)\n",
    "print(correction.shape)\n",
    "correction.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Applying the correction to com_immo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(32700, 10)\n"
     ]
    },
    {
     "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>Commune</th>\n",
       "      <th>Valeur fonciere</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Code commune</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Surface terrain</th>\n",
       "      <th>Surface Carrez</th>\n",
       "      <th>Prix_m2</th>\n",
       "      <th>N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAST</td>\n",
       "      <td>129665.200000</td>\n",
       "      <td>64460.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.600000</td>\n",
       "      <td>608.400000</td>\n",
       "      <td>118.600000</td>\n",
       "      <td>1075.662291</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABAINVILLE</td>\n",
       "      <td>63735.164286</td>\n",
       "      <td>55130.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.857143</td>\n",
       "      <td>396.142857</td>\n",
       "      <td>88.285714</td>\n",
       "      <td>682.972083</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABANCOURT</td>\n",
       "      <td>120205.960952</td>\n",
       "      <td>59902.666667</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.095238</td>\n",
       "      <td>553.095238</td>\n",
       "      <td>90.904762</td>\n",
       "      <td>1379.239892</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ABAUCOURT</td>\n",
       "      <td>133970.214286</td>\n",
       "      <td>54610.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.857143</td>\n",
       "      <td>441.571429</td>\n",
       "      <td>109.071429</td>\n",
       "      <td>1360.566532</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAUCOURT-HAUTECOURT</td>\n",
       "      <td>84500.000000</td>\n",
       "      <td>55400.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>696.000000</td>\n",
       "      <td>142.000000</td>\n",
       "      <td>609.778226</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Commune  Valeur fonciere   Code postal  Code commune  \\\n",
       "0                  AAST    129665.200000  64460.000000           1.0   \n",
       "1            ABAINVILLE     63735.164286  55130.000000           1.0   \n",
       "2             ABANCOURT    120205.960952  59902.666667           1.0   \n",
       "3             ABAUCOURT    133970.214286  54610.000000           1.0   \n",
       "4  ABAUCOURT-HAUTECOURT     84500.000000  55400.000000           2.0   \n",
       "\n",
       "   Code type local  Nombre pieces principales  Surface terrain  \\\n",
       "0              1.0                   4.600000       608.400000   \n",
       "1              1.0                   3.857143       396.142857   \n",
       "2              1.0                   4.095238       553.095238   \n",
       "3              1.0                   3.857143       441.571429   \n",
       "4              1.0                   6.000000       696.000000   \n",
       "\n",
       "   Surface Carrez      Prix_m2   N  \n",
       "0      118.600000  1075.662291   5  \n",
       "1       88.285714   682.972083  14  \n",
       "2       90.904762  1379.239892  42  \n",
       "3      109.071429  1360.566532  14  \n",
       "4      142.000000   609.778226   2  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#corrected_com_immo=com_immo.copy()\n",
    "for i in range(0,correction.shape[0]) :\n",
    "    com_immo.loc[com_immo.Commune==correction.inaccurate_label[i],'Commune']=correction.corrected_label[i]      \n",
    "print(com_immo.shape)\n",
    "com_immo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>Commune</th>\n",
       "      <th>Valeur fonciere</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Code commune</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Surface terrain</th>\n",
       "      <th>Surface Carrez</th>\n",
       "      <th>Prix_m2</th>\n",
       "      <th>N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAST</td>\n",
       "      <td>129665.200000</td>\n",
       "      <td>64460.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.600000</td>\n",
       "      <td>608.400000</td>\n",
       "      <td>118.600000</td>\n",
       "      <td>1075.662291</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABAINVILLE</td>\n",
       "      <td>63735.164286</td>\n",
       "      <td>55130.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.857143</td>\n",
       "      <td>396.142857</td>\n",
       "      <td>88.285714</td>\n",
       "      <td>682.972083</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABANCOURT</td>\n",
       "      <td>120205.960952</td>\n",
       "      <td>59902.666667</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.095238</td>\n",
       "      <td>553.095238</td>\n",
       "      <td>90.904762</td>\n",
       "      <td>1379.239892</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ABAUCOURT</td>\n",
       "      <td>133970.214286</td>\n",
       "      <td>54610.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.857143</td>\n",
       "      <td>441.571429</td>\n",
       "      <td>109.071429</td>\n",
       "      <td>1360.566532</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAUCOURT-HAUTECOURT</td>\n",
       "      <td>84500.000000</td>\n",
       "      <td>55400.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>696.000000</td>\n",
       "      <td>142.000000</td>\n",
       "      <td>609.778226</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Commune  Valeur fonciere   Code postal  Code commune  \\\n",
       "0                  AAST    129665.200000  64460.000000           1.0   \n",
       "1            ABAINVILLE     63735.164286  55130.000000           1.0   \n",
       "2             ABANCOURT    120205.960952  59902.666667           1.0   \n",
       "3             ABAUCOURT    133970.214286  54610.000000           1.0   \n",
       "4  ABAUCOURT-HAUTECOURT     84500.000000  55400.000000           2.0   \n",
       "\n",
       "   Code type local  Nombre pieces principales  Surface terrain  \\\n",
       "0              1.0                   4.600000       608.400000   \n",
       "1              1.0                   3.857143       396.142857   \n",
       "2              1.0                   4.095238       553.095238   \n",
       "3              1.0                   3.857143       441.571429   \n",
       "4              1.0                   6.000000       696.000000   \n",
       "\n",
       "   Surface Carrez      Prix_m2   N  \n",
       "0      118.600000  1075.662291   5  \n",
       "1       88.285714   682.972083  14  \n",
       "2       90.904762  1379.239892  42  \n",
       "3      109.071429  1360.566532  14  \n",
       "4      142.000000   609.778226   2  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#com_immo.to_csv(r'com_immo.csv', index=False)\n",
    "com_immo=pd.read_csv(r'com_immo.csv')\n",
    "com_immo.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Merging the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_data_immo=pd.merge(all_data,com_immo,on='Commune', how='outer', indicator=True)\n",
    "all_data_immo.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "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>Code INSEE</th>\n",
       "      <th>Code Postal</th>\n",
       "      <th>Commune</th>\n",
       "      <th>Code Département</th>\n",
       "      <th>Code Région</th>\n",
       "      <th>Superficie_km2</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>age_mean</th>\n",
       "      <th>age_std</th>\n",
       "      <th>age_median</th>\n",
       "      <th>population</th>\n",
       "      <th>merge1</th>\n",
       "      <th>Fhousehold</th>\n",
       "      <th>person_p_Fhousehold</th>\n",
       "      <th>revenue_median</th>\n",
       "      <th>revenue_inequalities</th>\n",
       "      <th>merge2</th>\n",
       "      <th>density</th>\n",
       "      <th>Valeur fonciere</th>\n",
       "      <th>Code postal</th>\n",
       "      <th>Code commune</th>\n",
       "      <th>Code type local</th>\n",
       "      <th>Nombre pieces principales</th>\n",
       "      <th>Surface terrain</th>\n",
       "      <th>Surface Carrez</th>\n",
       "      <th>Prix_m2</th>\n",
       "      <th>N</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59276</td>\n",
       "      <td>59287</td>\n",
       "      <td>GUESNAIN</td>\n",
       "      <td>59</td>\n",
       "      <td>31.0</td>\n",
       "      <td>4.05</td>\n",
       "      <td>50.348346</td>\n",
       "      <td>3.148167</td>\n",
       "      <td>40.851860</td>\n",
       "      <td>24.642605</td>\n",
       "      <td>41.0</td>\n",
       "      <td>4651.0</td>\n",
       "      <td>both</td>\n",
       "      <td>1884.0</td>\n",
       "      <td>4624.0</td>\n",
       "      <td>17270.0</td>\n",
       "      <td>2.6</td>\n",
       "      <td>both</td>\n",
       "      <td>1148.395062</td>\n",
       "      <td>126111.978261</td>\n",
       "      <td>59287.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.391304</td>\n",
       "      <td>410.869565</td>\n",
       "      <td>93.391304</td>\n",
       "      <td>1368.677765</td>\n",
       "      <td>138.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>88128</td>\n",
       "      <td>88210</td>\n",
       "      <td>DENIPAIRE</td>\n",
       "      <td>88</td>\n",
       "      <td>41.0</td>\n",
       "      <td>7.02</td>\n",
       "      <td>48.339817</td>\n",
       "      <td>6.961893</td>\n",
       "      <td>43.832158</td>\n",
       "      <td>22.810009</td>\n",
       "      <td>48.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>both</td>\n",
       "      <td>108.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>21720.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>35.042735</td>\n",
       "      <td>98260.000000</td>\n",
       "      <td>88210.0</td>\n",
       "      <td>128.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.400000</td>\n",
       "      <td>456.400000</td>\n",
       "      <td>99.200000</td>\n",
       "      <td>1002.946560</td>\n",
       "      <td>5.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>57538</td>\n",
       "      <td>57170</td>\n",
       "      <td>PETTONCOURT</td>\n",
       "      <td>57</td>\n",
       "      <td>41.0</td>\n",
       "      <td>4.93</td>\n",
       "      <td>48.788126</td>\n",
       "      <td>6.411069</td>\n",
       "      <td>40.315972</td>\n",
       "      <td>23.739636</td>\n",
       "      <td>41.0</td>\n",
       "      <td>288.0</td>\n",
       "      <td>both</td>\n",
       "      <td>109.0</td>\n",
       "      <td>298.0</td>\n",
       "      <td>20970.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>58.417850</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>54459</td>\n",
       "      <td>54630</td>\n",
       "      <td>RICHARDMENIL</td>\n",
       "      <td>54</td>\n",
       "      <td>41.0</td>\n",
       "      <td>7.07</td>\n",
       "      <td>48.595865</td>\n",
       "      <td>6.176145</td>\n",
       "      <td>46.335881</td>\n",
       "      <td>23.049189</td>\n",
       "      <td>51.0</td>\n",
       "      <td>2358.0</td>\n",
       "      <td>both</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>2395.0</td>\n",
       "      <td>25960.0</td>\n",
       "      <td>2.4</td>\n",
       "      <td>both</td>\n",
       "      <td>333.521924</td>\n",
       "      <td>201593.181818</td>\n",
       "      <td>54630.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.738636</td>\n",
       "      <td>493.113636</td>\n",
       "      <td>107.909091</td>\n",
       "      <td>1924.344829</td>\n",
       "      <td>88.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35022</td>\n",
       "      <td>35190</td>\n",
       "      <td>BECHEREL</td>\n",
       "      <td>35</td>\n",
       "      <td>53.0</td>\n",
       "      <td>0.55</td>\n",
       "      <td>48.296509</td>\n",
       "      <td>-1.942824</td>\n",
       "      <td>41.722078</td>\n",
       "      <td>25.144570</td>\n",
       "      <td>42.0</td>\n",
       "      <td>673.0</td>\n",
       "      <td>both</td>\n",
       "      <td>293.0</td>\n",
       "      <td>632.0</td>\n",
       "      <td>19260.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>both</td>\n",
       "      <td>1223.636364</td>\n",
       "      <td>142893.235294</td>\n",
       "      <td>35190.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.823529</td>\n",
       "      <td>327.500000</td>\n",
       "      <td>117.735294</td>\n",
       "      <td>1239.205157</td>\n",
       "      <td>34.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Code INSEE Code Postal       Commune Code Département  Code Région  \\\n",
       "0      59276       59287      GUESNAIN               59         31.0   \n",
       "1      88128       88210     DENIPAIRE               88         41.0   \n",
       "2      57538       57170   PETTONCOURT               57         41.0   \n",
       "3      54459       54630  RICHARDMENIL               54         41.0   \n",
       "4      35022       35190      BECHEREL               35         53.0   \n",
       "\n",
       "   Superficie_km2        lat       lng   age_mean    age_std  age_median  \\\n",
       "0            4.05  50.348346  3.148167  40.851860  24.642605        41.0   \n",
       "1            7.02  48.339817  6.961893  43.832158  22.810009        48.0   \n",
       "2            4.93  48.788126  6.411069  40.315972  23.739636        41.0   \n",
       "3            7.07  48.595865  6.176145  46.335881  23.049189        51.0   \n",
       "4            0.55  48.296509 -1.942824  41.722078  25.144570        42.0   \n",
       "\n",
       "   population merge1  Fhousehold  person_p_Fhousehold  revenue_median  \\\n",
       "0      4651.0   both      1884.0               4624.0         17270.0   \n",
       "1       246.0   both       108.0                246.0         21720.0   \n",
       "2       288.0   both       109.0                298.0         20970.0   \n",
       "3      2358.0   both      1020.0               2395.0         25960.0   \n",
       "4       673.0   both       293.0                632.0         19260.0   \n",
       "\n",
       "   revenue_inequalities merge2      density  Valeur fonciere  Code postal  \\\n",
       "0                   2.6   both  1148.395062    126111.978261      59287.0   \n",
       "1                   NaN   both    35.042735     98260.000000      88210.0   \n",
       "2                   NaN   both    58.417850              NaN          NaN   \n",
       "3                   2.4   both   333.521924    201593.181818      54630.0   \n",
       "4                   NaN   both  1223.636364    142893.235294      35190.0   \n",
       "\n",
       "   Code commune  Code type local  Nombre pieces principales  Surface terrain  \\\n",
       "0         276.0              1.0                   4.391304       410.869565   \n",
       "1         128.0              1.0                   4.400000       456.400000   \n",
       "2           NaN              NaN                        NaN              NaN   \n",
       "3         459.0              1.0                   4.738636       493.113636   \n",
       "4          22.0              1.0                   4.823529       327.500000   \n",
       "\n",
       "   Surface Carrez      Prix_m2      N     _merge  \n",
       "0       93.391304  1368.677765  138.0       both  \n",
       "1       99.200000  1002.946560    5.0       both  \n",
       "2             NaN          NaN    NaN  left_only  \n",
       "3      107.909091  1924.344829   88.0       both  \n",
       "4      117.735294  1239.205157   34.0       both  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#all_data_immo.to_csv(r'all_data_immo.csv', index=False)\n",
    "#all_data_immo=pd.read_csv(r'all_data_immo.csv')\n",
    "all_data_immo.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's analyse how the merging process went :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " Rows in all_data 36748 \n",
      " Rows in corrected_com_immo 32700 \n",
      " Rows successfuly merged : 33770 \n",
      " Rows in all_data but not in corrected_com_immo : 2999 \n",
      " Rows in com_immo but not in all_data : 1536\n"
     ]
    }
   ],
   "source": [
    "print('\\n','Rows in all_data', all_data.shape[0], '\\n',\n",
    "      'Rows in corrected_com_immo', com_immo.shape[0], '\\n',\n",
    "    'Rows successfuly merged :', all_data_immo[all_data_immo['_merge']=='both'].shape[0],\n",
    "      '\\n',\n",
    "    'Rows in all_data but not in corrected_com_immo :', all_data_immo[all_data_immo['_merge']=='left_only'].shape[0],\n",
    "      '\\n',\n",
    "    'Rows in com_immo but not in all_data :', all_data_immo[all_data_immo['_merge']=='right_only'].shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We managed to merge most of the data. Compared to the first try merging the data, we merge around 4500 rows more. Unfortunately, around 1500 rows over a total of 32500 still couldn't be merge. There is room for improvment here, but some of the communes were actually modified by the French government along the years. We will have to ignore that in this case study. "
   ]
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}