Using splitapply to add a column to a table

2 次查看（过去 30 天）
Bill
Bill 2022-5-4
评论： Star Strider 2023-8-7
I have data in a matlab table in long format. It contains stock tickers and monthly returns. I'd like to group the data by tickers, than grab the next periods return, and make a column of that next periods return. Here is my code. It returns cell arrays for each group. Is there a way to put each cell array back into Tbl with a new a column named NextMonthsReturn? Thanks!
Tickers = ["AAPL"; "AAPL"; "AAPL"; "MSFT"; "MSFT"; "MSFT"];
MonthlyReturns = [4.3;3.2;5.6;7.3;2.5;-5.6];
Tbl = table(Tickers, MonthlyReturns);
Grps = findgroups(Tbl.('Tickers'));
Rslts = splitapply(@(x) {[x(2:end);nan(1,1)]}, Tbl.('MonthlyReturns'), Grps);
Rslts

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

采纳的回答

Star Strider
Star Strider 2022-5-4
Ran in:
I am not certain what result you want.
Try this —
Tickers = ["AAPL"; "AAPL"; "AAPL"; "MSFT"; "MSFT"; "MSFT"];
MonthlyReturns = [4.3;3.2;5.6;7.3;2.5;-5.6];
Tbl = table(Tickers, MonthlyReturns)
Tbl = 6×2 table
Tickers MonthlyReturns _______ ______________ "AAPL" 4.3 "AAPL" 3.2 "AAPL" 5.6 "MSFT" 7.3 "MSFT" 2.5 "MSFT" -5.6
Grps = findgroups(Tbl.('Tickers'));
Rslts = splitapply(@(x) {[x(2:end);nan(1,1)]}, Tbl.('MonthlyReturns'), Grps);
Tbl = addvars(Tbl,cat(1,Rslts{:}),'NewVariableNames',{'NextMonthsReturn'})
Tbl = 6×3 table
Tickers MonthlyReturns NextMonthsReturn _______ ______________ ________________ "AAPL" 4.3 3.2 "AAPL" 3.2 5.6 "AAPL" 5.6 NaN "MSFT" 7.3 2.5 "MSFT" 2.5 -5.6 "MSFT" -5.6 NaN
See the documentation for addvars and cat for details on those functions.
.
  4 个评论
显示 2更早的评论
Simon
Simon 2023-8-7
编辑：Simon 2023-8-7
If Tickers are not sorted, either Tbl or Rslts needs to be sorted before addvars. How to sort Tbl so the rows in the same group are put next to each other? Sorting the group can work, but will it have any bug? It seems not able to maintain the original within group order. That worries me.
Tickers_unsorted = ["AAPL"; "AAPL"; "MSFT"; "MSFT"; "MSFT"; "AAPL"];
MonthlyReturns = [4.3; 5.6; 7.3; 2.5; -5.6; 3.2];
Tbl = table(Tickers_unsorted, MonthlyReturns);
Grps = findgroups(Tbl.('Tickers2'))
% not in correct order
Rslts = splitapply(@(x) {[x(2:end);nan(1,1)]}, Tbl.('MonthlyReturns'), Grps);
% need to sort table rows so same group rows are next to each other
[~, ix] = sort(Grps);
Tbl = Tbl(ix,:);
Tbl = addvars(Tbl,cat(1,Rslts{:}),'NewVariableNames',{'NextMonthsReturn'})
Star Strider
Star Strider 2023-8-7
@Simon — I’m not certain that was a problem here, however it doesn’t specifically concern the problem this post is addressing (adding the variable to the existing table), since the column to be added has already been calculated.
It would likely be better for you to post this as a new question, citing to this or copying the code. I will not address it here.

请先登录，再进行评论。

更多回答（1 个）

Matt J
Matt J 2022-5-4
编辑：Matt J 2022-5-4
Ran in:
Tickers = ["AAPL"; "AAPL"; "AAPL"; "MSFT"; "MSFT"; "MSFT"];
MonthlyReturns = [4.3;3.2;5.6;7.3;2.5;-5.6];
Tbl = table(Tickers, MonthlyReturns);
out=varfun(@(x) [x(2:end);nan(1,1)],Tbl,'Input','MonthlyReturns','Group','Tickers');
Tbl.NextMonthsReturns=out{:,3}
Tbl = 6×3 table
Tickers MonthlyReturns NextMonthsReturns _______ ______________ _________________ "AAPL" 4.3 3.2 "AAPL" 3.2 5.6 "AAPL" 5.6 NaN "MSFT" 7.3 2.5 "MSFT" 2.5 -5.6 "MSFT" -5.6 NaN

类别

在 Help Center 和 File Exchange 中查找有关 Web Services 的更多信息

标签

产品


版本

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by