Reformat table with a column as a top row

1 view (last 30 days)
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

Accepted Answer

Turlough Hughes
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);
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 Comments
DavidL88
DavidL88 on 11 Jan 2022
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 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()

Sign in to comment.

More Answers (1)

Walter Roberson
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"

Categories

Find more on Characters and Strings in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!