Create a new column in data frame based on the range found in another column and a common column-CodePudding

I'm trying to create a new column in a data frame based on values from another data frame.

df1 is,

Name    Depth
A   100
A   120
B   200

df2 is,

Name    Start_Depth End_Depth   Zone
A   50  150 Zone1
A   150 200 Zone2
B   50  120 Zone3
B   120 300 Zone4

I want to add Zone column in df1, based on two conditions,

"Name" should match in both data frames
df1.Depth should be between Start_Depth and End_Depth in df2 for same "Name"

Output df1,

Name    Depth   Zone
A   100 Zone1
A   120 Zone1
B   200 Zone4

CodePudding user response：

Use df.merge with df.query:

In [120]: r = df1.merge(df2).query('End_Depth >= Depth > Start_Depth')[['Name', 'Depth', 'Zone']]

In [121]: r
Out[121]: 
  Name  Depth   Zone
0    A    100  Zone1
2    A    120  Zone1
5    B    200  Zone4

OR use Series.between:

In [114]: x = df1.merge(df2)
In [124]: r = x[x.Depth.between(x.Start_Depth, x.End_Depth)][['Name', 'Depth', 'Zone']]

In [125]: r
Out[125]: 
  Name  Depth   Zone
0    A    100  Zone1
2    A    120  Zone1
5    B    200  Zone4