Credits to: Ye Joo Park, Sandip Sonawane from the University of Illinois Urbana-Champaign

<aside> 📌 Airbnb is a popular platform for hosts to accommodate guests with short-term stays and leisure activities. It was born in 2008 when the two founders welcomed three guests to their San Francisco home. Airbnb has grown to serve 4 million hosts in about 100,000 cities. That makes Airbnb's valuation worth more than top three hotel chains combined as of December 2020 (source). In this data project, the main goal is to find a creative way to deal with one of the most severe concerns property owners on Airbnb have, what to do with vacant properties?

</aside>

Import Packages and datasets

import pandas as pd
import numpy as np
import sqlite3
df_listings = pd.read_csv('<https://github.com/bdi475/datasets/raw/main/case-studies/airbnb-sql/Shanghai.csv>')

Create and populate the listings table


conn = sqlite3.connect('airbnb.db')

# Create a cursor
# A cursor enables users of a DBMS to traverse through the result set
# without having to retrieve all results at once
c = conn.cursor()

# Drop (delete) listings table if it already exists
c.execute('DROP TABLE IF EXISTS listings')
conn.commit()

# Create table
# Triple quotes (''') denote multiline strings
create_table_query = '''
CREATE TABLE IF NOT EXISTS listings (
    name TEXT,
    neighbourhood TEXT,
    room_type TEXT,
    bedrooms REAL,
    bathrooms REAL,
    price REAL,
    accommodates INT,
    minimum_nights INT,
    availability_365 INT,
    number_of_reviews INTEGER,
    review_score REAL,
    is_superhost INT,
    latitude REAL,
    longitude REAL
)
'''
c.execute(create_table_query)
conn.commit()

# Populate the listings table
df_listings = pd.read_csv(file_url)
df_listings.to_sql(name='listings', index=False, con=conn, if_exists='append')

conn.close()

Untitled

Find listings that can accommodate 8 or more people


query_eight_or_more = ''' 
select name,room_type,bedrooms,bathrooms,accommodates
from listings
where accommodates >=8
order by accommodates desc
'''
conn = sqlite3.connect('airbnb.db')
df_result = pd.read_sql_query(query_eight_or_more, con=conn)
display(df_result)
conn.close()

Untitled

Finding long-term stays

# listings with minimum nights of 30 or more
query_long_term_stays = '''
select name, room_type, bedrooms, bathrooms, minimum_nights, price, review_score
from listings
where minimum_nights >= 30
order by price
'''
conn = sqlite3.connect('airbnb.db')
df_result = pd.read_sql_query(query_long_term_stays, con=conn)
display(df_result)
conn.close()

Untitled