MATLAB: How to filter and regroup data in tables (unstack function)

unstack table

Hello, I am quite new user of Matlab and searched for solution for my problem, but without success. I have table with 2 variables (different reoccurring Elements, element State). There are 72 different elements with 2929 occurrences. (2929×2 table) Example:
I want to regroup my tall table to wide table with unique element numbers as row group and all element states as variables. Example:
I have tried unstack function, which does the grouping/conversion, but variables (states) will be as sum or mean of different states not different values. (I got 1×72 table, but it should be 200x72table) I also tried filtering out unique results of elements and joining tables, but problem was that number of states differ within elements (one Element has 200 different States, but other only 2).
I know this is simple issue, but I just don't have any idea.
Sander

Best Answer

Possibly, this will do what you want, assuming you want empty elements for the shorter columns:
t = array2table([400 2;411 1; 400 3; 400 1; 411 1; 410 5], 'VariableNames', {'Element', 'State'}) %demo data
[elements, ~, row] = unique(t.Element);
elementstates = accumarray(row, t.State, [], @(v) {v}); %create a cell array of states for each element
maxheight = max(cellfun(@numel, elementstates)); %find maximum height of table
%now convert each elementstate matrix into a cell array and append empty if necessary
elementstates = cellfun(@(es) [num2cell(es); cell(maxheight - numel(es), 1)], elementstates, 'UniformOutput', false);
%finally convert cell array into table. Note that sprintfc is not officially documented
newt = cell2table([elementstates{:}], 'VariableNames', sprintfc('Element_%d', elements))
Note that since rows can have empty elements, the columns of the table are now cell arrays, which may not be as convenient for data manipulation.
Related Question