Creating a daily date vector

Hello,

I hope this is a fairly easy question for the community. I have a half hourly datetime vector which I would like to convert to a daily datetime vector. I don't seem to be having any luck. There must be a simple code to do this? Any help would be much appreciated.

Here is an example of my data: date2017 (17520x1 datetime)

date2017 =

'01/01/2017 00:30:01'

'01/01/2017 01:00:01'

'01/01/2017 01:30:01'

'01/01/2017 02:00:01'

'01/01/2017 02:30:01'

'01/01/2017 03:00:01'

'01/01/2017 03:30:01'

'01/01/2017 04:00:01'

'01/01/2017 04:30:01'

'01/01/2017 05:00:01'

'01/01/2017 05:30:01'

'01/01/2017 06:00:01'

'01/01/2017 06:30:01'

'01/01/2017 07:00:01'

'01/01/2017 07:30:01'

'01/01/2017 08:00:01'

'01/01/2017 08:30:01'

'01/01/2017 09:00:01'

'01/01/2017 09:30:01'

'01/01/2017 10:00:01'

'01/01/2017 10:30:01'

'01/01/2017 11:00:01'

'01/01/2017 11:30:01'

'01/01/2017 12:00:01'

'01/01/2017 12:30:01'

'01/01/2017 13:00:01'

'01/01/2017 13:30:01'

'01/01/2017 14:00:01'

'01/01/2017 14:30:01'

I'd like to do this so that I can match it to daily mean values I have for temperature.

I tried this but something went wrong. I ended up with a DateDaily (367x0 timetable) vector, so no date values.

T = table(date2017, 'VariableNames' , {'Date'});

D = table2timetable(T);

DateDaily = retime(D, 'daily');

Thank you!

Answer 1

[Steven Lord]

Based on your stated goal, I'd probably store the data in a timetable array and then use retime to change it to a 'daily' timetable.

n = 15;
randomHourVector = randi(6, n, 1); % n random integers between 1 and 6
d = datetime('now') + hours(cumsum(randomHourVector));
x = (1:n).';
tt = timetable(d, x)

tt = 15×1 timetable
             d              x 
    ____________________    __

    29-Apr-2022 21:32:15     1
    30-Apr-2022 00:32:15     2
    30-Apr-2022 06:32:15     3
    30-Apr-2022 08:32:15     4
    30-Apr-2022 11:32:15     5
    30-Apr-2022 12:32:15     6
    30-Apr-2022 17:32:15     7
    30-Apr-2022 20:32:15     8
    01-May-2022 02:32:15     9
    01-May-2022 04:32:15    10
    01-May-2022 08:32:15    11
    01-May-2022 09:32:15    12
    01-May-2022 13:32:15    13
    01-May-2022 15:32:15    14
    01-May-2022 16:32:15    15

tt2 = retime(tt, 'daily', @mean)

tt2 = 3×1 timetable
         d         x 
    ___________    __

    29-Apr-2022     1
    30-Apr-2022     5
    01-May-2022    12

[Lejla Latifovic]

Thank you and I'm sorry, I'm not sure I follow. I was using timetable, is something off in my code?

T = table(date2017, 'VariableNames' , {'Date'});

D = table2timetable(T);

DateDaily = retime(D, 'daily');

[Steven Lord]

Please show us the outputs of these commands:

head(D)
head(DateDaily)

[Lejla Latifovic]

Here they are, something is clearly not working?

>> head(D)

ans =

8×0 empty timetable

>> head(DateDaily)

ans =

8×0 empty timetable

[Steven Lord]

Your table T has just the datetime data in it. table2timetable converts the first date or time variable from your table into the RowTimes of the timetable and converts all the other table variables into timetable variables. But you don't have any other table variables so the result is an empty timetable.

Put your datetime data and the associated data that you want to "match it to daily mean values I have for temperature" into the table T and convert that into a timetable. Note that tt in my example has both datetime data (the d variable) and non-datetime data (x.)

[Lejla Latifovic]

Okay, understood. That is all good and works. Appreciate your help! I still need to get the cumsum for the year (also working with precipitation, NEP values). However, when I try to use cumsum using the retime function I get an error "Unrecognized synchronization method: 'cumsum'." Is it the right command? Sum and Mean both work well.

T = table(date2017,NEP_17, 'VariableNames' , {'Date' 'NEP'});

D = table2timetable(T);

DateDaily = retime(D, 'daily' , 'cumsum');

>> head(D)

ans =

8×1 timetable

Date NEP

___________________ _________

01/01/2017 00:30:01 -0.01767

01/01/2017 01:00:01 -0.051263

01/01/2017 01:30:01 -0.040264

01/01/2017 02:00:01 -0.0512

01/01/2017 02:30:01 -0.031572

01/01/2017 03:00:01 -0.012442

01/01/2017 03:30:01 -0.026695

01/01/2017 04:00:01 -0.03076

[Steven Lord]

The output of cumsum is not going to be suitable for use as an aggregation method in retime. It doesn't reduce a vector of data down to a scalar value like sum and mean do. But if I understand what you want, you can do that using cumsum after the retime call.

n = 15;
randomHourVector = randi(6, n, 1); % n random integers between 1 and 6
d = datetime('now') + hours(cumsum(randomHourVector));
x = (1:n).';
tt = timetable(d, x)

tt = 15×1 timetable
             d              x 
    ____________________    __

    29-Apr-2022 22:57:36     1
    30-Apr-2022 00:57:36     2
    30-Apr-2022 03:57:36     3
    30-Apr-2022 06:57:36     4
    30-Apr-2022 08:57:36     5
    30-Apr-2022 11:57:36     6
    30-Apr-2022 13:57:36     7
    30-Apr-2022 16:57:36     8
    30-Apr-2022 19:57:36     9
    01-May-2022 00:57:36    10
    01-May-2022 03:57:36    11
    01-May-2022 05:57:36    12
    01-May-2022 09:57:36    13
    01-May-2022 11:57:36    14
    01-May-2022 15:57:36    15

tt2 = retime(tt, 'daily', @mean)

tt2 = 3×1 timetable
         d          x  
    ___________    ____

    29-Apr-2022       1
    30-Apr-2022     5.5
    01-May-2022    12.5

tt2.CumulativeSum = cumsum(tt2.x)

tt2 = 3×2 timetable
         d          x      CumulativeSum
    ___________    ____    _____________

    29-Apr-2022       1           1     
    30-Apr-2022     5.5         6.5     
    01-May-2022    12.5          19     

[Lejla Latifovic]

Yes, that is exactly it. Wonderful! Thank you for all of your help, much appreciated.