Reformat table with a column as a top row
2 次查看(过去 30 天)
显示 更早的评论
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 个评论
采纳的回答
Turlough Hughes
2022-1-10
编辑:Turlough Hughes
2022-1-11
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 个评论
Walter Roberson
2022-1-11
If your goal is an Excel file with that content, then I recommend that you create a cell array and use writecell()
更多回答(1 个)
Walter Roberson
2022-1-10
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 个评论
另请参阅
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!