JSON Values "key","value" as Columns

[alextruesdale]

Hello,

I have a .csv with nested JSON rows that I would like to break into columns. I have been following steps from a similar posting from earlier this year, which have me close to my goal.

An example input row looks like the following (formatted here for legibility):

[{
     "key":"Pattern",
     "value":["Solid"]
},
{
     "key":"Worldstock Country",
     "value":["Peru"]
},
{
     "key":"Neck Style",
     "value":["Scoop Neck"]
}

... and it continues

My workflow gets me to this point so far:

RecordID	Key	Value
1	Pattern,Worldstock Country,Neck Style,Sleeve-length,Front,Care Instruction,Closure,Dress Length,Size,Dimensions,Model Number,material	Solid,Peru,Scoop Neck,Long Sleeve,Flat Front,Hand Wash,Pull Over,Short,L, M, S, XL,See Details,231516,Acrylic, Alpaca Wool
2	Recommended Use,Primary Color,Battery Type,Heel Height	NOT STATED,Multi-Color,Does Not Contain a Battery,Flat/Low Heel (Under 1\
3	Heel Height,Material,Gender,Shoe Size,Size,Color,Model,Casual & Dress Shoe Style,Manufacturer Part Number,Brand,Age Group,Shoe Width	Low (3/4 in. to 1 1/2 in.),Leather,Women,7,7,Black,FAWNA-BLKMUL,Oxfords,FAWNA-BLKMUL,Marc Fisher,Adult,B(M)

This is close to what I want to achieve - something like this:

(important that the recordID be preserved)

RecordID	Pattern	Worldstock Country	Neck Style	Sleeve-length	Front	Care Instruction
1	Solid	Peru	Scoop Neck	Long Sleeve	Flat Front	Hand Wash

What is an efficient way to achieve this final outcome? Workflow attached.

Any and all help/suggestions greatly appreciated.

[NickC · Accepted solution]

Hello Alex,

The workflow you sent didn't include the text file you were working from, so I have built the solution from where you got to... 

RecordID	Key	Value
1	Pattern,Worldstock Country,Neck Style,Sleeve-length,Front,Care Instruction,Closure,Dress Length,Size,Dimensions,Model Number,material	Solid,Peru,Scoop Neck,Long Sleeve,Flat Front,Hand Wash,Pull Over,Short,L, M, S, XL,See Details,231516,Acrylic, Alpaca Wool
2	Recommended Use,Primary Color,Battery Type,Heel Height	NOT STATED,Multi-Color,Does Not Contain a Battery,Flat/Low Heel (Under 1\
3	Heel Height,Material,Gender,Shoe Size,Size,Color,Model,Casual & Dress Shoe Style,Manufacturer Part Number,Brand,Age Group,Shoe Width	Low (3/4 in. to 1 1/2 in.),Leather,Women,7,7,Black,FAWNA-BLKMUL,Oxfords,FAWNA-BLKMUL,Marc Fisher,Adult,B(M)

The format you want the data in doesn't work particularly well because as you can see the 'key' changes for each record ID.  I have got it in the format you want but it still shows a lot of blanks.

 Hope this helps.

Thanks,

Nick

[alextruesdale]

Hi Nick,

Thank you for the response and solution. I recently came to very similar conclusion (admittedly with a few extra steps).

There are quite a few empty spaces with the format that the data is in; for the time, that will be a separate issue -- happy now to have it in a more machine-readable format. 

I have posted my workflow below as a reference for others if they want a similar approach.

Many thanks (text file to be included next time),

Alex