Bracket remove with Column Rename (regex) Node

[sabsab]

Hello all,

Trying to use Column Rename (regex) to remove the attachment of a column name e.g. “(Iter #123)”.
Did with (Iter #…). removed almost everything, only the first bracket always remains with the column name.
How do I get rid of the first bracket as well?

Many greetings!

[Matt_D]

@sabsab

(Iter #123)

Hi @sabsab,

I would go down the route of creating 3 groups and then using your second group as the replacement

I used (\(Iter\s\#)(\d+)(\)) but in theory (\(Iter\s\#)(.*)(\)) would pick up anything between (Iter # AND )

Hope this helps

Matt

[sabsab]

@Matt_D

((Iter\s#)(.*)())

Hi Matt,
thanks, for the quick reply. Unfortunately, it does not work for me.

Group solution:

Genrell solution:

I must confess, I’m still using version 4.1.4 - waiting for update by admin Hope, thats not the problem.

Many greetings!

[Matt_D]

Hi @sabsab,

You need to add $2 in the Replacement field for the solution to work.

BUT given what you’ve just posted I’m not sure I quite understood your request. Can you give the above a go and if a different solution is required we can work through it from there.

Thanks,

Matt

[sabsab]

@sabsab

Column Rename (regex)

Hi Matt,

The problem is that there are duplicate column labels and in the process e.g. (Iter #123) is appended to the duplicate column. Example: Column 1: Ecosystem; Column 2: Ecosystem (Iter #123). This is ok, because the database cannot work with duplicates.
But I want to export the data to Excel and remove “(Iter #123)” with Column Rename (regex) before.

I hope now a little more understandable.

Greetings
Bernd

PS: I had done that with $2, result: in picture 1 with yellow background

[Matt_D]

Understaood but you’re still in the same boat with KNMIE, you cannot have duplicate column names. So if you had

Test
Test (Iter #123)

If we try and remove (Iter #123) it will fail as you cannot have 2 columns called Test.

The change in theory is:

(.*)(\s\(Iter\s\#\d+\)) replace with $1

Essentially taking anything literal of (Iter # along with any number of digits followed by a literal ) and replacing this with the string beforehand, the 1st group.

I think your solution here is to unpivot the columns which need renaming, rename those values now in a row and then pivot it back to a table essentially merging the renamed rows back into a column but this doesn’t feel like it’s going to work unless the values are uniquely in only 1 of those columns. We could also look at merging those columns Column Merger – KNIME Hub

Does this help?

Do you have a sample of the dataset we could have a look at?

Matt

[sabsab]

Makes sense!
I will have * replaced inside Knime (Iter #123) and remove the * after export to Excel.

Thanks for the insight though!

Greetings
Bernd