CODEWITHSUNDEEP
pythonpandasmatch
lorenzo
lorenzo

Reputation: 407

matching 2 dfs on multiple conditions

I am trying to get the corresponding value of df1 from df2 based on 2 conditions. here are the 2 dfs. df1:

    name value1
0   abc  3      
1   cde  1      
2   cde  4      
3   ijk  6

df2:

    name value1 value2
0   abc  3      5
1   cde  3      5
2   cde  1      1
3   ijk  2      3

I want to get in df1 the df2.value2 for which the name matches and for which df1.value1.between(df2.value1, df2.value2) is verified

I have tried:

df1['value2'] = df2[(df2['name']==df1['name'])(df1['value1'].between(df2['value1']., df2['value2']))]['value2']

but without luck.. do you guys have any suggestion?

here you see my expected output on df1:

    name value1 value2
0   abc  3      5
1   cde  1      1
2   cde  4      5
3   ijk  6      nan

Upvotes: 1

Views: 301

Answers (2)

jezrael
jezrael

Reputation: 862851

You need merge both columns together and then filter by between, if necessary missing values for non matched values add merge with left join:

print (df1)
  name  value1  col
0  abc       3    1
1  cde       1    5
2  cde       4    7
3  ijk       6    3


df = df1.merge(df2, on='name', how='outer', suffixes=('','_'))

df = df[df.value1.between(df.value1_, df.value2)]
df = df1.merge(df[df2.columns], how='left')
print (df)
  name  value1  col  value2
0  abc       3   10     5.0
1  cde       1   50     1.0
2  cde       4   70     5.0
3  ijk       6   30     NaN

Upvotes: 1

Loochie
Loochie

Reputation: 2472

Also you can use numpy.where():

df1['value2'] =np.where(df1.value1.between(df2.value1, df2.value2), df2['value2'], np.nan)

Upvotes: 0

Related Questions