Matlab / Simulink : create array from fact table

Question

Would greatly appreciate some help with the following challenge:

I am importing a fact table from a database into a Matlab table. The fact table consist of a sequence of observations across several categories as follows:

SeqNo       Cat    Observation
1           A      0.3
1           B      0.5
1           C      0.6
2           B      0.9
2           C      1.0
3           A      1.2
3           C      1.5

I need now to delinearize the fact table and create a matrix (or another table) with the categories representing columns, i.e. something like this:

Seq    A      B     C
1     0.3    0.5   0.6
2     NaN    0.9   1.0
3     1.2    NaN   1.5

I played around with findgroup and the split-apply-combine workflow, but no luck. In the end I had to resort to SPSS Modeler create to create a properly structured csv file for import, but would need to achieve this fully in Matlab or Simulink.

Any help would be most welcome.

Answer 1

%Import table
T=readtable('excelTable.xlsx');
obs_Array=T.Observation;
%Extract unique elements from SeqNo column
seqNo_values=(unique(T.SeqNo)); 
%Extract unique elements from Cat column
cat_values=(unique(T.Cat));

%Notice that the elements in seqNo_values
%already specify the row of your new matrix

%The index of each element in cat_values
%does the same thing for the columns of your new matrix. 

numRows=numel(seqNo_values);
numCols=numel(cat_values);

%Initialize a new, NaN matrix:
reformatted_matrix=NaN(numRows,numCols);

%magic numbers:
seqNo_ColNum=1;
cat_ColNum=2;

for i=1:numel(obs_Array)
    target_row=T(i,seqNo_ColNum);
    %convert to array for ease of indexing
    target_row=table2array(target_row);

    %convert to array for ease of indexing
    target_cat=table2array(T(i,cat_ColNum));
    target_cat=cell2mat(target_cat);

    target_col=find([cat_values{:}] == target_cat);
    reformatted_matrix(target_row,target_col)=obs_Array(i);
end 
    reformatted_matrix

Output:

reformatted_matrix =

    0.3000    0.5000    0.6000
       NaN    0.9000    1.0000
    1.2000       NaN    1.5000