# Select values based on datetime

4 views (last 30 days)
stelios loizidis on 7 Apr 2023
Commented: stelios loizidis on 10 Apr 2023
Hello,
I have the matrix Values (1X2100) and the corresponding DateTime matrix with datetime data (1x2100 datetime). The values in Values matrix are hourly. I want to categorize the values of the Values table into 3 categories. I wrote the following codes for categorization,
[k1,i1]=find(Values<0);
[k2,i2]=find(0<=Values & Values<=80;
[k3,i3]=find(80<Values);
Based on the i1, i2 and i3 I created the following matrices,
Cat_Time_1=DateTime(i1);
Cat_Time_2=DateTime(i2);
Cat_Time_3=DateTime(i3);
Cat_Time_1 (1X30 datetime), Cat_Time_2 (1X290 datetime) and cat_Time_3 (1X1780 datetime)
But what happens is the following: For example, in Cat_Time_1 there is 04-Jan-2012 04:00 and in Cat_Time_2 there is 04-Jan-2012 16:00. Another example is in Cat_Time_2 there is 16-Jan-2012 07:00 and in Cat_Time_3 there is the 16-Jan-2012 02:00. What I want is the same dates that exist in Cat_Time_1 and Cat_Time_2 stay in Cat_Time_1 and leave Cat_Time_2. The same dates that exist in Cat_Time_1 and Cat_Time_3 should leave from cat_Time_3 and stay in Cat_Time_1, while the same dates that exist in Cat_time_2 and Cat_Time_3 should leave Cat_Time_2 and stay in Cat_Time_3.
After that, based on the dates that exist in each matrix (Cat_Time_1, Cat_Time_2 and Cat_Time_3), all the values must be selected from the Values matrix of prices that correspond to the date. (e.g. in Cat_Time_1 the first date is 04-Jan-2012. The first row of the cat1 matrix should contain the 24 values corresponding to this date. The same in the other cases). How are these done?

Your description and your code are contradictory. If you care about the date, why does your code use Values as the classification standard? If two datetimes are on the same day but one Value is 0 and the other is 100, how should they be classified?

Peter Perkins on 9 Apr 2023
The very first thing you should do is put your data and timestamps in a timetable.
Once you do that, use retime to create a daily timetable, using your own custom aggregation function that returns 1, 2, or 3 according to your rules ("the same dates that exist in Cat_Time_1 and Cat_Time_2 stay in Cat_Time_1 and leave Cat_Time_2 ...", which I don't really understand but you should be able to write that function.
Now you have a list of days and what category they are in. Use dateshift on your original timetable to add a new variable that is the day timetamp, use join to combine that with the daily timetable, and select data from tyhat using the category number.
The custom function aside, this is just a few lines of code. No loops needed.
stelios loizidis on 10 Apr 2023
Okay. It works! Thanks for the valuable help.

Hichem on 7 Apr 2023
To keep the common dates in one category, you can use the intersect function to find the common dates between two categories and remove them from the latter category.
Here's an example:
% Find common dates between Cat_Time_1 and Cat_Time_2
common_dates = intersect(Cat_Time_1, Cat_Time_2);
% Remove common dates from Cat_Time_2
Cat_Time_2 = setdiff(Cat_Time_2, common_dates);
% Find common dates between Cat_Time_1 and Cat_Time_3
common_dates = intersect(Cat_Time_1, Cat_Time_3);
% Remove common dates from Cat_Time_3
Cat_Time_3 = setdiff(Cat_Time_3, common_dates);
% Find common dates between Cat_Time_2 and Cat_Time_3
common_dates = intersect(Cat_Time_2, Cat_Time_3);
% Remove common dates from Cat_Time_3
Cat_Time_3 = setdiff(Cat_Time_3, common_dates);
To select the values corresponding to each category, you can use logical indexing on the Values matrix using the indices i1, i2, and i3 that you already have:
Cat_Values_1 = Values(i1);
Cat_Values_2 = Values(i2);
Cat_Values_3 = Values(i3);
Note that the sizes of Cat_Values_1, Cat_Values_2, and Cat_Values_3 will correspond to the number of elements in Cat_Time_1, Cat_Time_2, and Cat_Time_3, respectively.