How to combine two tables with measurements taken over the same range but different intervals?

I am trying to combine two log core datasets with different measurements types. One has the lithology as follows:

HOLE# START END LITHO

001ab 0 2 gray

001ab 2 4.67 red

002ab 4.67 35 gray

And the other the geotechnical data

HOLE# START END HARDNESS RQD

001ab 0 1.2 7 89

001ab 1.2 1.8 8 98

001ab 1.8 3.1 9 86

001ab 3.1 4.67 7 76

002ab 4.67 8 6 45

Is there a simple way to combine them based on coordinates (first 3 columns) in MATLAB resulting in something like this:

HOLE# START END LITHO HARDNESS RQD

001ab 0 1.2 gray 7 89

001ab 1.2 1.8 gray 8 98

001ab 1.8 2 gray 9 86

001ab 2 3.1 red 9 86

001ab 3.1 4.67 red 7 76

002ab 4.67 8 gray 6 45

002ab 8 35 gray ... ...

In reality, these tables consist of two Excel documents with over 40,000 entries. I looked into using join or synchronize but it doesn't apply because these aren't dates and the intervals don't overlap unless the HOLE# changes. My plan is to try using for loops and conditional statements but I thought I'd check if there is any simpler way.

Thank you in advance for your help!

[Mathieu NOE]

hello Emelie

I think you can load each data set by using readtable , and then easily combine the data together based on variable names

I will try to propose something

Answer 1

[Jon]

Put the two sets of data into MATLAB timetables, then use the synchronize function to combine them

Answer 2

[Eric Sofen]

This is a tricky problem. I agree that conceptually it seems like a join, but you don't have exact matches in start/end between datasets, so join won't work. You're right that if these datasets were a function of time rather than depth, you could use synchronize. Under the hood, synchronize uses interp1 plus some subscripting tricks to do a lot of the alignment of disparate timetables. You may be able to mock up a toy example versus time, and step through synchronize with those timetables to see how synchronize processes the data, and develop something similar based on depth. Essentially, you'll want to use interp1 to interpolate (not necessarily linear interpolation - likely "next" or "previous") one core onto the sampling of the other, then concatenate the result onto the other table. There may be extra trickiness because of the start/end depths of each sample - if there are gaps, you may do two rounds of interpolation, one with the start, one with the end, then screen out samples that fall in the gaps.