Gettind the indexes of table array with time and data according to datetime vector with time. Or how to syncronize datetime vector with table array

Hi

I am very confused by datetime vector in Matlab and can not understand completely how I can solve my problem:

I have datetime vector with time:

and I have a table array with time and corresponding data:

I need to get a vector of indixes of the table array which correspond to time from datetime vector. For example to get smth like this (If you do manually):

I tried to transform datetime array to a timetable and the use syncronize function but I didnot manage to do it. Tried to use find function but stil does not work.

Thank you!

These two arrays are attached

[dpb]

I don't follow how the second vector is supposed to be related to the first???

What, specifically, are you trying to do?

[Yurii Iotov]

First vector - datetime vector with time (21*1), second array - table(295*10) with data measured at specific time (column two).

I need to acces the indexes to get data later from table array according to time at datetime vector and to have a vector with indixes (look at the last picture with number 207, 207 ...

So for example first time value at datetime vector is 15:14 this corresponds to the time 15:14 at table array at row # 207 so we save this index at vector, next one is olso 15:14 and svae 207 and so on...

Answer 1

[dpb]

OK, with the code to see what it was you were really trying to do it's easier...

First, there's an issue with your table that makes using the datetime more difficult...note the result of the following (firstly, I shortened the table variable name to just t for brevity):

> t.Time.Format='default';
>> t.Time(1:5)
ans = 
  5×1 datetime array
   31-Dec-1899 11:48:00
   31-Dec-1899 11:49:00
   31-Dec-1899 11:50:00
   31-Dec-1899 11:51:00
   31-Dec-1899 11:52:00
>> 

NB: the actual datetime value isn't the same date as the search date so nothing will match. This probably is a major reason you had no success with retime or other attempts to try to use the datetime values. Since inspection reveals all dates are the same day in the date column, I used it to create a new variable in the table that is the correct datetime for each as:

t.time=datetime(2019,7,9,hour(t.Time),minute(t.Time),second(t.Time));

To keep the two separate I used the lowercase time instead of Time; in reality I'd fix the Time variable in the table (or, better yet, create it initially with proper date and time).

Then, to get your result of the location of the closest prior time (I also named the lookup time series dt for less typing, I'm lazy...),

ix=interp1(t.time,1:height(t),dt,'previous')
ix =
   207
   207
   207
   206
   206
   205
   205
   205
   204
   204
   204
   204
   204
   205
   205
   205
   206
   206
   207
   207
   207
>>

The nearest in absolute time differential generally returned the subsequent interval (but not always, there are a few such as the third location that are same):

>> interp1(t.time,1:height(t),dt,'nearest')
ans =
   208
   208
   207
   207
   207
   206
   205
   205
   205
   205
   204
   205
   205
   205
   205
   206
   207
   207
   207
   208
   208
>> 

You can choose whichever scheme you wish that best suits your needs...

[dpb]

The key item to note from the above is that the output format which defines the display of a datetime variable does NOT affect the content of the variable itself; just because one only sees HH:MM doesn't mean there aren't days of the year and seconds or even fractions thereof in the actual value.

Secondly, a datetime does not exist without an associated date with a time (and vice versa). This is entirely different behavior than the venerable datenum in which the date part was the integer portion of the value and the time a fraction of a day the fractional part. Thus one could have either without the other; that is just not possible with a datetime object--the class simply doesn't have such a representation.

If one wants just time, one must use a duration, not a datetime.

[Yurii Iotov]

Thanks! I understood what was the problem! Your one line solution is beautiful.

And the problem was in exporting data from excel which has two separate cells of date and time, like 09-07-19 11:48 and it does not recognise cell with date as a date. The solution is to have one cell with date and time which is recognisible by excell, then importing it to matlab gives and recognizes as a datetime value.

[dpb]

Or, import the time-only field from Excel as a duration instead of datetime. That would require an import options object to explcitly define the variable type

Answer 2

[Yurii Iotov]

The solution is:

Maybe it will help someone!

If there is more fast or shorter way (more "beautiful") working with datetime vectror, please show!

tTarget = table_with_time_and_data{:,2};
tSource = datetime_vector_with_time;

rowIndexes = zeros(size(tSource));
rowIndexes_1 = rowIndexes;
for k = 1:length(rowIndexes)
    [~,bestHourIndex] = min(abs(hour(tTarget)-hour(tSource(k))));
    currentRowIndexes = find(hour(tTarget) == hour(tTarget(bestHourIndex)));
    subset = tTarget(currentRowIndexes);
    [~,bestMinuteIndex] = min(abs(minute(subset)-minute(tSource(k))));
    rowIndexes(k) = currentRowIndexes(bestMinuteIndex);
    
    %Alternative Solution
    Hour_currentRowIndexes_1 = find(hour(tTarget) == hour(tSource(k)));
    Minute_currentRowIndex_2 = find(minute(tTarget(Hour_currentRowIndexes_1)) == minute(tSource(k)));
    rowIndexes_1(k) = Hour_currentRowIndexes_1(Minute_currentRowIndex_2);

end
[tTarget(rowIndexes), tSource, tTarget(rowIndexes_1)]
[rowIndexes(:)-rowIndexes_1(:)]