Sum elements in a column where there are matching values in an adjacent column?

Question

Tim Baker 2015-12-31

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/262293-sum-elements-in-a-column-where-there-are-matching-values-in-an-adjacent-column

编辑： Tim Baker 2016-1-6

I have a numeric matrix A = [1 1 100; 1 2 200; 1 3 50; 2 1 100; 2 2 200; 2 3 50; 3 1 100; 3 2 200; 3 3 50; 3 4 20]

I am trying to obtain a vector of values of equal dimension e.g. ans = [0; 0; 0; 100; 200; 50; 200; 400; 100; 0]

My aim is to sum & average prior elements in the 3rd column where the corresponding element in the 2nd column value matches, not including the row each vector element corresponds to. I hope I have made this question clear, but please don't hesitate to ask for clarification if not. I have lots of rows to process hence a time efficient solution would be greatly appreciated.

Note: I have tried my best to find a solution from scanning through the previous Q & As but haven't had any luck so far.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Sean de Wolski 2015-12-31

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/262293-sum-elements-in-a-column-where-there-are-matching-values-in-an-adjacent-column#answer_204761

编辑：Sean de Wolski 2016-1-5

在 MATLAB Online 中打开

Here's one way, there are probably better ones, but this works:

NEW

A = [1 1 100; 1 2 200; 1 3 50; 2 1 100; 2 2 200; 2 3 50; 3 1 100; 3 2 200; 3 3 50; 3 4 20];
[~,~,idx] = unique(A(:,2)); % Unique second column indices
A3 = A(:,3); % third column values
% Build array with index and cumulative value
idxdv = accumarray(idx,(1:numel(idx))',[],@(x){[sort(x) cumsum(A3(sort(x)))]});
idxdv = cell2mat(cellfun(@(x)[x(:,1), [0;x(1:end-1,2)]],idxdv,'UniformOutput',false))
% Sort it by index and extract second column
dv = sortrows(idxdv);
results = dv(:,2)

OLD, for reference in comments

A = [1 1 100; 1 2 200; 1 3 50; 2 1 100; 2 2 200; 2 3 50; 3 1 100; 3 2 200; 3 3 50; 3 4 20];
[~,~,idx] = unique(A(:,2)); % Unique second column indices
A3 = A(:,3); % third column values
% Build array with index and cumulative value
idxdv = cell2mat(accumarray(idx,(1:numel(idx))',[],@(x){[sort(x) cumsum([0;A3(sort(x(1:end-1)))])]}));
% Sort it by index and extract second column
dv = sortrows(idxdv);
results = dv(:,2)

5 个评论
显示 3更早的评论隐藏 3更早的评论

Sean de Wolski 2016-1-5

It's a bug in my logic, I'm sorting n-1 elements of x where x is unordered where I should be keeping n-1 elements after x is sorted.

This should be fixed by breaking the anonymous function into two steps. See NEW above.

Tim Baker 2016-1-6

编辑：Tim Baker 2016-1-6

Thank you so much for sharing your wisdom on this challenge and helping me get to the bottom of it, tis deeply appreciated and I have learnt a lot.

请先登录，再进行评论。

Answer 2

Guillaume 2016-1-5

2
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/262293-sum-elements-in-a-column-where-there-are-matching-values-in-an-adjacent-column#answer_205100

编辑：Guillaume 2016-1-5

在 MATLAB Online 中打开

Since accumarray appears to be doing some weird reordering, I'm not sure it can be use to do what you want. No matter, a loop always works. It's probably also easier to understand:

B = [1 1 20; 1 2 40; 1 3 60; 1 4 80; 2 1 30; 2 2 50; 2 3 50; 2 4 90; 3 1 40; 3 2 60; 3 3 80]
out = zeros(size(B, 1), 1);
for v = unique(B(:,2))'
   rows = B(:, 2) == v;
   sums = cumsum([0; B(rows, 3)]);
   out(rows) = sums(1:end-1);
end