MATLAB Answers

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

조회 수: 1(최근 30일)
Émélie Gagnon
Émélie Gagnon 2020년 12월 11일
답변: Eric Sofen 2021년 1월 5일
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
Mathieu NOE
Mathieu NOE 2020년 12월 11일
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

댓글을 달려면 로그인하십시오.

답변(2개)

Jon
Jon 2020년 12월 11일
Put the two sets of data into MATLAB timetables, then use the synchronize function to combine them

Eric Sofen
Eric Sofen 2021년 1월 5일
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.

Community Treasure Hunt

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

Start Hunting!

Translated by