MATLAB: How to use “movsum()” with panel data in a table using loop mechanisms

Question

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.

Answer 1

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:

1. Iterate over the rows
2. Compute the L_sum value for that row by L_sum(i) = sum(T{i, T.L(i,:)});
3. Repeat the same for the U_sum.
4. 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.