MATLAB Answers

How to use movsum with datetime?

4 views (last 30 days)
SaaraL
SaaraL on 15 Mar 2021
Answered: Seth Furman on 19 Mar 2021
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.

Answers (2)

Seth Furman
Seth Furman on 19 Mar 2021
@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.
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
>> 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
>> 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

Walter Roberson
Walter Roberson on 15 Mar 2021
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.
  12 Comments
Walter Roberson
Walter Roberson on 17 Mar 2021
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

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!