Indexing a timetable using time

Dear

I have a timetable in a range of many days in seconds, see figure below. I need to indexing this timetable in a range of hour and minutes for all range of dates. To do that i used this code:

Dates_ind = Universo_MidPrice.DateTime >=datetime('10:00:00.000',"Format","HH:mm:ss.SSS") & Universo_MidPrice.DateTime < datetime('16:50:00.000',"Format","HH:mm:ss.SSS");

After that, I will use this logical vector to indexing the timetable. This did not work. The problem is the absence of date in the datetime function once this code works if you include only a range of dates. However, the aim is indexing all dates in the range of hours and minutes. Any help will be appreciate.

Answer 1

[Star Strider]

One option may be findgroups —

DateTime = (datetime('2022-02-12') : minutes(30) : datetime('2022-02-18')).';
Universo_MidPrice = table(DateTime, randn(size(DateTime)), randn(size(DateTime)), randn(size(DateTime)))

Universo_MidPrice = 289×4 table
          DateTime             Var2         Var3        Var4  
    ____________________    __________    ________    ________

    12-Feb-2022 00:00:00      -0.26009     0.37457     -1.6895
    12-Feb-2022 00:30:00       0.94363     0.30367     -1.0551
    12-Feb-2022 01:00:00       0.73106     0.54439    -0.44869
    12-Feb-2022 01:30:00    0.00050895     -0.6857     -1.7172
    12-Feb-2022 02:00:00       0.99595    -0.73168     -1.7563
    12-Feb-2022 02:30:00       0.59001    -0.45604     0.76968
    12-Feb-2022 03:00:00       0.85465    -0.24803    -0.26923
    12-Feb-2022 03:30:00       0.67185      1.0021     0.04371
    12-Feb-2022 04:00:00       0.35326     0.50226    -0.78448
    12-Feb-2022 04:30:00      -0.88073    -0.22449     -1.9487
    12-Feb-2022 05:00:00      -0.56076    -0.51367      1.5368
    12-Feb-2022 05:30:00       -0.6697    -0.35228     0.44245
    12-Feb-2022 06:00:00      -0.85416      1.1535    0.083761
    12-Feb-2022 06:30:00       -0.6104    -0.54791    -0.84248
    12-Feb-2022 07:00:00       -1.7337     0.50155     0.39231
    12-Feb-2022 07:30:00      -0.47918     -2.4689     0.57241

[G,ID] = findgroups(hour(Universo_MidPrice.DateTime), minute(Universo_MidPrice.DateTime))   % Index By 'hour' & 'minute'

G = 289×1

1 2 3 4 5 6 7 8 9 10

ID = 48×1

0 0 1 1 2 2 3 3 4 4

Check = unique(G)

Check = 48×1

1 2 3 4 5 6 7 8 9 10

OutTest_1 = Universo_MidPrice(G==2,:)

OutTest_1 = 6×4 table
          DateTime           Var2       Var3        Var4   
    ____________________    _______    _______    _________

    12-Feb-2022 00:30:00    0.94363    0.30367      -1.0551
    13-Feb-2022 00:30:00    0.67361    0.73177       1.4942
    14-Feb-2022 00:30:00    0.50965    0.20715     -0.54938
    15-Feb-2022 00:30:00    0.13079     -1.154      0.53995
    16-Feb-2022 00:30:00    0.69907     2.4382    -0.037897
    17-Feb-2022 00:30:00    -2.0525     1.8627       1.1165

OutTest_2 = Universo_MidPrice(G==10,:)

OutTest_2 = 6×4 table
          DateTime            Var2        Var3        Var4   
    ____________________    ________    ________    _________

    12-Feb-2022 04:30:00    -0.88073    -0.22449      -1.9487
    13-Feb-2022 04:30:00     0.32262    -0.01214    -0.070054
    14-Feb-2022 04:30:00     0.42971      1.4757     -0.46184
    15-Feb-2022 04:30:00     -1.7657      1.7573      0.76109
    16-Feb-2022 04:30:00     -1.2849     -1.5435      0.85655
    17-Feb-2022 04:30:00      0.2469      1.9542     -0.45876

There are 289 rows in ‘Universo_MidPrice’ and 48 unique values for the grouping index ‘G’ and the output tests ‘OutTest’ demonstrate the indexing result.

.

[Victor Oliveira]

This works! Thanks

[Star Strider]

As always, my pleasure!

I also thought about this a bit more and came up with an easier way to determine a specific group.

For example, to get the group at 12:30, this would work —

[G,IDH,IDM] = findgroups(hour(Universo_MidPrice.DateTime), minute(Universo_MidPrice.DateTime)); % Index By 'hour' & 'minute'

Gfun = @(Hq,Mq,IDH,IDM) G((Hq == IDH) & (Mq == IDM)); % Group Index Function

Gq = Gfun(12,30,IDH,IDM); % Call Function

OutTest_3 = Universo_MidPrice(G==Gq,:) % Retrieve Data

OutTest_3 =

6×4 table

DateTime Var2 Var3 Var4

____________________ ________ ________ _______

12-Feb-2022 12:30:00 -0.62213 -0.54984 -0.407

13-Feb-2022 12:30:00 -0.96357 -0.34282 1.9107

14-Feb-2022 12:30:00 -0.47125 0.11991 0.91968

15-Feb-2022 12:30:00 0.5523 -0.89608 0.42213

16-Feb-2022 12:30:00 -0.51851 1.8942 0.11464

17-Feb-2022 12:30:00 -0.86003 0.55424 1.3727

This might make the table a bit easier to work with.

.