Using splitapply to add a column to a table

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

Answer 1

[Star Strider]

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.

.

[Bill]

Thanks!

[Star Strider]

As always, my pleasure!

Answer 2

[Matt J]

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       

[Bill]

Thanks!