Restructuring table - converting row values to columns
4 views (last 30 days)
Show older comments
Hello all,
I have a large CSV file with a table like this:
% Original table %
Parameter Order Value
=========================
text1 1 0.884
text1 2 0.851
text1 3 0.827
text1 4 0.823
text1 5 0.825
text1 6 0.825
text2 1 0.797
text2 2 0.763
text2 3 0.769
text2 4 0.754
text2 5 0.720
text2 6 0.705
text2 7 0.686
text3 1 0.696
text3 2 0.696
text3 3 0.671
text3 4 0.643
text3 5 0.630
… and so on
I want to restructure the above table into something like shown below:
% New table %
Order text1 text2 text3
=============================
1 0.884 0.797 0.696
2 0.851 0.763 0.696
3 0.827 0.769 0.671
4 0.823 0.754 0.643
5 0.825 0.720 0.630
6 0.825 0.705
7 0.686
Can you please help me acheive this? Thanks in advance!
0 Comments
Answers (2)
Voss
on 18 Jun 2022
% creating a table like the one you have:
Parameter = strcat('text',sprintfc('%d',[1 1 1 1 1 1 2 2 2 2 2 2 2 3 3 3 3 3]).');
Order = [1:6 1:7 1:5].';
Value = rand(18,1);
T = table(Parameter,Order,Value)
% group Parameter and Order from the table:
[pg_idx,pg] = findgroups(T.Parameter);
[og_idx,og] = findgroups(T.Order);
% new table has #-of-order-groups rows and 1+(#-of-parameter-groups) columns
% (first column is order groups - remaining columns are initialized to NaNs):
temp = [{og} repmat({NaN(numel(og),1)},1,numel(pg))];
T_new = table(temp{:},'VariableNames',['Order' pg.']);
% place each Value one at a time from the old table into the new table,
% according to the corresponding order group (row in new_T) and parameter
% group (column in new_T, offset by 1):
for jj = 1:size(T,1)
T_new{og_idx(jj),pg_idx(jj)+1} = T{jj,'Value'};
end
T_new
0 Comments
See Also
Categories
Find more on Annotations 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!