MATLAB Answers

John
0

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

John
さんによって質問されました 2016 年 12 月 31 日
最新アクティビティ Chris Turnes
さんによって コメントされました 2017 年 1 月 5 日
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.

  0 件のコメント

サインイン to comment.

2 件の回答

Chris Turnes
回答者: Chris Turnes
2017 年 1 月 4 日
 採用された回答

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 件のコメント

Chris Turnes
2017 年 1 月 4 日
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
2017 年 1 月 4 日
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
2017 年 1 月 5 日
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.

サインイン to comment.


回答者: Vishal Neelagiri 2017 年 1 月 3 日

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 件のコメント

John
2017 年 1 月 4 日
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
2017 年 1 月 4 日
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

サインイン to comment.



Translated by