Reshape a matrix according to specific columns.

Hi all,

I have a problem with a specific task I am trying to solve but I am unable to get the correct code to accomplish this in MATLAB.

What I am trying to do is reshape a matrix of 3 columns into a M x N matrix based upon the unique values in the first two columns.

So I in general I have something like this,

[1  a  5;
1  b  6;
1  c  13;
2  a  8;
2  b  9;
3  a  10;
3  b  11;
3  c  12]

And I would like the output to be something like this;

   1   2   3
a  5   8   10
b  6   9   11
c  13  NaN 12

I am thinking I should define the size of the matrix dimensions by the output from the unique function applied on the first two columns provides, but after this I am lost on how to proceed.

Thank you in advance for your help.

Willem

Answer 1

[Azzi Abdelmalek]

a=1000;
b=2000;
c=3000;
A=[1  a  5;
1  b  6;
1  c  13;
2  a  8;
2  b  9;
3  a  10;
3  b  11;
3  c  12]
ii1=unique(A(:,2),'stable');
ii2=unique(A(:,1),'stable');
nn=numel(ii1);
mm=numel(ii2);
out=nan(nn,mm);
for k=1:nn
  idx=find(ismember(A(:,2),ii1(k)));
  out(k,A(idx,1))=A(idx,3)';
end
disp(out)

[Willem]

Thank you, this is exactly as I wanted. But is it also possible to create the output to so that the unique column values are appended to the result.

[Azzi Abdelmalek]

add this

out=[nan ii2';[ii1 out]]

[Willem]

Thank you, this was a simple solution that I should have been able to complete myself.

I have a problem but with the original code.

My original matrix A is actually 1234914x3 (all elements are numbers)

When I run the code to sort the data, ii1 is 180x1 and ii2 is 15121x1, which is correct for what I want to do, but the matrix of "out" is 180x93436. I am unable to understand why it does not become 180x15121, which is my desired output. I have checked my data and I do not believe it is because of bad data points.

[Azzi Abdelmalek]

Try this

ii1=unique(A(:,2));
ii2=unique(A(:,1));
nn=numel(ii1);
mm=numel(ii2);
out=nan(nn,mm);
for k=1:nn
  idx=find(ismember(A(:,2),ii1(k)));
  id=find(ismember(ii2,A(idx,1)));
  out(k,id)=A(idx,3)';
end
disp(out)
out=[nan ii2';[ii1 out]]

Answer 2

[Andrei Bobrov]

A = {1  'a'  5;
1  'b'  6;
1  'c'  13;
2  'a'  8;
2  'b'  9;
3  'a'  10;
3  'b'  11;
3  'c'  12}

[i0,i2,i2] = unique(A(:,2))
c = cat(1,A{:,1}); % OR [j0,j2,j2] = unique(cat(1,A{:,1}));
Z = accumarray([i2,c],cat(1,A{:,3}),[],[],nan);% OR c -> j2
out = cell(size(Z)+1);
out(2:end,1) =i0;
out(2:end,2:end) = num2cell(Z);
out(1,2:end) = num2cell(1:max(c)); % OR num2cell(j0);

[Azzi Abdelmalek]

This will not work for

A = {1  'a'  5;
1  'b'  6;
1  'c'  13;
2  'a'  8;
2  'b'  9;
5 'a'  10;
5  'b'  11;
5  'c'  12}

[Andrei Bobrov]

corrected

[Azzi Abdelmalek]

The result is

    []    [ 1]    [  2]    [  3]    [  4]    [ 5]
    'a'    [ 5]    [  8]    [NaN]    [NaN]    [10]
    'b'    [ 6]    [  9]    [NaN]    [NaN]    [11]
    'c'    [13]    [NaN]    [NaN]    [NaN]    [12]

It should be

    []     [ 1]    [  2]      [ 5]
    'a'    [ 5]    [  8]      [10]
    'b'    [ 6]    [  9]      [11]
    'c'    [13]    [NaN]      [12]

[Azzi Abdelmalek]

You can add

idx1=setdiff(min(c):max(c),c);
out(:,idx1+1)=[]

[Andrei Bobrov]

used expression after sign '% OR'