Reorganize table and make groups of rows to columns

5 views (last 30 days)
Hnor
Hnor on 3 Aug 2021
Commented: Hnor on 3 Aug 2021
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?

Accepted Answer

Peter Perkins
Peter Perkins on 3 Aug 2021
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

More Answers (1)

Benjamin Kraus
Benjamin Kraus on 3 Aug 2021
The command you are looking for is called unstack.
For example:
newtbl = unstack(origtbl,'Return','Permno')

Community Treasure Hunt

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

Start Hunting!

Translated by