Reformat table with a column as a top row
3 views (last 30 days)
Show older comments
DavidL88
on 10 Jan 2022
Commented: Walter Roberson
on 11 Jan 2022
How do I re-format the below table so that one of the Columns (ROI) becomes a row with the data re-structured under this new format?
OLD TABLE
Name ROI Tvalue Pvalue
TEST1 A 5 4
TEST1 B 6 6
TEST1 D 3 1
TEST1 E 2 4
TEST2 C 6 7
TEST2 D 8 3
TEST2 E 0 4
TEST2 F 1 5
NEW TABLE
A B C D E F
Name Tvalue Pvalue Tvalue Pvalue Tvalue Pvalue Tvalue Pvalue Tvalue Pvalue Tvalue Pvalue
TEST1 5 4 6 6 3 1 2 4
TEST2 6 7 8 3 0 4 1 5
OR
Name A B C D E F
TEST1 t = 5 t = 6 t = 3 t = 2
p = 4 p = 6 p = 1 p = 4
TEST2 t = 6 t = 8 t = 0 t = 1
p = 7 p = 3 p = 4 p = 5
0 Comments
Accepted Answer
Turlough Hughes
on 10 Jan 2022
Edited: Turlough Hughes
on 11 Jan 2022
You can reorganise the data pretty close to what you showed using unstack, but to get the header organised as you have shown would take some extra steps. First I just recreate your table as follows:
Name = [repmat("TEST1",4,1); repmat("TEST2",4,1)];
ROI = ["A","B","D","E","C","D","E","F"].';
Tvalue = [5 6 3 2 6 8 0 1].';
Pvalue = [4 6 1 4 7 3 4 5].';
T = table(Name, ROI, Tvalue, Pvalue);
pivotVars = ["Tvalue", "Pvalue"];
Tp = unstack(T,pivotVars,'ROI')
To obtain merged columns for A, B, C, etc, one has to make a nested table. Here's one way to do that:
headerNames = Tp.Properties.VariableNames;
for thisROI = unique(T.ROI.')
idx = ismember(headerNames, pivotVars + "_" + thisROI);
s.(thisROI) = array2table(Tp{:,idx},'VariableNames', pivotVars);
end
Tout = struct2table(s,'RowNames',Tp.Name)
Edit (regarding your comment below) - you can't do that exactly other than creating string representations "t = 5", and so on. In any case, I think the table is tidier if you use indicators in the row names instead having "t=..." and "p=..." in every cell:
varNames = unique(T.ROI.');
T2 = array2table(zeros(4,numel(varNames)),'VariableNames',varNames,...
'RowNames',{'Test1_t','Test1_p','Test2_t','Test2_p'});
for thisROI = varNames
idx = ismember(headerNames, pivotVars + "_" + thisROI);
T2.(thisROI) = reshape(Tp{:,idx}.',[],1);
end
T2
4 Comments
Walter Roberson
on 11 Jan 2022
If your goal is an Excel file with that content, then I recommend that you create a cell array and use writecell()
More Answers (1)
Walter Roberson
on 10 Jan 2022
You can get the Tvalue / PValue to be the same by making each variable in the table be a table itself.
However, there is no way to get the empty parts to display as empty. To get emptiness you would need to convert everything to cell array of character vectors, or to string array -- but both of those are going to display "decoration" such as
TEST1 {'5'} {'4'} {'6'} {'6'} {''} {''} "" "" "3" "1"
0 Comments
See Also
Categories
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!