MATLAB: How to circular shift table columns

circular shiftMATLABrotatetable

Dear Experts,
I have a master table, tableTotal. Initially it is empty.
portfoliot0 is a table of two columns
portfoliot0 
  stockName weight
  Apple     10
  IBM       90
It is then assigned to tableTotal.
TableTotal
stockName portfoliot0
Apple     10
IBM       90
Now portfoliot0 has new stocks and weights
   portfoliot0 
    stockName weight
    Apple     20
    Groupon   80
Hence TableTotal will need to shift while portfoliot0 is appended to it.
    TableTotal
    stockName portfoliot0 portfoliot1
    Apple     20          10
    Groupon   80          0
    IBM       0           90
Now portfoliot0 has new stocks and weights
     portfoliot0 
      stockName weight
      Citi      30
      Goldman   70
TableTotal will need to shift while portfoliot0 is appended to it. This is the result
TableTotal
    stockName portfoliot0 portfoliot1 portfoliot2
    Apple     0           20          10
    Groupon   0           80          0
    IBM       0           0           90
    Citi      30          0           0
    Goldman   70          0           0
I have to repeat the above 20 times till it becomes first in, last out. Meaning it will have maximum 20 columns. Once it reaches 20 columns, every new data will result the oldest data to be discarded.
I cant assign portfoliot"i" where "i" is a variable. Do i need to use eval?
Thank you very much for your advice LL

Best Answer

This seems to do what you want:
function t = circulartableadd(t, portfolio, maxportfolios)
    %circulartableadd add a new portfolio to the table t shifting or rotating right the existing portfolio up to maxportfolios
    %newtable = circulartableadd([], portfolio)         %to initialise
    %newtable = circulartableadd(oldtable, portfolio)   %to add a portfolio
    %newtable = circulartable(___, maxportfolios)       %to specify the maximum number of portfolios other than the default 20.
    %
    %oldtable:      circular table to hold the portfolios. set to [] to initialise with 1st portfolio
    %portfolio:     portfolio table with at least 2 variables: stockName, weight
    %maxportfolios: optional integer specifying the max number of portfolios to hold. Old portfolios are dropped to make room for new ones    
    if nargin < 3
        maxportfolios = 20;
    else
        validateattributes(maxportfolios, {'numeric'}, {'scalar', 'integer', 'positive'});
    end
    assert(all(ismember({'stockName', 'weight'}, portfolio.Properties.VariableNames)), 'Missing stockName or weight variables in portfolio');
    if isempty(t)
        t = portfolio(:, {'stockName', 'weight'});
        t.Properties.VariableNames{strcmp(t.Properties.VariableNames, 'weight')} = 'portfolio0';
    else
        assert(all(strncmp(t.Properties.VariableNames(2:end), 'portfolio', numel(portfolio))), 'invalid variable names in circular table');
        assert(strcmp(t.Properties.VariableNames{1}, 'stockName'), '1st variable of circular table must be ''stockName');
        t = stack(t, 2:width(t), 'IndexVariableName', 'portfolio', 'NewDataVariableName', 'weight');
        portfolionumber = str2double(regexp(cellstr(char(t.portfolio)), '\d+', 'match', 'once')) + 1;
        t.portfolio = arrayfun(@(pn) sprintf('portfolio%d', pn), portfolionumber, 'UniformOutput', false);
        t(portfolionumber >= maxportfolios, :) = [];
        portfolio.portfolio = repmat({'portfolio0'}, height(portfolio), 1);
        t = fillmissing(unstack([t; portfolio], 'weight', 'portfolio'), 'constant', 0, 'DataVariables', @isnumeric);
        [~, varorder] = sort(str2double(regexp(t.Properties.VariableNames(2:end), '\d+', 'match', 'once')));
        t = t(:, [1 varorder+1]);
    end
end
Usage example:
portfolio = table({'Apple'; 'IBM'}, [10;90], 'VariableNames', {'stockName', 'weight'})
tabletotal = circulartableadd([], portfolio)
portfolio = table({'Apple'; 'Groupon'}, [20;80], 'VariableNames', {'stockName', 'weight'})
tabletotal = circulartableadd(tabletotal, portfolio)
portfolio = table({'Citi'; 'Goldman'}, [30;70], 'VariableNames', {'stockName', 'weight'})
tabletotal = circulartableadd(tabletotal, portfolio)
Related Question