- Iterate over the rows
- Compute the L_sum value for that row by L_sum(i) = sum(T{i, T.L(i,:)});
- Repeat the same for the U_sum.
- Assign the results into the table.

4 views (last 30 days)

I have the following 606 x 281 table ('ABC'):

- - -

Goal: For each date, create 2 new variables (e.g. 'LSum' and 'USum'). 'LSum' should calculate the sum of all cell values across the columns (4-281), but only with those values whose header is in the cell array of ABC.L, for that specific date. In the same fashion, 'USum' should calculate the sum of all cell values across the columns, but only with those values whose header is in the cell array of ABC.U, for that specific date.

- - -

How I would start:

% load content

load ('ABC.mat');

% run through every date, starting from the top

for row=1:size(ABC,1);

% for-loop for 'L' that determines for what specific cells (of col. 4-281) the following calculation has to be done: how?

% for-loop for 'U' that determines for what specific cells (of col. 4-281) the following calculation has to be done: how?

% now generate new variables

LSum = sum(); % But how can I use if clause here to select only eligible cells that enter into the sum calculation?

USum = sum(); % Same problem here as LSum

end;

% Concatenate table ABC and the newly formed variables into 1 table

ABC = [ABC(:,1:3) LSum USum ABC(:,3+1:end)];

- - -

Thanks in advance for your help, especially for the looping through date and the cell arrays of 'L' and 'U' at the same time.

Chris Turnes
on 4 Jan 2017

I think you just want to use sum here, and not movsum, since you're summing over different columns each time. Regardless, this is fairly straightforward with table indexing, actually. The strategy would be:

- Iterate over the rows
- Compute the L_sum value for that row by L_sum(i) = sum(T{i, T.L(i,:)});
- Repeat the same for the U_sum.
- Assign the results into the table.

Here's a small example on just the L_sum part:

>> L = [ { 'ABC', 'GHI' }; { 'DEF', 'GHI' }; { 'ABC', 'DEF' } ];

>> ABC = randn(3,1);

>> DEF = randn(3,1);

>> GHI = randn(3,1);

>> L_sum = zeros(height(T),1);

for i = 1:height(T)

L_sum(i) = sum(T{i, T.L(i,:)});

end

>> T.L_sum = L_sum

T =

3×5 table

L ABC DEF GHI L_sum

______________ _______ _________ ________ _______

'ABC' 'GHI' -1.3499 -0.063055 -0.12414 -1.474

'DEF' 'GHI' 3.0349 0.71474 1.4897 2.2044

'ABC' 'DEF' 0.7254 -0.20497 1.409 0.52044

There are probably fancier ways of doing this, but I think this might be the easiest to look at and understand.

Chris Turnes
on 4 Jan 2017

Chris Turnes
on 5 Jan 2017

Your cellstrs are wrapped in cells. That is, each ABC.L(i) is a 1 x 1 cell, containing a 1 x 27 cell. So, when you do iscellstr(ABC.L(1,:)) as I suggested before, it reports false.

If you change the expression to sum(ABC{i, ABC.L{i}}), it will get you most of the way there, but ABC.L{556} has a value of NaN rather than a cellstr, so you'll get an error here. You'll have to handle that however you want -- either checking with isnan or using try / catch or whatever.

Vishal Neelagiri
on 3 Jan 2017

You can use Properties.VariableNames to access the header names of the table ABC. Then you can use these property names to compare them with the 1 X 27 cell elements of either L or U. You can refer to the code below:

props = ABC.Properties.VariableNames;

% props should be a 1*281 cell array consisting of the header names of the columns

LSum = 0;

for k=1:606;

for i=1:27

for j=4:281

if (strcmp(props{j},ABC.L{1,i})

LSum = LSum + ABC{k,j};

end

end

end

end

You can use a similar workflow to calculate USum as well.

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

Start Hunting!
## 0 Comments

Sign in to comment.