Alteryx , flat limitations, Pets and Tableau. What Else?
This is my first blog post on the Data School Blog, is a foggy friday in London Town, and...what can make life brighter and more fun? A Dog of course! Unfortunately i am not allowed to keep pets in my flat...so i decided to be creative and find a tech solution. I will try to get some data from "The Dog API" and then use it for a Tableau Viz that will show you some info about your favourite dog breed! Are you ready?
In this lovely site you can find more than 120 breeds of dog, with useful informations on their standard weight, size. There is also a Picture sections, that we are going to use for our dashboard!
It's friday , so we are meant to relax. I will guide trought every step, and...check the blue underlines for extra useful infos!
Alteryx: authentication, data preparation and picture download.
We have an amazing dog API service, our mighty Alteryx ..let's get it on!
This is my final Alteryx flow, divided in 4 parts: the Api and Download One, where we connect to "The Dog Api" , the "Making Data Usable" , the "Temperament split" and the "Get Images" Part. The title are pretty self-explanatory, but i will guide you trough every passage so you can understand what is going on !
Api and Download
I took a look to the official Dog API Documentation that you can find at https://docs.thedogapi.com/api-reference/ .
There are a bunch of calls that we can do but the main data is contained in the call called "breeds". The other calls will retrieve data that is already contained in the "Breed" data body.
You have to sign up to their API service (no worries, is free!) and after a couple of minute you will receive an API key, that will act as an authentication. We are ready to go!
Lets get our hands dirty and lets open Alteryx!
- Insert a text input with the API that we want to call.
In this case we will input https://api.thedogapi.com/v1/breeds - Insert a download tool.
We will download the data and authenticate to the API at the same time.
Go in the Basic configuration part and in the URL field select our "url" field.
We will authenticate now. In the Header panel add
x-api-key to the name field and the API key you have received before via email .
Making data usable
In this part of the flow we have downloaded our data . But...is not that pretty...
The download tool is succesfully connected to the API, and now we have to clean everything. The file that we have downloaded is in the JSON format, that needs to be parsed to be usable. The flow in the photo is a classic Json parse flow, divided in 3 parts:
- The JSON Parse tool
Extract to multiple rows the content of the data that we previously downloaded.
Simple configure it selecting the Json Field called "DownloadData".
Et Voila'.
2. Text-to-column Tool, and Cross Tab Tool
To use this data in Tableau, we want our JSON_Name field to be Column Header and the JSON_valuestring our new column values.
To do so lets configurate our tools in this way.
We have splitted our name field . As you can see each dog breed now is marked with a number in the Json_Name column, and all the headers are on the Json_name2 columns.
Time to Group them and pivot their values.
As you can see in the configuration tab of our Crosstab Tool we have grouped by JsonName1, changing column headers to the JsonName2 value .
Values for the new columns will be the Json_Valuestring fields. Choose Concatenate as an aggregate value .
The Cleaning tool after the crosstab will deselect fields that are completely empty in our dataset.
The "Temperament field" was filled with a long list of temperaments that i wanted to split to multiple columns. They can be useful for a further analysis on Tableau!
I then added a Data Cleansing tool to remove the null values and some trailing spaces.
Get Cute Images!
A dog database and no cute dog pics? NO PLEASE! We want also all the photos of the amazing breeds that we have just listed!
In this dataset we also have a link where we can download the photo for each breed. We can download it and do a simple trick to make them more usable in Tableau ( they are a LOT ! ).
To Extract the urls of all the images you can simply use a select tool to choose the url column and...this is the useful trick. Keep the Json_Name1 as an unique identifier and make it a string, and then call it "file Name" You can append this number to the images that you will download.
Use the same ol'good download data tool to dowload the BLOB pics .
Use the blob Output. You will be able to download each image url and then append the name that you want. In the Blob output follow the configuration in the picture. You will append the filename field to the file name of your photo output!
And...this is the result!
We have an ordered serie of photos that can be easily imported in Tableau!
The Final Tableau
As a friday project, i had limited time to finish the my task, so i have ended up with a simple viz that shows the photo and some infos of your favourite breed!
What i have learned
This Friday project was extremely useful for me because it helped me getting a grip with the basic API requests. I always prefer to do simple things at the beginning to learn all the basic techniques that I need for one tasks: solid basis are a must have for a constant improvement!
I really hope that you enjoy the process and that it was readable and...enjoyable.
Now please grab a beer, some friends , your partner and have fun.
It's friday again!!
