Moving sum in a timetable

Hi,

I've timetable with 2 clomuns (time column) 527040×1 datetime and 527040×1 double (1st column) , please look at my attached screenshot :)

the datetime is on a minutley basis from 01.01.2018 00:00:00 until 01.01.2019 23:59:00

I want to calculte the moving sum or sum of the double column for t0 = sum over the next 5 minutes startting at t1

So it should be value at t0 = sum of of double from t1+...+t6

for value at t1 = sum t2+...+t7

and so on.....

any ideas :)

Answer 1

[Bob Thompson]

I'm a little bit confused what you're asking for, but here is a first attempt. As I understand you are looking for each row to have a sum of the next several doubles. That would be done something like:

for i = 1:size(data,1) % I don't know what your data variable is called, replace as needed
    if i+5<=size(data,1)
        data(i,3) = sum(data(i+1:i+5,2));
    elseif i+1<=size(data,1)
        data(i,3) = sum(data(i+1:end,2));
    else
        data(i,3) = NaN;
    end
end

I do not, off the top of my head, know a way to calculate a moving some without a loop in Matlab.

[Antonio Melieni]

there is a movsum function, but ok

it says : "Variable index exceeds table dimensions."

Can u maybe check the screenshot I made to see how my table looks like :)

[Antonio Melieni]

Just had to do some corrections but thanks for the hint :)

[Bob Thompson]

I was not aware that movsum existed, that's good to know. In glancing over the documentation for it, it does not seem like it would fit what you were looking for though, as the summations seem to be limited to either centered on a specific value, or trailing after a specific value, whereas you want terms leading that value.

Glad you got things worked out though.

EDIT**

It is possible to use movsum to sum a range leading each summation index by defining the before and after range.

B = movsum(A,[0 5]); %[kb kf]

The limitation of this ability, however, is that the range values must be positive integers, so it is not possible to examine values exclusively in front of the summation index, as including 0 as the kb term will begin the summation at the current index.

[Antonio Melieni]

Thanks Bob :)

[Steven Lord]

The limitation of this ability, however, is that the range values must be positive integers, so it is not possible to examine values exclusively in front of the summation index, as including 0 as the kb term will begin the summation at the current index.

If you specify datetime or duration as the value for the SamplePoints name-value pair argument, the window length values may be nonnegative integer values or may be duration values. So for this scenario using the timetable RowTimes as the SamplePoints in movsum would allow you to specify minutes([5 0]) as the window length input.

You are correct that you can't take the moving sum (or use any of the moving functions) and exclude the element around which the window is located. But for purposes of movsum that just leaves one post-movsum step. Use fillmissing or isnan to replace any NaN values in A with 0 if necessary then subtract A from the results of movsum. That should be fairly safe for most data sets.