How to calculate Trailing Moving Sums going up vertically in a table?

I have the following table:

- - -

Columns 'L' and 'U' consist of cells that contain object names that correspond to the headers in columns 4-281. Example for ABC.L{1,1} :

- - -

Goal: For every date verify what objects are in 'L' (respectively 'U') and sum the aggregate of those objects' 4-point trailing moving sum and its standard deviation (going up in the table!) and store it in a new variable, e.g. LSum and LStd for 'L' as well as USum and UStd for 'U'. For dates with insufficient values, e.g. 15-Jul-2016 with only 3 instead of 4 time steps ahead, return NaN's.

- - -

How I would start:

for row=1:size(ABC,1)
    row_values = ABC{row,:}; 
    row_values = row_values(4:end);
% How to make the loop for columns L and U where there are multiple objects in one cell?
% How can I use 'movsum' and 'movstd' here to calculate values vertically going up?
end;

Thanks a lot for your help!

Answer 1

[Image Analyst]

Extract the column of data, then use conv() to sum up the counts and values, then divide them.

column = randi(9, 11, 1) % Sample data
kernel = [0;0;0;1;1;1;1]; % Tell it to use a kernel that will compute the trailing 4 values.
onesVector = ones(length(column), 1);
cellSum = conv(onesVector, kernel, 'same')
valuesSum = conv(column, kernel, 'same')
trailingMean = valuesSum ./ cellSum

[John]

thanks Image Analyst. Can you be more precise, please? Especially how I can use those formulas in the context of the problem as stated above (loops within loop etc.)

[Image Analyst]

John, there may be some function in the Financial Toolbox to do it but I don't have that toolbox so I just used conv. If you have a 2D array and want to do it for every column, just loop over columns. First you have to get the numbers out of your cell array but I assume you know how to do that. If not, attach your ABC cell array in a .mat file and I'll show you. Let's assume the numerical 2D data is called "data" then,

[rows, columns] = size(data);
kernel = [0;0;0;1;1;1;1]; % Tell it to use a kernel that will compute the trailing 4 values.
onesVector = ones(columns, 1);
for col = 1 : columns
    thisColumn = data(:, col); % Extract all rows from this column.
    cellSum = conv(onesVector, kernel, 'same');
    valuesSum = conv(thisColumn, kernel, 'same');
    trailingMean(col) = valuesSum ./ cellSum;
end

[John]

that would be helpful. Thanks in advance, Image Analyst! Attached you find my ABC .mat file

[John]

that would be helpful. Thanks in advance, Image Analyst! Attached you find my ABC .mat file

[Image Analyst]

Try this:

% Load table variable ABC into the workspace.
load('ABC table.mat')
[rows, columns] = size(ABC)
kernel = [0;0;0;1;1;1;1]; % Tell it to use a kernel that will compute the trailing 4 values.
onesVector = ones(rows, 1);
trailingMean = zeros(rows, columns);
for col = 4 : columns
    thisColumn = ABC{:, col}; % Extract all rows from this column.
    cellSum = conv(onesVector, kernel, 'same');
    valuesSum = conv(thisColumn, kernel, 'same');
    trailingMean(:, col) = valuesSum ./ cellSum;
end
% View as an image
imshow(trailingMean, []);
axis on;

[John]

In your cellSum vector the first 3 rows indicate numbers 1,2,3 whereas they should be zero. (no trailing sum calculations since not enough values for 4-point trailing sum calculation) -> Thus, in the trailingMean table the first 3 rows should be NaN's. Another strange thing: in your trailingMean table the first computable value for the trailing sum for x1COVGY shouldn't be 0.0209 but 0.0836 (sum of 0.0377, 0.0095, -0.0037 and 0.0401)

Thanks in advance for fixing this, Image Analyst!

[Image Analyst]

You're correct, and so is my code. Look:

K>> thisColumn(1:4)
ans =
        0.0376999566108383
       0.00945625359211284
       -0.0037372516115108
        0.0400936676870531
K>> valuesSum(4)
ans =
        0.0835126262784934

I don't know where you got that 0.0209 number.

How you handle what they call "edge effects" is a judgment call. There are several ways you can do it. What I did was to have a shrinking window as it nears the edges. If you want to avoid the first 3 completely, then you can call conv() or conv2() with the 'valid' option and it won't move the window closer to the edge if the window would leave the array. However this way will leave you with 3 fewer rows than your data so row 1 corresponds to row 4 of your data. This is fine as long as you remember that fact. Or you can do what I did and have a shrinking window and then just replace all the elements in the first 3 rows with nans after the loop has finished:

trailingMean(1:3, :) = nan;

[John]

Perfect, thank you!