How can I Stack data in a Table

Hi,

My data looks like this (A 3046X5 table):

Date | Excess_Stock | Excess_Index | FD_Stock | FD_Index

731910 | 0.01 | 0.02 | 0.05 | 0.06

731911 | 0.02 | 0.04 | 0.04 | 0.4

. .

. .

My question: Is it possible to get the data stack the following way

Date SecName Excess FD

731910 Stock 0.01 0.05

731911 Stock 0.02 0.04

731910 Index 0.02 0.06

731911 Index 0.04 0.4

Answer 1

[Guillaume]

I'm not sure stack can produce the output you want, but with only two different categories to stack a manual extraction would work:

names = {'Date' 'Excess_Stock' 'Excess_Index' 'FD_Stock' 'FD_Index'};
T = array2table([731910  0.01  0.02  0.05  0.06;...
                 731911  0.02  0.04  0.04  0.4], ...
                'VariableNames', names);
C1 = table2cell(T(:, {'Date', 'Excess_Stock', 'FD_Stock'}));  %or T(:, [1 2 4]);
C2 = table2cell(T(:, {'Date', 'Excess_Index', 'FD_Index'}));  %or T(:, [1 3 5]);
C1 = [C1(:, 1), repmat({'Stock'}, height(T), 1), C1(:, [2 end])];
C2 = [C2(:, 1), repmat({'Index'}, height(T), 1), C2(:, [2 end])];
newnames = {'Date', 'SecName', 'Excess', 'FD'};
stackedT = cell2table([C1; C2], 'VariableNames', newnames)

[Peter Kristiansen]

I think your right.. Currently, I'm working with a smaller data set to get the model work. In the future I think that I will have about 8 categories.. But your answer gives the right output!

Answer 2

[Peter Perkins]

There's a function specifically to do this: stack. In this case, you are stacking two groups of variables, so it's not quite as simple as the simplest case, but it's pretty simple.

% set up some fake data

>> vnames = {'Date' 'Excess_Stock' 'Excess_Index' 'FD_Stock' 'FD_Index'};
>> tunstacked = array2table([[1;2;3] rand(3,4)],'VariableNames',vnames)
tunstacked = 
    Date    Excess_Stock    Excess_Index    FD_Stock    FD_Index
    ____    ____________    ____________    ________    ________
    1       0.77491         0.084436        0.80007     0.18185 
    2        0.8173          0.39978        0.43141      0.2638 
    3       0.86869          0.25987        0.91065     0.14554 

% stack the two Excess vars and the two FD vars
>> tstacked = stack(tunstacked,{{'Excess_Stock' 'Excess_Index'} {'FD_Stock' 'FD_Index'}});
>> tstacked.Properties.VariableNames = {'Date' 'SecName' 'Excess' 'FD'};
>> tstacked.SecName = categorical(tstacked.SecName,[2 3],{'Stock' 'Index'})
tstacked = 
    Date    SecName     Excess       FD   
    ____    _______    ________    _______
    1       Stock       0.77491    0.80007
    1       Index      0.084436    0.18185
    2       Stock        0.8173    0.43141
    2       Index       0.39978     0.2638
    3       Stock       0.86869    0.91065
    3       Index       0.25987    0.14554

[Guillaume]

Aaah! That's how you do it. Shouldn't that be documented?

edit: I just realised that it is sort of documented under the tips section which is a) not very clear, b) not where I'd expect it. I would expect to see this in the documentation of the input vars.

[Peter Perkins]

By "that", I think you mean, "stacking more than one set of variables at a time." Fair enough, I've made a note to have this made more obvious in the documentation.

It is also possible to call stack twice and horizontally concatenate the two results.