RETO MEIER: Today, I learned where
rats like to hang out when they're in New York City.
And which neighborhoods complain too much, and which too little,
about seeing furry rodent visitors.
I'm Reto Meier, Google Developer Advocate.
Join me as I upload and then investigate the New York City
rodent inspection data set with BigQuery.
Using the 311 one table from the New York City public data
sets in BigQuery, we can see a dramatic increase in complaints
about rats starting in 2013.
If we bucket those reports into locations,
we can even create a heat map that
shows just the density of rat sightings reported
across the city.
But that only tells us part of the story.
To better understand the true extent of rats in New York,
we need to get more data.
New York City Open Data Project includes
this table of written inspections across the five
boroughs.
We're going to load that data into BigQuery.
We'll start by downloading the CSV file, and while we're here,
let's grab the data dictionary and import that into Google
Sheet so we can use the schema.
Now we go back to BigQuery.
Select a data set and press the plus symbol
to create a new table.
For small files up to 10 megs, you
can upload it here directly.
But our vermin table is around 200 megs,
so instead we'll upload it to Google Cloud Storage first.
You can also import data from Google Drive,
but we'll save that for another episode.
Once we have a location for our newly uploaded rodent table
CSV, we'll return to BigQuery and enter that,
as well as providing a name for our new table.
Then it's just a case of specifying a schema
by adding the name and type of each column.
We'll use the data dictionary we imported
into Google Sheets earlier as a guide for the column names
and to get the corresponding data types.
We won't need all of these columns for our analysis,
but there's no cost for importing data
so we can happily pool it all in now
and then trim our table later.
In this table, it turns out that the time stamp
format used in the CSV file isn't compatible with BigQuery.
So we'll treat it as a string for the initial import.
Once the import job is done, we can review the new table.
Let's immediately use it as a source
of creating another table.
This one will include only the columns we need,
latitude, longitude, borough inspection, type, and results.
And convert the time stamp strings into real time stamps.
Once we save the results to a new table,
we can delete the CSV file from Cloud Storage and the larger
original import table.
BigQuery charges $0.02 per gig, per month,
pro-rated each second.
The table we're going to use is only 63 megs,
so we'd need to keep it for almost eight months
before we got charged a full cent for using it.
With that in mind, we're all set to continue our investigation.
We'll start by finding the total number of inspections conducted
each year.
We can see a trend similar to the 311
calls over the past three years, but with an odd bump in 2012.
One of the advantages of using this new table
is that it also includes inspection results.
We can see that about 75% of the time, the initial inspection
actually passes.
Now if we limit our results to inspections
that find active rat signs, we can
see that the 2012 bump corresponds
to a significant increase in inspections, but not in rats.
That's not the case in 2016, increases across the board.
But New York is a big city.
If we examine the trends per borough,
looking only to inspections that found active rat signs,
we can see that while there was an uptick across the board,
Manhattan and Brooklyn are definitely
catching up to the Bronx, crowned rat capital in 2011.
We can continue our investigation
by using the locations of each specific inspection
to create heat maps telling which neighborhoods have
the most rat signs and what the current trends suggest
for the future of rats in New York.
Check out the blog post listed in the description below
to see where those investigations lead.
BigQuery includes a range of public data sets,
including over 130 gigabytes of New York City data,
including calls to the 311 municipal complaints line.
As always, your first byte of data processing each month
is free.
Go to BigQuery today to import your own data sets
and share the results with us using the TILwBQ hashtag.
And tune in each week to learn more about BigQuery
with tips and tricks.
Subscribe to the Google Cloud YouTube channel
and follow our blog to learn something new with BigQuery.