Filter data based on conditions from a dataframe

I have two dataframe(s): df1:

  | from id | from group | to id | to group   |
  |    1    |      A     |   3   |      B     |
  |    4    |      B     |   4   |      X     | 
  |    5    |      F     |   5   |      J     |
  |    2    |      B     |   3   |      A     |

df2:

   | From | To |
   |   A  |  B |
   |   F  |  J |

I want to filter out the values in df2, if present in the ‘from group’ and ‘to group’ columns of df1

Expected output:

     | from id | from group | to id | to group   |
     |    4    |      B     |   4   |      X     | 
     |    2    |      B     |   3   |      A     |

I am looking for a flexible solution. A solution that would not change if there are changes to the values in the df2 columns.

Answer

You can use .merge with indicator=True, then filter the df1:

x = df1[
    df1.merge(
        df2,
        left_on=["from group", "to group"],
        right_on=["From", "To"],
        indicator=True,
        how="left",
    )._merge.eq("left_only")
]
print(x)

Prints:

   from id from group  to id to group
1        4          B      4        X
3        2          B      3        A