how to circular shift table columns

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

Answer 1

[Guillaume]

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)

[Cheerful]

I got it.

replace fill missing with

t = unstack([t; portfolio], 'weight', 'portfolio');
t{:,2:end}(isnan(t{:,2:end}))=0;

many thanks

Answer 2

[Peter Perkins]

For what you're doing, I recommend that you take advantage of a table's row names. It makes growing the rows happen automatically.

>> portfolioNames = strcat({'portfoliot'},num2str((0:20)','%-d'));

>> ttotal = table;
>> newData = table({'Apple';'IBM'},[10;90],'VariableNames',{'stockName' 'weight'});
>> ttotal{newData.stockName,end+1} = newData.weight;
>> ttotal = ttotal(:,[end 1:min(end-1,19)]);
>> ttotal.Properties.VariableNames = portfolioNames(1:width(ttotal))
ttotal =
             portfoliot0
             ___________
    Apple    10         
    IBM      90         
>> newData = table({'Apple';'Groupon'},[20;80],'VariableNames',{'stockName' 'weight'});
>> ttotal{newData.stockName,end+1} = newData.weight;
>> ttotal = ttotal(:,[end 1:min(end-1,19)]);
>> ttotal.Properties.VariableNames = portfolioNames(1:width(ttotal))
ttotal =
               portfoliot0    portfoliot1
               ___________    ___________
    Apple      20             10         
    IBM         0             90         
    Groupon    80              0         
>> newData = table({'Citi';'Goldman'},[30;70],'VariableNames',{'stockName' 'weight'});
>> ttotal{newData.stockName,end+1} = newData.weight;
>> ttotal = ttotal(:,[end 1:min(end-1,19)]);
>> ttotal.Properties.VariableNames = portfolioNames(1:width(ttotal))
ttotal =
               portfoliot0    portfoliot1    portfoliot2
               ___________    ___________    ___________
    Apple       0             20             10         
    IBM         0              0             90         
    Groupon     0             80              0         
    Citi       30              0              0         
    Goldman    70              0              0

If you run this code, you'll get warnings about assigning default values when you grow the table with new rows, but you can turn those off like I did.

[Cheerful]

You are right.

So how to turn off the warnings?

Warning: The assignment added rows to the table, but did not assign values to all of the table's existing variables. Those
variables have been extended with rows containing default values. 
> In table/subsasgnParens (line 290)
  In table/subsasgnBraces (line 139)
  In table/subsasgn (line 64)

[Peter Perkins]

help warning.

In short:

>> t(1:4,2) = table([5;6;7;8]); % generate the warning
>> [~,id] = lastwarn % figure out the ID
id =
    'MATLAB:table:RowsAddedExistingVars'
>> warning('off','MATLAB:table:RowsAddedExistingVars') % turn it off

You probably also want to turn the warning back on at some point.

[Cheerful]

Many thanks. Your method works wonderful