accumarray does not return the correct sum

Hello all,

I have a table dataset like below

table=

Date ID x y z Profit

'01-Jan-2019' 157350 0 1 100 0.470000000000000

'01-Jan-2019' 38715 0 1 100 1.05000000000000

'01-Jan-2019' 157350 0 1 100 0.540000000000000

'01-Jan-2019' 157350 0 1 100 0.320000000000000

'01-Jan-2019' 81074 1 1 100 2.28000000000000

'01-Jan-2019' 81074 1 1 100 1.66000000000000

'01-Jan-2019' 141788 0 1 100 0

'01-Jan-2019' 19105 0 1 300 -0.230000000000000

'01-Jan-2019' 19105 0 1 300 0.130000000000000

'01-Jan-2019' 5492 1 1 1500 7.51000000000000

'01-Jan-2019' 36435 0 1 100 0.110000000000000

'01-Jan-2019' 130445 1 1 100 -79.6700000000000

'01-Jan-2019' 130445 1 1 100 -78.4800000000000

'01-Jan-2019' 130445 1 1 100 -79.4300000000000

'01-Jan-2019' 154312 0 1 100 -2.39000000000000

'01-Jan-2019' 130445 1 1 100 -79.9200000000000

'01-Jan-2019' 130445 1 1 100 -80.7500000000000

'01-Jan-2019' 130445 1 1 100 -80.3100000000000

'01-Jan-2019' 130445 1 1 100 -80.0600000000000

'01-Jan-2019' 81074 1 1 100 1.62000000000000

I use

[ids, ~, rows] = unique(table(:, 2)); to get unique IDs and subscripts.

then I want to calculate the sum of positive profit and the sum of negative profit for each ID like:

myGrossprofit=accumarray(rows,table2array(table(:, 2)>0, @sum);

myGrossLoss=accumarray(rows, table2array(table(:, 2)<0, @sum);

But my results is not correct. It even shows an integer when Profit is decimal and my GrossLoss is a positive number.

Thanks in advance for any help.

[madhan ravi]

Please write your desired output explicitly.

[Walter Roberson]

table(:,2) is a table object and cannot be compared to 0. If you use {} to extract the content then the result of the comparison would be logical and could not be passed to table2array.

Column 2 of your table is your id and none of your id are negative .

Naming a variable table is going to cause problems with using the table() function .

[aggelos]

Hi Walter and thank you for checking.

I have a typo here:

myGrossprofit=accumarray(rows,table2array(table(:, 2)>0, @sum);

should be myGrossprofit=accumarray(rows,table2array(table(:, 6)>0, @sum);

What I want is for ID 157350 for example, to sum all positive table(:, 6) values and all negative table(:, 6) values.

Thanks.

[Walter Roberson]

mask = YourTable{:,6}<0;

accumarray(rows(mask), YourTable{mask,6}, [max(rows),1],@sum)

[aggelos]

Hi Walter,

It works like a charm using the mask. Thank you. I don't see an option to accept your answer thought?

Answer 1

[Stephen23]

Your data:

hdr = {'Date','ID','x','y','z','Profit'};
arr = {...
'01-Jan-2019'	157350	0	1	100	0.470000000000000
'01-Jan-2019'	38715	0	1	100	1.05000000000000
'01-Jan-2019'	157350	0	1	100	0.540000000000000
'01-Jan-2019'	157350	0	1	100	0.320000000000000
'01-Jan-2019'	81074	1	1	100	2.28000000000000
'01-Jan-2019'	81074	1	1	100	1.66000000000000
'01-Jan-2019'	141788	0	1	100	0
'01-Jan-2019'	19105	0	1	300	-0.230000000000000
'01-Jan-2019'	19105	0	1	300	0.130000000000000
'01-Jan-2019'	5492	1	1	1500	7.51000000000000
'01-Jan-2019'	36435	0	1	100	0.110000000000000
'01-Jan-2019'	130445	1	1	100	-79.6700000000000
'01-Jan-2019'	130445	1	1	100	-78.4800000000000
'01-Jan-2019'	130445	1	1	100	-79.4300000000000
'01-Jan-2019'	154312	0	1	100	-2.39000000000000
'01-Jan-2019'	130445	1	1	100	-79.9200000000000
'01-Jan-2019'	130445	1	1	100	-80.7500000000000
'01-Jan-2019'	130445	1	1	100	-80.3100000000000
'01-Jan-2019'	130445	1	1	100	-80.0600000000000
'01-Jan-2019'	81074	1	1	100	1.62000000000000
};
tbl = cell2table(arr,'VariableNames',hdr);

Code:

>> [G,ID] = findgroups(tbl.ID);
>> fun = @(v)[sum(v(v>0)),sum(v(v<0))];
>> Y = splitapply(fun,tbl.Profit,G);
>> [ID,Y]
ans =
                      5492                      7.51                         0
                     19105                      0.13                     -0.23
                     36435                      0.11                         0
                     38715                      1.05                         0
                     81074                      5.56                         0
                    130445                         0                   -558.62
                    141788                         0                         0
                    154312                         0                     -2.39
                    157350                      1.33                         0