<jupyter_start><jupyter_text>In the Business Understanding and Data Understanding part, we already discussed that calendar dataset might be a great source to answer question like what are the busiest times of the year to visit Seattle? By how much do prices spike?To get started, let's read in the necessary libraries we will need to wrangle our data: pandas and numpy. If we decided to build some basic plots, matplotlib might prove useful as well.<jupyter_code># import necessary library and calendar dataset
import numpy as np
import pandas as pd


import matplotlib.pyplot as plt
%matplotlib inline

calendar = pd.read_csv('calendar.csv')

calendar.head()
# check how many rows and columns in the dataset
calendar.shape
# check how many missing values in each column
(calendar.shape[0]-calendar.count())/calendar.shape[0]<jupyter_output><empty_output><jupyter_text>In the above check na cell, we found out only price field has missing values, and about 32.9% of price is NA. So first we need to take care of NAs in the price field. What cause these missing values? My guess is the missing values in price was caused by unavailability.<jupyter_code># for those records having missing values in price, what's their value in available column?
calendar[calendar.isnull().values==True]['available'].value_counts()<jupyter_output><empty_output><jupyter_text>Now we know  NA in price caused by unavailability. So we will use same listing_id's price on available date that is closest to the unavailable date to replace the NAs.<jupyter_code># fill in NA in price field : use a for loop, if we find a NA in price, but the same listing id have 
# price value in previous records, we will use the same listing id's previous price to replace the NA in price.
for i in range(1,calendar.shape[0]):
    if calendar.iloc[i,0]==calendar.iloc[i-1,0] and calendar.iloc[i,3] is np.NaN:
        calendar.iloc[i,3]=calendar.iloc[i-1,3]
    else:
        continue<jupyter_output><empty_output><jupyter_text>In order to calculate the persent of avilable listings on each day, I will replace the t in avilable with 1, f in avilable with 0 in availabel field<jupyter_code># transform the available column : t → 1， f → 0
calendar.loc[calendar['available']=='t','available']=1
calendar.loc[calendar['available']=='f','available']=0

# calculate on each date, how much persent of listings still available
availability=pd.pivot_table(calendar,index=['date'],values=['available'],aggfunc=np.sum)
# we know there are 3818 listings in total, so the persent of avilable listings= number of available listings/total amount of listings
availability['available_persent']=availability['available']/3818
availability.head()<jupyter_output><empty_output><jupyter_text>To better intuitively present the answer of what are the busiest times of the year to visit Seattle, I will draw a line graph, with X axis being the date and Y axis being the available_persent.<jupyter_code># plot the available_persent by date
plt.figure(figsize=(20,10))
plt.plot(availability['available_persent'], 'o-')
plt.title('available_persent by date')
plt.show()<jupyter_output><empty_output><jupyter_text>However, since there are too many dates in this dataset, so we can't clearly see the date information in x axis. So I decided to show available_persent by week. X axis is the week number and Y axis is the average available_persent in that week.<jupyter_code>#generate a new column: date
availability['date']=availability.index

# generate a new column: week
from datetime import datetime
availability['date'] = availability['date'].apply(lambda x:datetime.strptime(x, '%Y-%m-%d'))
availability['week']=availability['date'].apply(lambda x:x.isocalendar()[1])
# calculate the average available_persent of each week
availability_w=pd.pivot_table(availability,index=['week'],values=['available_persent'],aggfunc=np.mean)
# plot the available_persent by week
plt.figure(figsize=(20,10))
plt.plot(availability_w.index,availability_w['available_persent'], 'o-')
plt.xticks(np.arange(1, 52, step=4))
plt.title('available_persent by week')
plt.show()<jupyter_output><empty_output><jupyter_text>From the aboce graph, we can observe that week1-week4(January) is the busiest season to visit Seattle because the avaiable persent level is lowest in the whole year.The second busiest seamon might be week27-week30(summer vacation). Next, let's see how do price change by weeks.  If we want to calculate the average price of each date, we should only use those listings that were booked. So first we need to filter those observations whose availabel value is 0.<jupyter_code># change the data type of price from string to number
calendar['price']=calendar['price'].str.replace('$','')
calendar['price']=calendar['price'].str.replace(',','')
calendar['price']=calendar['price'].astype('float64')

#calculate the average price of booked listings in each week
price=pd.pivot_table(calendar[calendar['available']==0],index=['date'],values=['price'],aggfunc=np.mean,dropna=True)
price.head()
# create a new column: week
price['date']=price.index
price['date'] = price['date'].apply(lambda x:datetime.strptime(x, '%Y-%m-%d'))
price['week']=price['date'].apply(lambda x:x.isocalendar()[1])
price_w=pd.pivot_table(price,index=['week'],values=['price'],aggfunc=np.mean)
# plot the average_price by week
plt.figure(figsize=(20,10))
plt.plot(price_w.index,price_w['price'], 'o-')
plt.xticks(np.arange(1, 52, step=4))
plt.title('average_price by week')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10849056603773585, 'fraction_numerical': 0.01088534107402032, 'mean_word_length': 6.380187416331995, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '523983', 'license': 'CNRI-Python', 'n_tokens_mistral': 1692, 'n_tokens_neox': 1603, 'n_words': 602, 'path': 'Q1 seasonal trend.ipynb', 'repo_name': 'haleyxu816/Seattle-Airbnb-Data-Analysis', 'size': '5512'}