Problem with datetime and daily values
显示 更早的评论
Hello guys! Could you please give me a hand on this?
I have the attached file which contains 5 columns:
1st- Date
2nd-Time
3rd-Datetime (combine from 1st and 2nd, in local time)
4th-T (temperature values)
5th-DATE_UTC (3rd column turned to UTC instead of local time)
What I want to do is get daily temperature values according to column DATE_UTC.
I usually do something like this but this time it doesn't work:
DTindex = datenum(data.DATE_UTC);
lim = min(DTindex);
Hindd=DTindex-lim+1;
Daily_T = accumarray(Hindd, data.T, [], @nanmean );
The problem is that DTindex turns out screwed up but I cannot figoure out why...
Are there any ideas?
PS. I am on MatLab 2019b
采纳的回答
Steve Eddins
2021-3-17
Use a timetable and the retime function.
>> T = readtable("data.xlsx");
>> head(T)
ans =
8×5 table
Date Time Datetime T DATE_UTC
___________ _______ ____________________ ____ ____________________
13-Jan-2006 0.54167 13-Jan-2006 13:00:00 2.35 13-Jan-2006 11:00:00
13-Jan-2006 0.58333 13-Jan-2006 14:00:00 3.36 13-Jan-2006 12:00:00
13-Jan-2006 0.625 13-Jan-2006 15:00:00 3.2 13-Jan-2006 13:00:00
13-Jan-2006 0.75 13-Jan-2006 18:00:00 2.16 13-Jan-2006 16:00:00
13-Jan-2006 0.79167 13-Jan-2006 19:00:00 2.64 13-Jan-2006 17:00:00
13-Jan-2006 0.83333 13-Jan-2006 20:00:00 1.73 13-Jan-2006 18:00:00
13-Jan-2006 0.875 13-Jan-2006 21:00:00 1.54 13-Jan-2006 19:00:00
13-Jan-2006 0.91667 13-Jan-2006 22:00:00 1.24 13-Jan-2006 20:00:00
>> TT = table2timetable(T(:,["DATE_UTC" "T"]));
>> head(TT)
ans =
8×1 timetable
DATE_UTC T
____________________ ____
13-Jan-2006 11:00:00 2.35
13-Jan-2006 12:00:00 3.36
13-Jan-2006 13:00:00 3.2
13-Jan-2006 16:00:00 2.16
13-Jan-2006 17:00:00 2.64
13-Jan-2006 18:00:00 1.73
13-Jan-2006 19:00:00 1.54
13-Jan-2006 20:00:00 1.24
>> TT_daily = retime(TT,'daily','mean');
>> head(TT_daily)
ans =
8×1 timetable
DATE_UTC T
___________ ___
27-Mar-2002 NaN
28-Mar-2002 NaN
29-Mar-2002 NaN
30-Mar-2002 NaN
31-Mar-2002 NaN
01-Apr-2002 NaN
02-Apr-2002 NaN
03-Apr-2002 NaN
Note that the "mean" aggregation method ignores NaNs in the retime function, but apparently you have some days for which all the measurements are recorded as NaN.
>> plot(TT_daily.DATE_UTC,TT_daily.T)
6 个评论
Daphne PARLIARI
2021-3-17
Steve Eddins
2021-3-17
The expression T(:,["DATE_UTC" "T"]) creates a new table from T that contains only the table variables DATE_UTC and T.
The function table2timetable converts a table to a timetable. A timetable is similar to a table, and it contains one special variable containing timestamps. If the input to table2timetable contains a variable containing datetime values, then that variable is automatically used as the timestamp variable in the timetable.
The retime function produces a new timetable with the timestamps resampled to a specific unit of time: daily, hourly, yearly, etc. The third input to retime specifies how multiple timetable rows are to be combined. So, retime(TT,'daily','mean') creates a new timetable with one row per day. If the input timetable has more than one row in a day, the rows are combined using the mean.
Daphne PARLIARI
2021-3-17
Steve Eddins
2021-3-17
When I run my code above, the earliest date in TT_daily is in March 2002. The dates go back to 2002 because of row 6764 in your spreadsheet (or row 6763 in T), which has the date 3/28/02.
You could either clean up the bad data in the spreadsheet, or you could clean up the timetable, TT, with something like this:
TT(TT.DATE_UTC < datetime(2006,1,1),:) = [];
If you're still getting dates in 1999 with the retime function, double-check your code carefully. If you can't find the problem, then copy-paste the exact code you are using here, and I'll try running it.
Daphne PARLIARI
2021-3-18
Daphne PARLIARI
2021-3-18
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Timetables 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
