Transform single column to multiple columns - with an arbitrary number of values per column

[PaulPman]

Hello!
I`m rather new to KNIME and have trouble doing the transformation I am looking for.
So my input is a table displaying the results from image analysis. For each image I analyse the area of a certain number of particles, but the amount of particles per image varies strongly.
I receive this data in the form of a table, in which column 1 displays the number of a particle, column 2 displaying the area of that particle and column 3 displaying the file name of the corresponding image.
Now, what I would like to do is to transform this table so that each column corresponds to one of my images and includes the area values of all particles in that image.

I found a post with a similar problem (Transform single column to multiple columns) - my problem is that I have this arbitrary number of particles and thus, values per image, preventing the “Chunk Loop Start” node to be useful.

An idea I had was to create a matrix using the “One to many” node, starting a loop to go through it row by row and then assigning the respective area values for each image, but I couldnt get it to work.
I also tried playing around with the “Column List Loop Start” node as a replacement for the “Chunk Loop Start” node, but did not really get anywhere, either.

If anybody has an idea what I could do, I would greatly appreciate any support

[HansS]

Hi @PaulPman

Instead of a ChunLoop you can use a GroupLoop. See this wf
transpose_single_column.knwf (27.1 KB)

The ColumnRename node uses the GroupIdentifier (flowvariable) to rename the column.

gr. Hans

[Thyme]

Hello @PaulPman and Welcome to the KNIME Community!

What you’re looking for is the Pivoting node. It does exactly the transformation you need (and it can also do aggregations during that process).

• Group columns: every individual value will be one row in the output (here it’s Particle Number)
• Pivot columns: every individual value will be one column in the output (here it’s image)
• Aggregation columns: How the cells should be aggregated. With your example there’s only one value per row and column so I used “First”, otherwise you might want to use something else (e.g. sum).
  
  transform single column into multiple columns.knwf (17.2 KB)

PS: Generally speaking, loops should be avoided if it’s somewhat easy to do so. Hans’ solution is fine if the performance is good though.

[PaulPman]

Wow, that was way easier than expected. Both solutions work perfectly, thank you guys soo much!!! @Thyme @HansS