Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Comparing 2 columns of two Python Pandas dataframes and getting the common rows

I have 2 Dataframe as follows:

DF1=
    A    B   C    D
0   AA   BA  KK   0
1   AD   BD  LL   0
2   AF   BF  MM   0

DF2=
    K    L
0   AA   BA
1   AD   BF
2   AF   BF

At the end what I want to get is:

DF1=
    A    B   C    D
0   AA   BA  KK   1
1   AD   BD  LL   0
2   AF   BF  MM   1

So, I want to compare two dataframe, I want to see which rows of first data frame (for column A and B) are in common of of second dataframe(Column K and L) and assign 1 on the coulmn D of first dataframe.

I can use for loop, but It will be very slow for large number of entries.

Any clue or suggestion will be appreciated.

like image 509
Mohammad Saifullah Avatar asked May 17 '15 19:05

Mohammad Saifullah


People also ask

How do you find common rows in two DataFrames Pandas?

Python Pandas – Find the common rows between two DataFrames with merge() To find the common rows between two DataFrames with merge(), use the parameter “how” as “inner” since it works like SQL Inner Join and this is what we want to achieve.


2 Answers

This would be easier if you renamed the columns of df2 and then you can compare row-wise:

In [35]:

df2.columns = ['A', 'B']
df2
Out[35]:
    A   B
0  AA  BA
1  AD  BF
2  AF  BF
In [38]:

df1['D'] = (df1[['A', 'B']] == df2).all(axis=1).astype(int)
df1
Out[38]:
    A   B   C  D
0  AA  BA  KK  1
1  AD  BD  LL  0
2  AF  BF  MM  1
like image 181
EdChum Avatar answered Sep 30 '22 12:09

EdChum


df1['ColumnName'].isin(df2['ColumnName']).value_counts()
like image 43
Vipul Saxena Avatar answered Sep 30 '22 14:09

Vipul Saxena