I have a table that contains a field with key value pairs (it’s a gff file).
Each row contains a different set of key value pairs in that field.
I would like to map the values into new columns with the keys as headers. I tried the ‘Cell Splitter’ already but that works best for a fixed split with a fixed output.
Do you have any pointers how to approach this?
Hi @mpreusse and welcome to the Knime community.
You can use Cell Splitter, but there are more operations to be added to accomplish what you want to do.
The main challenge here is that your records might not all have the same keys. For this type of structure, I usually use JSON, as that’s exactly how a JSON “structure” is, and also because converting JSON to a table, Knime will automatically create the necessary columns dynamically.
If I look at your data, I am assuming that each pair is delimited by a semi-colon (
;), and that a pairing of key value is determined by
key=value format. I am assuming it’s 2 lines, therefore 2 records, is that correct? I’m making this assumption because I see the key
I have put something together based on these assumptions, and here are the results:
The workflow looks like this:
And I used the input that you gave:
Here’s the workflow: Expand key-value pairs into columns.knwf (10.4 KB)
Thanks @bruno29a, that is a really elegant solution!
I am new to KNIME and did not know about the JSON nodes before.
Next step is to run it in parallel because I have around 3 million rows with the key value pairs
No problem @mpreusse , it should still work with 3 million rows. It’ll just require some memory, and will take longer to run.
I tried it without chunking or parallelization and it was really fast, took a few minutes. Really impressive!
Hi @mpreusse , yes, it should be ok without chunking/batches or parallelization. There’s really not much happening there. They’re straight forward operations.
Glad that it worked very well for you.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.