How to use movsum with datetime?
7 views (last 30 days)
Show older comments
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.
0 Comments
Answers (2)
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
0 Comments
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.
14 Comments
Peter Perkins
on 7 Dec 2021
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
on 7 Dec 2021
Ah! Though I think you would use 'movsum' rather than 'movmean' for the purpose of the original poster.
See Also
Categories
Find more on Dates and Time in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!