t_kahi’s blog

KNIMEやCellProfiler、創薬に関する記事と,日々のメモです

【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.
f:id:t_kahi:20190622210056p:plain

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

f:id:t_kahi:20190622190010p:plain
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.
f:id:t_kahi:20190622190705p:plain
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

2019/06/24

I received a comment from tymsk3891 after posting.

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

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

f:id:t_kahi:20190624211227p:plain

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

Then I used 'ungroup node to unlist each columns.
f:id:t_kahi:20190624212430p:plain

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