Using groupby to split columns

[z_thiel]

Hi there,

I have just started using KNIME and I came upon the following problem in my workflow:

I would like to group certain replicates in a column and make individual columns out of these groups.

For example:

Compound / fold change
A / 1.1
A / 1.3
A / 1.2
B / 1.7
B / 1.8
C / 0.8
C / 0.7
C / 0.3

should become something like this:

A / B / C
1.1 / 1.7 / 0.8
1.3 / 1.8 / 0.7
1.2 / – / 0.3

I tried using groupby node, but I can only aggregate the values and not sort them into new columns. How would you approach this task?

Thank you in advance.

[Ergonomist]

Hi,

Please find a workable, loop-based flow attached below. If your tables aren’t too large this should perform OK.

Cheers,
E

Groups to cols.knwf (12.8 KB)

[ipazin]

Hi there @z_thiel,

welcome to KNIME Community!

You can try following as well. In GroupBy node choose List as Aggregation method. Then use Split Collection Column node to have values in separate columns. Using RowID node you can have you Compound names transferred to row ids and follow all that with Transpose node

Br,
Ivan

[z_thiel]

That’s it! Thank you very much!

@Ergonomist: Your worklflow was also helpful on a reduced dataset, but I have around 10 million entries in my final dataset, so it might be a bit too slow.

[Ergonomist]

Very elegant, thanks!

[ipazin]

Hi there,

glad it helped!

Br,
Ivan

[Aswin]

I may risk looking like a smartass here, but why not use the Pivoting / Unpivoting nodes? They are the most underused nodes in the whole repository I think…

KNIME_project.knwf (8.1 KB)

[ipazin]

Nice one @Aswin!
I think people don’t understand how to use it properly and get confused by configuration and 3 output ports after that. (Me included as I always have to play with it a bit before I use it )
Br,
Ivan

[Aswin]

@ipazin I only ever use the first output

[ipazin]

@Aswin so ignoring other output ports does the trick