topic Re: Challenge #7: Download Data and Parse JSON in Weekly Challenges

Challenge #7: Download Data and Parse JSON

GeneR — Tue, 13 Sep 2022 13:50:41 GMT

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.

Re: Alteryx Weekly Exercise #7 Download data and parse JSON (Advanced)

martinbosak — Tue, 05 Jan 2016 08:27:04 GMT

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.

Re: Alteryx Weekly Exercise #7 Download data and parse JSON (Advanced)

dataMack — Tue, 05 Jan 2016 14:32:07 GMT

Great exercise here. I'm finding the need to parse JSON data more and more. It'll be interesting to see the variety of ways people choose to wrangle the data after the initial JSON parse- I can think of a few different options.

Re: Alteryx Weekly Exercise #7 Download data and parse JSON (Advanced)

TaraM — Mon, 11 Jan 2016 16:53:37 GMT

The Exercise has been updated with the solution. Thanks for playing along.

Re: Alteryx Weekly Exercise #7 Download data and parse JSON (Advanced)

CaptCube — Thu, 09 Jun 2016 02:37:37 GMT

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

Re: Challenge #7: Download Data and Parse JSON

TomWelgemoed — Wed, 28 Dec 2016 14:14:02 GMT

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.

Re: Challenge #7: Download Data and Parse JSON

mceleavey — Wed, 25 Jan 2017 12:07:31 GMT

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 began by simply feeding the URL into the download tool and parsing the JSON file using the JSON Parse tool:

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:

Re: Challenge #7: Download Data and Parse JSON

SeanAdams — Sun, 05 Mar 2017 14:57:26 GMT

Very similar solution to the one provided except....

left the data in a transposed state, and then used a join to replace the field names (similar to what @mceleavey mentioned)
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.

Re: Challenge #7: Download Data and Parse JSON

KatieA — Wed, 08 Mar 2017 04:37:56 GMT

Solution attached.

I really liked @GeneR use of the Dynamic Rename!

Re: Challenge #7: Download Data and Parse JSON

MarqueeCrew — Sun, 02 Apr 2017 13:31:11 GMT

Happy Sunday

Cheers,

Mark

Re: Challenge #7: Download Data and Parse JSON

NicoleJohnson — Sun, 09 Apr 2017 03:59:40 GMT

My solution.

Was able to solve using most of my usual suspects (Cross-Tab, Text to Columns), but managed to get away from using multiple filter or sample tools by switching to the Select Records tool, which I have never used before... seems to me a good solution as long as the data structure doesn't change. But probably not a great solution if additional columns were ever added to the source data. Would need to think more dynamically in that case, perhaps using Multi-Row Formulas or parsing Formulas + Tiles?

Re: Challenge #7: Download Data and Parse JSON

Laurap1228 — Sun, 16 Apr 2017 13:15:32 GMT

Challenge 7 done!

Re: Challenge #7: Download Data and Parse JSON

estherb47 — Wed, 26 Apr 2017 20:23:26 GMT

Re: Challenge #7: Download Data and Parse JSON

DE0413 — Fri, 05 May 2017 16:37:03 GMT

I haven't worked with JSON before so after help research and trial and error...here is my solution

Re: Challenge #7: Download Data and Parse JSON

Phil_L57 — Tue, 09 May 2017 13:19:11 GMT

Great exercise for dealing with JSON format data. Coincidentally I just created this exact workflow during a training session I was running today.

I connected to Quandl to collect Crude Oil Prices

Re: Challenge #7: Download Data and Parse JSON

LordNeilLord — Sun, 21 May 2017 18:43:42 GMT

I'm getting the hang of JSON now :)

Re: Challenge #7: Download Data and Parse JSON

Pavel_G — Tue, 30 May 2017 10:48:49 GMT

My second challenge.

Re: Challenge #7: Download Data and Parse JSON

iiyoob — Fri, 09 Jun 2017 17:46:33 GMT

My solution - @NicoleJohnson's idea with a generic OR.

Re: Challenge #7: Download Data and Parse JSON

KOBoyle — Tue, 13 Jun 2017 20:45:00 GMT

Solution attached. On the JSON parse, I used the option to 'Output values into data type specific fields' which led to a slightly different solution. The Dynamic Rename used in provided solution looks useful. I'll keep that in mind.

Ken

Re: Challenge #7: Download Data and Parse JSON

philipmannering — Sun, 06 Aug 2017 21:35:12 GMT

Had to do a few of these API Calls before, so this was a quick one.