<jupyter_start><jupyter_text>**Context**Since 2008, guests and hosts have used Airbnb to travel in a more unique, personalized way. As part of the Airbnb Inside initiative, this dataset describes the listing activity of homestays in Seattle, WA. **Business Understanding**The data analysis below will help you understand the approached data as well as find out the best answers for these following questions:1. How does the price change by seasons?2. What will be the factors that make the price higher?3. Will there be an affection to the price if that homestay/apartments get more reviews?Finally, we will be able to find the best way to get reasonable price for vacation rental in Seattle.  **Data Understanding** > Content>The following Airbnb activity is included in this Seattle dataset:> - Listings, including full descriptions and average review score> - Reviews, including unique id for each reviewer and detailed comments> - Calendar, including listing id and the price and availability for that day<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline

import warnings
warnings.filterwarnings('ignore')
#import data of Seattle Airbnb
dfSEA_calendar = pd.read_csv('Seattle/calendar.csv')
dfSEA_listing = pd.read_csv('Seattle/listings.csv')
dfSEA_reviews = pd.read_csv('Seattle/reviews.csv')
# calendar dataset
dfSEA_calendar.head()
# listing dataset
dfSEA_listing.head()
#reviews dataset
dfSEA_reviews.head()
print('*** Seattle Airbnb ***')
print('\n- Calendar information:')
print(dfSEA_calendar.info())
print('\n- Listing information:')
print(dfSEA_listing.info())
print('\n- Reviews information:')
print(dfSEA_reviews.info())<jupyter_output>*** Seattle Airbnb ***

- Calendar information:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1393570 entries, 0 to 1393569
Data columns (total 4 columns):
 #   Column      Non-Null Count    Dtype 
---  ------      --------------    ----- 
 0   listing_id  1393570 non-null  int64 
 1   date        1393570 non-null  object
 2   available   1393570 non-null  object
 3   price       934542 non-null   object
dtypes: int64(1), object(3)
memory usage: 42.5+ MB
None

- Listing information:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3818 entries, 0 to 3817
Data columns (total 92 columns):
 #   Column                            Non-Null Count  Dtype  
---  ------                            --------------  -----  
 0   id                                3818 non-null   int64  
 1   listing_url                       3818 non-null   object 
 2   scrape_id                         3818 non-null   int64  
 3   last_scraped                      3818 non-null   object 
 4   name              [...]<jupyter_text>**Data Preparation**  Clean Calendar Dataset<jupyter_code># total null values of Price column
print('Total null values of the Price column is {}'.format(dfSEA_calendar.isnull().sum()['price']))
# no. of null values of Price column when Available column is False
print("Number of null values of the Price column when the apartments/homestays are not available is {}".format(
    dfSEA_calendar[dfSEA_calendar['available']=='f']['price'].isnull().count()))<jupyter_output>Number of null values of the Price column when the apartments/homestays are not available is 459028<jupyter_text>- The result above shows that the price will be missing only if the apartments/homestays are not available for that day. Therefore, we can consider filling those missing values with $0.<jupyter_code># fill NaN values of Price column with $0
dfSEA_calendar = dfSEA_calendar.fillna('$0')
dfSEA_calendar.info()
# functions to change types of columns
# convert to string
def string_covert(ser):
    return ser.astype(str)

# convert to datetime
def date_convert(ser):
    return ser.astype('datetime64[ns]')

# convert money to float
def money_float_convert(ser):  
    return ser.apply(lambda x: x.translate({ ord(c): None for c in "$," })).astype(float)
# change types of the columns 
# convert listing_id to string
dfSEA_calendar['listing_id'] = string_covert(dfSEA_calendar['listing_id'])

# convert date to datetime
dfSEA_calendar['date'] = date_convert(dfSEA_calendar['date'])

# remove the '$' and ',' in the price and convert it to float 
dfSEA_calendar['price'] = money_float_convert(dfSEA_calendar['price'])
dfSEA_calendar.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 1393570 entries, 0 to 1393569
Data columns (total 4 columns):
 #   Column      Non-Null Count    Dtype         
---  ------      --------------    -----         
 0   listing_id  1393570 non-null  object        
 1   date        1393570 non-null  datetime64[ns]
 2   available   1393570 non-null  object        
 3   price       1393570 non-null  float64       
dtypes: datetime64[ns](1), float64(1), object(2)
memory usage: 42.5+ MB<jupyter_text>Clean Listing Dataset<jupyter_code># select some important features
dfSEA_listing_new = dfSEA_listing[['id', 'name', 'host_response_time',
       'host_response_rate', 'latitude', 'longitude', 'property_type', 'room_type', 'accommodates',
       'bathrooms', 'bedrooms', 'beds', 'bed_type', 'amenities', 'price', 'weekly_price', 'monthly_price',
       'cleaning_fee', 'minimum_nights', 'number_of_reviews', 'review_scores_rating',
       'cancellation_policy']]

dfSEA_listing_new.head()
dfSEA_listing_new.info()
dfSEA_listing_new.isnull().sum()
# change types of columns 
# remove the '$' and ',' in the price and convert it to float 
dfSEA_listing_new['price'] = money_float_convert(dfSEA_listing_new['price'])

# convert id column to string
dfSEA_listing_new['id'] = string_covert(dfSEA_listing_new['id'])

# fill NaN values of host_response_rate and host_response_time 
dfSEA_listing_new['host_response_time'] = dfSEA_listing_new['host_response_time'].replace(np.nan, 'Others')
dfSEA_listing_new['host_response_rate'] = dfSEA_listing_new['host_response_rate'].fillna('0%')

# convert host_response_rate to float
dfSEA_listing_new['host_response_rate'] = dfSEA_listing_new['host_response_rate'].apply(lambda x: float(x.split('%')[0])/100)
dfSEA_listing_new.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 3818 entries, 0 to 3817
Data columns (total 22 columns):
 #   Column                Non-Null Count  Dtype  
---  ------                --------------  -----  
 0   id                    3818 non-null   object 
 1   name                  3818 non-null   object 
 2   host_response_time    3818 non-null   object 
 3   host_response_rate    3818 non-null   float64
 4   latitude              3818 non-null   float64
 5   longitude             3818 non-null   float64
 6   property_type         3817 non-null   object 
 7   room_type             3818 non-null   object 
 8   accommodates          3818 non-null   int64  
 9   bathrooms             3802 non-null   float64
 10  bedrooms              3812 non-null   float64
 11  beds                  3817 non-null   float64
 12  bed_type              3818 non-null   object 
 13  amenities             3818 non-null   object 
 14  price                 3818 non-null   float64
 15  weekly_price         [...]<jupyter_text>Clean Reviews Dataset<jupyter_code>dfSEA_reviews.info()
dfSEA_reviews = dfSEA_reviews.dropna(subset=['comments'])
dfSEA_reviews.info()
# change types of columns
# convert IDs to string
dfSEA_reviews['listing_id'] = string_covert(dfSEA_reviews['listing_id'])
dfSEA_reviews['id'] = string_covert(dfSEA_reviews['id'])
dfSEA_reviews['reviewer_id'] = string_covert(dfSEA_reviews['reviewer_id'])

# convert date to datetime
dfSEA_reviews['date'] = date_convert(dfSEA_reviews['date'])
dfSEA_reviews.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 84831 entries, 0 to 84848
Data columns (total 6 columns):
 #   Column         Non-Null Count  Dtype         
---  ------         --------------  -----         
 0   listing_id     84831 non-null  object        
 1   id             84831 non-null  object        
 2   date           84831 non-null  datetime64[ns]
 3   reviewer_id    84831 non-null  object        
 4   reviewer_name  84831 non-null  object        
 5   comments       84831 non-null  object        
dtypes: datetime64[ns](1), object(5)
memory usage: 4.5+ MB<jupyter_text>**Data Visualization, Modeling and Results**<jupyter_code>sns.set_style(style='darkgrid')
# add day, month and year columns
dfSEA_calendar['month'] = pd.DatetimeIndex(dfSEA_calendar['date']).month
dfSEA_calendar['year'] = pd.DatetimeIndex(dfSEA_calendar['date']).year
dfSEA_calendar.head()
# encode available column
dfSEA_calendar = pd.get_dummies(dfSEA_calendar, columns=['available'], drop_first=True)
dfSEA_calendar.head()
# availability by months
dfSEA_month_avail = dfSEA_calendar.groupby('month').sum().drop(['price', 'year'], axis=1)
dfSEA_month_avail.rename(columns={'available_t': 'totalAvailability'}, inplace=True)

# average price by months
dfSEA_month_avgPrice = dfSEA_calendar.groupby('month').mean().drop(['year','available_t'], axis=1)
dfSEA_month_avgPrice.rename(columns={'price': 'averagePrice'}, inplace=True)
fig, axes = plt.subplots(2, 1, figsize=(16,10), sharex=True)

# Availability by months chart
sns.lineplot(x=dfSEA_month_avail.index, y=dfSEA_month_avail['totalAvailability'], data=dfSEA_month_avail, ax=axes[0])
plt.xticks(np.arange(1, 13))
axes[0].set_xlabel('Months')
axes[0].set_ylabel('Availability')
axes[0].set_title('The availability of the hosts through months', fontdict={'fontsize': 15})

# Average Price by months chart
sns.lineplot(x=dfSEA_month_avgPrice.index, y=dfSEA_month_avgPrice['averagePrice'], data=dfSEA_month_avgPrice, ax=axes[1])
plt.xticks(np.arange(1, 13))
axes[1].set_xlabel('Months')
axes[1].set_ylabel('Average of Price')
axes[1].set_title('The average of the price through months', fontdict={'fontsize': 15})

#save img
plt.savefig('PricebySeasons.png')

plt.show()<jupyter_output><empty_output><jupyter_text>The chart shows that the demand rapidly increases in the first quarter, the host's availabity starts slightly dropping and the price almost reaches the top one in mid-year. The top average price and availability time are both at the end of the year.<jupyter_code># correlation of features
plt.figure(figsize=(12,6))
sns.heatmap(dfSEA_listing_new.corr(), annot=True)
# check correlation of price column with other features
price_corr = dfSEA_listing_new.corr()['price'].sort_values(ascending=False)
price_corr.drop('price', inplace=True)
dfSEA_price_corr = pd.DataFrame(price_corr)
dfSEA_price_corr.rename(columns={'price':'priceCorr'}, inplace=True)
dfSEA_price_corr
# Bar chart of price correlation
plt.figure(figsize=(16,6))
sns.barplot(x=dfSEA_price_corr.index, y=dfSEA_price_corr['priceCorr'], data=dfSEA_price_corr)
plt.title('The correlation of Price with the other features', fontdict={'fontsize':20})
plt.ylabel('Correlation to Price')

#save img
plt.savefig('PriceCorrelation.png')
plt.show()<jupyter_output><empty_output><jupyter_text>The bar chart above shows us the 4 main factors that affect the most to the rental price such as: Accommodates, number of bedrooms, bathrooms and beds. With the scale from 0 to 1 for the correlation with the price, those factors reach the point of 0.5 and above. And that indicates they all have strong affection with the rental price.<jupyter_code># create new df of top correlation with Price and fill NaN values with mode of each column 
dfSEA_top_priceCorr = dfSEA_listing_new[['accommodates', 'bedrooms', 'beds', 'bathrooms', 'price']]
dfSEA_top_priceCorr.fillna({
                            'bedrooms': dfSEA_top_priceCorr['bedrooms'].mode()[0],
                            'beds': dfSEA_top_priceCorr['beds'].mode()[0],
                            'bathrooms': dfSEA_top_priceCorr['bathrooms'].mode()[0]
                            }, inplace=True)
dfSEA_top_priceCorr.info()
fig, axes = plt.subplots(2, 2, sharey=True, figsize=(16, 6))
fig.suptitle('Top 4 features that affect to the price', fontsize=20)

sns.scatterplot(x=dfSEA_top_priceCorr['accommodates'], y=dfSEA_top_priceCorr['price'], data=dfSEA_top_priceCorr, ax=axes[0][0])
sns.scatterplot(x=dfSEA_top_priceCorr['bedrooms'], y=dfSEA_top_priceCorr['price'], data=dfSEA_top_priceCorr, ax=axes[0][1])
sns.scatterplot(x=dfSEA_top_priceCorr['beds'], y=dfSEA_top_priceCorr['price'], data=dfSEA_top_priceCorr, ax=axes[1][0])
sns.scatterplot(x=dfSEA_top_priceCorr['bathrooms'], y=dfSEA_top_priceCorr['price'], data=dfSEA_top_priceCorr, ax=axes[1][1])

#save img
plt.savefig('TopFeatures.png')
plt.show()<jupyter_output><empty_output><jupyter_text>The chart shows that there's a certain affection to the change of rental price by 4 factors. An apartment that has only 1 bedroom will have a range of price almost from $50 to $400; however, an apartment with 5 bedrooms will have a range of price almost from $200 to $600.<jupyter_code># No. of Review vs Price
plt.figure(figsize=(10,6))
sns.scatterplot(x=dfSEA_listing_new['number_of_reviews'], y=dfSEA_listing_new['price'], data=dfSEA_listing_new, hue=dfSEA_listing_new['property_type'])
plt.title('Reviews Vs Price', fontsize=20)
#save img
plt.savefig('ReviewVsPrice.png')
plt.show()<jupyter_output><empty_output><jupyter_text>The chart shows that most of the reviews are written for Apartment, House and Cabin. That also means people tend to rent these three kind of properties for their vacation. And they're extremely interested in the place with the range of price from $50 to $300. The number of people significantly decreases when the rental price goes up.<jupyter_code>sns.scatterplot(x=dfSEA_listing_new['longitude'], y=dfSEA_listing_new['latitude'], data=dfSEA_listing_new, hue=dfSEA_listing_new['price'])
plt.show()<jupyter_output><empty_output><jupyter_text>- In the chart above, we can see that there's no correlation between the price and the hosts' locations.<jupyter_code>#extract list of comments and concatenate as a String 
com_arr = dfSEA_reviews['comments'].values
comments = ' '.join(com_arr)

#remove specific control characters
remove_char = ['\n','\r']
for char in remove_char:
    comments = comments.replace(char, ' ')
from wordcloud import WordCloud, STOPWORDS
stopwords = set(STOPWORDS)

wc = WordCloud(background_color='black', stopwords=stopwords)
wc.generate(comments)
plt.figure(figsize=(10,12))
plt.imshow(wc, interpolation='bilinear')
plt.axis('off')
plt.show()<jupyter_output><empty_output><jupyter_text>We can see that in all comments, the most common words using are clean, great location, highly recommend...<jupyter_code>print('The average of review scores is {}'.format(dfSEA_listing_new['review_scores_rating'].mean()))<jupyter_output>The average of review scores is 94.53926206244087
{'fraction_non_alphanumeric': 0.1129218442316833, 'fraction_numerical': 0.030284511441569906, 'mean_word_length': 4.322732200939646, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 53, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '218687', 'license': 'CNRI-Python', 'n_tokens_mistral': 5074, 'n_tokens_neox': 4578, 'n_words': 1293, 'path': 'SeattleAirbnb_Analysis.ipynb', 'repo_name': 'QuanTM26/QuanTM', 'size': '14727'}