Pandas filter dataframe on multiple columns wrt corresponding column values from another dataframe

Question

I have a pandas dataframe, df1:

a b c d e f
1 1 1 x 1 5
1 1 1 x 1 6
1 1 1 y 1 5
1 1 1 y 1 7

and another dataframe, df2:

a b c d e f
1 1 1 x 1 5

Now I want to filter df1 on columns a, b, c, d if it is present in respective columns of df2. This is what I tried:

mask = df1['a'].isin(df2['a']) & df1['b'].isin(df2['b']) & df1['c'].isin(df2['c']) & df1['d'].isin(df2['d'])
df_new = df1[mask]

so df_new finally contains:

a b c d e f
1 1 1 x 1 5
1 1 1 x 1 6

I am looking for a better approach as there are a lot of columns to compare and a lot of rows, both in df1 and df2.

Answer 1

You need to merge dataframes like done here in Pandas Merge. You also need to read on dataframe joins which is a common topic in learning databases.Here I have not done any tweaking with indices i.e, whether to keep left ones or right ones but you can check the docs for better information on here Merge Docs Pydata

import pandas as pd

columns = "a b c d e f".split()
data = '''1 1 1 x 1 5
1 1 1 x 1 6
1 1 1 y 1 5
1 1 1 y 1 7'''.split("\n")

data = list(map(lambda x:x.split(), data ))

---

left = pd.DataFrame(columns = columns, data=data)



    a   b   c   d   e   f
0   1   1   1   x   1   5
1   1   1   1   x   1   6
2   1   1   1   y   1   5
3   1   1   1   y   1   7

---

right = pd.DataFrame(data = ["1 1 1 x 1 5".split()], columns=columns)


    a   b   c   d   e   f
0   1   1   1   x   1   5

---

pd.merge(left, right, how="right", on=["a", "b", "c", "d"])

    a   b   c   d   e_x f_x e_y f_y
0   1   1   1   x   1   5   1   5
1   1   1   1   x   1   6   1   5

---

pd.merge(left, right, how="right", on=["a", "b", "c", "d"], suffixes=["", "_"] ).drop(["e_", "f_"], axis=1)


    a   b   c   d   e   f
0   1   1   1   x   1   5
1   1   1   1   x   1   6

---