Consolidating row values from previous column duplicate values.

Hi all,

Is it possible to write a script to make MATLAB look at the first two columns of a matrix, and if there are multiple rows where Column 1 and Column 2 have the same values, to put these rows together and sum the corresponding values of a third column.

An example would be like this;

[a  x  2;
a  x  3;
b  x  4;
a  y  5;
b  y  6;
b  y  7;
c  y  8]

and so the script will recognize there are two rows with a in column 1 and x in column 2, so it will put them together and add the values in the third row.

So the output would be like this;

[a  x  5;
b  x  4;
a  y  5;
b  y  13;
c  y  8]

Forgive my bad formatting of the matrix, but any help will be greatly appreciated!

Kind regards

Willem

[Evan]

Do you only add rows that are adjacent to one another, or would another row, say [a x 6] appended to the bottom of the matrix, also be added to the top two?

[Willem]

No, I have the ability to sort the data and make sure they will be sorted by the first two columns, with the primary sort being on the second column.

Answer 1

[the cyclist]

You need the accumarray function:

A = [1 1 3;
     1 1 4;
     1 1 7;
     1 2 1;
     1 2 6;
     2 3 5;
     3 6 4; 
     3 6 9;
     3 6 3];

[Aunique,i,j] = unique(A(:,[1 2]),'rows');

Asum = accumarray(j,A(:,3),[],@sum);

output = [Aunique,Asum]

[Willem]

Thank you very much for your help!

[Willem]

Would it be possible to ask for an additional tweak to the code, as I looked at the MATLAB help for accumarray but I was unable to resolve this.

How would I modify the code so that the second line of,

Asum = accumarray(j,A(:,3),[],@sum);

Gives me the sum for n columns?

In my case I have 192 additional columns (not just the third column anymore) and I would like to have the 192 columns giving me the sum for all the unique combinations from column 1 and 2.

Thank you very much again for your help!

[the cyclist]

Here is an example where I expand A to have a few extra columns, but wrote it in a way that should generalize:

A = [1 1 3;
     1 1 4;
     1 1 7;
     1 2 1;
     1 2 6;
     2 3 5;
     3 6 4; 
     3 6 9;
     3 6 3];

A(:,4) = 2*A(:,3);
A(:,5) = 3*A(:,3);
A(:,6) = 5*A(:,3)

[Aunique,i,j] = unique(A(:,[1 2]),'rows')

dataMatrix = A(:,3:end);
dataVector = reshape(dataMatrix,[],1);

[NR,NC] = size(dataMatrix);

rowIndex    = repmat(j,[NC 1]);
columnIndex = reshape(repmat(1:NC,[NR 1]),[],1);

B = accumarray([rowIndex columnIndex],dataVector,[],@sum)

The idea here is that you have to reshape the 192-columns in to one long vector for the purposes of accumarray. Then, you accumulate them back into their columns again (using the rowIndex and columnIndex to keep track).

[Willem]

Thank you very much again, this is perfectly what I need. I will study and try to understand this code.

[the cyclist]

Don't feel bad if accumarray seems confusing. It's doing a pretty complex thing, and I personally never found the documentation for that particular function to be helpful to me.

Answer 2

[Azzi Abdelmalek]

[a,b,c]=unique(A(:,1:2),'rows','stable')
out=[a accumarray(c,A(:,3))]

[Willem]

Thank you very much for your help also!

Answer 3

[Andrei Bobrov]

A = [1 1 3;
     1 1 4;
     1 1 7;
     1 2 1;
     1 2 6;
     2 3 5;
     3 6 4;
     3 6 9;
     3 6 3]
A(:,end + (1:3)) = randi(10,size(A,1),3);

[a1,ii,ii] = unique(A(:,1:2),'rows');
[i1 j1] = ndgrid(ii,1:size(A,2) - 2);
a2 = A(:,3:end);
out = [a1 accumarray([i1(:),j1(:)],a2(:))];