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

7 次查看（过去 30 天）
Tim Baker
Tim Baker 2015-12-31
编辑： 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.

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

采纳的回答

Sean de Wolski
Sean de Wolski 2015-12-31
编辑：Sean de Wolski 2016-1-5
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更早的评论
Sean de Wolski
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
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.

请先登录，再进行评论。

更多回答（1 个）

Guillaume
Guillaume 2016-1-5
编辑：Guillaume 2016-1-5
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

类别

在 Help Center 和 File Exchange 中查找有关 Matrices and Arrays 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by