How to do pivot from table-type variable

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

[Peter Perkins]

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]

The updated question and output clarify that unstack does what you need, as Steve suggests.

Answer 1

[Steven Lord]

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, :)

[Pete sherer]

Thank you very much

Answer 2

[Lola Davidson]

As of R2023a, you can also use the pivot function.

pivotedTable = pivot(tdata, Rows="yearID", Columns="cluster", DataVariable="GroupCount");