How to get a column from another dataframe based on condition (dataframes have different shape0

Question

I have two dataframes df1 and df2. I want to add a column "Layer" from dataframe df2 to my main dataframe df1 based on the depth intervals. Both dataframes have different shape. Could you please help me to understand how can I do in a most efficient way in pandas? Df3 is what is desired. I grouped first on index and then tried to wright a code, but cannot understand the logic which I should follow.

Df1:

Df2:

Df3:

Answer 1

Try with merge_asof:

df3 = (pd.merge_asof(df1, 
                    df2[["Layer", "Top_depth"]].sort_values("Top_depth"), 
                    left_on="Depth", 
                    right_on="Top_depth")
         .drop("Top_depth", 1))

>>> df3
   Index  Depth  Total Layer
0      A   2310  0.002  Urdu
1      A   2311  0.001  Urdu
2      A   2312  0.006  Urdu
3      A   2313  0.002  Urdu
4      A   2314  0.008  Urdu
5      A   2315  0.005  Sami
6      A   2316  0.004  Sami
7      A   2317  0.006  Sami
8      A   2318  0.007  Sami
9      A   2319  0.001  Sami
10     A   2320  0.002  Sami
11     A   2321  0.006  Sami
12     A   2322  0.009  Sami
13     A   2323  0.004  Sami
14     A   2324  0.005  Sami
15     A   2325  0.009  Sami
16     A   2326  0.001  Sami
17     A   2327  0.001  Sami
18     A   2328  0.002  Sami
19     A   2329  0.001  Sami
20     A   2330  0.001  Sami
21     A   2331  0.003  Sami
22     A   2332  0.006  Oslo
23     A   2333  0.005  Oslo
24     A   2334  0.004  Oslo
25     A   2335  0.002  Oslo
26     A   2336  0.003  Oslo
27     A   2337  0.001  Oslo
28     A   2338  0.001  Oslo