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

1 view (last 30 days)
Austin Burritt
Austin Burritt on 6 Jun 2022
Answered: Peter Perkins on 13 Jun 2022
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?

Accepted Answer

Voss
Voss on 6 Jun 2022
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>

More Answers (1)

Peter Perkins
Peter Perkins on 13 Jun 2022
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.

Tags

Products


Release

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by