join and synchronize two dataset with non uniform intervals

Hi,

I am a new user of Matlab.

I need to joind the two datase DB1 and DB2 using the datetime of DB1 to create a new DB2 that has average values based on the datetime of DB1 (see attached files).

I tried to use retime function but I do not understand how to prepare the NewTimes vector as required.

"TT2 = retime(TT1,newTimes,method) adjusts the timetable variables data to the time vector newTimes, using the method specified by method. The newTimes time vector can be irregular, but it must be a sorted datetime or duration vector and contain unique values. The times in newTimes become the row times of TT2."

Any suggestion?

Thanks in advance

Alessandro

Answer 1

[Cris LaPierre]

It looks like there are 409 unique times in DB1. I would use groupsummary to create your summary table with groups defined by the time intervals from DB1, and the area info in DB2. That appears to result in 250 groupings

load DB1.mat
load DB2.mat

tmInterval = unique(DB1.datetime(~ismissing(DB1.datetime)));
newDB2 = groupsummary(DB2,"DateTime",tmInterval,'mean')

newDB2 = 250×3 table
                   disc_DateTime                    GroupCount    mean_Area
    ____________________________________________    __________    _________

    [11-May-2020 02:00:00, 11-May-2020 19:00:00)       677          35355  
    [11-May-2020 19:00:00, 12-May-2020 07:30:00)       724          35544  
    [12-May-2020 07:30:00, 12-May-2020 13:30:00)       348          36998  
    [12-May-2020 13:30:00, 12-May-2020 23:30:00)       584          41495  
    [12-May-2020 23:30:00, 13-May-2020 00:30:00)        57          34594  
    [13-May-2020 00:30:00, 13-May-2020 07:30:00)       397          40518  
    [13-May-2020 07:30:00, 13-May-2020 08:30:00)        58          36761  
    [13-May-2020 08:30:00, 13-May-2020 10:30:00)       112          40409  
    [13-May-2020 10:30:00, 13-May-2020 14:30:00)       221          38129  
    [13-May-2020 14:30:00, 13-May-2020 18:30:00)       231          37736  
    [13-May-2020 18:30:00, 13-May-2020 21:00:00)       139          38177  
    [13-May-2020 21:00:00, 14-May-2020 13:30:00)       950          38706  
    [14-May-2020 13:30:00, 15-May-2020 01:30:00)       684          38598  
    [15-May-2020 01:30:00, 15-May-2020 12:00:00)       588          34026  
    [15-May-2020 12:00:00, 15-May-2020 19:30:00)       433          39419  
    [15-May-2020 19:30:00, 16-May-2020 05:30:00)       570          35978  

If you would like to have a row for each of the 409 times, you could try this approach using retime. Just note that I have not independently verfied that the mean areas are correct.

DB1TT = table2timetable(DB1);
DB2TT = table2timetable(DB2);

newTimes = unique(DB1TT.datetime(~ismissing(DB1TT.datetime)));
TT2 = retime(DB2TT,newTimes,'mean')

TT2 = 409×1 timetable
          DateTime          Area 
    ____________________    _____

    11-May-2020 02:00:00    35355
    11-May-2020 19:00:00    35544
    12-May-2020 07:30:00    36998
    12-May-2020 13:30:00    41495
    12-May-2020 23:30:00    34594
    13-May-2020 00:30:00    40518
    13-May-2020 07:30:00    36761
    13-May-2020 08:30:00    40409
    13-May-2020 10:30:00    38129
    13-May-2020 14:30:00    37736
    13-May-2020 18:30:00    38177
    13-May-2020 21:00:00    38706
    14-May-2020 13:30:00    38598
    15-May-2020 01:30:00    34026
    15-May-2020 12:00:00    39419
    15-May-2020 19:30:00    35978

[ALESSANDRO D'ALESSANDRO]

Great!

thanks a lot Cris. I will check by myself if means are correct, but reasonably it will works.