Python + SQL

Pandas, Requests, Beautiful Soup, Google Big Query, DuckDB

SQL – BIGQUERY – JUPYTER LAB – Barcelona Number of Foreign Tourists by Neighbourhood per Day

SQL – BIGQUERY¶

Number of National and Foreign TOURISTS per DAY, Lodging Category and Neighbourhood in 2022

REQUIRED LIBRARIES

In [103]:

import pandas as pd
from pandas.io import gbq
import duckdb

In [2]:

project_id=''

INFORMATION_SCHEMA.SCHEMATA¶

In [3]:

df_information_schema_schemata = gbq.read_gbq(query='SELECT schema_name FROM region-us.INFORMATION_SCHEMA.SCHEMATA;', project_id=project_id)
df_information_schema_schemata

Out[3]:

INFORMATION_SCHEMA.TABLES – TOURISM¶

In [4]:

df_information_schema_tables_tourism = gbq.read_gbq(query='SELECT table_name FROM TOURISM.INFORMATION_SCHEMA.TABLES;')
df_information_schema_tables_tourism

Out[4]:

In [5]:

df_information_schema_tables_tourism_tl = gbq.read_gbq(query='SELECT table_schema,table_name,column_name,data_type FROM TOURISM.INFORMATION_SCHEMA.COLUMNS WHERE table_name = "TL";')
df_information_schema_tables_tourism_tl

Out[5]:

In [6]:

df_information_schema_tables_tourism_tt = gbq.read_gbq(query='SELECT table_schema,table_name,column_name,data_type FROM TOURISM.INFORMATION_SCHEMA.COLUMNS WHERE table_name = "TT";')
df_information_schema_tables_tourism_tt

Out[6]:

In [7]:

df_information_schema_tables_tourism_ft = gbq.read_gbq(query='SELECT table_schema,table_name,column_name,data_type FROM TOURISM.INFORMATION_SCHEMA.COLUMNS WHERE table_name = "FT";')
df_information_schema_tables_tourism_ft

Out[7]:

SOURCE DATAFRAMES¶

In [8]:

df_tourism_tl = gbq.read_gbq(query='SELECT * FROM TOURISM.TL;')
df_tourism_tl.head(1)

Out[8]:

1 rows × 24 columns

In [9]:

df_tl = df_tourism_tl.copy()

In [10]:

df_tourism_tt = gbq.read_gbq(query='SELECT * FROM TOURISM.TT;')
df_tourism_tt.head(1)

Out[10]:

In [11]:

df_tt = df_tourism_tt.copy()

In [12]:

df_tourism_ft = gbq.read_gbq(query='SELECT * FROM TOURISM.FT;')
df_tourism_ft.head(1)

Out[12]:

In [13]:

df_ft = df_tourism_ft.copy()

SQL¶

ESTIMATING THE NUMBER OF NATIONAL AND FOREIGN TOURISTS PER DAY IN 2022

SOURCE:
https://www.observatoriturisme.barcelona/en/destination-barcelona-tourism-activity-latest-data

In [107]:

q = """
SELECT year as 'year_x',month as 'month_x',SUM(n_tourists) as 'TOTAL_N_TOURISTS_PER_MONTH'
FROM df_tt 
GROUP BY month, year
;"""
df_total_n_tourists_per_month_2022 = duckdb.query(q).df()
df_total_n_tourists_per_month_2022

Out[107]:

In [108]:

q = """
SELECT FT_YEAR,FT_MONTH,SUM(FT_N_TOURISTS) as 'TOTAL_N_FOREIGN_TOURISTS_PER_MONTH'
FROM df_ft WHERE FT_YEAR=2022
GROUP BY FT_YEAR, FT_MONTH
ORDER BY FT_YEAR, FT_MONTH
;"""
df_n_foreign_tourists_per_month_2022 = duckdb.query(q).df()
df_n_foreign_tourists_per_month_2022

Out[108]:

In [110]:

q = """
SELECT year,month,lodging_type,n_tourists AS 'N_TOURISTS_PER_LODGING_TYPE_PER_MONTH',overnight_stays,average_lenght_stay,TOTAL_N_TOURISTS_PER_MONTH,
n_tourists/TOTAL_N_TOURISTS_PER_MONTH*100 as 'LODGING_TYPE_SHARE'
FROM df_tt LEFT JOIN df_total_n_tourists_per_month_2022 ON month=month_x
;"""
df_lodging_share_total_n_tourists_2022 = duckdb.query(q).df()
df_lodging_share_total_n_tourists_2022

Out[110]:

In [111]:

q = """
SELECT year,month,lodging_type,N_TOURISTS_PER_LODGING_TYPE_PER_MONTH,overnight_stays,average_lenght_stay,LODGING_TYPE_SHARE,TOTAL_N_TOURISTS_PER_MONTH,
TOTAL_N_TOURISTS_PER_MONTH-TOTAL_N_FOREIGN_TOURISTS_PER_MONTH AS 'TOTAL_N_NATIONAL_TOURISTS_PER_MONTH',
TOTAL_N_FOREIGN_TOURISTS_PER_MONTH,
(LODGING_TYPE_SHARE/100*TOTAL_N_NATIONAL_TOURISTS_PER_MONTH)  AS 'N_NATIONAL_TOURISTS_PER_LODGING_TYPE_PER_MONTH',
(LODGING_TYPE_SHARE/100*TOTAL_N_FOREIGN_TOURISTS_PER_MONTH)  AS 'N_FOREIGN_TOURISTS_PER_LODGING_TYPE_PER_MONTH'
FROM df_lodging_share_total_n_tourists_2022, df_n_foreign_tourists_per_month_2022
WHERE month=FT_MONTH AND year=FT_YEAR
;"""
df_lodging_share_total_n_tourists_n_foreign_tourists_per_month_2022 = duckdb.query(q).df()
df_lodging_share_total_n_tourists_n_foreign_tourists_per_month_2022

Out[111]:

In [112]:

q = """
SELECT *,
(N_NATIONAL_TOURISTS_PER_LODGING_TYPE_PER_MONTH*average_lenght_stay/30) AS 'N_NATIONAL_TOURISTS_PER_LODGING_TYPE_PER_DAY',
(N_FOREIGN_TOURISTS_PER_LODGING_TYPE_PER_MONTH*average_lenght_stay/30) AS 'N_FOREIGN_TOURISTS_PER_LODGING_TYPE_PER_DAY'
FROM df_lodging_share_total_n_tourists_n_foreign_tourists_per_month_2022
;"""
df_lodging_share_total_n_tourists_n_foreign_tourists_per_month_per_day_2022 = duckdb.query(q).df()
df_lodging_share_total_n_tourists_n_foreign_tourists_per_month_per_day_2022

Out[112]:

NUMBER OF PLACES PER CATEGORY AND NEIGHBOURHOOD

This table agregates the number of places per lodging category for each neighbourhood.

Data refers to the tourist lodgings active at the end of 2022.

Data on those tourist lodgings is here treated as constant throughout 2022.

In [113]:

q = """
SELECT district_code, district_name, neighbourhood_code, neighbourhood_name, category,
SUM(n_places) AS 'NEIGHBOURHOOD_N_PLACES'
FROM df_tl
GROUP BY 3,4,1,2,5
ORDER BY 3,5
;"""
df_n_places_per_neighbourhood_category_2022 = duckdb.query(q).df()
df_n_places_per_neighbourhood_category_2022

Out[113]:

269 rows × 6 columns

In [114]:

q = """
SELECT *,
CASE
WHEN  CONTAINS(category, 'Turístic')  THEN 'homes_for_tourist_use'
ELSE 'hotel'
END 
AS "LODGING_TYPE"
FROM df_n_places_per_neighbourhood_category_2022
;"""
df_n_places_per_neighbourhood_category_lodging_type_2022 = duckdb.query(q).df()
df_n_places_per_neighbourhood_category_lodging_type_2022

Out[114]:

269 rows × 7 columns

In [115]:

q = """
SELECT LODGING_TYPE as 'LODGING_TYPE_X',SUM(NEIGHBOURHOOD_N_PLACES) AS "TOTAL_N_PLACES_PER_LODGING_TYPE"
FROM df_n_places_per_neighbourhood_category_lodging_type_2022
GROUP BY LODGING_TYPE
;"""
df_total_n_places_per_lodging_type_2022 = duckdb.query(q).df()
df_total_n_places_per_lodging_type_2022

Out[115]:

In [116]:

q = """
SELECT year,month,lodging_type AS 'lodging_type_x',	N_NATIONAL_TOURISTS_PER_LODGING_TYPE_PER_DAY,	N_FOREIGN_TOURISTS_PER_LODGING_TYPE_PER_DAY,TOTAL_N_PLACES_PER_LODGING_TYPE,
N_NATIONAL_TOURISTS_PER_LODGING_TYPE_PER_DAY/TOTAL_N_PLACES_PER_LODGING_TYPE*100 AS 'PERCENTAGE_NATIONAL_TOURISTS_PER_N_PLACES',
N_FOREIGN_TOURISTS_PER_LODGING_TYPE_PER_DAY/TOTAL_N_PLACES_PER_LODGING_TYPE*100 AS 'PERCENTAGE_FOREIGN_TOURISTS_PER_N_PLACES'
FROM df_lodging_share_total_n_tourists_n_foreign_tourists_per_month_per_day_2022, df_total_n_places_per_lodging_type_2022
WHERE LODGING_TYPE=LODGING_TYPE_X
;"""
df_n_national_n_foreign_tourists_per_n_places = duckdb.query(q).df()
df_n_national_n_foreign_tourists_per_n_places

Out[116]:

CONCATENATED TABLE: NUMBER OF NATIONAL AND FOREIGN TOURISTS PER DAY, CATEGORY AND NEIGHBOURHOOD IN 2022

In [123]:

q = """
SELECT year, month, district_code, district_name, neighbourhood_code, neighbourhood_name, category, LODGING_TYPE, NEIGHBOURHOOD_N_PLACES,
PERCENTAGE_NATIONAL_TOURISTS_PER_N_PLACES+PERCENTAGE_NATIONAL_TOURISTS_PER_N_PLACES AS 'PERCENTAGE_BOOKED_PLACES',
100-(PERCENTAGE_NATIONAL_TOURISTS_PER_N_PLACES+PERCENTAGE_NATIONAL_TOURISTS_PER_N_PLACES) AS 'PERCENTAGE_FREE_PLACES',
PERCENTAGE_NATIONAL_TOURISTS_PER_N_PLACES AS 'PERCENTAGE_TOTAL_PLACES_BOOKED_BY_NATIONAL_TOURISTS',
PERCENTAGE_FOREIGN_TOURISTS_PER_N_PLACES AS 'PERCENTAGE_TOTAL_PLACES_BOOKED_BY_FOREIGN_TOURISTS',
(PERCENTAGE_NATIONAL_TOURISTS_PER_N_PLACES/100*NEIGHBOURHOOD_N_PLACES) AS 'N_NATIONAL_TOURISTS_PER_DAY',	
(PERCENTAGE_FOREIGN_TOURISTS_PER_N_PLACES/100*NEIGHBOURHOOD_N_PLACES) AS 'N_FOREIGN_TOURISTS_PER_DAY',
N_NATIONAL_TOURISTS_PER_DAY+N_FOREIGN_TOURISTS_PER_DAY AS 'TOTAL_N_TOURISTS_PER_DAY'
FROM 
df_n_places_per_neighbourhood_category_lodging_type_2022, df_n_national_n_foreign_tourists_per_n_places
WHERE  lodging_type_x=LODGING_TYPE
;"""
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_2022 = duckdb.query(q).df()
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_2022

Out[123]:

3228 rows × 16 columns

VERIFICATION¶

data tested on month 11

In [124]:

q = """
SELECT 
(SELECT SUM(N_FOREIGN_TOURISTS_PER_DAY) FROM df_n_national_n_foreign_tourists_per_day_category_neighbourhood_2022 WHERE LODGING_TYPE='hotel' AND month=11)*30/2.559062 +
(SELECT SUM(N_FOREIGN_TOURISTS_PER_DAY) FROM df_n_national_n_foreign_tourists_per_day_category_neighbourhood_2022 WHERE LODGING_TYPE='homes_for_tourist_use' AND month=11)*30/4.475032,
SUM(NEIGHBOURHOOD_N_PLACES)
FROM 
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_2022
WHERE month=11
;"""
duckdb.query(q).df()

Out[124]:

In [125]:

q = """
SELECT SUM(FT_N_TOURISTS)
FROM 
df_tourism_ft
WHERE FT_MONTH=11 AND FT_YEAR=2022
;"""
duckdb.query(q).df()

Out[125]:

In [126]:

q = """
SELECT SUM(n_places)
FROM df_tourism_tl
;"""
duckdb.query(q).df()

Out[126]:

In [127]:

q = """
SELECT 
(SELECT SUM(TOTAL_N_TOURISTS_PER_DAY) FROM df_n_national_n_foreign_tourists_per_day_category_neighbourhood_2022 WHERE LODGING_TYPE='hotel' AND month=11)*30/2.559062 +
(SELECT SUM(TOTAL_N_TOURISTS_PER_DAY) FROM df_n_national_n_foreign_tourists_per_day_category_neighbourhood_2022 WHERE LODGING_TYPE='homes_for_tourist_use' AND month=11)*30/4.475032
FROM 
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_2022
WHERE month=11
LIMIT 1
;"""
duckdb.query(q).df()

Out[127]:

In [129]:

q = """
SELECT 
(SELECT SUM(N_TOURISTS_PER_LODGING_TYPE_PER_MONTH) FROM df_lodging_share_total_n_tourists_2022 WHERE LODGING_TYPE='hotel' AND month=11) +
(SELECT SUM(N_TOURISTS_PER_LODGING_TYPE_PER_MONTH) FROM df_lodging_share_total_n_tourists_2022 WHERE LODGING_TYPE='homes_for_tourist_use' AND month=11)
FROM 
df_lodging_share_total_n_tourists_2022
WHERE month=11
LIMIT 1
;"""
duckdb.query(q).df()

Out[129]:

EXCEL EXPORT FILE¶

NORMALIZATION

In [130]:

#ROUNDING VALUES
q = """
SELECT year, month, district_code, district_name, neighbourhood_code, neighbourhood_name, category, LODGING_TYPE, NEIGHBOURHOOD_N_PLACES,
PERCENTAGE_BOOKED_PLACES,PERCENTAGE_FREE_PLACES,PERCENTAGE_TOTAL_PLACES_BOOKED_BY_NATIONAL_TOURISTS,PERCENTAGE_TOTAL_PLACES_BOOKED_BY_FOREIGN_TOURISTS,
ROUND(N_NATIONAL_TOURISTS_PER_DAY) AS 'N_NATIONAL_TOURISTS_PER_DAY',	
ROUND(N_FOREIGN_TOURISTS_PER_DAY) AS 'N_FOREIGN_TOURISTS_PER_DAY',
ROUND(TOTAL_N_TOURISTS_PER_DAY) AS 'TOTAL_N_TOURISTS_PER_DAY'
FROM 
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_2022
;"""
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022 = duckdb.query(q).df()
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022

Out[130]:

3228 rows × 16 columns

In [131]:

#distric_code and neighbourhood_code need a '0' in front of all numbers below 10 to combine with spacial data
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022['district_code'] = df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022['district_code'].astype(str).str.strip()
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022['neighbourhood_code'] = df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022['neighbourhood_code'].astype(str).str.strip()
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022[['district_code']] = df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022[['district_code']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])
df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022[['neighbourhood_code']] = df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022[['neighbourhood_code']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])

In [132]:

df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022

Out[132]:

3228 rows × 16 columns

In [133]:

df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022.to_excel('T_SQL_BIGQUERY_N_TOURISTS_PER_NEIGHBOURHOOD_PER_DAY_2022.xlsx', sheet_name='sql_touristsXday')

DATABASE UPLOAD¶

In [134]:

df_n_national_n_foreign_tourists_per_day_category_neighbourhood_r_2022.to_gbq(
        destination_table="TOURISM.T_SQL_BIGQUERY_N_TOURISTS_PER_NEIGHBOURHOOD_PER_DAY_2022",
        project_id=project_id)

In [135]:

gbq.read_gbq(query='SELECT table_name FROM TOURISM.INFORMATION_SCHEMA.TABLES;')

Out[135]:

In [137]:

gbq.read_gbq(query='SELECT table_schema,table_name,column_name,data_type FROM TOURISM.INFORMATION_SCHEMA.COLUMNS WHERE table_name = "T_SQL_BIGQUERY_N_TOURISTS_PER_NEIGHBOURHOOD_PER_DAY_2022";')

Out[137]:

JUPYTER LAB – Barcelona Tourist Lodgings

TROUBLESHOOTING VERSION¶

BARCELONA TOURISM¶

TOURIST LODGINGS – CAPACITY AND DISTRIBUTION BY NEIGHBOURHOOD¶

REQUIRED LIBRARIES¶

In [536]:

#FOR DATA EXTRACTION AND CLEANING:
import pandas as pd
import requests

DATA EXTRACTION AND CLEANING¶

DATAFRAME: DISTRICT_NEIGHBOURHOOD_TABLE¶

This file contains the codes and names of districts and neighbourhoods used in other projects.
The file is already cleaned and used here as a conversion table to make data compatible for visualizations with spatial files.

In [537]:

df_district_neighbourhood_table = pd.read_excel('F:DPortfolio ProjectsBARCELONA TOURISMT_NEIGHBOURHOODS.xlsx',converters={'District_Code':str,'Neighbourhood_Code':str})
df_district_neighbourhood_table

Out[537]:

73 rows × 4 columns

DATAFRAME: NUMBER OF PLACES BY ESTABLISHMENT¶

SOURCE:

https://ajuntament.barcelona.cat/ecologiaurbana/ca/tramits/activitats/cens

http://w121.bcn.cat/APPS/censactivitats/cceatDef.do?reqCode=search

In [538]:

df_np = pd.read_excel('extraccio.xlsx')
df_np.head(1)

Out[538]:

In [539]:

df_np1 = df_np.copy()

EXTRACT RELEVANT DATA¶

In [540]:

#DROP THE ZONE AND ZONE_DESCRIPTION COLUMNS
df_np1.drop(columns = ['Zona','Descripció zona'], inplace = True)

In [541]:

#RENAME COLUMNS IN ENGLISH
df_np1.columns = ['n_practice','rtc','category','address','street_type',
                       'street','street_number_1','street_letter_1','street_number_2', 'street_letter_2','block',
                       'entrance','stair','floor','door','neighbourhood_name','district_name','n_places']
df_np1.head(1)

Out[541]:

In [542]:

#ADD EMPTY COLUMNS FOR RELEVANT DATA TO ADD FROM OTHER TABLES:
df_np1['neighbourhood_code'] = None
df_np1['district_code'] = None
df_np1['longitude'] = None
df_np1['latitude'] = None
df_np1['name'] = None

In [543]:

#REARRANGE COLUMNS IN THE PREFERRED ORDER
df_np1.columns

Out[543]:

Index(['n_practice', 'rtc', 'category', 'address', 'street_type', 'street',
       'street_number_1', 'street_letter_1', 'street_number_2',
       'street_letter_2', 'block', 'entrance', 'stair', 'floor', 'door',
       'neighbourhood_name', 'district_name', 'n_places', 'neighbourhood_code',
       'district_code', 'longitude', 'latitude', 'name'],
      dtype='object')

In [544]:

#REARRANGE COLUMNS IN THE PREFERRED ORDER
df_np1 = df_np1[['n_practice', 'rtc', 'name', 'category', 'address', 'street_type', 'street',
       'street_number_1', 'street_letter_1', 'street_number_2',
       'street_letter_2', 'block', 'entrance', 'stair', 'floor', 'door',
        'district_code','district_name', 'neighbourhood_code','neighbourhood_name',
        'longitude', 'latitude', 'n_places']]
df_np1.head(1)

Out[544]:

1 rows × 23 columns

In [545]:

#CHECK THE CATEGORY ATTRIBUTE
df_np1['category'].value_counts()

Out[545]:

Habitatges d'Ús Turístic                 9409
Pensió                                    286
Hotel 4 estrelles                         144
Albergs                                   126
Hotel 3 estrelles                         119
Residències estudiants en sòl de zona      64
Hotel 1 estrella                           47
Hotel 2 estrelles                          42
Hotel 4 estrelles superior                 27
Hotel 5 estrelles                          24
Hotel gran luxe                            20
Hotel-Apart 4 estrelles                    12
Apartaments Turístics                      12
Hotel-Apart 3 estrelles                    10
Hotel-Apart 2 estrelles                     6
Hotel-Apart 1 estrella                      2
Hotel-Apart 4 estrelles superior            1
Name: category, dtype: int64

In [546]:

#SINCE THE STUDY FOCUSES ON ESTABLISHMENT FOR TOURIST, THE CATEGORY 'Residències estudiants en sòl de zona' (STUDENT RESIDENCIES) IS DROPPED
df_np1 = df_np1[df_np1['category']!='Residències estudiants en sòl de zona']
df_np1[df_np1['category']=='Residències estudiants en sòl de zona'].head(1)

Out[546]:

0 rows × 23 columns

In [547]:

df_np2 = df_np1.copy()

In [548]:

df_np2.head(5)

Out[548]:

5 rows × 23 columns

WHITESPACES¶

In [549]:

#REMOVING SPACES
# .replace(' ','', regex=True) - replace all spaces with nothing
# .str.strip() - replace 1 initial and 1 trailing space only
# .replace(r's+',' ', regex=True) - replace multiple spaces with one single space
# .replace(r'^s+|s+$','',regex=True) - replace all + spaces s starting ^ and trailing $
# .replace('nan','', regex=True) - replace pre-existing 'nan' strings into empty cells - not to be used for string columns potentially containing nan as subpart of string
# .replace('.0','',regex=True) - replace .0 with nothing - '' is required to assign '.' as a normal character and not as a special one
df_np2['n_practice'] = df_np2['n_practice'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['rtc'] = df_np2['rtc'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['category'] = df_np2['category'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_np2['address'] = df_np2['address'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_np2['street_type'] = df_np2['street_type'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_np2['street'] = df_np2['street'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_np2['street_number_1'] = df_np2['street_number_1'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['street_letter_1'] = df_np2['street_letter_1'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['street_number_2'] = df_np2['street_number_2'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['street_letter_2'] = df_np2['street_letter_2'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['block'] = df_np2['block'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['entrance'] = df_np2['entrance'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['stair'] = df_np2['stair'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['floor'] = df_np2['floor'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['door'] = df_np2['door'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_np2['district_name'] = df_np2['district_name'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_np2['neighbourhood_name'] = df_np2['neighbourhood_name'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_np2['n_places'] = df_np2['n_places'].astype(float)

In [550]:

#REPLACE CELL THAT IS ENTIRELY SPACE OR EMPTY with None
df_np2 = df_np2.applymap(lambda x: None if isinstance(x, str) and (x=='' or x.isspace()) else x)

In [551]:

df_np3 = df_np2.copy()

DUPLICATES¶

In [552]:

df_np3.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 10287 entries, 0 to 10350
Data columns (total 23 columns):
 #   Column              Non-Null Count  Dtype  
---  ------              --------------  -----  
 0   n_practice          10287 non-null  object 
 1   rtc                 10287 non-null  object 
 2   name                0 non-null      object 
 3   category            10287 non-null  object 
 4   address             10287 non-null  object 
 5   street_type         10287 non-null  object 
 6   street              10287 non-null  object 
 7   street_number_1     10287 non-null  object 
 8   street_letter_1     125 non-null    object 
 9   street_number_2     886 non-null    object 
 10  street_letter_2     4 non-null      object 
 11  block               14 non-null     object 
 12  entrance            3 non-null      object 
 13  stair               690 non-null    object 
 14  floor               9691 non-null   object 
 15  door                8697 non-null   object 
 16  district_code       0 non-null      object 
 17  district_name       10287 non-null  object 
 18  neighbourhood_code  0 non-null      object 
 19  neighbourhood_name  10287 non-null  object 
 20  longitude           0 non-null      object 
 21  latitude            0 non-null      object 
 22  n_places            10270 non-null  float64
dtypes: float64(1), object(22)
memory usage: 1.9+ MB

In [553]:

#PRELIMINARY CHECK FOR DUPLICATES
df_np3.duplicated().value_counts()

Out[553]:

False    10287
dtype: int64

ADDRESS¶

In [554]:

#CHECK FOR DUPLICATES BY EXCLUDING: N_PRACTICE, RTC AND N_PLACES - TO SEE IF THERE ARE DUPLICATES ONLY IN TERMS OF CATEGORY AND ADDRESS
df_np3[df_np3.duplicated(subset=['category','address','street_type',
                       'street','street_number_1','street_letter_1','street_number_2', 'street_letter_2','block',
                       'entrance','stair','floor','door','neighbourhood_name','district_name'], keep=False)].sort_values('rtc')

Out[554]:

2 rows × 23 columns

In [555]:

#FROM THE PREVIOUS CHECK, THERE APPEAR TO BE 1 CASE WITH 2 DISTINCT N_PRACTICE RECORDS WHERE THE VARIABLE OF INTEREST - N_PLACES - IS ONLY PRESENT IN ONE OF THE DUPLICATED VALUES. 
#TO SELECT THE DUPLICATED VALUES WITH NO VARIABLE OF INTEREST:
df_np3[df_np3.duplicated(subset=['category','address','street_type',
                       'street','street_number_1','street_letter_1','street_number_2', 'street_letter_2','block',
                       'entrance','stair','floor','door','neighbourhood_name','district_name'], keep=False) & (df_np2['n_places'].isnull() | (df_np2['n_places']==''))]

Out[555]:

1 rows × 23 columns

In [556]:

#DROP THE DUPLICATED CASES WITH NO VARIBLE OF INTEREST
df_np3.drop(df_np3[(df_np3.duplicated(subset=['category','address','street_type',
                       'street','street_number_1','street_letter_1','street_number_2', 'street_letter_2','block',
                       'entrance','stair','floor','door','neighbourhood_name','district_name'], keep=False)) 
                   & (df_np3['n_places'].isnull() | (df_np3['n_places']==''))].index, inplace=True)

In [557]:

#VERIFY THAT THE RIGHT RECORDS ARE GONE
df_np3[df_np3['n_practice']=='05-2004-0005']

Out[557]:

0 rows × 23 columns

In [558]:

df_np4 = df_np3.copy()

N_PRACTICE¶

In [559]:

#CHECK IF N_PRACTICE CONTAINS NULL VALUES
df_np4[(df_np4['n_practice'].isnull()) | (df_np4['n_practice']== None) 
                    | (df_np4['n_practice']=='nan') | (df_np4['n_practice']=='')]

Out[559]:

0 rows × 23 columns

In [560]:

#CHECK IF N_PRACTICE CONTAINS DUPLICATED VALUES
df_np4['n_practice'].value_counts()

Out[560]:

10-2001-0694    2
06-2017-0212    2
02-2015-0048    2
06-2010-0423    2
01-90-A-128     1
               ..
06-2013-0508    1
06-2012-0606    1
06-2012-0353    1
06-2014-0361    1
01-NT-0075      1
Name: n_practice, Length: 10282, dtype: int64

In [561]:

#FIND N_PRACTICE DUPLICATED VALUES
df_np4[df_np4.duplicated(subset=['n_practice'], keep=False)].sort_values('n_practice')

Out[561]:

8 rows × 23 columns

The records above appear to be related to cases where establishments have sections belonging to different categories within the same establishment – therefore, these are not dropped

In [562]:

df_np5 = df_np4.copy()

RTC¶

In [563]:

#CHECK IF RTC CONTAINS NULL VALUES
df_np5[(df_np5['rtc'].isnull()) | (df_np5['rtc']== None) 
                    | (df_np5['rtc']=='nan') | (df_np5['rtc']=='')]

Out[563]:

0 rows × 23 columns

In [564]:

#CHECK IF RTC CONTAINS DUPLICATED VALUES
df_np5['rtc'].value_counts().head(6)

Out[564]:

Pendent      68
HB-000957     3
HB-001951     2
HB-004669     2
ALB-562       2
HB-003893     1
Name: rtc, dtype: int64

In [565]:

#RTC DUPLICATED VALUES WITH "Pendent" (PENDING) VALUES - CATEGORY TYPES
df_np5[df_np5['rtc'] == 'Pendent'].value_counts('category')

Out[565]:

category
Habitatges d'Ús Turístic    48
Pensió                       7
Hotel 3 estrelles            4
Hotel 4 estrelles            3
Albergs                      2
Hotel 5 estrelles            2
Hotel 1 estrella             1
Hotel 2 estrelles            1
dtype: int64

In [566]:

#RTC DUPLICATED VALUES - EXCLUDING "Pendent" (PENDING) VALUES
df_np5[(df_np5.duplicated(subset=['rtc'], keep=False)) & (df_np5['rtc'] != 'Pendent')].sort_values('rtc')

Out[566]:

9 rows × 23 columns

The first 7 records refer to different floors within the same establishments.
However, only in one case the variable of interest – n_places – is indicated for all floors.
In the other cases, the varible of interest – n_places – is indicated only in one of the floors.

Records are not modified as it might be possible to correct them by crossreferencing them with other tables later.

The last 2 records might be a case where the RTC code was wrongly inputed as the same RTC code refers to 2 completely different establishments.
By crossreferencing with later tables, a match was found only for n_practice 02-2016-1310. Therefore, the rtc value for the other record n_practice 02-2016-1367 is modified with the suffix ERROR.

In [567]:

df_np5.loc[df_np5['n_practice']=='02-2016-1367', 'rtc'] = 'ERROR-HB-004669'
df_np5[df_np5['n_practice']=='02-2016-1367']

Out[567]:

1 rows × 23 columns

In [568]:

df_np5[df_np5['n_practice']=='02-2016-1310']

Out[568]:

1 rows × 23 columns

In [569]:

df_np6 = df_np5.copy()

MISSING VALUES¶

In [570]:

#QUICK CHECK FOR MISSING VALUES
df_np6.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 10286 entries, 0 to 10350
Data columns (total 23 columns):
 #   Column              Non-Null Count  Dtype  
---  ------              --------------  -----  
 0   n_practice          10286 non-null  object 
 1   rtc                 10286 non-null  object 
 2   name                0 non-null      object 
 3   category            10286 non-null  object 
 4   address             10286 non-null  object 
 5   street_type         10286 non-null  object 
 6   street              10286 non-null  object 
 7   street_number_1     10286 non-null  object 
 8   street_letter_1     125 non-null    object 
 9   street_number_2     886 non-null    object 
 10  street_letter_2     4 non-null      object 
 11  block               14 non-null     object 
 12  entrance            3 non-null      object 
 13  stair               690 non-null    object 
 14  floor               9691 non-null   object 
 15  door                8697 non-null   object 
 16  district_code       0 non-null      object 
 17  district_name       10286 non-null  object 
 18  neighbourhood_code  0 non-null      object 
 19  neighbourhood_name  10286 non-null  object 
 20  longitude           0 non-null      object 
 21  latitude            0 non-null      object 
 22  n_places            10270 non-null  float64
dtypes: float64(1), object(22)
memory usage: 1.9+ MB

N_PLACES¶

In [571]:

#CHECK WHICH CATEGORIES HAVE MISSING VALUES OF INTEREST - N PLACES
df_np6[(df_np6['n_places'].isnull()) | (df_np6['n_places']== None) 
                    | (df_np6['n_places']=='nan') | (df_np6['n_places']=='')].value_counts('category')

Out[571]:

category
Habitatges d'Ús Turístic    13
Pensió                       3
dtype: int64

NO MODIFICATIONS MADE FOR NOW HOW AS IT MIGHT BE POSSIBLE TO RECOVER SOME OF THE DATA FROM THE OTHER TABLES

DISTRICT – NEIGHBOURHOOD¶

Comparing table with district and neighbourhood table

In [572]:

df_np6[(df_np6['district_name'].isnull()) | (df_np6['district_name']== None)
                    | (df_np6['district_name']=='nan') | (df_np6['district_name']=='')].shape[0]

Out[572]:

9

In [573]:

df_np6[(df_np6['neighbourhood_name'].isnull()) | (df_np6['neighbourhood_name']== None) 
                    | (df_np6['neighbourhood_name']=='nan') | (df_np6['neighbourhood_name']=='')].shape[0]

Out[573]:

9

In [574]:

df_np6['district_name'].isin(df_district_neighbourhood_table['District_Name']).value_counts()

Out[574]:

False    10286
Name: district_name, dtype: int64

In [575]:

df_np6['neighbourhood_name'].isin(df_district_neighbourhood_table['Neighbourhood_Name']).value_counts()

Out[575]:

True     9759
False     527
Name: neighbourhood_name, dtype: int64

In [576]:

df_np6[['district_name','neighbourhood_name']][(~df_np6['neighbourhood_name'].isin(df_district_neighbourhood_table['Neighbourhood_Name'])) 
                                               & (df_np6['neighbourhood_name'].notnull())].sort_values('neighbourhood_name')

Out[576]:

527 rows × 2 columns

In [577]:

df_district_neighbourhood_table[df_district_neighbourhood_table['Neighbourhood_Name'].str.contains('Poble')]

Out[577]:

In [578]:

df_np6.loc[df_np6['neighbourhood_name']=='el Poble Sec','neighbourhood_name'] = 'el Poble-sec'

In [579]:

df_np6[['district_name','neighbourhood_name']][(~df_np6['neighbourhood_name'].isin(df_district_neighbourhood_table['Neighbourhood_Name'])) 
                                               & (df_np6['neighbourhood_name'].notnull())].sort_values('neighbourhood_name')

Out[579]:

In [580]:

df_np6[(df_np6['neighbourhood_name'].isnull()) | (df_np6['neighbourhood_name']=='') | (df_np6['neighbourhood_name']==None)| (df_np6['neighbourhood_name']=='nan') 
       | (df_np6['district_name'].isnull()) | (df_np6['district_name']=='') | (df_np6['district_name']==None)| (df_np6['district_name']=='nan')]

Out[580]:

9 rows × 23 columns

MANUAL INPUTATION AS MISSING RECORDS ARE ONLY 7

AV MERIDIANA 109 – neighbourhood_name: el Clot neighbourhood_code: 65
C INDUSTRIA 175 – neighbourhood_name: la Sagrada Família neighbourhood_code: 06
C JOSEP TORRES 26 – neighbourhood_name: la Vila de Gràcia neighbourhood_code: 31
C RIERA DE SANT MIQUEL 49 – neighbourhood_name: la Vila de Gràcia neighbourhood_code: 31
C SANT SALVADOR 20 – neighbourhood_name: la Vila de Gràcia neighbourhood_code: 31
C TAQUIGRAF SERRA 1 – neighbourhood_name: les Corts neighbourhood_code: 19
C TARRAGONA 84 – neighbourhood_name: la Nova Esquerra de l’Eixample neighbourhood_code: 09

SOURCE:
https://ajuntament.barcelona.cat/estadistica/catala/Territori/div84/convertidors/barris73.htm
In [581]:

df_np7 = df_np6.copy()

In [582]:

#FILL IN MISSING VALUES
df_np7.loc[df_np7['n_practice']=='10-2014-0475','neighbourhood_code'] = "65"
df_np7.loc[df_np7['n_practice']=='10-2014-0475','neighbourhood_name'] = "el Clot"
df_np7.loc[df_np7['n_practice']=='02-2013-0207','neighbourhood_code'] = "06"
df_np7.loc[df_np7['n_practice']=='02-2013-0207','neighbourhood_name'] = "la Sagrada Família"
df_np7.loc[df_np7['n_practice']=='06-2014-0281','neighbourhood_code'] = "31"
df_np7.loc[df_np7['n_practice']=='06-2014-0281','neighbourhood_name'] = "la Vila de Gràcia"
df_np7.loc[df_np7['n_practice']=='06-2013-0206','neighbourhood_code'] = "31"
df_np7.loc[df_np7['n_practice']=='06-2013-0206','neighbourhood_name'] = "la Vila de Gràcia"
df_np7.loc[df_np7['n_practice']=='06-2013-0207','neighbourhood_code'] = "31"
df_np7.loc[df_np7['n_practice']=='06-2013-0207','neighbourhood_name'] = "la Vila de Gràcia"
df_np7.loc[df_np7['n_practice']=='06-2013-0208','neighbourhood_code'] = "31"
df_np7.loc[df_np7['n_practice']=='06-2013-0208','neighbourhood_name'] = "la Vila de Gràcia"
df_np7.loc[df_np7['n_practice']=='06-2012-0286','neighbourhood_code'] = '31'
df_np7.loc[df_np7['n_practice']=='06-2012-0286','neighbourhood_name'] = 'la Vila de Gràcia'
df_np7.loc[df_np7['n_practice']=='04-2014-0216','neighbourhood_code'] = '19'
df_np7.loc[df_np7['n_practice']=='04-2014-0216','neighbourhood_name'] = 'les Corts'
df_np7.loc[df_np7['n_practice']=='02-2022-0524','neighbourhood_code'] = "09"
df_np7.loc[df_np7['n_practice']=='02-2022-0524','neighbourhood_name'] = "la Nova Esquerra de l'Eixample"

In [583]:

#MAPPING/REPLACING REQUIRES NO NULL VALUES IN COLUMN LINKED TO set_index COLUMN
df_np7['neighbourhood_code'] = df_np7['neighbourhood_name'].replace(df_district_neighbourhood_table.set_index('Neighbourhood_Name')['Neighbourhood_Code'])
df_np7['district_code'] = df_np7['neighbourhood_name'].replace(df_district_neighbourhood_table.set_index('Neighbourhood_Name')['District_Code'])
df_np7['district_name'] = df_np7['neighbourhood_name'].replace(df_district_neighbourhood_table.set_index('Neighbourhood_Name')['District_Name'])

In [584]:

#CHECK
df_np7[(df_np7['neighbourhood_name'].isnull()) | (df_np7['neighbourhood_name']=='nan') |  (df_np7['neighbourhood_name']==None) |  (df_np7['neighbourhood_name']=='')]

Out[584]:

0 rows × 23 columns

It might be possible to find and fill in the missing data later on when crossreferencing with other tables. Therefore, records kept incomplete for now.

In [585]:

df_np8 = df_np7.copy()

NORMALIZATION¶

In [586]:

df_np8.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 10286 entries, 0 to 10350
Data columns (total 23 columns):
 #   Column              Non-Null Count  Dtype  
---  ------              --------------  -----  
 0   n_practice          10286 non-null  object 
 1   rtc                 10286 non-null  object 
 2   name                0 non-null      object 
 3   category            10286 non-null  object 
 4   address             10286 non-null  object 
 5   street_type         10286 non-null  object 
 6   street              10286 non-null  object 
 7   street_number_1     10286 non-null  object 
 8   street_letter_1     125 non-null    object 
 9   street_number_2     886 non-null    object 
 10  street_letter_2     4 non-null      object 
 11  block               14 non-null     object 
 12  entrance            3 non-null      object 
 13  stair               690 non-null    object 
 14  floor               9691 non-null   object 
 15  door                8697 non-null   object 
 16  district_code       10286 non-null  object 
 17  district_name       10286 non-null  object 
 18  neighbourhood_code  10286 non-null  object 
 19  neighbourhood_name  10286 non-null  object 
 20  longitude           0 non-null      object 
 21  latitude            0 non-null      object 
 22  n_places            10270 non-null  float64
dtypes: float64(1), object(22)
memory usage: 1.9+ MB

In [587]:

df_np8['category'].value_counts()

Out[587]:

Habitatges d'Ús Turístic            9409
Pensió                               286
Hotel 4 estrelles                    144
Albergs                              125
Hotel 3 estrelles                    119
Hotel 1 estrella                      47
Hotel 2 estrelles                     42
Hotel 4 estrelles superior            27
Hotel 5 estrelles                     24
Hotel gran luxe                       20
Hotel-Apart 4 estrelles               12
Apartaments Turístics                 12
Hotel-Apart 3 estrelles               10
Hotel-Apart 2 estrelles                6
Hotel-Apart 1 estrella                 2
Hotel-Apart 4 estrelles superior       1
Name: category, dtype: int64

In [588]:

df_n_places = df_np8.copy()

DATAFRAME: COORDINATES FOR TOURIST ACCOMODATIONS IN PRIVATE HOUSES – hut – E.G. AIRBNB¶

SOURCE:

https://opendata-ajuntament.barcelona.cat/data/en/dataset/habitatges-us-turistic/resource/b32fa7f6-d464-403b-8a02-0292a64883bf

The csv file provided needs cleaning. The problem is created by the neighbourhood: “Sant Pere, Santa Caterina i la Ribera”. The comma within the neighbourhood name split the corresponding column into 2: “Sant Pere” and “Santa Caterina i la Ribera”. Data for that neighbourhood is therefore moved one column on the right, making the file appear to have one more column than it should. This prevents Pandas to allocate data within the apprpriate columns.
A way to clean the data is to:

• save the file as an excel file
• split values into columns by using comma as a delimiter
• frame the data as a table
• sort all results by including only those referring to “Sant Pere, Santa Caterina i la Ribera”
• find and replace “Sant Pere” with “Sant Pere, Santa Caterina i la Ribera”
• cut and paste all data on the right of the column “Santa Caterina i la Ribera” on the same column to eliminate it and reposition the data in the correct place

In [589]:

df_hut_coordinates = pd.read_excel('2022_Hut_comunicacio_cleaned.xlsx')
df_hut_coordinates.head(1)

Out[589]:

1 rows × 21 columns

In [590]:

df_hut_coordinates1 = df_hut_coordinates.copy()

EXTRACT RELEVANT DATA¶

In [591]:

#DROP COLUMNS
df_hut_coordinates1.drop(columns=['TIPUS_NUM'], inplace=True)

In [592]:

#RENAME COLUMNS IN ENGLISH
df_hut_coordinates1.columns = ['n_practice_hut','district_code_hut','district_name_hut','neighbourhood_code_hut',
                               'neighbourhood_name_hut','street_type_hut','street_hut',
                               'street_number_1_hut','street_letter_1_hut','street_number_2_hut','street_letter_2_hut',
                               'block_hut','entrance_hut','stair_hut','floor_hut','door_hut','rtc_hut','n_places_hut',
                               'longitude_hut','latitude_hut']
df_hut_coordinates1.head(1)

Out[592]:

In [593]:

#ADD COLUMN NAME WITH CATEGORY REPEATED AS THERE ARE NO NAMES FOR HUTS
df_hut_coordinates1['name_hut'] = "Habitatges d'Ús Turístic"
df_hut_coordinates1.head(1)

Out[593]:

1 rows × 21 columns

WHITESPACES¶

In [594]:

#REMOVING SPACES
# .replace(' ','', regex=True) - replace all spaces with nothing
# .str.strip() - replace 1 initial and 1 trailing space only
# .replace(r's+',' ', regex=True) - replace multiple spaces with one single space
# .replace(r'^s+|s+$','',regex=True) - replace all + spaces s starting ^ and trailing $
# .replace('nan','', regex=True) - replace pre-existing 'nan' strings into empty cells - not to be used for string columns potentially containing nan as subpart of string
# .replace('.0','',regex=True) - replace .0 with nothing - '' is required to assign '.' as a normal character and not as a special one
df_hut_coordinates1['n_practice_hut'] = df_hut_coordinates1['n_practice_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['rtc_hut'] = df_hut_coordinates1['rtc_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['street_type_hut'] = df_hut_coordinates1['street_type_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_hut_coordinates1['street_hut'] = df_hut_coordinates1['street_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_hut_coordinates1['street_number_1_hut'] = df_hut_coordinates1['street_number_1_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['street_letter_1_hut'] = df_hut_coordinates1['street_letter_1_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['street_number_2_hut'] = df_hut_coordinates1['street_number_2_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['street_letter_2_hut'] = df_hut_coordinates1['street_letter_2_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['block_hut'] = df_hut_coordinates1['block_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['entrance_hut'] = df_hut_coordinates1['entrance_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['stair_hut'] = df_hut_coordinates1['stair_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['floor_hut'] = df_hut_coordinates1['floor_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['door_hut'] = df_hut_coordinates1['door_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['district_code_hut'] = df_hut_coordinates1['district_code_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['neighbourhood_code_hut'] = df_hut_coordinates1['neighbourhood_code_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['district_name_hut'] = df_hut_coordinates1['district_name_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_hut_coordinates1['neighbourhood_name_hut'] = df_hut_coordinates1['neighbourhood_name_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_hut_coordinates1['longitude_hut'] = df_hut_coordinates1['longitude_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['latitude_hut'] = df_hut_coordinates1['latitude_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hut_coordinates1['n_places_hut'] = df_hut_coordinates1['n_places_hut'].astype(float)
df_hut_coordinates1['name_hut'] = df_hut_coordinates1['name_hut'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)

In [595]:

#DISTRICT AND NEIGHBOURHOOD CODES NEED TO BE IN STRING FORMAT AND REQUIRE AN ADDED '0' IN FRONT OF ALL NUMBERS BELOW 10
df_hut_coordinates1[['district_code_hut']] = df_hut_coordinates1[['district_code_hut']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])
df_hut_coordinates1[['neighbourhood_code_hut']] = df_hut_coordinates1[['neighbourhood_code_hut']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])

In [596]:

#REPLACE CELL THAT IS ENTIRELY SPACE OR EMPTY with None
df_hut_coordinates1 = df_hut_coordinates1.applymap(lambda x: None if isinstance(x, str) and (x=='' or x.isspace()) else x)

In [597]:

df_hut_coordinates2 = df_hut_coordinates1.copy()

DUPLICATES¶

In [598]:

#PRELIMINARY CHECK FOR DUPLICATES
df_hut_coordinates2.duplicated().value_counts()

Out[598]:

False    9409
dtype: int64

In [599]:

df_hut_coordinates2.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9409 entries, 0 to 9408
Data columns (total 21 columns):
 #   Column                  Non-Null Count  Dtype  
---  ------                  --------------  -----  
 0   n_practice_hut          9409 non-null   object 
 1   district_code_hut       9409 non-null   object 
 2   district_name_hut       9409 non-null   object 
 3   neighbourhood_code_hut  9405 non-null   object 
 4   neighbourhood_name_hut  9409 non-null   object 
 5   street_type_hut         9409 non-null   object 
 6   street_hut              9409 non-null   object 
 7   street_number_1_hut     9409 non-null   object 
 8   street_letter_1_hut     116 non-null    object 
 9   street_number_2_hut     747 non-null    object 
 10  street_letter_2_hut     3 non-null      object 
 11  block_hut               10 non-null     object 
 12  entrance_hut            3 non-null      object 
 13  stair_hut               689 non-null    object 
 14  floor_hut               9378 non-null   object 
 15  door_hut                8528 non-null   object 
 16  rtc_hut                 9361 non-null   object 
 17  n_places_hut            9396 non-null   float64
 18  longitude_hut           9409 non-null   object 
 19  latitude_hut            9409 non-null   object 
 20  name_hut                9409 non-null   object 
dtypes: float64(1), object(20)
memory usage: 1.5+ MB

In [600]:

df_hut_coordinates2.columns

Out[600]:

Index(['n_practice_hut', 'district_code_hut', 'district_name_hut',
       'neighbourhood_code_hut', 'neighbourhood_name_hut', 'street_type_hut',
       'street_hut', 'street_number_1_hut', 'street_letter_1_hut',
       'street_number_2_hut', 'street_letter_2_hut', 'block_hut',
       'entrance_hut', 'stair_hut', 'floor_hut', 'door_hut', 'rtc_hut',
       'n_places_hut', 'longitude_hut', 'latitude_hut', 'name_hut'],
      dtype='object')

In [601]:

#CHECK DUPLICATES BASED ON ADDRESS ONLY - EXCLUDING ID COLUMNS - 'n_practice_hut', 'rtc_hut': 
#TO SEE IF THERE ARE DUPLICATES ONLY IN TERMS OF ADDRESS
df_hut_coordinates2[df_hut_coordinates2.duplicated(subset=['district_code_hut', 'district_name_hut', 'neighbourhood_code_hut', 'neighbourhood_name_hut',
                                                           'street_type_hut', 'street_hut', 'street_number_1_hut',
                                                           'street_letter_1_hut', 'street_number_2_hut', 'street_letter_2_hut','block_hut', 
                                                           'entrance_hut', 'stair_hut', 'floor_hut', 'door_hut','n_places_hut','longitude_hut', 'latitude_hut'], keep=False)].shape[0]

Out[601]:

0

In [602]:

#CHECK FOR DUPLICATES BY FOCUSING ON ID COLUMN: 'rtc_hut' - EXCLUDING NULL VALUES
df_hut_coordinates1[(df_hut_coordinates1.duplicated(subset=['rtc_hut'], keep=False)) & df_hut_coordinates1['rtc_hut'].notnull()].shape[0]

Out[602]:

0

In [603]:

#CHECK FOR DUPLICATES BY FOCUSING ON ID COLUMN: 'n_practice_hut'
df_hut_coordinates1[df_hut_coordinates1.duplicated(subset=['n_practice_hut'], keep=False)].shape[0]

Out[603]:

0

In [604]:

df_hut_coordinates3 = df_hut_coordinates2.copy()

MISSING VALUES¶

In [605]:

#CHECK FOR MISSING VALUES
df_hut_coordinates3.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9409 entries, 0 to 9408
Data columns (total 21 columns):
 #   Column                  Non-Null Count  Dtype  
---  ------                  --------------  -----  
 0   n_practice_hut          9409 non-null   object 
 1   district_code_hut       9409 non-null   object 
 2   district_name_hut       9409 non-null   object 
 3   neighbourhood_code_hut  9405 non-null   object 
 4   neighbourhood_name_hut  9409 non-null   object 
 5   street_type_hut         9409 non-null   object 
 6   street_hut              9409 non-null   object 
 7   street_number_1_hut     9409 non-null   object 
 8   street_letter_1_hut     116 non-null    object 
 9   street_number_2_hut     747 non-null    object 
 10  street_letter_2_hut     3 non-null      object 
 11  block_hut               10 non-null     object 
 12  entrance_hut            3 non-null      object 
 13  stair_hut               689 non-null    object 
 14  floor_hut               9378 non-null   object 
 15  door_hut                8528 non-null   object 
 16  rtc_hut                 9361 non-null   object 
 17  n_places_hut            9396 non-null   float64
 18  longitude_hut           9409 non-null   object 
 19  latitude_hut            9409 non-null   object 
 20  name_hut                9409 non-null   object 
dtypes: float64(1), object(20)
memory usage: 1.5+ MB

N_PLACES¶

In [606]:

#CHECK MISSING VALUES OF INTEREST - N PLACES
df_hut_coordinates2[(df_hut_coordinates2['n_places_hut'].isnull()) 
                    | (df_hut_coordinates2['n_places_hut']=='') 
                    | (df_hut_coordinates2['n_places_hut']=='nan') 
                    | ((df_hut_coordinates2['n_places_hut']=='None'))].shape[0]

Out[606]:

13

FILLING INTO MISSING VALUES WILL BE OPERATED AFTER MERGING TABLES AS IT MIGHT BE POSSIBLE TO RETRIEVE DATA FROM THE OTHER TABLE

In [607]:

df_hut_coordinates3 = df_hut_coordinates2.copy()

DISTRICT – NEIGHBOURHOOD¶

Comparing table with district and neighbourhood table

In [608]:

#CHECK NULL VALUES
df_hut_coordinates3[(df_hut_coordinates3['neighbourhood_code_hut'].isnull()) | (df_hut_coordinates3['neighbourhood_code_hut']== None) 
                    | (df_hut_coordinates3['neighbourhood_code_hut']=='') | (df_hut_coordinates3['neighbourhood_code_hut']=='')]

Out[608]:

4 rows × 21 columns

In [609]:

#CHECK NULL VALUES
df_hut_coordinates3[(df_hut_coordinates3['neighbourhood_name_hut'].isnull()) | (df_hut_coordinates3['neighbourhood_name_hut']== None) 
                    | (df_hut_coordinates3['neighbourhood_name_hut']=='nan') | (df_hut_coordinates3['neighbourhood_name_hut']=='')].shape[0]

Out[609]:

9

In [610]:

#CHECK IF NEIGHBOURHOOD CODES ARE IN NEIGHBOURHOOD TABLE
df_hut_coordinates3['neighbourhood_code_hut'].isin(df_district_neighbourhood_table['Neighbourhood_Code']).value_counts()

Out[610]:

True     9405
False       4
Name: neighbourhood_code_hut, dtype: int64

In [611]:

#CHECK IF NEIGHBOURHOOD NAMES ARE IN NEIGHBOURHOOD TABLE
df_hut_coordinates3['neighbourhood_name_hut'].isin(df_district_neighbourhood_table['Neighbourhood_Name']).value_counts()

Out[611]:

True     8910
False     499
Name: neighbourhood_name_hut, dtype: int64

In [612]:

#CHECK WHICH NEIGHBOURHOOD CODES ARE NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_hut_coordinates3[(~df_hut_coordinates3['neighbourhood_code_hut'].isin(df_district_neighbourhood_table['Neighbourhood_Code'])) 
                    & df_hut_coordinates3['neighbourhood_code_hut'].notnull()].value_counts('neighbourhood_code_hut')

Out[612]:

Series([], dtype: int64)

In [613]:

#CHECK WHICH NEIGHBOURHOOD CODES ARE NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_hut_coordinates3[df_hut_coordinates3['neighbourhood_code_hut'].isnull()]

Out[613]:

4 rows × 21 columns

In [614]:

#FIND MATCHING RECORDS IN NEIGHBOURHOOD TABLE
df_district_neighbourhood_table[df_district_neighbourhood_table['Neighbourhood_Name'].str.contains('Poblenou')]

Out[614]:

In [615]:

df_hut_coordinates4 = df_hut_coordinates3.copy()

In [616]:

#REPLACE VALUES
df_hut_coordinates4.loc[df_hut_coordinates4['neighbourhood_name_hut']=='el Poblenou','neighbourhood_code_hut'] = '68'

In [617]:

#CHECK HOW MANY NEIGHBOURHOOD CODES ARE NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_hut_coordinates4[(~df_hut_coordinates4['neighbourhood_code_hut'].isin(df_district_neighbourhood_table['Neighbourhood_Code']))]

Out[617]:

3 rows × 21 columns

MANUAL INPUTATION AS MISSING RECORDS ARE ONLY 3
AV MERIDIANA 109 – neighbourhood_name: el Clot neighbourhood_code: 65
C SANT SALVADOR 20 – neighbourhood_name: la Vila de Gràcia neighbourhood_code: 31
C TAQUIGRAF SERRA 1 – neighbourhood_name: les Corts neighbourhood_code: 19

SOURCE:
https://ajuntament.barcelona.cat/estadistica/catala/Territori/div84/convertidors/barris73.htm
In [618]:

#FILL IN MISSING VALUES
df_hut_coordinates4.loc[df_hut_coordinates4['n_practice_hut']=='10-2014-0475','neighbourhood_code_hut'] = '65'
df_hut_coordinates4.loc[df_hut_coordinates4['n_practice_hut']=='10-2014-0475','neighbourhood_name_hut'] = 'el Clot'
df_hut_coordinates4.loc[df_hut_coordinates4['n_practice_hut']=='06-2012-0286','neighbourhood_code_hut'] = '31'
df_hut_coordinates4.loc[df_hut_coordinates4['n_practice_hut']=='06-2012-0286','neighbourhood_name_hut'] = 'la Vila de Gràcia'
df_hut_coordinates4.loc[df_hut_coordinates4['n_practice_hut']=='04-2014-0216','neighbourhood_code_hut'] = '19'
df_hut_coordinates4.loc[df_hut_coordinates4['n_practice_hut']=='04-2014-0216','neighbourhood_name_hut'] = 'les Corts'

In [619]:

#CHECK HOW MANY NEIGHBOURHOOD CODES ARE NOT IN THE NEIGHBOURHOOD TABLE
df_hut_coordinates4[(~df_hut_coordinates4['neighbourhood_code_hut'].isin(df_district_neighbourhood_table['Neighbourhood_Code']))]

Out[619]:

0 rows × 21 columns

In [620]:

#MAPPING/REPLACING REQUIRES NO NULL VALUES IN COLUMN LINKED TO set_index COLUMN
df_hut_coordinates4['neighbourhood_name_hut'] = df_hut_coordinates4['neighbourhood_code_hut'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['Neighbourhood_Name'])
df_hut_coordinates4['district_code_hut'] = df_hut_coordinates4['neighbourhood_code_hut'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Code'])
df_hut_coordinates4['district_name_hut'] = df_hut_coordinates4['neighbourhood_code_hut'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Name'])

In [621]:

#CHECK NULL VALUES
df_hut_coordinates4[(df_hut_coordinates4['neighbourhood_code_hut'].isnull()) | (df_hut_coordinates4['neighbourhood_code_hut']== None) 
                    | (df_hut_coordinates4['neighbourhood_code_hut']=='nan') | (df_hut_coordinates4['neighbourhood_code_hut']=='')]

Out[621]:

0 rows × 21 columns

In [622]:

#CHECK NULL VALUES
df_hut_coordinates4[(df_hut_coordinates4['neighbourhood_name_hut'].isnull()) | (df_hut_coordinates4['neighbourhood_name_hut']== None) 
                    | (df_hut_coordinates4['neighbourhood_name_hut']=='nan') | (df_hut_coordinates4['neighbourhood_name_hut']=='')]

Out[622]:

0 rows × 21 columns

In [623]:

df_hut_coordinates4[['district_code_hut','district_name_hut','neighbourhood_code_hut','neighbourhood_name_hut']].sort_values('neighbourhood_code_hut')

Out[623]:

9409 rows × 4 columns

In [624]:

df_hut_coordinates5 = df_hut_coordinates4.copy()

NORMALIZATION¶

In [625]:

df_hut_coordinates5.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9409 entries, 0 to 9408
Data columns (total 21 columns):
 #   Column                  Non-Null Count  Dtype  
---  ------                  --------------  -----  
 0   n_practice_hut          9409 non-null   object 
 1   district_code_hut       9409 non-null   object 
 2   district_name_hut       9409 non-null   object 
 3   neighbourhood_code_hut  9409 non-null   object 
 4   neighbourhood_name_hut  9409 non-null   object 
 5   street_type_hut         9409 non-null   object 
 6   street_hut              9409 non-null   object 
 7   street_number_1_hut     9409 non-null   object 
 8   street_letter_1_hut     116 non-null    object 
 9   street_number_2_hut     747 non-null    object 
 10  street_letter_2_hut     3 non-null      object 
 11  block_hut               10 non-null     object 
 12  entrance_hut            3 non-null      object 
 13  stair_hut               689 non-null    object 
 14  floor_hut               9378 non-null   object 
 15  door_hut                8528 non-null   object 
 16  rtc_hut                 9361 non-null   object 
 17  n_places_hut            9396 non-null   float64
 18  longitude_hut           9409 non-null   object 
 19  latitude_hut            9409 non-null   object 
 20  name_hut                9409 non-null   object 
dtypes: float64(1), object(20)
memory usage: 1.5+ MB

In [626]:

#CHECK 'street_number_1'
df_hut_coordinates5[df_hut_coordinates5['street_number_1_hut'].str.isdecimal()==False].value_counts('street_number_1_hut') 

Out[626]:

Series([], dtype: int64)

In [627]:

#CHECK 'street_number_2'
df_hut_coordinates5[df_hut_coordinates5['street_number_2_hut'].str.isdecimal()==False].value_counts('street_number_2_hut')

Out[627]:

Series([], dtype: int64)

In [628]:

df_hut = df_hut_coordinates5.copy()

DATAFRAME: COORDINATES FOR HOTELS¶

SOURCE:

https://opendata-ajuntament.barcelona.cat/data/en/dataset/allotjaments-hotels/resource/9bccce1b-0b9d-4cc6-94a7-459cb99450de

In [629]:

R_ID_HOTEL = '9bccce1b-0b9d-4cc6-94a7-459cb99450de'
url_hotel = 'https://opendata-ajuntament.barcelona.cat/data/api/action/datastore_search_sql?sql=SELECT%20*%20from%20%22{}%22'.format(R_ID_HOTEL)
response_hotel = requests.get(url_hotel)
if response_hotel.ok:
    data_hotel = response_hotel.json()
    result_hotel = data_hotel.get('result')
    records_hotel = result_hotel.get('records')
else:
    print('Problem with: ', url_hotel)
df_hotel_coordinates = pd.DataFrame.from_dict(records_hotel)
df_hotel_coordinates.head(1)

Out[629]:

1 rows × 38 columns

In [630]:

df_hotel_coordinates1 = df_hotel_coordinates.copy()

EXTRACT RELEVANT DATA¶

In [631]:

#REMOVE UNNECESSARY COLUMNS
df_hotel_coordinates1.columns

Out[631]:

Index(['addresses_roadtype_name', 'addresses_end_street_number',
       'institution_name', 'values_attribute_name', 'addresses_road_name',
       'values_category', 'addresses_zip_code', 'secondary_filters_id',
       'values_value', 'addresses_town', 'geo_epgs_4326_y', 'geo_epgs_4326_x',
       'secondary_filters_name', 'secondary_filters_tree',
       'addresses_district_name', 'geo_epgs_25831_x',
       'addresses_start_street_number', 'register_id', 'institution_id',
       'addresses_main_address', 'addresses_district_id',
       'addresses_roadtype_id', 'addresses_type', 'addresses_neighborhood_id',
       'values_id', 'name', 'addresses_road_id', 'created', 'geo_epgs_25831_y',
       '_full_text', 'modified', 'secondary_filters_asia_id',
       'secondary_filters_fullpath', 'values_description', '_id',
       'addresses_neighborhood_name', 'values_outstanding',
       'values_attribute_id'],
      dtype='object')

In [632]:

#CHECK BEFORE ELIMINATING COLUMNS
df_hotel_coordinates1[['created','modified','institution_name','institution_id','_full_text',
          'values_id','values_attribute_name','values_category','values_value',
          'values_outstanding','values_attribute_id','values_description']].head(1)

Out[632]:

In [633]:

#CHECK BEFORE ELIMINATING COLUMNS
df_hotel_coordinates1[['_id','secondary_filters_asia_id','secondary_filters_fullpath','secondary_filters_tree',
          'addresses_roadtype_name','addresses_main_address', 'addresses_type', 'addresses_road_id', 
          'addresses_roadtype_id','geo_epgs_25831_x', 'geo_epgs_25831_y']].head(1)

Out[633]:

In [634]:

#CHECK BEFORE ELIMINATING COLUMNS
df_hotel_coordinates1[['addresses_zip_code','secondary_filters_id',
          'addresses_town','register_id']].head(1)

Out[634]:

In [635]:

#DROP COLUMNS
df_hotel_coordinates1.drop(columns=['created','modified','institution_name','institution_id','_full_text',
                       'values_id','values_attribute_name','values_category','values_value',
                       'values_outstanding','values_attribute_id','values_description','_id',
                       'secondary_filters_asia_id','secondary_filters_fullpath',
                       'secondary_filters_tree','addresses_roadtype_name','addresses_main_address', 
                       'addresses_type', 'addresses_road_id', 'addresses_roadtype_id','geo_epgs_25831_x', 
                       'geo_epgs_25831_y','addresses_zip_code',
                       'secondary_filters_id','addresses_town','register_id'], inplace=True)
df_hotel_coordinates1.head(1)

Out[635]:

In [636]:

df_hotel_coordinates2 = df_hotel_coordinates1.copy()

In [637]:

#RENAME COLUMNS
df_hotel_coordinates2.columns = ['street_number_2_hotel','address_hotel','longitude_hotel','latitude_hotel','category_hotel','district_name_hotel','street_number_1_hotel',
                                 'district_code_hotel','neighbourhood_code_hotel','name_hotel','neighbourhood_name_hotel']
df_hotel_coordinates2.head(1)

Out[637]:

In [638]:

#FUNCTION TO EXTRACT ID COLUMN
def rtc_split_hotel(arg):
    if "HB-" in arg:
        return arg.split("HB-", 1) # 1 : to split at the first found only
    else:
        return [arg, None] # None : to add a Null value when split character not found and so preserve the same column length

In [639]:

#EXTRACT ID COLUMN RTC FROM NAME COLUMN
df_hotel_coordinates2[['name_hotel','rtc_hotel']] = [rtc_split_hotel(x) for x in df_hotel_coordinates2['name_hotel']]
df_hotel_coordinates2['rtc_hotel'] = 'HB-' + df_hotel_coordinates2['rtc_hotel']
df_hotel_coordinates2['rtc_hotel'] = df_hotel_coordinates2['rtc_hotel'].str.strip().replace(' ','')
df_hotel_coordinates2['name_hotel'] = df_hotel_coordinates2['name_hotel'].str.replace('-',' ').str.strip()
df_hotel_coordinates2.head(1)

Out[639]:

In [640]:

df_hotel_coordinates3 = df_hotel_coordinates2.copy()

WHITESPACES¶

In [641]:

#REMOVING SPACES
# .replace(' ','', regex=True) - replace all spaces with nothing
# .str.strip() - replace 1 initial and 1 trailing space only
# .replace(r's+',' ', regex=True) - replace multiple spaces with one single space
# .replace(r'^s+|s+$','',regex=True) - replace all + spaces s starting ^ and trailing $
# .replace('nan','', regex=True) - replace pre-existing 'nan' strings into empty cells - not to be used for string columns potentially containing nan as subpart of string
# .replace('.0','',regex=True) - replace .0 with nothing - '' is required to assign '.' as a normal character and not as a special one
df_hotel_coordinates3['rtc_hotel'] = df_hotel_coordinates3['rtc_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hotel_coordinates3['address_hotel'] = df_hotel_coordinates3['address_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_hotel_coordinates3['street_number_1_hotel'] = df_hotel_coordinates3['street_number_1_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hotel_coordinates3['street_number_2_hotel'] = df_hotel_coordinates3['street_number_2_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hotel_coordinates3['district_code_hotel'] = df_hotel_coordinates3['district_code_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True)
df_hotel_coordinates3['neighbourhood_code_hotel'] = df_hotel_coordinates3['neighbourhood_code_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True)
df_hotel_coordinates3['neighbourhood_name_hotel'] = df_hotel_coordinates3['neighbourhood_name_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_hotel_coordinates3['longitude_hotel'] = df_hotel_coordinates3['longitude_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hotel_coordinates3['latitude_hotel'] = df_hotel_coordinates3['latitude_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hotel_coordinates3['name_hotel'] = df_hotel_coordinates3['name_hotel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)

In [642]:

#DISTRICT AND NEIGHBOURHOOD CODES NEED TO BE IN STRING FORMAT AND REQUIRE AN ADDED '0' IN FRONT OF ALL NUMBERS BELOW 10
df_hotel_coordinates3[['district_code_hotel']] = df_hotel_coordinates3[['district_code_hotel']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])
df_hotel_coordinates3[['neighbourhood_code_hotel']] = df_hotel_coordinates3[['neighbourhood_code_hotel']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])

In [643]:

#REPLACE CELL THAT IS ENTIRELY SPACE OR EMPTY with None
df_hotel_coordinates3 = df_hotel_coordinates3.applymap(lambda x: None if isinstance(x, str) and (x=='' or x.isspace()) else x)

In [644]:

df_hotel_coordinates4 = df_hotel_coordinates3.copy()

DUPLICATES¶

In [645]:

#QUICK CHECK FOR DUPLICATES
df_hotel_coordinates4.duplicated().value_counts()

Out[645]:

False    441
dtype: int64

In [646]:

#VERIFY ID COLUMN: 'rtc_hotel'
df_hotel_coordinates4.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 441 entries, 0 to 440
Data columns (total 12 columns):
 #   Column                    Non-Null Count  Dtype 
---  ------                    --------------  ----- 
 0   street_number_2_hotel     13 non-null     object
 1   address_hotel             441 non-null    object
 2   longitude_hotel           441 non-null    object
 3   latitude_hotel            441 non-null    object
 4   category_hotel            441 non-null    object
 5   district_name_hotel       441 non-null    object
 6   street_number_1_hotel     439 non-null    object
 7   district_code_hotel       441 non-null    object
 8   neighbourhood_code_hotel  441 non-null    object
 9   name_hotel                441 non-null    object
 10  neighbourhood_name_hotel  441 non-null    object
 11  rtc_hotel                 440 non-null    object
dtypes: object(12)
memory usage: 41.5+ KB

In [647]:

#CHECK DUPLICATES ON ID COLUMN: 'rtc_hotel'
df_hotel_coordinates4[df_hotel_coordinates4.duplicated(subset='rtc_hotel', keep=False)]

Out[647]:

MISSING VALUES¶

In [648]:

df_hotel_coordinates4.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 441 entries, 0 to 440
Data columns (total 12 columns):
 #   Column                    Non-Null Count  Dtype 
---  ------                    --------------  ----- 
 0   street_number_2_hotel     13 non-null     object
 1   address_hotel             441 non-null    object
 2   longitude_hotel           441 non-null    object
 3   latitude_hotel            441 non-null    object
 4   category_hotel            441 non-null    object
 5   district_name_hotel       441 non-null    object
 6   street_number_1_hotel     439 non-null    object
 7   district_code_hotel       441 non-null    object
 8   neighbourhood_code_hotel  441 non-null    object
 9   name_hotel                441 non-null    object
 10  neighbourhood_name_hotel  441 non-null    object
 11  rtc_hotel                 440 non-null    object
dtypes: object(12)
memory usage: 41.5+ KB

RTC¶

In [649]:

#IDENTIFY NULL VALUES IN ID COLUMN: 'rtc_hotel'
df_hotel_coordinates4[(df_hotel_coordinates4['rtc_hotel'].isnull()) 
                      | (df_hotel_coordinates4['rtc_hotel']=='nan') 
                      |  (df_hotel_coordinates4['rtc_hotel']==None) 
                      |  (df_hotel_coordinates4['rtc_hotel']=='')]

Out[649]:

DISTRICT – NEIGHBOURHOOD¶

In [650]:

#IDENTIFY NULL VALUES
df_hotel_coordinates4[(df_hotel_coordinates4['neighbourhood_code_hotel'].isnull()) 
                      | (df_hotel_coordinates4['neighbourhood_code_hotel']=='nan') 
                      |  (df_hotel_coordinates4['neighbourhood_code_hotel']==None) 
                      |  (df_hotel_coordinates4['neighbourhood_code_hotel']=='')].shape[0]

Out[650]:

0

In [651]:

#IDENTIFY NULL VALUES
df_hotel_coordinates4[(df_hotel_coordinates4['neighbourhood_name_hotel'].isnull()) 
                      | (df_hotel_coordinates4['neighbourhood_name_hotel']=='nan') 
                      |  (df_hotel_coordinates4['neighbourhood_name_hotel']==None) 
                      |  (df_hotel_coordinates4['neighbourhood_name_hotel']=='')].shape[0]

Out[651]:

0

In [652]:

#CHECK HOW MANY NEIGHBOURHOOD CODES ARE NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_hotel_coordinates4[~df_hotel_coordinates4['neighbourhood_code_hotel'].isin(df_district_neighbourhood_table['Neighbourhood_Code'])].shape[0]

Out[652]:

0

In [653]:

#CHECK HOW MANY NEIGHBOURHOOD NAMES ARE NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_hotel_coordinates4[~df_hotel_coordinates4['neighbourhood_name_hotel'].isin(df_district_neighbourhood_table['Neighbourhood_Name'])].shape[0]

Out[653]:

0

In [654]:

#MAPPING/REPLACING REQUIRES NO NULL VALUES IN COLUMN LINKED TO set_index COLUMN
df_hotel_coordinates4['neighbourhood_name_hotel'] = df_hotel_coordinates4['neighbourhood_code_hotel'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['Neighbourhood_Name'])
df_hotel_coordinates4['district_code_hotel'] = df_hotel_coordinates4['neighbourhood_code_hotel'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Code'])
df_hotel_coordinates4['district_name_hotel'] = df_hotel_coordinates4['neighbourhood_code_hotel'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Name'])

In [655]:

#IDENTIFY NULL VALUES
df_hotel_coordinates4[(df_hotel_coordinates4['neighbourhood_code_hotel'].isnull()) 
                      | (df_hotel_coordinates4['neighbourhood_code_hotel']=='nan') 
                      |  (df_hotel_coordinates4['neighbourhood_code_hotel']==None) 
                      |  (df_hotel_coordinates4['neighbourhood_code_hotel']=='')].shape[0]

Out[655]:

0

In [656]:

#CHECK HOW MANY NEIGHBOURHOOD CODES ARE NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_hotel_coordinates4[~df_hotel_coordinates4['neighbourhood_code_hotel'].isin(df_district_neighbourhood_table['Neighbourhood_Code'])].shape[0]

Out[656]:

0

In [657]:

df_hotel_coordinates4.head(1)

Out[657]:

In [658]:

df_hotel_coordinates5 = df_hotel_coordinates4.copy()

NORMALIZATION¶

In [659]:

#CHECK 'street_number_1'
df_hotel_coordinates5[df_hotel_coordinates5['street_number_1_hotel'].str.isdecimal()==False].value_counts('street_number_1_hotel') 

Out[659]:

Series([], dtype: int64)

In [660]:

#CHECK 'street_number_2'
df_hotel_coordinates5[df_hotel_coordinates5['street_number_2_hotel'].str.isdecimal()==False].value_counts('street_number_2_hotel')

Out[660]:

Series([], dtype: int64)

In [661]:

df_hotel_coordinates5['category_hotel'].value_counts()

Out[661]:

Hotels 4 estr.    187
Hotels 3 estr.    121
Hotels 2 estr.     50
Hotels 5 estr.     44
Hotels 1 estr.     39
Name: category_hotel, dtype: int64

In [662]:

df_hotel = df_hotel_coordinates5.copy()

In [663]:

df_hotel.head(1)

Out[663]:

DATAFRAME: COORDINATES FOR HOSTELS¶

SOURCE:

https://opendata-ajuntament.barcelona.cat/data/en/dataset/allotjaments-pensions

In [664]:

url_hostel = 'https://www.bcn.cat/tercerlloc/files/allotjament/opendatabcn_allotjament_pensions-js.json'
response_hostel = requests.get(url_hostel)
if response_hostel.ok:
    data_hostel = response_hostel.json()
else:
    print('Problem with: ', url_hostel)
df_hostel_coordinates = pd.DataFrame.from_dict(data_hostel)

In [665]:

df_hostel_coordinates1 = df_hostel_coordinates.copy()

EXTRACT RELEVANT DATA¶

In [666]:

df_hostel_coordinates1.columns

Out[666]:

Index(['register_id', 'prefix', 'suffix', 'name', 'created', 'modified',
       'status', 'status_name', 'core_type', 'core_type_name', 'body',
       'tickets_data', 'addresses', 'entity_types_data',
       'attribute_categories', 'values', 'from_relationships',
       'to_relationships', 'classifications_data', 'secondary_filters_data',
       'timetable', 'image_data', 'gallery_data', 'warnings', 'geo_epgs_25831',
       'geo_epgs_23031', 'geo_epgs_4326', 'is_section_of_data',
       'sections_data', 'start_date', 'end_date', 'estimated_dates',
       'languages_data', 'type', 'type_name', 'period', 'period_name',
       'event_status_name', 'event_status', 'ical'],
      dtype='object')

In [667]:

#CHECK BEFORE DROPPING
df_hostel_coordinates1[['register_id', 'prefix', 'suffix', 'created', 'modified', 'status', 
           'status_name', 'core_type', 'core_type_name', 'body', 'tickets_data', 
           'from_relationships', 'to_relationships', 'timetable', 'image_data']].head(1)

Out[667]:

In [668]:

#CHECK BEFORE DROPPING
df_hostel_coordinates1[['gallery_data', 'warnings', 'geo_epgs_25831', 'geo_epgs_23031', 
           'is_section_of_data', 'sections_data', 'start_date', 'end_date', 
           'estimated_dates', 'languages_data', 'type', 'type_name', 'period', 
           'period_name', 'event_status_name', 'event_status', 'ical']].head(1)

Out[668]:

In [669]:

#CHECK BEFORE DROPPING
df_hostel_coordinates1[['secondary_filters_data','entity_types_data','attribute_categories','values']].head(1)

Out[669]:

In [670]:

#DROP COLUMNS
df_hostel_coordinates1.drop(columns=['register_id', 'prefix', 'suffix', 'created', 'modified', 
                        'status', 'status_name', 'core_type', 'core_type_name', 'body', 
                        'tickets_data', 'from_relationships', 'to_relationships', 'timetable', 
                        'image_data', 'gallery_data', 'warnings', 'geo_epgs_25831',
                        'geo_epgs_23031', 'is_section_of_data', 'sections_data', 'start_date', 
                        'end_date', 'estimated_dates','languages_data', 'type', 'type_name', 
                        'period', 'period_name','event_status_name', 'event_status', 'ical',
                        'secondary_filters_data','entity_types_data','attribute_categories','values'], inplace=True)
df_hostel_coordinates1.head(1)

Out[670]:

In [671]:

df_hostel_coordinates2 = df_hostel_coordinates1.copy()

In [672]:

#FUNCTION TO SPLIT COORDINATES
def split_coordinates(arg):
    if "," in arg:
        return arg.split(",",1) # 1 : to split at the first found only
    else:
        return (arg, None) # None : to add a Null value when split character not found and so preserve the same column length

In [673]:

#SPLIT AND RENAME COLUMNS
df_hostel_coordinates2['geo_epgs_4326'] = df_hostel_coordinates2['geo_epgs_4326'].astype(str)
df_hostel_coordinates2[['latitude_hostel','longitude_hostel']] = [split_coordinates(x) for x in df_hostel_coordinates2['geo_epgs_4326']]
df_hostel_coordinates2['latitude_hostel'] = df_hostel_coordinates2['latitude_hostel'].str.replace("{'x': ",'',regex=True)
df_hostel_coordinates2['longitude_hostel'] = df_hostel_coordinates2['longitude_hostel'].str.replace("'y': ",'',regex=True).replace('}','', regex=True)
df_hostel_coordinates2.drop(columns=['geo_epgs_4326'],inplace=True)
df_hostel_coordinates2.head(1)

Out[673]:

In [674]:

df_hostel_coordinates2.loc[0,'classifications_data']

Out[674]:

[{'id': 1003005,
  'name': 'Pensions, hostals',
  'full_path': 'Tipologia EQ >> Allotjament >> Pensions, hostals',
  'dependency_group': 3033964,
  'parent_id': 1003,
  'tree_id': 1,
  'asia_id': '0000102003005',
  'core_type': 'place',
  'level': 2},
 {'id': 108215,
  'name': 'Gay Friendly ',
  'full_path': 'Col·lectius EQ >> Gay Friendly ',
  'dependency_group': 3033964,
  'parent_id': 108,
  'tree_id': 108,
  'asia_id': '0010801215',
  'core_type': 'place',
  'level': 1}]

In [675]:

classifications_data  = []
for i in df_hostel_coordinates2['classifications_data']:
        c = list([x.get('name') for x in i])[0] #to get first item from dictionary with 'name' as key
        classifications_data.append(c)
df_hostel_coordinates2['category_hostel'] = classifications_data
df_hostel_coordinates2.drop(columns='classifications_data', inplace=True)
df_hostel_coordinates2.head(1)

Out[675]:

In [676]:

df_hostel_coordinates2.loc[0,'addresses']

Out[676]:

[{'place': None,
  'district_name': 'Eixample',
  'district_id': '02',
  'neighborhood_name': "la Dreta de l'Eixample",
  'neighborhood_id': '07',
  'address_name': 'C Diputació',
  'address_id': '100800',
  'block_id': None,
  'start_street_number': 346,
  'end_street_number': None,
  'street_number_1': '346',
  'street_number_2': None,
  'stairs': None,
  'level': '1r',
  'door': '1a',
  'zip_code': '08013',
  'province': 'BARCELONA',
  'town': 'BARCELONA',
  'country': 'ESPANYA',
  'comments': None,
  'position': 0,
  'main_address': True,
  'road_name': None,
  'road_id': None,
  'roadtype_name': None,
  'roadtype_id': None,
  'location': {'type': 'GeometryCollection',
   'geometries': [{'type': 'Point',
     'coordinates': [431059.3968618301, 4583099.462077796]}]},
  'related_entity': None,
  'related_entity_data': None,
  'hide_address': False}]

In [677]:

district_name = []
district_id = []
neighborhood_name = []
neighborhood_id = []
address_name  = []
street_number_1 = []
street_number_2 = []
for i in df_hostel_coordinates2['addresses']:
        dn = list([x.get('district_name') for x in i])[0]
        dc = list([x.get('district_id') for x in i])[0]
        nn = list([x.get('neighborhood_name') for x in i])[0]
        nc = list([x.get('neighborhood_id') for x in i])[0]
        an = list([x.get('address_name') for x in i])[0]
        sn1 = list([x.get('street_number_1') for x in i])[0]
        sn2 = list([x.get('street_number_2') for x in i])[0]
        district_name.append(dn)
        district_id.append(dc)
        neighborhood_name.append(nn)
        neighborhood_id.append(nc)
        address_name.append(an)
        street_number_1.append(sn1)
        street_number_2.append(sn2)
df_hostel_coordinates2['district_code_hostel'] = district_id
df_hostel_coordinates2['district_name_hostel'] = district_name
df_hostel_coordinates2['neighbourhood_code_hostel'] = neighborhood_id
df_hostel_coordinates2['neighbourhood_name_hostel'] = neighborhood_name
df_hostel_coordinates2['address_hostel'] = address_name
df_hostel_coordinates2['street_number_1_hostel'] = street_number_1
df_hostel_coordinates2['street_number_2_hostel'] = street_number_2
df_hostel_coordinates2.drop(columns='addresses', inplace=True)
df_hostel_coordinates2.head(1)

Out[677]:

In [678]:

df_hostel_coordinates3 = df_hostel_coordinates2.copy()

In [679]:

#FUNCTION TO EXTRACT ID COLUMN
def rtc_split_hostel(arg):
    if "HB-" in arg:
        return arg.split("HB-", 1) # 1 : to split at the first found only
    else:
        return [arg, None] # None : to add a Null value when split character not found and so preserve the same column length

In [680]:

df_hostel_coordinates3[['name','rtc_hostel']] = [rtc_split_hostel(x) for x in df_hostel_coordinates3['name']]
df_hostel_coordinates3['rtc_hostel'] = 'HB-' + df_hostel_coordinates3['rtc_hostel']
df_hostel_coordinates3['rtc_hostel'] = df_hostel_coordinates3['rtc_hostel'].str.strip().replace(' ','')
df_hostel_coordinates3['name_hostel'] = df_hostel_coordinates3['name'].str.replace('-',' ').str.strip()
df_hostel_coordinates3.drop(columns='name', inplace=True)
df_hostel_coordinates3.head(1)

Out[680]:

In [681]:

df_hostel_coordinates4 = df_hostel_coordinates3.copy()

WHITESPACES¶

In [682]:

#REMOVING SPACES
# .replace(' ','', regex=True) - replace all spaces with nothing
# .str.strip() - replace 1 initial and 1 trailing space only
# .replace(r's+',' ', regex=True) - replace multiple spaces with one single space
# .replace(r'^s+|s+$','',regex=True) - replace all + spaces s starting ^ and trailing $
# .replace('nan','', regex=True) - replace pre-existing 'nan' strings into empty cells - not to be used for string columns potentially containing nan as subpart of string
# .replace('.0','',regex=True) - replace .0 with nothing - '' is required to assign '.' as a normal character and not as a special one
df_hostel_coordinates4['rtc_hostel'] = df_hostel_coordinates4['rtc_hostel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hostel_coordinates4['address_hostel'] = df_hostel_coordinates4['address_hostel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_hostel_coordinates4['street_number_1_hostel'] = df_hostel_coordinates4['street_number_1_hostel'].astype(str).replace(r'','',regex=True).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hostel_coordinates4['street_number_2_hostel'] = df_hostel_coordinates4['street_number_2_hostel'].astype(str).replace(r'','',regex=True).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hostel_coordinates4['district_code_hostel'] = df_hostel_coordinates4['district_code_hostel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True)
df_hostel_coordinates4['neighbourhood_code_hostel'] = df_hostel_coordinates4['neighbourhood_code_hostel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True)
df_hostel_coordinates4['neighbourhood_name_hostel'] = df_hostel_coordinates4['neighbourhood_name_hostel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_hostel_coordinates4['longitude_hostel'] = df_hostel_coordinates4['longitude_hostel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hostel_coordinates4['latitude_hostel'] = df_hostel_coordinates4['latitude_hostel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_hostel_coordinates4['name_hostel'] = df_hostel_coordinates4['name_hostel'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)

In [683]:

#DISTRICT AND NEIGHBOURHOOD CODES NEED TO BE IN STRING FORMAT AND REQUIRE AN ADDED '0' IN FRONT OF ALL NUMBERS BELOW 10
df_hostel_coordinates4[['district_code_hostel']] = df_hostel_coordinates4[['district_code_hostel']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])
df_hostel_coordinates4[['neighbourhood_code_hostel']] = df_hostel_coordinates4[['neighbourhood_code_hostel']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])

In [684]:

#REPLACE CELL THAT IS ENTIRELY SPACE OR EMPTY with None
df_hostel_coordinates4 = df_hostel_coordinates4.applymap(lambda x: None if isinstance(x, str) and (not x or x.isspace()) else x)

In [685]:

df_hostel_coordinates5 = df_hostel_coordinates4.copy()

DUPLICATES¶

In [686]:

df_hostel_coordinates5.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 244 entries, 0 to 243
Data columns (total 12 columns):
 #   Column                     Non-Null Count  Dtype 
---  ------                     --------------  ----- 
 0   latitude_hostel            244 non-null    object
 1   longitude_hostel           244 non-null    object
 2   category_hostel            244 non-null    object
 3   district_code_hostel       244 non-null    object
 4   district_name_hostel       244 non-null    object
 5   neighbourhood_code_hostel  244 non-null    object
 6   neighbourhood_name_hostel  244 non-null    object
 7   address_hostel             244 non-null    object
 8   street_number_1_hostel     244 non-null    object
 9   street_number_2_hostel     0 non-null      object
 10  rtc_hostel                 242 non-null    object
 11  name_hostel                244 non-null    object
dtypes: object(12)
memory usage: 23.0+ KB

In [687]:

df_hostel_coordinates5.duplicated().value_counts()

Out[687]:

False    243
True       1
dtype: int64

In [688]:

df_hostel_coordinates5[df_hostel_coordinates5.duplicated(keep=False)]

Out[688]:

In [689]:

df_hostel_coordinates5 = df_hostel_coordinates5.drop_duplicates()

In [690]:

#CHECK DUPLICATES ON ID COLUMN
df_hostel_coordinates5[df_hostel_coordinates5.duplicated(subset='rtc_hostel', keep=False)]

Out[690]:

In [691]:

df_hostel_coordinates6 = df_hostel_coordinates5.copy()

MISSING VALUES¶

In [692]:

df_hostel_coordinates6.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 243 entries, 0 to 243
Data columns (total 12 columns):
 #   Column                     Non-Null Count  Dtype 
---  ------                     --------------  ----- 
 0   latitude_hostel            243 non-null    object
 1   longitude_hostel           243 non-null    object
 2   category_hostel            243 non-null    object
 3   district_code_hostel       243 non-null    object
 4   district_name_hostel       243 non-null    object
 5   neighbourhood_code_hostel  243 non-null    object
 6   neighbourhood_name_hostel  243 non-null    object
 7   address_hostel             243 non-null    object
 8   street_number_1_hostel     243 non-null    object
 9   street_number_2_hostel     0 non-null      object
 10  rtc_hostel                 241 non-null    object
 11  name_hostel                243 non-null    object
dtypes: object(12)
memory usage: 24.7+ KB

RTC¶

In [693]:

df_hostel_coordinates6[df_hostel_coordinates6['rtc_hostel'].isnull()]

Out[693]:

DISTRICT – NEIGHBOURHOOD¶

In [694]:

#IDENTIFY NULL VALUES
df_hostel_coordinates6[(df_hostel_coordinates6['neighbourhood_code_hostel'].isnull()) 
                       | (df_hostel_coordinates6['neighbourhood_code_hostel']=='nan') 
                       |  (df_hostel_coordinates6['neighbourhood_code_hostel']==None) 
                       |  (df_hostel_coordinates6['neighbourhood_code_hostel']=='')].shape[0]

Out[694]:

0

In [695]:

#IDENTIFY NULL VALUES
df_hostel_coordinates6[(df_hostel_coordinates6['neighbourhood_name_hostel'].isnull()) 
                       | (df_hostel_coordinates6['neighbourhood_name_hostel']=='nan') 
                       |  (df_hostel_coordinates6['neighbourhood_name_hostel']==None) 
                       |  (df_hostel_coordinates6['neighbourhood_name_hostel']=='')].shape[0]

Out[695]:

0

In [696]:

#CHECK HOW MANY NEIGHBOURHOOD NAMES ARE NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_hostel_coordinates6[~df_hostel_coordinates6['neighbourhood_name_hostel'].isin(df_district_neighbourhood_table['Neighbourhood_Name'])].shape[0]

Out[696]:

61

In [697]:

#CHECK HOW MANY NEIGHBOURHOOD CODES ARE NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_hostel_coordinates6[~df_hostel_coordinates6['neighbourhood_code_hostel'].isin(df_district_neighbourhood_table['Neighbourhood_Code'])].shape[0]

Out[697]:

0

In [698]:

#MAPPING/REPLACING REQUIRES NO NULL VALUES IN COLUMN LINKED TO set_index COLUMN
df_hostel_coordinates6['neighbourhood_name_hostel'] = df_hostel_coordinates6['neighbourhood_code_hostel'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['Neighbourhood_Name'])
df_hostel_coordinates6['district_code_hostel'] = df_hostel_coordinates6['neighbourhood_code_hostel'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Code'])
df_hostel_coordinates6['district_name_hostel'] = df_hostel_coordinates6['neighbourhood_code_hostel'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Name'])

In [699]:

#CHECK HOW MANY NEIGHBOURHOOD NAMES ARE NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_hostel_coordinates6[~df_hostel_coordinates6['neighbourhood_name_hostel'].isin(df_district_neighbourhood_table['Neighbourhood_Name'])].shape[0]

Out[699]:

0

NORMALIZATION¶

In [700]:

df_hostel_coordinates7 = df_hostel_coordinates6.copy()

In [701]:

#CHECK 'street_number_1'
df_hostel_coordinates7[df_hostel_coordinates7['street_number_1_hostel'].str.isdecimal()==False].value_counts('street_number_1_hostel') 

Out[701]:

street_number_1_hostel
1*3        4
116*LB     1
11C        1
149*155    1
16*18      1
2*4        1
3*5        1
32*34      1
373*377    1
433*LB     1
56*58      1
77*79      1
8*10       1
83*85      1
95*97      1
98*100     1
dtype: int64

In [702]:

#CHECK 'street_number_2'
df_hostel_coordinates7[df_hostel_coordinates7['street_number_2_hostel'].str.isdecimal()==False].value_counts('street_number_2_hostel')

Out[702]:

Series([], dtype: int64)

In [703]:

def split_street_number_1_2 (df_target,column_address_1,column_address_2):
    df = df_target.loc[df_target[column_address_1].str.isdecimal()==False,column_address_1]
    df = df.str.split(pat='(d+)', expand=True)
    df_target.loc[df_target[column_address_1].str.isdecimal()==False, column_address_2] = df.iloc[:,3] #column_address_2 needs to precede column_address_1 
    df_target.loc[df_target[column_address_1].str.isdecimal()==False, column_address_1] = df.iloc[:,1] #the condition on which .loc is based is lost
    return print('Split Values:'), df

In [704]:

split_street_number_1_2(df_hostel_coordinates7,'street_number_1_hostel','street_number_2_hostel')

Split Values:

Out[704]:

(None,
     0    1    2     3     4
 14       1    *     3      
 25     433  *LB  None  None
 38       1    *     3      
 50      16    *    18      
 52       3    *     5      
 63       2    *     4      
 84     116  *LB  None  None
 99      98    *   100      
 110      8    *    10      
 124     11    C  None  None
 141     77    *    79      
 157     32    *    34      
 158    149    *   155      
 198     95    *    97      
 201     56    *    58      
 207      1    *     3      
 208      1    *     3      
 225     83    *    85      
 239    373    *   377      )

In [705]:

#CHECK AGAIN 'street_number_1'
df_hostel_coordinates7['street_number_1_hostel'] = df_hostel_coordinates7['street_number_1_hostel'].astype(str).str.strip()
df_hostel_coordinates7[df_hostel_coordinates7['street_number_1_hostel'].str.isdecimal()==False].value_counts('street_number_1_hostel') 

Out[705]:

Series([], dtype: int64)

In [706]:

#CHECK AGAIN 'street_number_2'
df_hostel_coordinates7['street_number_2_hostel'] = df_hostel_coordinates7['street_number_2_hostel'].astype(str).str.strip()
df_hostel_coordinates7[df_hostel_coordinates7['street_number_2_hostel'].str.isdecimal()==False].value_counts('street_number_2_hostel')

Out[706]:

street_number_2_hostel
None    227
dtype: int64

In [707]:

df_hostel = df_hostel_coordinates7.copy()

DATAFRAME: COORDINATES FOR OTHER ESTABLISHMENTS¶

SOURCE:

https://opendata-ajuntament.barcelona.cat/data/en/dataset/allotjaments-altres

In [708]:

url_oe = 'https://www.bcn.cat/tercerlloc/files/allotjament/opendatabcn_allotjament_altres-allotjaments-js.json'
response_oe = requests.get(url_oe)
if response_oe.ok:
    data_oe = response_oe.json()
else:
    print('Problem with: ', url_oe)
df_other_establishments = pd.DataFrame.from_dict(data_oe)

In [709]:

df_other_establishments1 = df_other_establishments.copy()

EXTRACT RELEVANT DATA¶

In [710]:

#DROP UNNECESSARY COLUMNS
df_other_establishments1.columns

Out[710]:

Index(['register_id', 'prefix', 'suffix', 'name', 'created', 'modified',
       'status', 'status_name', 'core_type', 'core_type_name', 'body',
       'tickets_data', 'addresses', 'entity_types_data',
       'attribute_categories', 'values', 'from_relationships',
       'to_relationships', 'classifications_data', 'secondary_filters_data',
       'timetable', 'image_data', 'gallery_data', 'warnings', 'geo_epgs_25831',
       'geo_epgs_23031', 'geo_epgs_4326', 'is_section_of_data',
       'sections_data', 'start_date', 'end_date', 'estimated_dates',
       'languages_data', 'type', 'type_name', 'period', 'period_name',
       'event_status_name', 'event_status', 'ical'],
      dtype='object')

In [711]:

#CHECK COLUMNS TO DROP
df_other_establishments1[['register_id','prefix', 'suffix', 'created', 'modified','status', 'status_name', 'core_type', 
       'core_type_name', 'body','tickets_data','entity_types_data', 'attribute_categories', 'values']].head(1)

Out[711]:

In [712]:

#CHECK COLUMNS TO DROP
df_other_establishments1[['from_relationships', 'to_relationships', 'timetable', 'image_data', 'gallery_data', 'warnings',
       'geo_epgs_25831', 'geo_epgs_23031', 'is_section_of_data', 'sections_data', 
       'start_date', 'end_date', 'estimated_dates']].head(1)

Out[712]:

In [713]:

#CHECK COLUMNS TO DROP
df_other_establishments1[[ 'secondary_filters_data','languages_data', 'type','type_name', 'period', 
       'period_name','event_status_name', 'event_status', 'ical']].head(1)

Out[713]:

In [714]:

#DROP COLUMNS
df_other_establishments1.drop(columns=['register_id','prefix', 'suffix', 'created', 'modified', 'status', 'status_name', 
                    'core_type', 'core_type_name', 'body', 'tickets_data', 'from_relationships',
                    'to_relationships','timetable', 'image_data', 'gallery_data', 'warnings',
                    'geo_epgs_25831', 'geo_epgs_23031', 'is_section_of_data','sections_data', 
                    'start_date', 'end_date', 'estimated_dates', 'languages_data', 'type', 
                    'type_name', 'period', 'period_name', 'event_status_name', 'event_status',
                    'ical','values', 'entity_types_data','attribute_categories','secondary_filters_data'], inplace=True)
df_other_establishments1.head(1)

Out[714]:

In [715]:

df_other_establishments2 = df_other_establishments1.copy()

In [716]:

#FUNCTION TO SPLIT THE COORDINATE COLUMNS INTO TWO SEPARATE COLUMNS
def split_coordinates(arg):
    if "," in arg:
        return arg.split(",",1) # 1 : to split at the first found only
    else:
        return (arg, None) # None : to add a Null value when split character not found and so preserve the same column length

In [717]:

df_other_establishments2['geo_epgs_4326'] = df_other_establishments2['geo_epgs_4326'].astype(str)
df_other_establishments2[['latitude_oe','longitude_oe']] = [split_coordinates(x) for x in df_other_establishments2['geo_epgs_4326']]
df_other_establishments2['latitude_oe'] = df_other_establishments2['latitude_oe'].str.replace("{'x': ",'',regex=True)
df_other_establishments2['longitude_oe'] = df_other_establishments2['longitude_oe'].str.replace("'y': ",'',regex=True).replace('}','', regex=True)
df_other_establishments2.drop(columns=['geo_epgs_4326'],inplace=True)
df_other_establishments2.head(1)

Out[717]:

In [718]:

#EXTRACT A CLASSIFICATION COLUMNS FROM THE CLASSIFICATION_DATA COLUMN - THE COLUMN NEEDS TO BE IN OBJECT TYPE FOR THE EXTRACTION TO WORK
df_other_establishments2.loc[0,'classifications_data']

Out[718]:

[{'id': 1003011,
  'name': 'Apartaments turÃxadstics',
  'full_path': 'Tipologia EQ >> Allotjament >> Apartaments turÃxadstics',
  'dependency_group': 3033964,
  'parent_id': 1003,
  'tree_id': 1,
  'asia_id': '0000102003011',
  'core_type': 'place',
  'level': 2},
 {'id': 28793722,
  'name': '3 estrelles',
  'full_path': 'Categories >> Estrelles >> 3 estrelles',
  'dependency_group': 3033964,
  'parent_id': 103001,
  'tree_id': 103,
  'asia_id': '0010302001003',
  'core_type': 'place',
  'level': 2},
 {'id': 105001,
  'name': 'Accessible per a persones amb discapacitat fÃxadsica',
  'full_path': 'Accessibilitat >> Accessible per a persones amb discapacitat fÃxadsica',
  'dependency_group': 3033964,
  'parent_id': 105,
  'tree_id': 105,
  'asia_id': '0010501001',
  'core_type': 'place',
  'level': 1},
 {'id': 72314191,
  'name': 'Hospedaje en aparta-hoteles',
  'full_path': 'Arbre Principal Barcelona Activa  >> Industria, comerc i serveis >> Comercio, rest.y hospedajes reparaciones >> Servicio de hospedaje >> Hospedaje en aparta-hoteles',
  'dependency_group': 2206253,
  'parent_id': 27751630,
  'tree_id': 129,
  'asia_id': '0012904000006008004',
  'core_type': 'event',
  'level': 4},
 {'id': 68601655,
  'name': 'Guardia y custodia vehiculos en parkings',
  'full_path': 'Arbre Principal Barcelona Activa  >> Industria, comerc i serveis >> Transporte y comunicaciones >> Actividades anexas a los transportes >> Actividades anexas transporte terrestre >> Guardia y custodia vehiculos en parkings',
  'dependency_group': 2206253,
  'parent_id': 73311188,
  'tree_id': 129,
  'asia_id': '0012905000007005001002',
  'core_type': 'event',
  'level': 5}]

In [719]:

classifications_data  = []
for i in df_other_establishments2['classifications_data']:
        c = list([x.get('name') for x in i])[0] #to get first item from dictionary with 'name' as key
        classifications_data.append(c)
df_other_establishments2['category_oe'] = classifications_data
df_other_establishments2.drop(columns=['classifications_data'], inplace=True)
df_other_establishments2.head(1)

Out[719]:

In [720]:

df_other_establishments2.loc[0,'addresses']

Out[720]:

[{'place': None,
  'district_name': 'Eixample',
  'district_id': '02',
  'neighborhood_name': "la Nova Esquerra de l'Eixample",
  'neighborhood_id': '09',
  'address_name': 'C CalÃxa0bria',
  'address_id': '054509',
  'block_id': None,
  'start_street_number': 129,
  'end_street_number': None,
  'street_number_1': '129',
  'street_number_2': None,
  'stairs': None,
  'level': None,
  'door': None,
  'zip_code': '08015',
  'province': 'BARCELONA',
  'town': 'BARCELONA',
  'country': 'ESPANYA',
  'comments': None,
  'position': 0,
  'main_address': True,
  'road_name': None,
  'road_id': None,
  'roadtype_name': None,
  'roadtype_id': None,
  'location': {'type': 'GeometryCollection',
   'geometries': [{'type': 'Point',
     'coordinates': [429195.2433352358, 4581395.340320568]}]},
  'related_entity': None,
  'related_entity_data': None,
  'hide_address': False}]

In [721]:

district_name = []
district_id = []
neighborhood_name = []
neighborhood_id = []
address_name  = []
street_number_1 = []
street_number_2 = []
for i in df_other_establishments2['addresses']:
        dn = list([x.get('district_name') for x in i])[0]
        dc = list([x.get('district_id') for x in i])[0]
        nn = list([x.get('neighborhood_name') for x in i])[0]
        nc = list([x.get('neighborhood_id') for x in i])[0]
        an = list([x.get('address_name') for x in i])[0]
        sn1 = list([x.get('street_number_1') for x in i])[0]
        sn2 = list([x.get('street_number_2') for x in i])[0]
        district_name.append(dn)
        district_id.append(dc)
        neighborhood_name.append(nn)
        neighborhood_id.append(nc)
        address_name.append(an)
        street_number_1.append(sn1)
        street_number_2.append(sn2)
df_other_establishments2['district_code_oe'] = district_id
df_other_establishments2['district_name_oe'] = district_name
df_other_establishments2['neighbourhood_code_oe'] = neighborhood_id
df_other_establishments2['neighbourhood_name_oe'] = neighborhood_name
df_other_establishments2['address_name_oe'] = address_name
df_other_establishments2['street_number_1_oe'] = street_number_1
df_other_establishments2['street_number_2_oe'] = street_number_2
df_other_establishments2.drop(columns='addresses', inplace=True)
df_other_establishments2.head(1)

Out[721]:

In [722]:

df_other_establishments2.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 206 entries, 0 to 205
Data columns (total 11 columns):
 #   Column                 Non-Null Count  Dtype 
---  ------                 --------------  ----- 
 0   name                   206 non-null    object
 1   latitude_oe            206 non-null    object
 2   longitude_oe           206 non-null    object
 3   category_oe            206 non-null    object
 4   district_code_oe       206 non-null    object
 5   district_name_oe       206 non-null    object
 6   neighbourhood_code_oe  206 non-null    object
 7   neighbourhood_name_oe  206 non-null    object
 8   address_name_oe        206 non-null    object
 9   street_number_1_oe     206 non-null    object
 10  street_number_2_oe     0 non-null      object
dtypes: object(11)
memory usage: 17.8+ KB

In [723]:

df_other_establishments2['category_oe'].value_counts()

Out[723]:

Albergs                   121
Residències               61
Apartaments turístics     12
Col·legis majors          11
Empreses de serveis         1
Name: category_oe, dtype: int64

In [724]:

df_other_establishments3 = df_other_establishments2.copy()

In [725]:

#RTC ID CODE IS INCLUDED IN MOST NAMES, ADDED AT THE END FOLLOWING A "-" AS SEPARATING CHARACTER
df_other_establishments3['name']

Out[725]:

0      Apartament Turístic Atenea Calabria - ATB-000001
1                 Col.legi  Major Penyafort - Montserrat
2                                Col.legi Major Bonaigua
3            Residència Universitària Mare Anna Ravell
4      Centre d’Acollida del Baix Guinardó per a l...
                             ...                        
201                 Residència Salesiana Martí Codolar
202                 Residència d'Estudiants Mare Güell
203                          Residència Erasmus Gràcia
204         Residència d'Estudiants LIV Student Sarrià
205            Residència Universitària Josep Manyanet
Name: name, Length: 206, dtype: object

In [726]:

#3 RESIDENCIES, 1 COLLEGIS MAJOR AND EMPRESES DE SERVEIS APPEAR TO HAVE THE SEPARATING CHARACTER
df_other_establishments3[['name','category_oe']][(df_other_establishments3['name'].str.contains(' - ')) 
                                                    & (df_other_establishments3['category_oe'] != 'Albergs') 
                                                    & (df_other_establishments3['category_oe'] != 'Apartaments turístics')]

Out[726]:

In [727]:

#HOWEVER, LIKE THE OTHERS, IT IS NOT FOLLOWED BY AN RTC CODE
df_other_establishments3[['name','category_oe']][(df_other_establishments3['category_oe'] != 'Albergs') 
                                                    & (df_other_establishments3['category_oe'] != 'Apartaments turístics')]

Out[727]:

73 rows × 2 columns

In [728]:

#AMONG ALBERGS AND APARTMENTS TURISTICS - ONLY 1 APARTMENTS TURISTICS APPEARS TO BE MISSING THE SEPARATING CHARACTER
df_other_establishments3[['name','category_oe']][(~df_other_establishments3['name'].str.contains(' - ')) 
                                                    & ((df_other_establishments3['category_oe'] == 'Albergs') 
                                                    | (df_other_establishments3['category_oe'] == 'Apartaments turístics'))].value_counts('category_oe')

Out[728]:

category_oe
Apartaments turístics    1
dtype: int64

In [729]:

#IN THIS CASE, THE SEPARATING CHARACTER IS FOLLOWED BY AN RTC CODE
df_other_establishments3[['name','category_oe']][(~df_other_establishments3['name'].str.contains(' - ')) 
                                                    & ((df_other_establishments3['category_oe'] == 'Albergs') 
                                                    | (df_other_establishments3['category_oe'] == 'Apartaments turístics'))]

Out[729]:

In [730]:

#ALBERGS AND APARTMENTS TURISTIC ALL HAVE A "-" SEPARATING CHARACTER, FOLLOWED BY AN RTC CODE - HOWEVER TO AVOID SPLITTING RECORDS IN OTHER CATEGORIES, IT IS BEST TO USE "- A" AS SPLITTING CHARACTER AND THEN REINSTATE THE A
df_other_establishments3[['name','category_oe']][(df_other_establishments3['name'].str.contains(' - ')) 
                                                    & ((df_other_establishments3['category_oe'] == 'Albergs') 
                                                    | (df_other_establishments3['category_oe'] == 'Apartaments turístics'))].sort_values('category_oe')

Out[730]:

132 rows × 2 columns

In [731]:

df_other_establishments4 = df_other_establishments3.copy()

In [732]:

#FUNCTION TO EXTRACT ID COLUMN
def rtc_split_oe(arg):
    if "- A" in arg:
        return arg.split("- A", 1) # 1 : to split at the first found only
    else:
        return [arg, None] # None : to add a Null value when split character not found and so preserve the same column length

In [733]:

df_other_establishments4['name'] = df_other_establishments4['name'].astype(str)
df_other_establishments4[['name_oe','rtc_oe']] = [rtc_split_oe(x) for x in df_other_establishments4['name']]
df_other_establishments4['name_oe'] = df_other_establishments4['name_oe'].str.strip()
df_other_establishments4['rtc_oe'] = "A" + df_other_establishments4['rtc_oe'] #REINSTATE THE 'A'
df_other_establishments4['rtc_oe'] = df_other_establishments4['rtc_oe'].str.strip()
df_other_establishments4.drop(columns=['name'],inplace=True)
df_other_establishments4.head(1)

Out[733]:

In [734]:

#COMPARING THE INITIAL RESULTS
df_other_establishments4['category_oe'].value_counts()

Out[734]:

Albergs                   121
Residències               61
Apartaments turístics     12
Col·legis majors          11
Empreses de serveis         1
Name: category_oe, dtype: int64

In [735]:

#NOW THE RTC IS NOT PRESENT IN THESE CATEGORIES
df_other_establishments4['category_oe'][df_other_establishments4['rtc_oe'].isnull()].value_counts()

Out[735]:

Residències           61
Col·legis majors      11
Empreses de serveis     1
Name: category_oe, dtype: int64

In [736]:

#AND IT IS PRESENT IN THESE CATEGORIES
df_other_establishments4['category_oe'][df_other_establishments4['rtc_oe'].notnull()].value_counts()

Out[736]:

Albergs                   121
Apartaments turístics     12
Name: category_oe, dtype: int64

THE FOCUS OF THIS ANALYSIS IS ON ESTABLISHMENTS FOR T0URISTS
THEREFORE ONLY ALBERGS AND APARTMENTS TURISTICS WILL BE RETAINED

In [737]:

df_other_establishments5 = df_other_establishments4.copy()

In [738]:

#DATAFRAME FOR "Apartaments turístics" - at
df_touristapartment_coordinates = df_other_establishments5[df_other_establishments5['category_oe']=='Apartaments turístics']
df_touristapartment_coordinates['category_oe'].value_counts()

Out[738]:

Apartaments turístics    12
Name: category_oe, dtype: int64

In [739]:

#DATAFRAME FOR "ALBERGS" - al
df_alberg_coordinates = df_other_establishments5[df_other_establishments5['category_oe']=='Albergs']
df_alberg_coordinates['category_oe'].value_counts()

Out[739]:

Albergs    121
Name: category_oe, dtype: int64

DATAFRAME: COORDINATES FOR “APARTAMENTS TURISTICS” – _touristapartment (FROM OTHER ESTABLISHMENTS)¶

SOURCE:

SECTION OTHER ESTABLISHMENT: df_at

In [740]:

df_at = df_touristapartment_coordinates.copy()

In [741]:

df_at.columns

Out[741]:

Index(['latitude_oe', 'longitude_oe', 'category_oe', 'district_code_oe',
       'district_name_oe', 'neighbourhood_code_oe', 'neighbourhood_name_oe',
       'address_name_oe', 'street_number_1_oe', 'street_number_2_oe',
       'name_oe', 'rtc_oe'],
      dtype='object')

In [742]:

#RENAME COLUMNS
df_at.columns = ['latitude_at', 'longitude_at', 'category_at', 'district_code_at',
       'district_name_at', 'neighbourhood_code_at', 'neighbourhood_name_at',
       'address_name_at', 'street_number_1_at', 'street_number_2_at', 'name_at', 'rtc_at']
df_at.head(1)

Out[742]:

WHITESPACES¶

In [743]:

#REMOVING SPACES
# .replace(' ','', regex=True) - replace all spaces with nothing
# .str.strip() - replace 1 initial and 1 trailing space only
# .replace(r's+',' ', regex=True) - replace multiple spaces with one single space
# .replace(r'^s+|s+$','',regex=True) - replace all + spaces s starting ^ and trailing $
# .replace('nan','', regex=True) - replace pre-existing 'nan' strings into empty cells - not to be used for string columns potentially containing nan as subpart of string
# .replace('.0','',regex=True) - replace .0 with nothing - '' is required to assign '.' as a normal character and not as a special one
df_at['rtc_at'] = df_at['rtc_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_at['category_at'] = df_at['category_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_at['address_name_at'] = df_at['address_name_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_at['street_number_1_at'] = df_at['street_number_1_at'].astype(str).replace(r'','',regex=True).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_at['street_number_2_at'] = df_at['street_number_2_at'].astype(str).replace(r'','',regex=True).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_at['district_code_at'] = df_at['district_code_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True)
df_at['district_name_at'] = df_at['district_name_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_at['neighbourhood_code_at'] = df_at['neighbourhood_code_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True)
df_at['neighbourhood_name_at'] = df_at['neighbourhood_name_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_at['longitude_at'] = df_at['longitude_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_at['latitude_at'] = df_at['latitude_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_at['name_at'] = df_at['name_at'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)

In [744]:

#DISTRICT AND NEIGHBOURHOOD CODES NEED TO BE IN STRING FORMAT AND REQUIRE AN ADDED '0' IN FRONT OF ALL NUMBERS BELOW 10
df_at[['district_code_at']] = df_at[['district_code_at']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])
df_at[['neighbourhood_code_at']] = df_at[['neighbourhood_code_at']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])

In [745]:

#REPLACE CELL THAT IS ENTIRELY SPACE OR EMPTY with None
df_at = df_at.applymap(lambda x: None if isinstance(x, str) and (x=='' or x.isspace()) else x)

In [746]:

df_at.head(1)

Out[746]:

In [747]:

df_at1 = df_at.copy()

DUPLICATES¶

In [748]:

#PRELIMINARY CHECK
df_at1.duplicated().value_counts()

Out[748]:

False    12
dtype: int64

In [749]:

#CHECK ON ID COLUMN
df_at1[df_at1.duplicated(subset=['rtc_at'], keep=False)]

Out[749]:

MISSING VALUES¶

In [750]:

df_at1.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 12 entries, 0 to 195
Data columns (total 12 columns):
 #   Column                 Non-Null Count  Dtype 
---  ------                 --------------  ----- 
 0   latitude_at            12 non-null     object
 1   longitude_at           12 non-null     object
 2   category_at            12 non-null     object
 3   district_code_at       12 non-null     object
 4   district_name_at       12 non-null     object
 5   neighbourhood_code_at  12 non-null     object
 6   neighbourhood_name_at  12 non-null     object
 7   address_name_at        12 non-null     object
 8   street_number_1_at     12 non-null     object
 9   street_number_2_at     0 non-null      object
 10  name_at                12 non-null     object
 11  rtc_at                 12 non-null     object
dtypes: object(12)
memory usage: 1.2+ KB

In [751]:

df_at1['category_at'].value_counts()

Out[751]:

Apartaments turístics    12
Name: category_at, dtype: int64

RTC¶

In [752]:

#IDENTIFY NULL VALUES
df_at1[(df_at1['rtc_at'].isnull()) 
       | (df_at1['rtc_at']=='nan') 
       | (df_at1['rtc_at']==None) 
       | (df_at1['rtc_at']=='')].shape[0]

Out[752]:

0

DISTRICT – NEIGHBOURHOOD¶

In [753]:

#IDENTIFY NULL VALUES
df_at1[(df_at1['neighbourhood_code_at'].isnull()) 
       | (df_at1['neighbourhood_code_at']=='nan') 
       |  (df_at1['neighbourhood_code_at']==None) 
       |  (df_at1['neighbourhood_code_at']=='')].shape[0]

Out[753]:

0

In [754]:

#IDENTIFY NULL VALUES
df_at1[(df_at1['neighbourhood_name_at'].isnull()) 
       | (df_at1['neighbourhood_name_at']=='nan') 
       |  (df_at1['neighbourhood_name_at']==None) 
       |  (df_at1['neighbourhood_name_at']=='')].shape[0]

Out[754]:

0

In [755]:

#CHECK IF NEIGHBOURHOOD NAMES ARE COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_at1[~df_at1['neighbourhood_name_at'].isin(df_district_neighbourhood_table['Neighbourhood_Name'])].shape[0]

Out[755]:

5

In [756]:

#CHECK IF NEIGHBOURHOOD CODES ARE COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_at1[~df_at1['neighbourhood_code_at'].isin(df_district_neighbourhood_table['Neighbourhood_Code'])].shape[0]

Out[756]:

0

In [757]:

#MAPPING/REPLACING REQUIRES NO NULL VALUES IN COLUMN LINKED TO set_index COLUMN
df_at1['neighbourhood_name_at'] = df_at1['neighbourhood_code_at'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['Neighbourhood_Name'])
df_at1['district_code_at'] = df_at1['neighbourhood_code_at'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Code'])
df_at1['district_name_at'] = df_at1['neighbourhood_code_at'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Name'])

In [758]:

#CHECK IF NEIGHBOURHOOD NAMES ARE COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_at1[~df_at1['neighbourhood_name_at'].isin(df_district_neighbourhood_table['Neighbourhood_Name'])].shape[0]

Out[758]:

0

In [759]:

df_at2 = df_at1.copy()

NORMALIZATION¶

In [760]:

df_at2.columns

Out[760]:

Index(['latitude_at', 'longitude_at', 'category_at', 'district_code_at',
       'district_name_at', 'neighbourhood_code_at', 'neighbourhood_name_at',
       'address_name_at', 'street_number_1_at', 'street_number_2_at',
       'name_at', 'rtc_at'],
      dtype='object')

In [761]:

#CHECK 'street_number_1'
df_at2[df_at2['street_number_1_at'].str.isdecimal()==False].value_counts('street_number_1_at') 

Out[761]:

street_number_1_at
276*280    1
dtype: int64

In [762]:

#CHECK 'street_number_2'
df_at2[df_at2['street_number_2_at'].str.isdecimal()==False].value_counts('street_number_2_at')

Out[762]:

Series([], dtype: int64)

In [763]:

def split_street_number_1_2 (df_target,column_address_1,column_address_2):
    df = df_target.loc[df_target[column_address_1].str.isdecimal()==False,column_address_1]
    df = df.str.split(pat='(d+)', expand=True)
    df_target.loc[df_target[column_address_1].str.isdecimal()==False, column_address_2] = df.iloc[:,3] #column_address_2 needs to precede column_address_1 
    df_target.loc[df_target[column_address_1].str.isdecimal()==False, column_address_1] = df.iloc[:,1] #the condition on which .loc is based is lost
    return print('Split Values:'), df

In [764]:

split_street_number_1_2(df_at2,'street_number_1_at','street_number_2_at')

Split Values:

Out[764]:

(None,
    0    1  2    3 4
 54    276  *  280  )

In [765]:

#CHECK AGAIN 'street_number_1'
df_at2[df_at2['street_number_1_at'].str.isdecimal()==False].value_counts('street_number_1_at') 

Out[765]:

Series([], dtype: int64)

In [766]:

#CHECK AGAIN 'street_number_2'
df_at2[df_at2['street_number_2_at'].str.isdecimal()==False].value_counts('street_number_2_at')

Out[766]:

Series([], dtype: int64)

In [767]:

df_at2.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 12 entries, 0 to 195
Data columns (total 12 columns):
 #   Column                 Non-Null Count  Dtype 
---  ------                 --------------  ----- 
 0   latitude_at            12 non-null     object
 1   longitude_at           12 non-null     object
 2   category_at            12 non-null     object
 3   district_code_at       12 non-null     object
 4   district_name_at       12 non-null     object
 5   neighbourhood_code_at  12 non-null     object
 6   neighbourhood_name_at  12 non-null     object
 7   address_name_at        12 non-null     object
 8   street_number_1_at     12 non-null     object
 9   street_number_2_at     1 non-null      object
 10  name_at                12 non-null     object
 11  rtc_at                 12 non-null     object
dtypes: object(12)
memory usage: 1.2+ KB

In [768]:

df_touristapartment = df_at2.copy()

DATAFRAME: COORDINATES FOR “ALBERGS” – alberg (FROM OTHER ESTABLISHMENTS)¶

SOURCE:

SECTION OTHER ESTABLISHMENT: df_albergs

In [769]:

df_al = df_alberg_coordinates.copy()

In [770]:

df_al.columns

Out[770]:

Index(['latitude_oe', 'longitude_oe', 'category_oe', 'district_code_oe',
       'district_name_oe', 'neighbourhood_code_oe', 'neighbourhood_name_oe',
       'address_name_oe', 'street_number_1_oe', 'street_number_2_oe',
       'name_oe', 'rtc_oe'],
      dtype='object')

In [771]:

#RENAME COLUMNS
df_al.columns = ['latitude_al', 'longitude_al', 'category_al', 'district_code_al',
       'district_name_al', 'neighbourhood_code_al', 'neighbourhood_name_al',
       'address_name_al', 'street_number_1_al', 'street_number_2_al', 'name_al', 'rtc_al']
df_al.head(1)

Out[771]:

WHITESPACES¶

In [772]:

#REMOVING SPACES
# .replace(' ','', regex=True) - replace all spaces with nothing
# .str.strip() - replace 1 initial and 1 trailing space only
# .replace(r's+',' ', regex=True) - replace multiple spaces with one single space
# .replace(r'^s+|s+$','',regex=True) - replace all + spaces s starting ^ and trailing $
# .replace('nan','', regex=True) - replace pre-existing 'nan' strings into empty cells - not to be used for string columns potentially containing nan as subpart of string
# .replace('.0','',regex=True) - replace .0 with nothing - '' is required to assign '.' as a normal character and not as a special one
df_al['rtc_al'] = df_al['rtc_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_al['category_al'] = df_al['category_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_al['address_name_al'] = df_al['address_name_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_al['street_number_1_al'] = df_al['street_number_1_al'].astype(str).replace(r'','',regex=True).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_al['street_number_2_al'] = df_al['street_number_2_al'].astype(str).replace(r'','',regex=True).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_al['district_code_al'] = df_al['district_code_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True)
df_al['district_name_al'] = df_al['district_name_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_al['neighbourhood_code_al'] = df_al['neighbourhood_code_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('.0','',regex=True)
df_al['neighbourhood_name_al'] = df_al['neighbourhood_name_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)
df_al['longitude_al'] = df_al['longitude_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_al['latitude_al'] = df_al['latitude_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True).replace('nan','', regex=True).replace('None','', regex=True)
df_al['name_al'] = df_al['name_al'].astype(str).replace(r's+',' ',regex=True).replace(r'^s+|s+$','',regex=True)

In [773]:

#DISTRICT AND NEIGHBOURHOOD CODES NEED TO BE IN STRING FORMAT AND REQUIRE AN ADDED '0' IN FRONT OF ALL NUMBERS BELOW 10
df_al[['district_code_al']] = df_al[['district_code_al']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])
df_al[['neighbourhood_code_al']] = df_al[['neighbourhood_code_al']].apply(lambda x: ['0{}'.format(y) if len(y) == 1 else y for y in x])

In [774]:

#REPLACE CELL THAT IS ENTIRELY SPACE OR EMPTY with None
df_al = df_al.applymap(lambda x: None if isinstance(x, str) and (x=='' or x.isspace()) else x)

In [775]:

df_al1 = df_al.copy()

DUPLICATES¶

In [776]:

#PRELIMINARY CHECK
df_al1.duplicated().value_counts()

Out[776]:

False    121
dtype: int64

In [777]:

#CHECK ON ID COLUMN
df_al1[df_al1.duplicated(subset=['rtc_al'], keep=False)]

Out[777]:

MISSING VALUES¶

In [778]:

df_al1.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 121 entries, 6 to 200
Data columns (total 12 columns):
 #   Column                 Non-Null Count  Dtype 
---  ------                 --------------  ----- 
 0   latitude_al            121 non-null    object
 1   longitude_al           121 non-null    object
 2   category_al            121 non-null    object
 3   district_code_al       121 non-null    object
 4   district_name_al       121 non-null    object
 5   neighbourhood_code_al  121 non-null    object
 6   neighbourhood_name_al  121 non-null    object
 7   address_name_al        121 non-null    object
 8   street_number_1_al     121 non-null    object
 9   street_number_2_al     0 non-null      object
 10  name_al                121 non-null    object
 11  rtc_al                 121 non-null    object
dtypes: object(12)
memory usage: 12.3+ KB

RTC¶

In [779]:

#IDENTIFY NULL VALUES
df_al1[(df_al1['rtc_al'].isnull()) 
       | (df_al1['rtc_al']=='nan') 
       | (df_al1['rtc_al']==None) 
       | (df_al1['rtc_al']=='')].shape[0]

Out[779]:

0

DISTRICT – NEIGHBOURHOOD¶

In [780]:

#IDENTIFY NULL VALUES
df_al1[(df_al1['neighbourhood_code_al'].isnull()) 
       | (df_al1['neighbourhood_code_al']=='nan') 
       | (df_al1['neighbourhood_code_al']==None) 
       | (df_al1['neighbourhood_code_al']=='')].shape[0]

Out[780]:

0

In [781]:

#IDENTIFY NULL VALUES
df_al1[(df_al1['neighbourhood_name_al'].isnull()) 
      | (df_al1['neighbourhood_name_al']=='nan') 
      | (df_al1['neighbourhood_name_al']==None) 
      | (df_al1['neighbourhood_name_al']=='')].shape[0]

Out[781]:

0

In [782]:

#CHECK HOW MANY RECORDS NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_al1[~df_al1['neighbourhood_name_al'].isin(df_district_neighbourhood_table['Neighbourhood_Name'])].shape[0]

Out[782]:

17

In [783]:

#CHECK HOW MANY RECORDS NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_al1[~df_al1['neighbourhood_code_al'].isin(df_district_neighbourhood_table['Neighbourhood_Code'])].shape[0]

Out[783]:

0

In [784]:

#MAPPING/REPLACING REQUIRES NO NULL VALUES IN COLUMN LINKED TO set_index COLUMN
df_al1['neighbourhood_name_al'] = df_al1['neighbourhood_code_al'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['Neighbourhood_Name'])
df_al1['district_code_al'] = df_al1['neighbourhood_code_al'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Code'])
df_al1['district_name_al'] = df_al1['neighbourhood_code_al'].map(df_district_neighbourhood_table.set_index('Neighbourhood_Code')['District_Name'])

In [785]:

#CHECK HOW MANY RECORDS NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_al1[~df_al1['neighbourhood_name_al'].isin(df_district_neighbourhood_table['Neighbourhood_Name'])].shape[0]

Out[785]:

0

In [786]:

#CHECK HOW MANY RECORDS NOT COMPATIBLE WITH NEIGHBOURHOOD TABLE
df_al1[~df_al1['neighbourhood_code_al'].isin(df_district_neighbourhood_table['Neighbourhood_Code'])].shape[0]

Out[786]:

0

NORMALIZATION¶

In [787]:

df_al2 = df_al1.copy()

In [788]:

#CHECK 'street_number_1'
df_al2[df_al2['street_number_1_al'].str.isdecimal()==False].value_counts('street_number_1_al') 

Out[788]:

street_number_1_al
51*LB      2
6*8        2
116*LB     1
8*10       1
70*74      1
58*60      1
56*58      1
55*57      1
52*54      1
48*52      1
149*151    1
45*47      1
41*51      1
402*404    1
38*42      1
33*LB      1
21*23      1
17*19      1
86*88      1
dtype: int64

In [789]:

#CHECK 'street_number_2'
df_al2[df_al2['street_number_2_al'].str.isdecimal()==False].value_counts('street_number_2_al')

Out[789]:

Series([], dtype: int64)

In [790]:

def split_street_number_1_2 (df_target,column_address_1,column_address_2):
    df = df_target.loc[df_target[column_address_1].str.isdecimal()==False,column_address_1]
    df = df.str.split(pat='(d+)', expand=True)
    df_target.loc[df_target[column_address_1].str.isdecimal()==False, column_address_2] = df.iloc[:,3] #column_address_2 needs to precede column_address_1 
    df_target.loc[df_target[column_address_1].str.isdecimal()==False, column_address_1] = df.iloc[:,1] #the condition on which .loc is based is lost
    return print('Split Values:'), df

In [791]:

split_street_number_1_2(df_al2,'street_number_1_al','street_number_2_al')

Split Values:

Out[791]:

(None,
     0    1    2     3     4
 7       56    *    58      
 11      58    *    60      
 18      17    *    19      
 19      45    *    47      
 28      55    *    57      
 39       6    *     8      
 43      21    *    23      
 64     402    *   404      
 67      48    *    52      
 94      51  *LB  None  None
 110    116  *LB  None  None
 112     33  *LB  None  None
 115     51  *LB  None  None
 130      6    *     8      
 140     70    *    74      
 141     41    *    51      
 159     38    *    42      
 160     52    *    54      
 183    149    *   151      
 185     86    *    88      
 192      8    *    10      )

In [792]:

#CHECK AGAIN 'street_number_1'
df_al2[df_al2['street_number_1_al'].str.isdecimal()==False].value_counts('street_number_1_al') 

Out[792]:

Series([], dtype: int64)

In [793]:

#CHECK AGAIN 'street_number_2'
df_al2[df_al2['street_number_2_al'].str.isdecimal()==False].value_counts('street_number_2_al')

Out[793]:

Series([], dtype: int64)

In [794]:

df_al2.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 121 entries, 6 to 200
Data columns (total 12 columns):
 #   Column                 Non-Null Count  Dtype 
---  ------                 --------------  ----- 
 0   latitude_al            121 non-null    object
 1   longitude_al           121 non-null    object
 2   category_al            121 non-null    object
 3   district_code_al       121 non-null    object
 4   district_name_al       121 non-null    object
 5   neighbourhood_code_al  121 non-null    object
 6   neighbourhood_name_al  121 non-null    object
 7   address_name_al        121 non-null    object
 8   street_number_1_al     121 non-null    object
 9   street_number_2_al     17 non-null     object
 10  name_al                121 non-null    object
 11  rtc_al                 121 non-null    object
dtypes: object(12)
memory usage: 12.3+ KB

PROBLEM: THE RTC CODES APPEAR IN A DIFFERENT FORMAT

In [795]:

df_n_places[df_n_places['category']=='Albergs'].head(1)

Out[795]:

1 rows × 23 columns

In [796]:

df_al2.head(1)

Out[796]:

VERIFY ADDRESS MATCH

In [797]:

df_n_places_albergs_only = df_n_places[df_n_places['category']=='Albergs']
df_n_places_albergs_only.head(1)

Out[797]:

1 rows × 23 columns

In [798]:

df_al_verify_address_match = df_al2.copy()

In [799]:

df_n_places_al_verify_address_match = df_n_places_albergs_only.copy()

In [800]:

#TO MERGE ON PARTIAL ADDRESS:
#STRIP DIGITS FROM
df_n_places_al_verify_address_match['address_verify'] = df_n_places_al_verify_address_match['address'].replace(r'd+','',regex=True).replace(r's+',' ',regex=True).str.strip()
#UPPER CASE THE 'address_name_al'
df_al_verify_address_match['address_verify_al'] = df_al_verify_address_match['address_name_al'].str.upper()

In [801]:

df_n_places_al_verify_address_match['address_verify']

Out[801]:

113             AV DIAGONAL
148             AV DIAGONAL
149             AV DIAGONAL
265               AV ICARIA
336            AV MERIDIANA
                ...        
10125      RDA SANT PERE PR
10160    RDA UNIVERSITAT EN
10330           VIA AUGUSTA
10331           VIA AUGUSTA
10338             VIA JULIA
Name: address_verify, Length: 125, dtype: object

In [802]:

df_verify_address_match = pd.merge(df_n_places_al_verify_address_match,df_al_verify_address_match, how='inner', 
                                   left_on=['address_verify','street_number_1','neighbourhood_code'],
                                   right_on=['address_verify_al','street_number_1_al','neighbourhood_code_al'])
df_verify_address_match[['rtc','rtc_al','street_number_1','street_number_1_al','neighbourhood_code','neighbourhood_code_al']]

Out[802]:

BY MERGING THE DATAFRAMES ON PARTIAL ADDRESS, THE RTC CODES APPEAR TO MATCH EXCEPT FOR THE STARTING LETTERS
THEREFORE:
MODIFY THE RTC CODES ON THE STARTING LETTERS AND VERIFY RTC MATCH:

In [803]:

df_n_places_al_verify_rtc_match = df_n_places[df_n_places['category']=='Albergs']
df_n_places_al_verify_rtc_match.head(1)

Out[803]:

1 rows × 23 columns

In [804]:

df_al_verify_rtc_match = df_al2.copy()

In [805]:

df_al_verify_rtc_match['rtc_al_modified'] = df_al_verify_rtc_match['rtc_al'].astype(str).str.replace('AJ000','ALB-')
df_al_verify_rtc_match.head(1)

Out[805]:

In [806]:

df_verify_rtc_match = df_al_verify_rtc_match.merge(df_n_places_al_verify_rtc_match, 
                          how='inner', left_on=['rtc_al_modified'], right_on=['rtc'])

In [807]:

df_verify_rtc_match

Out[807]: