<jupyter_start><jupyter_text>Airbnb NYC the big apple analysis  Business Understanding New York City if not the most, certainly is on top 5, of visited cities in the world. The objective of this analysis is to understand if it's possible to predeterminate a review of the guest based on the characteristics of the house:* How to create a new metric to correlate the overall score and the number of reviews?* This metric will be better to predict the overall score?* Is the features of the house enough to determinate the overall score? Import libraries<jupyter_code>#import main libraries

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import re
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split
from sklearn.metrics import r2_score, mean_squared_error
from math import e
import sklearn.metrics as sm
import seaborn as sns
import functions as f

%matplotlib inline<jupyter_output><empty_output><jupyter_text>Data Understanding - The Dataset<jupyter_code>#get main dataset
dtypes = {'Listing ID': 'int',
          'Name': 'str',
          'Host ID': 'int',
          'Host Name': 'str',
          'Host Response Rate' : 'float',
          'Host Is Superhost': 'bool',
          'Host total listings count' : 'float',
          'Street' : 'str', 
          'City' : 'str', 
          'Neighbourhood cleansed' : 'str',
          'State' : 'str', 
          'Country' : 'str',
          'latitude': 'float',
          'longitude': 'float',
          'Property type': 'str',
          'Room type': 'str',
          'Accommodates': 'float',
          'Bathrooms': 'float',
          'Bedrooms': 'float',
          'Amenities': 'str',
          'Price': 'float',
          'Minimum nights': 'float',
          'Maximum nights': 'float',
          'Availability 365': 'float',
          'Calendar last scraped': 'object',
          'Number of reviews': 'float',
          'Last Review Date': 'object',
          'Review Scores Rating': 'float',
          'Review Scores Accuracy': 'float',
          'Review Scores Cleanliness': 'float',
          'Review Scores Checkin': 'float',
          'Review Scores Communication' : 'float',
          'Review Scores Location': 'float',
          'Review Scores Value': 'float',
          'Reviews per month': 'float'

}

df = pd.read_csv("./Datasets/NY_Listings.csv", sep=",", encoding = "ISO8859-1", dtype =dtypes)

df.head()
df.shape
df[['Accommodates',
       'Bathrooms', 'Bedrooms', 'Price', 'Minimum nights',
       'Maximum nights', 'Availability 365']].describe()
df[['Number of reviews', 'Last Review Date', 'Review Scores Rating',
       'Review Scores Accuracy', 'Review Scores Cleanliness',
       'Review Scores Checkin', 'Review Scores Communication',
       'Review Scores Location', 'Review Scores Value',
       'Reviews per month']].describe()<jupyter_output><empty_output><jupyter_text>Data prepration - Cleaning the dataset<jupyter_code>analysisDataFrame = df
sns.boxplot(x=analysisDataFrame['Review Scores Rating'])
analysisDataFrame = analysisDataFrame[analysisDataFrame["Review Scores Rating"] != 0]

analysisDataFrame = analysisDataFrame[analysisDataFrame["Review Scores Rating"] < 100]
sns.boxplot(x=analysisDataFrame['Review Scores Rating'])<jupyter_output><empty_output><jupyter_text>Treat Categorical variables:Treat each categorical variable indivdually:* Last Review Date: when null put the oldest review date.* Bedrooms: Relate with Property Type and get mean.* Accommodates: Relate with Bedrooms and Propoerty Type and get the mean.* Bathrooms: Relate with Accommodates, Bedrooms and Property type and get the mean.* Host Response Rate: Empty should be zero responses.* Host total listings count: Number of houses of each Host, null should be 1.* Name and Host Name: Should be empty, they will not used on model.<jupyter_code>countRateNA = lambda col : col.isna().sum()/col.shape[0]

nullColumns = analysisDataFrame.apply(countRateNA,axis=0)
nullColumns = nullColumns.sort_values(ascending = False)

nullColumns = nullColumns[~(nullColumns.where(nullColumns>0).isnull())]

nullColumns
analysisDataFrame["Last Review Date"]= pd.to_datetime(analysisDataFrame["Last Review Date"])

analysisDataFrame['Last Review Date'].fillna(min(analysisDataFrame["Last Review Date"]), inplace=True)
analysisDataFrame['Bedrooms'] = analysisDataFrame.groupby(['Property type'], sort=False)['Bedrooms'].apply(lambda x: x.fillna(x.mean()))

analysisDataFrame['Bedrooms'].fillna((analysisDataFrame['Bedrooms'].mean()), inplace=True)
analysisDataFrame['Accommodates'] = analysisDataFrame.groupby(['Property type', 'Bedrooms'], sort=False)['Accommodates'].apply(lambda x: x.fillna(x.mean()))

analysisDataFrame['Accommodates'].fillna((analysisDataFrame['Accommodates'].mean()), inplace=True)
analysisDataFrame['Bathrooms'] = analysisDataFrame.groupby(['Property type', 'Bedrooms','Accommodates'], sort=False)['Bathrooms'].apply(lambda x: x.fillna(x.mean()))

analysisDataFrame['Bathrooms'].fillna((analysisDataFrame['Bathrooms'].mean()), inplace=True)
analysisDataFrame['Host Response Rate'].fillna(0, inplace=True)
analysisDataFrame['Host total listings count'].fillna(1, inplace=True)
analysisDataFrame['Name'].fillna("NA", inplace=True)

analysisDataFrame['Host Name'].fillna("NA", inplace=True)
def pars_string(df,col1):
    
"""
Description: This function get a list of all possible values on a dataframe column.

Arguments:
    df: dataframe with the column to get the list
    filepath: the column where to get the list

Returns:
    the list with unique values
"""
    
    splitCol=df[col1].value_counts().reset_index()
    splitCol['index']=splitCol['index'].str.split(";")
    # get the list from all of the answer
    df1=[]
    for i in range(splitCol.shape[0]):
        for j in splitCol['index'][i]:
            df1.append(j)
    # get unique value from df1
    list_1=list(set(df1))
    # there are some leading or trailing spaces in the list_1 which need to be deleted to get unique value 
    list_2=[x.strip(' ') for x in list_1]
    # get unique value from list_2
    list_3=list(set(list_2))
    
    return(list_3)

Amenities = pars_string(analysisDataFrame,"Amenities")

analysisDataFrame["Amenities"] = analysisDataFrame["Amenities"].str.replace('[^A-Za-z0-9 ;]+', '')

for Amenitie in Amenities:
    #print(Amenitie)
    
    Amenitie = re.sub('[^A-Za-z0-9 ;]+', '', Amenitie)
    
    analysisDataFrame = pd.concat([analysisDataFrame, (analysisDataFrame["Amenities"].str.contains(Amenitie)*1).rename(Amenitie)], axis=1)
    analysisDataFrame[Amenitie] = analysisDataFrame[Amenitie].fillna(0)


analysisDataFrame = analysisDataFrame.drop(columns=["Amenities"])
analysisDataFrame.head()
nullColumns = analysisDataFrame.apply(countRateNA,axis=0)
nullColumns = nullColumns.sort_values(ascending = False)

nullColumns = nullColumns[~(nullColumns.where(nullColumns>0).isnull())]

nullColumns.shape
analysisDataFrame.columns.values<jupyter_output><empty_output><jupyter_text>Modeling - The Review Scores Rating<jupyter_code>analysisDataFrameRanked = analysisDataFrame
Q = 144

analysisDataFrameRanked["ScoreReviewScoresRating"] = 50 * analysisDataFrameRanked["Review Scores Rating"] / 100 + 50 * ( 1 - e** ( - analysisDataFrameRanked["Number of reviews"] / Q))

analysisDataFrameRanked.head()
analysisDataFrameRanked["Review Scores Rating"].hist();
analysisDataFrameRanked["ScoreReviewScoresRating"].describe()
analysisDataFrameRanked["ScoreReviewScoresRating"].hist();
analysisDataFrameRanked.groupby(["Host Is Superhost"])["ScoreReviewScoresRating"].mean()
analysisDataFrameRanked.groupby(["Property type"])["ScoreReviewScoresRating"].mean().sort_values(ascending=False)
analysisDataFrameRanked.groupby(["Neighbourhood cleansed"])["ScoreReviewScoresRating"].mean().sort_values(ascending=False)
analysisDataFrameRanked.groupby(["Number of reviews"])["Review Scores Rating"].mean().sort_values(ascending=False)
analysisDataFrameRegression= analysisDataFrameRanked[['Host Response Rate','Number of reviews',
       'Host Is Superhost', 'Host total listings count', 'City', 'latitude',
       'longitude', 'Accommodates', 'Bathrooms', 'Bedrooms', 'Price',
       'Minimum nights', 'Maximum nights', 'Availability 365',
       'Hot tub', 'BBQ grill',
       'Game console', 'Essentials', 'Firm mattress', 'EV charger',
       'Ethernet connection', 'Bathtub with shower chair',
       'Disabled parking spot', 'Beach essentials', 'Suitable for events',
       'Doorman', 'Private entrance', 'Dishwasher',
       'Wide clearance to bed', 'Smart lock', 'Childrens books and toys',
       'Cooking basics', 'Elevator', 'Air conditioning', 'Crib',
       'Hot water', 'Oven', 'Hair dryer', 'First aid kit', 'Stove',
       'Roomdarkening shades', 'Heating', 'Extra pillows and blankets',
       'Luggage dropoff allowed', 'Wheelchair accessible', 'Baby bath',
       'Shampoo', 'Iron', 'Ground floor access', 'Dryer', 'Breakfast',
       'Washer  Dryer', 'Lockbox', 'Internet', 'Other',
       'Cleaning before checkout', 'Microwave',
       'translation missing enhostingamenity50', 'Pets allowed',
       'High chair', 'Buzzerwireless intercom', 'Fireplace guards',
       'Pool', 'Coffee maker', 'Wide hallway clearance', 'Gym',
       '24hour checkin', 'Accessibleheight bed',
       'smooth pathway to front door', 'Welllit path to entrance',
       'Pack n Playtravel crib', 'Self CheckIn', 'Wide doorway',
       'Familykid friendly', 'Lock on bedroom door',
       'Free parking on premises', 'Wireless Internet',
       'Free parking on street', 'Bed linens',
       'Fixed grab bars for shower  toilet', 'Stair gates',
       'Air purifier', 'Pets live on this property',
       'Dishes and silverware', 'Keypad', 'Private bathroom',
       'Table corner guards', 'Cats', 'Single level home',
       'Accessibleheight toilet', 'Outlet covers', 'Smoking allowed',
       'Babysitter recommendations', 'Indoor fireplace', 'Ski inSki out',
       'Flat', 'Rollin shower with chair', 'Fire extinguisher',
       'Host greets you', 'Washer', 'Handheld shower head',
       'Wide entryway', 'Laptop friendly workspace', 'Hangers',
       'Private living room', 'Carbon monoxide detector',
       'translation missing enhostingamenity49',
       'Wide clearance to shower  toilet', 'Bathtub', 'Dogs',
       'Beachfront', 'Other pets', 'Lake access', 'Patio or balcony',
       'Waterfront', 'Childrens dinnerware', 'TV', 'Cable TV',
       'Garden or backyard', 'Pocket wifi', 'Long term stays allowed',
       'Kitchen', 'Smoke detector', 'Safety card', 'Window guards',
       'Hot water kettle', 'Refrigerator', 'Baby monitor',
       'Stepfree access', 'Changing table','Review Scores Rating', 'ScoreReviewScoresRating']]<jupyter_output><empty_output><jupyter_text>Evaluation<jupyter_code>cat_cols = analysisDataFrame.select_dtypes(include=['object']).columns

def create_dummy(df, cat_cols, dummy_na):
"""
Description: create new columns in the dataset with the categorical columns, each will be a new column with 0 or 1.

Arguments:
    df: dataframe with the column to get the values
    cat_cols: the categorical columns to create the columns
    dummy_na: A bool to create or not a new column where exists NaN values

Returns:
    the list with unique values
"""

        for col in  cat_cols:
            try:
                # for each cat add dummy var, drop original column
                df = pd.concat([df.drop(col, axis=1), pd.get_dummies(df[col], prefix=col, prefix_sep='_', drop_first=True, dummy_na=dummy_na)], axis=1)
            except:
                continue
        return df
    
analysisDataFrameRegression = create_dummy(analysisDataFrameRegression, cat_cols, False)
analysisDataFrameRegression.head()
col_to_predict = "ScoreReviewScoresRating"

print(analysisDataFrameRegression.shape)


X = analysisDataFrameRegression.drop(col_to_predict, axis=1)
y = analysisDataFrameRegression[col_to_predict]
X = X.drop("Review Scores Rating", axis=1) 

lm_model = LinearRegression(normalize=True) # Instantiate

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=.30, random_state = 50)

lm_model.fit(X_train, y_train) #Fit

y_train_preds = lm_model.predict(X_test)

#Score using your model
train_r2 = r2_score(y_test, y_train_preds)
train_mean_squared_error=mean_squared_error(y_test, y_train_preds)

print(train_r2)
print(train_mean_squared_error)

col_to_predict = "ScoreReviewScoresRating"

print(analysisDataFrameRegression.shape)


X = analysisDataFrameRegression.drop(col_to_predict, axis=1)
y = analysisDataFrameRegression[col_to_predict]

X = X.drop("Number of reviews", axis=1)
X = X.drop("Review Scores Rating", axis=1) 

lm_model = LinearRegression(normalize=True) # Instantiate

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=.30, random_state = 50)    

lm_model.fit(X_train, y_train) #Fit

y_train_preds = lm_model.predict(X_test)

#Score using your model
train_r2 = r2_score(y_test, y_train_preds)
train_mean_squared_error=mean_squared_error(y_test, y_train_preds)

print(train_r2)
print(train_mean_squared_error)

col_to_predict = "Review Scores Rating"

print(analysisDataFrameRegression.shape)


X = analysisDataFrameRegression.drop(col_to_predict, axis=1)
y = analysisDataFrameRegression[col_to_predict]
X = X.drop("ScoreReviewScoresRating", axis=1) 

lm_model = LinearRegression(normalize=True) # Instantiate

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=.30, random_state = 50)    

lm_model.fit(X_train, y_train) #Fit

y_train_preds = lm_model.predict(X_test)

#Score using your model
train_r2 = r2_score(y_test, y_train_preds)
train_mean_squared_error=mean_squared_error(y_test, y_train_preds)

print(train_r2)
print(train_mean_squared_error)

col_to_predict = "Review Scores Rating"

print(analysisDataFrameRegression.shape)


X = analysisDataFrameRegression.drop(col_to_predict, axis=1)
y = analysisDataFrameRegression[col_to_predict]

X = X.drop("Number of reviews", axis=1)
X = X.drop("ScoreReviewScoresRating", axis=1) 

lm_model = LinearRegression(normalize=True) # Instantiate

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=.30, random_state = 50)    

lm_model.fit(X_train, y_train) #Fit

y_train_preds = lm_model.predict(X_test)

#Score using your model
train_r2 = r2_score(y_test, y_train_preds)
train_mean_squared_error=mean_squared_error(y_test, y_train_preds)

print(train_r2)
print(train_mean_squared_error)<jupyter_output>(24451, 135)
0.1500540813130954
62.6556953476431
{'fraction_non_alphanumeric': 0.12043720354712312, 'fraction_numerical': 0.010242661717192549, 'mean_word_length': 4.9258655804480656, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '547349', 'license': 'MIT', 'n_tokens_mistral': 4753, 'n_tokens_neox': 4480, 'n_words': 1192, 'path': 'analysisAirbnbNYC.ipynb', 'repo_name': 'rdomingues/AirbnbNYC', 'size': '14547'}