Locate indices of datetime from one table in another?

Hello,

I have to loacte indices in the date column in a table that looks like this (Table 1)

from a table that looks like (Table 2)

Given that the Table 2 (day_time_date) has categorical values whereas the Table 1 has datetime values. The reason it is categorical is because I have used

groupsummary

to summarise the dates from Table 1 which removed the timestamps as well. Thanks

Answer 1

[Peter Perkins]

1) It looks like you must have use "day" as an input to groupsummary, which returns a categorical as the group values:

>> tt = timetable(datetime(2022,3,randi([1 3],10,1)),rand(10,1))
tt =
  10×1 timetable
            Time              Var1  
    ____________________    ________
    01-Mar-2022 00:09:43     0.45054
    01-Mar-2022 00:47:39    0.083821
    02-Mar-2022 00:18:40     0.22898
    03-Mar-2022 00:31:42     0.91334
    03-Mar-2022 00:09:56     0.15238
    01-Mar-2022 00:36:07     0.82582
    02-Mar-2022 00:15:46     0.53834
    02-Mar-2022 00:39:14     0.99613
    01-Mar-2022 00:41:21    0.078176
    02-Mar-2022 00:44:53     0.44268
>> groupsummary(tt,"Time","day","mean")
ans =
  3×3 table
     day_Time      GroupCount    mean_Var1
    ___________    __________    _________
    01-Mar-2022        4          0.35959 
    02-Mar-2022        4          0.55153 
    03-Mar-2022        2          0.53286 
>> class(ans.day_Time)
ans =
    'categorical'

Categorical is what those "convenience" flags like "day" do. But you don't have to use that. The simplest way to get the group values as datetimes is like this:

>> tt.Day = dateshift(tt.Time,'start','day')
tt =
  10×2 timetable
            Time              Var1          Day    
    ____________________    ________    ___________
    01-Mar-2022 00:09:43     0.45054    01-Mar-2022
    01-Mar-2022 00:47:39    0.083821    01-Mar-2022
    02-Mar-2022 00:18:40     0.22898    02-Mar-2022
    03-Mar-2022 00:31:42     0.91334    03-Mar-2022
    03-Mar-2022 00:09:56     0.15238    03-Mar-2022
    01-Mar-2022 00:36:07     0.82582    01-Mar-2022
    02-Mar-2022 00:15:46     0.53834    02-Mar-2022
    02-Mar-2022 00:39:14     0.99613    02-Mar-2022
    01-Mar-2022 00:41:21    0.078176    01-Mar-2022
    02-Mar-2022 00:44:53     0.44268    02-Mar-2022
>> groupsummary(tt,"Day","mean")
ans =
  3×3 table
        Day        GroupCount    mean_Var1
    ___________    __________    _________
    01-Mar-2022        4          0.35959 
    02-Mar-2022        4          0.55153 
    03-Mar-2022        2          0.53286 
>> class(ans.Day)
ans =
    'datetime'

2) I'm confused. None of the "dates" in day_time_date appear in time_date.

Your question might be, "which rows of time_date correspond to each row of day_time_date?" That's easy, once you have tt.Day:

ismember(tt.Day,day_time_date)

[BR]

Incredible, very keen insights. Much appreciated and I do apologise for my late gratefulness. :-)

To answer your confusion, I agree they aren't very clear but first few dates are there from day_time_date that appear in time_date (08-Jun-2020/ 09-Jun-2020). But, as you clearly shown me the solution in your first point saves the day using ismember then. Thanks

Answer 2

[KSSV]

Read about ismember, ismembertol.

[BR]

Hi,

Thank you for your answer. However, I tried ismember aleady - where both variables have to be from the same class type and sme for ismembertol?

[KSSV]

You can convert them into same class..whats the problem?