<jupyter_start><jupyter_text>Explore relationships, derive columns  1. Correlations  2. Scatterplots  3. Pair plots  4. Derive new columns  5. Categorical plots  6. Early visual EDA<jupyter_code># Import libraries
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib
import os
# Check mastplotlib version
matplotlib.__version__
# Display charts with no need to call
%matplotlib inline
# Import data
df = pd.read_csv(r'/Users/OldBobJulia/Desktop/CF/Course/6. Advanced Analytics and Dashboard Design/Berlin Airbnb Analysis/02 Data/Prepared data/listing_wrangled_cleaned.csv')
df.head()
df.shape
# Get rid of unnecessary columns
df_2 = df.drop(columns = ['Unnamed: 0', 'id', 'name', 'host_id', 'host_name', 'last_review', 'neighbourhood'])
df_2.head()<jupyter_output><empty_output><jupyter_text>1. Correlations<jupyter_code>df_2.corr()
# Create subplot with matplotlib
f,ax = plt.subplots(figsize=(10,10))
# Create correlation matrix headmap with seaborn
corr = sns.heatmap(df_2.corr(), annot = True, ax = ax) 
corr.figure<jupyter_output><empty_output><jupyter_text>With the exception of availability_365 most relationships are generally weak throughout. The strong positive relationship between number_of_reviews and reviews_per_month is because reviews_per_month is a calculated variable based on number_of_reviews.I find interesting:- the positive relationship between price and calculated_host_listings_count because it might point towards the fact that potential commercial hosts rent rooms at higher prices- the positive relationship of availability_365 with calculated_host_listings_count because the more likely a host is to be commercial, the more availability throughout the year they will have- the positive relationship between mininum_nights and calculated_host_listings_count for the same reason: that commercial hosts potentially run Airbnbs for profit  2. Scatterplots<jupyter_code># Create scatterplot for variables "price" and "calculated_host_listings_count"
sns.lmplot(x = 'calculated_host_listings_count', y = 'price', data = df_2)<jupyter_output><empty_output><jupyter_text>The outliers at 8000 which I decided to keep now stop me from seeing this scatter plot clearly.<jupyter_code># I will impute the 8000 values with the mean in order to be able to perform analyses
df_2['price']=df_2['price'].replace(8000,df_2['price'].mean())
# Try scatterplot again
sns.lmplot(x = 'calculated_host_listings_count', y = 'price', data = df_2)<jupyter_output><empty_output><jupyter_text>We can see that the most expensive rooms are run by hosts with 1-3 listings. A relatively high amount of hosts has 1-2 listings and a big mass of hosts has up to 19 listings, which include quite a few higher priced ones. We have significant amounts of hosts with 20 to 30, as well as 45 listings, who have rooms priced up to 600 EUR / night.  The data points cluster fairly well around the line of best fit, however I would argue there is a down curve at first, before the values go up again.There is definitely a relationship between price and calculated_host_listings_count but the reason is not clear because it is not clearly linear.<jupyter_code># Create scatterplot to explore "calculated_host_listings_count" and "availability_365"
sns.lmplot(x = 'calculated_host_listings_count', y = 'availability_365', data = df_2)<jupyter_output><empty_output><jupyter_text>Here there's also a relationship, which is also not linear.   3. Pair plots<jupyter_code># Create new subset for pair plot with only relevant variables
df_3 = df_2[['price', 'minimum_nights', 'number_of_reviews', 'calculated_host_listings_count', 'availability_365']]
# Create pair plot
p = sns.pairplot(df_3)<jupyter_output><empty_output><jupyter_text>Here I find interesting that: - the price of rooms with an availability of up to 90 days is significantly higher than the rest. Could that be the legal limit of days for private hosts renting rooms? Is a 3 month stay especially in demand in Berlin?  - likewise, the price of rooms available 365 days / year is higher. Why?- the number of reviews is highest for hosts with 6 or 24 listings. Could these be part- and full-time commercial hosts?- most rooms are available between 0 and 20 days and 350 and 365 days. Why?<jupyter_code># Create histogram of price
sns.distplot(df_2['price'], bins = 10)<jupyter_output>/opt/anaconda3/lib/python3.8/site-packages/seaborn/distributions.py:2557: FutureWarning: `distplot` is a deprecated function and will be removed in a future version. Please adapt your code to use either `displot` (a figure-level function with similar flexibility) or `histplot` (an axes-level function for histograms).
  warnings.warn(msg, FutureWarning)<jupyter_text>4. Derive new columns<jupyter_code># Define categories
df_2.loc[df_2['price'] < 80, 'Price_category'] = 'Low price'
df_2.loc[(df_2['price'] >= 80) & (df_2['price'] < 300), 'Price_category'] = 'Middle price'
df_2.loc[df_2['price'] >= 500, 'Price category'] = 'High price'
df_2.head()
# Count price category
df_2['Price category'].value_counts(dropna = False)<jupyter_output><empty_output><jupyter_text>5. Categorical plots<jupyter_code># Create catplot with categories
sns.set(style="ticks")
p = sns.catplot(x="calculated_host_listings_count", y="price", hue="Price category", data=df_2)<jupyter_output><empty_output><jupyter_text>Now we can see at one glance how many rooms of the high and middle price categories are run by potential commercial as opposed to private hosts. Potential commercial hosts can be considered as those having more than 2 properties. Up to 2 properties run privately is reasonable- you could rent your own and one of your friends' alternatively, both hosts then using the 2nd apartment to stay while one is rented. Based on the above assumption, the most expensive properties in Berlin seem to be rented out by private hosts.   6. Early visual EDA<jupyter_code># Check counts of room types
df_2['room_type'].value_counts(dropna=False)
# Check number of rooms in each neighbourhood_group by room type
crosstab = pd.crosstab(df_2['neighbourhood_group'], df_2['room_type'], dropna = False)
crosstab
cross_plot_1=crosstab.plot(kind='barh', stacked=True).legend(loc='center left', bbox_to_anchor=(1, 0.5))<jupyter_output><empty_output><jupyter_text>Most rooms for rent are in Mitte and Friedrichshain-Kreuzberg. Most entire homes for rent are in Mitte.<jupyter_code># Check neighbourhood_group x price category
crosstab_2 = pd.crosstab(df_2['neighbourhood_group'], df_2['Price category'], dropna = False)
crosstab_2
cross_plot_2=crosstab_2.plot(kind='bar', stacked=True).legend(loc='center left', bbox_to_anchor=(1, 0.5))<jupyter_output><empty_output><jupyter_text>Most middle price properties are in Mitte, followed by Friedrichshain-Kreuzberg and Pankow. Most low price properties for rent are in Friedrichshain-Kreuzberg. The only neighbourhood with high price rentals is Mitte. The neighbourhoods with least properties for rent are Marzahn-Hellersdorf and Spandau.<jupyter_code># Define categories for rental availability
df_2.loc[df_2['availability_365'] <= 90, 'Rental availability'] = 'Short term'
df_2.loc[(df_2['availability_365'] > 90) & (df_2['availability_365'] <= 180) , 'Rental availability'] = 'Middle term'
df_2.loc[df_2['availability_365'] > 180, 'Rental availability'] = 'Long term'
df_2['Rental availability'].value_counts(dropna=False)
crosstab_3=pd.crosstab(df_2['Rental availability'], df_2['Price category'], dropna = False)
crosstab_3
# Plot it
cross_plot_3=crosstab_3.plot(kind='bar', stacked=True, title="Count of available properties by rental duration and price category").legend(loc='center left', bbox_to_anchor=(1, 0.5))<jupyter_output><empty_output><jupyter_text>Most properties are available for short term and fall in the low price category.<jupyter_code>df_2.head()
# Check mean price per neighourhood
df_2.groupby('neighbourhood_group').agg({'price': 'mean'}).sort_values('price', ascending=False)<jupyter_output><empty_output><jupyter_text>The highest average price can be found in Mitte, followed by Charlottenburg-Wilmersdorf and Pankow.<jupyter_code># Check count of availability per year by property type 
entire_home_availability=df_2.groupby('room_type').agg({'availability_365': ['sum']})
entire_home_availability<jupyter_output><empty_output><jupyter_text>The type of property with most availability throughout the year are entire homes.<jupyter_code># Create new category for host type for later analysis
df_2.loc[df_2['calculated_host_listings_count'] <= 2, 'Host type'] = 'Private host'
df_2.loc[df_2['calculated_host_listings_count'] > 2, 'Host type'] = 'Commercial host'
df_2.head()
# Drop column host_type (I renamed it for consistency)
df_2 = df_2.drop(columns = ['host_type'])
df_2.head()
# Count host types
df_2['Host type'].value_counts(dropna=False)
# Check mean price per host type 
host_type_mean=df_2.groupby('Host type').agg({'price': 'mean'})
host_type_mean<jupyter_output><empty_output><jupyter_text>The mean price of properties run by commercial hosts is nearly twice as high as the one of private hosts. This is further aggravated by the fact htat there are 5 times more private than commercial hosts. It also further suggests to check the correlation between calculated_host_listing_count and price for statistical relevance.<jupyter_code># Export useful visuals, set path
path = r'/Users/OldBobJulia/Desktop/CF/Course/6. Advanced Analytics and Dashboard Design/Berlin Airbnb Analysis' 
cross_plot_1.figure.savefig(os.path.join(path, 'EDA_neighbourhood_roomtype.png'))
cross_plot_2.figure.savefig(os.path.join(path, 'EDA_neighbourhood_pricecat.png'))
cross_plot_3.figure.savefig(os.path.join(path, 'EDA_rentalavail_pricecat.png'))
# Export wrangled data set with derived columns
df_2.to_csv(os.path.join(path, '02 Data','Prepared Data', 'listing_derivedcolumns.csv'))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09017713365539452, 'fraction_numerical': 0.020833333333333332, 'mean_word_length': 6.637970791698693, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 69, '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': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '360651', 'license': 'CC0-1.0', 'n_tokens_mistral': 3208, 'n_tokens_neox': 2991, 'n_words': 1041, 'path': 'Berlin Airbnb Analysis/03 Scripts/Explore relationships, derive columns, EDA.ipynb', 'repo_name': 'juliafor/portfolio-final', 'size': '9936'}