<jupyter_start><jupyter_text>![BTS](https://4d17d13o8yuu3wluzr4c2feo-wpengine.netdna-ssl.com/wp-content/uploads/2019/02/btslogo.png) Master in Big Data Solutions Final Project - Smart Tour <NAME>, <NAME> & <NAME>Data preparation  Data Sources 1. Number of tourists in Catalunya per month from 2015 to 2019:https://datos.gob.es/es/catalogo/ea0010587-numero-de-turistas-segun-comunidad-autonoma-de-destino-principal-mensual-comunidades-autonomas-movimientos-turisticos-en-fronteras-identificador-api-1082312. Barcelona list of tourist attractions and number of annual visitors from 1994 to 2019: Observatorio de Turismo de Barcelona3. Barcelona list of gastronomic equipment: https://opendata-ajuntament.barcelona.cat/data/es/dataset/equipament-restaurants4. Barcelona tourist attractions geolocation: https://opendata-ajuntament.barcelona.cat/data/es/dataset/punts-informacio-turistica5. Lonely Planet list of Barcelona tourist attractions with features: Web Scraping from Web Sitehttps://www.lonelyplanet.com/spain/barcelona/attractions?page=16. TripAdvisor Restaurants Info for 31 Euro-Cities: https://www.kaggle.com/damienbeneschi/krakow-ta-restaurans-data-raw7. Barcelona neighbourhoods and districs geodata: https://github.com/martgnz/bcn-geodata  Package import<jupyter_code>import pandas as pd
from re import search
from math import sqrt, radians
from sklearn.neighbors import DistanceMetric
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Loading datasets<jupyter_code>#od is OpenData, pits is Points of Interest
pits_od=pd.read_excel('pits_opendata.xls')

#ob is Observsatorium de Turismo de Barcelona, pits is Points of Interest
pits_ob=pd.read_excel('barcelona-cultura-y-ocio-1994-2019.xlsx')

#TA is TripAdvisor
TA_data=pd.read_csv('TA_restaurants_curated.csv')

#lp is Lonely Planet
pits_lp=pd.read_excel('pits_lp.xlsx', sheet_name='Curado')

restaurants_op=pd.read_csv('restaurants_opendata.csv')

#cat is Catalunya
cat_visitors=pd.read_excel('catalunya_visitors2015-2019.xlsx')

#POI_barcleona
poi_bcn=pd.read_excel('POI_barcelona.xlsx')<jupyter_output><empty_output><jupyter_text>Formatting, dropping columns, identifying missing values  Catalunya visitors dataset<jupyter_code>cat_visitors.head(2)
cat_visitors.shape
cat_visitors.dtypes
cat_visitors.columns
#Removing space from benining and end of columns names
cat_visitors.columns = cat_visitors.columns.str.lstrip()
cat_visitors.columns
#Dropping unnecessary columns
cat_visitors=cat_visitors.drop(['Mes2', 'Tasa de variación anual', 'Acumulado en lo que va de año', 'Tasa de variación acumulada'], axis=1)
cat_visitors.head()<jupyter_output><empty_output><jupyter_text>Points of interest Observatorium of Tourism in Barcelona<jupyter_code>pits_ob.head(2)
pits_ob.dtypes
#Dropping unnecessary columns with index number
pits_ob=pits_ob.drop('numero', axis=1)
#Replace string values with 0 in columns 2015 and 2016
pits_ob[2015].replace(['Cerrado', 'nd'], 0, inplace=True)
pits_ob[2016].replace(['Cerrado', 'nd'], 0, inplace=True)
#Changing columns 2015 and 2016 to int type
pits_ob[2015]=pits_ob[2015].astype(int)
pits_ob[2016]=pits_ob[2016].astype(int)
#Renaming columns
pits_ob.rename(columns={'Visitantes de museos y colecciones (MC)':'name', 'Type':'type'}, inplace=True)
#Apply upper case for names
pits_ob['name'] = pits_ob['name'].str.upper()
pits_ob['type']= pits_ob['type'].str.upper()

#Remove white space at the beginning and end of the name
pits_ob['name'] = pits_ob['name'].str.lstrip()
pits_ob['type']= pits_ob['type'].str.lstrip()
#Check for duplicate names
pits_ob.name.value_counts()
#Checking every column has a value
pits_ob.count()<jupyter_output><empty_output><jupyter_text>Points of interest Open Data Barcelona<jupyter_code>pits_od.dtypes
pits_od.shape
#Drop unnecessary columns
pits_od=pits_od.drop(['originalpost','categories','atencio_eq', 'phonenumber','type','tp','date','author','tags','language','pos', 'num', 'city', 'address', 'code_url', 'related_post', 'attachments', 'vignette', 'moreinfo', 'usergroup', 'post_modified', 'original_modified', 'wt', 'sigla', 'sectionname'], axis=1)
pits_od.head(2)
#Apply upper case for names
pits_od['name'] = pits_od['name'].str.upper()

#Remove white space at the beginning and end of the name
pits_od['name'] = pits_od['name'].str.lstrip()

#Check for duplicates
pits_od.id.value_counts()
#Drop duplicate values
pits_od=pits_od.drop_duplicates('id')
pits_od.head(2)
#Removing noise strings from exceprt
pits_od['excerpt'] = pits_od['excerpt'].str.replace('<p>', '')
pits_od['excerpt'] = pits_od['excerpt'].str.replace('</p', '')


#Removing noise strings from content
pits_od['content']=pits_od['content'].str.replace('<h3>', '' )
pits_od['content']=pits_od['content'].str.replace('</h3>', '' )
pits_od['content']=pits_od['content'].str.replace('\r\n', '' )
pits_od['content']=pits_od['content'].str.replace('<p>', '' )
pits_od['content']=pits_od['content'].str.replace('<strong>', '' )
pits_od['content']=pits_od['content'].str.replace('</strong>', '' )
pits_od['content']=pits_od['content'].str.replace('</p>', '' )
pits_od['content']=pits_od['content'].str.replace('<!-- .photo-galleria -->', '' )

print(pits_od.excerpt[0])
print(" ")
print(pits_od.content[0])
pits_od.district.value_counts()
pits_od.count()<jupyter_output><empty_output><jupyter_text>Trip Advisor dataset<jupyter_code>TA_data.head(2)
TA_data.shape
TA_data.dtypes
TA_data_bcn=TA_data[TA_data['City']=='Barcelona']
TA_data_bcn.shape
TA_data_bcn.head(2)
TA_data_bcn=TA_data_bcn.drop(['Unnamed: 0', 'URL_TA', 'ID_TA', 'City'], axis=1)
TA_data_bcn.replace('NaN', np.nan, inplace=True)
TA_data_bcn.columns= TA_data_bcn.columns.str.lower()
TA_data_bcn.head(2)
TA_data_bcn=TA_data_bcn.reset_index().drop('index', axis=1)
TA_data_bcn['price range'].value_counts().sum()
TA_data_bcn.count()
TA_data_bcn.shape
pits_od=pits_od.drop_duplicates()
pits_od[pits_od['name']=='El Parc del Fòrum']['title']<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup