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

采纳的回答

Turlough Hughes
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);
Then using unstack we get:
pivotVars = ["Tvalue", "Pvalue"];
Tp = unstack(T,pivotVars,'ROI')
Tp = 2×13 table
Name Tvalue_A Tvalue_B Tvalue_C Tvalue_D Tvalue_E Tvalue_F Pvalue_A Pvalue_B Pvalue_C Pvalue_D Pvalue_E Pvalue_F _______ ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ "TEST1" 5 6 NaN 3 2 NaN 4 6 NaN 1 4 NaN "TEST2" NaN NaN 6 8 0 1 NaN NaN 7 3 4 5
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)
Tout = 2×6 table
A B C D E F Tvalue Pvalue Tvalue Pvalue Tvalue Pvalue Tvalue Pvalue Tvalue Pvalue Tvalue Pvalue ________________ ________________ ________________ ________________ ________________ ________________ TEST1 5 4 6 6 NaN NaN 3 1 2 4 NaN NaN TEST2 NaN NaN NaN NaN 6 7 8 3 0 4 1 5
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
T2 = 4×6 table
A B C D E F ___ ___ ___ _ _ ___ Test1_t 5 6 NaN 3 2 NaN Test1_p 4 6 NaN 1 4 NaN Test2_t NaN NaN 6 8 0 1 Test2_p NaN NaN 7 3 4 5
  4 个评论
DavidL88
DavidL88 2022-1-11
Thank you. I'm looking to create a table like the one below for publication. I thought the most straightforward would be to create the table above in MatLab and then use Excel to generate the below one. I have alot of data so if it could be automated it would be really helpful. I think I could generate the string from the original variables like "t"+" "="_"tvalue but I'm not sure how after converting the column to row for ROI as you outlined to then place this data - t and p in one cell in a table?
Brain region
Left cuneus Left entorhinal
Experiment 1 t = -3.66 t = -3.38
p = 0.001 p = 0.002
x, y, z = -26.1, -6.2, 52.2 x, y, z = -48.6, -43.7, 0.7
Nvx = 31 Nvx = 20
Experiment 2 t = -4.66 t = -7.38
p = 0.002 p = 0.003
x, y, z = -46.1, -7.2, 52.2 x, y, z = -48.6, -63.7, 1.7
Nvx = 21 Nvx = 22
Walter Roberson
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
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"

类别

Help CenterFile Exchange 中查找有关 Tables 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by