replace " yes " by 1 and " no " by 0 multiples columns in knime

Hello everyone, I have a CSV according to the image where it has 57403 lines and 385 fields, the lines have “yes” and “no” values, I need to replace these values where “yes” is 1 and where “no” is 0 .
I researched a lot before making this post, I found the solution “column list Loop start” with on rule engine "as shown in the image, but I’m having trouble understanding how to do what I need, can someone help me?

Hi @Ranalytics,

You need to create the expression as a flow variable in the String Manipulation (Variable) node first and then use it in the Rule Engine node.

But it’s more straightforward to use the Column Expressions node like this:

if (column(variable("currentColumnName")) == "Yes") 1
else if (column(variable("currentColumnName")) == "No") 0

:blush:

2 Likes

I created the column expression as you said, but it still didn’t work, because I attached the workflow because I don’t know what I’m doing wrong

Hi @Ranalytics

if you want to share workflows you can also upload them on our hub: hub.knime.com
This way you have control over what you are sharing. If you need more information you can find it on the about page: https://hub.knime.com/site/about

3 Likes

You have to check “Replace Column” option for the expression and set the “outputName” option in the Flow Variables tab to currentColumnName.

Recipe.knwf (63.4 KB)

:blush:

2 Likes

Again, thank you very much for your help, on my machine this workflow is taking time to process this “Loop” I’m using a dell notebook my processor is a 2.4GHz i7 CPU, 8GB ram
video card GeForce 830M 2GB.
Is this delay normal?

Hi there @Ranalytics,

considering you have 385 columns it should take some time as in each iteration one column is processed. To speed up your KNIME you can always assign more memory to it. (Check this blog on how to do that and other optimization tips&tricks - blog is a bit older but still lot of points valid.)

Regarding solution with loop you currently have I would use another one. And that is Unpivoting node to have all columns that need replacement in one column. Then you don’t need loop as one Rule Engine covers it all. After replacement use Pivoting to obtain original data structure. Should be much faster than using loop but also considering your table properties won’t be finished in 2 seconds obviously.

Here is example workflow which you can download and check:


Additionally there is ticket (Internal reference: AP-13315) to create node which would allow search and replace functionality over multiple columns. Will add +1.

Hope this helps!

Br,
Ivan

4 Likes

Thank you very much, for the tip, I had done it with unpivot rule engine and then pivot, but it was taking too long then I saw the solution on the Loop forum and decided to use everything, I am already grateful because I did not know how to use Loop on knime, I found it that it was faster because in pytho it is very fast, but I am really enjoying the knime, I know that creating workflows from data sciences are not as easy as it seems, so I am going little by little, and when I do not move forward in the knime I ask for help, a lot thank you all !

1 Like

Helllo!

From new, 4.2.0., KNIME version now there is one node solution for these kind of tasks: String Manipulation (Multi Column) node.
https://www.knime.com/whats-new-in-knime-42#ux-improvements

Enjoy!

Br,
Ivan

3 Likes

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