Pandas - select all rows between two values when a string is a match

user_11611283 0
Viewed 251

I have two DataFrames:

import pandas as pd
import numpy as np
d = {'fruit': ['apple', 'pear', 'peach'] * 5, 'values': np.random.randint(0,1000,15)}
df = pd.DataFrame(data=d)

d2 = {'fruit': ['apple', 'pear', 'peach'] * 2, 'min': [43, 196, 143, 174, 510, 450], 'max': [120, 310, 311, 563, 549, 582]}
df2 = pd.DataFrame(data=d2)

I'd like to select all the rows in df with matching fruit to df2 and values between min and max.

I'm trying something like:

df.loc[df['fruit'].isin(df2['fruit'])].loc[df['values'].between(df2['min'], df2['max'])]

But predictably this is returning a ValueError: Can only compare identically-labeled Series objects.

EDIT: you'll notice that fruit is repeated in df2. This is intentional. I still am trying to grab the rows between min and max as above, but I don't just want to collapse the fruits and take the rows between the absolute min and max.

For example, in df1 where fruit == 'apple' I'd like all the rows with values between 43-120 and 174-563.

pandas python
1 Answers
df3 = df.merge(df2, on='fruit', how='inner') # Thanks for Henry Ecker for suggesting inner join
df3 = df3.loc[(df3['min'] < df3['values']) & (df3['max'] > df3['values'])]
df3

Output

    fruit   values  min max
3   apple   883     467 947
6   apple   805     467 947
9   apple   932     467 947
11  peach   331     307 618
12  apple   665     467 947

If we don't want min and max col in output

df3 = df3.drop(columns=['min', 'max'])
df3

Output

    fruit   values
3   apple   883
6   apple   805
9   apple   932
11  peach   331
12  apple   665
user_21069340
Related