2
\$\begingroup\$

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.

\$\endgroup\$
1
1
\$\begingroup\$

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

\$\endgroup\$
1
  • \$\begingroup\$ Those screenshots don't make the answer clearer. You can use to_clipboard to get a decent text representation \$\endgroup\$ – Maarten Fabré Jul 2 '20 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.