How to combine two tables with measurements taken over the same range but different intervals?
3 views (last 30 days)
Show older comments
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!
1 Comment
Mathieu NOE
on 11 Dec 2020
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
Answers (2)
Jon
on 11 Dec 2020
Put the two sets of data into MATLAB timetables, then use the synchronize function to combine them
0 Comments
Eric Sofen
on 5 Jan 2021
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.
0 Comments
See Also
Categories
Find more on Tables in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!