Create new variable based on existing columns of a cell

I have a cell-array with 600 000 rows and 5 columns. The cell-array is sorted by a code (c1) and then by year (c2). In the following example I only present 3 different codes and a period of 5 years.

A:

    c1   c2        c3        c4  c5 
    1   2006    20060425    559 'IA'
    1  2007  20070129    559  'LO'
    1  2007  20070826    559  'VC'
    1  2008  20080825     34  'VP'
    1  2009  20090116     34  'ZO'
    4  2007  20070725     42  'OI'
    4  2008  20080712     42  'TF'
    4  2008  20080428     42  'XU'
    11  2007    20070730    118 'AM'
    11  2008    20080912    118 'HK'
    11  2009    20090318      2 'VT'
    11  2010    20100121      2 'ZZ'

I would like to obtain a new variable that gives for each code (C1) the years in wich C1 appears in the sample and the corresponding c4 value. For instance:

New:

x  2006 2007 2008 2009 2010
1  559  559  34   34   - 
4   -   42   42   -    -
11  -   118  118  2    2

To get to my cell-array, this is the code I used so far:

a1=T_ANNDAT3;
a2=I{:,7};
a3=I{:,6};
a4=I{:,16};
a5=I{:,1};
TRACK_AN = [num2cell([a2 a1 a4 a3]) a5];
TRACK_AN(cell2mat(TRACK_AN(:,1))==0,:)=[];
[~,indTA,~] = unique(strcat(TRACK_AN(:,1),TRACK_AN(:,2),TRACK_AN(:,4),TRACK_AN(:,5)));
TRACK_AN = TRACK_AN(indTA,:);

Can someone help me? Thanks

Answer 1

[Azzi Abdelmalek]

A={'c1'   'c2'   'c3'       'c4' 'c5'
     1  2006    20060425     559 'IA'
     1  2007  20070129    559  'LO'
     1  2007  20070826    559  'VC'
     1  2008  20080825     34  'VP'
     1  2009  20090116     34  'ZO'
     4  2007  20070725     42  'OI'
     4  2008  20080712     42  'TF'
     4  2008  20080428     42  'XU'
     11  2007    20070730    118 'AM'
     11  2008    20080912    118 'HK'
     11  2009    20090318      2 'VT'
     11  2010    20100121      2 'ZZ'}
% M=cell2table(A(2:end,:),'Variablenames',A(1,:))
c2=cell2mat(A(2:end,2));
c1=cell2mat(A(2:end,1));
c4=cell2mat(A(2:end,4));
c=[c1 c2];
[ii,jj,kk]=unique(c,'rows');
o=[ii  c4(jj)];
cc1=o(:,1);
cc2=o(:,2);
cc4=o(:,3);
aa=unique(c1);
bb=unique(c2);
V=cell(numel(aa),numel(bb));
for  hh=1:numel(aa);
  idx1=ismember(bb,cc2(cc1==aa(hh)))
  ccc4=cc4(cc1==aa(hh));
  V(hh,idx1)=num2cell(ccc4);
end
out=[{'x'}  num2cell(bb');num2cell(aa) V]

The result

    'x'     [2006]    [2007]    [2008]    [2009]    [2010]
    [ 1]    [ 559]    [ 559]    [  34]    [  34]        []
    [ 4]        []    [  42]    [  42]        []        []
    [11]        []    [ 118]    [ 118]    [   2]    [   2]

[Maria]

Thank you. It is working!

Answer 2

[Azzi Abdelmalek]

Using table

M=cell2table(A(2:end,:),'Variablenames',A(1,:))
[ii,jj]=unique(M(:,1:2))
a=[ii M(jj,4)]
out=unstack(a,'c4','c2')