Create new variable based on existing columns of a cell

1 次查看（过去 30 天）
Maria
Maria 2014-7-19
回答： Azzi Abdelmalek 2014-7-19
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

请先登录，再回答此问题。

采纳的回答

Azzi Abdelmalek
Azzi Abdelmalek 2014-7-19
编辑：Azzi Abdelmalek 2014-7-19
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]

更多回答（1 个）

Azzi Abdelmalek
Azzi Abdelmalek 2014-7-19
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')

类别

在 Help Center 和 File Exchange 中查找有关 Shifting and Sorting Matrices 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by