Data Preparation
Set Up
Importing packages for analysis:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from pandas.api.types import is_string_dtype
from pandas.api.types import is_numeric_dtype
from pandas.api.types import is_categorical_dtype
Import the dataset:
airbnb = pd.read_csv("/work/Airbnb_Open_Data.csv")
Basic Inspection:
airbnb.shape
airbnb.info()
Data Cleaning - Part 1
Removal of excess columns and null value handling
print(airbnb.info())
Removal of columns not needed for analysis.
print(airbnb["host_identity_verified"].unique())
print(airbnb["country"].unique())
print(airbnb["country code"].unique())
airbnb_skinny = airbnb.drop(airbnb.columns[[3,4,9,10,22,25]], axis=1)
print(airbnb_skinny.columns)
Null values in 'price' should be removed due to inability to accurately calculate potential value of listing and low frequency of NaN.
airbnb_skinny = airbnb_skinny.dropna(axis=0, how="any", subset=['price'])
Neighbourhood cannot be reliably identified through its neighbourhood group. The 15 records of null values will be dropped from the analysis.
airbnb_skinny[airbnb_skinny['neighbourhood'].isnull()]
airbnb_skinny = airbnb_skinny.dropna(axis=0, how="any", subset=['neighbourhood'])
airbnb_skinny = airbnb_skinny.dropna(axis=0, how="any", subset=['Construction year'])
Null 'Neighbourhood Group' handling
Neighbourhood Group Formatting & Null Handling:
print(airbnb_skinny['neighbourhood group'].unique())
airbnb_skinny['neighbourhood group'] = airbnb_skinny['neighbourhood group'].str.replace('brookln','Brooklyn')
airbnb_skinny['neighbourhood group'] = airbnb_skinny['neighbourhood group'].str.replace('manhatan', 'Manhattan')
print(airbnb_skinny['neighbourhood group'].unique())
airbnb_skinny[airbnb_skinny['neighbourhood group'].isnull()]
group_master = airbnb_skinny.dropna(axis=0, how="any", subset=['neighbourhood group'])
group_master2 = group_master.groupby('neighbourhood')['neighbourhood group'].unique().reset_index()
print (group_master2)
Left Join to replace values:
## Left join to master table of neighbourhood groups.
airbnb_skinny = airbnb_skinny.merge(group_master2, on='neighbourhood', how='left')
## Remove old column
airbnb_skinny = airbnb_skinny.drop(airbnb_skinny.columns[3], axis=1)
## Rename it
airbnb_skinny = airbnb_skinny.rename(columns = {'neighbourhood group_y':'neighbourhood group'})
airbnb_skinny['neighbourhood group'] = airbnb_skinny['neighbourhood group'].astype(str)
airbnb_skinny['neighbourhood group'] = airbnb_skinny['neighbourhood group'].str.strip()
airbnb_skinny['neighbourhood group'] = airbnb_skinny['neighbourhood group'].str.replace('[','')
airbnb_skinny['neighbourhood group'] = airbnb_skinny['neighbourhood group'].str.replace(']','')
airbnb_skinny['neighbourhood group'] = airbnb_skinny['neighbourhood group'].str.replace("'","")
print(airbnb_skinny['neighbourhood group'].head(5))
Data Cleaning - Part 2
Remove Duplicates
airbnb_skinny[airbnb_skinny.duplicated(['id'], keep=False)]
airbnb_skinny = airbnb_skinny.drop_duplicates(subset=['id'])
Remove Outliers
Checking the min/max of availability365 for invalid integers:
print(airbnb_skinny['availability 365'].min())
print(airbnb_skinny['availability 365'].max())
airbnb_skinny = airbnb_skinny[airbnb_skinny['availability 365'].between(0, 365)]
print(airbnb_skinny.shape)
for column in airbnb_skinny.columns:
if is_string_dtype(airbnb_skinny[column]) or is_categorical_dtype(airbnb_skinny[column]):
airbnb_skinny[column] = airbnb_skinny[column].fillna(airbnb_skinny[column].mode()[0])
elif is_numeric_dtype(airbnb_skinny[column]):
airbnb_skinny[column] = airbnb_skinny[column].fillna(airbnb_skinny[column].mean())
Data Prep for Analysis
Data Type Conversion
Price needs to be turned nurmeric before Revenue is created. Price has a US currency sign and commas that need to be removed to accomplish this.
airbnb_skinny['price'] = airbnb_skinny['price'].str.strip()
airbnb_skinny['price'] = airbnb_skinny['price'].str.replace('$','')
airbnb_skinny['price'] = airbnb_skinny['price'].str.replace(',','')
airbnb_skinny['price'] = pd.to_numeric(airbnb_skinny['price'])
## airbnb_skinny[‘price’] = airbnb_skinny[‘price’].astype(int)
Adding Revenue
airbnb_skinny['potential revenue'] = airbnb_skinny['price'] * 365
Adding Customer Review rating
airbnb_skinny['calc_review_rate'] = airbnb_skinny['reviews per month'] * airbnb_skinny['review rate number']
Total Records Dropped
dropped = 102599-98009
print('There were ' + str(dropped) + ' rows removed during the cleaning process.')
percent_dropped = dropped / 102599 *100
print('A total of ' + str(round(percent_dropped, 2)) + '% records were removed.')
airbnb_skinny
Data Analysis
Neighbourhoods
How many listings exist within neighbourhood groups and neighbourhoods?
group_counts = airbnb_skinny['neighbourhood group'].value_counts(sort=True)
print(group_counts)
sns.countplot(x='neighbourhood group',
data=airbnb_skinny)
plt.title('Amount of listings by neighbourhood group')
plt.xlabel('Neighbourhood group', fontsize=8)
plt.ylabel('Count', fontsize=8)
plt.show()
neighbourhood_counts = airbnb_skinny['neighbourhood'].value_counts(sort=True)
neighbourhood_counts = neighbourhood_counts.reset_index()
top15 = neighbourhood_counts.iloc[0:15]
top15 = top15.reset_index()
top15 = top15.rename(columns = {'index':'neighbourhood','neighbourhood':'count'})
top15 = top15.merge(group_master2, on='neighbourhood', how='left')
top15['neighbourhood group'] = top15['neighbourhood group'].astype(str)
top15['neighbourhood group'] = top15['neighbourhood group'].str.strip()
top15['neighbourhood group'] = top15['neighbourhood group'].str.replace('[','')
top15['neighbourhood group'] = top15['neighbourhood group'].str.replace(']','')
top15['neighbourhood group'] = top15['neighbourhood group'].str.replace("'","")
print(top15)
sns.barplot(x='neighbourhood',
y='count',
data=top15,
dodge=False,
hue='neighbourhood group')
plt.title('Top 15 neighbourhoods for listings')
plt.xlabel('Neighbourhood', fontsize=8)
plt.ylabel('Count', fontsize=8)
plt.xticks(rotation=75)
plt.legend(bbox_to_anchor=(1.05, 1.0), loc='upper left')
plt.tight_layout()
plt.show()
Manhattan & Brooklyn & Queens account for 95.5% of the listings. Manhattan neighbourhoods have the largest presence in the top 15, followed by Brooklyn and 1 for Queens. These neighbourhoods and groups represent the top areas for current Airbnb listings.
Potential Revenue
What is the average listing price for a neighbourhood?
# Count column
airbnb_skinny["total listings"] = 1
print(airbnb_skinny.info())
group2 = airbnb_skinny.groupby(['neighbourhood group', 'neighbourhood'],sort=True)[["potential revenue","total listings"]].sum()
group_df2 = group2.reset_index()
group_df2["avg revenue"] = group_df2["potential revenue"] / group_df2["total listings"]
avg_revenue = group_df2.sort_values(['avg revenue'], ascending=[False])
avg_rev_skinny = avg_revenue.head(20)
print(avg_rev_skinny)
print(avg_revenue.info())
print(group_df2.info())
avg_rev_group = avg_revenue.groupby('neighbourhood group')["avg revenue"].mean()
avg_rev_group = avg_rev_group.reset_index()
print(avg_rev_group.head(6))
sns.barplot(x='neighbourhood group',
y='avg revenue',
data=avg_rev_group,
dodge=False)
plt.title('Avg revenue by neighbourhood')
plt.xlabel('Neighbourhood')
plt.ylabel('Avg revenue')
plt.xticks(rotation=75)
plt.tight_layout()
plt.show()
sns.barplot(x='neighbourhood',
y='avg revenue',
data=avg_rev_skinny,
dodge=False,
hue='neighbourhood group')
plt.title('Avg revenue by top 20 neighbourhood')
plt.xlabel('Neighbourhood')
plt.ylabel('Avg revenue')
plt.xticks(rotation=90)
plt.legend(bbox_to_anchor=(1.05, 1.0), loc='upper left')
plt.tight_layout()
plt.show()
Brooklyn does not appear in the top 20 neighbourhoods by average revenue for a listing. Its large presence in the market and low variability in average revenue indicates that the pricing is lower per listing. A closer look at the availability in these markets will provide more information on a need for more listings. Recommendations will likely include a portfolio of high and low revenue generating properties based on demand for certain neighbourhoods.
Market Saturation
Utilize 'availability 365' to identify demnand. How saturated is the market? (SUM(avail 365) / # of listings
## SUM avail 365 by neighbourhood group and neighbourhood
group_saturation = airbnb_skinny.pivot_table(index=['neighbourhood group'], values=['availability 365','total listings'], aggfunc='sum')
group_saturation['avg availability'] = group_saturation['availability 365'] / group_saturation['total listings']
group_saturation = group_saturation.sort_values('avg availability', ascending=False)
group_saturation = group_saturation.reset_index()
print(group_saturation)
## Count listings in each category
## Calculate total availability / # of listings
sns.barplot(x='neighbourhood group', y='avg availability', data = group_saturation)
## SUM avail 365 by neighbourhood group and neighbourhood
neighbourhood_saturation = airbnb_skinny.pivot_table(index=['neighbourhood','neighbourhood group'], values=['availability 365','total listings'], aggfunc='sum')
neighbourhood_saturation['avg availability'] = neighbourhood_saturation['availability 365'] / neighbourhood_saturation['total listings']
neighbourhood_saturation = neighbourhood_saturation.sort_values('avg availability', ascending=True)
neighbourhood_saturation = neighbourhood_saturation.reset_index()
print(neighbourhood_saturation.head(10))
## Count listings in each category
## Calculate total availability / # of listings
avail_revenue = neighbourhood_saturation.reset_index()
avail_revenue = avail_revenue.drop(avail_revenue.columns[[2,3]], axis=1)
avail_revenue = avail_revenue.merge(avg_revenue, on='neighbourhood', how='left')
print(avail_revenue.head(20))
print(avail_revenue.info())
Using a neighbourhood's average availability can provide insight to which areas have low availability and can accomodate more listings for best performance. Compare its avg availability to avg revenue.
sns.scatterplot(y='avg availability', x='avg revenue', data=avail_revenue, hue='neighbourhood group')
sns.lmplot(data=avail_revenue, y='avg availability', x='avg revenue',col='neighbourhood group', col_wrap=3, fit_reg=True)
Brooklyn and Manhattan have the lowest avg availability. This could make them potential investments. Brooklyn has demand since it is ranked 2nd by count of listings and it has a low average availability. New listings in this market would be on the lower end of price but likely to perform well in the market. Manhattan has low availability as well with the most listings in the market.
Manhattan, Bronx, and Queens having promising regression lines that indicate higher revenue listings have less availability.
Get coefficients of above subsets to support!
Listing Type
Room Type
Overall count of listings grouped by room type
room_pie = airbnb_skinny["room type"].value_counts().reset_index()
plt.pie(data=room_pie,
x="room type",
startangle= 90)
plt.legend(room_pie["index"],loc='upper right',bbox_to_anchor=(1.3, 1))
plt.title("Room Type")
plt.show()
sns.countplot(x='room type',
data=airbnb_skinny,
hue='neighbourhood group')
plt.title('Types of Rooms')
plt.xlabel('Room type', fontsize=8)
plt.ylabel('Count', fontsize=8)
plt.show()
Create subplots for each neighbourhood group
sns.FacetGrid(airbnb_skinny)
sns.FacetGrid(airbnb_skinny,col="neighbourhood group")
## Tight layout with varied y-axis scale. Good for general trends
##
g=sns.FacetGrid(data=airbnb_skinny,col='neighbourhood group',col_wrap=3, sharex=False, sharey=False)
g.map(sns.countplot,'room type', order=['Private room','Entire home/apt','Shared room','Hotel room'])
g.set_xticklabels(rotation=75)
g.tight_layout()
## Fixed y axis for comparison across groups
g=sns.FacetGrid(data=airbnb_skinny,col='neighbourhood group',col_wrap=3, sharex=False)
g.map(sns.countplot,'room type',order=['Private room','Entire home/apt','Shared room','Hotel room'])
g.set_xticklabels(rotation=75)
g.tight_layout()
corr_df = airbnb_skinny.drop(airbnb_skinny.columns[[0,1,2,4,5,18,20,21,22]], axis=1)
corr = corr_df.corr()
sns.heatmap(corr, annot=True)
corr_private = corr_df[corr_df['room type'] == 'Private room'].corr()
sns.heatmap(corr_private, annot=True)
corr_entire = corr_df[corr_df['room type'] == 'Entire home/apt'].corr()
sns.heatmap(corr_entire, annot=True)
corr_shared = corr_df[corr_df['room type'] == 'Shared room'].corr()
sns.heatmap(corr_shared, annot=True)
corr_shared = corr_df[corr_df['room type'] == 'Hotel room'].corr()
sns.heatmap(corr_shared, annot=True)
Private room and entire home/apt are the most popular type of listings. These should likely be focused for investment opportunity.
Price Variability
There is huge variability in pricing but consistent trends across IQR. Median for Shared Rooms is slightly higher in price than the median of others.
sns.boxplot(data=airbnb_skinny, x='room type', y='price')
Filtering for Manhattan:
man_box = airbnb_skinny[airbnb_skinny['neighbourhood group'] == 'Manhattan']
sns.boxplot(data=man_box, x='room type', y='price')
plt.title('Pricing of Manahttan')
que_box = airbnb_skinny[airbnb_skinny['neighbourhood group'] == 'Queens']
sns.boxplot(data=que_box, x='room type', y='price')
plt.title('Pricing of Queens')
## Room type average revenue
room_type = airbnb_skinny.groupby('room type')['potential revenue'].mean()
room_type = room_type.reset_index()
print(room_type)
Hotel rooms are the most expensive but not likely the type of investment being investigated. Surprisingly, shared rooms have the next highest average revenue potential.
Customer Reviews
Are there any themes with the top rated listings? What gets the most ratings?
Insights
Revenue has equity among the neighbourhood groups
Average revenue is similar across all neighbourhoods. Focusing on where the demand is and space for expansion will be key to investment.
sns.barplot(x='neighbourhood group',
y='avg revenue',
data=avg_rev_group,
dodge=False)
plt.title('Avg revenue by neighbourhood')
plt.xlabel('Neighbourhood')
plt.ylabel('Avg revenue')
plt.xticks(rotation=75)
plt.tight_layout()
plt.show()
ax1 = sns.barplot(x='neighbourhood group',
y='avg revenue',
data=avg_rev_group,
dodge=False)
plt.title('Average Revenue + Availability')
plt.xlabel('Neighbourhood')
plt.ylabel('Avg revenue')
ax2 = ax1.twinx()
ax2 = sns.lineplot(
x='neighbourhood group',
y='avg availability',
data=group_saturation,
sort=False,
color='blue'
)
plt.xticks(rotation=75)
plt.tight_layout()
plt.show()
Invest in Manhattan and Queens
Manhattan, Bronx, and Queens show that higher revenue listings have lower availability over the next 365 days. The investment should focus on Manhattan and Queens to grow $2M in revenue.
While Staten Island shows the lowest availability and high avg revenue, the correlation shows higher revenue properties having higher availability over the next 365 days. To perform well in this market, there would need to be investment in lower priced properties and ultimately, it would require more resources and overhead to maintaining the market due to needing to purchase more properties to achieve revenue goal.
While the Bronx exhibits properties of higher revenue having lower availability, it also has the highest availability across all neighbourhood groups. This makes it less desirable for investment compared to Manhattan & Queens.
sns.lmplot(data=avail_revenue, y='avg availability', x='avg revenue',col='neighbourhood group', col_wrap=2, fit_reg=True, sharex=False)
Popular Neighbourhoods
Looking at only Manhattan & Queens, I've created a top 20 neighbourhood list by total listings.
popular1 = neighbourhood_saturation[neighbourhood_saturation["neighbourhood group"] == 'Manhattan']
popular2 = neighbourhood_saturation[neighbourhood_saturation["neighbourhood group"] == 'Queens']
popular = popular1.append(popular2).sort_values('total listings', ascending=False)
popular_20 = popular.head(20)
popular_50 = popular.head(50)
print(popular.head(5))
sns.barplot(x='neighbourhood',
y='total listings',
data=popular_20,
dodge=False,
hue='neighbourhood group')
plt.title('Top 20 neighbourhoods for listings')
plt.xlabel('Neighbourhood', fontsize=8)
plt.ylabel('Count', fontsize=8)
plt.xticks(rotation=75)
plt.legend(bbox_to_anchor=(1.05, 1.0), loc='upper left')
plt.tight_layout()
plt.show()
Pricing of Neighbourhoods
Looking at only Manhattan & Queens, I've created a top 20 neighbourhood list by avg revenue.
price1 = avail_revenue[avail_revenue["neighbourhood group"] == 'Manhattan']
price2 = avail_revenue[avail_revenue["neighbourhood group"] == 'Queens']
price = price1.append(price2).sort_values('avg revenue', ascending=False)
price_20 = price.head(20)
price_50 = price.head(50)
sns.barplot(x='neighbourhood',
y='avg revenue',
data=price_20,
dodge=False,
hue='neighbourhood group')
plt.title('Top 20 neighbourhoods for avg revenue')
plt.xlabel('Neighbourhood', fontsize=8)
plt.ylabel('Revenue', fontsize=8)
plt.xticks(rotation=75)
plt.legend(bbox_to_anchor=(1.05, 1.0), loc='upper left')
plt.tight_layout()
plt.show()
Average Availability
Lower availability neighbourhoods will allow for more listings to enter the market.
popular = popular.sort_values('avg availability', ascending=True)
avail_top20 = popular.head(20)
avail_top50 = popular.head(50)
sns.barplot(x='neighbourhood',
y='avg availability',
data=avail_top20,
dodge=False,
hue='neighbourhood group')
plt.title('Top 20 neighbourhoods for availability')
plt.xlabel('Neighbourhood', fontsize=8)
plt.ylabel('Count', fontsize=8)
plt.xticks(rotation=75)
plt.legend(bbox_to_anchor=(1.05, 1.0), loc='upper left')
plt.tight_layout()
plt.show()
Joining lists to find neighbourhoods that are both low in availability and high in revenue.
## Utilizing top 20, there are 7 neighbourhoods within Manhattan & Queens that have some of the
## lowest availability and higher revenue.
invest = avail_top20.merge(price_20, on='neighbourhood', how='inner').sort_values('avg revenue', ascending=False)
invest = invest.drop(invest.columns[[5,6,7,8,9]], axis=1)
print(invest)
## If needed, there are 35 options by using top 50 in Manhattan & Queens
invest_50 = avail_top50.merge(price_50, on='neighbourhood', how='inner').sort_values('avg revenue', ascending=False)
print(invest_50.info())
Room Type: Price & Availability
Creating smaller df with investment neighbourhoods
rec_nghbr = invest["neighbourhood"]
recommend = airbnb_skinny[airbnb_skinny["neighbourhood"].isin(rec_nghbr)]
print(recommend.info())
Room type visual for investment neighbourhoods (low availability, high revenue):
## Create room type popularity as pie chart for presentation
recommend_pie = recommend["room type"].value_counts().reset_index()
plt.pie(data=recommend_pie,
x="room type",
startangle= 90)
plt.legend(room_pie["index"],loc='upper right',bbox_to_anchor=(1.3, 1))
plt.title("Room Type")
plt.show()
Entire home/Apt is 3 quarters of the market followed by Private Room.
Instant Bookable
## Create visual for presentation
recommend["instant_bookable"].value_counts()
Recommendations
Investing in Manhattan & Queens
Neighbourhood groups: (1) Manhattan & (2) Queens
Neighbourhoods:
invest.iloc[:,0:2]
Room Type: Entire home/Apt (Manhattan) & Private Room (Queens). Most suitable for investment
Instant Bookable: Doesn't matter to the consumer. Leans slightly towards not.
Approximately how many listings will be needed to achieve potential $2M in annual revenue? ???????
recommend.groupby(['neighbourhood','neighbourhood group'])['potential revenue'].mean()
invest
A constraint of this study is that the cost to buy the property cannot be known. Recommendations will be based on projected market performance and target revenue goal.
Additionally, once in Tableau, geospatial data will be utilized to assess the neighbourhoods location to key tourist sites for consideration in final recommendation.
Stuyvesant Town has the lowest avg availability (79 days) and 2nd highest average revenue at approximately 250,000. This shows the highest tolerance for additional listings over the next year and should be the primary investment.
invest["avg revenue"].mean()
Potential revenue is approximately 250,000 per unit on average. This means that there should be 8-10 properties total in the investment to meet a revenue stream of $2M. Considering actual revenue and vacancy, 1 or 2 extra properties is recommended to meet actual reveneue goal.
With further exploration of the real estate market and property acquisition costs, more tailored recommendations can be made within these neighbourhoods.
Export to csv for further analysis.
invest.to_csv('invest.csv')
recommend.to_csv('recommend.csv')
airbnb_skinny.to_csv('skinny.csv')
group_saturation.to_csv('group_saturation.csv')
Constraint: Cost to buy the listing cannot be known, therefore actual profits and cost to execute cannot be determined.
Project is to recommend best type of listings for good market performance.