dleal dleal - 1 year ago 78
Python Question

compare whether value in one column is between two values in another column python pandas

I have two data frames as follow:

A = pd.DataFrame({"value":[3, 7, 5 ,18,23,27,21,29]})

B = pd.DataFrame({"low":[1, 6, 11 ,16,21,26], "high":[5,10,15,20,25,30], "name":["one","two","three","four","five", "six"]})

I want to find whether "value" in A is between 'high' and low' in B, and if so, I want to copy the column name from B to A.

The output should look like this:

A = pd.DataFrame({"value":[3, 7, 5 ,18,23,27,21,29], "name":["one","two","one","four","five", "six", "five", "six"]})

My function uses iterrows as follows:

def func1(row):
x = row['value']
for index,value in B.iterrows():
if ((value['low'] <= x) &(x<=value['high'])):
return value['name']

But it doesn't yet achieve what i want to do,

thank you,

Answer Source

You can use a list comprehension to iterate through the values in A, and then use loc to get the relevant mapped values. le is less than or equal to, and ge is greater than or equal to.

For example, v = 3 in the first row. Using simple boolean indexing:

>>> B[(B['low'].le(v)) & (B['high'].ge(v))]
   high  low name
0     5    1  one

Assuming that DataFrame B does not have any overlapping ranges, then you will get back one row as above. One then uses loc to get the name column, as below. Because each returned name is a a series, you need get the first and only scalar value (using iat, for example).

A['name'] = [B.loc[(B['low'].le(v)) & (B['high'].ge(v)), 'name'].iat[0] 
             for v in A['value']]

>>> A
   value  name
0      3   one
1      7   two
2      5   one
3     18  four
4     23  five
5     27   six
6     21  five
7     29   six
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download