Transform each value of a Collection type column to a yes/no column, put the value as column name

I have this dataframe:

ID, Relations
X1, [account, loan]
X2, [loan]
X3, [account]
X4, []
X5, [loan, card]

Relations is a collection type column

Now, I want to create this

ID, account, loan, card
X1, yes, yes, no
X2, no, yes, no
X3, yes, no, no
X4, no, no, no
X5, no, yes, yes

Hi @soumyabg and welcome to the Knime Community.

If there are only these 3 columns, it can be quite simple and be done in a Column Expressions node. I put something quickly together for you.

The workflow looks like this:
image

Input data (same as yours):
image

Results (same as you want):
image

Basically, in the Column Expressions, I’m simply checking if each of the 3 values exists in the collection. If exists, return “yes”, else return “no”, and save result in a new column under the name of the relation I am checking:

Here is the workflow: Transform values to columns.knwf (10.6 KB)

5 Likes

Great! This is a working solution, but I am not marking it as the “Solution” because if there are unknown or too many values in the collection type column, this solution will not work or will become very repetitive.

Hello @soumyabg,

and welcome to KNIME Community!

Check this workflow:

Br,
Ivan

2 Likes

Hi @soumyabg , sure, no problem, as I said “If there are only these 3 columns” :slight_smile:

If you want something more dynamic, I’d convert to json first, as json structure allows me to have different “columns” if you will, and then convert back to columns. Knime will automatically create the required columns dynamically.

The new workflow looks like this:

As expected, I get the same results as before:
Input (same as yours):
image

Results:
image

Pushing the test further, I added new relations as input data:
image

Results:
image

So, it will dynamically create the columns based on the different types of Relations the input data has.

Here’s the new version of the workflow:
Transform values to columns dynamically.knwf (21.7 KB)

3 Likes

Hello @bruno29a,

have seen some nice and useful workflows you done lately and wondering why are you not using KNIME Hub to store those? More users will find them and benefit from them, upon modification link still applies and it’s easier for you to locate them and share in another topic…

Br,
Ivan

2 Likes

Hey @ipazin , I guess I’ve not gotten to it yet. In all honesty, I don’t know how to use the Hub to store my workflows :smiley:

I’m relatively new to all of this. Though I registered to the forum I think November of last year, I’ve only started using it mid-March of this year.

I’ll look into how to store workflows in the hub when I can. Is there any quick guide that I can follow?

2 Likes

Hi @bruno29a -

Scroll down on the page below to “Share & Collaborate” and you will find a very short video that shows exactly how to do it:

3 Likes

Thanks @ScottF , I will look into it

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