<jupyter_start><jupyter_text>Table of Contents1&nbsp;&nbsp;Load dataset2&nbsp;&nbsp;Clean dataset2.1&nbsp;&nbsp;1) Filter and clean data of interest2.2&nbsp;&nbsp;2) Create table of available days by month and listing2.3&nbsp;&nbsp;3) Focus on listings &amp; months with low booking rate3&nbsp;&nbsp;Merge datasets3.1&nbsp;&nbsp;Merge listings and low_occ<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Load dataset<jupyter_code># Load datasets
source="./airbnb_data/"
calendar=pd.read_csv(source+"calendar.csv")
listings=pd.read_csv(source+"listings.csv")
calendar.head()<jupyter_output><empty_output><jupyter_text>Clean dataset 1) Filter and clean data of interest<jupyter_code>print(calendar['metro_area'].unique().tolist())
# Filter only NYC data
calendar = calendar[calendar['metro_area']=='NYC']

# In 'available' column, change true to 1 and false to 0
calendar['available'] = calendar['available'].apply(lambda x:1 if x == 't' else 0)

# Convert date column to data type date
calendar['date'] = pd.to_datetime(calendar['date'])

calendar.head()<jupyter_output><empty_output><jupyter_text>2) Create table of available days by month and listing<jupyter_code># Sum number of days in a month which listing is available for
occupancy = calendar.groupby([pd.Grouper(key='date',freq='M'),'listing_id'])['available'].sum().to_frame()
occupancy = occupancy.reset_index()
# Compute number of booked days and occupancy rate
occupancy['booked_days'] = occupancy['date'].dt.day - occupancy['available']
occupancy['occupancy_rate'] = occupancy['booked_days']/occupancy['date'].dt.day
occupancy.head()<jupyter_output><empty_output><jupyter_text>3) Focus on listings & months with low booking rateWe find that a significant percentage of listings and months are available for almost a full month. That is, the listings are shown as available on the calendar on Airbnb but are not booked at all/have extremely low booking rates. We choose to focus on examining why by exploring their characteristics and identifying patterns.<jupyter_code># Number of unique listing ids in dataset
print("Number of unique listings: {}".format(len(occupancy['listing_id'].unique().tolist())))

# Percentage of listing months which have 30 or 31 days available i.e. low booking rate
print("% of listing-months with low booking rate: {}".format(len(occupancy[occupancy['booked_days'] == 0])/len(occupancy)*100))

low_occ = occupancy[occupancy['booked_days']==0]
print("Ave # months/listing with low booking rate: {}".format(len(low_occ)/len(low_occ['listing_id'].unique().tolist())))
low_occ.head()
#low_occ.to_csv(r'./high_avail(month).csv', index = False)
len(low_occ['listing_id'].unique().tolist())<jupyter_output><empty_output><jupyter_text>Merge datasets(1) Merge listings and low_occ to characterise the listings with low booking rates  (2) Merge econ_stat and low_occ to characterise months with low booking rates? Merge listings and low_occ<jupyter_code>listings.head()
print(listings.columns)
# Drop unnecessary columns
listings_new = listings.drop(['has_availability','availability_30','host_id','name','weekly_price'],axis=1)
# KIV

occupancy_yr = calendar.groupby([pd.Grouper(key='date',freq='Y'),'listing_id'])['available'].sum().to_frame()
occupancy_yr = occupancy_yr.reset_index()
occupancy_yr.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.130550621669627, 'fraction_numerical': 0.010361160449970397, 'mean_word_length': 8.010666666666667, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, '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': '531016', 'license': 'MIT', 'n_tokens_mistral': 1081, 'n_tokens_neox': 1032, 'n_words': 281, 'path': '.ipynb_checkpoints/Low booking rate listings_Terence edited-checkpoint.ipynb', 'repo_name': 'terenceneo/Airbnb-NYC-data-study', 'size': '3378'}