How to use "movsum()" with panel data in a table using loop mechanisms?

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

[Chris Turnes]

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.

[Chris Turnes]

To elaborate on this answer a bit, let me break down the indexing expression T{i, T.L(i,:)}. The outer curly braces extract and concatenate the values within the table rather than giving a table back as output. Notice the difference:

 >> T(1, { 'ABC', 'GHI' })
 ans =
       ABC        GHI   
     _______    ________
     -1.3499    -0.12414
 >> T{1, { 'ABC', 'GHI' } }
 ans =
    -1.3499   -0.1241

Second, by using a cellstr as the second index, you can specify the table variables by name (as my above demonstration showed). Finally, the T.L(i,:) extracts the value of the ith row of the table variable L. So what you get is T{1, T.L(1,:)} is the same as T{1, {'ABC', 'GHI'}}, which is the same as [T.ABC(1), T.GHI(1)].

[John]

Thank you, Chris! One last problem occurred:

Table variable subscripts must be real positive integers, logicals, character vectors, or cell arrays of character
vectors.

I assume this is because the function starts calculation at the wrong cell? I tried

L_sum(i) = sum(G{i, G.L(i,4:end)});

but L_sum is empty still. Do you think this is because of NaN's?

[Chris Turnes]

Without having access to the data it's a bit hard to say, but my guess is that L(i,4:end) is not a cellstr but something else. You should verify that L(i,4:end) returns a vector that only contains character row vectors. You can check this with iscellstr.

[Chris Turnes]

Also, you can always do dbstop if error to debug the situation once an error is hit. When the error occurs, you can examine each portion of the indexing expression to determine where something has gone awry. You might find the tools described here useful.

[John]

iscellstr(G.L(i,4:end))
ans =
  logical

   1

I don't see why the code doesn't work.. Now, the function calculates the sum of all cells, which is not what I am looking for because I only want objects in "L" to be considered..

L_sum(i) = sum(ABC{i, G.L(i,4:end)});

[Chris Turnes]

Again, without having the data I really can't say. Based on how your variables are named in the original question, my guess is that what you want is L_sum(i) = sum(ABC{i, ABC.L(i, :)});

[John]

The ABC / G in last comment was a typo. I attached the ABC.mat file now, would appreciate if you have a look at it. The code still returns

"Table variable subscripts must be real positive integers, logicals, character vectors, or cell arrays of character
vectors."

[Chris Turnes]

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.

Answer 2

[Vishal Neelagiri]

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.

[John]

the following error message occurs: "Index exceeds matrix dimensions." Also, I see that LSum is empty whereas it should perform the sum calculation for each day. Thank you for helping fix this problem!!

[John]

Another issue: "i=1:27" is only the case where I have 27 objects within my cell in the L column. But this changes over time once I scroll down, e.g. 10 years down the line (with only 24 entries). How can I replace the "27" with a generic form that adjusts for the number of objects within the cell array? @Vishal Neelagiri