How to reformat a table

Hi,

My problem is very straightforward: i need to transform T1 into T2. What is tricky about this (in my mind anyway) is that some variable names (those that begin with Cl_) in T1 are now values in T2 (i.e: T1.Cl_10(1) == 1 therefore T2.length(3) == 10 and T2.N(3) == 1, see?). The zeroes in T1 have also been eliminated in T2.

% Table 1
cell1 = {7, 892, 'T19069', 138999, 0,0,0,1,2,0,0,0,0,1};
cell2 = {8, 892, 'T19070', 159237, 1,0,0,0,0,0,0,0,0,0};

T1 = cell2table([cell1; cell2]);
T1.Properties.VariableNames = {'set','sp','voy','nbpc','Cl_01','Cl_02','Cl_03','Cl_04','Cl_05','Cl_06','Cl_07','Cl_08','Cl_09','Cl_10'};


% Table 2
cell1 = {7, 892, 'T19069', 138999, 4, 1};
cell2 = {7, 892, 'T19069', 138999, 5, 2};
cell3 = {7, 892, 'T19069', 138999, 10, 1};
cell4 = {8, 892, 'T19070', 159237, 1, 1};

T2 = cell2table([cell1; cell2; cell3; cell4]);
T2.Properties.VariableNames = {'set','sp','voy','nbpc','length','N'};

How would I go about doing that ?

Thank you,

Answer 1

[Cris LaPierre]

Not pretty per se, but it works.

T1b = mergevars(T1,5:14);
[r,c]=find(T1b.Var5>0);
linind = sub2ind(size(T1b.Var5),r,c);
T3 = [T1b(r,1:4) table(c,T1b.Var5(linind))];
T3.Properties.VariableNames = {'set','sp','voy','nbpc','length','N'};
T3 = sortrows(T3,["set","length"])