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

1 ビュー (過去 30 日間)
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?


Voss 2022 年 6 月 6 日
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};
% 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>

その他の回答 (1 件)

Peter Perkins
Peter Perkins 2022 年 6 月 13 日
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.


Find more on Tables in Help Center and File Exchange





Community Treasure Hunt

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

Start Hunting!

Translated by