Merge into table from two matlab tables with different date/time format-CodePudding

I have two Matlab tables and want to merge into, but the time intervals are different. thanks in advance

Table TEST_1 with the time intervals of 3 hours and the key data DINDEX.

DUT                DINDEX    
2018/8/4           0.7   
2018/8/4 3:00:00   1.3   
2018/8/4 9:00:00   0.7   
2017/8/4 9:00:00   0.7   
2018/10/5          0     
2018/10/5 9:00:00  0.3   
2018/10/5 18:00:00 1     
2018/10/5 21:00:00 0.7   
2018/10/10         0.7

for example,

the first row show time range is between 2018/8/4 00:00:00 and 2018/8/4 3:00:00 with 3 hours time interval.

the second row show time range is between 2018/8/4 3:00:00 and 2018/8/4 6:00:00 with 3 hours time interval, and so forth.

DATE of Table TABLE_2 has no regular.

UT                                  KP  
2018/8/4 1:02:05.421000       
2018/8/4 4:02:06.921000                 
2018/8/4 9:02:08.421000       
2017/8/4 9:02:09.921000                 
2018/10/5 1:02:11.421000      
2018/10/5 10:02:14.421000               
2018/10/5 19:02:15.921000     
2018/10/5 21:02:17.421000               
2018/10/10 0:02:18.921000

And now, I want to merge into the field KP of Table TEST_2 using the key data DINDEX in Table TEST_1 by time, what I expect is:

UT                                  KP 
2018/8/4 1:02:05.421000             0.7
2018/8/4 4:02:06.921000             1.3
2018/8/4 9:02:08.421000             0.7
2017/8/4 9:02:09.921000             0.7
2018/10/5 1:02:11.421000            0  
2018/10/5 10:02:14.421000           0.3
2018/10/5 19:02:15.921000           1  
2018/10/5 21:02:17.421000           0.7
2018/10/10 0:02:18.921000           0.7

the first UT (2018/8/4 1:02:05.421000) is between 2018/8/4 00:00:00 and 2018/8/4 3:00:00, so the KP of the first UT is 0.7, corresponds to the first DINDEX.

CodePudding user response：

You can do this using the timetable operation retime. This allows you to "interpolate" time series data in a variety of ways. Here's a fabricated example that I think is somewhat similar to what you're describing:

First make some regularly-spaced data, put into a timetable

>> regularData = timetable(datetime(2018,8,4)   hours(0:6:48)', randi(10, 9, 1))
regularData =
  9x1 timetable
            Time            Var1
    ____________________    ____
    04-Aug-2018 00:00:00      9
    04-Aug-2018 06:00:00      6
    04-Aug-2018 12:00:00      6
    04-Aug-2018 18:00:00     10
    05-Aug-2018 00:00:00      3
    05-Aug-2018 06:00:00      8
    05-Aug-2018 12:00:00      8
    05-Aug-2018 18:00:00      4
    06-Aug-2018 00:00:00      6

These are the times that we wish to interpolate to

>> wantTimes = datetime(2018,8,4)   hours(sort(48 * rand(5,1)))
wantTimes =
  5x1 datetime array
   04-Aug-2018 02:35:22
   04-Aug-2018 03:38:27
   05-Aug-2018 01:28:41
   05-Aug-2018 13:24:00
   05-Aug-2018 20:49:57

Perform the interpolation using 'nearest' values.

>> retime(regularData, wantTimes, 'nearest')
ans =
  5x1 timetable
            Time            Var1
    ____________________    ____
    04-Aug-2018 02:35:22     9
    04-Aug-2018 03:38:27     6
    05-Aug-2018 01:28:41     3
    05-Aug-2018 13:24:00     8