How to transform a table to a nested structure comparable to a pivot table in excel?

I have a table which I want to structure into a nested structure to get something like a pivot table in excel, where the data is structured according to certain conditions so that I can calculate means and standard deviations for different study groups.

E.g. I want to calculate the mean of value 1 for all data where test='A', condition='C', participant='X' and date='d1'

The table I have looks like this:

% Build sample table
test=["A";"A";"B";"A";"B"];
condition=["C";"RT";"C";"C";"RT"];
participant=["X";"X";"Y";"Z";"Y"];
date=["d1";"d2";"d1";"d1";"d3"];
sample=[1;2;3;4;5];
value1=[10;11;12;10;12];
value2=[3;4;2;5;5];
value3=[13;16;25;25;36];

T=table(test,condition,participant,date,value1,value2,value3)

T = 5×7 table
    test    condition    participant    date    value1    value2    value3
    ____    _________    ___________    ____    ______    ______    ______

    "A"       "C"            "X"        "d1"      10        3         13  
    "A"       "RT"           "X"        "d2"      11        4         16  
    "B"       "C"            "Y"        "d1"      12        2         25  
    "A"       "C"            "Z"        "d1"      10        5         25  
    "B"       "RT"           "Y"        "d3"      12        5         36  

And the nested structure I want to get should look somewhat like that:

% Build nested structure
ssample=[1;2;3];
svalue1=[4;7;12];
svalue2=[3;9;1];
svalue3=[4;34;12];
s=table(ssample,svalue1,svalue2,svalue3);

m=struct('d1',s,'d2',s,'d3',s);
n=struct('C',m,'RT',m);
o=struct('X', n, 'Y', n, 'Z',n);
EVAL=struct('A',o,'B',o);

I don't want to assign all data seperatly since my actual table is 300x200. Is there anyway to do that at least semi-automatic?

Thanks fo any suggestions!

Answer 1

[Stephen23]

"I want to calculate the mean of value 1 for all data where test='A', condition='C', participant='X' and date='d1'"

The MATLAB approach:

% Build sample table
test=["A";"A";"B";"A";"B"];
condition=["C";"RT";"C";"C";"RT"];
participant=["X";"X";"Y";"Z";"Y"];
date=["d1";"d2";"d1";"d1";"d3"];
sample=[1;2;3;4;5];
value1=[10;11;12;10;12];
value2=[3;4;2;5;5];
value3=[13;16;25;25;36];
T=table(test,condition,participant,date,value1,value2,value3)

T = 5×7 table
    test    condition    participant    date    value1    value2    value3
    ____    _________    ___________    ____    ______    ______    ______

    "A"       "C"            "X"        "d1"      10        3         13  
    "A"       "RT"           "X"        "d2"      11        4         16  
    "B"       "C"            "Y"        "d1"      12        2         25  
    "A"       "C"            "Z"        "d1"      10        5         25  
    "B"       "RT"           "Y"        "d3"      12        5         36  

G = groupsummary(T,["test","condition","participant","date"],"mean","value1")

G = 5×6 table
    test    condition    participant    date    GroupCount    mean_value1
    ____    _________    ___________    ____    __________    ___________

    "A"       "C"            "X"        "d1"        1             10     
    "A"       "C"            "Z"        "d1"        1             10     
    "A"       "RT"           "X"        "d2"        1             11     
    "B"       "C"            "Y"        "d1"        1             12     
    "B"       "RT"           "Y"        "d3"        1             12     

X = G.test=="A" & G.condition=="C" & G.participant=="X" & G.date=="d1";
G{X,'mean_value1'}

ans = 10

[as132]

Great, thank you very much!

[Peter Perkins]

A bit more on Stephen23's answer:

You have four grouping variables, so a cross-tabulation is 4-D. However, the format of a pivot table in Excel is necessily 2-D, so instead Excel nests some of those dimensions. On the other hand, groupsummary flattens those 4 dimensions into a table with four grouping variables and however many summary vars you want. Stephen23 computed the mean of value1, but that summary table could just as easily had means of all three of your data vars. And it could have contained the mean AND std dev for all three.

I would also recommend using categorical grouping variables where possible instead of string.

Answer 2

[Lola Davidson]

As of R2023a, you can use the "pivot" function to orient the data more like what excel provides: https://www.mathworks.com/help/matlab/ref/pivot.html

For example, you could put two variables along the rows, and two along the columns:

P = pivot(T, Rows=["test" "condition"], Columns=["participant","date"], DataVariable="value1", Method="mean")

This provides a nested table as the output. You can get vertical slices of the data using dot indexing like with structs:

>> P.X.d1