Rows to columns restructuring

KNIME Analytics Platform
1

Hi,

I am trying to restructure my table data as follows:

- the "category" column values  will be appended as new columns to existing table, and the values of the new columns will be taken from the intersection between "category" and "value".

original table:

row#    category    value
1       diecut      79.70
2       flatcut     88.06
3       laser       69.80
4       laser       4.17
5       flatcut     5.13
6       laser       38.93
7       diecut      6.11
8       laser       32.0            
9       flatcut     27.0            
10      diecut      21.0

restructured table:

row#    category    value     diecut  flatcut laser
1       diecut      79.70     79.70   null    null
2       flatcut     88.06     null    88.06   null
3       laser       69.80     null    null    69.80
4       laser       4.17      null    null    4.17
5       flatcut     5.13      null    5.13    null
6       laser       38.93     null    null    38.93
7       diecut      6.11      6.11    null    null
8       laser       32.0      null    null    32.0
9       flatcut     27.0      null    27.0    null
10      diecut      21.0      21.0    null    null

Can someone guide about how I can achieve that in Knime?

Thank you!

 

 

 

 

2

I got something going using the following:

table data -> "One to Many" -> "Rule Engine" -> "Rule Engine" -> "Rule Engine"

Note: the "rule engines" are used to replace the "1" values for the new columns with the actual value from the original row/column.

However, this is done manualy: I added one "Rule Engine" for each category value. If another category value will show up, I have to add another "Rule Engine" manualy. Is there a way to accomplish that automaticaly?

Ex.

"groupby" -> "loop start" -> "rule engine" -> "loop end"

where:
                groupby - selects all row distinct values to be added as new columns
                rule engine - to replace the "1" of the new column with the actual value

I am new to Knime and I don't know how to work with loops. Any help will be highly appreciated.

 

Thank you!

3

No need for loop or groupby.

Just use Missing Value Node to replace "?" by "0". Define it using the variable type, that way it does not depend on any variable or category names.

Depending on whether you still need the category and value variables, a Pivot node would have done instead of the One To Many. I don't remember whether Pivot allows to handle missing values automatically or not. Another solution to explore.

4

Excellent! Thank you, Geo. I got it to work with a pivot table per your suggestion. I had to create some additional columns to make the groups unique, but it worked out in the end.