new column based on row logic

[ogon]

Dear all,

i´m facing a new challenge in KNIME and want to know if you can help me with the following:

I want to create numbers in column “D” which refers to some rows logic. It should give me “1” if A from column “A” had anything else than “Tree” in column “C” in 2020. The list is much longer and it´s not only 2021 & 2020. In any other cases it should give a “0” in column “D”. And the output should appear only in 2021.

How can i solve this?

[gab1one]

Hi @ogon,

The nodes that can help you do this are either the Rule Engine node or the Column Expressions node. They allow you to define custom transformation rules. Take a look at his example workflow on how to use them:

Column Expressions for Data Manipulation – knime

[ogon]

Hi @gab1one,

thanks. I already tried it with column expression. This is my code:

if ((column(“Col1”)==“Tree”)&&
not(column(“Col2”)==“Tree”)){“1”;}else{“0”};

But it´s not the desired output:

[gab1one]

With the following code, I can create a table like the one you had in your first post:

out = 0
if(column("column2") == "2021" && column("column3") == "Tree"){
   out = 1 
} 
out // this is needed so that the expression returns this as the new value

I have this input:

and that output:

best,
Gabriel

[ogon]

Hi Gabriel,

thanks. But it´s not that easy.

I want to create “New_Column” depending on rows from “2020”, meaning if “A” has no “Tree” in 2020 then it should show a “1”. As you can see in former output for C it is showing a “0” because it was in 2020 also “Tree”.

[ogon]

this is the desired output:

[gab1one]

Hi @ogon,

That is a bit more involved, I used the pivot and unpivot nodes to get the data into a shape that makes sense for the column expressions node and then to restore the original data layout:

Pivoting example – gab1one

best,
Gabriel

[ogon]

is there a way i can check the logic in rows directly in the column expression? Means:

if column2=2021 & column3=Tree & not column3= Tree in column 2=2020?

[gab1one]

Hi @ogon,

the column expression node looks at each row independently. So you can’t do any logic that spans several rows. You would need to use a node that loads the whole table at once, like the Python Snippet.

best,
Gabriel

[ogon]

Hi Gabriel,

thanks a lot, that helped me! One last question:

How can i set an “Or” statement within expression? I want to expand Tree with “Lights”. If it´s not tree or lights in 2020…

out = 0
if (column(“2020”) != “tree” && column(“2021”) == “tree”) {
out = 1
}
out

[ogon]

Hey Gabriel,

here´s is what im facing right now. I have blank columns. This is the desired output:

How can i create a nested if clause with “or” and “and”? The “1” should appear in column D if Column C=Tree and only if column B=Lights or Street.

Thanks!

[gab1one]

Hi @ogon,
You can easily nest expressions, and you can use the || symbol to express or:

out = 0
if (column(“2020”) != “tree” && column(“2021”) == “tree”) {
   if (column("B") == "lights" || column("B") == "street") {
       out = 1
    }
}
out

best,
Gabriel

[takbb]

Some time later… … The specific case of logic for one row based on another rows can sometimes be resolved by thinking in terms of “sets” rather than specific rows.

Here is a workflow that does just that to try to solve the problem that you have posed in your sample data. It may or may not work for your additional data, but you may be able to adapt the idea to assist you. What I have done is to take your primary data set and create from it a subset of items that did not have a tree in the 2020 row. By then joining back with a “left outer join”, you can infer which items should be “1” and which should be “0”. Some additional logic is added in a “rule engine” to set all items to “0” if they have a year of “2020”.

As I said, this workflow answers the specific question, and may not be sufficiently generic for your needs but it returns the “desired output” that was posted, and the ideas may be adaptable to your further use case.

KNIME_new column based on row logic.knwf (21.2 KB)