Sum over rows in a matrix for specific numbers in the last column

I have a matrix of data that looks like (my origninal data set is a 1450x6 matrix, but I think this matrix illustrates the problem)

A=[0.45 0.11 0.00 -0.32 1;
-0.06 -0.18  0.62  0.20 1;
-0.20  0.50  0.80  0.10 2;
-0.50  0.20  0.10  0.60 2;
-0.30  0.10 -0.05  0.20 2;
-0.45  0.11  0.00 -0.32 3;
-0.60  0.20  0.10  0.40 3;
 0.80 -0.20 -0.20  0.50 4;
-0.30  0.10 -0.05  0.20 4;
 0.45 0.11 0.00 -0.32   1;
-0.06 -0.18  0.62  0.20 1]

In this matrix I need to sum all rows containing a 1 in the last column, then the rows containing a 2 in the last column and so on with preserving the order of the data. To be more explicit the first sum is supposed to be row 1+2, second sum is row (3+4+5), third sum is row (6+7), fourth sum is row (8+9), and then the fifth sum which starts again with ones is row (10+11).

How can this be solved in MATLAB?

Many thanks for your help and support!

Answer 1

[Jan]

An emulation of splitapply for older Matlab versions:

group  = cumsum(diff([0; A(:, end)]) ~= 0);
nRowR  = size(A, 2) - 1;
R      = zeros(group(end), nRowR);
for iG = 1:group(end)
  R(iG, :) = sum(A(group == iG, 1:nRowR));
end

Unfortunately I did not get a smart version with accumarray, because val can be a vector only. But at least:

group = cumsum(diff([0; A(:, end)]) ~= 0);
nRowR = size(A, 2) - 1;
R     = zeros(group(end), nRowR);
for iR = 1:nRowR
  R(:, iR) = accumarray(group, A(:, iR));  % Default fun: @sum
end

@All: Is there no way to let accumarray operate on a matrix? If so, could we rename it to accumvector?

I expect the first approach to be remarkably faster.

[JF]

Many thanks to all of you guys! This solves the problem! Learnt a lot today!

Answer 2

[Guillaume]

One simple way:

group = cumsum(diff([0; A(:, end)]) ~= 0);
result = splitapply(@sum, A(:, 1:end-1), group)

[Andrei Bobrov]

+1

[JF]

Thanks for that suggestion. However, I'm working on MATLAB version 2009a and do not have access to the function splitapply.

Answer 3

[Andrei Bobrov]

My ruble:

ii = cumsum(diff([0;A(:,end)])~=0);
[rs,cs] = ndgrid(ii,1:size(A,2)-1);
result = accumarray([rs(:),cs(:)],reshape(A(:,1:end-1),[],1))

[Jan]

+1: That's the way to run accumarray (which should be called "accumvector") on a matrix. But I like the simpler splitapply.

Answer 4

[Star Strider]

More information about column 5 would be helpful.

If column 5 goes from 1 to 4 and then repeats (regardless of the number of contiguous rows), this will work. If column 5 is random, this fails (and I will delete this Answer). It appends the value of column 5 to the sum for each section, in case you want that.

section = [0; find(diff(A(:,5))<0); size(A,1)];
for k1 = 1:numel(section)-1
    rowidx = section(k1)+1:section(k1+1);
    rows = [sum(A(rowidx,1:4),2) A(rowidx,5)];
    S{k1,:} = [accumarray(rows(:,2), rows(:,1)) unique(rows(:,2))];
end
Sm = cell2mat(S);                                                       % Recover Matrix From Cell Array

[JF]

Many thanks for coming up with a solution. Column 5 goes always from 1 to 4 only the number of 1,2,3 and 4s varies through the matrix. However, I need the summation over columns so that the first two rows in the Sm matrix are supposed to be (I'm sorry for the initial confusion)

Sm=[0.39, -0.07 0.62 -0.12 1; -1 0.8 0.85 0.9 2]

For my example the Sm matrix is supposed to be a 5x5 matrix in the end.

[Star Strider]

I wasn’t certain what you were summing over.

Oh, well...