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

Question

Yurii Iotov 2019-7-17

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/472206-gettind-the-indexes-of-table-array-with-time-and-data-according-to-datetime-vector-with-time-or-how

评论： dpb 2019-7-19

采纳的回答： dpb

data.mat

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

2 个评论
显示无隐藏无

dpb 2019-7-18

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 2019-7-18

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 2019-7-18

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/472206-gettind-the-indexes-of-table-array-with-time-and-data-according-to-datetime-vector-with-time-or-how#answer_383788

编辑：dpb 2019-7-18

在 MATLAB Online 中打开

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...

3 个评论
显示 1更早的评论隐藏 1更早的评论

Yurii Iotov 2019-7-19

编辑：Yurii Iotov 2019-7-19

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 2019-7-19

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 2019-7-18

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/472206-gettind-the-indexes-of-table-array-with-time-and-data-according-to-datetime-vector-with-time-or-how#answer_383756

编辑：Yurii Iotov 2019-7-18

在 MATLAB Online 中打开

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(:)]