Reorganize table and make groups of rows to columns

I have a 115248x2 timetable that is organized like the small example below, where Permno is each stocks ID:

However, I want to reorganize it to look like this:

How do I go about that?

Answer 1

[Peter Perkins]

You need unstack:

>> Date = datetime(1963,[1;2;3;1;2;3;1;2;3],[31;28;29;31;28;29;31;28;29]);
>> Permno = [10065;10065;10065;10145;10145;10145;10225;10225;10225];
>> Return = rand(9,1);
>> tt = timetable(Date,Permno,Return)
tt =
  9×2 timetable
       Date        Permno     Return 
    ___________    ______    ________
    31-Jan-1963    10065      0.39223
    28-Feb-1963    10065      0.65548
    29-Mar-1963    10065      0.17119
    31-Jan-1963    10145      0.70605
    28-Feb-1963    10145     0.031833
    29-Mar-1963    10145      0.27692
    31-Jan-1963    10225     0.046171
    28-Feb-1963    10225     0.097132
    29-Mar-1963    10225      0.82346
>> tt2 = unstack(tt,'Return','Permno')
Warning: Table variable names that were not valid MATLAB identifiers have been modified. Since table variable names must be unique, any table variable names that
happened to match the new identifiers also have been modified.
To use the original INDVAR values as table variable names, set 'VariableNamingRule' to 'preserve'. 
tt2 =
  3×3 timetable
       Date        x10065      x10145      x10225 
    ___________    _______    ________    ________
    31-Jan-1963    0.39223     0.70605    0.046171
    28-Feb-1963    0.65548    0.031833    0.097132
    29-Mar-1963    0.17119     0.27692     0.82346

unstack has put an 'x' in front of the Permno numbers to make them names, so then you can access each variable easily:

>> tt2.x10065
ans =
      0.39223
      0.65548
      0.17119

You can give those variables your own names, or even tell unstack to not rename them, but in that case it will the be a little more typing to access the data:

>> tt2 = unstack(tt,'Return','Permno','VariableNamingRule','preserve')
tt2 =
  3×3 timetable
       Date         10065      10145       10225  
    ___________    _______    ________    ________
    31-Jan-1963    0.39223     0.70605    0.046171
    28-Feb-1963    0.65548    0.031833    0.097132
>> tt2.('10065')
ans =
      0.39223
      0.65548
      0.17119

[Hnor]

Thanks!

Answer 2

[Benjamin Kraus]

The command you are looking for is called unstack.

For example:

newtbl = unstack(origtbl,'Return','Permno')

[Hnor]

Thanks!