0

I have some depth-based geological data in pandas data frames, organised by drill hole name, depth from, and depth to, like this:

DDH_name From_ft To_ft ALT1
0 A-7CD5 0.0 90.0 20
1 A-7CD5 90.0 110.0 70
3 A-7CD5 110.0 150.0 7

I also have another dataframe with more detailed data, on 1ft intervals.

I need a faster way of resampling the "coarse" data (in this case, the 'ALT1' field) into the "fine" data frame, so that the data that exists in the first row of the coarse dataframe that goes from 0ft to 90ft gets painted over the first 90 rows of the fine dataframe when merging. The field looks numerical but is actually a discrete label, so I can't use any interpolation.

Here's the code I have that does the merging at present:

df['ALT1'] = ''

for i in df.index:
    w = df['DDH_name'].iloc[i]
    d = df['From_ft'].iloc[i]
    try:
        df['ALT1'][i] = df_alter[df_alter['DDH_name']==w][df_alter['From_ft']<=d][df_alter['To_ft']>d]['ALT1'].values[0]
    except:
        pass

This works and produces the desired result, but seems inefficient and takes a long time (up to 30 seconds if the "fine" dataframe has ~100k rows). Is there a better way to merge and resample from coarse intervals to fine ones?

EDIT: Expected final dataframe should look like this (taken just before and after the change in ALT1 value at 90ft):

DDH_name From_ft To_ft ALT1
85 A-7CD5 85.0 86.0 20
86 A-7CD5 86.0 87.0 20
87 A-7CD5 87.0 88.0 20
88 A-7CD5 88.0 89.0 20
89 A-7CD5 89.0 90.0 20
90 A-7CD5 90.0 91.0 70
91 A-7CD5 91.0 92.0 70
92 A-7CD5 92.0 93.0 70
93 A-7CD5 93.0 94.0 70
94 A-7CD5 94.0 95.0 70
95 A-7CD5 95.0 96.0 70
96 A-7CD5 96.0 97.0 70
GlenB
  • 1
  • 1
  • 1
    There's definitely a better and quicker way to do this, but it's hard to help without a [mcve]. Mind posting a sample of the second df, and the expected output ? – rafaelc May 16 '23 at 02:45
  • kindly add an expected output dataframe – sammywemmy May 16 '23 at 03:27
  • you should also include the `fine` dataframe – sammywemmy May 16 '23 at 03:45
  • Thanks team, have included the expected output now. The "fine" data frame is just the hole name and the depths at 1ft intervals and some chemical composition data, however only the hole name and depths are shown, because that's all that is common between data frames – GlenB May 16 '23 at 06:14

0 Answers0