<jupyter_start><jupyter_text>In this notebook, I will analyse a dataset to see how data analysis can help us to get insights from the data. I selected one of the famous opened datasets in the internet which is Airbnb dataset. I will use [Boston Airbnb](https://www.kaggle.com/airbnb/boston/) and [Seattle Airbnb](https://www.kaggle.com/airbnb/seattle/data) datasets. Here, I will try to answer four questions which are:- What is most common property type for rent in each state?- What aspects correlate well to the listing review scores rating?- What is the average home rental price in Seattle and Boston? and in which seasons do the prices spike?- How can I predict the listings prices in Boston and Seattle?  In order to answer the questions, I will follow the CRISP-DM process which are: Business understanding, Data understanding, Data preparation, Modeling, Evaluation, Deployment.*I will not apply the last step.*   Data UnderstandingThe goal of this step is to understand what you want to accomplish from a business perspective and to set the project objectives.  The project objectives are:- Identifying  the most presented property type for rent in each state.- Finding the relations between the listing review score and the other features in the dataset.- Identifying  the average of the home renting prices in Seattle and Boston and knowing in which seasons the prices spike.- Predicting the listings prices in Boston and Seattle.<jupyter_code># Import files
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from IPython import display
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split
from sklearn.metrics import r2_score
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Data UnderstandingThis step includes data loading and data exploration.<jupyter_code># Read the head of Boston Airbnb listing data 
df_boston = pd.read_csv('./boston-airbnb/listings.csv')
df_boston.head()
# Read the head of Seattle Airbnb listing data 
df_seattle = pd.read_csv('./seattle-airbnb/listings.csv')
df_seattle.head()
# The size of Boston dataset 
df_boston.shape
# The size of Seattle dataset 
df_seattle.shape<jupyter_output><empty_output><jupyter_text>We can notice that the number of coulmns are different, so, I would like to explore the coulmns of each dataset and then delete the differnet columns in Boston dataset.<jupyter_code>df_boston.info()
df_seattle.info()
# Read the head of Boston Airbnb calendar data 
df_boston_cal = pd.read_csv('./boston-airbnb/calendar.csv')
df_boston_cal.head()
# Read the head of Seattle Airbnb calendar data 
df_seattle_cal = pd.read_csv('./seattle-airbnb/calendar.csv')
df_seattle_cal.head()
df_boston_cal.info()
df_boston_cal.shape
df_seattle_cal.info()
df_seattle_cal.shape<jupyter_output><empty_output><jupyter_text>Data PreperationThis step inludes data selection and data cleaning. I will remove some columns that I think they are not important in the project. I will also do some necessary data preparation as:- Deleting the different coulmns in Boston dataset. - Exploring the missing values in each column, and finding the columns that have no missing values and the cloumns that have more than 50% missing values.- Converting the price coulmns from object to float.- Converting the date coulmns from object to datetime.*Some of the preperation steps will be done when answering the questions.*<jupyter_code># Find the different columns

boston_columns = df_boston.columns
seattle_columns = df_seattle.columns

differ_col = []
print('The differnt columns are:')
 
for col in boston_columns:
    if col not in seattle_columns:
        differ_col.append(col)

for col in seattle_columns:
    if col not in boston_columns:
        differ_col.append(col)
        
print(differ_col)        
        
# Delete the differnt coulumns from Boston dataset
df_boston = df_boston.drop(differ_col, axis = 1)
df_boston.describe()
df_seattle.describe()
# Find the columns that have no missing values in Boston listing dataset
df_boston.columns[df_boston.isnull().sum() == 0]
# Find the columns that have no missing values in Seattle listing dataset
df_seattle.columns[df_seattle.isnull().sum() == 0]<jupyter_output><empty_output><jupyter_text>**Note:** I can see that the columns that do not have missing values are almost same in the two datasets.<jupyter_code># Find cloumns that have more than 50% missing values in Boston listing dataset
df_boston.columns[df_boston.isnull().sum() > df_boston.shape[0] * 0.5]
# Find cloumns that have more than 50% missing values in Seattle listing dataset
df_seattle.columns[df_seattle.isnull().sum() > df_seattle.shape[0] * 0.5]<jupyter_output><empty_output><jupyter_text>**Note:** The Boston dataset has nine columns that have 50% missing values whearas Seattle dataset has four columns that have 50% missing values. I will delete all the nine columns from the two dataset.<jupyter_code># Delete cloumns that have more than 50% missing values in Boston and Seattle listing datases
drop_col = df_boston.columns[df_boston.isnull().sum() > df_boston.shape[0] * 0.5]
df_boston = df_boston.drop(drop_col, axis=1)
df_seattle = df_seattle.drop(drop_col, axis=1)
# Convert price to float

def convert_price_to_float(df, col):
    
    '''
    This function converts the price column from to float dtype
    
    INPUT:
    df - a dataframe
    col - a price coulmn

    '''
    # Remove the $ from the price and then converting it to float
    df[col] = df[col].replace('[\$,]', '', regex=True).astype(float)
     
    
convert_price_to_float(df_boston, 'price')
convert_price_to_float(df_seattle, 'price')
convert_price_to_float(df_boston_cal, 'price')
convert_price_to_float(df_seattle_cal, 'price')
# Convert date columns to datetime dtype for easy analysis.

def convert_to_date(col):
    '''
    This function converts the date column from object dtype to datetime dtype
    
    INPUT:
    col - a dataframe column which has object dtype 
    
    OUTPUT:
    col - a dataframe column which has datetime dtype
    '''
    col = pd.to_datetime(col)
    return col
# Convert to datetime dtype

df_boston['last_scraped'] = convert_to_date(df_boston['last_scraped'])
df_seattle['last_scraped'] = convert_to_date(df_seattle['last_scraped'])

df_boston['host_since'] = convert_to_date(df_boston['host_since'])
df_seattle['host_since'] = convert_to_date(df_seattle['host_since'])

df_boston['last_review'] = convert_to_date(df_boston['last_review'])
df_seattle['last_review'] = convert_to_date(df_seattle['last_review'])

df_boston['first_review'] = convert_to_date(df_boston['first_review'])
df_seattle['first_review'] = convert_to_date(df_seattle['first_review'])

df_boston_cal['date'] = convert_to_date(df_boston_cal['date'])
df_seattle_cal['date'] = convert_to_date(df_seattle_cal['date'])
df_boston.info()
df_seattle.info()
df_boston_cal.info()
df_seattle_cal.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 1393570 entries, 0 to 1393569
Data columns (total 4 columns):
listing_id    1393570 non-null int64
date          1393570 non-null datetime64[ns]
available     1393570 non-null object
price         934542 non-null float64
dtypes: datetime64[ns](1), float64(1), int64(1), object(1)
memory usage: 42.5+ MB<jupyter_text>ModelingThe goal of this step is to build a model to answer the questions. In the first three, I do not need to use a machine learning model so, I will use some statistical techniques with visualization to answer the question. For answering the last question, I will use a machine learning model.  Question 1: What is the most common property type for rent in each state?To answer this question, I will use viualization.<jupyter_code># Show the most rented property type in Boston
prop_typ_val = pd.Series(df_boston['property_type'].value_counts())
(prop_typ_val/df_boston.shape[0]).plot(kind = 'bar');
plt.title('The most common property type in Boston')
plt.xlabel('Property type')
plt.ylabel('Frequency')
# Show the most rented property type in Seattle
prop_typ_val = pd.Series(df_seattle['property_type'].value_counts())
(prop_typ_val/df_seattle.shape[0]).plot(kind = 'bar');
plt.title('The most common property type in Seattle')
plt.xlabel('Property type')
plt.ylabel('Frequency')<jupyter_output><empty_output><jupyter_text>**The answer:**As we can see, there are different type of property in Seattle and Boston. The most presented property type in Boston is "Apartment", more than 70% of the presented property in Boston is "Apartment". The second most presented property type in Boston is "House" property type comes at the second. As for Seattle, The most presented property type in it is "House", arround 45% of the presented property types are "House" and also arround 45% of them are "Apartment".  Question 2: What aspects correlate well to the listing review scores rating? and what is the average of the review scores in Boston and Seattle?To answer this question,firstly, I will do some data preperation, I will check the null values in review_scores_rating column and delete them. Then, I will try to find the relation between the review scores and bed type, room type and price. After that, I will  claculate the average of the review scores in Boston and Seattle.<jupyter_code># Calculate the number and proportion of missing values of the review scores in Boston dataset
null_review_boston = df_boston['review_scores_rating'].isnull().sum()
print(null_review_boston)
print(null_review_boston/df_boston.shape[0])
# Delete the missing values of the review scores in Boston dataset
df_boston = df_boston.dropna(subset=['review_scores_rating'])
# Calculating the number and proportion of missing values of the review scores in Seattle dataset
null_review_seattle = df_seattle['review_scores_rating'].isnull().sum()
print(null_review_seattle)
print(null_review_seattle/df_seattle.shape[0])
# Delete the missing values of the review scores in Boston dataset
df_seattle = df_seattle.dropna(subset=['review_scores_rating'])
df_boston.groupby(['room_type']).mean()['review_scores_rating']
df_seattle.groupby(['room_type']).mean()['review_scores_rating']<jupyter_output><empty_output><jupyter_text>**Note:** I could not say there is a relation between the review scores and the room type. As you can see, there is less than a 2% difference between the average of the review scores in the two states.<jupyter_code>df_boston.groupby(['bed_type']).mean()['review_scores_rating']
df_seattle.groupby(['bed_type']).mean()['review_scores_rating']<jupyter_output><empty_output><jupyter_text>**Note:** The listings with couch bed type in Boston has lower review scores.<jupyter_code>df_boston.plot.scatter(x ='review_scores_rating', y ='price')
plt.title('The correlation between the listing price and the review scores rating in Boston')
plt.xlabel("Review Score rating")
plt.ylabel("price")
plt.show()
df_seattle.plot.scatter(x ='review_scores_rating', y ='price')
plt.title('The correlation between the listing price and the review scores rating in Seattle')
plt.xlabel("Review Score rating")
plt.ylabel("price")
plt.show()<jupyter_output><empty_output><jupyter_text>**Note:** I have noticed the following:- In Boston, the listings with reviews scores ratings less than 50%, its prices are less than \$400, and the listings with prices greater than \$800 have reviews scores of 100%. - In Seattle, the listings with prices greater than \$800 have review scores greater than 92%. This is because the expensive listings often offer excellent services and this makes customers give these listings high reviews scores.<jupyter_code>df_boston['review_scores_rating'].mean()
df_seattle['review_scores_rating'].mean()<jupyter_output><empty_output><jupyter_text>**The answer:**There is a correlation between the listings prices and the reviews scores ratings. The average of review scores rating in Seattle (94.5%) is greater than the average of review scores rating in Boston (91.9%).  Question 3: What is the average home rental price in Seattle and Boston? and in which seasons do the prices spike?To answer this question, firstly, I will calculate the mean of the prices, then, I will use viualization to see if there is a spike in price in a season. I will use listing and calendar datasets.<jupyter_code>print('The average of home renting prices in Boston is ${}'.format(round(df_boston['price'].mean(),2)))
print('The average of home renting prices in Seattle is ${}'.format(round(df_seattle['price'].mean(),2)))
df_boston_price = df_boston[['price']]
df_seattle_price = df_seattle[['price']]
fig = plt.figure(figsize=(16, 8))
ax = fig.add_subplot(111)
ax.plot(df_boston_price, label = 'Boston')
ax.plot(df_seattle_price, label = 'Seattle')
ax.legend()
plt.title('The renting prices in Boston and Seattle')
plt.xlabel('Listings')
plt.ylabel('Price $')<jupyter_output><empty_output><jupyter_text>**Note:** As you can see, in general, the prices of the home renting in Boston is higher than the prices of the home renting in Seattle.  To investigate in which seasons the prices spike, I will use the calender dataset. I have noticed that the range of the date in the Boston dataset is from Sep 2016 to Sep 2017, and the range of the date in the Seattle dataset is from Jan 2016 to Jan 2017. So, I will use day of year instead of date.<jupyter_code># Use the day of year and then group the table by day of year. 
 
df_boston_cal['doy'] = df_boston_cal['date'].dt.dayofyear.astype(int)
year_boston_price = df_boston_cal.groupby(['doy']).mean()
year_boston_price = year_boston_price.drop(['listing_id'], axis = 1)

df_seattle_cal['doy'] = df_seattle_cal['date'].dt.dayofyear.astype(int)
year_seattle_price = df_seattle_cal.groupby(['date']).mean()
year_seattle_price = year_seattle_price.drop(['listing_id'], axis = 1)
# Reset index
year_boston_price = year_boston_price.reset_index()
year_seattle_price = year_seattle_price.reset_index()
fig = plt.figure(figsize=(12, 6))
ax = fig.add_subplot(111)
ax.plot(year_boston_price['price'], label = 'Boston', color = 'magenta')
ax.plot(year_seattle_price['price'], label = 'Seattle', color = 'cyan')
ax.legend()
plt.title('Prices at day of year in Boston and Seattle')
plt.xlabel('Day of year')
plt.ylabel('Price $')<jupyter_output><empty_output><jupyter_text>**The answer:** The average home rental prices in Boston is \$166.68, while the average in Seattle is \$126.8.  In Boston, the prices spike in September and May, and in general, the prices in September, October, November are higher than the other months of the year. In Seattle, the prices increase in June and start to decrease in September.  Question 4: How can I predict the price listings prices in Boston and Seattle? The goal of this question is to build a model for prices prediction using LinearRegression model. I will use LinearRegression model because it is used to measure the relationship between predictor variables and response variable which is a continuous variable. Before creating the model, I need to some data preperation techinques. In this part, I will do the following:- Select some features that I think they are related to the price.- Change the amenities column from a multi value column to single value columns.- Change categorical column to dummy categorical columns.- Fill the mean of the column for any missing values.- Create training and test sets of data.- Instantiate a LinearRegression model with normalized data.- Fit the model to the training data.- Calculate the rscore value for both the training and test data.- Identify the coefficient of the selected features.<jupyter_code># Insert a list of a variables that I think it affects the price

var = ['neighbourhood_cleansed', 'property_type', 'room_type', 'bathrooms', 'bedrooms', 'amenities', 'price']

# Create a dataframe using the variables
df_boston_model = df_boston[var]
df_seattle_model = df_seattle[var]
df_boston_model.head()
df_seattle_model.head()
# Change the amenities column from a multi value column to single value columns

def preprocess_amenities_col(df):
    '''
    This function converts the amenities column from a multi value column to single value columns that have bool dtype
    
    INPUT:
    df - a dataframe that has amenities column
    
    OUTPUT:
    new_df - a dataframe that has columns of amenities that have bool dtype
    unique_amenities - a list of amenities
    '''
    
    new_df = df
    
    # Remove {, }, " charecters from the amenities column
    repl = ['{', '}', '"']
    for ch in repl:
        new_df['amenities'] = new_df['amenities'].str.replace(ch,'')
        
    # Split the column contents in ',' and put the contents in a list
    amenities_lists = new_df['amenities'].map(lambda val: val.split(",")).values
    
    # Concatenate all lists and select the unique values
    unique_amenities = np.unique(np.concatenate(amenities_lists))
    
    # Put in the amenities coulmn a boolean list of the uinque amenities
    is_amenity = np.array([new_df['amenities'].map(lambda val: item in val) for item in unique_amenities])
    
    # Add columns to the dataframe of uinque amenities that has true, false values 
    new_df = pd.concat([new_df, pd.DataFrame(data=is_amenity.T, columns=unique_amenities)], axis=1)
    
    # Delete amenities column
    new_df = new_df.drop(['amenities'], axis = 1)
    
    # Change the dtype of the uinque amenities columns to bool
    for col in unique_amenities:
        new_df[col] = new_df[col].astype(bool)
    
    return new_df, unique_amenities
# Use preprocess_amenities_col

clean_df_boston_model, amenities_boston = preprocess_amenities_col(df_boston_model)
clean_df_boston_model.head()
clean_df_boston_model.info()
# Use preprocess_amenities_col

clean_df_seattle_model, amenities_seattle = preprocess_amenities_col(df_seattle_model)
clean_df_seattle_model.head()
clean_df_seattle_model.info()
# Delete bad columns from the dataframe
clean_df_boston_model = clean_df_boston_model.drop(["", "translation missing: en.hosting_amenity_49", 
                                                    "translation missing: en.hosting_amenity_50"], axis=1)
clean_df_seattle_model = clean_df_seattle_model.drop([""], axis=1)
# Subset to a dataframe only holding the categorical columns

cat_df = clean_df_boston_model.select_dtypes(include=['object'])
cat_cols_lst_boston = cat_df.columns

cat_df = clean_df_seattle_model.select_dtypes(include=['object'])
cat_cols_lst_seattle = cat_df.columns

#Pull a list of the column names of the categorical variables

def create_dummy_df(df, cat_cols, dummy_na):
    '''
    INPUT:
    df -dataframe with categorical variables 
    cat_cols - list of categorical columns names
    dummy_na - Bool holding whether you want to dummy NA vals of categorical columns or not
    
    OUTPUT:
    df - a new dataframe that has the following characteristics:
            1. contains all columns that were not specified as categorical
            2. removes all the original columns in cat_cols
            3. dummy columns for each of the categorical columns in cat_cols
            4. if dummy_na is True - it also contains dummy columns for the NaN values
            5. Use a prefix of the column name with an underscore (_) for separating 
    '''
    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
# Fit a linear model

def fit_linear_model(df, predicted_col, cat_cols, dummy_na, test_size = 0.3, rand_state = 42):
    '''
    INPUT:
    df - a dataframe holding all the variables of interest
    predicted_col - a string holding the name of the column 
    cat_cols - list of strings that are associated with names of the categorical columns
    dummy_na - Bool holding whether you want to dummy NA vals of categorical columns or not
    test_size - a float between [0,1] about what proportion of data should be in the test dataset
    rand_state - an int that is provided as the random state for splitting the data into training and test 
    
    OUTPUT:
    test_score - float - r2 score on the test data
    train_score - float - r2 score on the test data
    lm_model - model object from sklearn
    X_train, X_test, y_train, y_test - output from sklearn train test split used for optimal model
    coef - the coefficient list of the dataset coulmns
    
    '''
    # Drop the rows with missing price values
    df  = df.dropna(subset=[predicted_col], axis=0)

    # Dummy categorical variables
    df = create_dummy_df(df, cat_cols, dummy_na)

    # Mean function
    fill_mean = lambda col: col.fillna(col.mean())
    
    # Fill the mean
    df = df.apply(fill_mean, axis=0)

    # Remove predicted column from the dataframe variables
    X = df.drop(predicted_col, axis=1)
    y = df[predicted_col]

    # Split into train and test
    X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = test_size, random_state = rand_state)

    lm_model = LinearRegression(normalize=True) # Instantiate
    lm_model.fit(X_train, y_train) #Fit

    # Predict using your model
    y_test_preds = lm_model.predict(X_test)
    y_train_preds = lm_model.predict(X_train)

    # Score using your model
    test_score = r2_score(y_test, y_test_preds)
    train_score = r2_score(y_train, y_train_preds)
    
    coef = lm_model.coef_

    return test_score, train_score, lm_model, X_train, X_test, y_train, y_test, coef

# Use the function with the listing Boston dataset
test_score, train_score, lm_model, X_train, X_test, y_train, y_test, coef_ = fit_linear_model(clean_df_boston_model, 'price', cat_cols_lst_boston, dummy_na = False)

# Print training and testing score
print("The rsquared on the training data is {}.  The rsquared on the test data is {}.".format(train_score, test_score))
# Show a plot that shows the coefficient of the selected features.

fig = plt.figure(figsize=(30,10))
ax = fig.add_subplot(111)
cols = np.arange(len(X_train.columns))
plt.xticks(cols, X_train.columns)
_, labels = plt.xticks()
plt.setp(labels, rotation=90)
plt.bar(cols, coef_)
plt.title('The coefficients of the selected features in Boston dataset')
plt.xlabel('Features')
plt.ylabel('Coefficient')
plt.show();

# Use the function with the listing Seattle dataset
test_score, train_score, lm_model, X_train, X_test, y_train, y_test, coef_ = fit_linear_model(clean_df_seattle_model, 'price', cat_cols_lst_seattle, dummy_na = False)

# Print training and testing score
print("The rsquared on the training data is {}.  The rsquared on the test data is {}.".format(train_score, test_score))
# Show a plot that shows the coefficient of the selected features.

fig = plt.figure(figsize=(30,10))
ax = fig.add_subplot(111)
cols = np.arange(len(X_train.columns))
plt.xticks(cols, X_train.columns)
_, labels = plt.xticks()
plt.setp(labels, rotation=90)
plt.bar(cols, coef_)
plt.title('The coefficients of the selected features in Seattle dataset')
plt.xlabel('Features')
plt.ylabel('Coefficient')
plt.show();<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08428344017553673, 'fraction_numerical': 0.00916404939121456, 'mean_word_length': 4.81245311327832, 'pattern_counts': {'":': 0, '<': 57, '<?xml version=': 0, '>': 60, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '587382', 'license': 'CNRI-Python', 'n_tokens_mistral': 7176, 'n_tokens_neox': 6867, 'n_words': 2559, 'path': 'Aribnb_Data_Science.ipynb', 'repo_name': 'nadaalay/AirBnb_Data_Science', 'size': '23243'}