How to use movsum with datetime?

I have a 79880x43 table, where the first column is datetime (yyyy-mm-dd hh:mm:ss) and columns 2-43 are stations. In each row there's preciptation measurements with one hour time step. Now I need to use movsum on the whole table together, so it calculates the sum of two hours of sample points (two rows) etc and also sums the time because I need the timestamp too for further analsys. So far I have used:

t = timetable2table(tt)
k = hours(2)
date = t(:,1)
t.date = [ ] % removed the the date column from the table 
movsum(t,k,'omitnan','Samplepoints',date);

But this gives me an error: "Error using movsum Invalid data type. First input must be numeric or logical."

I have also attached a picture of my original table.

Answer 1

[Seth Furman]

@SaaraL I should first mention that the function call in your original question should work if you pass a numeric vector or matrix instead of a table as the first argument. You can convert your timetable or table to a numeric matrix using table2array.

https://www.mathworks.com/help/matlab/ref/table2array.html

k = hours(2);
movsum(table2array(tt),k,'omitnan','SamplePoints',tt.date)

If you want to get the corresponding row-times for the start of each window you can use movmin. Using the values in Walter's example

https://www.mathworks.com/help/matlab/ref/movmin.html

>> k = hours(3);
>> tt = timetable(datetime(2021,3,17,2:12,1,11)',[-8,-2,-2,-2,-7,-9,3,-6,2,-9,4]');
>> tt.Time(movmin(1:height(tt),k,'SamplePoints',tt.Time),1)

ans = 

  11×1 datetime array

   17-Mar-2021 02:01:11
   17-Mar-2021 02:01:11
   17-Mar-2021 03:01:11
   17-Mar-2021 04:01:11
   17-Mar-2021 05:01:11
   17-Mar-2021 06:01:11
   17-Mar-2021 07:01:11
   17-Mar-2021 08:01:11
   17-Mar-2021 09:01:11
   17-Mar-2021 10:01:11
   17-Mar-2021 11:01:11

Likewise, if you want to get the corresponding row-times for the end of each window you can use movmax. Using the values in Walter's example

https://www.mathworks.com/help/matlab/ref/movmax.html

>> k = hours(3);
>> tt = timetable(datetime(2021,3,17,2:12,1,11)',[-8,-2,-2,-2,-7,-9,3,-6,2,-9,4]');
>> tt.Time(movmax(1:height(tt),k,'SamplePoints',tt.Time),1)

ans = 

  11×1 datetime array

   17-Mar-2021 03:01:11
   17-Mar-2021 04:01:11
   17-Mar-2021 05:01:11
   17-Mar-2021 06:01:11
   17-Mar-2021 07:01:11
   17-Mar-2021 08:01:11
   17-Mar-2021 09:01:11
   17-Mar-2021 10:01:11
   17-Mar-2021 11:01:11
   17-Mar-2021 12:01:11
   17-Mar-2021 12:01:11

Answer 2

[Walter Roberson]

Keep it as a time table and use retime() . If you need the hours to be paired up, 1+2, 3+4, then it is easy enough. If you need sliding, 1+2, 2+3, 3+4, then you can do that as two cases using disjoint pairs, with a 1 hour starting difference between the two.

But you cannot add timestamps -- only durations, or timestamp plus duration.

[SaaraL]

Yes, I need sliding. Could you elaborate how to do that?

k = hours(2)

retime(tt,k)

I tried this, but I get the error : "Error using timetable/retime (line 140) All input timetables must have row times with the same data type as target time vector."

[Walter Roberson]

retime with 'regular', 'sum', 'timestep', hours(2))

and then a second pass with tt(2:end, :)

[SaaraL]

I don't think I was clear enough with what I wanted. I need to sum the first two hours (also the first two measurements in all of the stations, so row 1 and 2). Then I need it to sum hours 2 and 3, then 3 and 4. So the sum is sliding. But this code sums rows 1 and 2 then 3 and 4.

[Walter Roberson]

yes, variable holds 1 2 3 4 5 6, first pass you retime giving 1+2, 3+4, 5+6. Then you do a second retime of the data without the first sample so it is handed 2 3 4 5 6 and retimes to 2+3, 4+5, 6. You now have all of the needed outputs between the two so interweave the two. Drop the final entry because it will be the last value by itself. Which of the two retime() the extra shows up in depends on whether you had an odd or even number of entries originally, but it will always be the last one after the interweaving.

[SaaraL]

When I used:

tt2 = retime(tt,'regular','sum','TimeStep',dt)

tt3 = retime(tt(2:end,:),'regular','sum','TimeStep',dt)

They both gave me the same result... I don't understand how.

Also, how do I interweave the two outputs, so the datetime is in correct order or it shouldn't matter when finding yearly maximum later?

Thank you for your answers so far, of course! Have helped a lot!

[SaaraL]

Doing the second retime, it still takes it according to the datetime and only gives me sums of even hours.

[Walter Roberson]

Ah. You can pass in a vector of new times instead of using regular and hours(2)

[Walter Roberson]

tt = timetable(datetime('now')+hours(-10:0).', randi([-9 9], 11,1))

tt = 11×1 timetable
            Time            Var1
    ____________________    ____

    15-Mar-2021 12:45:20      0 
    15-Mar-2021 13:45:20      3 
    15-Mar-2021 14:45:20     -7 
    15-Mar-2021 15:45:20      6 
    15-Mar-2021 16:45:20      6 
    15-Mar-2021 17:45:20     -2 
    15-Mar-2021 18:45:20      6 
    15-Mar-2021 19:45:20     -8 
    15-Mar-2021 20:45:20      7 
    15-Mar-2021 21:45:20     -9 
    15-Mar-2021 22:45:20     -4 

h1 = dateshift(tt.Time(1),'start','hour')

h1 = datetime
   15-Mar-2021 12:00:00

N = height(tt);
N2 = ceil(N/2)+1;
r1 = retime(tt, h1+2*hours(0:N2-1).', 'sum')

r1 = 7×1 timetable
            Time            Var1
    ____________________    ____

    15-Mar-2021 12:00:00      3 
    15-Mar-2021 14:00:00     -1 
    15-Mar-2021 16:00:00      4 
    15-Mar-2021 18:00:00     -2 
    15-Mar-2021 20:00:00     -2 
    15-Mar-2021 22:00:00     -4 
    16-Mar-2021 00:00:00      0 

r2 = retime(tt,h1+hours(1)+2*hours(0:N2-1).', 'sum')

r2 = 7×1 timetable
            Time            Var1
    ____________________    ____

    15-Mar-2021 13:00:00     -4 
    15-Mar-2021 15:00:00     12 
    15-Mar-2021 17:00:00      4 
    15-Mar-2021 19:00:00     -1 
    15-Mar-2021 21:00:00    -13 
    15-Mar-2021 23:00:00      0 
    16-Mar-2021 01:00:00      0 

ntt = sortrows([r1;r2]);
h2 = dateshift(tt.Time(end),'start', 'hour');
ntt = ntt(timerange(h1,h2),:)

ntt = 10×1 timetable
            Time            Var1
    ____________________    ____

    15-Mar-2021 12:00:00      3 
    15-Mar-2021 13:00:00     -4 
    15-Mar-2021 14:00:00     -1 
    15-Mar-2021 15:00:00     12 
    15-Mar-2021 16:00:00      4 
    15-Mar-2021 17:00:00      4 
    15-Mar-2021 18:00:00     -2 
    15-Mar-2021 19:00:00     -1 
    15-Mar-2021 20:00:00     -2 
    15-Mar-2021 21:00:00    -13 

Here you have to retime to a final time after your last reading in order to get your last reading included. This results in one final reading after each of the tables that you can remove. Also, an extra entry can be generated in one of the two depending on whether the number of rows is odd or even. The timerange is used to select only the desired times.

[SaaraL]

Thank you so much! Been struggling with this for weeks. Two last questions: the output times are of the starting hour, but I need it to be at the end of two hours because that means how much precipitation had accumulated by that time. So when it puts two hours together ex. 00:00:00 + 01:00:00, it gives that output time as 00:00:00, but I need it to be 01:00:00. I thought of just changing the date column of the output table to start at 01:00:00, but how would I do that? Also, I don't really understand how do I exactly get the last entry (last row) to be included.

[SaaraL]

Does this sliding method work with using hours 3,4,5...12 too by just changing the 2 in the code to these hours?

[SaaraL]

I fixed the two questions that I asked, but now the question is how do I do the same thing with 3,4,5 ... 12 hours? Just replacing the 2 with 3 didn't do it correctly.

[Walter Roberson]

window = 3;   %hours

tt = timetable(datetime('now')+hours(-10:0).', randi([-9 9], 11,1))

tt = 11×1 timetable
            Time            Var1
    ____________________    ____

    17-Mar-2021 02:01:11     -8 
    17-Mar-2021 03:01:11     -2 
    17-Mar-2021 04:01:11     -2 
    17-Mar-2021 05:01:11     -2 
    17-Mar-2021 06:01:11     -7 
    17-Mar-2021 07:01:11     -9 
    17-Mar-2021 08:01:11      3 
    17-Mar-2021 09:01:11     -6 
    17-Mar-2021 10:01:11      2 
    17-Mar-2021 11:01:11     -9 
    17-Mar-2021 12:01:11      4 

h1 = dateshift(tt.Time(1),'start','hour')

h1 = datetime
   17-Mar-2021 02:00:00

N = height(tt);
Nwind = ceil(N/window)+1;
ntt = [];
for offset = 0:window-1
   r = retime(tt, h1 + hours(offset) + window .* hours(0:Nwind-1).', 'sum')
   ntt = [ntt;r];
end

r = 5×1 timetable
            Time            Var1
    ____________________    ____

    17-Mar-2021 02:00:00    -12 
    17-Mar-2021 05:00:00    -18 
    17-Mar-2021 08:00:00     -1 
    17-Mar-2021 11:00:00     -5 
    17-Mar-2021 14:00:00      0 

r = 5×1 timetable
            Time            Var1
    ____________________    ____

    17-Mar-2021 03:00:00     -6 
    17-Mar-2021 06:00:00    -13 
    17-Mar-2021 09:00:00    -13 
    17-Mar-2021 12:00:00      4 
    17-Mar-2021 15:00:00      0 

r = 5×1 timetable
            Time            Var1
    ____________________    ____

    17-Mar-2021 04:00:00    -11 
    17-Mar-2021 07:00:00    -12 
    17-Mar-2021 10:00:00     -3 
    17-Mar-2021 13:00:00      0 
    17-Mar-2021 16:00:00      0 

ntt = sortrows(ntt);
h2 = dateshift(tt.Time(end),'start', 'hour');
ntt = ntt(timerange(h1,h2),:);
ntt.Time = ntt.Time + hours(window-1);

ntt

ntt = 10×1 timetable
            Time            Var1
    ____________________    ____

    17-Mar-2021 04:00:00    -12 
    17-Mar-2021 05:00:00     -6 
    17-Mar-2021 06:00:00    -11 
    17-Mar-2021 07:00:00    -18 
    17-Mar-2021 08:00:00    -13 
    17-Mar-2021 09:00:00    -12 
    17-Mar-2021 10:00:00     -1 
    17-Mar-2021 11:00:00    -13 
    17-Mar-2021 12:00:00     -3 
    17-Mar-2021 13:00:00     -5 

[Peter Perkins]

I'm late to this party, but a couple of comments:

The answer to Walter's "Ah. You can pass in a vector of new times instead of using regular and hours(2)" is that retime(...,'regular', 'sum', 'timestep', hours(2)) basically says to itself, "Ha! They want a time step of two hours, so I'm going to put the bin edges at nice locations", and that means at 0 hours, 2 hours, etc. But you have always been able to pass in a time vector top retime to, and as Walter says, just retime to 0,2,4 and then to 1,3,5, and combine.

But really, I think you need to stick with timetables and just use smoothdata. I think this becomes a one-liner:

>> tt = timetable((1:10)',(11:20)','RowTimes',datetime(2021,12,7,0:9,0,0))
tt =
  10×2 timetable
            Time            Var1    Var2
    ____________________    ____    ____
    07-Dec-2021 00:00:00      1      11 
    07-Dec-2021 01:00:00      2      12 
    07-Dec-2021 02:00:00      3      13 
    07-Dec-2021 03:00:00      4      14 
    07-Dec-2021 04:00:00      5      15 
    07-Dec-2021 05:00:00      6      16 
    07-Dec-2021 06:00:00      7      17 
    07-Dec-2021 07:00:00      8      18 
    07-Dec-2021 08:00:00      9      19 
    07-Dec-2021 09:00:00     10      20 
>> smoothdata(tt,'movmean',[hours(1) 0]) % current and prev hour
ans =
  10×2 timetable
            Time            Var1    Var2
    ____________________    ____    ____
    07-Dec-2021 00:00:00      1       11
    07-Dec-2021 01:00:00    1.5     11.5
    07-Dec-2021 02:00:00    2.5     12.5
    07-Dec-2021 03:00:00    3.5     13.5
    07-Dec-2021 04:00:00    4.5     14.5
    07-Dec-2021 05:00:00    5.5     15.5
    07-Dec-2021 06:00:00    6.5     16.5
    07-Dec-2021 07:00:00    7.5     17.5
    07-Dec-2021 08:00:00    8.5     18.5
    07-Dec-2021 09:00:00    9.5     19.5
>> smoothdata(tt,'movmean',[hours(2) 0]) % current and prev two hours
ans =
  10×2 timetable
            Time            Var1    Var2
    ____________________    ____    ____
    07-Dec-2021 00:00:00      1       11
    07-Dec-2021 01:00:00    1.5     11.5
    07-Dec-2021 02:00:00      2       12
    07-Dec-2021 03:00:00      3       13
    07-Dec-2021 04:00:00      4       14
    07-Dec-2021 05:00:00      5       15
    07-Dec-2021 06:00:00      6       16
    07-Dec-2021 07:00:00      7       17
    07-Dec-2021 08:00:00      8       18
    07-Dec-2021 09:00:00      9       19

[Walter Roberson]

Ah! Though I think you would use 'movsum' rather than 'movmean' for the purpose of the original poster.