Locate indices of datetime from one table in another?

4 views (last 30 days)
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

Accepted Answer

Peter Perkins
Peter Perkins on 4 Mar 2022
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)
  1 Comment
BR
BR on 7 Mar 2022
Edited: BR on 7 Mar 2022
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

Sign in to comment.

More Answers (1)

KSSV
KSSV on 4 Mar 2022
Read about ismember, ismembertol.
  2 Comments
BR
BR on 4 Mar 2022
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
KSSV on 4 Mar 2022
You can convert them into same class..whats the problem?

Sign in to comment.

Categories

Find more on Data Type Identification 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!