t_kahi’s blog


【KNIME】Transpose rows to multiple columns by category

Hi, I'm @PK.
I tried to transpose rows to multiple columns by category.
The example below showed the transpose of sample data.

To solve this issue , I made KNIME workflow below.

First, I read sample data which contains two columns, "compound" & "value".
"compound" column was used as category.


Next, I used 'Group Loop Start' node to start loop by category.
Group Loop Start — NodePit

This figure showed that "compound = A " was selected in first iteration.

Then I used 'Column Rename (regex)' node to change column name as "compound name" .
Column Rename (Regex) — NodePit
Flow variables were used in this node to obtain the compound name.
After execute the node, column name were converted to "A"

Removed "compound" column by 'Column Filter' and 'Row ID' node was used to make the same row ID in each loop. f:id:t_kahi:20190622192632p:plain

Finally , stop the loop by 'Loop End (Column Append)' node. The results below showed that the rows were transposed to multiple columns by each category! f:id:t_kahi:20190622193032p:plain


I received a comment from tymsk3891 after posting.

ループの中で Column Rename (regex) を使うといろいろなことができそうですね。あまり難しくない処理であれば Pivoting ノード(compound でピボットして value を Aggregation 'List') → Ungroup ノードでも似たようなことができました。

Thanks for the useful advice!! I took in a roundabout way...


Using'Pivoting' node, the table changed below. f:id:t_kahi:20190624211631p:plain

Then I used 'ungroup node to unlist each columns.

The rows were transposed to multiple columns by each category easily.
I learned a lot. Thanks!