Table of Contents
The outline of your notebook will show up here. You can include headings in any text cell by starting a line with #
, ##
, ###
, etc., depending on the desired title hierarchy.
Welcome to New York City, one of the most-visited cities in the world. There are many Airbnb listings in New York City to meet the high demand for temporary lodging for travelers, which can be anywhere between a few nights to many months. In this notebook, we will take a closer look at the New York Airbnb market by combining data from multiple file types like .csv
, .tsv
, and .xlsx
.
Recall that CSV, TSV, and Excel files are three common formats for storing data. Three files containing data on 2019 Airbnb listings are available to you:
data/airbnb_price.csv
listing_id
: unique identifier of listingprice
: nightly listing price in USDnbhood_full
: name of borough and neighborhood where listing is located
data/airbnb_room_type.xlsx This is an Excel file containing data on Airbnb listing descriptions and room types.
listing_id
: unique identifier of listingdescription
: listing descriptionroom_type
: Airbnb has three types of rooms: shared rooms, private rooms, and entire homes/apartments
data/airbnb_last_review.tsv This is a TSV file containing data on Airbnb host names and review dates.
listing_id
: unique identifier of listinghost_name
: name of listing hostlast_review
: date when the listing was last reviewed
Our goals are to convert untidy data into appropriate formats to analyze, and answer key questions including:
- What is the average price, per night, of an Airbnb listing in NYC?
- How does the average price of an Airbnb listing, per month, compare to the private rental market?
- How many adverts are for private rooms?
- How do Airbnb listing prices compare across the five NYC boroughs?
# We've loaded your first package for you! You can add as many cells as you need.
import numpy as np
import pandas as pd
import datetime as dt
# Read in airbnb_price.csv
airbnb_price = pd.read_csv('data/airbnb_price.csv')
# print(airbnb_price.info())
# print(airbnb_price.head())
# Read in airbnb_room_type.xlsx
airbnb_room_type = pd.read_excel('data/airbnb_room_type.xlsx', )
# print(airbnb_room_type.head())
# Read in airbnb_last_review.tsv
airbnb_last_review = pd.read_csv('data/airbnb_last_review.tsv', sep='\t')
# print(airbnb_last_review.head())
# Remove the string 'dollars' from price column
airbnb_price['price'] = airbnb_price['price'].str.rstrip(' dollars')
# Convert price column datatype into float
airbnb_price['price'] = airbnb_price['price'].astype('float')
# Filter price of free of charge
filtered_price = airbnb_price['price'] == 0
# Exclude the free price
airbnb_price = airbnb_price[~filtered_price]
# print(price.describe())
# Calculating the average price
avg_price = airbnb_price['price'].mean().round(2)
# print(avg_price)
# Add a new column price per month
airbnb_price['price_per_month'] = airbnb_price['price']*365/12
# Calculating the average price per month
average_price_per_month = airbnb_price['price_per_month'].mean().round(2)
# print(average_price_per_month)
# Calculating the differences price between private rental market
difference = (average_price_per_month - 3100).round(2)
# print(difference)
# Clean the room type column
airbnb_room_type['room_type'] = airbnb_room_type['room_type'].str.lower()
# Convert the room type column data into category
airbnb_room_type['room_type'] = airbnb_room_type['room_type'].astype('category')
# Calculating the room frequecny of the room type
room_frequencies = airbnb_room_type['room_type'].value_counts()
# print(room_frequencies)
# Convert the last_review column data into datetime
airbnb_last_review['last_review'] = pd.to_datetime(airbnb_last_review['last_review'])
# print(airbnb_last_review.info())
# Calculating the first review
first_reviewed = airbnb_last_review['last_review'].dt.date.min()
# print(first_reviewed)
# Calculating the last review
last_reviewed = airbnb_last_review['last_review'].dt.date.max()
# print(last_reviewed)
# Merge dataframe of price and room
price_roomtype = airbnb_price.merge(airbnb_room_type, how='outer', on='listing_id')
# print(price_roomtype.columns)
# Merge dataframe of price_roomtype with last_review
airbnb_merged = price_roomtype.merge(airbnb_last_review, on='listing_id', how='outer')
# print(airbnb_merged.columns)
# Drop missing value in the merged table
airbnb_merged.dropna(inplace=True)
# print(airbnb_merged.nbhood_full.value_counts())
# print(airbnb_merged.isnull().sum())
# print(airbnb_merged.duplicated().sum())
# Extract borough from nbhood_full column
airbnb_merged['borough'] = airbnb_merged['nbhood_full'].str.partition(',')[0]
# print(airbnb_merged.borough.value_counts())
# Group by borough to calculate the summary statistics: sum, mean, median, count
summary_statistics = airbnb_merged.groupby('borough')['price'].agg(['sum', 'mean', 'median', 'count']).round(2)
# print(summary_statistics.sort_values('mean', ascending=False))
# Create a list of label names
label_names = ["Budget", "Average", "Expensive", "Extravagant"]
# Create a list of price range
ranges = [0, 69, 175, 350, np.inf]
# Create a new column of price range
airbnb_merged['price_range'] = pd.cut(airbnb_merged['price'], bins=ranges, labels=label_names)
# print(airbnb_merged['price_range'].value_counts())
# Calculating prices by borough
prices_by_borough = airbnb_merged.groupby(['borough', 'price_range'])['price_range'].count()
# print(prices_by_borough)
# Create a dictionary called airbnb_analysis
airbnb_analysis ={'avg_price': avg_price, 'average_price_per_month': average_price_per_month, 'difference': difference, 'room_frequencies': room_frequencies, 'first_reviewed': first_reviewed, 'last_reviewed': last_reviewed, 'prices_by_borough': prices_by_borough}
print(airbnb_analysis)