Karim Abbas
← All work 2023Personal project

Airbnb market analysis

The source data was one huge CSV covering over 250,000 listings in ten cities. I redesigned it as a set of related tables, loaded it into SQL, and wrote queries to answer ten questions about hosts, prices and reviews.

  • SQL
  • MySQL
  • Python
  • Pandas
  • Tableau
Code
Results
250k+
listings across 10 cities
5M+
reviews in the source data
6
key tables after normalising

What I did

  • Schema. Split the flat file into Host, Location, Property, ReviewScores, Listing and Review tables to remove repetition and make queries faster.
  • Loading. My first plan was to load everything and split it with SQL, which was slow and fiddly. It worked far better to build CSVs in pandas that already matched the final schema and import those.
  • Reviews. A short Python script matched listing IDs against the reviews file so only relevant reviews were kept.
  • The questions. Who the most active hosts are, how many are superhosts, which neighbourhoods have the most listings and what they cost, how room types and property types split, how review scores look, and how prices and review activity change over time.

Caveats

  • I built and cleaned everything on a 500-row subset before scaling, so the full dataset has not been through the whole pipeline.
  • The Tableau dashboard was an early version when I wrote this up.