Challenge #7: Download Data and Parse JSON
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Welcome to 2016 we hope you are enjoying these challenges. The link to the solution for last challenge #6 is here. For the seventh challenge let’s look at downloading data with an API and parsing that data from JSON into a usable format.
The data we will use comes from Quandl. The Quandl site offers access to several million financial, economic and social datasets. Data is indexed from multiple sources allowing users to find and download in various formats. All Quandl's data are accessible via an API.
For this example the response from these APIs is JSON. Our user is trying to get aggregated Annual Outbound Tourism Statistics for the US dating back to 1995. The Text Input contains the URL for the API request. Your goal is to parse the response.
Hint: After parsing the JSON, you will need to further identify the patterns within the data to effectively stage into a table for analytics.
Note: The data in the API is subject to change. When trying to match the output, the effort should be focused on achieving an identically structured dataset.
We have listed this as an advanced challenge since configuring the download tool and parsing functions are more advanced topics. We are looking forward to hearing your feedback.
UPDATE 1/11/2016: the solution to this exercise can be found below.
UPDATE 4/10/2019: Previous API link expired. Exercise updated to new API.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This one is very nice! And i think relatively frequently used example. Great for playing with data from internet and so many options how to get this done. In the end i used a lot of tools here.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The Exercise has been updated with the solution. Thanks for playing along.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This was a tricky one. On first connetion I got an error message in the download field and thought the URL had gone bad. Manually checked it in the browser and saw that it was actually part of the header of the dataset. Sneaky
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for this great exercise. We're using this as part of our certification practise.
Just a pointer that the output data appears to now have moved/changed (so the challenge output is now out of date), which is perhaps understandable if pulling from an API.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Ok, not sure if this is the best way, but hey, it's Alteryx!
I used the Dynamic Rename tool, but I sure this could be sorted and joined in a different way which may be more efficient. Anyway:
I then split the resulting data into two streams based on filters, first to isolate the Column Headers:
and then the data:
The data stream was then pivoted so the Field Numbers are the headers of the columns.
I then used the Dynamic Rename tool to join the data but replacing the numbered headerfs with the header values from the Columns stream:
I then simply sorted by row and dropped the unwanted columns:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Very similar solution to the one provided except....
However - the provided solution does a better job of ordering the fields, so that it doesn't have to be done manually using a select tool.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Happy Sunday
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.