Locate indices of datetime from one table in another?
3 views (last 30 days)
Show older comments
Hello,
I have to loacte indices in the date column in a table that looks like this (Table 1)![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/914239/image.png)
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/914239/image.png)
from a table that looks like (Table 2) ![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/914244/image.png)
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/914244/image.png)
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
0 Comments
Accepted Answer
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)
More Answers (1)
See Also
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!