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!

[Simon]

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]

@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.

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!