Aggregate and remove attributes

Hi,

I have a data set containing 2000 columns or attributes having values as real number. What i want to do is aggregate (sum) three attributes and create a new attribute with the summing result and remove the last two leaving first attribute and the resulting attribute. I can do that by using operators column aggregator and filter column. But i want to impose this procedure to the whole 2000 attributes, that means three attributes will be summed leaving two attributes (including resulting one) and then next three will be summed leaving two attributes and so on. Is there any procedure that i can impose that column aggregator and filter column to the whole data set automatically or is there any other procedure to do it? please let me know if you need more specifications.

Jony

Is this a pure numerical data set?

and if your input columns are 1,2,3,4,5,.....,10

your output would be two columns? (1+2+3) and (6+7+8) 

Hi,

Yes it is a pure numerical data set.

if the input columns are 1,2,3...10, i want the output as 1,(1+2+3),4,(4+5+6)..i want to keep the first columns of each agrregation and the aggregated result columns, removing the later two.

if its not possible then output as (1+2+3) and (4+5+6) will also do.

Hi Jony,

I made you a workflow which can do the job.

The basic idea is, to work in a row vice fashion by transposing the table before hand.

Hope it helps, however, I do not it's a little bit complicated.

Iris

Hi, 

Which version of KNIME have you done with it. The thing is i can open the workflow but can not run it, it shows some errors like 

ERROR LoadWorkflowRunnable Errors during load: Status: DataLoadError: aggregateandremove 2 loaded with error during data load
ERROR LoadWorkflowRunnable   Status: DataLoadError: aggregateandremove 2
ERROR LoadWorkflowRunnable     Status: Error: Node "Column Appender" not available (provided by "KNIME GmbH, Konstanz, Germany"; plugin "org.knime.base" is installed)
ERROR LoadWorkflowRunnable     Status: DataLoadError: Data Generator 2:1
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Data Generator": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Data Generator (#1)\port_1\data.xml
ERROR LoadWorkflowRunnable       Status: DataLoadError: State has changed from EXECUTED to CONFIGURED
ERROR LoadWorkflowRunnable     Status: DataLoadError: Column Filter 2:4
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Column Filter": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Column Filter (#4)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: Transpose 2:3
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Transpose": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Transpose (#3)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: Chunk Loop Start 2:2
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Chunk Loop Start": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Chunk Loop Start (#2)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: Row Filter 2:7
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Row Filter": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Row Filter (#7)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: RowID 2:16
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "RowID": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\RowID (#16)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: RowID 2:10
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "RowID": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\RowID (#10)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: Row Filter 2:21
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Row Filter": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Row Filter (#21)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: GroupBy 2:12
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "GroupBy": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\GroupBy (#12)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: Transpose 2:13
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Transpose": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Transpose (#13)\port_1\data.xml
ERROR LoadWorkflowRunnable       Status: Warning: Loading model settings failed: Config for key "model" not found.
ERROR LoadWorkflowRunnable     Status: DataLoadError: Transpose 2:22
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Transpose": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Transpose (#22)\port_1\data.xml
ERROR LoadWorkflowRunnable       Status: Warning: Loading model settings failed: Config for key "model" not found.
ERROR LoadWorkflowRunnable     Status: DataLoadError: TableRow To Variable 2:17
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "TableRow To Variable": Can't read spec file C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\TableRow To Variable (#17)\port_1\spec\spec.zip
ERROR LoadWorkflowRunnable       Status: Warning: Loading model settings failed: Config for key "model" not found.
ERROR LoadWorkflowRunnable     Status: DataLoadError: Column Aggregator 2:14
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Column Aggregator": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Column Aggregator (#14)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: Column Rename 2:18
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Column Rename": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Column Rename (#18)\port_1\data.xml
ERROR LoadWorkflowRunnable     Status: DataLoadError: MISSING Column Appender 2:20
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "MISSING Column Appender": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Column Appender (#20)\port_1\data.xml
ERROR LoadWorkflowRunnable       Status: Warning: Loading model settings failed: Config for key "model" not found.
ERROR LoadWorkflowRunnable     Status: DataLoadError: Loop End (Column Append) 2:19
ERROR LoadWorkflowRunnable       Status: DataLoadError: Unable to load port content for node "Loop End (Column Append)": No such data file: C:\Users\jonyr1\Downloads\knime_2.7.4\workspace\aggregateandremove\Loop End _Column Append_ (#19)\port_1\data.xml
ERROR LoadWorkflowRunnable       Status: Warning: Loading model settings failed: Config for key "model" not found.
ERROR LoadWorkflowRunnable     Status: DataLoadError: State has changed from EXECUTED to IDLE
 
seems like some of the features are not available in my version, btw i am using 2.7.4 version, and when i was opening the file it showed that 'it is from another version do you still want to open' this kind of message, can you please help regarding this issue?
 
Jony

Hi Jony,

 

I'm using the current version 2.8.0.

You can make an update under Help -> Check for Updates.

Iris

hi,

Its working good now..

in the output i am getting all the rows but in the input i mean after data generator i am setting a node interactive table and it does not show all rows, only four rows naming cluster 0,1, 2, 2..why so? what if i want to see the whole input data set?

 

Jony

Hi, this is the second outport of the Data Generator which only provides you with the centers of the generated clusters.

 

btw. you don't need a interactive table to see the data. You can just rightclick the node and open the data views at the bottom of the list.

i got it now, thak you very much...appreciated!

Jony

sorry forgot to ask one thing, i did not understand the connection between node table row variable and column rename, can you plz explain that a bit?

This is something which is only for beauty reasons ;).

So the output column of the column aggregator  is called "Sum", independently from the input columns.

Because of that I wanted to rename the column to something meaningful. In this case to col1,col2,col3, this is created by groupBy(Node 12). Than I make a variable out of it with the TableRow To Variable node. With variables (flow variables) you can change the configuration of a node. Therefore in the Column Rename Configuration go to the second Tab ("Flow Variables") and here I set the name of the column Sum to the value of the ColName Variable.

FlowVariables are very useful, but are one of the more difficult concepts in KNIME.