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

4 次查看（过去 30 天）
as132
as132 2022-12-10
回答： Lola Davidson 2023-3-17
Ran in:
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!

请先登录，再回答此问题。

采纳的回答

Stephen23
Stephen23 2022-12-10
Ran in:
"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
  2 个评论
Peter Perkins
Peter Perkins 2022-12-12
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.

请先登录，再进行评论。

更多回答（1 个）

Lola Davidson
Lola Davidson 2023-3-17
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

类别

在 Help Center 和 File Exchange 中查找有关 Tables 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by