Makeover Monday’s week 21 dataset was about bear attacks in the United States and Canada since 1901.
Given the type of data, I thought it would be interesting to plot the attack sites on a map, somehow. However, there were no coordinates on the dataset, only info about State names (though with no distinction between Canada and the US). Then I remembered hearing about Google’s geolocation API and when we explored Alteryx’s spatial tools. Also, I remembered Jonathan’s blog post, where he explains how he used it to find nearby gym locations.
Getting started with the API
Google has plenty of useful documentation on how to leverage the capabilities of its geolocation API – I highly recommend giving it a read if you’re thinking about using it. After that, you’ll need an API key. You can request it here.
Now, you just need your trusty alteryx designer, and you should be ready to start apiing. The first steps, as you can see in the print below, are to get the data into alteryx and start to clean it and get it into the request URL shape.
Our end goal for the request URL looks like this:
As you can see, we’re requesting the data to be downloaded in JSON format. After that, we have three separate paramaters. The first in an address (1600 Amphiteatre Parkway), followed by a region (Mountain View) and a state code (California). Finally, you’ll need your API key. As you’ll see, the requests to the API won’t need to have such a detailed address as in the example above, but we’ll have to type in the parameters in the same format.
Going back to our bear attacks exercise, the addresses we have are kind of vague. We have one field for a site, and one for the state. Each word in each of the fields will have to be separated by a ‘+’, something we can easily do in alteryx.
Just before showing you my workflow, this is what the response from the API will look like:
There’s a bunch of info we’re not interested in. We’ll be focusing only on lat and lng fields under “geometry” : { “location”.
Downloading and parsing the data with alteryx
To get those fields in alteryx, we’ll have to use the JSON parse tool, after some easy data prepping steps, like cleaning out words like ‘near’ from the site location column and also getting rid of all the leading and trailing white spaces in the text columns.
Another important step is to create a record ID for each row so that we can join the latitude and longitude columns to the original data later on. Check out my alteryx workflow below, which shows the downloading and parsing of the data obtained via the API request we just built and a preview of our end goal.
(click the image to enlarge)
Now that we have everything we need from the API, we can take the data to Tableau and visualise every attack and a more precise location. Click the image to open the workbook on Tableau Public.
