How to find value of one column in Dataframe in another Dataframe

Question

I have two dataframes namely:

df1:

col1     col2
A1         20
B1         22         
A2         23
B2         24

df2:

Column1     Column2
A1          20
A2          23
A3          25 
A4          28
B1          22
B2          24
B3          27
B4          33

Now, I want to return all rows from df2 having values of df1

Hence , output should be:

df2:

A1         20
B1         22         
A2         23
B2         24

harvpan · Answer 1 · 2018-07-18T14:13:34.907

0

You need to have same column names in order to perform a full inner merge.

df1.rename(columns=dict(zip(df1.columns, df2.columns))).merge(df2)

Output:

   Column1  Column2
0   A1       20
1   B1       22
2   A2       23
3   B2       24

edited Jul 18 '18 at 14:13

answered Jul 18 '18 at 14:06

harvpan

8,571
2
18
36

Would this work if the length of the two frames don't match. – Marcelo BD Jul 18 '18 at 14:09
Yes, it would work. @MarceloBD – harvpan Jul 18 '18 at 14:12

score 0 · Answer 2 · answered Jul 18 '18 at 14:10

You can using merge

df2.merge(df1,left_on=['Column1','Column2'],right_on=['col1','col2'],how='left').dropna()[df2.columns]
Out[446]: 
  Column1  Column2
0      A1       20
1      A2       23
4      B1       22
5      B2       24

Or using tuple with isin

df2[df2.apply(tuple,1).isin(df1.apply(tuple,1))]
Out[453]: 
  Column1  Column2
0      A1       20
1      A2       23
4      B1       22
5      B2       24

score 0 · Answer 3 · answered Jul 18 '18 at 14:22

You can use merge:

import pandas as pd
df1 = pd.DataFrame({'col1': ['A1', 'B1', 'A2', 'B2'], 'col2': [20, 22, 23, 24]})
df2 = pd.DataFrame({'Column1': ['A1', 'A2', 'A3', 'A4', 'B1', 'B2', 'B3', 'B4'], 'Column2': [20, 23, 25, 28, 22, 24, 27, 33]})
df3 = df1.merge(df2, left_on='col1', right_on='Column1', how='left')
df4 = df3[['col1','Column2']]
print(df4)
>    col1  Column2
  0   A1       20
  1   B1       22
  2   A2       23
  3   B2       24

How to find value of one column in Dataframe in another Dataframe

3 Answers3