APIs are an essential tool in your search for exciting datasets to go forth and visualise. They allow you to pull in data from a database somewhere in the world, and even better they are often live and will update through each call.

For an extensive list of APIs you can use, check out this list. Be aware that some of the APIs require an authorisation key, if you’re just starting up go for one that doesn’t require one.

I will take you through a very basic example of calling an API within Alteryx and extracting the data you need ready for Tableau. In this example I will be using NFL arrest data, fire up Alteryx and let’s go!

An API is called by using a URL to bring in the data. It can be tested straight from your browser. Each API usually comes with extensive documentation for how to use them. For our NFL arrests API, the URL to look for a list of crimes committed is NflArrest.com/api/v1/crime … In our browser it is like so:

• So in Alteryx we want to use this URL to fetch our data from the database. Pull in a text input, rename the first column ‘URL’ and in the row below simply paste in the URL call.

• The next tool we will use is download, this step essentially fires up the API call to collect our data. Drag in the tool, make sure the dropdown is set to URL and hit run

You’ll see in the data pane three columns, the ‘DownloadData’ field contains our data.

• The data is in a JSON format, not very useful at the moment. Thankfully, Alteryx has the perfect tool; JSON Parse. Drag it in and make sure it is set to the DownloadData column

You should start to see the data coming together

• We need to split out the ‘JSON_Name’ column so we can get to what are going to become our column headers. So drag in the ‘Text To Columns’ tool and set the delimiter to ‘.’. We’ll only need to split to 2 columns.

Our results should look like so:

We have our column headers in JSON_Name2 alongside our values in JSON_ValueString. The JSON_Name1 column which actually come in useful. We need to pivot our data and that column will allow us to group by that column. This ensures there is an association between each column header and its value, and that they create a row for each, not just all in one row.

• Before we pivot we’ll drag in a select and keep only the three columns JSON_ValueString, JSON_Name1 and JSON_Name2

• Let’s drag in the CrossTab tool. We need to group by JSON_Name1, make JSON_Name2 the column headers and JSON_ValueString the value. Tick concatenate for the method.

Check out what we have now:

We’re almost there now!

• Add a select and just deselect the JSON_Name1 field.

• Add a sort tool and just order by arrest_count, descending.

There we have it:

The data is now ready to put into Tableau and visualised!

Maybe something like this:

I hope this helps show you a very basic guide to calling an API to grab some data and start visualising.

In my next blog I show you how you can extend this and start exploring more of the database by appending the above crimes onto another URL from within the API to start finding information about the NFL teams committing these crimes.