Merge list and string column into a new list

I have a table with a list in one column and a string in another column. The string in the second column only exists if the list is empty.

Bildschirmfoto 2021-09-26 um 19.10.15

I would like to merge the second column into the list, i.e. whenever the list is empty, I would like to create a list with one element which is the string from the second list.

I tried various nodes (such as Column Merger) but did not find a way yet to do proper type casting.

Note: This is the result of some data processing which can be improved. I still think the question is relevant.

Hi @mpreusse

Just a quick answer for a problem I believe can be solved as follows:

  1. Convert the -accession_single- column into a list, for instance using the -cell splitter- node with list configuration. It should generate a column of type list with empty lists where the cells are empty and with lists made of just one single token (your word) where the token exists.

  2. Then use a -Rule Engine- node to replace with the new created list, the empty cell content in the -accession- column only if -accession_single- is not empty.

The solution should look more or less like this:

image

The -Cell Splitter- node configuration should be like this one:

In the Settings, just enter a -blank space- as Delimiter.

and the -Rule Engine- node configuration could be as follows:

MISSING $accession$ => $accession_single_SplitResultList$
TRUE => $accession$

This should do the trick. If this is not clear enough, please post your data example in a minimalist workflow and I’ll take it from there to provide you with the corresponding solution.

Hope this helps.

Best,

Ael

6 Likes

Very nice @aworker . You can also use the Create Collection Column to convert the column to a list :slight_smile:

Demo:
image

Input:
image

Config:

Result:
image

This is presenting an alternative only to the use of the Cell Splitter part. The rest would be the same, that is applying the Rule Engine.

4 Likes

Thank you so much @aworker and @bruno29a!

Solution works perfectly. I used the Create Collection Column followed by the Rule Engine. I have never used the Rule Engine before (still pretty new to KNIME).

For reference, here is the rule I used to match empty collections:

$accession$ = "[]" => $accession_single$
TRUE => $accession$

MISSING does not work with empty collections.

3 Likes

Hi @mpreusse

Thanks for your feedback and for validating the solution. You are right, an empty list is not a missing value and you nicely found a solution to check the case of empty list. Amazingly, the -Rule Engine- node automatically converts a list type into a string type when comparing a list to a string as it is the case in your solution. Good to know it :smiley: !

Thanks @bruno29a for suggesting the alternative solution too based on the -Create Collection Column- node.

Best wishes,

Ael

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.