<jupyter_start><jupyter_text>Data cleaning<jupyter_code>#cleaning & formatting price columns 
dataframes = [df_listings, df_reviews, df_calendar]

#drop missing values in price columns & convert to float
#missing values are dropped as price is used for all subsequent operations
#imputing values not practical as prices vary along different dimensions + might distort results
for df in dataframes:
    for col in df.select_dtypes(include="object").columns:
        if np.all(df[col].dropna().str.startswith("$")):
            df[col] = df[col].str.replace('\$|\,',"").astype("float")<jupyter_output><empty_output><jupyter_text>Data Exploration<jupyter_code>display(df_listings.shape)
df_listings.describe()
columns = ["price","bedrooms" , "host_listings_count" , "guests_included", "property_type"]

fig, ((ax1, ax2, ax3), (ax4, ax5, ax6)) = plt.subplots(2,3, figsize=(25,15))

df_listings.hist(column="bedrooms" , ax=ax1, bins=8)
df_listings.hist(column="price" , ax=ax2, bins=100)
df_listings.hist(column="host_listings_count" , ax=ax3, bins=100)
df_listings.hist(column="guests_included" , ax=ax4, bins=15)
pd.Series(df_listings["property_type"].value_counts()).plot(kind="bar", ax= ax5, title="property_type")
df_listings.hist(column="square_feet" , ax=ax6, bins=15)

# check for different types of rentals

pd.Series(df_listings["property_type"].value_counts()).plot(kind="bar")
df_listings["property_type"].value_counts()

# check distribution of number of let apartments
df_listings.host_listings_count.hist(bins=15)
for i in range(75,100,1):
    print("percentile {}: number of listings {}".format(i,np.nanpercentile(df_listings["host_listings_count"], i, axis=0)))<jupyter_output>percentile 75: number of listings 3.0
percentile 76: number of listings 3.0
percentile 77: number of listings 3.0
percentile 78: number of listings 3.0
percentile 79: number of listings 3.0
percentile 80: number of listings 3.0
percentile 81: number of listings 4.0
percentile 82: number of listings 4.0
percentile 83: number of listings 4.0
percentile 84: number of listings 4.0
percentile 85: number of listings 5.0
percentile 86: number of listings 5.0
percentile 87: number of listings 6.0
percentile 88: number of listings 7.0
percentile 89: number of listings 8.0
percentile 90: number of listings 9.0
percentile 91: number of listings 11.0
percentile 92: number of listings 14.600000000000364
percentile 93: number of listings 18.950000000000273
percentile 94: number of listings 34.0
percentile 95: number of listings 34.0
percentile 96: number of listings 36.0
percentile 97: number of listings 37.0
percentile 98: number of listings 48.0
percentile 99: number of listings 169.0<jupyter_text>Busiest time of the year ?what's the busiest time of the year ?- based on availability fluctuation - based on price fluctuation<jupyter_code>#setting date index excluding 2017
df_calendar.index = pd.DatetimeIndex(data = df_calendar.set_index("date").index)
df_calendar  = df_calendar[df_calendar.index.year == 2016]

#Use availability as proxy for high/low season
availb_listings_true = df_calendar["available"][df_calendar["available"]=="t"].resample("w").count()
availb_listings = availb_listings_true/df_calendar["available"].resample("w").count()



##Use mean price variance as proxy for high/low season
price_listings_mean_yr = df_calendar["price"].resample("w").mean()
price_listings = price_listings_mean_yr/ df_calendar["price"].mean()


#plotting
fig, (ax1, ax2) = plt.subplots(2,1, figsize=(15,10), sharex= True)

price_listings.plot(title="Mean Price per Month", ax= ax1, ylim=(0.8,1.2))
availb_listings.plot(title="Availablity per Month", ax= ax2, ylim=(0.4,.8))<jupyter_output><empty_output><jupyter_text>What are the most popular neighbourhoods ?- what are the most popular neighbours?- did popularity change over time ?<jupyter_code>#calculating top 10 most popular neighbourhoods based on # reviews across all years


#neighbourhoods = df.loc[:,["id", "host_neighbourhood","price","review_scores_rating"]]
pop_neighbourhoods = df_reviews.merge(df_listings.loc[:,["id","price","review_scores_rating","host_neighbourhood"]], 
                           how='left', left_on="listing_id", right_on="id", copy=True)
pop_neighbourhoods = pop_neighbourhoods.set_index(pd.to_datetime(pop_neighbourhoods["date"], dayfirst=True), drop=True)

pop_neighbourhoods_grouped = pop_neighbourhoods.groupby("host_neighbourhood")["price", "review_scores_rating"].agg(num_review_tot =("review_scores_rating", "count"),
                                                                                 mean_review =("review_scores_rating", np.mean),
                                                                                 mean_price =("price", np.mean)).sort_values(by=["num_review_tot","mean_review"], 
                                                                                      ascending=[False, False]).head(10)
display(pop_neighbourhoods_grouped.style.background_gradient(subset=['mean_review'], cmap='BuGn').background_gradient(subset=['num_review_tot'], cmap='BuGn').background_gradient(subset=['mean_price'], cmap='BuGn'));


#breaking down popularity per year for each neighbourhood
years = np.sort(pop_neighbourhoods.index.year.unique())
pop_neighbourhoods_all_yrs = pd.DataFrame()

for year in years:
    df_ = pop_neighbourhoods[pop_neighbourhoods.index.year ==year]

    df_ = df_.groupby("host_neighbourhood")["price","review_scores_rating"].agg(mean_price =("price", np.mean), mean_review=("review_scores_rating", np.mean),no_review=("review_scores_rating", "count"))
    #df_ = df_.dropna(axis=0).sort_values(by=["no_review","mean_review"], ascending=[False, False])
    df_["year"] = year
    
    pop_neighbourhoods_all_yrs = pop_neighbourhoods_all_yrs.append(df_)


#filtering top 10 eighbourhoods from above
top10_neighbourhoods = pop_neighbourhoods_grouped.index

revenue_top10 = pop_neighbourhoods_all_yrs .reset_index().set_index(["year","host_neighbourhood"]).sort_index(level=0)
revenue_top10  = revenue_top10[revenue_top10.index.isin(top10_neighbourhoods, level=1)].drop(2016,axis=0)

#calculating revenues for each neighbourhood based on mean price & # of reviews
revenue_top10["revenue"]=revenue_top10.mean_price*revenue_top10.no_review
revenue_top10 = revenue_top10.reset_index("host_neighbourhood").loc[:,("host_neighbourhood","revenue","no_review")]


#plotting
fig, ax = plt.subplots(1,1, figsize=(15,10))
sns.lineplot(data=revenue_top10 , x=revenue_top10 .index, y="no_review", hue="host_neighbourhood")<jupyter_output>/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:8: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.<jupyter_text>Price development- What's the proportion of the top 10% airbnb renters to the overall renters ?- How did prices develop amd who is profiting most ?- What are the main price drivers ?<jupyter_code>#merge listings and reviews datasets

merged = df_reviews.merge(df_listings.loc[:,["id","price","property_type"]], how='left', left_on="listing_id", right_on="id", copy=True)
merged.index = pd.DatetimeIndex(merged["date"])
merged = merged[merged.index.year!=2016]
years = np.sort(merged.index.year.unique())

#fetching avg price, revenues & # of booking per year for all and top10 airbnbs
revenue_per_year = {"revenue_top_10%":[], "revenue_total":[]}
av_price_per_year = []
num_bookings_per_year = []

for year in years:
    df_ = merged[merged.index.year==year]
    df_ = df_.groupby(["listing_id","property_type"])["price"].agg(
        total_earnings = lambda x: x.mean()*x.count(),
            avg_price="mean",
                num_bookings ="count", ).sort_values(by="total_earnings", ascending=False)
    
    
    quantile = np.array(df_["total_earnings"].quantile([.9]))[0]
    
    revenue_per_year["revenue_top_10%"].append(df_["total_earnings"][df_["total_earnings"] < quantile].sum()/1000000)
    revenue_per_year["revenue_total"].append(df_["total_earnings"].sum()/1000000)
    av_price_per_year.append(df_["total_earnings"].sum()/df_["num_bookings"].sum())
    num_bookings_per_year.append(df_["num_bookings"].sum())

    
#plotting  
fig, ((ax1, ax2),(ax3, ax4))  = plt.subplots(2,2, figsize=(15,10))

pd.DataFrame({"av_price":av_price_per_year}, index= years).plot(title="avg booking price per year (in $)", ax=ax1,ylim=(50,120))
pd.DataFrame({"num_bookings":num_bookings_per_year, "num_of-listings":list(merged["listing_id"].resample("Y").nunique())}, index= years).plot(title="#of bookings vs # listings per year",ax=ax2)
pd.DataFrame(revenue_per_year, index= years).plot(title="total revenue of top10 aribnbs vs total airbnbs (in million $)",ax=ax3)
pd.Series(np.array(revenue_per_year["revenue_top_10%"]) / np.array(revenue_per_year["revenue_total"]), index=years).plot(title="top 10 aribnbs' share of total revenue ",ax=ax4,ylim=(0,1))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13453366137329456, 'fraction_numerical': 0.034220532319391636, 'mean_word_length': 5.703898050974512, 'pattern_counts': {'":': 5, '<': 20, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '32563', 'license': 'FTL', 'n_tokens_mistral': 3245, 'n_tokens_neox': 2961, 'n_words': 626, 'path': 'Seattle_Part1_descript_statistics.ipynb', 'repo_name': 'Nassim814/Seattle-Airbnd-Data_set', 'size': '8942'}