MATLAB Answers

How to join two datetime table datasets with closest times paired?

2 views (last 30 days)
I have two datasets of time series arrays one is timestamped GPS positioning time and lat long measuremnts (this array is approximately 72000 points) I also have a smaller array of time stamped ping times and their ping strength which have longer intervals thus, a smaller number of measuremnts (48000 points). I need to match them up as close as I can so I need to find closest times and link those up. The gaps are fine as they can just be nans I have tried interp1 and that just matches up what they find and throws out a column with the results but I need to have both columns still as I need to put the positioning in the correct place and time. I am unsure how to do that if someone is able to help please. I also converted both time series to datenum but I have them in datetime aswell. I have tried to join aswell as using ismembertol but i wasn't getting the output that I wanted, however, ismembertol worked well for matching my dates I am just unsure how to extract the matched dates and put them next to each other (whilst also retaining the other columns). I have tried a few different ways but have just spent a few hours failing.
This is what the positioning times look like:
'07-Sep-2020 08:06:27'
'07-Sep-2020 08:06:27'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:30'
'07-Sep-2020 08:06:30'
And here is a sample of the ping times:
'07-Sep-2020 08:06:27'
'07-Sep-2020 08:06:27'
'07-Sep-2020 08:06:27'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:28'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:29'
'07-Sep-2020 08:06:30'
'07-Sep-2020 08:06:30'
'07-Sep-2020 08:06:30'
'07-Sep-2020 08:06:30'
'07-Sep-2020 08:06:31'
Those just show the datetime column but I have positioning data following the columns in a second and third column with heights also and the ping data contains times aswell as ping correlation so I need to join the positioning to the seabed measuremnts. The output that I need is a big merged dataset of the correctly matched up times next to each other. The GPS data measuremnts are a lot more frequent (8 times a second) compared with the ping data (3 times a second) so gaps will be encountered which I would like to leave as nans. Furthermore, the version of matlab I am working with is 2021a so I feel like there is an easier way to do this and I may be overcomplicating things. I hope this makes sense, any help or suggestions or advice is very appreciated. They are both currently in separate tables format. Thank you in advance and thank you for taking the time to help its very appreciated!:)

Accepted Answer

Scott MacKenzie
Scott MacKenzie on 5 Aug 2021
Edited: Scott MacKenzie on 5 Aug 2021
TT1 = table2timetable(Dataset1); % 1st data set table with datetime column
TT2 = table2timetable(Dataset2); % 2nd data set table with datatime column
TT3 = sychronize(TT1, TT2); % merge (includes all columns from dataset1 and dataset2)

More Answers (0)

Products


Release

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by