1_Data_Preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preparation and Feature augmentation and engineering\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### House Transactions dataset\n",
    "\n",
    "Steps to follow:\n",
    "\n",
    "- Read data and join with postocdes to geolocate\n",
    "- Upload to GIS server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "#Setting for pandas to show all columns\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "\n",
    "#For GIS Analysis\n",
    "import arcpy\n",
    "\n",
    "#GIS Libraries and credentials\n",
    "from arcgis.gis import GIS\n",
    "from IPython.display import display\n",
    "from arcgis.features import enrich_data\n",
    "from arcgis.features import FeatureLayerCollection\n",
    "from arcgis.geoenrichment import *\n",
    "gis = GIS()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "house_transactions = pd.read_csv('./Data/House_Prices/last_year_house_transactions_london.csv',sep=',')\n",
    "uk_postcodes = pd.read_csv('./Data/House_Prices/ukpostcodes.csv',sep=',')"
   ]
  },
  {
   "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>Transaction unique identifier</th>\n",
       "      <th>Price</th>\n",
       "      <th>Date of Transfer</th>\n",
       "      <th>postcode</th>\n",
       "      <th>Property Type</th>\n",
       "      <th>Town</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{68FEB20C-3FF9-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>460000</td>\n",
       "      <td>11/24/2017</td>\n",
       "      <td>N3 2FH</td>\n",
       "      <td>F</td>\n",
       "      <td>BARNET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{68FEB20C-4001-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>332000</td>\n",
       "      <td>11/20/2017</td>\n",
       "      <td>E14 0HT</td>\n",
       "      <td>F</td>\n",
       "      <td>TOWER HAMLETS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{68FEB20C-4007-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>210000</td>\n",
       "      <td>11/27/2017</td>\n",
       "      <td>UB10 8AU</td>\n",
       "      <td>F</td>\n",
       "      <td>HILLINGDON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{68FEB20C-400F-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>260000</td>\n",
       "      <td>10/6/2017</td>\n",
       "      <td>NW2 6JH</td>\n",
       "      <td>F</td>\n",
       "      <td>BRENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{68FEB20C-4014-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>375000</td>\n",
       "      <td>11/30/2017</td>\n",
       "      <td>NW6 5BA</td>\n",
       "      <td>F</td>\n",
       "      <td>BRENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Transaction unique identifier   Price Date of Transfer  postcode  \\\n",
       "0  {68FEB20C-3FF9-38DA-E053-6C04A8C051AE}  460000       11/24/2017    N3 2FH   \n",
       "1  {68FEB20C-4001-38DA-E053-6C04A8C051AE}  332000       11/20/2017   E14 0HT   \n",
       "2  {68FEB20C-4007-38DA-E053-6C04A8C051AE}  210000       11/27/2017  UB10 8AU   \n",
       "3  {68FEB20C-400F-38DA-E053-6C04A8C051AE}  260000        10/6/2017   NW2 6JH   \n",
       "4  {68FEB20C-4014-38DA-E053-6C04A8C051AE}  375000       11/30/2017   NW6 5BA   \n",
       "\n",
       "  Property Type           Town  \n",
       "0             F         BARNET  \n",
       "1             F  TOWER HAMLETS  \n",
       "2             F     HILLINGDON  \n",
       "3             F          BRENT  \n",
       "4             F          BRENT  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "house_transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>id</th>\n",
       "      <th>postcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>AB10 1XG</td>\n",
       "      <td>57.144165</td>\n",
       "      <td>-2.114848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>AB10 6RN</td>\n",
       "      <td>57.137880</td>\n",
       "      <td>-2.121487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>AB10 7JB</td>\n",
       "      <td>57.124274</td>\n",
       "      <td>-2.127190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>AB11 5QN</td>\n",
       "      <td>57.142701</td>\n",
       "      <td>-2.093295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>AB11 6UL</td>\n",
       "      <td>57.137547</td>\n",
       "      <td>-2.112233</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  postcode   latitude  longitude\n",
       "0   1  AB10 1XG  57.144165  -2.114848\n",
       "1   2  AB10 6RN  57.137880  -2.121487\n",
       "2   3  AB10 7JB  57.124274  -2.127190\n",
       "3   4  AB11 5QN  57.142701  -2.093295\n",
       "4   5  AB11 6UL  57.137547  -2.112233"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uk_postcodes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Transaction unique identifier</th>\n",
       "      <th>Price</th>\n",
       "      <th>Date of Transfer</th>\n",
       "      <th>postcode</th>\n",
       "      <th>Property Type</th>\n",
       "      <th>Town</th>\n",
       "      <th>id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{68FEB20C-4001-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>332000</td>\n",
       "      <td>11/20/2017</td>\n",
       "      <td>E14 0HT</td>\n",
       "      <td>F</td>\n",
       "      <td>TOWER HAMLETS</td>\n",
       "      <td>1250232</td>\n",
       "      <td>51.514245</td>\n",
       "      <td>0.001029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{64342BFD-B07B-422C-E053-6C04A8C0FB8A}</td>\n",
       "      <td>325000</td>\n",
       "      <td>8/16/2017</td>\n",
       "      <td>E14 0HT</td>\n",
       "      <td>F</td>\n",
       "      <td>TOWER HAMLETS</td>\n",
       "      <td>1250232</td>\n",
       "      <td>51.514245</td>\n",
       "      <td>0.001029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{68FEB20C-4007-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>210000</td>\n",
       "      <td>11/27/2017</td>\n",
       "      <td>UB10 8AU</td>\n",
       "      <td>F</td>\n",
       "      <td>HILLINGDON</td>\n",
       "      <td>124987</td>\n",
       "      <td>51.560992</td>\n",
       "      <td>-0.468725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{666758D7-90D6-3363-E053-6B04A8C0D74E}</td>\n",
       "      <td>520000</td>\n",
       "      <td>11/27/2017</td>\n",
       "      <td>UB10 8AU</td>\n",
       "      <td>F</td>\n",
       "      <td>HILLINGDON</td>\n",
       "      <td>124987</td>\n",
       "      <td>51.560992</td>\n",
       "      <td>-0.468725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{68FEB20C-400F-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>260000</td>\n",
       "      <td>10/6/2017</td>\n",
       "      <td>NW2 6JH</td>\n",
       "      <td>F</td>\n",
       "      <td>BRENT</td>\n",
       "      <td>637800</td>\n",
       "      <td>51.558771</td>\n",
       "      <td>-0.246120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Transaction unique identifier   Price Date of Transfer  postcode  \\\n",
       "0  {68FEB20C-4001-38DA-E053-6C04A8C051AE}  332000       11/20/2017   E14 0HT   \n",
       "1  {64342BFD-B07B-422C-E053-6C04A8C0FB8A}  325000        8/16/2017   E14 0HT   \n",
       "2  {68FEB20C-4007-38DA-E053-6C04A8C051AE}  210000       11/27/2017  UB10 8AU   \n",
       "3  {666758D7-90D6-3363-E053-6B04A8C0D74E}  520000       11/27/2017  UB10 8AU   \n",
       "4  {68FEB20C-400F-38DA-E053-6C04A8C051AE}  260000        10/6/2017   NW2 6JH   \n",
       "\n",
       "  Property Type           Town       id   latitude  longitude  \n",
       "0             F  TOWER HAMLETS  1250232  51.514245   0.001029  \n",
       "1             F  TOWER HAMLETS  1250232  51.514245   0.001029  \n",
       "2             F     HILLINGDON   124987  51.560992  -0.468725  \n",
       "3             F     HILLINGDON   124987  51.560992  -0.468725  \n",
       "4             F          BRENT   637800  51.558771  -0.246120  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "house_transactions = pd.merge(house_transactions,uk_postcodes, on='postcode')\n",
    "house_transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Transaction unique identifier</th>\n",
       "      <th>Price</th>\n",
       "      <th>Date of Transfer</th>\n",
       "      <th>postcode</th>\n",
       "      <th>Property Type</th>\n",
       "      <th>Town</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{68FEB20C-4001-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>332000</td>\n",
       "      <td>11/20/2017</td>\n",
       "      <td>E14 0HT</td>\n",
       "      <td>F</td>\n",
       "      <td>TOWER HAMLETS</td>\n",
       "      <td>51.514245</td>\n",
       "      <td>0.001029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{64342BFD-B07B-422C-E053-6C04A8C0FB8A}</td>\n",
       "      <td>325000</td>\n",
       "      <td>8/16/2017</td>\n",
       "      <td>E14 0HT</td>\n",
       "      <td>F</td>\n",
       "      <td>TOWER HAMLETS</td>\n",
       "      <td>51.514245</td>\n",
       "      <td>0.001029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{68FEB20C-4007-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>210000</td>\n",
       "      <td>11/27/2017</td>\n",
       "      <td>UB10 8AU</td>\n",
       "      <td>F</td>\n",
       "      <td>HILLINGDON</td>\n",
       "      <td>51.560992</td>\n",
       "      <td>-0.468725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{666758D7-90D6-3363-E053-6B04A8C0D74E}</td>\n",
       "      <td>520000</td>\n",
       "      <td>11/27/2017</td>\n",
       "      <td>UB10 8AU</td>\n",
       "      <td>F</td>\n",
       "      <td>HILLINGDON</td>\n",
       "      <td>51.560992</td>\n",
       "      <td>-0.468725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{68FEB20C-400F-38DA-E053-6C04A8C051AE}</td>\n",
       "      <td>260000</td>\n",
       "      <td>10/6/2017</td>\n",
       "      <td>NW2 6JH</td>\n",
       "      <td>F</td>\n",
       "      <td>BRENT</td>\n",
       "      <td>51.558771</td>\n",
       "      <td>-0.246120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Transaction unique identifier   Price Date of Transfer  postcode  \\\n",
       "0  {68FEB20C-4001-38DA-E053-6C04A8C051AE}  332000       11/20/2017   E14 0HT   \n",
       "1  {64342BFD-B07B-422C-E053-6C04A8C0FB8A}  325000        8/16/2017   E14 0HT   \n",
       "2  {68FEB20C-4007-38DA-E053-6C04A8C051AE}  210000       11/27/2017  UB10 8AU   \n",
       "3  {666758D7-90D6-3363-E053-6B04A8C0D74E}  520000       11/27/2017  UB10 8AU   \n",
       "4  {68FEB20C-400F-38DA-E053-6C04A8C051AE}  260000        10/6/2017   NW2 6JH   \n",
       "\n",
       "  Property Type           Town   latitude  longitude  \n",
       "0             F  TOWER HAMLETS  51.514245   0.001029  \n",
       "1             F  TOWER HAMLETS  51.514245   0.001029  \n",
       "2             F     HILLINGDON  51.560992  -0.468725  \n",
       "3             F     HILLINGDON  51.560992  -0.468725  \n",
       "4             F          BRENT  51.558771  -0.246120  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "house_transactions = house_transactions.drop(labels=['id'], axis=1)\n",
    "house_transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Number of Null</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>longitude</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latitude</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Town</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Property Type</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>postcode</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date of Transfer</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Price</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transaction unique identifier</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Number of Null\n",
       "longitude                                   0\n",
       "latitude                                    0\n",
       "Town                                        0\n",
       "Property Type                               0\n",
       "postcode                                    0\n",
       "Date of Transfer                            0\n",
       "Price                                       0\n",
       "Transaction unique identifier               0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#how many nulls?\n",
    "pd.DataFrame(house_transactions.isnull().sum().sort_values(ascending=False), columns = ['Number of Null']).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "house_transactions.to_csv('./Data/House_Prices/house_transactions_geolocated.csv',\n",
    "                         index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create Shapefile**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Result '.\\\\Data\\\\Shapefiles\\\\house_transactions.shp'>"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#we create the shapefile\n",
    "arcpy.env.workspace = './Data/Shapefiles'\n",
    "arcpy.management.XYTableToPoint('./Data/House_Prices/house_transactions_geolocated.csv','./Data/Shapefiles/house_transactions.shp',\n",
    "                                \"longitude\", \"latitude\", \"\",\n",
    "                                arcpy.SpatialReference(4326, 115700))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hotels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Price_Night_USD</th>\n",
       "      <th>Total_Reviews</th>\n",
       "      <th>Stars_Rating</th>\n",
       "      <th>Link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Arch London</td>\n",
       "      <td>257.0</td>\n",
       "      <td>1992</td>\n",
       "      <td>5.0</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Pelham – Starhotels Collezione</td>\n",
       "      <td>264.0</td>\n",
       "      <td>940</td>\n",
       "      <td>4.5</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Premier Inn London Kings Cross Hotel</td>\n",
       "      <td>160.0</td>\n",
       "      <td>4899</td>\n",
       "      <td>4.5</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mondrian London at Sea Containers</td>\n",
       "      <td>286.0</td>\n",
       "      <td>4289</td>\n",
       "      <td>4.5</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Royal Horseguards</td>\n",
       "      <td>205.0</td>\n",
       "      <td>5188</td>\n",
       "      <td>4.5</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Name  Price_Night_USD  Total_Reviews  \\\n",
       "0                       The Arch London            257.0           1992   \n",
       "1    The Pelham – Starhotels Collezione            264.0            940   \n",
       "2  Premier Inn London Kings Cross Hotel            160.0           4899   \n",
       "3     Mondrian London at Sea Containers            286.0           4289   \n",
       "4                 The Royal Horseguards            205.0           5188   \n",
       "\n",
       "   Stars_Rating                                               Link  \n",
       "0           5.0  https://www.tripadvisor.com/Hotel_Review-g1863...  \n",
       "1           4.5  https://www.tripadvisor.com/Hotel_Review-g1863...  \n",
       "2           4.5  https://www.tripadvisor.com/Hotel_Review-g1863...  \n",
       "3           4.5  https://www.tripadvisor.com/Hotel_Review-g1863...  \n",
       "4           4.5  https://www.tripadvisor.com/Hotel_Review-g1863...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#List of hotels, selenium scraper\n",
    "hotels_list = pd.read_csv('./Data/Hotels/hotel_list_london.csv',\n",
    "                    names=['Name','Price_Night_USD','Total_Reviews','Stars_Rating','Ranking_best_value','Link'])\n",
    "hotels_list = hotels_list.drop(labels='Ranking_best_value', axis=1)\n",
    "hotels_list.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>h_address</th>\n",
       "      <th>postcode</th>\n",
       "      <th>h_amenities</th>\n",
       "      <th>h_link</th>\n",
       "      <th>h_ranking_users</th>\n",
       "      <th>h_star</th>\n",
       "      <th>r_average</th>\n",
       "      <th>r_excellent</th>\n",
       "      <th>r_poor</th>\n",
       "      <th>r_terrible</th>\n",
       "      <th>r_very_good</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50 Great Cumberland Place</td>\n",
       "      <td>W1H 7FD</td>\n",
       "      <td>Restaurant,Free High Speed Internet (WiFi),Fit...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1480.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>215.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Shoreditch High Street</td>\n",
       "      <td>E1 6JQ</td>\n",
       "      <td>Restaurant,Room Service,Fitness Center with Gy...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>363.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>531.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>199 - 206 High Holborn</td>\n",
       "      <td>WC1V 7BD</td>\n",
       "      <td>Free High Speed Internet (WiFi),Room Service,R...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>1167.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>383.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10 Manchester Street</td>\n",
       "      <td>W1U 4DG</td>\n",
       "      <td>Room Service,Restaurant,Free High Speed Intern...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>238.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>464.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>108 Baker Street</td>\n",
       "      <td>W1U 6LJ</td>\n",
       "      <td>Room Service,Fitness Center with Gym / Workout...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>365.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>312.0</td>\n",
       "      <td>914.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>814.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     h_address  postcode  \\\n",
       "0   50 Great Cumberland Place    W1H 7FD   \n",
       "1  100 Shoreditch High Street     E1 6JQ   \n",
       "2      199 - 206 High Holborn   WC1V 7BD   \n",
       "3        10 Manchester Street    W1U 4DG   \n",
       "4            108 Baker Street    W1U 6LJ   \n",
       "\n",
       "                                         h_amenities  \\\n",
       "0  Restaurant,Free High Speed Internet (WiFi),Fit...   \n",
       "1  Restaurant,Room Service,Fitness Center with Gy...   \n",
       "2  Free High Speed Internet (WiFi),Room Service,R...   \n",
       "3  Room Service,Restaurant,Free High Speed Intern...   \n",
       "4  Room Service,Fitness Center with Gym / Workout...   \n",
       "\n",
       "                                              h_link  h_ranking_users  h_star  \\\n",
       "0  https://www.tripadvisor.com/Hotel_Review-g1863...              9.0     5.0   \n",
       "1  https://www.tripadvisor.com/Hotel_Review-g1863...            363.0     5.0   \n",
       "2  https://www.tripadvisor.com/Hotel_Review-g1863...             76.0     4.0   \n",
       "3  https://www.tripadvisor.com/Hotel_Review-g1863...            238.0     4.0   \n",
       "4  https://www.tripadvisor.com/Hotel_Review-g1863...            365.0     4.0   \n",
       "\n",
       "   r_average  r_excellent  r_poor  r_terrible  r_very_good  \n",
       "0       48.0       1480.0     7.0         5.0        215.0  \n",
       "1      161.0        531.0    63.0        65.0        320.0  \n",
       "2       78.0       1167.0    15.0         6.0        383.0  \n",
       "3       74.0        464.0    25.0        19.0        207.0  \n",
       "4      312.0        914.0   127.0        76.0        814.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Details of hotels, including the address that we will merge\n",
    "hotels_details = pd.read_csv('./Data/Hotels/hotel_details_london.csv')\n",
    "hotels_details.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>h_address</th>\n",
       "      <th>postcode</th>\n",
       "      <th>h_amenities</th>\n",
       "      <th>h_link</th>\n",
       "      <th>h_ranking_users</th>\n",
       "      <th>h_star</th>\n",
       "      <th>r_average</th>\n",
       "      <th>r_excellent</th>\n",
       "      <th>r_poor</th>\n",
       "      <th>r_terrible</th>\n",
       "      <th>r_very_good</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50 Great Cumberland Place</td>\n",
       "      <td>W1H 7FD</td>\n",
       "      <td>Restaurant,Free High Speed Internet (WiFi),Fit...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1480.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>51.515962</td>\n",
       "      <td>-0.159802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Shoreditch High Street</td>\n",
       "      <td>E1 6JQ</td>\n",
       "      <td>Restaurant,Room Service,Fitness Center with Gy...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>363.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>531.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>51.525743</td>\n",
       "      <td>-0.077353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>199 - 206 High Holborn</td>\n",
       "      <td>WC1V 7BD</td>\n",
       "      <td>Free High Speed Internet (WiFi),Room Service,R...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>1167.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>383.0</td>\n",
       "      <td>51.516893</td>\n",
       "      <td>-0.122558</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10 Manchester Street</td>\n",
       "      <td>W1U 4DG</td>\n",
       "      <td>Room Service,Restaurant,Free High Speed Intern...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>238.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>464.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>51.518800</td>\n",
       "      <td>-0.153879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>108 Baker Street</td>\n",
       "      <td>W1U 6LJ</td>\n",
       "      <td>Room Service,Fitness Center with Gym / Workout...</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>365.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>312.0</td>\n",
       "      <td>914.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>814.0</td>\n",
       "      <td>51.520914</td>\n",
       "      <td>-0.156705</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     h_address  postcode  \\\n",
       "0   50 Great Cumberland Place    W1H 7FD   \n",
       "1  100 Shoreditch High Street     E1 6JQ   \n",
       "2      199 - 206 High Holborn   WC1V 7BD   \n",
       "3        10 Manchester Street    W1U 4DG   \n",
       "4            108 Baker Street    W1U 6LJ   \n",
       "\n",
       "                                         h_amenities  \\\n",
       "0  Restaurant,Free High Speed Internet (WiFi),Fit...   \n",
       "1  Restaurant,Room Service,Fitness Center with Gy...   \n",
       "2  Free High Speed Internet (WiFi),Room Service,R...   \n",
       "3  Room Service,Restaurant,Free High Speed Intern...   \n",
       "4  Room Service,Fitness Center with Gym / Workout...   \n",
       "\n",
       "                                              h_link  h_ranking_users  h_star  \\\n",
       "0  https://www.tripadvisor.com/Hotel_Review-g1863...              9.0     5.0   \n",
       "1  https://www.tripadvisor.com/Hotel_Review-g1863...            363.0     5.0   \n",
       "2  https://www.tripadvisor.com/Hotel_Review-g1863...             76.0     4.0   \n",
       "3  https://www.tripadvisor.com/Hotel_Review-g1863...            238.0     4.0   \n",
       "4  https://www.tripadvisor.com/Hotel_Review-g1863...            365.0     4.0   \n",
       "\n",
       "   r_average  r_excellent  r_poor  r_terrible  r_very_good   latitude  \\\n",
       "0       48.0       1480.0     7.0         5.0        215.0  51.515962   \n",
       "1      161.0        531.0    63.0        65.0        320.0  51.525743   \n",
       "2       78.0       1167.0    15.0         6.0        383.0  51.516893   \n",
       "3       74.0        464.0    25.0        19.0        207.0  51.518800   \n",
       "4      312.0        914.0   127.0        76.0        814.0  51.520914   \n",
       "\n",
       "   longitude  \n",
       "0  -0.159802  \n",
       "1  -0.077353  \n",
       "2  -0.122558  \n",
       "3  -0.153879  \n",
       "4  -0.156705  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hotels_details = pd.merge(hotels_details,uk_postcodes, on='postcode')\n",
    "hotels_details = hotels_details.drop(labels='id',axis=1)\n",
    "hotels_details.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>h_address</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>postcode</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h_amenities</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h_link</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h_ranking_users</th>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h_star</th>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r_average</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r_excellent</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r_poor</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r_terrible</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r_very_good</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latitude</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>longitude</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   0\n",
       "h_address          0\n",
       "postcode           0\n",
       "h_amenities        5\n",
       "h_link             0\n",
       "h_ranking_users   69\n",
       "h_star           114\n",
       "r_average         17\n",
       "r_excellent       17\n",
       "r_poor            17\n",
       "r_terrible        17\n",
       "r_very_good       17\n",
       "latitude           0\n",
       "longitude          0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#cheking null values\n",
    "pd.DataFrame(hotels_details.isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>index</th>\n",
       "      <th>Name</th>\n",
       "      <th>Price_Night_USD</th>\n",
       "      <th>Total_Reviews</th>\n",
       "      <th>Stars_Rating</th>\n",
       "      <th>Link</th>\n",
       "      <th>h_address</th>\n",
       "      <th>postcode</th>\n",
       "      <th>h_amenities</th>\n",
       "      <th>h_ranking_users</th>\n",
       "      <th>h_star</th>\n",
       "      <th>r_average</th>\n",
       "      <th>r_excellent</th>\n",
       "      <th>r_poor</th>\n",
       "      <th>r_terrible</th>\n",
       "      <th>r_very_good</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>The Arch London</td>\n",
       "      <td>257.0</td>\n",
       "      <td>1992</td>\n",
       "      <td>5.0</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>50 Great Cumberland Place</td>\n",
       "      <td>W1H 7FD</td>\n",
       "      <td>Restaurant,Free High Speed Internet (WiFi),Fit...</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1480.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>51.515962</td>\n",
       "      <td>-0.159802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>The Pelham – Starhotels Collezione</td>\n",
       "      <td>264.0</td>\n",
       "      <td>940</td>\n",
       "      <td>4.5</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>15 Cromwell Place</td>\n",
       "      <td>SW7 2LA</td>\n",
       "      <td>Restaurant,Room Service,Bar/Lounge,Fitness Cen...</td>\n",
       "      <td>191.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>534.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>214.0</td>\n",
       "      <td>51.494367</td>\n",
       "      <td>-0.175074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Premier Inn London Kings Cross Hotel</td>\n",
       "      <td>160.0</td>\n",
       "      <td>4899</td>\n",
       "      <td>4.5</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>26-30 York Way</td>\n",
       "      <td>N1 9AA</td>\n",
       "      <td>Restaurant,Bar/Lounge,Free Internet,Air Condit...</td>\n",
       "      <td>181.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>2376.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1506.0</td>\n",
       "      <td>51.531269</td>\n",
       "      <td>-0.122325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Mondrian London at Sea Containers</td>\n",
       "      <td>286.0</td>\n",
       "      <td>4289</td>\n",
       "      <td>4.5</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>20 Upper Ground</td>\n",
       "      <td>SE1 9PD</td>\n",
       "      <td>Room Service,Bar/Lounge,Free High Speed Intern...</td>\n",
       "      <td>92.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>199.0</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>721.0</td>\n",
       "      <td>51.508412</td>\n",
       "      <td>-0.106932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>The Royal Horseguards</td>\n",
       "      <td>205.0</td>\n",
       "      <td>5188</td>\n",
       "      <td>4.5</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>2 Whitehall Court</td>\n",
       "      <td>SW1A 2EJ</td>\n",
       "      <td>Room Service,Restaurant,Fitness Center with Gy...</td>\n",
       "      <td>175.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>352.0</td>\n",
       "      <td>3210.0</td>\n",
       "      <td>147.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>1006.0</td>\n",
       "      <td>51.505814</td>\n",
       "      <td>-0.124186</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                                  Name  Price_Night_USD  \\\n",
       "0      0                       The Arch London            257.0   \n",
       "1      1    The Pelham – Starhotels Collezione            264.0   \n",
       "2      2  Premier Inn London Kings Cross Hotel            160.0   \n",
       "3      3     Mondrian London at Sea Containers            286.0   \n",
       "4      4                 The Royal Horseguards            205.0   \n",
       "\n",
       "   Total_Reviews  Stars_Rating  \\\n",
       "0           1992           5.0   \n",
       "1            940           4.5   \n",
       "2           4899           4.5   \n",
       "3           4289           4.5   \n",
       "4           5188           4.5   \n",
       "\n",
       "                                                Link  \\\n",
       "0  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "1  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "2  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "3  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "4  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "\n",
       "                    h_address  postcode  \\\n",
       "0  50 Great Cumberland Place    W1H 7FD   \n",
       "1          15 Cromwell Place    SW7 2LA   \n",
       "2             26-30 York Way     N1 9AA   \n",
       "3            20 Upper Ground    SE1 9PD   \n",
       "4          2 Whitehall Court   SW1A 2EJ   \n",
       "\n",
       "                                         h_amenities  h_ranking_users  h_star  \\\n",
       "0  Restaurant,Free High Speed Internet (WiFi),Fit...              9.0     5.0   \n",
       "1  Restaurant,Room Service,Bar/Lounge,Fitness Cen...            191.0     5.0   \n",
       "2  Restaurant,Bar/Lounge,Free Internet,Air Condit...            181.0     4.0   \n",
       "3  Room Service,Bar/Lounge,Free High Speed Intern...             92.0     5.0   \n",
       "4  Room Service,Restaurant,Fitness Center with Gy...            175.0     5.0   \n",
       "\n",
       "   r_average  r_excellent  r_poor  r_terrible  r_very_good   latitude  \\\n",
       "0       48.0       1480.0     7.0         5.0        215.0  51.515962   \n",
       "1       68.0        534.0    22.0         5.0        214.0  51.494367   \n",
       "2      350.0       2376.0   130.0        76.0       1506.0  51.531269   \n",
       "3      199.0       2900.0    52.0        36.0        721.0  51.508412   \n",
       "4      352.0       3210.0   147.0        66.0       1006.0  51.505814   \n",
       "\n",
       "   longitude  \n",
       "0  -0.159802  \n",
       "1  -0.175074  \n",
       "2  -0.122325  \n",
       "3  -0.106932  \n",
       "4  -0.124186  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merge of the two tables by the hotel link\n",
    "hotels = pd.merge(hotels_list,hotels_details, left_on='Link', right_on='h_link').reset_index()\n",
    "hotels.drop(labels='h_link',axis=1, inplace=True)\n",
    "hotels.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "hotels.to_csv('./Data/Hotels/hotels_london.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GIS Enriching of datasets\n",
    "\n",
    "We want to add the following around each of the hotels:\n",
    "\n",
    "- Purchasing Power per Capita\n",
    "- Average of household size\n",
    "- Total household\n",
    "- Ratio of each age group (there are 4 of them)\n",
    "- Traveltime to the main city centre, finantial centre and Canary Wharf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1063, 18)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hotels.shape #to check number of hotels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create Shapefile**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Result '.\\\\Data\\\\Shapefiles\\\\hotels.shp'>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#we create the shapefile\n",
    "arcpy.env.workspace = './Data/Shapefiles'\n",
    "arcpy.management.XYTableToPoint('./Data/Hotels/hotels_london.csv','./Data/Shapefiles/hotels.shp',\n",
    "                                \"longitude\", \"latitude\", \"\",\n",
    "                                arcpy.SpatialReference(4326, 115700))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Publish to GIS for enriching"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='http://JLLesp.maps.arcgis.com/home/item.html?id=685f91645ede4949a9aaf503b059a6a3' target='_blank'>\n",
       "                        <img src='http://static.arcgis.com/images/desktopapp.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='http://JLLesp.maps.arcgis.com/home/item.html?id=685f91645ede4949a9aaf503b059a6a3' target='_blank'><b>hotels_london_tripadvisor</b>\n",
       "                        </a>\n",
       "                        <br/><img src='http://JLLesp.maps.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by nacho.moreno_JLLesp\n",
       "                        <br/>Last Modified: September 08, 2018\n",
       "                        <br/>0 comments, 0 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"hotels_london_tripadvisor\" type:Feature Layer Collection owner:nacho.moreno_JLLesp>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Publish to GIS\n",
    "hotels_csv = './Data/Hotels/hotels_london.csv'\n",
    "item_prop = {'title':'hotels_london_tripadvisor'} #name of the layer in AGOL\n",
    "#Try to add the csv to agol, if item exists will delete and add again\n",
    "csv_to_publish = gis.content.add(item_properties=item_prop, data=hotels_csv)\n",
    "hotels_shp = csv_to_publish.publish()\n",
    "hotels_shp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Enrich dataset**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "uk = Country.get('GB')\n",
    "uk.properties.datasets\n",
    "\n",
    "datasets_esri = uk.data_collections\n",
    "\n",
    "#we save in a CSV the data name sos we can gather the one sof interest\n",
    "datasets_esri.to_csv('./Data/Shapefiles/datasets_esri.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='http://JLLesp.maps.arcgis.com/home/item.html?id=685f91645ede4949a9aaf503b059a6a3' target='_blank'>\n",
       "                        <img src='http://static.arcgis.com/images/desktopapp.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='http://JLLesp.maps.arcgis.com/home/item.html?id=685f91645ede4949a9aaf503b059a6a3' target='_blank'><b>hotels_london_tripadvisor</b>\n",
       "                        </a>\n",
       "                        <br/><img src='http://JLLesp.maps.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by nacho.moreno_JLLesp\n",
       "                        <br/>Last Modified: September 08, 2018\n",
       "                        <br/>0 comments, 0 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"hotels_london_tripadvisor\" type:Feature Layer Collection owner:nacho.moreno_JLLesp>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#We get the dataset in case has already been published\n",
    "search_results = gis.content.search('title:hotels_london_tripadvisor', 'Feature Layer')\n",
    "hotels_shp = search_results[0]\n",
    "hotels_shp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Enrich the dataset\n",
    "data_collections = ['KeyFacts']\n",
    "\n",
    "analysis_variables= ['KeyFacts.PAGE01_CY', #2017 Total Population Age 0-14\n",
    "                     'KeyFacts.PAGE02_CY', #15-29\n",
    "                     'KeyFacts.PAGE03_CY', #30-44\n",
    "                     'KeyFacts.PAGE04_CY', #45-59\n",
    "                     'KeyFacts.PAGE05CY', # >60\n",
    "                     'KeyFacts.TOTPOP_CY', #Total populations\n",
    "                     'KeyFacts.TOTHH_CY', # Total Households\n",
    "                     'KeyFacts.HTYP01A_CY', #households with Single Person\n",
    "                     'KeyFacts.PPPC_CY', #Purchasing Power per Capita\n",
    "                     'KeyFacts.AVGHHSZ_CY', #Average Household Size\n",
    "                    ]\n",
    "\n",
    "#hotels_enriched = enrich_data.enrich_layer(hotels_shp, buffer_type='StraightLine', distance=0.5 ,units='Kilometers', \n",
    "                                         data_collections= data_collections, analysis_variables = analysis_variables)\n",
    "\n",
    "#How to transform a feature collection to a df, first we 'query' teh feature collection adn a \n",
    "#Featureset is returned, then we use the df method to transform to a pd dataframe \n",
    "\n",
    "#Documentation here https://esri.github.io/arcgis-python-api/apidoc/html/arcgis.features.toc.html#featurecollection and\n",
    "# here: https://esri.github.io/arcgis-python-api/apidoc/html/arcgis.features.toc.html#featureset\n",
    "hotels_enriched_df = hotels_enriched.query().df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hh_size</th>\n",
       "      <th>single_hhs</th>\n",
       "      <th>Link</th>\n",
       "      <th>Name</th>\n",
       "      <th>p_b_14</th>\n",
       "      <th>p15_29</th>\n",
       "      <th>p30_44</th>\n",
       "      <th>p45_59</th>\n",
       "      <th>ppc</th>\n",
       "      <th>Price_Night_USD</th>\n",
       "      <th>Stars_Rating</th>\n",
       "      <th>tot_hhs</th>\n",
       "      <th>tot_pop</th>\n",
       "      <th>Total_Reviews</th>\n",
       "      <th>bufferRadii</th>\n",
       "      <th>bufferUnitsAlias</th>\n",
       "      <th>h_address</th>\n",
       "      <th>h_amenities</th>\n",
       "      <th>h_ranking_users</th>\n",
       "      <th>h_star</th>\n",
       "      <th>index_</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>postcode</th>\n",
       "      <th>r_average</th>\n",
       "      <th>r_excellent</th>\n",
       "      <th>r_poor</th>\n",
       "      <th>r_terrible</th>\n",
       "      <th>r_very_good</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.8</td>\n",
       "      <td>2844</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>The Arch London</td>\n",
       "      <td>1313</td>\n",
       "      <td>2291</td>\n",
       "      <td>3237</td>\n",
       "      <td>1679</td>\n",
       "      <td>45036.36</td>\n",
       "      <td>257.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5598</td>\n",
       "      <td>10274</td>\n",
       "      <td>1992</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>50 Great Cumberland Place</td>\n",
       "      <td>Restaurant,Free High Speed Internet (WiFi),Fit...</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>51.515962</td>\n",
       "      <td>-0.159802</td>\n",
       "      <td>W1H 7FD</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1480.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>215.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.5</td>\n",
       "      <td>602</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>Ruskin Hotel</td>\n",
       "      <td>2775</td>\n",
       "      <td>2869</td>\n",
       "      <td>3168</td>\n",
       "      <td>1966</td>\n",
       "      <td>14963.16</td>\n",
       "      <td>56.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>3451</td>\n",
       "      <td>12243</td>\n",
       "      <td>626</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>386 High Street North</td>\n",
       "      <td>Free Parking,Bar/Lounge,Free High Speed Intern...</td>\n",
       "      <td>604.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>500</td>\n",
       "      <td>51.544138</td>\n",
       "      <td>0.049934</td>\n",
       "      <td>E12 6PH</td>\n",
       "      <td>60.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.9</td>\n",
       "      <td>1798</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>The Pelham – Starhotels Collezione</td>\n",
       "      <td>1205</td>\n",
       "      <td>1360</td>\n",
       "      <td>1957</td>\n",
       "      <td>1495</td>\n",
       "      <td>56240.80</td>\n",
       "      <td>264.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3874</td>\n",
       "      <td>7419</td>\n",
       "      <td>940</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>15 Cromwell Place</td>\n",
       "      <td>Restaurant,Room Service,Bar/Lounge,Fitness Cen...</td>\n",
       "      <td>191.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>51.494367</td>\n",
       "      <td>-0.175074</td>\n",
       "      <td>SW7 2LA</td>\n",
       "      <td>68.0</td>\n",
       "      <td>534.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>214.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.5</td>\n",
       "      <td>1772</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>Premier Inn London Kings Cross Hotel</td>\n",
       "      <td>1629</td>\n",
       "      <td>4421</td>\n",
       "      <td>2335</td>\n",
       "      <td>1489</td>\n",
       "      <td>23642.09</td>\n",
       "      <td>160.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4344</td>\n",
       "      <td>10974</td>\n",
       "      <td>4899</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>26-30 York Way</td>\n",
       "      <td>Restaurant,Bar/Lounge,Free Internet,Air Condit...</td>\n",
       "      <td>181.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>51.531269</td>\n",
       "      <td>-0.122325</td>\n",
       "      <td>N1 9AA</td>\n",
       "      <td>350.0</td>\n",
       "      <td>2376.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1506.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.9</td>\n",
       "      <td>1311</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>Mondrian London at Sea Containers</td>\n",
       "      <td>351</td>\n",
       "      <td>2164</td>\n",
       "      <td>1082</td>\n",
       "      <td>824</td>\n",
       "      <td>26184.80</td>\n",
       "      <td>286.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2618</td>\n",
       "      <td>5073</td>\n",
       "      <td>4289</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>20 Upper Ground</td>\n",
       "      <td>Room Service,Bar/Lounge,Free High Speed Intern...</td>\n",
       "      <td>92.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "      <td>51.508412</td>\n",
       "      <td>-0.106932</td>\n",
       "      <td>SE1 9PD</td>\n",
       "      <td>199.0</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>721.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hh_size  single_hhs                                               Link  \\\n",
       "0      1.8        2844  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "1      3.5         602  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "2      1.9        1798  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "3      2.5        1772  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "4      1.9        1311  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "\n",
       "                                   Name  p_b_14  p15_29  p30_44  p45_59  \\\n",
       "0                       The Arch London    1313    2291    3237    1679   \n",
       "1                          Ruskin Hotel    2775    2869    3168    1966   \n",
       "2    The Pelham – Starhotels Collezione    1205    1360    1957    1495   \n",
       "3  Premier Inn London Kings Cross Hotel    1629    4421    2335    1489   \n",
       "4     Mondrian London at Sea Containers     351    2164    1082     824   \n",
       "\n",
       "        ppc  Price_Night_USD  Stars_Rating  tot_hhs  tot_pop  Total_Reviews  \\\n",
       "0  45036.36            257.0           5.0     5598    10274           1992   \n",
       "1  14963.16             56.0           3.5     3451    12243            626   \n",
       "2  56240.80            264.0           4.5     3874     7419            940   \n",
       "3  23642.09            160.0           4.5     4344    10974           4899   \n",
       "4  26184.80            286.0           4.5     2618     5073           4289   \n",
       "\n",
       "   bufferRadii bufferUnitsAlias                   h_address  \\\n",
       "0          0.5       Kilometers  50 Great Cumberland Place    \n",
       "1          0.5       Kilometers      386 High Street North    \n",
       "2          0.5       Kilometers          15 Cromwell Place    \n",
       "3          0.5       Kilometers             26-30 York Way    \n",
       "4          0.5       Kilometers            20 Upper Ground    \n",
       "\n",
       "                                         h_amenities  h_ranking_users  h_star  \\\n",
       "0  Restaurant,Free High Speed Internet (WiFi),Fit...              9.0     5.0   \n",
       "1  Free Parking,Bar/Lounge,Free High Speed Intern...            604.0     3.0   \n",
       "2  Restaurant,Room Service,Bar/Lounge,Fitness Cen...            191.0     5.0   \n",
       "3  Restaurant,Bar/Lounge,Free Internet,Air Condit...            181.0     4.0   \n",
       "4  Room Service,Bar/Lounge,Free High Speed Intern...             92.0     5.0   \n",
       "\n",
       "   index_   latitude  longitude postcode  r_average  r_excellent  r_poor  \\\n",
       "0       0  51.515962  -0.159802  W1H 7FD       48.0       1480.0     7.0   \n",
       "1     500  51.544138   0.049934  E12 6PH       60.0        130.0    31.0   \n",
       "2       1  51.494367  -0.175074  SW7 2LA       68.0        534.0    22.0   \n",
       "3       2  51.531269  -0.122325   N1 9AA      350.0       2376.0   130.0   \n",
       "4       3  51.508412  -0.106932  SE1 9PD      199.0       2900.0    52.0   \n",
       "\n",
       "   r_terrible  r_very_good  \n",
       "0         5.0        215.0  \n",
       "1        33.0        136.0  \n",
       "2         5.0        214.0  \n",
       "3        76.0       1506.0  \n",
       "4        36.0        721.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#We export teh shapefile for further analysis\n",
    "\n",
    "hotels_enriched.query().save(save_location= './Data/Shapefiles',\n",
    "     out_name='hotels_enriched', encoding=None)\n",
    "\n",
    "#Lets change the name of the columns and clean the data\n",
    "hotels_enriched_df = hotels_enriched_df.drop(['HasData', 'ID', 'OBJECTID', 'ENRICH_FID', 'aggregationMethod', 'areaType', 'bufferUnits', 'sourceCountry', 'SHAPE' ], axis=1)\n",
    "hotels_enriched_df = hotels_enriched_df.rename(index=str, columns={\"PAGE01_CY\": \"p_b_14\", \n",
    "                                                               \"PAGE02_CY\": \"p15_29\",\n",
    "                                                               \"PAGE03_CY\": \"p30_44\",\n",
    "                                                               \"PAGE04_CY\": \"p45_59\",\n",
    "                                                               \"PAGE05_CY\": \"p_m_60\",\n",
    "                                                               \"TOTPOP_CY\": \"tot_pop\",\n",
    "                                                               \"TOTHH_CY\": \"tot_hhs\",\n",
    "                                                               \"HTYP01A_CY\": \"single_hhs\",\n",
    "                                                               \"PPPC_CY\": \"ppc\",\n",
    "                                                               \"AVGHHSZ_CY\": \"hh_size\"})\n",
    "\n",
    "#Just to check we have the right data and save it ot a new dataframe and CSV so we dont need to run everytime\n",
    "hotels_enriched_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Lets save the data in a CSV\n",
    "hotels_enriched_df.to_csv('./Data/Hotels/hotels_enriched.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Unnamed: 0</th>\n",
       "      <th>hh_size</th>\n",
       "      <th>single_hhs</th>\n",
       "      <th>Link</th>\n",
       "      <th>Name</th>\n",
       "      <th>p_b_14</th>\n",
       "      <th>p15_29</th>\n",
       "      <th>p30_44</th>\n",
       "      <th>p45_59</th>\n",
       "      <th>ppc</th>\n",
       "      <th>Price_Night_USD</th>\n",
       "      <th>Stars_Rating</th>\n",
       "      <th>tot_hhs</th>\n",
       "      <th>tot_pop</th>\n",
       "      <th>Total_Reviews</th>\n",
       "      <th>bufferRadii</th>\n",
       "      <th>bufferUnitsAlias</th>\n",
       "      <th>h_address</th>\n",
       "      <th>h_amenities</th>\n",
       "      <th>h_ranking_users</th>\n",
       "      <th>h_star</th>\n",
       "      <th>index_</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>postcode</th>\n",
       "      <th>r_average</th>\n",
       "      <th>r_excellent</th>\n",
       "      <th>r_poor</th>\n",
       "      <th>r_terrible</th>\n",
       "      <th>r_very_good</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>2844</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>The Arch London</td>\n",
       "      <td>1313</td>\n",
       "      <td>2291</td>\n",
       "      <td>3237</td>\n",
       "      <td>1679</td>\n",
       "      <td>45036.36</td>\n",
       "      <td>257.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5598</td>\n",
       "      <td>10274</td>\n",
       "      <td>1992</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>50 Great Cumberland Place</td>\n",
       "      <td>Restaurant,Free High Speed Internet (WiFi),Fit...</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>51.515962</td>\n",
       "      <td>-0.159802</td>\n",
       "      <td>W1H 7FD</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1480.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>215.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>602</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>Ruskin Hotel</td>\n",
       "      <td>2775</td>\n",
       "      <td>2869</td>\n",
       "      <td>3168</td>\n",
       "      <td>1966</td>\n",
       "      <td>14963.16</td>\n",
       "      <td>56.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>3451</td>\n",
       "      <td>12243</td>\n",
       "      <td>626</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>386 High Street North</td>\n",
       "      <td>Free Parking,Bar/Lounge,Free High Speed Intern...</td>\n",
       "      <td>604.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>500</td>\n",
       "      <td>51.544138</td>\n",
       "      <td>0.049934</td>\n",
       "      <td>E12 6PH</td>\n",
       "      <td>60.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1798</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>The Pelham – Starhotels Collezione</td>\n",
       "      <td>1205</td>\n",
       "      <td>1360</td>\n",
       "      <td>1957</td>\n",
       "      <td>1495</td>\n",
       "      <td>56240.80</td>\n",
       "      <td>264.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3874</td>\n",
       "      <td>7419</td>\n",
       "      <td>940</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>15 Cromwell Place</td>\n",
       "      <td>Restaurant,Room Service,Bar/Lounge,Fitness Cen...</td>\n",
       "      <td>191.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>51.494367</td>\n",
       "      <td>-0.175074</td>\n",
       "      <td>SW7 2LA</td>\n",
       "      <td>68.0</td>\n",
       "      <td>534.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>214.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1772</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>Premier Inn London Kings Cross Hotel</td>\n",
       "      <td>1629</td>\n",
       "      <td>4421</td>\n",
       "      <td>2335</td>\n",
       "      <td>1489</td>\n",
       "      <td>23642.09</td>\n",
       "      <td>160.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4344</td>\n",
       "      <td>10974</td>\n",
       "      <td>4899</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>26-30 York Way</td>\n",
       "      <td>Restaurant,Bar/Lounge,Free Internet,Air Condit...</td>\n",
       "      <td>181.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>51.531269</td>\n",
       "      <td>-0.122325</td>\n",
       "      <td>N1 9AA</td>\n",
       "      <td>350.0</td>\n",
       "      <td>2376.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1506.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1311</td>\n",
       "      <td>https://www.tripadvisor.com/Hotel_Review-g1863...</td>\n",
       "      <td>Mondrian London at Sea Containers</td>\n",
       "      <td>351</td>\n",
       "      <td>2164</td>\n",
       "      <td>1082</td>\n",
       "      <td>824</td>\n",
       "      <td>26184.80</td>\n",
       "      <td>286.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2618</td>\n",
       "      <td>5073</td>\n",
       "      <td>4289</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Kilometers</td>\n",
       "      <td>20 Upper Ground</td>\n",
       "      <td>Room Service,Bar/Lounge,Free High Speed Intern...</td>\n",
       "      <td>92.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "      <td>51.508412</td>\n",
       "      <td>-0.106932</td>\n",
       "      <td>SE1 9PD</td>\n",
       "      <td>199.0</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>721.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  hh_size  single_hhs  \\\n",
       "0           0      1.8        2844   \n",
       "1           1      3.5         602   \n",
       "2           2      1.9        1798   \n",
       "3           3      2.5        1772   \n",
       "4           4      1.9        1311   \n",
       "\n",
       "                                                Link  \\\n",
       "0  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "1  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "2  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "3  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "4  https://www.tripadvisor.com/Hotel_Review-g1863...   \n",
       "\n",
       "                                   Name  p_b_14  p15_29  p30_44  p45_59  \\\n",
       "0                       The Arch London    1313    2291    3237    1679   \n",
       "1                          Ruskin Hotel    2775    2869    3168    1966   \n",
       "2    The Pelham – Starhotels Collezione    1205    1360    1957    1495   \n",
       "3  Premier Inn London Kings Cross Hotel    1629    4421    2335    1489   \n",
       "4     Mondrian London at Sea Containers     351    2164    1082     824   \n",
       "\n",
       "        ppc  Price_Night_USD  Stars_Rating  tot_hhs  tot_pop  Total_Reviews  \\\n",
       "0  45036.36            257.0           5.0     5598    10274           1992   \n",
       "1  14963.16             56.0           3.5     3451    12243            626   \n",
       "2  56240.80            264.0           4.5     3874     7419            940   \n",
       "3  23642.09            160.0           4.5     4344    10974           4899   \n",
       "4  26184.80            286.0           4.5     2618     5073           4289   \n",
       "\n",
       "   bufferRadii bufferUnitsAlias                   h_address  \\\n",
       "0          0.5       Kilometers  50 Great Cumberland Place    \n",
       "1          0.5       Kilometers      386 High Street North    \n",
       "2          0.5       Kilometers          15 Cromwell Place    \n",
       "3          0.5       Kilometers             26-30 York Way    \n",
       "4          0.5       Kilometers            20 Upper Ground    \n",
       "\n",
       "                                         h_amenities  h_ranking_users  h_star  \\\n",
       "0  Restaurant,Free High Speed Internet (WiFi),Fit...              9.0     5.0   \n",
       "1  Free Parking,Bar/Lounge,Free High Speed Intern...            604.0     3.0   \n",
       "2  Restaurant,Room Service,Bar/Lounge,Fitness Cen...            191.0     5.0   \n",
       "3  Restaurant,Bar/Lounge,Free Internet,Air Condit...            181.0     4.0   \n",
       "4  Room Service,Bar/Lounge,Free High Speed Intern...             92.0     5.0   \n",
       "\n",
       "   index_   latitude  longitude postcode  r_average  r_excellent  r_poor  \\\n",
       "0       0  51.515962  -0.159802  W1H 7FD       48.0       1480.0     7.0   \n",
       "1     500  51.544138   0.049934  E12 6PH       60.0        130.0    31.0   \n",
       "2       1  51.494367  -0.175074  SW7 2LA       68.0        534.0    22.0   \n",
       "3       2  51.531269  -0.122325   N1 9AA      350.0       2376.0   130.0   \n",
       "4       3  51.508412  -0.106932  SE1 9PD      199.0       2900.0    52.0   \n",
       "\n",
       "   r_terrible  r_very_good  \n",
       "0         5.0        215.0  \n",
       "1        33.0        136.0  \n",
       "2         5.0        214.0  \n",
       "3        76.0       1506.0  \n",
       "4        36.0        721.0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Load data and inspect\n",
    "hotels_enriched = pd.read_csv('./Data/Hotels/hotels_enriched.csv')\n",
    "hotels_enriched.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Airbnb Data\n",
    "\n",
    "We have prefiltered the data to take into account, only private rooms, with a review in the last 9 months, more than 10 reviews and with availability."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>id</th>\n",
       "      <th>listing_url</th>\n",
       "      <th>host_id</th>\n",
       "      <th>host_url</th>\n",
       "      <th>host_name</th>\n",
       "      <th>host_since</th>\n",
       "      <th>host_location</th>\n",
       "      <th>host_about</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>is_location_exact</th>\n",
       "      <th>property_type</th>\n",
       "      <th>room_type</th>\n",
       "      <th>accommodates</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>beds</th>\n",
       "      <th>bed_type</th>\n",
       "      <th>price</th>\n",
       "      <th>guests_included</th>\n",
       "      <th>calendar_updated</th>\n",
       "      <th>has_availability</th>\n",
       "      <th>availability_30</th>\n",
       "      <th>availability_60</th>\n",
       "      <th>availability_90</th>\n",
       "      <th>availability_365</th>\n",
       "      <th>calendar_last_scraped</th>\n",
       "      <th>number_of_reviews</th>\n",
       "      <th>last_review</th>\n",
       "      <th>review_scores_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9554</td>\n",
       "      <td>https://www.airbnb.com/rooms/9554</td>\n",
       "      <td>31655</td>\n",
       "      <td>https://www.airbnb.com/users/show/31655</td>\n",
       "      <td>Guy</td>\n",
       "      <td>40039</td>\n",
       "      <td>London, England, United Kingdom</td>\n",
       "      <td>Please contact me before booking! Please see d...</td>\n",
       "      <td>N8 0EY</td>\n",
       "      <td>51.587767</td>\n",
       "      <td>-0.105666</td>\n",
       "      <td>f</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Private room</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Real Bed</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>2 weeks ago</td>\n",
       "      <td>t</td>\n",
       "      <td>13</td>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>262</td>\n",
       "      <td>43321</td>\n",
       "      <td>131</td>\n",
       "      <td>43315.0</td>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13913</td>\n",
       "      <td>https://www.airbnb.com/rooms/13913</td>\n",
       "      <td>54730</td>\n",
       "      <td>https://www.airbnb.com/users/show/54730</td>\n",
       "      <td>Alina</td>\n",
       "      <td>40133</td>\n",
       "      <td>London, England, United Kingdom</td>\n",
       "      <td>I am a Multi-Media Visual Artist and Creative ...</td>\n",
       "      <td>N4 3</td>\n",
       "      <td>51.568017</td>\n",
       "      <td>-0.111208</td>\n",
       "      <td>t</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Private room</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Real Bed</td>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "      <td>a week ago</td>\n",
       "      <td>t</td>\n",
       "      <td>29</td>\n",
       "      <td>59</td>\n",
       "      <td>89</td>\n",
       "      <td>364</td>\n",
       "      <td>43321</td>\n",
       "      <td>14</td>\n",
       "      <td>43268.0</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17402</td>\n",
       "      <td>https://www.airbnb.com/rooms/17402</td>\n",
       "      <td>67564</td>\n",
       "      <td>https://www.airbnb.com/users/show/67564</td>\n",
       "      <td>Liz</td>\n",
       "      <td>40182</td>\n",
       "      <td>London, England, United Kingdom</td>\n",
       "      <td>Hello!\\r\\n\\r\\nWe are Liz and Jack. We look for...</td>\n",
       "      <td>W1T4BP</td>\n",
       "      <td>51.520982</td>\n",
       "      <td>-0.140024</td>\n",
       "      <td>t</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>6</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Real Bed</td>\n",
       "      <td>300</td>\n",
       "      <td>4</td>\n",
       "      <td>today</td>\n",
       "      <td>t</td>\n",
       "      <td>13</td>\n",
       "      <td>25</td>\n",
       "      <td>49</td>\n",
       "      <td>135</td>\n",
       "      <td>43321</td>\n",
       "      <td>31</td>\n",
       "      <td>43313.0</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25123</td>\n",
       "      <td>https://www.airbnb.com/rooms/25123</td>\n",
       "      <td>103583</td>\n",
       "      <td>https://www.airbnb.com/users/show/103583</td>\n",
       "      <td>Grace</td>\n",
       "      <td>40273</td>\n",
       "      <td>London, England, United Kingdom</td>\n",
       "      <td>Easy going, friendly\\r\\n\\r\\nAttentive to detai...</td>\n",
       "      <td>NW11 9</td>\n",
       "      <td>51.572243</td>\n",
       "      <td>-0.209060</td>\n",
       "      <td>t</td>\n",
       "      <td>House</td>\n",
       "      <td>Private room</td>\n",
       "      <td>2</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Real Bed</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>5 weeks ago</td>\n",
       "      <td>t</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>285</td>\n",
       "      <td>43321</td>\n",
       "      <td>114</td>\n",
       "      <td>43221.0</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26223</td>\n",
       "      <td>https://www.airbnb.com/rooms/26223</td>\n",
       "      <td>110865</td>\n",
       "      <td>https://www.airbnb.com/users/show/110865</td>\n",
       "      <td>Paulo</td>\n",
       "      <td>40287</td>\n",
       "      <td>London, England, United Kingdom</td>\n",
       "      <td>Originally from Southern Europe, and now livin...</td>\n",
       "      <td>N1 2</td>\n",
       "      <td>51.541680</td>\n",
       "      <td>-0.102065</td>\n",
       "      <td>t</td>\n",
       "      <td>Apartment</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Real Bed</td>\n",
       "      <td>150</td>\n",
       "      <td>4</td>\n",
       "      <td>today</td>\n",
       "      <td>t</td>\n",
       "      <td>29</td>\n",
       "      <td>45</td>\n",
       "      <td>75</td>\n",
       "      <td>350</td>\n",
       "      <td>43321</td>\n",
       "      <td>54</td>\n",
       "      <td>43308.0</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                         listing_url  host_id  \\\n",
       "0   9554   https://www.airbnb.com/rooms/9554    31655   \n",
       "1  13913  https://www.airbnb.com/rooms/13913    54730   \n",
       "2  17402  https://www.airbnb.com/rooms/17402    67564   \n",
       "3  25123  https://www.airbnb.com/rooms/25123   103583   \n",
       "4  26223  https://www.airbnb.com/rooms/26223   110865   \n",
       "\n",
       "                                   host_url host_name  host_since  \\\n",
       "0   https://www.airbnb.com/users/show/31655       Guy       40039   \n",
       "1   https://www.airbnb.com/users/show/54730     Alina       40133   \n",
       "2   https://www.airbnb.com/users/show/67564       Liz       40182   \n",
       "3  https://www.airbnb.com/users/show/103583     Grace       40273   \n",
       "4  https://www.airbnb.com/users/show/110865     Paulo       40287   \n",
       "\n",
       "                     host_location  \\\n",
       "0  London, England, United Kingdom   \n",
       "1  London, England, United Kingdom   \n",
       "2  London, England, United Kingdom   \n",
       "3  London, England, United Kingdom   \n",
       "4  London, England, United Kingdom   \n",
       "\n",
       "                                          host_about zipcode   latitude  \\\n",
       "0  Please contact me before booking! Please see d...  N8 0EY  51.587767   \n",
       "1  I am a Multi-Media Visual Artist and Creative ...    N4 3  51.568017   \n",
       "2  Hello!\\r\\n\\r\\nWe are Liz and Jack. We look for...  W1T4BP  51.520982   \n",
       "3  Easy going, friendly\\r\\n\\r\\nAttentive to detai...  NW11 9  51.572243   \n",
       "4  Originally from Southern Europe, and now livin...    N1 2  51.541680   \n",
       "\n",
       "   longitude is_location_exact property_type        room_type  accommodates  \\\n",
       "0  -0.105666                 f     Apartment     Private room             2   \n",
       "1  -0.111208                 t     Apartment     Private room             2   \n",
       "2  -0.140024                 t     Apartment  Entire home/apt             6   \n",
       "3  -0.209060                 t         House     Private room             2   \n",
       "4  -0.102065                 t     Apartment  Entire home/apt             4   \n",
       "\n",
       "   bathrooms  bedrooms  beds  bed_type  price  guests_included  \\\n",
       "0        NaN       1.0   1.0  Real Bed     35                1   \n",
       "1        1.0       1.0   1.0  Real Bed     45                1   \n",
       "2        2.0       3.0   3.0  Real Bed    300                4   \n",
       "3        1.5       1.0   1.0  Real Bed     29                1   \n",
       "4        1.0       1.0   3.0  Real Bed    150                4   \n",
       "\n",
       "  calendar_updated has_availability  availability_30  availability_60  \\\n",
       "0      2 weeks ago                t               13               21   \n",
       "1       a week ago                t               29               59   \n",
       "2            today                t               13               25   \n",
       "3      5 weeks ago                t                4                4   \n",
       "4            today                t               29               45   \n",
       "\n",
       "   availability_90  availability_365  calendar_last_scraped  \\\n",
       "0               21               262                  43321   \n",
       "1               89               364                  43321   \n",
       "2               49               135                  43321   \n",
       "3               10               285                  43321   \n",
       "4               75               350                  43321   \n",
       "\n",
       "   number_of_reviews  last_review  review_scores_rating  \n",
       "0                131      43315.0                  97.0  \n",
       "1                 14      43268.0                  95.0  \n",
       "2                 31      43313.0                  93.0  \n",
       "3                114      43221.0                  95.0  \n",
       "4                 54      43308.0                  84.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb = pd.read_csv('./Data/Airbnb/airbnb_listings_for_project.csv')\n",
    "airbnb.head()                    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create shapefile**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Result '.\\\\Data\\\\Shapefiles\\\\airbnb.shp'>"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#we create the shapefile\n",
    "arcpy.env.workspace = './Data/Shapefiles'\n",
    "arcpy.management.XYTableToPoint('./Data/Airbnb/airbnb_listings_for_project.csv','./Data/Shapefiles/airbnb.shp',\n",
    "                                \"longitude\", \"latitude\", \"\",\n",
    "                                arcpy.SpatialReference(4326, 115700))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Restaurants Dataset\n",
    "\n",
    "Steps to follow:\n",
    "\n",
    "- Read both datasets and merge by link\n",
    "- Create Shapefile (already have coordinates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Restaurant Name</th>\n",
       "      <th>Price Range</th>\n",
       "      <th>Cuisine type</th>\n",
       "      <th>Number of reviews</th>\n",
       "      <th>Reviews rating (out of 5)</th>\n",
       "      <th>Ranking</th>\n",
       "      <th>Restaurant Link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kama Sushi @ Sticky Mango</td>\n",
       "      <td>$$$$</td>\n",
       "      <td>Japanese, Sushi, Asian, Vegetarian Friendly, G...</td>\n",
       "      <td>88</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Amrutha Lounge</td>\n",
       "      <td>$$ - $$$</td>\n",
       "      <td>Indian, Asian, Thai, Healthy, Vegetarian Frien...</td>\n",
       "      <td>160</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Humble Grape</td>\n",
       "      <td>$$ - $$$</td>\n",
       "      <td>European, Wine Bar, Vegan Options, Vegetarian ...</td>\n",
       "      <td>169</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Lounge Cafe</td>\n",
       "      <td>$</td>\n",
       "      <td>British, Vegetarian Friendly, Vegan Options, G...</td>\n",
       "      <td>131</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pizza Union Dalston</td>\n",
       "      <td>$</td>\n",
       "      <td>Italian, Pizza, Fast Food, Vegetarian Friendly...</td>\n",
       "      <td>368</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Restaurant Name Price Range  \\\n",
       "0  Kama Sushi @ Sticky Mango        $$$$   \n",
       "1             Amrutha Lounge    $$ - $$$   \n",
       "2               Humble Grape    $$ - $$$   \n",
       "3            The Lounge Cafe           $   \n",
       "4        Pizza Union Dalston           $   \n",
       "\n",
       "                                        Cuisine type  Number of reviews  \\\n",
       "0  Japanese, Sushi, Asian, Vegetarian Friendly, G...                 88   \n",
       "1  Indian, Asian, Thai, Healthy, Vegetarian Frien...                160   \n",
       "2  European, Wine Bar, Vegan Options, Vegetarian ...                169   \n",
       "3  British, Vegetarian Friendly, Vegan Options, G...                131   \n",
       "4  Italian, Pizza, Fast Food, Vegetarian Friendly...                368   \n",
       "\n",
       "   Reviews rating (out of 5)  Ranking  \\\n",
       "0                        5.0      1.0   \n",
       "1                        5.0      2.0   \n",
       "2                        5.0      3.0   \n",
       "3                        5.0      4.0   \n",
       "4                        5.0      5.0   \n",
       "\n",
       "                                     Restaurant Link  \n",
       "0  https://www.tripadvisor.com/Restaurant_Review-...  \n",
       "1  https://www.tripadvisor.com/Restaurant_Review-...  \n",
       "2  https://www.tripadvisor.com/Restaurant_Review-...  \n",
       "3  https://www.tripadvisor.com/Restaurant_Review-...  \n",
       "4  https://www.tripadvisor.com/Restaurant_Review-...  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurant_list = pd.read_csv('./Data/Restaurants/rest_list_london.csv')\n",
    "restaurant_list.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>r_address</th>\n",
       "      <th>r_data</th>\n",
       "      <th>r_lat</th>\n",
       "      <th>r_link</th>\n",
       "      <th>r_long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91 Stamford St , London SE1 9NR,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.506176</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>-0.109894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>38C Kensington Church Street , London W8 4BX,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.504189</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>-0.192753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>34-38 Southampton Street , London WC2E 7HG,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.510845</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>-0.121941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1A Launceston Place , London W8 5RL,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.499207</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>-0.185377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28 Church Row , London NW3 6UP,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.555576</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>-0.178780</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        r_address  r_data      r_lat  \\\n",
       "0               91 Stamford St , London SE1 9NR,      NaN  51.506176   \n",
       "1  38C Kensington Church Street , London W8 4BX,      NaN  51.504189   \n",
       "2    34-38 Southampton Street , London WC2E 7HG,      NaN  51.510845   \n",
       "3           1A Launceston Place , London W8 5RL,      NaN  51.499207   \n",
       "4                28 Church Row , London NW3 6UP,      NaN  51.555576   \n",
       "\n",
       "                                              r_link    r_long  \n",
       "0  https://www.tripadvisor.com/Restaurant_Review-... -0.109894  \n",
       "1  https://www.tripadvisor.com/Restaurant_Review-... -0.192753  \n",
       "2  https://www.tripadvisor.com/Restaurant_Review-... -0.121941  \n",
       "3  https://www.tripadvisor.com/Restaurant_Review-... -0.185377  \n",
       "4  https://www.tripadvisor.com/Restaurant_Review-... -0.178780  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurant_details = pd.read_csv('./Data/Restaurants/rest_details_london.csv')\n",
    "restaurant_details.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Restaurant Name</th>\n",
       "      <th>Price Range</th>\n",
       "      <th>Cuisine type</th>\n",
       "      <th>Number of reviews</th>\n",
       "      <th>Reviews rating (out of 5)</th>\n",
       "      <th>Ranking</th>\n",
       "      <th>Restaurant Link</th>\n",
       "      <th>r_address</th>\n",
       "      <th>r_data</th>\n",
       "      <th>r_lat</th>\n",
       "      <th>r_long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kama Sushi @ Sticky Mango</td>\n",
       "      <td>$$$$</td>\n",
       "      <td>Japanese, Sushi, Asian, Vegetarian Friendly, G...</td>\n",
       "      <td>88</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>91 Stamford St , London SE1 9NR,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.506176</td>\n",
       "      <td>-0.109894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Amrutha Lounge</td>\n",
       "      <td>$$ - $$$</td>\n",
       "      <td>Indian, Asian, Thai, Healthy, Vegetarian Frien...</td>\n",
       "      <td>160</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>326 Garratt Lane , London SW18 4EJ,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.443775</td>\n",
       "      <td>-0.189937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Humble Grape</td>\n",
       "      <td>$$ - $$$</td>\n",
       "      <td>European, Wine Bar, Vegan Options, Vegetarian ...</td>\n",
       "      <td>169</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>11-13 Theberton Street , London N1 0QY,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.537575</td>\n",
       "      <td>-0.103633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Lounge Cafe</td>\n",
       "      <td>$</td>\n",
       "      <td>British, Vegetarian Friendly, Vegan Options, G...</td>\n",
       "      <td>131</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>Welford Centre 113 Chalkhill Road , London HA9...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.564049</td>\n",
       "      <td>-0.276614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pizza Union Dalston</td>\n",
       "      <td>$</td>\n",
       "      <td>Italian, Pizza, Fast Food, Vegetarian Friendly...</td>\n",
       "      <td>368</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>https://www.tripadvisor.com/Restaurant_Review-...</td>\n",
       "      <td>14 Kingsland Road , London E13 9PA,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.546890</td>\n",
       "      <td>-0.075558</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Restaurant Name Price Range  \\\n",
       "0  Kama Sushi @ Sticky Mango        $$$$   \n",
       "1             Amrutha Lounge    $$ - $$$   \n",
       "2               Humble Grape    $$ - $$$   \n",
       "3            The Lounge Cafe           $   \n",
       "4        Pizza Union Dalston           $   \n",
       "\n",
       "                                        Cuisine type  Number of reviews  \\\n",
       "0  Japanese, Sushi, Asian, Vegetarian Friendly, G...                 88   \n",
       "1  Indian, Asian, Thai, Healthy, Vegetarian Frien...                160   \n",
       "2  European, Wine Bar, Vegan Options, Vegetarian ...                169   \n",
       "3  British, Vegetarian Friendly, Vegan Options, G...                131   \n",
       "4  Italian, Pizza, Fast Food, Vegetarian Friendly...                368   \n",
       "\n",
       "   Reviews rating (out of 5)  Ranking  \\\n",
       "0                        5.0      1.0   \n",
       "1                        5.0      2.0   \n",
       "2                        5.0      3.0   \n",
       "3                        5.0      4.0   \n",
       "4                        5.0      5.0   \n",
       "\n",
       "                                     Restaurant Link  \\\n",
       "0  https://www.tripadvisor.com/Restaurant_Review-...   \n",
       "1  https://www.tripadvisor.com/Restaurant_Review-...   \n",
       "2  https://www.tripadvisor.com/Restaurant_Review-...   \n",
       "3  https://www.tripadvisor.com/Restaurant_Review-...   \n",
       "4  https://www.tripadvisor.com/Restaurant_Review-...   \n",
       "\n",
       "                                           r_address  r_data      r_lat  \\\n",
       "0                  91 Stamford St , London SE1 9NR,      NaN  51.506176   \n",
       "1               326 Garratt Lane , London SW18 4EJ,      NaN  51.443775   \n",
       "2           11-13 Theberton Street , London N1 0QY,      NaN  51.537575   \n",
       "3  Welford Centre 113 Chalkhill Road , London HA9...     NaN  51.564049   \n",
       "4               14 Kingsland Road , London E13 9PA,      NaN  51.546890   \n",
       "\n",
       "     r_long  \n",
       "0 -0.109894  \n",
       "1 -0.189937  \n",
       "2 -0.103633  \n",
       "3 -0.276614  \n",
       "4 -0.075558  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Merging both datasets and saving the results to csv too\n",
    "restaurants = pd.merge(restaurant_list,restaurant_details, left_on='Restaurant Link', right_on='r_link')\n",
    "restaurants.drop(labels='r_link',axis=1, inplace=True)\n",
    "restaurants.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurants.to_csv('./Data/Restaurants/restaurants.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Restaurant Name</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Price Range</th>\n",
       "      <td>5283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cuisine type</th>\n",
       "      <td>3661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Number of reviews</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Reviews rating (out of 5)</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ranking</th>\n",
       "      <td>1771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Restaurant Link</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r_address</th>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r_data</th>\n",
       "      <td>18727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r_lat</th>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r_long</th>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               0\n",
       "Restaurant Name                0\n",
       "Price Range                 5283\n",
       "Cuisine type                3661\n",
       "Number of reviews              0\n",
       "Reviews rating (out of 5)      0\n",
       "Ranking                     1771\n",
       "Restaurant Link                0\n",
       "r_address                    196\n",
       "r_data                     18727\n",
       "r_lat                        117\n",
       "r_long                       117"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#cheking null values\n",
    "pd.DataFrame(restaurants.isnull().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create shapefile**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Result '.\\\\Data\\\\Shapefiles\\\\restaurants.shp'>"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#we create the shapefile\n",
    "arcpy.env.workspace = './Data/Shapefiles'\n",
    "arcpy.management.XYTableToPoint('./Data/Restaurants/restaurants.csv','./Data/Shapefiles/restaurants.shp',\n",
    "                                \"r_long\", \"r_lat\", \"\",\n",
    "                                arcpy.SpatialReference(4326, 115700))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}