<jupyter_start><jupyter_text>Seattle and Boston AirBnB Data Analysis In this notebook I'm going to analyze AirBnB data for the cities of Seattle and Boston. In particular, I'm going to answer to the following questions:1. Which months during the year have listing prices that are, on average, higher or lower? Are there any differences between the two cities? 2. How does the average price differ based on neighbourhood and accomodation type for the two cities?3. Are house prices and ratings correlated?4. How accurately can house prices be predicted, based on the available information?  Let's import all the necessary libraries first<jupyter_code>import pandas as pd
import numpy as np
import sklearn
import matplotlib.pyplot as plt
from matplotlib.pyplot import figure
import math
import os
import datetime
import re
import xgboost as xgb
import seaborn as sns
from sklearn import svm
from sklearn.linear_model import LinearRegression
from sklearn.linear_model import LogisticRegression
from sklearn.ensemble import RandomForestRegressor
from sklearn.model_selection import train_test_split
from sklearn.metrics import r2_score, mean_squared_error

#print(os.getcwd())
pd.options.display.max_columns = None #show all columns of dataframe<jupyter_output><empty_output><jupyter_text>1. Months with higher and lower prices To answer the first question, I'm going to use the data available from the _calendar_ dataset. This dataset contains a series of listings, along with their price and availiability on a certain date.<jupyter_code># read in the datasets
df_cal_sea = pd.read_csv('.\Seattle\calendar.csv')
df_cal_bos = pd.read_csv('.\Boston\calendar.csv')
df_cal_bos.head()<jupyter_output><empty_output><jupyter_text>In the _calendar_ dataset, the __listing_id__ column represents the different accomodations. For each accomodation, information like __price__, and __availability__ on a certain __date__ is displayed. From the data above, price values are null. Let's check then the number of null values in the datasets.<jupyter_code>print(df_cal_sea.isna().mean(), "\n\n", df_cal_bos.isna().mean())<jupyter_output>listing_id    0.00000
date          0.00000
available     0.00000
price         0.32939
dtype: float64 

 listing_id    0.000000
date          0.000000
available     0.000000
price         0.508716
dtype: float64<jupyter_text>Nulls are present only in the __price__ column for both datasets. Since their proportion is quite relevant, it is likely that the __price__ column has nulls whenever the respective listing is not available. Let's check if there are any listings that are available and have null price at the same time.<jupyter_code># check number of available listings with null price
print("Number of listings in Seattle without price: {}".format(df_cal_sea[(df_cal_sea.price.isna()) & 
                                                                          (df_cal_sea.available == 't')].shape[0]))
print("Number of listings in Boston without price: {}".format(df_cal_bos[(df_cal_bos.price.isna()) & 
                                                                         (df_cal_bos.available == 't')].shape[0]))<jupyter_output>Number of listings in Seattle without price: 0
Number of listings in Boston without price: 0<jupyter_text>The result from the code cell above is 0 for both datasets, meaning that null values of price are only associated with the unavailability of the listings. Now, in order to obtain the average monthly price of each listing, it is necessary first to handle the missing values and to prepare the data.Since the proportion of missing prices is large, I'm not going to compute them, instead I will simply remove the rows with no price, as I want to calculate the mean price based on those days where each listing is available. To prepare the data, I need to introduce a __month__ column, obtained from the __date__ column, and to convert price values into floats. All these steps are perfomed in the function below.<jupyter_code># drop rows with nan price and prepare data
def format_calendar(df):
    '''
    This function takes as input a dataframe originated by reading in the calendar.csv files, and formats it to make it
    ready to use for analysis on monthly prices, adding a column for the month, obtained by the date, removing nans 
    from the price column, and dropping the "date" and "available" columns
    
    Input:
    df - a pandas dataframe obtained by reading in the calendar.csv files
    
    Output:
    df_new - a pandas dataframe containing all the necessary information for the analyis
    '''
        
    df_new = df.dropna(subset=['price']) 
    df_new.loc[:, 'price'] = df_new['price'].str.replace('$','').str.replace(',','').astype(float) # cast price values into floats
    df_new['month'] = pd.to_datetime(df_new['date']).dt.month # add month column
    df_new_clean = df_new.drop(columns=['date', 'available']) 

    return df_new_clean

# apply function to both cities
df_cal_sea_form = format_calendar(df_cal_sea)
df_cal_bos_form = format_calendar(df_cal_bos)

df_cal_sea_form.head()<jupyter_output>C:\Users\Duratorre\Anaconda\lib\site-packages\pandas\core\indexing.py:543: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self.obj[item] = s
C:\Users\Duratorre\Anaconda\lib\site-packages\ipykernel_launcher.py:17: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy<jupyter_text>Now that the data is ready for the analysis, in order to obtain the average monthly prices of each listing, the pandas *pivot_table* method comes in handy. With this method, it is possible to display in a table the average price of each listing for each month, with months being on the columns and the listings being on the rows. The second step consists in deriving, for each listing, the months with the highest or lowest price, I'll use the methods _idxmax_ and _idxmin_, which output respectively the column indexes of the highest and lowest average monthly price. There will be some exceptions in these calculations, and three cases need to be distinguished:1. Listings that have constant price throughout the year, resulting in the same average monthly price 2. Listings that have constant price throughout the year except a shorter period where the price is higher, resulting in many months having the same minimum average price3. Listings that have constant price throughout the year except a shorter period where the price is lower, resulting in many months having the same maximum average priceFor the 1st case, there will be no maximums nor minimums, while for the 2nd and 3rd there won't be respectively any minimums and maximums.<jupyter_code># obtain the months where prices are lower and higher for the two cities
def get_extremes(df, values, index, columns, aggfunc):
    '''
    This function creates a pivot table out of a dataframe and add columns "max" and "min" to the existing dataframe
    containing respectively the column labels with the highest and lowest values
    
    Input:
    df - a pandas dataframe that needs to be transformed into a pivot table
    values - a pandas dataframe column label that will be the aggregate value of the pivot
    index - a pandas dataframe column label that will be the rows of the pivot
    columns - a pandas dataframe column label that will be the columns of the pivot
    aggfunc - a function for aggregating the values
    
    Output:
    df_pivot - a pandas dataframe transformed into a pivot and with a "max" column for the maximum value among 
    existing columns
    '''
    
    # make a pivot out of the input df
    df_pivot = df.pivot_table(values=values, index=index, columns=columns, aggfunc=aggfunc).reset_index()
    
    # fill max and min with the indexes of the corresponding values
    df_pivot['max'] = df_pivot.drop(
        columns=df_pivot.columns[0]).idxmax(
        axis=1, skipna=True).astype(int) # the index of the max value is also the corresponding month, drop the first column that contains listing id
    df_pivot['min'] = df_pivot.drop(
        columns=[df_pivot.columns[0], df_pivot.columns[-1]]).idxmin(axis=1, skipna=True).astype(int) # drop also the last column as it contains the max
    df_pivot.columns.name = None
    
    # remove max e min for those rows with constant price throughout the year
    df_pivot.loc[(df_pivot['max'] == df_pivot['min']),['max', 'min']] = np.nan
    
    for i in range(df_pivot.shape[0]):
        # remove max from those rows where the max price is present in more than 1 month
        if sum(df_pivot.iloc[i, 1:-2] == df_pivot.iloc[i, 1:-2].max()) > 1:
            df_pivot.iloc[i, -2] = np.nan
        # remove min from those rows where the min is present in more than 1 month
        if sum(df_pivot.iloc[i, 1:-2] == df_pivot.iloc[i, 1:-2].min()) > 1:
            df_pivot.iloc[i, -1] = np.nan

    return df_pivot<jupyter_output><empty_output><jupyter_text>Results Now, I can apply the function __get_extremes__ to the dataframes and obtain the maximums and minimums of the listings.<jupyter_code># Seattle
df_sea_ext = get_extremes(df_cal_sea_form, values='price', index='listing_id', columns='month', aggfunc=np.nanmean)
df_sea_ext.head()
# Boston
df_bos_ext = get_extremes(df_cal_bos_form, values='price', index='listing_id', columns='month', aggfunc=np.nanmean)
df_bos_ext.head()<jupyter_output><empty_output><jupyter_text>Get the months which have the highest and lowest average price for the majority of listings inthe two cities The data now contains, for each listing, the months wih the highest and lowest average price. In order to obtain the months with the highest and lowest average price for the majority of listings in the two cities, I'll simply take the _mode_ of the __max__ and __min__ columns in the dataframes. The month with the highest average price will be, on average, the worst to rent an accomodation, whereas the month with the lowest average price will be, on average, the best to rent an accomodation.<jupyter_code># Seattle
best_sea = df_sea_ext['max'].mode()
worst_sea = df_sea_ext['min'].mode()

# Boston
best_bos = df_bos_ext['max'].mode()
worst_bos = df_bos_ext['min'].mode()

# map numeric values of months to corresponding strings
numeric_to_string = {1:'January', 2:'February', 3:'March', 4:'April', 5:'May', 6:'June', 7:'July', 
                         8:'August', 9:'September', 10:'October', 11:'November', 12:'December'}

print('The worst month to rent an airbnb in Seattle is {}, while the best month is {}'
      .format(numeric_to_string[best_sea.values[0]], numeric_to_string[worst_sea.values[0]]))
print('The worst month to rent an airbnb in Boston is {}, while the best month is {}'
      .format(numeric_to_string[best_bos.values[0]], numeric_to_string[worst_bos.values[0]]))<jupyter_output>The worst month to rent an airbnb in Seattle is April, while the best month is January
The worst month to rent an airbnb in Boston is October, while the best month is December<jupyter_text>In Seattle, the majority of listings have their maximum monthly price during April and their minimum prices in January, but what about the other listings? In order to have an idea of how maximum and minimum average monthly prices are distributed during the year in the two cities, let's plot the histograms of the maximum and minimum columns for the *df_sea_ext* and *df_bos_ext* dataframes.  Plot the distributions of the maximums and minimum for the two cities<jupyter_code># plot density probabilities of max and min for Seattle and Boston
figure(figsize=(10,10))

values, counts = np.unique(df_sea_ext['max'].dropna(), return_counts=True) # get months and the number of their occurence in the max column
plt.subplot(2,2,1)
plt.bar(values, counts, align='center', color='Indigo')
plt.gca().set_xticks(values)
plt.xlabel('Month')
plt.title('Distribution of max price\n during the year in Seattle')

values, counts = np.unique(df_sea_ext['min'].dropna(), return_counts=True) # get months and the number of their occurence in the min column
plt.subplot(2,2,2)
plt.bar(values, counts, align='center', color='Indigo')
plt.gca().set_xticks(values)
plt.xlabel('Month')
plt.title('Distribution of min price\n during the year in Seattle')

values, counts = np.unique(df_bos_ext['max'].dropna(), return_counts=True)
plt.subplot(2,2,3)
plt.bar(values, counts, align='center', color='Indigo')
plt.gca().set_xticks(values)
plt.xlabel('Month')
plt.title('Distribution of max price\n during the year in Boston')

values, counts = np.unique(df_bos_ext['min'].dropna(), return_counts=True)
plt.subplot(2,2,4)
plt.bar(values, counts, align='center', color='Indigo')
plt.gca().set_xticks(values)
plt.xlabel('Month')
plt.title('Distribution of min price\n during the year in Boston')

plt.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>From the graphs above, it can be seen that the maximum and minimum prices for Seattle have defined distributions, with peaks respectively for April and January. On the other side, the situation for Boston is not as easy to read, as the prices tend to be higher during September and October, albeit in the same months there are also a lot of listings with the lowest price (September is the second month with the highest number of listings for both maximum and minimum price).   2. Average Price vs Neighbourhood and Accomodation Type To answer this question, I'm going to look at the _listings_ datasets. These datasets contain a series of listings along with various information like price, host personal information, amenities, neighbourhood, accomodation type, etc.<jupyter_code># read in Seattle dataset
df_lis = pd.read_csv('.\Seattle\listings.csv')
df_lis.head()
# read in Boston dataset
df_lis_bos = pd.read_csv('.\Boston\listings.csv')
df_lis_bos.head()<jupyter_output><empty_output><jupyter_text>I'm interested, for the moment, in the __price__, __neighbourhood__ and __room_type__ features, as I want to analyze the variation of the average price between different neighbourhoods and room types. There are three columns that contain information about neighbourhood (__neighbourhood__, __neigbourhood_cleansed__, __neigbourhood_group_cleansed__): it appears each of them lists neighbrouhoods with a different level of detail. Let's look at their unique values for the two cities.<jupyter_code># Seattle
print(df_lis['neighbourhood'].unique(), '\n\n', df_lis['neighbourhood_cleansed'].unique(), '\n\n', df_lis['neighbourhood_group_cleansed'].unique())
# Boston
print(df_lis_bos['neighbourhood'].unique(), '\n\n', df_lis_bos['neighbourhood_cleansed'].unique(), '\n\n', df_lis_bos['neighbourhood_group_cleansed'].unique())<jupyter_output>['Roslindale' nan 'Jamaica Plain' 'Mission Hill' 'Fenway/Kenmore'
 'Back Bay' 'Leather District' 'Chinatown' 'Hyde Park' 'North End'
 'Roxbury' 'South End' 'Mattapan' 'East Boston' 'South Boston'
 'Charlestown' 'West End' 'Beacon Hill' 'Theater District'
 'Downtown Crossing' 'Downtown' 'Financial District' 'Government Center'
 'Allston-Brighton' 'West Roxbury' 'Chestnut Hill' 'Dorchester'
 'Brookline' 'Cambridge' 'Somerville' 'Harvard Square'] 

 ['Roslindale' 'Jamaica Plain' 'Mission Hill' 'Longwood Medical Area'
 'Bay Village' 'Leather District' 'Chinatown' 'North End' 'Roxbury'
 'South End' 'Back Bay' 'East Boston' 'Charlestown' 'West End'
 'Beacon Hill' 'Downtown' 'Fenway' 'Brighton' 'West Roxbury' 'Hyde Park'
 'Mattapan' 'Dorchester' 'South Boston Waterfront' 'South Boston'
 'Allston'] 

 [nan]<jupyter_text>From the results above, it appears that for Seattle the __neighbourhood_cleansed__ column has more detailed information than the __neighbourhood__ column. The latter contains *nans*, whereas the first doesn't. The column __neighbrouhood_group_cleansed__ contains aggregated information about neighbourhoods. In order to have a better understanding of how price differ according to neighbourhoods and to make visualizations more clear, I'll use this column to represent neighbourhoods in Seattle.For Boston, the __neighbourhood_group_cleansed__ column has all *nan* values. The __neighbourhood__ column has more detailed information, but also contains *nan* values, therefore I'll use the **neighbourhood_cleansed column**, which allows for easier visualizations as well.Regarding the __price__ column, its values are strings in both dataframes, therefore a conversion to floats is needed.<jupyter_code># create sub-dataset of Seattle
df_lis_comp = df_lis[['neighbourhood_group_cleansed', 'room_type', 'price']]
# convert price column into float
df_lis_comp.loc[:,'price'] = df_lis_comp['price'].str.replace('$', '').str.replace(',', '').astype(float)

# create sub-dataset of Boston
df_lis_bos_comp = df_lis_bos[['neighbourhood_cleansed', 'room_type', 'price']]
# convert price column into float
df_lis_bos_comp.loc[:,'price'] = df_lis_bos_comp['price'].str.replace('$', '').str.replace(',', '').astype(float)
df_lis_bos_comp.head()<jupyter_output>C:\Users\Duratorre\Anaconda\lib\site-packages\pandas\core\indexing.py:635: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self.obj[item_labels[indexer[info_axis]]] = value<jupyter_text>Let's check now for null values in the two dataframes.<jupyter_code>df_lis_comp.isna().mean(), df_lis_bos_comp.isna().mean()<jupyter_output><empty_output><jupyter_text>No nulls are present in the two dataframes, so let's proceed to calculate the average price based on neighbourhood and accomodation type.   Results I'll use again the _pivot table_ method to have a table with average prices as values and neighbourhoods as rows and accomodation type as columns.  Seattle<jupyter_code>df_lis_plot = df_lis_comp.pivot_table(values='price', index='neighbourhood_group_cleansed', 
                                         columns='room_type', aggfunc=np.nanmean)
df_lis_plot.index.name = None # reomve index name
df_lis_plot.head()
df_lis_plot.sort_values(by='Entire home/apt', ascending=False).plot.bar(figsize=(20,10), legend=False, fontsize=20)#, 
plt.title("Average price in Seattle for neighbourhood and accomodation type", fontsize=20)
plt.legend(fontsize=20)
plt.ylabel('Price', fontsize=20)<jupyter_output><empty_output><jupyter_text>It is possible to see from the graph above that, as one should expect, prices of an entire home or apartment are greater than those for a private or shared room. Regarding the private and shared room, the price for the first is higher than the second one. A noticeable thing is the fact that. for those neighbourhoods that have the greatest average price for entire homes/apartments, not the same can be said about private or shared rooms. For istance, while Magnolia has the most expensive entire home and apartments on average, the most expensive private rooms are in Downtown and the most expensive shared room are in Cascade.The average prices in the different neigbourhoods may be affected by outliers. For example, in one neighbourhood there could be a few listing with very high price, which results in the neighbourhood having a large average price, despite the majority of its listings being on a medium range price. Let's check for outliers in the price of entire homes/apartments using boxplots.<jupyter_code>figure(figsize=(20,10))
df_lis_box = df_lis_comp[df_lis_comp.room_type == 'Entire home/apt']
df_lis_sort = df_lis_box.groupby(['neighbourhood_group_cleansed']).mean().sort_values(by='price', ascending=False)

# make a boxplot
sns.boxplot(y='price', x='neighbourhood_group_cleansed', data=df_lis_box, order=df_lis_sort.index) 
plt.xticks(rotation=90, fontsize=15)
plt.yticks(fontsize=15)
plt.ylabel('price', fontsize=15)
plt.xlabel('')
plt.title('Boxplots of price grouped by neighbourhood', fontsize=20)<jupyter_output><empty_output><jupyter_text>The figure above shows the presence of outliers, the majority of them being extreme outliers above the upper limit. It can be seen that in some cases, the presence of outliers affects the order in which neighbourhoods are sorted. For example, Queen Anne and West Seattle have approximately the same median, with the interquartile range being greater for the second; without the presence of outliers, it would be very likely that West Seattle would have a greater average price than Queen Anne. The presence of outliers, however, determines that Queen Anne is more expensive than West Seattle on average.  Boston Let's plot the same graphs for Boston.<jupyter_code>df_lis_bos_plot = df_lis_bos_comp.pivot_table(values='price', index='neighbourhood_cleansed', 
                                         columns='room_type', aggfunc=np.nanmean)

df_lis_bos_plot.index.name = None
df_lis_bos_plot.head()
df_lis_bos_plot.sort_values(by='Entire home/apt', ascending=False).plot.bar(figsize=(20,10), fontsize=20) 
plt.title("Average price in Boston for neighbourhood and accomodation type", fontsize=20)
plt.legend(fontsize=20)
plt.ylabel('Price', fontsize=20)
figure(figsize=(20,10))
df_lis_bos_box = df_lis_bos_comp[df_lis_bos_comp.room_type == 'Entire home/apt']
df_lis_bos_sort = df_lis_bos_box.groupby(['neighbourhood_cleansed']).mean().sort_values(by='price', ascending=False)

# make a boxplot
sns.boxplot(y='price', x='neighbourhood_cleansed', data=df_lis_bos_box, order=df_lis_bos_sort.index) 
plt.xticks(rotation=90, fontsize=15)
plt.yticks(fontsize=15)
plt.ylabel('price', fontsize=15)
plt.xlabel('')
plt.title('Boxplots of price grouped by neighbourhood, ordered by average price', fontsize=20)
figure(figsize=(20,10))
df_lis_bos_box = df_lis_bos_comp[df_lis_bos_comp.room_type == 'Entire home/apt']
df_lis_bos_sort = df_lis_bos_box.groupby(['neighbourhood_cleansed']).median().sort_values(by='price', ascending=False)

# make a boxplot
sns.boxplot(y='price', x='neighbourhood_cleansed', data=df_lis_bos_box, order=df_lis_bos_sort.index) 
plt.xticks(rotation=90, fontsize=15)
plt.yticks(fontsize=15)
plt.ylabel('price', fontsize=15)
plt.xlabel('')
plt.title('Boxplots of price grouped by neighbourhood, ordered by median price', fontsize=20)<jupyter_output><empty_output><jupyter_text>Looking at the results for Boston, the conclusions are almost the same as Seattle, with the only difference being that in Boston there are some neighbourhoods where the average price of private rooms is lower compared to shared rooms. Regarding the outliers, it is possible to see, for example, that for the neighbourhood Fenway, the presence of an outlier with a very high price (around 4000) makes on average the neighbourhood one of the most expensive, whereas it's median and interquartile range suggests that it could be lower in order than Downtown.  3. Correlation of price and ratings To check if there is correlation between these two variables, it is better first to isolate them into a separate dataframe. After that, I'm going to perform proper data type conversion for price like above, and finally use a scatter plot of the two variables to check their behaviour against each other.<jupyter_code># Seattle
# create sub-dataframe of price and rating
df_lis_scat = df_lis[['price', 'review_scores_rating']]
# convert price to float
df_lis_scat.loc[:,'price'] = df_lis_scat['price'].str.replace('$', '').str.replace(',', '').astype(float)
# drop null values
df_lis_scat.dropna(inplace=True)

# Boston
df_lis_bos_scat = df_lis_bos[['price', 'review_scores_rating']]
df_lis_bos_scat.loc[:,'price'] = df_lis_bos_scat['price'].str.replace('$', '').str.replace(',', '').astype(float)
df_lis_bos_scat.dropna(inplace=True)
# making the plots
# Seattle
figure(figsize=(20,10))
plt.subplot(1,2,1)
plt.scatter(df_lis_scat['price'], df_lis_scat['review_scores_rating'])
plt.title('Seattle', fontsize=20)
plt.xlabel('Price', fontsize=15)
plt.xticks(fontsize=15)
plt.ylabel('Rating', fontsize=15)
plt.yticks(fontsize=15)

# Boston
plt.subplot(1,2,2)
plt.scatter(df_lis_bos_scat['price'], df_lis_bos_scat['review_scores_rating'])
plt.title('Boston', fontsize=20)
plt.xlabel('Price', fontsize=15)
plt.xticks(fontsize=15)
plt.ylabel('Rating', fontsize=15)
plt.yticks(fontsize=15)<jupyter_output><empty_output><jupyter_text>The pictures above suggest that price and rating are not much correlated. The only noticeable thing is that for higher prices ratings tend to be higher, while for low prices ratings have multiple values, meaning that, in general, high prices are an indication of good ratings.  4. Predict house prices Last step of the analysis revolves around predicting house prices. The data necessary for this analysis is contained in the _listings_ datasets. I'm going to consider, for the moment, only the data for Seattle. In order to run a predicting model on the data, it is first necessary to make it ready for the analysis. First of all, there a lot of columns in the _listings_ dataframes that contains urls. These urls can't be used to predict price, therefore they are going to be discarded.<jupyter_code># remove all columns with url
df_lis = df_lis.loc[:, ~df_lis.columns.str.contains('url', case=False)]<jupyter_output><empty_output><jupyter_text>Some of the columns may have just one value, like __country__, and __country_code__. If a column has only one unique value, it does not provide any useful information to the model to predict prices, therefore it can be removed from the dataframe.<jupyter_code># remove columns with unique values
unique_values = []
for col in list(df_lis.columns):
    if df_lis[col].unique().size==1:
        unique_values.append(col)

unique_values.remove('last_scraped')
print(unique_values)

df_lis_no_unique = df_lis.drop(columns=unique_values)<jupyter_output>['scrape_id', 'experiences_offered', 'market', 'country_code', 'country', 'has_availability', 'calendar_last_scraped', 'requires_license', 'license', 'jurisdiction_names']<jupyter_text>Now, there are some columns left that provide no relevant information to predict price. These columns are those that contain data like personal information about the host, as these do not affect house price, which is a value decided by the host themselves, house description, zipcode, weekly price and monthly price, as I'm interested in just the daily price, and city and state, as these two columns contain data that can be traced back to just one unique value. Let's remove these columns.<jupyter_code># remove unnecessary columns
unnec_col = ['summary', 'space', 'description','name', 'host_id', 'host_name', 'host_location', 'host_about', 
             'host_neighbourhood', 'neighborhood_overview', 'neighbourhood', 'neighbourhood_cleansed', 'notes', 
             'street', 'city', 'state', 'zipcode', 'smart_location', 'latitude', 'longitude', 
             'weekly_price', 'monthly_price', 'host_acceptance_rate', 'calendar_updated']

df_lis_no_unn = df_lis_no_unique.drop(columns=unnec_col)
df_lis_no_unn.head()<jupyter_output><empty_output><jupyter_text>Let's check now the amount of _null_ values in each column.<jupyter_code>print(df_lis_no_unn.isna().mean())<jupyter_output>id                                  0.000000
last_scraped                        0.000000
transit                             0.244631
host_since                          0.000524
host_response_time                  0.136983
host_response_rate                  0.136983
host_is_superhost                   0.000524
host_listings_count                 0.000524
host_total_listings_count           0.000524
host_verifications                  0.000000
host_has_profile_pic                0.000524
host_identity_verified              0.000524
neighbourhood_group_cleansed        0.000000
is_location_exact                   0.000000
property_type                       0.000262
room_type                           0.000000
accommodates                        0.000000
bathrooms                           0.004191
bedrooms                            0.001572
beds                                0.000262
bed_type                            0.000000
amenities                           0.000000
square_fee[...]<jupyter_text>The column __square_feet__ has a lot of _null_ values (more than 97%). Therefore it's best to discard it. Other columns, like __review_scores_rating__ , __host_response_rate__ and __review_scores_accuracy__ have some _null_ values. For __review_scores_rating__ and __host_response_rate__, the _null_ values are very likely associated with new hosts, as they still haven't hosted anyone in their accodomodations nor received any inquiries yet, and therefore these values can't be calculated. I'm going to delete the rows with _null_ values for any of these columns, without computing the _nans_, which means the analysis will exclude new hosts.<jupyter_code># dropping columns with majority on nan values, like square_feet 
df_lis_new = df_lis_no_unn.drop(columns=['square_feet'])

# dropping rows with nans in review_scores_ratings, host_response_rate and review_scores_accuracy
cols_with_nans = ['review_scores_rating', 'host_response_rate', 'review_scores_accuracy']

for subset in cols_with_nans:
    df_lis_new.dropna(axis=0, how='any', subset=[subset], inplace=True)<jupyter_output><empty_output><jupyter_text>Let's check once again the proportion of _nulls_ left in the dataframe for each column.<jupyter_code>df_lis_new.isna().mean()<jupyter_output><empty_output><jupyter_text>The only columns left with _nans_ are __transit__,  __bathrooms__, __bedrooms__, __security_deposit__ and __cleaning_fee__. The column transit will be dealt with later on. For the __bathrooms__, __bedrooms__, __security_deposit__ and __cleaning_fee__ columns I'm going to compute the _null_ values. For bedrooms we are going to put 0, as it is very likely that the listings without information about bedrooms are those that do not have one. The same goes for security deposit and cleaning fees: when these values are not present, it's probably because they are 0. For bathrooms the situation is slightly different: the _nulls_ are usually associated with accomodations such as private or shared rooms. As there is no house without a bathroom, I'm going to set the null to 1. Before I compute the _null_ values, however, let's perform some data conversion.<jupyter_code># data conversion
# float columns
float_cols = ['host_response_rate', 'price', 'security_deposit', 'cleaning_fee', 'extra_people']

for col in float_cols:
    df_lis_new.loc[:,col] = df_lis_new[col].str.replace(r'[\%\$\,]', '', regex=True).astype(float)
    
# boolean columns
df_lis_new.replace('t', 1, inplace=True)
df_lis_new.replace('f', 0, inplace=True)

# compute null values
null_cols = ['bedrooms', 'bathrooms', 'security_deposit', 'cleaning_fee']

for col in null_cols:
    df_lis_new[col].fillna(1 if col is "bathrooms" else 0, inplace=True) # as there can be homes without bedrooms
    #and all listings are entire building apartments or single rooms, there has to be at least one bathroom, whether private or shared

df_lis_new.head()<jupyter_output><empty_output><jupyter_text>The next action regards feature engineering. There are still columns with date values that can't be used in the analysis, thus I need to create features out of these columns. The columns with date values are __last_scraped__, __host_since__, __first_review__ and __last_review__, which contain dates respectively about the last day the data was extracted, the day when the host joined AirBnB, and the dates of first and last review for each listing. I can use the information from these columns to calculate values such as the host seniority, e.g. the number of days the host has been such at the day of the data extraction, and the number of days from first and last review for each listing.<jupyter_code># create new features
df_lis_clean = df_lis_new
df_lis_clean['host_seniority'] = (pd.to_datetime(df_lis_new['last_scraped']) - pd.to_datetime(df_lis_new['host_since'])).dt.days
df_lis_clean['days_since_first_review'] = (pd.to_datetime(df_lis_new['last_scraped']) - pd.to_datetime(df_lis_new['first_review'])).dt.days
df_lis_clean['days_since_last_review'] = (pd.to_datetime(df_lis_new['last_scraped']) - pd.to_datetime(df_lis_new['last_review'])).dt.days
df_lis_clean.drop(columns=['last_scraped', 'host_since', 'first_review', 'last_review'], inplace = True)
df_lis_clean.head()<jupyter_output><empty_output><jupyter_text>The columns left that aren't ready for analysis yet are those with categorical values, like __host_response_time__, __neighbourhood_group_cleansed__, __property_type__, __room_type__, __bed_type__, __cancellation_policy__. Columns like __host_verifications__ and __amenities__ have a set of values for each row. In order to extract information from these two columns, it is necessary to one hot encode them with a custom function.<jupyter_code># extract features from columns with sets of values in each row
def extract_features(df, col_names):
    '''
    This function reproduces one hot encoding for those columns of a pandas dataframe that have a set or list of values 
    in each row
    
    Input:
    df - a pandas dataframe 
    col_names - a list of columns whose values are sets or lists
    
    Output:
    df_feat - a dataframe with one hot encoding for the selected columns
    '''
    
    for col in col_names:
        #one hot encoding of df[col]
        features = df[col].apply(lambda x: re.sub(r"[^a-zA-Z0-9\,\/\-]", "", x))
        features_onehot = features.str.get_dummies(sep=',').add_prefix(col + '_')
        df = pd.concat((df, features_onehot), axis=1)
    
    # remove original columns from output dataframe 
    df_feat = df.drop(columns=col_names)
    
    return df_feat

feat_cols = ['amenities', 'host_verifications']
df_lis_feat = extract_features(df_lis_clean, feat_cols)
df_lis_feat.head()<jupyter_output><empty_output><jupyter_text>Now let's perform *one hot encoding* for the remaing columns with categorical variables.<jupyter_code># Extracting features from categorical columns
one_hot_columns = ['host_response_time', 'neighbourhood_group_cleansed', 'property_type', 'room_type', 'bed_type', 'cancellation_policy']
df_lis_ohe = pd.get_dummies(data = df_lis_feat, columns = one_hot_columns)  #, drop_first=True)
df_lis_ohe.head()<jupyter_output><empty_output><jupyter_text>The only column left to handle is __transit__. In order to extract information from this column, text analytics should be considered. However, to make the process simpler, I'm going to use a different approach. By selecting the most common transportation means in Seattle, I'll simply perform *one hot encoding* on this column based on whether each transportation mean appears in the __transit__ column of each row.<jupyter_code># One Hot Encoding for transportation

# most commom transportation means in Seattle, including NaN, e.g. no transportation
transport_means = ['Bus', 'Light Rail', 'Ferry', 'Heavy Rail', 'Train', 'Metro', 'NaN']

df_lis_ohe['transit'].fillna("NaN", inplace=True)

for tr_mean in transport_means:  
    booleans = df_lis_ohe['transit'].str.contains(tr_mean, False)    
    new_col = [int(boolean) for boolean in booleans]
    new_col_name = 'transit_{}'.format(tr_mean)
    df_lis_ohe[new_col_name] = new_col

df_lis_ohe.drop(columns='transit', inplace=True)
df_lis_ohe.shape<jupyter_output><empty_output><jupyter_text>Now, let's look at the final dataframe ready for analysis.<jupyter_code>df_lis_pred = df_lis_ohe.drop(columns='id')
df_lis_pred.head()<jupyter_output><empty_output><jupyter_text>Now, the dataframe is ready for prediction analysis.  Let's do all the steps for the Boston listing dataset as well<jupyter_code># remove all columns with url
lis_bos = df_lis_bos.loc[:,~df_lis_bos.columns.str.contains('url', case = False)]

# remove columns with unique values
unique_values = []
for col in list(lis_bos.columns):
    if lis_bos[col].unique().shape[0]==1:
        unique_values.append(col)

unique_values.remove('last_scraped')

lis_bos_nounq = lis_bos.drop(columns=unique_values)

# remove unnecessary columns
unnec_col = ['summary', 'space', 'description','name', 'host_id', 'host_name', 'host_location', 'host_about', 
             'host_neighbourhood', 'neighborhood_overview', 'neighbourhood', 'notes', 'market', 
             'street', 'city', 'zipcode', 'smart_location', 'latitude', 'longitude', 'weekly_price', 'monthly_price', 
             'host_acceptance_rate', 'calendar_updated', 'access', 'interaction', 'host_acceptance_rate', 'house_rules']

lis_bos_nec = lis_bos_nounq.drop(columns=unnec_col)

# dropping columns with majority on nan values 
lis_bos_new = lis_bos_nec.drop(columns=['square_feet'])

# dropping rows with nans in review_scores_ratings, host_response_rate, review_scores_accuracy and review_scores_location
cols_with_nans = ['review_scores_rating', 'host_response_rate', 'review_scores_accuracy', 'review_scores_location']

for col in cols_with_nans:
    lis_bos_new.dropna(axis=0, how='any', subset=[col], inplace=True)

# data conversion
# float columns
lis_bos_clean = lis_bos_new
float_cols = ['host_response_rate', 'price', 'security_deposit', 'cleaning_fee', 'extra_people']

for col in float_cols:
    lis_bos_clean.loc[:,col] = lis_bos_clean[col].str.replace(r'[\%\$\,]', '', regex=True).astype(float)

# boolean columns
lis_bos_clean.replace('t', 1, inplace=True)
lis_bos_clean.replace('f', 0, inplace=True)

# compute null values
null_cols = ['bedrooms', 'bathrooms', 'security_deposit', 'cleaning_fee', 'beds']

for col in null_cols:
    lis_bos_clean[col].fillna(1 if col is 'bathrooms' else 0, inplace=True) # as there can be homes w/o bedrooms
    #and all houses are entire building apartments or single rooms, so there has to be a bathroom

# create new features
lis_bos_clean['host_seniority'] = (pd.to_datetime(lis_bos_clean['last_scraped']) - pd.to_datetime(lis_bos_clean['host_since'])).dt.days
lis_bos_clean['days_since_first_review'] = (pd.to_datetime(lis_bos_clean['last_scraped']) - pd.to_datetime(lis_bos_clean['first_review'])).dt.days
lis_bos_clean['days_since_last_review'] = (pd.to_datetime(lis_bos_clean['last_scraped']) - pd.to_datetime(lis_bos_clean['last_review'])).dt.days
lis_bos_clean.drop(columns=['last_scraped', 'host_since', 'first_review', 'last_review'], inplace = True)

# extract features from categorical columns
bos_lis_feat = extract_features(lis_bos_clean, ['amenities', 'host_verifications'])
one_hot_columns = ['host_response_time', 'neighbourhood_cleansed', 'property_type', 'room_type', 'bed_type', 'cancellation_policy']
bos_lis_ohe = pd.get_dummies(data = bos_lis_feat, columns = one_hot_columns, dummy_na = False)
bos_lis_ohe.head()

# one hot encoding for transportation
transport_means = ['Bus', 'Light Rail', 'Ferry', 'Heavy Rail', 'Train', 'Metro', 'NaN']

bos_lis_ohe['transit'].fillna("NaN", inplace=True)

for mean in transport_means:
    booleans = bos_lis_ohe['transit'].str.contains(mean, False)    
    new_col = [int(boolean) for boolean in booleans]
    new_col_name = 'transit_{}'.format(mean)
    bos_lis_ohe[new_col_name] = new_col

bos_lis_ohe.drop(columns='transit', inplace=True)

# dataframe ready for prediction
bos_lis_pred = bos_lis_ohe.drop(columns='id')
bos_lis_pred.head()<jupyter_output><empty_output><jupyter_text>Let's predict house pricesNow that both the dataframes are ready for the analysis, let's run some predicting models and see their output. The models I'll be using are **Linear Regression**, **Random Forest Regression** and **Extreme Gradient Boost**. In order to measure the goodness of the predicted prices I'll look at two measures: the root-mean-square error ($RMSE$) and R-squared ($R^2$), or coefficient of determination. The first is the square root of the mean-square-error ($MSE$), which is the average of the squared difference between predicted and observed data, the second defines the proportion of variance in the target variable that can be explained by the remaining variables, and it is used to determine how well the regression model fits the observed data. Let's start with Seattle first.  Linear Regression<jupyter_code># linear regression
X = df_lis_pred.drop(columns='price')
y = df_lis_pred.price

# split the data into train and test
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)

# fit the model and obtain pred response
lm_model = LinearRegression(normalize=True)
lm_model.fit(X_train, y_train)
y_pred = lm_model.predict(X_test)
y_train_preds = lm_model.predict(X_train)

# calculate goodness of model on train and test data
print('Test:', math.sqrt(mean_squared_error(y_test, y_pred)), r2_score(y_test, y_pred))
print('Train:', math.sqrt(mean_squared_error(y_train, y_train_preds)), r2_score(y_train, y_train_preds))<jupyter_output>Test: 4246148064960.8887 -1.9724666084407035e+21
Train: 46.12020912272431 0.6919564047720081<jupyter_text>Random Forest<jupyter_code># random forest regressor
rfr = RandomForestRegressor(n_estimators=250, random_state=42)
rfr.fit(X_train, y_train)

y_pred_rfr = rfr.predict(X_test)
y_pred_train = rfr.predict(X_train)

print('Test:', math.sqrt(mean_squared_error(y_test, y_pred_rfr)), r2_score(y_test, y_pred_rfr))
print('Train:', math.sqrt(mean_squared_error(y_train, y_pred_train)), r2_score(y_train, y_pred_train))<jupyter_output>Test: 59.201772488936314 0.6165675974635467
Train: 17.908323469352624 0.9535549421405304<jupyter_text>Extreme Gradient Boost<jupyter_code># xgboost
#!conda install -c conda-forge xgboost --yes
#!conda install -c anaconda py-xgboost

xgb_reg = xgb.XGBRegressor(
    n_estimators=250,
    reg_lambda=10,
    gamma=0,
    max_depth=3,
    objective='reg:squarederror',
    random_state=42
)

xgb_reg.fit(X_train, y_train)

y_pred_xgb = xgb_reg.predict(X_test)
y_pred_train = xgb_reg.predict(X_train)

print('Test:', math.sqrt(mean_squared_error(y_test, y_pred_xgb)), r2_score(y_test, y_pred_xgb))
print('Train:', math.sqrt(mean_squared_error(y_train, y_pred_train)), r2_score(y_train, y_pred_train))<jupyter_output>C:\Users\Duratorre\Anaconda\lib\site-packages\xgboost\core.py:587: FutureWarning: Series.base is deprecated and will be removed in a future version
  if getattr(data, 'base', None) is not None and \<jupyter_text>Let's apply the same three regression techniques above to the Boston dataframe as well.<jupyter_code>X = bos_lis_pred.drop(columns='price')
y = bos_lis_pred.price

# split the data into train and test
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)

# linear regression
lm_model = LinearRegression(normalize=True)
lm_model.fit(X_train, y_train)
y_pred = lm_model.predict(X_test)
y_train_preds = lm_model.predict(X_train)

print('LR Test:', math.sqrt(mean_squared_error(y_test, y_pred)), r2_score(y_test, y_pred))
print('LR Train:', math.sqrt(mean_squared_error(y_train, y_train_preds)), r2_score(y_train, y_train_preds))

# random forest 
rfr = RandomForestRegressor(n_estimators=100)
rfr.fit(X_train, y_train)

y_pred_rfr = rfr.predict(X_test)
y_pred_train_rfr = rfr.predict(X_train)

print('RF Test:', math.sqrt(mean_squared_error(y_test, y_pred_rfr)),r2_score(y_test, y_pred_rfr))
print('RF Train:', math.sqrt(mean_squared_error(y_train, y_pred_train_rfr)),r2_score(y_train, y_pred_train_rfr))

# xgboost
xgb_reg = xgb.XGBRegressor(
    n_estimators=250,
    reg_lambda=10,
    gamma=0,
    max_depth=3,
    random_state=42
)

xgb_reg.fit(X_train, y_train)

y_pred_xgb = xgb_reg.predict(X_test)
y_pred_train = xgb_reg.predict(X_train)

print('XGB Test:', math.sqrt(mean_squared_error(y_test, y_pred_xgb)), r2_score(y_test, y_pred_xgb))
print('XGB Train:', math.sqrt(mean_squared_error(y_train, y_pred_train)), r2_score(y_train, y_pred_train))<jupyter_output>LR Test: 62.38726851429946 0.6743285445970504
LR Train: 57.26238227763678 0.7337824231545175
RF Test: 58.56741988807431 0.7129880783548247
RF Train: 22.58050688096095 0.9586033425707791
[00:40:14] WARNING: src/objective/regression_obj.cu:152: reg:linear is now deprecated in favor of reg:squarederror.
{'fraction_non_alphanumeric': 0.09168146366680902, 'fraction_numerical': 0.01667753028702434, 'mean_word_length': 5.295740766944955, 'pattern_counts': {'":': 0, '<': 125, '<?xml version=': 0, '>': 127, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '34', 'dir': 'jupyter-structured-clean-dedup', 'id': '648339', 'license': 'zlib-acknowledgement', 'n_tokens_mistral': 14679, 'n_tokens_neox': 13533, 'n_words': 4575, 'path': 'Analysis.ipynb', 'repo_name': 'Duratorre/AirBnB-Data-Analysis', 'size': '44491'}