MATLAB Answers

Problem with datetime and daily values

3 views (last 30 days)
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

Accepted Answer

Steve Eddins
Steve Eddins on 17 Mar 2021
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 Comments
Daphne PARLIARI
Daphne PARLIARI on 18 Mar 2021
Corrected my error above, using
data(data.DATE_UTC < datetime(2006,1,1, 'TimeZone', 'UTC'),:) = [];

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by