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

Answer 1

[Steve Eddins]

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)

[Daphne PARLIARI]

Great, it works! Thank you so much!

Can you please explain what does

TT = table2timetable(T(:,["DATE_UTC" "T"]));

and

TT_daily = retime(TT,'daily','mean');

do?

[Steve Eddins]

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]

And another question please:

TT_daily ends up with way more dates than should be (e.g. from 01/01/1999 instead of 01/01/2006).

Is there any way to fix it?

[Steve Eddins]

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]

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]

Corrected my error above, using

data(data.DATE_UTC < datetime(2006,1,1, 'TimeZone', 'UTC'),:) = [];