Using splitapply to add a column to a table

3 views (last 30 days)
Bill
Bill on 4 May 2022
Commented: Star Strider on 4 May 2022
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

Accepted Answer

Star Strider
Star Strider on 4 May 2022
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.
.

More Answers (1)

Matt J
Matt J on 4 May 2022
Edited: Matt J on 4 May 2022
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

Products


Release

R2021b

Community Treasure Hunt

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

Start Hunting!