Reconcile data from GlobalNames API into separate columns

Support and Helpdesk Data cleaning and transformations
1

Hello,

I am using the GlobalNames API to reconcile data. The version I am using is 3.6.2.

Here is a screen shot below.

image
image489×758 27.2 KB

This is the data.
[{"taxon":"Biota","rank":"unranked"},{"taxon":"Plantae","rank":"kingdom"},{"taxon":"Tracheophyta","rank":"phylum"},{"taxon":"Magnoliopsida","rank":"class"},{"taxon":"Myrtales","rank":"order"},{"taxon":"Myrtaceae","rank":"family"},{"taxon":"Myrtoideae","rank":"subfamily"},{"taxon":"Kunzea","rank":"genus"}]

What GREL can I use to extract this into separate columns based on 'taxon' and 'rank'?

Thanks,
Johno

2

Something like "Add column based on this column..." with

forEach(value.parseJson(),i,i.taxon).join('|')

for taxon and rank to create two new columns and then "Split multi-valued cells..." with the pipe (|) character as your delimiter should do the trick.

Tom

3

That works. Brilliant, thanks so much!