Problem with datetime and daily values

8 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
When I try
TT(TT.DATE_UTC < datetime(2006,1,1),:) = [];
I get
Cannot combine or compare a datetime array with a time zone with one without a time zone...
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)

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!