Put rows together in a matrix

I have constructed a matrix of various values and have sorted based on the values of the first column. As you can see, there are repeated values in the first column. What I would like to do is to "put the rows together" that have the same value in the first column.

For example, currently my sorted matrix, M, is as follows:

M =

         0       NaN       NaN       NaN       NaN  -50.0903   -0.0576       NaN    3.0000
    0.0637       NaN       NaN   -0.5110   49.9888       NaN       NaN       NaN    2.0000
    0.0637       NaN       NaN       NaN       NaN  -50.0488   -0.3238       NaN    3.0000
    0.1274       NaN       NaN       NaN       NaN  -50.0456   -1.0427       NaN    3.0000
    0.1911       NaN       NaN   -1.4978   49.9976       NaN       NaN       NaN    2.0000
    0.1911       NaN       NaN       NaN       NaN  -49.8547   -1.5354       NaN    3.0000
    0.2548       NaN       NaN       NaN       NaN  -49.9960   -2.0440       NaN    3.0000
    0.3185       NaN       NaN   -2.4941   49.9332       NaN       NaN       NaN    2.0000
    0.3185       NaN       NaN       NaN       NaN  -50.0354   -2.2987       NaN    3.0000
    0.3822       NaN       NaN       NaN       NaN  -49.8377   -2.9497       NaN    3.0000

What I would like, for example for the value of 0.0637 (in the first column) to have the the second row be replaced by the following:

0.0637 NaN NaN -0.5510 49.9888 -50.0488 -0.3238 NaN 13.0000

where 13.000 = 2^2 + 3^2 (i.e. the values of the last column in rows 2 and 3, squared and summed).

I would like this to be repeated for each row of repeating first column values and then construct the new M matrix (knowing that it will have fewer rows).

Is this possible? Please let me know if you need more information, and thanks for your help!

[James Kepper]

As a clarification, the above Matrix is the first 10 rows of my matrix that is 1036 x 9, so if possible to make it "automatic" by means of a for loop or some other means, that would great! Thanks for the help!

[James Tursa]

Are there ever more than two rows repeated? If so, is the last value in that row the sum square of it and all the previous repeated matching rows?

[James Kepper]

Yes, there are more than two rows repeated (ie. the values in the first column are repeated, but the values of the remaining columns are different). And, yes, I would like the value in the last column (column 9) to be the sum square of all of the values of all previous matching rows.

Please let me know if that helps answer your question. Thanks for the help!

[James Kepper]

Here is some code below that I have worked on since posting my question. There might be some room for improvement, but I think it does work. Any feedback would be greatly appreciated (I'm fairly new to MATLAB, so any help is greatly appreciated!). Thanks!

% Construct New Matrix that incorporates values in rows for equal time
% values
time = M(:,1);
for i = 2:length(time)
  if time(i) == time(i-1)
      row            = M(i-1,2:end-1);    % DO NOT include time and src column
      notNanArray    = ~isnan(row);       % array of good values in PREVIOUS row
      goodIdx        = find(notNanArray);
      M(i,goodIdx+1) = M(i-1,goodIdx+1);  % Indices of good values in PREVIOUS row; add 1 since t not included
      M(i,end)       = (M(i-1,end))^2 + (M(i,end))^2;  % Gives new src column value
      % Save index to remove from row of Matrix, M
      removeRowIdxArray(i) = i-1;
  end
end

% Remove rows from M that have same time values
nonZeroIdx = find(removeRowIdxArray); % determines the indices of non-zero values in the removeRowIdx Array
[M,~] = removerows(M,'ind',removeRowIdxArray(nonZeroIdx));

Answer 1

[KSSV]

clc; clear all ;
data= [        0       NaN       NaN       NaN       NaN  -50.0903   -0.0576       NaN    3.0000
    0.0637       NaN       NaN   -0.5110   49.9888       NaN       NaN       NaN    2.0000
    0.0637       NaN       NaN       NaN       NaN  -50.0488   -0.3238       NaN    3.0000
    0.1274       NaN       NaN       NaN       NaN  -50.0456   -1.0427       NaN    3.0000
    0.1911       NaN       NaN   -1.4978   49.9976       NaN       NaN       NaN    2.0000
    0.1911       NaN       NaN       NaN       NaN  -49.8547   -1.5354       NaN    3.0000
    0.2548       NaN       NaN       NaN       NaN  -49.9960   -2.0440       NaN    3.0000
    0.3185       NaN       NaN   -2.4941   49.9332       NaN       NaN       NaN    2.0000
    0.3185       NaN       NaN       NaN       NaN  -50.0354   -2.2987       NaN    3.0000
    0.3822       NaN       NaN       NaN       NaN  -49.8377   -2.9497       NaN    3.0000] ;

M = data ;
%%Get indices of repeated rows
idx = find(diff(data(:,1))==0) ;

for i = 1:length(idx)
    % replace 6th and 7 th column 
    M(idx(i),6:7) = data(idx(i)+1,6:7) ;
    % replace 9th column
    M(idx(i),9) = M(idx(i),9)^2+data(idx(i)+1,9)^2 ;
end

[James Kepper]

Thanks for the response. Unfortunately, this seems only for column 6 and 7. There are other instances in the matrix when columns 1 and 2, and columns 3 and 4, need to be replaced as well.

Please see my comment above on some more code that I posted. I would appreciate your feedback if on it, and I hope it can express what I am trying to accomplish. Thanks for the help!

[KSSV]

Replacing the other columns is straight forward, you can follow the way, how the columns 6,7 and 9 th replaced.