Reformat table with a column as a top row

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

Answer 1

[Turlough Hughes]

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

[DavidL88]

Hi Turlough,

Thanks for this. Would the second format (in the edit above) be easier? That way all data is in one continuous column. I was planning to add more variables into each column along t and p values to put the data I have in a publishable format.

[Turlough Hughes]

See the edit above.

[DavidL88]

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]

If your goal is an Excel file with that content, then I recommend that you create a cell array and use writecell()

Answer 2

[Walter Roberson]

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"