Cells splitter for multiple columns

Hi everyone,

I need your help about data cleaning in a excel file.

In fact, I have multiples indicators per country, but in these indicators there are the evolution compare to the previous year.
So i would like to separate this evolution in a new column.

The evolution is in parenthis, So I was thinkhing about using the Cell splitter Node and specify the parenthis as delimiter.

But i can’t manage to do for all columns.
I tried to use the Column list loop Star or the Table Row to Variable Loop Start nodes but it’s not working.

So, If you can help me do to this, it will be very nice.

Here, the example of dataset.

Country Population - Million GDP - Billion Inflation %
Germany 83 (1,2) 3997 (2,2) 12 (0,1)
UK 67 (0,7) 2895 (0,8) 10 (2,2)
USA 331 (1,8) 20494 ( 1,5) 20 (3,2)
Italy 60 (-0,2) 2527 ( 0,6) 15 (2,8)
Spain 46 (0,3) 1426 (1,2) 5 (-0,8)
Brazil 212 (1,1) 1869 (2,5) 18 (2,5)

And the result should be like this :

Country Population - Million Evolution Population GDP - Billion Evolution GDP Inflation % Evolution Inflation
Germany 83 1,2 3997 2,2 12 0,1
UK 67 0,7 2895 0,8 10 2,2
USA 331 1,8 20494 1,5 20 3,2
Italy 60 -0,2 2527 0,6 15 2,8
Spain 46 0,3 1426 1,2 5 -0,8
Brazil 212 1,1 1869 2,5 18 2,5

Thank you.

Hi @Foxyellow

See this topic from KNIME Hub .
It works KNIME_project.knwf (24.8 KB)

gr. Hans

2 Likes

Hello @HansS,

I tried your solution and it’s working percetly.

Thank you so much. So If I want to do a manipulation on multiple columns. I always have to use the pivoting node to get a single column and then switch back with the pivoting node. Is that right ?

There will just be a problem with the name of the columns because I need to specify a pivot name method.

1 Like

Hi @Foxyellow

I added a second approach to the workflow KNIME_project_2.knwf (58.4 KB)


gr. Hans

Hello @Foxyellow,

In some use cases that is best approach. But as shown by @HansS you can also use loop or nodes doing operations on multiple columns - String Manipulation (Multi Column), Math Formula (Multi Column), Column Expressions

Br,
Ivan

1 Like

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