Split nested json data into columns and rows

[Daesung_Kim]

Hello folks! I just started using parabola and found it very useful!

Btw, I want to split following json data into columns for confirmed, deaths, and recovered, and rows for each date.

{“1/22/20”:{“confirmed”:1,“deaths”:0,“recovered”:0},“1/23/20”:{“confirmed”:1,“deaths”:0,“recovered”:0},“1/24/20”:{“confirmed”:2,“deaths”:0,“recovered”:0},“1/25/20”:{“confirmed”:2,“deaths”:0,“recovered”:0},“1/26/20”:{“confirmed”:5,“deaths”:0,“recovered”:0},“1/27/20”:{“confirmed”:5,“deaths”:0,“recovered”:0},“1/28/20”:{“confirmed”:5,“deaths”:0,“recovered”:0},“1/29/20”:{“confirmed”:5,“deaths”:0,“recovered”:0}, …

I tried json flattener, but it just split all data into columns which is not I expected.

What I want is to group data with same date into a same row.
Could anybody help me out with this?

Thanks in advance!

[brian]

Hey there!

This type of JSON structure can be tricky to deal with.

Paste this into your flow, to add the steps that I created for you:

parabola:cb:8f8ee3c487034cfcb507c2f4743a5d57

Connect your API data to that column filter and it should work!

Here is what the sequence looks like:

We expand the data into new columns, as you have in your first screenshot, and then we flip that table to turn the headers into rows - the headers have the information we are after to create a nice looking table.

Then we rename it to make that column friendly looking.

Then we split the column containing the old column names, since they are in JSON dot notation, there are names like timeseries.1/22/20.confirmed and timeseries.1/22/20.deaths and we want to have confirmed and deaths as headers, but want the dates to be rows - so we need to separate them!

Then we remove the extra column that was created so that it doesn’t mess up later steps.

Then we use a Pivot columns step to move those labels up into the column headers, and that consolidates the data into our timeseries table!

[Daesung_Kim]

Super!

Thank you so much for your help. This saved my day!
I gotta recommend Parabola to my colleagues.