How to filter dataframe by values from columns in other dataframe thats different size in python?

user_16494816 0
Viewed 67

Hey i am trying to filter dataframe df2 by values in dataframe df1 in columns 'Currency' and 'Type'.

df1:

Currency   Type   Amount

USD        Buy    13003,00
EUR        Sell   920,00

df2:

Currency   Type   Amount
USD        Buy    21414,00
USD        Sell   56236,00
USD        Sell   15151,00
PLN        Buy    1235,00
EUR        Sell   951,00
EUR        Buy    1451,00
EUR        Buy    961,00

I want to filter df2 so it will drop rows that have the same Currency and opposite Type in df1. I am looking to get result like this:

Currency   Type   Amount
USD        Sell   56236,00
USD        Sell   15151,00
EUR        Buy    1451,00
EUR        Buy    961,00
pandas series dataframe filter python
1 Answers

One way to do it (though I'm not sure if it's the easiest):

df3 = pd.merge(df2, df1[['Currency', 'Type']], how='left', on=['Currency']).dropna()
df3 = df3[df3['Type_x'] != df3['Type_y']]
del df3['Type_y']

And in the end you can rename the Column 'Type_x' if you need to.

user_164954950
Related