How to move array data to additional columns based on repeats in a vector or column

I have an array like A (below) with duplicates in the second column, for example there are three 1's,three 6's and four 7's. What I would like to do is either have a separate array for each block (to include all the multiples) or preferably be able to move the data for the multiples in the 3rd and 4th columns to the same row as the first element but in extra columns I could preallocate for? i have some code to move the duplicates out onto the same row and replace the original data with nans but cannot get them all on one row.

A=[ 1            1       1.0277      0.56932
            2            1       1.0426       0.4202
            3            1        1.048      0.42017
            4            2       1.1466      0.48309
            5            6       1.4731       0.4024
            6            6       1.5244      0.62436
            7            6       1.5295      0.40456
            8            7         1.55      0.58695
            9            7       1.5658      0.41037
           10            7       1.6107       0.4693
           11            7       1.6248      0.62088
           12            8       1.7182         0.48]

for k=2:size(A)
    if A(k,2)==A(k-1,2)
        A(k-1,5)=A(k,3)
        A(k-1,6)=A(k,4)
        A(k,3)=nan
        A(k,4)=nan

      end
  end

Best regards

Steve

Answer 1

[Stephen23]

Creating new columns would not be trivially simple without expanding the array continuously. A simpler solution is to put the groups into their own cells of one cell array: one of the most versatile is to use accumarray:

>>[U,~,X] = unique(A(:,2));
>> fun = @(r){A(r,1:4)};
>> Z = accumarray(X,A(:,1),[],fun);
>> Z{:}
ans =
   1.00000   1.00000   1.02770   0.56932
   2.00000   1.00000   1.04260   0.42020
   3.00000   1.00000   1.04800   0.42017
ans =
   4.00000   2.00000   1.14660   0.48309
ans =
   5.00000   6.00000   1.47310   0.40240
   6.00000   6.00000   1.52440   0.62436
   7.00000   6.00000   1.52950   0.40456
ans =
    8.00000    7.00000    1.55000    0.58695
    9.00000    7.00000    1.56580    0.41037
   10.00000    7.00000    1.61070    0.46930
   11.00000    7.00000    1.62480    0.62088
ans =
   12.00000    8.00000    1.71820    0.48000

You could easily reshape the data within each group, or select only the columns that you want: simply change the function to suit your needs, e.g.:

fun = @(r){A(r,3:4)};

or

fun = @(r){reshape(A(r,3:4).',1,[])};

to produce:

 >> Z {:}
  ans =
     1.02770   0.56932   1.04260   0.42020   1.04800   0.42017
  ans =
     1.14660   0.48309
  ans =
     1.47310   0.40240   1.52440   0.62436   1.52950   0.40456
  ans =
     1.55000   0.58695   1.56580   0.41037   1.61070   0.46930   1.62480   0.62088
  ans =
     1.71820   0.48000

You might also find X useful, e.g.:

>> U(X)
ans =
   1
   1
   1
   2
   6
   6
   6
   7
   7
   7
   7
   8

[Stephen Devlin]

Hi Stephen, I am probably able to use a form similar to the one you have where the returns are split by the repeat value or have a single row where there are n sets of xy coordinates for the occasion there are n repeats, probably the method you have here will be best as it is easier to loop and post process.

Would it be too much to ask what these lines mean/do?

>>[U,~,X] = unique(A(:,2));
>> fun = @(r){A(r,1:4)};
>> Z = accumarray(X,A(:,1),[],fun);
>> Z{:}

I have seen people on here using fun and accumarray but I've not understood the doc for them (now that I have a practical example of how it is useful to me that may change).

Thank you Stephen

[Stephen23]

[U,~,X] = unique(A(:,2));

I use unique to get the vector X: this is basically a sequential enumeration of the input values, or you could think of it as a mapping from whatever the input values are to 1,2,3,.... The input values here are A(:,2), as you specified that this defines the groups. Thus each group gets a number!

fun = @(r){A(r,1:4)};

fun is an anonymous function that collects columns 1:4 of rows r of array A and puts that sub-array into a scalar cell array.

accumarray(X,A(:,1),[],fun);

uses the values of A(:,1) as inputs to fun (because the first column of A seems to contain the rows of A, and fun requires a row index input). fun then returns those rows of A and accumarray puts them into the output cell array: their positions in the output are determined by X (the group enumeration 1,2,3,...): thus each row gets placed into the appropriate cell array for that group.

I know that accumarray is a bit of a mindflip at first, but it is well worth the time and effort learning how it works. Practice.

[Stephen Devlin]

That is going to take a bit of study and practice, big thanks Stephen