<jupyter_start><jupyter_text>---  Part 1 - Pre-processing and exploratory analysis<jupyter_code>import os
import pandas as pd
import numpy as np
import scipy as sp
from scipy import stats
from scipy.stats import ttest_ind
import seaborn as sns
import matplotlib as plt
import statsmodels.api as sm
import statsmodels.formula.api as smf
import sklearn as sk
import datetime



basedir = ''<jupyter_output><empty_output><jupyter_text>***Dataframe columns description***:- `id` - unique ID identifying the listing- `name` - title of the listing- `host_id` - unique ID for a host- `host_name` - first name of the host- `host_since` - date that the host first joined Airbnb- `host_is_superhost` - whether or not the host is a superhost, which is a mark of quality for the top-rated and most experienced hosts, and can increase your search ranking on Airbnb- `host_listings_count` - how many listings the host has in total- `host_has_profile_pic` - whether or not the host has a profile picture- `host_identity_verified` - whether or not the host has been verified with his passport- `neighbourhood_cleansed` - the borough the property is in- `latitude` and `longitude` - geolocation coordinates of the property- `property_type` - type of property, e.g. house or flat- `room_type` - type of listing, e.g. entire home, private room or shared room- `accommodates` - how many people the property accommodates- `bedrooms` - number of bedrooms- `beds` - number of beds- `price` - nightly advertised price (the target variable)- `minimum_nights` - the minimum length of stay- `maximum_nights` - the maximum length of stay- `availability_30` - how many nights are available to be booked in the next 30 days- `availability_60` - how many nights are available to be booked in the next 60 days- `availability_90` - how many nights are available to be booked in the next 90 days- `availability_365` - how many nights are available to be booked in the next 365 days- `number_of_reviews` - the number of reviews left for the property- `number_of_reviews_ltm` - the number of reviews left for the property in the last twelve months- `first_review` - the date of the first review- `last_review` - the date of the most recent review- `review_scores_rating` - guests can score properties overall from 1 to 5 stars- `review_scores_accuracy` - guests can score the accuracy of a property's description from 1 to 5 stars- `review_scores_cleanliness` - guests can score a property's cleanliness from 1 to 5 stars- `review_scores_checkin` - guests can score their check-in from 1 to 5 stars- `review_scores_communication` - guests can score a host's communication from 1 to 5 stars- `review_scores_location` - guests can score a property's location from 1 to 5 stars- `review_scores_value` - guests can score a booking's value for money from 1 to 5 stars- `instant_bookable` - whether or not the property can be instant booked (i.e. booked straight away, without having to message the host first and wait to be accepted)- `reviews_per_month` - calculated field of the average number of reviews left by guest each month<jupyter_code>### DO NOT CHANGE THIS CELL
def load_csv(basedir):
  return pd.read_csv(os.path.join(basedir, 'listings.csv'))
### DO NOT CHANGE THIS CELL
df = load_csv(basedir)
df.head()

def drop_cols(df):
    cols=['scrape_id','last_scraped','description','listing_url','neighbourhood','calendar_last_scraped', 'amenities',
          'neighborhood_overview', 'picture_url','host_url', 'host_about', 'host_location','host_total_listings_count',
          'host_thumbnail_url','host_picture_url','host_verifications','bathrooms_text','has_availability','minimum_minimum_nights',
          'maximum_minimum_nights','minimum_maximum_nights','maximum_maximum_nights','minimum_nights_avg_ntm','maximum_nights_avg_ntm',
          'number_of_reviews_l30d','calculated_host_listings_count','calculated_host_listings_count_entire_homes','calculated_host_listings_count_private_rooms',
          'calculated_host_listings_count_shared_rooms']
    df=df.drop(columns=cols,axis=1)
    return df
df_drop=drop_cols(df)
df_drop.head()
def drop_cols_na(df,threshold):
    na_rate=df.isna().sum().tolist()
    print(na_rate)
    cols=df.columns.tolist()
    length=len(df)
    #map(lambda col,na_rate: df.drop(columns=col,axis=1,inplace=True) if na_rate/length > threshold else None, cols,na_rate)
    for i in range(len(na_rate)):
        if na_rate[i]/length>=threshold:
            df.drop(columns=cols[i],axis=1,inplace=True)
    return df
df_drop_na=drop_cols_na(df_drop,0.5)
df_drop_na.head()
def binary_encoding(df):
    cols=['host_is_superhost','host_has_profile_pic','host_identity_verified','instant_bookable']
    for i in cols:
        df[i]=np.where(df[i]=='t',1,0)
    return df
df_drop_na_num=binary_encoding(df_drop_na)
# hint: check Pandas to_datetime method
def add_host_days(df):
    df['host_since']=pd.to_datetime(df['host_since'])
    df['host_days']=(datetime.datetime.now()-df['host_since']).dt.days
    return df
df_drop_na_num_days=add_host_days(df_drop_na_num)
def convert_price(df):
    df['price']=df['price'].map(lambda x:x[1:]).str.replace(',','').astype(float)
    return df
df_drop_na_num_days_price=convert_price(df_drop_na_num_days)
df_drop_na_num_days_price['price'].head()<jupyter_output><empty_output><jupyter_text>```python What is the total number of rows in the dataframe?df.shape[0]```<jupyter_code># How many hosts offer 2 or more properties for rent?
df_drop_na_num_days_price[df['host_listings_count']>1].shape[0]
# 4172
# What is the highest price for a listing?
df_drop_na_num_days_price['price'].max()
# What is the ID of the listing that has the largest number of bedrooms?
np.where(df_drop_na_num_days_price['bedrooms']>=max(df_drop_na_num_days_price['bedrooms']))
# What is the ID of the listing with the largest advertised price
np.where(df_drop_na_num_days_price['price']>=max(df_drop_na_num_days_price['price']))
# There are different room types. How many listings are there for the most common room type?
df_drop_na_num_days_price.groupby('room_type',sort=True)['id'].count()
# How many hosts are there that have been registered for more than 3000 days?
df_drop_na_num_days_price[df_drop_na_num_days_price['host_days']>3000].shape[0]<jupyter_output><empty_output><jupyter_text>Question 1d: Exploratory analyses Produce a barplot of the average nightly price per neighbourhood:<jupyter_code>data=df_drop_na_num_days_price[['neighbourhood_cleansed','price']]
dp1=data.groupby('neighbourhood_cleansed').mean().sort_values(by='price')
sns.barplot(y=dp1.index,x='price',data=dp1)<jupyter_output><empty_output><jupyter_text>Plot a correlation matrix as instructed in the Coursework proforma:<jupyter_code>data=df_drop_na_num_days_price[['review_scores_cleanliness','review_scores_checkin','review_scores_communication','review_scores_location','review_scores_value']]
dp2=data.corr(method='pearson')
sns.heatmap(dp2,cmap="Reds",annot=True)<jupyter_output><empty_output><jupyter_text>Plot a geographical distribution as instructed in the Coursework proforma:<jupyter_code>df_drop_na_num_days_price['price'].dtype

expensive=df_drop_na_num_days_price[df_drop_na_num_days_price['price']>150] #get rid of the listings have less price than 150
listLabels=['150-180','180-200','200-260','more than 260'] #set different category 
listBins=[150,180,200,260,10000]

expensive['category']=pd.cut(expensive['price'],bins=listBins,labels=listLabels,
                             include_lowest=True)

prices_types=['150','179','202','260','500']
colors={'150-180':'firebrick','180-200':'teal','200-260':'dodgerblue','more than 260':'lightgreen'}
sizes={'150-180':10,'180-200':40,'200-260':80,'more than 260':200}
alpha={'150-180':0.3,'180-200':0.5,'200-260':0.6,'more than 260':0.8}

%matplotlib inline
x=expensive.latitude
y=expensive.longitude

plt.rcParams['figure.figsize'] = [12, 8]
fig, ax = plt.pyplot.subplots()
grouped=expensive.groupby('category')
zorder_tier=20
for key,group in grouped:
    group.plot(ax=ax,kind='scatter',x='latitude',y='longitude',
               s=sizes[key],color=colors[key],label=key,
               alpha=0.5,zorder=zorder_tier)
    zorder_tier=zorder_tier-5

plt.pyplot.show()<jupyter_output><ipython-input-26-1f3c9b93acf8>:6: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  expensive['category']=pd.cut(expensive['price'],bins=listBins,labels=listLabels,<jupyter_text>---  Question 2a: Linear regression and t-tests<jupyter_code># your linear regression code...
data=df_drop_na_num_days_price[['review_scores_accuracy','review_scores_cleanliness','review_scores_checkin','review_scores_communication','review_scores_location','review_scores_value','review_scores_rating']]
data=data.dropna(axis=0,how='any')
x = data[['review_scores_accuracy','review_scores_cleanliness','review_scores_checkin','review_scores_communication','review_scores_location','review_scores_value']]
y = data['review_scores_rating']
x.shape
y.shape
x = sm.add_constant(x,prepend=False)
mod = sm.OLS(y,x)
res = mod.fit()
print(res.summary())<jupyter_output>OLS Regression Results                             
================================================================================
Dep. Variable:     review_scores_rating   R-squared:                       0.726
Model:                              OLS   Adj. R-squared:                  0.726
Method:                   Least Squares   F-statistic:                     7008.
Date:                  Thu, 03 Jun 2021   Prob (F-statistic):               0.00
Time:                          00:59:07   Log-Likelihood:                -42957.
No. Observations:                 15880   AIC:                         8.593e+04
Df Residuals:                     15873   BIC:                         8.598e+04
Df Model:                             6                                         
Covariance Type:              nonrobust                                         
===============================================================================================
             [...]<jupyter_text>**T-test questions**:Which room types are significantly different in terms of nightly price?YOUR ANSWER (1-2 sentences):  There are significantly different in terms of nightly price in Entire home/apt and Private room,Entire home/apt and Shared room,Hotel room and Private room.Do the significances change if you perform Bonferroni correction to the alpha level: https://en.wikipedia.org/wiki/Bonferroni_correction ?YOUR ANSWER (1-2 sentences):NO<jupyter_code>data=df_drop_na_num_days_price[['room_type','price']]
t_data_1=data[data['room_type']=='Entire home/apt']
t_data_2=data[data['room_type']=='Hotel room']
t_data_3=data[data['room_type']=='Private room']
t_data_4=data[data['room_type']=='Shared room']
ttest_ind(t_data_1['price'],t_data_2['price'],equal_var=False)
ttest_ind(t_data_1['price'],t_data_3['price'],equal_var=False)
ttest_ind(t_data_1['price'],t_data_4['price'],equal_var=False)
ttest_ind(t_data_2['price'],t_data_3['price'],equal_var=False)
ttest_ind(t_data_2['price'],t_data_4['price'],equal_var=False)
ttest_ind(t_data_3['price'],t_data_4['price'],equal_var=False)
# your code...
def variable_selection(df,predictors,target,alpha):
    preds=[]
    rem_pred=[]
    rsadj_max=0
    for col in predictors:
        fml="{}~{}+1".format(target,col)
        mod=smf.ols(formula=fml,data=df)
        res=mod.fit()
        #print(++i)
        if res.pvalues[col]<=alpha:
            rem_pred.append((res.rsquared_adj,col))
            print("Remaining column name:"+col)
        else:
            predictors.remove(col)
            print("Removed column name:"+col)
    while rem_pred:
        rem_pred=[]
        for col in predictors:
            #print(++i)
            fml="{}~{}+1".format(target,'+'.join(preds+[col]))
            mod=smf.ols(formula=fml,data=df)
            res=mod.fit()
            rem_pred.append((res.rsquared_adj,col))
        rem_pred.sort()
        (rsadj,col)=rem_pred.pop()
        if rsadj_max<rsadj:
            predictors.remove(col)
            preds.append(col)
        else:
            rem_pred=[]
    return preds
data_vs=df_drop_na_num_days_price.select_dtypes(exclude='object')
data_vs=data_vs.select_dtypes(exclude='datetime')
data_vs=data_vs.join(pd.get_dummies(df_drop_na_num_days_price['room_type'],dtype=float))
data_vs.dropna(axis=0,how="any")
data_vs=data_vs.rename(columns={'Entire home/apt':'entire_home',"Hotel room":"hotel_room","Private room":"private_room","Shared room":"shared_room"})
predictors=data_vs.columns.tolist()
target="price"
#data_vs.columns=predictors
predictors.remove(target)
col_vs=variable_selection(data_vs,predictors,target,0.05)
col_vs<jupyter_output>Removed column name:id
Remaining column name:host_is_superhost
Remaining column name:host_listings_count
Removed column name:host_has_profile_pic
Removed column name:latitude
Remaining column name:accommodates
Remaining column name:bedrooms
Remaining column name:beds
Remaining column name:minimum_nights
Remaining column name:maximum_nights
Remaining column name:availability_30
Remaining column name:availability_60
Remaining column name:availability_90
Remaining column name:availability_365
Remaining column name:number_of_reviews
Remaining column name:number_of_reviews_ltm
Remaining column name:review_scores_rating
Removed column name:review_scores_accuracy
Removed column name:review_scores_checkin
Remaining column name:review_scores_location
Removed column name:review_scores_value
Remaining column name:reviews_per_month
Remaining column name:host_days
Remaining column name:entire_home
Removed column name:hotel_room
Remaining column name:shared_room<jupyter_text>Question 2c: Recommendation systems  Recommend a neighbourhood given a budget<jupyter_code>def recommend_neighbourhood(df,budget_min,budget_max,relative):
    can_house = df[(df['price']>= budget_min) & (df['price']<=budget_max)]
    can_house = can_house.groupby(['neighbourhood_cleansed'])['id'].count()
    can_house = pd.DataFrame(can_house)

    if(relative):
        nei_count = df.groupby(['neighbourhood_cleansed'])['id'].count()
        nei_count = pd.DataFrame(nei_count)
        nei_count = nei_count.reset_index()
        nei_count = nei_count.rename(columns={'id':'id_count'})
        can_house = can_house.reset_index()
        can_house = can_house.rename(columns={'id':'budget_neighbourhood_count'})
        budget_nei_result = can_house.merge(nei_count,on=['neighbourhood_cleansed'],how='inner')
        budget_nei_result['relative_rate'] = budget_nei_result['budget_neighbourhood_count'] / budget_nei_result['neighbourhood_count']
        budget_nei_result = budget_nei_result.sort_values('relative_rate',ascending=False)
        budget_nei_result_list = budget_nei_result['neighbourhood_cleansed'].tolist()
        return budget_nei_result_list[0]
    else:
         recommend_result = can_house.sort_values('id',ascending=False)
         recommend_result_list = recommend_result.index.tolist()
         return recommend_result_list[0]
        
neighbourhood_result = recommend_neighbourhood(df_drop_na_num_days_price,200,5000,False)
print(neighbourhood_result)<jupyter_output><NAME> - Oud-West<jupyter_text>Price recommender for hosts<jupyter_code>def recommend_price(df,latitude,longitude,n_neighbours,room_type):
    # 
    price_recommder = df[['id','price','latitude','longitude','room_type']]

    if(room_type == ''):
        Y = np.array([latitude,longitude])
        cal_values_list = []
        for index,rows in price_recommder.iterrows():
          X = np.array([float(rows['latitude']),float(rows['longitude'])])
          calute_value = np.sqrt(np.sum(np.square(X-Y)))
          cal_values_list.append(calute_value)
        price_recommder['cal_values']=pd.Series(cal_values_list)
        price_result_recommder = price_recommder.sort_values(by='cal_values')
        result_recommder = price_result_recommder.head(n_neighbours)
        return result_recommder['price'].mean()
    else:
        price_recommder = price_recommder[price_recommder['room_type']==room_type]
        Y = np.array([latitude,longitude])
        cal_values_list = []
        for index,rows in price_recommder.iterrows():
          X = np.array([float(rows['latitude']),float(rows['longitude'])])
          calute_value = np.sqrt(np.sum(np.square(X-Y)))
          cal_values_list.append(calute_value)
        price_recommder['cal_values']=pd.Series(cal_values_list)
        price_result_recommder = price_recommder.sort_values(by='cal_values')
        result_recommder = price_result_recommder.head(n_neighbours)
        return result_recommder['price'].mean()
    return price_recommender
price_recommder_val = recommend_price(df_drop_na_num_days_price,52.1,5.1,5,'')
print(price_recommder_val)

price_recommder_val = recommend_price(df_drop_na_num_days_price,52.1,5.1,5,'entire_home')
print(price_recommder_val)<jupyter_output>117.6
nan
{'fraction_non_alphanumeric': 0.12437405380225923, 'fraction_numerical': 0.019855595667870037, 'mean_word_length': 5.071049840933192, 'pattern_counts': {'":': 3, '<': 41, '<?xml version=': 0, '>': 46, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '638305', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 5956, 'n_tokens_neox': 5589, 'n_words': 1124, 'path': 'Part1_2.ipynb', 'repo_name': 'Jutraman/NLP', 'size': '17179'}