accumarray does not return the correct sum

1 次查看（过去 30 天）
aggelos
aggelos 2019-2-13
评论： aggelos 2019-2-13
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.
  5 个评论
显示 3更早的评论
Walter Roberson
Walter Roberson 2019-2-13
mask = YourTable{:,6}<0;
accumarray(rows(mask), YourTable{mask,6}, [max(rows),1],@sum)
aggelos
aggelos 2019-2-13
Hi Walter,
It works like a charm using the mask. Thank you. I don't see an option to accept your answer thought?

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Stephen23
Stephen23 2019-2-13
编辑：Stephen23 2019-2-13
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

类别

在 Help Center 和 File Exchange 中查找有关 Data Preprocessing 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by