How can I Stack data in a Table

2016 9 21
2 个回答
回答已采纳
更新时间：2016 9 23
2 次查看（30 天）

0 个投票

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

请先登录，再回答此问题。

 采纳的回答

Guillaume
Guillaume 2016-9-21

0 个投票

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)

1 个评论
显示 -1更早的评论 隐藏 -1更早的评论

Peter Kristiansen
Peter Kristiansen 2016-9-21
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!

请先登录，再进行评论。

更多回答（1 个）

Peter Perkins
Peter Perkins 2016-9-21

0 个投票

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

2 个评论
显示 无 隐藏 无

Guillaume
Guillaume 2016-9-22
编辑：Guillaume 2016-9-22
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
Peter Perkins 2016-9-23
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.

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Tables 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by