"""
![](https://i.imgur.com/OTmSEnt.jpg)  
Photo of Marienplatz in Munich by <a href="https://unsplash.com/@danielsessler">Daniel Seßler</a> on <a href="https://unsplash.com/photos/C6l894Q7wpI">Unsplash</a>
"""
"""
# 0. Stablishing the goal 
<h1 style="background:#FFDE91;
           font-family:newtimeroman;
           font-size:350%;
           text-align:center;
           border-radius: 50px 50px;
           border:5px solid black;
           ; padding:15px"> 🏁 Goal </h1><a id=0></a>
"""
"""
**Using the open source airbnb dataset from below, build a dashboard to help property owners and investors understand the Airbnb property market.**

**Beginner**  
Create a dashboard to describe the Airbnb property market by showing the number of properties with specific property attributes

* How can we help property owners and investors get a high-level overview of the property market?
* What is the average rental price for apartments in your chosen city?
* What number of bedrooms is the most common?
* What proportion of Airbnb rentals have a swimming pool, air-conditioning, washing machine, etc?

**Intermediate**   
Create an interactive dashboard to explore the property market. Build on the beginner level by allowing users to filter data directly from the dashboard.

* How can we help a property owner understand the market and compare their property to others nearby?
* If I own a 2 bedroom apartment with a swimming pool in the centre of your chosen city, how much would I be able to rent it for?
* How many other properties are similar to mine?
* Can you bring in any external data sources to support your dashboard, e.g. transport stations, distance to airport, schools, etc

**Advanced‍**  
Build an interactive dashboard with your own custom metrics to evaluate the state of Airbnb property markets.

* What factors might impact whether an Airbnb rental is a successful investment?
* What data can we provide to a property investor to help them decide where next to buy a property?
* Can we make the market measurable through a custom evaluation metric, eg "Amsterdam has a stable tourism industry year round and the demand for properties is higher than the supply. It is an A+ investment. Barcelona's tourism industry is highly seasonal & demand is low. It is a B- investment.)
"""
"""
****
"""
"""
# 1. Importing 
<h1 style="background:#FFDE91;
           font-family:newtimeroman;
           font-size:350%;
           text-align:center;
           border-radius: 50px 50px;
           border:5px solid black;
           ; padding:15px"> 📚 Importing Libraries and Data </h1><a id=1></a>
"""
import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)
import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))

import matplotlib
import matplotlib.pyplot as plt
import seaborn as sns

import random

# turn off warnings for final notebook
import warnings
warnings.filterwarnings('ignore')

pd.set_option('display.max_columns', None)
%matplotlib inline
sns.set_context('notebook')
sns.set_palette('Set2')
sns.set_style('darkgrid')

class color:
   PURPLE = '\033[95m'
   CYAN = '\033[96m'
   DARKCYAN = '\033[36m'
   BLUE = '\033[94m'
   GREEN = '\033[92m'
   YELLOW = '\033[93m'
   RED = '\033[91m'
   BOLD = '\033[1m'
   UNDERLINE = '\033[4m'
   END = '\033[0m'
df_list=pd.read_csv('../input/listingsairbnbmunich/listings.csv')
df_list.head(5)
df_rev=pd.read_csv('../input/reviewsairbnbmunich/reviews.csv')
df_rev.head(5)
df_cal=pd.read_csv('../input/calendarairbnbmunich/calendar.csv')
df_cal.head(5)
"""
### **Conclusions/Insights:**

* The Listing dataset is going to require the most work, given its size. It has many columns, few of which will not be used for this exercise.
"""
"""
****
"""
"""
# 2. Exploring and Preparing 
<h1 style="background:#FFDE91;
           font-family:newtimeroman;
           font-size:350%;
           text-align:center;
           border-radius: 50px 50px;
           border:5px solid black;
           ; padding:15px"> 🔎 Exploring and Preparing </h1><a id=2></a>
"""
"""
First let's take a look at the columns of the Listing dataset
"""
df_list.columns
"""
Now let's only keep the ones we are going to work with
"""
df_list_1=df_list[['id', 'host_since',
       'host_is_superhost',
       'neighbourhood_cleansed',
       'property_type', 'room_type', 'accommodates',
       'bathrooms_text', 'bedrooms', 'beds', 'amenities', 'price',
       'minimum_nights', 'maximum_nights', 'number_of_reviews',
       'number_of_reviews_ltm', 'number_of_reviews_l30d', 'first_review',
       'last_review', 'review_scores_rating', 'review_scores_accuracy',
       'review_scores_cleanliness', 'review_scores_checkin',
       'review_scores_communication', 'review_scores_location',
       'review_scores_value', 'reviews_per_month']]
df_list_1.columns
"""

**Understanding the features**

https://docs.google.com/spreadsheets/d/1iWCNJcSutYqpULSQHlNyGInUvHg2BoUGoNRIGa6Szc4/edit#gid=1938308660


"""
"""
### **General information about the Listings dataset**
"""
# general information about the dataset
df=df_list_1

df.info()                                     # check for incorrect datatypes
df.isnull().sum().sort_values(ascending=False)# check for missing data (first step)

print("Duplicated values")
df_list.duplicated().value_counts()
# Attention price is listed as object, let's take a deeper look.
print(df_list_1['price'].apply(type).value_counts())
df_list_1['price']
"""
"Price" is listed as string.This has to be corrected immediately or it will hinder the next analysis
"""
#This has to be corrected immediately
df= df_list_1

def clean_currency(x):
    """ If the value is a string, then remove currency symbol and delimiters
    otherwise, the value is numeric and can be converted
    """
    if isinstance(x, str):
        return(x.replace('$', '').replace(',', ''))
    return(x)

df['price'] = df['price'].apply(clean_currency).astype('float')

print(df_list_1['price'].apply(type).value_counts())
df_list_1['price']
df=df_list_1


sns.heatmap(df.isnull(),cbar=True).set_title("Visualizing Missing Values\n", fontsize=25)
plt.gcf().set_size_inches(30,8)
plt.xticks(ticks=[x+0.5 for x in range(df.columns.shape[0])],labels=df.columns,rotation=80, fontsize=15)

plt.show()
df=df_list_1

pd.set_option("display.precision", 2)

def highlight_columns(df, rows=20, color='lightgreen', columns_to_shadow=[], columns_to_show=[]):
    highlight = lambda slice_of_df: 'background-color: %s' % color
    sample_df = df.head(rows)
    if len(columns_to_show) != 0:
        sample_df = sample_df[columns_to_show]
    highlighted_df = sample_df.style.applymap(highlight, subset=pd.IndexSlice[{'accommodates','bedrooms','beds','number_of_reviews_l30d'}, columns_to_shadow])
    return highlighted_df

subsets = pd.IndexSlice[{'accommodates','beds','price'}, {'min'}]
highlight_columns(df.describe().T,columns_to_shadow=['max']).applymap(lambda x: "background-color: lightgreen", subset=subsets) # Here we highlight suspicious numbers to futher investigate
"""
### **Further Exploration for missing values:**  
(Conclusions resumed below, open if you want to check in details.)
"""
# Let's check if exists a missing value for reviews, but exists a non-zero number of reviews:
# These represent cases where reviews exist, but the reviewee probably wrote a review, but didn't fill the score review.(1372-1312 = 60 cases)
print(df_list_1.loc[(df_list_1['review_scores_value'].isnull()) & (df_list_1['number_of_reviews']!=0) ].shape)
df_list_1.loc[(df_list_1['review_scores_value'].isnull()) & (df_list_1['number_of_reviews']!=0) ].head(5)
# Here I checked if there is a good correlation between bedrooms and accommodations to guide imputation of missing values.
# There is no clear correlation to take advantage of.
sns.scatterplot(data=df_list_1,x="bedrooms",y="accommodates")
#Checking value count of 'accomodates' in cases of missing values for 'bedrooms', this will guide imputation
df_list_1[df_list_1['bedrooms'].isnull()]['accommodates'].value_counts()
# Here I checked if there is a good correlation between beds and accommodations to guide imputation of missing values.
# There is no clear correlation to take advantage of.
df_list_1[df_list_1['beds'].isnull()]['accommodates'].value_counts()
sns.scatterplot(data=df_list_1,x="beds",y="accommodates")
#Checking value count of 'accomodates' in cases of missing values for 'beds', this will guide imputation.
df_list_1[df_list_1['beds'].isnull()]['accommodates'].value_counts()
#Checking value count of 'accomodates' compared to 'beds', this will guide imputation.
df_list_1[['beds','accommodates']].value_counts( sort=False, ascending=True).head(30)
#Checking value count of 'accomodates' in cases of missing values for 'bathrooms_text', this will guide imputation.
df_list_1[df_list_1['bathrooms_text'].isnull()]['accommodates'].value_counts()
#Here we find the case where host_is_superhost and host_since features are missing
df_list_1[df_list_1['host_is_superhost'].isnull()]
"""
### **Further Exploration for atypical values:**  
(Conclusions resumed below, open if you want to check in details.)
"""
#There seems to be something strange with the max "accomodates" being 16, but the max "beds" and "bedrooms" are 50.
df_list[df_list_1['accommodates']>15]
# Accommodates seems to have a hardcap at 16 people.
# Using the links in the listing we can see some hostel, hotels and similar in the listing. What isn't the particular goal of the study.
# We are going to apply a filter for listing with "accommodatas" for 16 and "bedrooms" equal and higher than 10, this seeks to exclude hotels and similar that 
# didn't list in airbnb just about the room itself.
#There seems to be something strange with the "beds" being 0.
df_list.loc[(df_list_1['beds']==0)]
#After checking some entries through the listings_url. There are some campings, some makeshift beds out of sofas, but most of the visited listings have indeed a bed.
#It constutites a lesser error to impute all these values as 1.
#There seems to be something strange with the "prices" at 0 and "accomodates" at 0.
df_list.loc[(df_list_1['price']==0)|(df_list_1['accommodates']==0)]
# it seems that actual hotels can list at airbnb, but due to being unnusual they lack "price" and proper "accommodates".
#Definetely excluding those.
#There seems to be something strange with the "number_of_reviews_l30d" with numbers higher than 30. More than one per day?
df_list.loc[(df_list_1['number_of_reviews_l30d']>=30)]
# I couldn't pinpoint why this happened, the listings seem pretty normal. Maybe a case of selfreview to boost numbers? In any case I don't think this will hurt the overall results or model.
# We are leaving as it is for the moment.
df_list.loc[(df_list_1['accommodates']>=16)|(df_list_1['bedrooms']>=10)]
"""
### **Conclusions/Insights:**  

    
**DataTypes**  
* The column 'Price' seems to be saved as string and not as float. The datatype was corrected on the spot.

**Duplicates**  
* No duplicates found.

**Missing Values**

* 1372 rows (~27%) of the listings don't have have score reviews. Probably recent listings. Despite bringing additional information, the lack of reviews can be interpreted as evidence that the pricing is not  adapted to the market,e.g. listing at a lower price to catch initial customers. These listings will be included the general overview, but excluded of models for price prediction.
* 565 rows (~11%) of the listings don't have the number of bedrooms. Of those, 501 states that they accommodate 3 or less people.In this dataset the overwhelming majority of listings have only one bedroom. Because of this, in this notebook we will impute these values as being 1.
* 89 rows  (~2%) of the listings don't have the number of beds. Of those, 79 states that they accommodate 2 or less people.In this dataset the overwhelming majority of listings have only one bed. Because of this, in this notebook we will impute these values as being 1.
* 11 rows  (~0.2%) of the listings don't have bathrooms_text. Of those,10 states that they accommodate 3 or less people.In this dataset the overwhelming majority of listings have only one bathroom. Because of this, in this notebook we will impute these values as being 1.
* 1 row  (~0.2%) of the listings doesn't have host_is_superhost, host_since or score reviews. This row will be included in the general overview, but naturaly excluded of models. 

**Atypical Values**  

* Accommodates seems to have a hardcap at 16 people.Using the links in the listing we can see some hostel, hotels and similar in the listing. What isn't the particular goal of the study. We are going to apply a **filter** for listing with "accommodates" for 16 or "bedrooms" equal and higher than 10, which sums up** for 17 rows.
* 237 rows (~6%) had number of beds set as 0. Listings were inspected: there are some campings, some makeshift beds out of sofas, but most of the visited listings have indeed a bed.I conclude that it constutites a lesser error to replace all these values by 1.
* 3 rows had 0 as price or 0 accomodates. The listings referred to hotels. These rows will be excluded.
"""
"""
### **Appling changes:**  
Just code stuff
"""
# at this point we will change from df_list_1 to df_list_2 (consider this a version control)
df_list_2=df_list_1.copy()
df_list_2['bedrooms'].fillna(value=1, axis=None, inplace=True)
df_list_2['bedrooms'].isnull().value_counts()
df_list_2['beds'].fillna(value=1, axis=None, inplace=True)
df_list_2['beds'].replace(0,1, inplace=True)
df_list_2['beds'].isnull().value_counts()
df_list_2['bathrooms_text'].fillna(value=1, axis=None, inplace=True)
df_list_2['bathrooms_text'].isnull().value_counts()
df_list_2.drop(df_list_2.loc[(df_list_2['accommodates']>=16)|(df_list_2['bedrooms']>=10)].index, inplace=True)
df_list_2.drop(df_list_2.loc[(df_list_2['price']==0)|(df_list_2['accommodates']==0)].index, inplace=True)
"""
### **Categorical Variables**  

"""
"""
**Amenities**  
Amenities are a challenge. We can spend a whole lot of time perfecting this specific data, specially with NLP (Natural Language Processing). But for now let's stick with a simpler solution. We are going to remove some special characters, split each amenity in one column and then count the most frequent amenities.  
This procedure isn't perfect but it's fast and appropriately accurate at this level.
"""
df=df_list_2.copy()
df.amenities = df.amenities.str.replace('[{""}]', "")
df.amenities = df.amenities.str.replace('[', "")
df.amenities = df.amenities.str.replace(']', "")
df= df.amenities.str.get_dummies(sep = ",")
amenities=df.sum(axis=0).sort_values(ascending=False)
normalized_amenities=(amenities)/(df_list_2.shape[0])
normalized_amenities[normalized_amenities>0.05]
"""
Again further cleaning is possible and even desireble for a more acurate description of the data. But this is good enough for the moment. If we got time we can come back to this LATER.
"""
"""
**Neighborhood**  
Let's take a look at the neighborhood.
"""
df_list_2['neighbourhood_cleansed'].value_counts()
fig, ax = plt.subplots(figsize=(8, 8))
ax.set_title("Neighbourhood", size=20,fontweight='bold')

df_list_2['neighbourhood_cleansed'].value_counts().plot(kind='bar', cmap='tab20c', title="Property Type",sort_columns=True)
"""
No obvious problem here. But let's check if ("Sendling-Westpark", "Sendling") and ("Berg am Laim", "Laim") means different places.
  
After checking: yes, they are different places. Moving on.
"""
"""
**Property type**  
Let's inspect the property labels. Probably there will be some confusion.

"""
df_list_2['property_type'].value_counts()
"""
Wow. Cave? that was unexpected.  
  
Let's agree on standardizing these property types.
I suggest:
* Entire apartment  
* Private room in apartment  
* Private room in house  
* Room in hotel  
* Shared room in apartment  
* Entire house  
* Others

(Let's control the urge to put cave in a separate category.)
"""
df_list_2['property_type_clean']=df_list_2['property_type'].replace({
'Entire apartment':'Entire apartment',
'Private room in house':'Private room in house',
'Private room in apartment':'Private room in apartment',
'Private room in condominium':'Private room in apartment',
'Shared room in apartment':'Shared room in apartment',
'Entire townhouse':'Entire house',
'Private room in villa':'Private room in house',
'Entire condominium':'Private room in apartment',
'Private room in townhouse':'Private room in house',
'Entire house':'Entire house',
'Private room in loft':'Private room in apartment',
'Entire loft':'Entire apartment',
'Private room in bed and breakfast':'Private room in apartment',
'Private room':'Private room in apartment',
'Entire serviced apartment':'Entire apartment',
'Private room in camper/rv':'Other',
'Shared room in condominium':'Shared room in apartment',
'Camper/RV':'Other',
'Entire guest suite':'Private room in apartment',
'Hut':'Other',
'Entire home/apt':'Entire apartment',
'Entire hostel':'room in hotel/hostel',
'Private room in guest suite':'Private room in apartment',
'Private room in serviced apartment':'Private room in apartment',
'Private room in guesthouse':'Private room in house',
'Room in boutique hotel':'room in hotel/hostel',
'Entire place':'Entire apartment',
'Cave':'Other',
'Shared room in bed and breakfast':'Shared room in apartment',
'Entire bungalow':'Entire apartment',
'Private room in hostel':'room in hotel/hostel',
'Shared room in boat':'Other',
'Earth house':'Other',
'Room in bed and breakfast':'Private room in apartment',
'Shared room in tipi':'Other',
'Private room in tent':'Other',
'Shared room in tent':'Other',
'Room in hotel':'room in hotel/hostel',
'Room in serviced apartment':'Private room in apartment',
'Room in aparthotel':'room in hotel/hostel',
'Tiny house':'Entire house',
'Private room in bungalow':'Private room in house',
'Shared room in guesthouse':'Shared room in apartment',
'Bus':'Other',
'Shared room in loft':'Shared room in apartment',
'Shared room in hostel':'room in hotel/hostel',
'Entire guesthouse':'Entire house',
'Private room in nature lodge':'Other',
'Private room in barn':'Other'
    })
df_list_2['property_type_clean'].value_counts()
def label_function(val):
    return f'{val:.0f}%'

fig, ax = plt.subplots(figsize=(8, 8))
df_list_2.groupby('property_type_clean').size().plot(kind='pie', textprops={'fontsize': 13},autopct=label_function, cmap='tab20c', title="Property Type",sort_columns=True)
plt.axis('off')
ax.set_title("Property Type", size=20,fontweight='bold')
"""
Much better.
"""
"""
**Bathroom_text**  
Let's clean some toilet related data, dear data janitors.
"""
df_list_2['bathrooms_text'].value_counts()
"""
This would be way better as **numbers**, don't you think?
"""
df_list_2['bathrooms']=df_list_2['bathrooms_text'].replace({
    '1 bath':'1',
    '1 private bath':'1' ,
    '1 shared bath':'1' ,
    '1.5 shared baths':'1.5',
    'Shared half-bath':'0.5',
    '1.5 baths':'1.5',
    '2.5 baths':'2.5',
    '2.5 shared baths':'2.5' ,
    '2 baths':'2',
    '2 shared baths':'2' ,
    '0 baths':'0',
    '0 shared baths':'0' ,
    'Half-bath':'0.5',
    '3 baths':'3',
    '4 baths':'4',
    '3.5 baths':'3.5',
    '5 baths':'5',
    '4 shared baths':'4',
    '8 shared baths':'8',
    '8.5 shared baths':'8.5',
    '3 shared baths':'3'
    })

df_list_2['bathrooms']= df_list_2['bathrooms'].astype('float')
df_list_2['bathrooms'].value_counts()
"""
Done.
"""
"""
**Room type**  
Let's inspect the room types.
"""
df_list_2['room_type'].value_counts()
"""
Surprisingly clean =)
"""
"""
**Host is superhost?**  
Let's inspect the superhost status.
"""
df_list_2['host_is_superhost'].value_counts()
"""
We could turn this into a boolean. but maybe later.
"""
"""
****
Here we can already export the csv files and plot some graphs with tablaeu.
"""
df_list_2.to_csv('df_list_2.csv',index=False)
normalized_amenities.to_csv('amenities.csv',index=True)
"""
`Expand` to see possible dashboards at this point:  
[Link to Tableau Public](https://public.tableau.com/app/profile/aramis.farias/viz/AirbnbMunich/Story1)
"""
"""
![](https://i.imgur.com/Ky8ZWjX.jpg)
"""
"""
![](https://i.imgur.com/NRyNtLW.jpg)

"""
"""
****
"""
"""
![](http://www.graciano.adv.br/imagens/Under-construction1.jpg)
"""
"""
### **General information about the Reviews dataset**
"""
df_rev.head()
# general information about the dataset
df=df_rev

df.info()                                     # check for incorrect datatypes
df.isnull().sum().sort_values(ascending=False)# check for missing data (first step)
from scipy.signal import find_peaks

# Plot the number reviews over time to see any patterns
df_reviews_plot = df_rev.groupby('date')['id'].count().reset_index()
df_reviews_plot["rolling_mean"] = df_reviews_plot.id.rolling(window=30).mean()
df_reviews_plot['date'] = pd.to_datetime(df_reviews_plot['date'])

peaks, _ = find_peaks(df_reviews_plot['rolling_mean'],distance=200)

plt.figure(figsize=(20, 10))

plt.plot(df_reviews_plot['date'][peaks], df_reviews_plot["rolling_mean"][peaks], "x", color='red') # ploting an X in the peaks

plt.plot(df_reviews_plot.date, df_reviews_plot.rolling_mean); # ploting the series

plt.title("Number of reviews by date (last 30 days mean)", fontsize=20);
#plt.xlabel("time");
plt.ylabel("reviews", fontsize=14);
for i in peaks:
    plt.annotate(df_reviews_plot['date'][i].date(),xy=(df_reviews_plot['date'][i],df_reviews_plot['rolling_mean'][i]))

    
props = dict(boxstyle='round', facecolor='wheat', alpha=0.5)
textstr = "Oktoberfest period\n ~ 20 Sep - 05 Oct"
plt.annotate(textstr,xy=(0.85, 0.90), xycoords="axes fraction", fontsize=14, verticalalignment='top', bbox=props)


plt.grid()
"""
Here I used a text analysis software, namely Iramuteq, to group the most common words present in the comments.  
Notice: only word in english were included and with a frequency >100 counts.  
"""
"""
<h3 style="
           font-family:newtimeroman;
           font-size:300%;
           text-align:center;
           ; padding:15px"> Cooccurrence Analysis </h3><a id=2></a>
 
![](https://i.imgur.com/4sbMTit.png)
"""
"""
****
"""
"""
![](https://images-wixmp-ed30a86b8c4ca887773594c2.wixmp.com/f/8cc1eeaa-4046-4c4a-ae93-93d656f68688/dep51cx-d4206067-f005-4c8b-9fe8-d544c901b0a8.jpg?token=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJ1cm46YXBwOjdlMGQxODg5ODIyNjQzNzNhNWYwZDQxNWVhMGQyNmUwIiwiaXNzIjoidXJuOmFwcDo3ZTBkMTg4OTgyMjY0MzczYTVmMGQ0MTVlYTBkMjZlMCIsIm9iaiI6W1t7InBhdGgiOiJcL2ZcLzhjYzFlZWFhLTQwNDYtNGM0YS1hZTkzLTkzZDY1NmY2ODY4OFwvZGVwNTFjeC1kNDIwNjA2Ny1mMDA1LTRjOGItOWZlOC1kNTQ0YzkwMWIwYTguanBnIn1dXSwiYXVkIjpbInVybjpzZXJ2aWNlOmZpbGUuZG93bmxvYWQiXX0.9zBwe-a2skICkc12TH70AdE55bPsS4U1chM_MNSNS6A)

"""