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

1 view (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.

Accepted Answer

Chris Turnes
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:
  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.
  8 Comments
John
John on 4 Jan 2017
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
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.

Sign in to comment.

More Answers (1)

Vishal Neelagiri
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.
  2 Comments
John
John on 4 Jan 2017
Edited: John on 4 Jan 2017
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
John on 4 Jan 2017
Edited: John on 4 Jan 2017
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

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!