CODEWITHSUNDEEP
pythonpandasdataframe
Ewdlam
Ewdlam

Reputation: 935

How to extract rows of a pandas dataframe according to conditions based on another dataframe

I have these two dataframes :

df1 = pd.DataFrame({'Points':[1,2,3,4,5], 'ColX':[9,8,7,6,5]})
df1
    Points  ColX
0        1     9
1        2     8
2        3     7
3        4     6
4        5     5

df2 = pd.DataFrame({'Points':[2,5], 'Sum':[-1,1], 'ColY':[2,4]}) # ColY does not matter, I just added it to say that this dataframe can have other columns that the useful columns for this topic
df2
    Points  Sum  ColY
0        2   -1     2
1        5    1     4

I would like to get a dataframe with the rows of df1 where :

Consequently, I would like to get this dataframe (no matter the index) :

    Points  ColX
4        5     5

I tried the following but it didn't work :

df1[df1.merge(df2, on = 'Points')['Sum'] <= 2 and ['Sum']>=0]

Could you please help me to find the right code ?

Upvotes: 3

Views: 1845

Answers (3)

jezrael
jezrael

Reputation: 863501

Use Series.between for boolean mask with boolean indexing for filtering passed to another mask with Series.isin:

df = df1[df1['Points'].isin(df2.loc[df2['Sum'].between(0,2), 'Points'])]
print (df)
   Points  ColX
4       5     5

Your solution should be changed with DataFrame.query for filtering:

df = df1.merge(df2, on = 'Points').query('0<=Sum<=2')[df1.columns]
print (df)
   Points  ColX
1       5     5

Upvotes: 1

storm_88
storm_88

Reputation: 92

also works:

df3 = df1.merge(df2, on='Points')
result = df3[(df3.Sum >= 0) & (df3.Sum <= 2)]
result

Upvotes: 1

Scott Boston
Scott Boston

Reputation: 153510

Try this:

df1[df1['Points'].isin(df2.query('0 <= Sum <= 2')['Points'])]

Output:

  Points  ColX
4       5     5

Explained:

  • df2.query('0 <= Sum <=2') to filter df2 first to only valid records
  • Then use boolean indexing with isin of filter df2 Points column.

Upvotes: 3

Related Questions