How to reformat this table's information so that a column of data becomes the header's for matching data?

I apologize for the unclear title, not sure how to properly describe my request.

I have a table of data that is formatted as such when read into a matlab table:

     Items       Family  
    _______    __________

    "Item1"    "Family 1"
    "Item2"    "Family 2"
    "Item3"    "Family 4"
    "Item4"    "Family 2"
    "Item5"    "Family 3"
    "Item6"    "Family 1"
    "Item7"    "Family 1"
    "Item8"    "Family 3"

I want to convert this into a differently formatted table that has the items separated by families, like this:

    Family 1    Family 2    Family 3     Family 4 
    ________    ________    _________    _________

    "Item1"     "Item2"     "Item5"      "Item3"  
    "Item6"     "Item4"     "Item8"      <missing>
    "Item7"     <missing>   <missing>    <missing>

I am struggling to come up with a solution for this. How can this be achieved, and is a table the best way to format this data?

Answer 1

[Voss]

Here's one way to do it:

% construct the initial table:
Items = ["Item1";"Item2";"Item3";"Item4";"Item5";"Item6";"Item7";"Item8"];
Family = ["Family 1";"Family 2";"Family 4";"Family 2";"Family 3";"Family 1";"Family 1";"Family 3"];
t = table(Items,Family)

t = 8×2 table
     Items       Family  
    _______    __________

    "Item1"    "Family 1"
    "Item2"    "Family 2"
    "Item3"    "Family 4"
    "Item4"    "Family 2"
    "Item5"    "Family 3"
    "Item6"    "Family 1"
    "Item7"    "Family 1"
    "Item8"    "Family 3"

% group t.Family:
[idx,group_ids] = findgroups(t.Family);
n_groups = numel(group_ids);                        % number of Family groups

% split t.Items into groups according to Family:
grouped_items = splitapply(@(x){x},t.Items,idx);
n_items = cellfun(@numel,grouped_items);            % number of Items in each Family group

% construct 2d string array of Item* Strings, where each column 
% is the Items in a Family group, filled out with <missing> String(s):
vals = repmat(string(missing()),max(n_items),n_groups);
for ii = 1:n_groups
    vals(1:n_items(ii),ii) = grouped_items{ii};
end

% convert 2d String array into a cell array,
% to be used as an argument list in table():
vals = num2cell(vals,1);

% create the new table:
new_t = table(vals{:},'VariableNames',group_ids)

new_t = 3×4 table
    Family 1    Family 2     Family 3     Family 4 
    ________    _________    _________    _________

    "Item1"     "Item2"      "Item5"      "Item3"  
    "Item6"     "Item4"      "Item8"      <missing>
    "Item7"     <missing>    <missing>    <missing>

[Austin B]

This is excellent thank you. Produces exactly what I was looking for!

[Voss]

You're welcome!

Answer 2

[Peter Perkins]

I guess this is a useful format? Voss shows one way, here's another:

>> Items = "Item" + (1:8)';
>> Family = "Family" + [1;2;4;2;3;1;1;3];
>> t = table(Items,Family);
>> t2 = rowfun(@(x)[x' repmat(missing,1,3-length(x))],t,"GroupingVariable","Family","OutputVariableName","Items")
t2 =
  4×3 table
     Family      GroupCount                  Items              
    _________    __________    _________________________________
    "Family1"        3         "Item1"    "Item6"      "Item7"  
    "Family2"        2         "Item2"    "Item4"      <missing>
    "Family3"        2         "Item5"    "Item8"      <missing>
    "Family4"        1         "Item3"    <missing>    <missing>
>> t3 = array2table(t2.Items',"VariableNames",t2.Family)
t3 =
  3×4 table
    Family1     Family2      Family3      Family4 
    _______    _________    _________    _________
    "Item1"    "Item2"      "Item5"      "Item3"  
    "Item6"    "Item4"      "Item8"      <missing>
    "Item7"    <missing>    <missing>    <missing>

But I might argue that there are better ways to store these "ragged" data, for example as t2.