Taking the sum of all the rows for 1 ID

KNIME Analytics Platform
1

Hi all,

I need to take the sum of an X ammount of rows where an unique ID is the same as the previous line. So for example:

 

ID Col2 Col 3 Amount09 Amount10
1 x x 10 25
1 x x 10 10
1 x x 10 10
2 x x 10 25
2 x x 10 30
3 x x 10 20
3 xx x 10 100
3 x x 10 20
3 x xx 10 10

So now I want a row like this:

ID Col 2 Col 3 Amount09 Amount10
1 x x 30 45
2 x x 20 55
3 x x 40 150

I already have the table grouped by the unique ID.

 

But how can I add up those values with the restriction of being the same ID?
 

 

Thanks!

2

Hi

 

I am not sure if I understood it correctly. Do you want to sum up the values in columns "Amount09" and "Amount10" based on the column "ID"? Then the attached workflow will help you with that.

Not if not, please describe your problem in more detail.

Frank

 

3

Hi Frank,

Thanks for your response. I need a "Total 09 ammount" and a "Total 10 ammount" for every unique ID in the "ID column". So the "Total 09 ammount" for ID 1 =  10+10+10. And the total 10 amount for ID  1 = 25+10+10.

For ID 2 Total 09 ammount should be = 10+10. Total 10 ammount for id 2 should be = 25+30.

 

Is this more clear?

Thanks in advance!

 

 

4

Hi

The upper table is your input table and the lower table is your expected output tables as far as I understand, isn't it? My attached workflow generates the expected output table, or?

 

Frank

 

 

5

Hi Frank,

 

That is correct. The first one is an simplified version of what I have. The real file contains >40.000 rows and more than 30 columns. So the table creator can't be used.

 

Thanks!

6

Hi Marc,

You probably have to import your data with another node (see IO/Read nodes) - however, that was not your question, was it ?

Frank's solution does exactly what you request, i.e. a groupby node to sum the two variables by ID.