Column expressions dual or multiple functions same line

[jarviscampbell]

Hello there,

I have a column with values like, ‘blue’, ‘red’, ‘green’. I would like to change those values of the entire columns at once. If blue then change it to azul, if red then change it to vermelho, if green, then change it to verde, etc. Using the module ‘Column Expressions’ I’ve used the following expression:

replace(column(“color”),“blue”,“azul”,or, (column(“color”),“red”,“vermelho”),or,(column(“color”),“green”,“verde”)

It doesn’t work. This is my first expression on Knime. Any ideas on what I am doing wrong? Thanks!

[elsamuel]

Welcome to the forum @jarviscampbell .

I’ve used the following expression:

replace(column(“color”),“blue”,“azul”,or, (column(“color”),“red”,“vermelho”),or, column(“color”),“green”,“verde”)

It doesn’t work. This is my first expression on Knime. Any ideas on what I am doing wrong? Thanks!

What you have is a single misformatted replace function with some misformatted or functions and other arguments thrown in.

If you read the function documentation, you’ll see that the replace function takes 3 or 4 arguments: replace(str, search, replace, [modifiers]). You’ve given way more that this, and your formatting is inconsistent

If you read the function documentation, you’ll see that the or function should be or(val1, val2, ...). Your expression does not conform to this, and in any case, the or function returns TRUE or FALSE which I don’t think is appropriate in your situation.

If you want to use the Column Expressions node, then a series of if statements would be the easiest to understand and implement. The expression you want should look more like this:

if (column("color") == "blue")
{"azul"}
else if (column("color") == "red")
{"vermelho"}
else if (column("color") == "green")
{"verde"}

[duristef]

hi @jarviscampbell
@elsamuel suggested a simple and clear expression. If you prefer to use Knime nodes you have (AFAIK) two ways
Let’s say your data table is like this one

• the Joiner node
  Create a table like this one
  
  and join it to your data table on the “colour” column

• the “Rule engine (Dictionary)” node.
  Create another table with the rules
  

and apply the rules to the data table

In this workflow you find these two solutions
KNIME_project7.knwf (13.2 KB)

[jarviscampbell]

@elsamuel thank you so much for your explanation. I appreciate it. I will play around w those Expressions and learn from it. Cheers.

[jarviscampbell]

Thank you @duristef for an alternate solution to this. I will also try that. Have a good one.

[bruno29a]

Hi @jarviscampbell , and welcome to the Knime Community.

Just be aware that the solutions provided by @elsamuel and @duristef are a bit different from what the replace() function would do, and they could well be actually the behaviour that you are looking for. But since you did not provide any sample data and your expected output for the sample data, it’s impossible to tell which behaviour you want.

By the way, an alternative to what they have provided would be to use the Rule Engine where you can add all your rules in that same node:

$color$ = "blue" => "azul"
$color$ = "red" => "vermelho"
$color$ = "green" => "verde"
TRUE => $color$

Pointing our the difference, and to keep it simple, let’s look at replacing “blue” with “azul”:
With the proposed solution, it will “replace” only if the value of the cell is just and only “blue”, while the replace() function would also do this replacement, but also would replace “blue” as part of a string, or as a substring of a word.

For example, this would be the expected output for these input data:

ID	Input data	Proposed solutions	Replace()
1	blue	azul	azul
2	blue sky	blue sky	azul sky
3	bluefins	bluefins	azulfins

As you can see, only the record with ID 1 would be modified with the Proposed solutions, while all the records containing “blue” would be modified with Replace()

Of course, there are ways to also control what we want to replace, especially if you use regex - for example, you may want to replace “blue” only if it’s a word on its own (ID 1 and 2). Only a form of replace() can allow you to do this.

So it all depends on what behaviour you want. If you want to change only if the cell’s value is exactly “blue” (or “red”, or “green”), then one of the proposed solutions will work. But if you want “blue” (or “red”, or “green”) to be replaced as you wanted when you used the replace() function, then you have to use some form of replace() (replace() or regexReplace(), etc).

There are a few ways to do the replace() properly. You can do nested replace(), you can even do replace with if statements in Column Expressions. But first, let us know what is the behaviour you are looking for.

[duristef]

@jarviscampbell The one indicated by @bruno29a is actually a more general case, but I find it hard to imagine why you would want to translate a single word inside a sentence. I must admit that the result could be amusing (e.g. “Little Vermelho Riding Hood”, which sounds like a song by Daniela Mercury). However, in that case the replacement should be done with a loop (example 1 of my workflow).
If, on the other hand, the problem is “extracting” a color from a sentence, example 2 could be a solution.

If you’re looking for monsters you can modify the regex pattern in example 1 and replace strings instead of words. You’ll get the “azulfin”, an anglo-brazilian species of tuna (and also in this case the mercury has to do with it)

KNIME_projectx.knwf (18.1 KB)

[jarviscampbell]

lol , indeed… Will keep that in mind as well. One would assume if not a word but a couple or few words within strings would be treated the same way followed or prepended by wildcards and on. Nevertheless, will keep your solution as a possibility as well, thanks a lot.