Finding and counting of identical rows in a matrix

hello together,

I have to find the identical rows or identical lines in a matrix and sum the time values of these rows in the first column. At the end I have to create a new matrix and write only one of the identical rows and delete the rest identical rows. And I have to keep not identical rows for example

 Matrix:
 1 300 3500 500 6000
 3 200 3000 500 6500
 5 150 2500 450 6000
 8 400 2000 550 5500
 5 200 3000 500 6500
 9 150 2500 450 6000
 2 200 3000 500 6500
 ...

2nd, 5th and 7th rows are identical except 1st column . Then I have to add the corresponding values in the first column or 3 + 5 + 2 = 10. as well as 3rd and 6th, so 5 + 9 = 14

I have to write one of the identical rows and not identical rows. The identical lines occur more than 2 times. Matrix consists of many rows eg. 1000. The new matrix looks like this:

 1 300 3500 500 6000
 10 200 3000 500 6500
 14 150 2500 450 6000
 8 400 2000 550 5500

how can I realize this in Octave? Would anyone have an idea?

Thank you very much

Answer 1

[Andrei Bobrov]

For Octave?

M = [ 1 300 3500 500 6000
 3 200 3000 500 6500
 5 150 2500 450 6000
 8 400 2000 550 5500
 5 200 3000 500 6500
 9 150 2500 450 6000
 2 200 3000 500 6500];

[a,b,c] = unique(M(:,2:end),'rows','first');
[~,i] = sort(b);
[~,ii] = sort(i);
out = [accumarray(ii(c),M(:,1)), a(i,:)];

Here use Octave 5.1.0

[Aytug Bas]

Hi, thank you. it works but if I run this matrix below I get an error.

Mode =

      1    100   1500      0   1750
      1    100   1500      0   1750
      1    100   1500      0   1750
      1    100   1500      0   1750
      1    100   1750    -50   2250
      1    100   1750    -50   2250
      1    100   1750    -50   2250
      1    100   1750    -50   2250
      1    100   1500      0   2000
      1    100   1500      0   2000
      1    100   1500      0   2000
      1    100   1500      0   2000
      
      
  [a,b,c] = unique(Mode(:,2:end),'rows','first');
  [~,i] = sort(b);
  out = [accumarray(b(c),Mode(:,1)), a(i,:)];
  
  error: horizontal dimensions mismatch (9x1 vs 3x4)
  

M = [ 1 300 3500 500 6000
 3 200 3000 500 6500
 5 150 2500 450 6000
 8 400 2000 550 5500
 5 200 3000 500 6500
 9 150 2500 450 6000
 2 200 3000 500 6500];
[a,b,c] = unique(M(:,2:end),'rows','first');
[~,i] = sort(b);
out = [accumarray(b(c),M(:,1)), a(i,:)]; 

[Bruno Luong]

[a,~,c] = unique(M(:,2:end),'rows','stable');
out = [accumarray(c,M(:,1)), a]

[Aytug Bas]

I get this error

[a,~,c] = unique(M(:,2:end),'rows','stable')
out = [accumarray(c,M(:,1)), a]


error: unique: invalid option

[Bruno Luong]

'stable' is MATLAB option on recent release.If you don't have access

[a,b,c] = unique(M(:,2:end),'rows');
[~,i] = sort(b);
out = [accumarray(i(c),M(:,1)), a(i,:)]

[Andrei Bobrov]

Thank you Bruno! I'm fix.

[Andrei Bobrov]

Mode = [...
1    100   1500      0   1750
1    100   1500      0   1750
1    100   1500      0   1750
1    100   1500      0   1750
1    100   1500      0   1750
1    100   1500      0   1750
1    100   1750    -50   2250
1    100   1750    -50   2250
1    100   1750    -50   2250
1    100   1750    -50   2250
1    100   1750    -50   2250
1    100   1500      0   2000
1    100   1500      0   2000
1    100   1500      0   2000
1    100   1500      0   2000
1    100   1500     -1   500
1    100   1500     -1   500
1    100   1500     -1   500      ];


[a,b,c] = unique(Mode(:,2:end),'rows','first');
[~,i] = sort(b);
out = [accumarray(i(c),Mode(:,1)), a(i,:)]

out =

      5    100   1500      0   1750
      3    100   1750    -50   2250
      4    100   1500      0   2000
      6    100   1500     -1    500
      
[a,b,c] = unique(Mode(:,2:end),'rows','first');
[~,i] = sort(b);
[~,ii] = sort(i);
out = [accumarray(ii(c),Mode(:,1)), a(i,:)]

out =

      6    100   1500      0   1750
      5    100   1750    -50   2250
      4    100   1500      0   2000
      3    100   1500     -1    500

[Aytug Bas]

Thank you very much Bobrov.

Answer 2

[Walter Roberson]

M = [ 1 300 3500 500 6000
 3 200 3000 500 6500
 5 150 2500 450 6000
 8 400 2000 550 5500
 5 200 3000 500 6500
 9 150 2500 450 6000
 2 200 3000 500 6500];


[G,U1,U2,U3,U4] = findgroups(M(:,2),M(:,3),M(:,4),M(:,5));
newM = [splitapply(@sum, M(:,1), G), U1, U2, U3, U4];