How to do pivot from table-type variable
12 views (last 30 days)
Show older comments
Hi,
I have this table-type data
tdata.yearID=[1950;1950;1950;1951;1951;1951;1951;1952;1952;1952];
tdata.cluster=[1;3;4;1;2;3;4;1;2;3];
tdata.GroupCount=[5;2;6;2;2;4;2;1;2;2];
tdata= struct2table( tdata)
tdata =
10×3 table
yearID cluster GroupCount
______ _______ __________
1950 1 5
1950 3 2
1950 4 6
1951 1 2
1951 2 2
1951 3 4
1951 4 2
1952 1 1
1952 2 2
1952 3 2
Currently I am using this code below to do pivot, but it was very slow for large data. Is there a way to vectorize this code?
[uniqYr, ~, JYr]= unique( tdata.yearID);
nEventData.yearID= [1: 2000]';
[ nEventData.cnt]= zeros( length(nEventData.yearID), 4);
for runYr= 1: length( uniqYr)
indxYr= uniqYr( runYr);
tloc = (JYr== runYr);
tt = tdata( tloc, :);
nEventData.cnt( indxYr, tt.cluster')= tt.GroupCount';
end % for runYr
So results look like this for rows 1949-1955. Other rows have zeros.
nEventData.cnt (1949:1955,:)
ans =
0 0 0 0
5 0 2 6
2 2 4 2
1 2 2 0
0 0 0 0
0 0 0 0
0 0 0 0
2 Comments
Peter Perkins
on 5 Oct 2018
Pete, your code does not run and it's not at all clear what you want as your result. You are going to need to be much more clear.
Peter Perkins
on 5 Oct 2018
The updated question and output clarify that unstack does what you need, as Steve suggests.
Accepted Answer
Steven Lord
on 5 Oct 2018
The output you described looks somewhat like the first example in the documentation for the unstack function. I think what you want is something close to the pivotedTdata variable created by this example:
tdata.yearID=[1950;1950;1950;1951;1951;1951;1951;1952;1952;1952];
tdata.cluster=[1;3;4;1;2;3;4;1;2;3];
tdata.GroupCount=[5;2;6;2;2;4;2;1;2;2];
tdata= struct2table( tdata)
pivotedTdata = unstack(tdata, 'GroupCount', 'cluster')
You can retrieve data for a particular year this way:
pivotedTdata(pivotedTdata.yearID == 1950, :)
More Answers (1)
Lola Davidson
on 17 Mar 2023
pivotedTable = pivot(tdata, Rows="yearID", Columns="cluster", DataVariable="GroupCount");
0 Comments
See Also
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!