how to circular shift table columns

12 次查看（过去 30 天）
Cheerful
Cheerful 2017-1-27
评论： Cheerful 2017-2-1
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

请先登录，再回答此问题。

采纳的回答

Guillaume
Guillaume 2017-1-27
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)
  1 个评论
Cheerful
Cheerful 2017-2-1
编辑：Cheerful 2017-2-1
I got it.
replace fill missing with
t = unstack([t; portfolio], 'weight', 'portfolio');
t{:,2:end}(isnan(t{:,2:end}))=0;
many thanks

请先登录，再进行评论。

更多回答（1 个）

Peter Perkins
Peter Perkins 2017-1-27
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.
  3 个评论
显示 1更早的评论
Peter Perkins
Peter Perkins 2017-1-31
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
Cheerful 2017-2-1
Many thanks. Your method works wonderful

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Portfolio Optimization and Asset Allocation 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by