How to filter a dataframe based on a list threshold values

Question

Dataframe A-

target  query.   score
SDOIII  a          92.8
SDOII   a          72.8
SoxH    a          66
SDOIII  b          67
LbpA1   b          18
SoxH    b          12
SoxH    a          7
..............

Dataframe B (thresholds)

target        threshold

SDOIII           4
SDOII            5
SoxH             6
LbpA1            7
.................

dataframe B has many target variables with their thresholds.

I wish to filter dataframe A based on scores >= the threshold scores based on DataframeB. Can anyone guide me with a code in pyhton/R ?

I tried filtering multiple conditions but not feasible for so many conditions or scores

Answer 1

You can use something like this:

filter_dict=dict(zip(df2.target,df2.threshold))
#{'SDOIII': 4, 'SDOII': 5, 'SoxH': 6, 'LbpA1': 7}

def check(target,score):
    if target in list(filter_dict.keys()):
        if score >= filter_dict[target]:
            return True
        else:
            return False
    else:
        return None

df['check']=df[['target','score']].apply(lambda x: check(x['target'], x['score']),axis=1)

Output:

    target  query   score   check
0   SDOIII  a       92.8    True
1   SDOII   a       72.8    True
2   SoxH    a       66.0    True
3   SDOIII  b       67.0    True
4   LbpA1   b       18.0    True
5   SoxH    b       12.0    True
6   SoxH    a       7.0     True

#now you can select only True values
#df[df['check']==True]