DEV Community: Lena

Airbnb Properties in Australia

Lena — Sun, 01 Aug 2021 12:16:43 +0000

This time, I will explore the Airbnb dataset from 2021 obtained at insideairbnb.com for Australia. I'll visualise all the listings on the map using a great Python library Datashader, will use Metabase (an open-source BI tool) to answer questions like

What neighbourhood is the most popular Airbnb destination across Australia
2021 Christmas availability details
How much are hosts making from renting to tourists (compare that to long-term rentals)
Which hosts are running a business with multiple listings and what is the average number of listings per host

The dataset has about 200k listings, 6.3M reviews, and 70M calendar bookings. The live dashboard is available here.

Setup & Tools

The data exploration setup is similar to the one I described in one of my previous posts "Data behind parking tickets in New York City". I will use

Docker compose to launch Postgres database and Metabase container to use SQL to query the data
Python for scripting, Datashader to visualise Geodata on the map
As previously, I'll deploy everything to AWS for demo purposes

I assume that you have some basic understanding of containers, Docker and Compose, but if you don't, check out my previous post which explains everything in detail.

Importing Data

To import data into a Postgres database, let's create the following tables:

listing
review
calendar
suburbs - This is an additional table, not present in the original dataset. It contains the geodata of all Australian suburbs as per Australian Bureau of Statistics

The analysed dataset (although it has great insights) is not ready to use out of the box. Some of the records cannot be imported into the database, that's why I will create a simple Python ETL flow, the purpose of which is to

load CSV files one by one into memory
perform the data transformation part
insert results into the database

Python script GIST that performs that ETL process is available here.

Visualising with Datashader

This is the first time I used this tool for visualising geodata, and it turned out to be quite good. The reason for choosing it was that it can easily plot big datasets with millions of points of data.

The basic setup is described in the official documentation, I just want to point out a few things

Datashader library is rather quirky
I couldn't make it run either locally, or by using Virtualenv
Recommended setup via Anaconda had worked, but only after a few tries because every time Conda got stuck on solving environment issue
Try using low-res rendering instead of high-res, i.e. 800x500 will look better than 1900x1200

Let's first create a simple visualisation of all Airbnb listings across AU and NZ

import colorcet
import datashader as ds
import pandas as pd

df = pd.read_sql_table('listing', conn)
cvs = ds.Canvas(plot_width=800, plot_height=500)
agg = cvs.points(df, x='longitude', y='latitude')
img = ds.tf.shade(agg, cmap=colorcet.fire, how='log')

Now, let's visualise all the major cities separately. To do this, I will create a postgres view for each city, using its bounding geo coordinates. For example, a view for Melbourne can be created like that:

CREATE VIEW listing_melbourne AS
    SELECT *
    FROM listing
    WHERE (longitude BETWEEN 144.2589302195 AND 145.4866523875)
    AND  (latitude BETWEEN -38.517433861 AND -37.5733096779);

Then, I can ask Datashader to plot the entire listing_melbourne table like that:

df = pd.read_sql_table('listing_melbourne', conn)
cvs = ds.Canvas(plot_width=800, plot_height=500)
agg = cvs.points(df, x='longitude', y='latitude')
img = ds.tf.shade(agg, cmap=colorcet.fire, how='log')

Melbourne

Similar to Melbourne, it's easy to create datashader plots for Sydney, Brisbane, Perth, Adelaide, etc.

Data Exploration

Let's now turn to Metabase and build a few queries to address the questions at the beginning of that post.

What states have the most Airbnb listings

→ In Australia only

SELECT region_parent_name, COUNT(*) AS listing_count
FROM listing
WHERE region_parent_name IN ('New South Wales', 'Victoria','Queensland','Western Australia','South Australia','Tasmania','Australian Capital Territory', 'Northern Territory')
GROUP BY region_parent_name
ORDER BY listing_count DESC

Link to live query

→ In Australia and New Zealand

SELECT region_parent_name as region, COUNT(*) AS listing_count
FROM listing
WHERE region_parent_name IN ('New South Wales', 'Victoria','Queensland','Western Australia','South Australia','Tasmania','Australian Capital Territory', 'Northern Territory')
GROUP BY region_parent_name

UNION ALL

SELECT 'New Zealand' as region, COUNT(*) AS listing_count
FROM listing
WHERE region_parent_name NOT IN ('New South Wales', 'Victoria','Queensland','Western Australia','South Australia','Tasmania','Australian Capital Territory', 'Northern Territory')

ORDER BY listing_count DESC

Link to live query

What neighbourhoods are the most popular Airbnb destinations in Australia?

To answer this question, I can use one of the following columns in the listing table: neighbourhood, host_neighbourhood, host_location. The problem here is that a lot of these columns have NULL or empty values. For example: SELECT COUNT(*) FROM "listing" where "neighbourhood" !='' will only return 124,363 rows, or 64% of all the listings. Coalescing these three fields also won't work properly.

Instead, I will use geodata (lat, long) of the listings, and shapefiles from the Australian Bureau of Statistics to upload the suburb shapes into our database. There are several ways to upload .shp files into Postgres, but I recommend QGIS for that for its simplicity.

After uploading suburbs geodata into the new table suburbs, I will run the following query:

SELECT
  suburbs.sa3_name21 AS suburb_name,
  COUNT(listing.*) AS listing_count
FROM listing
JOIN suburbs
ON ST_Contains(ST_SetSRID(
           suburbs.geom::GEOMETRY,
           7844
         ), ST_SetSRID(ST_MakePoint(listing.longitude, listing.latitude), 7844))
GROUP BY suburb_name
ORDER BY listing_count DESC

which gives us suburbs with the highest number of Airbnb listings:

Link to live query

or as a table:

Link to live query

Click to expand the image ![suburbs_table](https://d1ydrm1s5noqxj.cloudfront.net/airbnb/bad_suburbs_listing_count.png)

Property Details

Distribution by the number of bedrooms doesn't look unexpected:

SELECT bedrooms, COUNT(*) AS listing_count
FROM airdata.listing
WHERE bedrooms>0 AND bedrooms<15
GROUP BY bedrooms
ORDER BY listing_count DESC

Link to live query

How many rental properties are there per host?

WITH count_by_host AS (
SELECT host_id, COUNT(*) AS listing_count
FROM airdata.listing
GROUP BY host_id
)
SELECT listing_count, COUNT(count_by_host.host_id) as host_count
FROM count_by_host
GROUP BY listing_count
ORDER BY listing_count DESC

Link to live query

Some additional interesting findings:

There are 117907 different hosts in Australia in total
There are 5 hosts in Australia with # of listings > 300
There are 39 hosts in Australia with # of listings > 100
There are 1064 hosts in Australia with # of listings > 10
There are 5599 hosts in Australia with # of listings > 3

Christmas 2021 Availability

One of the most interesting questions to address is the number of listings still available for Xmas 2021 period, per particular destination, i.e. what percentage of, say, Bondi Beach properties is available.

To find the answer, we need to use our calendar table, which has an enormous 70M records. The queries will be running for some time, so it's a good thing to increase statement timeout in the Postgres settings.

So, for example for Bondi Beach (id=372) we have:

WITH suburb_listings AS (
SELECT lst.id as lstid FROM "airdata"."listing" AS lst
JOIN "airdata"."suburbs" AS subs
ON ST_Contains(ST_SetSRID(
           subs.geom::GEOMETRY,
           7844
         ), ST_SetSRID(ST_MakePoint(lst.longitude, lst.latitude), 7844))
WHERE subs.id = 372
)
SELECT DISTINCT(cal.listing_id) FROM "airdata"."calendar" AS cal
WHERE cal.listing_id IN (SELECT lstid FROM suburb_listings)
AND (cal.date BETWEEN '2021-12-20 00:00:00+00' AND '2021-12-28 00:00:00+00')
AND cal.available=TRUE

The result is 347 listings. So out of 2350 total listings in Bondi Beach area, only 347 (< 15%) have some availability during the Christmas period. Let's try to visualise it as a chart. In order to do that, I will iterate the above query over all suburbs in the suburbs table, and visualise like that:

Link to live query

Keep in mind, the above is based on the booking data as of April 2021.

Summary

In this article, we've done the following things

Cleaned and uploaded the dataset from insideairbnb.com to a Postgres database using Python
Visualised listings geodata using Datashader Python library
Explored the dataset in details with Metabase (an open-source BI tool)
Created and published a live dashboard with all the above results with the help of AWS.

Thank you!

Data behind parking tickets in New York City

Lena — Sat, 17 Jul 2021 07:41:38 +0000

Data behind parking tickets in New York City

Here, we shall analyse the "NYC Parking Tickets" dataset from Kaggle. To dig deeper into the data and uncover various insights, I will introduce such tools as Docker and Metabase, and explain the setup step by step.

Live demo of the resulting dashboard can be found here.

About the dataset

The NYC Department of Finance collects data on every parking ticket issued in NYC (~10M per year!). This data is made publicly available to aid in ticket resolution and to guide policymakers.

This dataset is quite large - it contains about 42.3M rows, and covers the period from Aug 2013 to June 2017. Columns include information about the vehicle ticketed, the ticket issued, location, and time.

Here are some questions that might be interesting to address

When are tickets most likely to be issued? Any seasonality?
Where are tickets most commonly issued?
What are the most common years and types of cars to be ticketed?
Geographical distribution of the parking violations
What time of the day are you most likely to get a ticket?
What police precincts issue the most tickets?

Tools

The raw data is provided via multiple CSV files, the combined size of which exceeds 8GB. This is hard to analyse directly. My approach will involve importing the data into a Postgres database first, so I can run SQL and iterate faster.

Then, I will connect an open-source BI solution called Metabase and will build the resulting queries and the dashboard using Metabase's UI. Metabase is a great BI tool for individuals and small startups and is an ideal solution for prototyping.

In addition, it is open-source and has solid visualisation capabilities, and will be ideal for small teams that might not even have a data analyst.

The above will be packed into a Docker container using Compose and deployed to AWS Cloud for demo purposes. I'd assume that you have some basic understanding of containers, Docker, and Compose, but just in case, here's a link to the "Get Started" section of Docker documentation.

So, here's the summary of the steps we're going to take:

Download CSV files from Kaggle
Create Postgres container and import the files into a database
Create Metabase container and attach it to our Postgres database
Run analysis using SQL queries and build the resulting dashboard using Metabase

Importing data into Postgres

Container Definition

Our Postgres container definition will look like this

FROM postgres:13.3
COPY pg.conf /etc/postgresql/postgresql.conf
COPY init.sql /docker-entrypoint-initdb.d/

# importing data using 'copy' command
COPY ./data/* /docker-entrypoint-initdb.d/

CMD ["-c", "config_file=/etc/postgresql/postgresql.conf"]

We are using postgres:13.3 image from the official Postgres repository, and we are providing our own Postgres config, which is almost identical to the default one, with only a few adjustments in ./containers/postgres/pg.conf:

statement_timeout = 1200000 # in milliseconds (20 minutes) to allow COPY command to finish
max_wal_size = 3GB

Let's download the data files and put them in ./containers/postgres/data folder under the following names:

./containers/postgres/data/year_2014.csv
./containers/postgres/data/year_2015.csv
./containers/postgres/data/year_2016.csv
./containers/postgres/data/year_2017.csv

Database Initialisation

Now, let's look at the init.sql file in the postgres folder. There's an SQL definition of the table nyc_tickets that will contain all our data. I will create a few indices on the table, to speed up the future queries:

CREATE INDEX "IDX_tickets_vehicle_make" ON "nyc_tickets" ("vehicle_make");
CREATE INDEX "IDX_tickets_issue_date" ON "nyc_tickets" ("issue_date");

⚠️Cleaning and Preparing the Data

As per the dataset description on Kaggle, I am using summons_number as the primary key. However, in the input CSV files, there is a number of collisions and duplicates (i.e. multiple rows with identical summons_number values).

Normally, when inserting a possible duplicate into a table in Postgres, I would use something like

INSERT ... ON CONFLICT DO NOTHING;

or use a rule

CREATE RULE ... AS ON INSERT TO ... WHERE EXISTS ... DO INSTEAD NOTHING;

but, unfortunately, COPY command won't allow that syntax. So instead, we'll apply the following trick:

First, we will create a temporary table temp_tickets that is identical to the original but doesn't contain any primary keys or indices.
Then, we will COPY the data to that temporary table.
And finally, we will copy the data from the temporary table to nyc_tickets using ON CONFLICT DO NOTHING rule.

One thing to note though is that the 2017 CSV file doesn't have 8 additional columns, which are present in the other files (2013-2016). To address that issue, I will specify which exact fields I need to copy, while the rest will be replaced with default values:

COPY "temp_tickets" ("summons_number", "plate_id", "registration_state", "plate_type", "issue_date", "violation_code",
                     "vehicle_body_type", "vehicle_make", "issuing_agency", "street_code_1", "street_code_2",
                     "street_code_3", "vehicle_expiration_date", "violation_location", "violation_precinct",
                     "issuer_precinct", "issuer_code", "issuer_command", "issuer_squad", "violation_time",
                     "time_first_observed", "violation_county", "violation_in_front_of_or_opposite", "house_number",
                     "street_name", "intersecting_street", "date_first_observed", "law_section", "sub_division",
                     "violation_legal_code", "days_parking_in_effect", "from_hours_in_effect", "to_hours_in_effect",
                     "vehicle_color", "unregistered_vehicle", "vehicle_year", "meter_number", "feet_from_curb",
                     "violation_post_code", "violation_description", "no_standing_or_stopping_violation",
                     "hydrant_violation", "double_parking_violation")
FROM '/docker-entrypoint-initdb.d/year_2017.csv' DELIMITER ',' CSV HEADER;

Here is what the final version of init.sql looks like:

-- creating Metabase DB first
CREATE DATABASE "metabase";
GRANT ALL PRIVILEGES ON DATABASE "metabase" TO "analyst";

BEGIN;
GRANT ALL PRIVILEGES ON DATABASE "tickets" TO "analyst";
CREATE TABLE "nyc_tickets"
(
    "summons_number"                    BIGINT NOT NULL,
    "plate_id"                          TEXT,
    "registration_state"                TEXT,
    "plate_type"                        TEXT,
    "issue_date"                        DATE,
    "violation_code"                    INTEGER,
    "vehicle_body_type"                 TEXT,
    "vehicle_make"                      TEXT,
    "issuing_agency"                    TEXT,
    "street_code_1"                     INTEGER,
    "street_code_2"                     INTEGER,
    "street_code_3"                     INTEGER,
    "vehicle_expiration_date"           TEXT,
    "violation_location"                TEXT,
    "violation_precinct"                INTEGER,
    "issuer_precinct"                   INTEGER,
    "issuer_code"                       INTEGER,
    "issuer_command"                    TEXT,
    "issuer_squad"                      TEXT,
    "violation_time"                    TEXT,
    "time_first_observed"               TEXT,
    "violation_county"                  TEXT,
    "violation_in_front_of_or_opposite" TEXT,
    "house_number"                      TEXT,
    "street_name"                       TEXT,
    "intersecting_street"               TEXT,
    "date_first_observed"               TEXT,
    "law_section"                       INTEGER,
    "sub_division"                      TEXT,
    "violation_legal_code"              TEXT,
    "days_parking_in_effect"            TEXT,
    "from_hours_in_effect"              TEXT,
    "to_hours_in_effect"                TEXT,
    "vehicle_color"                     TEXT,
    "unregistered_vehicle"              TEXT,
    "vehicle_year"                      INTEGER,
    "meter_number"                      TEXT,
    "feet_from_curb"                    DECIMAL,
    "violation_post_code"               TEXT,
    "violation_description"             TEXT,
    "no_standing_or_stopping_violation" TEXT,
    "hydrant_violation"                 TEXT,
    "double_parking_violation"          TEXT,
    "latitude"                          DECIMAL,
    "longitude"                         DECIMAL,
    "community_board"                   TEXT,
    "community_council"                 TEXT,
    "census_tract"                      TEXT,
    "bin"                               TEXT,
    "bbl"                               TEXT,
    "nta"                               TEXT,
    CONSTRAINT "PK_tickets" PRIMARY KEY ("summons_number")
);

CREATE INDEX "IDX_tickets_vehicle_make" ON "nyc_tickets" ("vehicle_make");
CREATE INDEX "IDX_tickets_issue_date" ON "nyc_tickets" ("issue_date");

COMMIT;

BEGIN;

CREATE TEMP TABLE "temp_tickets"
(
    LIKE "nyc_tickets"
) ON COMMIT DROP;

COPY "temp_tickets" FROM '/docker-entrypoint-initdb.d/year_2014.csv' DELIMITER ',' CSV HEADER;
COPY "temp_tickets" FROM '/docker-entrypoint-initdb.d/year_2015.csv' DELIMITER ',' CSV HEADER;
COPY "temp_tickets" FROM '/docker-entrypoint-initdb.d/year_2016.csv' DELIMITER ',' CSV HEADER;
COPY "temp_tickets" ("summons_number", "plate_id", "registration_state", "plate_type", "issue_date", "violation_code",
                     "vehicle_body_type", "vehicle_make", "issuing_agency", "street_code_1", "street_code_2",
                     "street_code_3", "vehicle_expiration_date", "violation_location", "violation_precinct",
                     "issuer_precinct", "issuer_code", "issuer_command", "issuer_squad", "violation_time",
                     "time_first_observed", "violation_county", "violation_in_front_of_or_opposite", "house_number",
                     "street_name", "intersecting_street", "date_first_observed", "law_section", "sub_division",
                     "violation_legal_code", "days_parking_in_effect", "from_hours_in_effect", "to_hours_in_effect",
                     "vehicle_color", "unregistered_vehicle", "vehicle_year", "meter_number", "feet_from_curb",
                     "violation_post_code", "violation_description", "no_standing_or_stopping_violation",
                     "hydrant_violation", "double_parking_violation")
    FROM '/docker-entrypoint-initdb.d/year_2017.csv' DELIMITER ',' CSV HEADER;

INSERT INTO "nyc_tickets"
SELECT *
FROM "temp_tickets"
ON CONFLICT DO NOTHING;

COMMIT;

Metabase container

Metabase dockerfile doesn't contain anything except for the official image and environment files

FROM metabase/metabase:v0.39.4

Docker Compose

Now, we are ready to combine both containers into a docker-compose setup:

version: "3.0"

services:
  postgres:
    build: ./postgres
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=analyst
      - POSTGRES_PASSWORD=tickets
      - POSTGRES_DB=tickets
    volumes:
      - pg-data:/var/lib/postgresql/data

  metabase:
    # Database env variables are set in environment specific env files in ../containers/metabase
    build: ./metabase
    ports:
      - "3000:3000"

volumes:
  pg-data:

Let’s now launch the containers:

cd path/to/project
docker-compose -f containers/compose.yml up

Note, that you should probably give enough system resources to Docker, and it can take up to 10 minutes to import all 42M records into the database. Once it’s done, it time now to open Metabase at localhost:3000 and after a quick Metabase setup, start building our dashboard.

Analysis and Visualisation

This is probably the most interesting part of this tutorial, as we will be building SQL queries here to uncover different kinds of insights and visualising them on a dashboard.

Our first question is very simple: what types of cars are the most likely to receive a parking ticket

SELECT COUNT(*) AS "vehicle_count", "vehicle_make"
FROM "nyc_tickets" 
GROUP BY "vehicle_make" 
ORDER BY "vehicle_count" DESC

Link to live query

Let's group further by the make and year:

SELECT COUNT(*) AS "vehicle_count", concat("vehicle_make", '_', "vehicle_year") as make_year
FROM "nyc_tickets"
GROUP BY "vehicle_make", "vehicle_year" 
ORDER BY "vehicle_count" DESC

Link to live query

When are tickets most likely to be issued? Any seasonality?

For this one, we will group our results using a Postgres function [date_trunc](https://www.postgresql.org/docs/13/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC) which truncates the input date to the specified date precision, in our case month

SELECT date_trunc('month', issue_date) AS "ticket_month", COUNT(*) AS "tickets_count" 
FROM "nyc_tickets"
WHERE issue_date BETWEEN '2013-07-01 00:00:00+00' AND '2017-07-01 00:00:00+00'
GROUP BY "ticket_month" 
ORDER BY "ticket_month" ASC

Link to live query

What time of the day are you most likely to get a ticket:

SELECT COUNT(*) AS "vehicle_count", concat(left("violation_time", 2), right("violation_time", 1)) as vtime
FROM "nyc_tickets"
GROUP BY vtime 
ORDER BY "vehicle_count" DESC

Link to live query

Distribution by month

SELECT to_char("issue_date", 'month') AS "ticket_month", COUNT(*) AS "tickets_count" 
FROM "nyc_tickets"
WHERE issue_date BETWEEN '2013-07-01 00:00:00+00' AND '2017-07-01 00:00:00+00'
GROUP BY "ticket_month" 
ORDER BY "ticket_month" DESC

Link to live query

July is the most quit months, while October, March and May are when more tickets are issued.

Tickets issued by Police precinct

SELECT COUNT(*) AS "vehicle_count", "violation_precinct"
FROM "nyc_tickets"
WHERE "violation_precinct" != 0
GROUP BY "violation_precinct"
ORDER BY "vehicle_count" DESC

Link to live query

Visualising geographical distribution is a bit hard because the initial dataset doesn’t have geodata in it. Enriching the data by geocoding the address would not be possible either, as geocoding of 42M rows will cost approximately ~$20,000.

Instead, we can use the police precinct id as the geolocation source and get the geodata from here.

Source Code and Demo

You can find the source code for this project in my Github repository, and the live dashboard is hosted in AWS Cloud in here.

Thank you!