Compare two dataframes and keep the rows that overlap

user_12741159 0
Viewed 866

I have two dataframes: one with a single column(A) and another with two columns(B). I want to compare data frame A with a column of dataframe B (A contains the same type of data as the column in B and some of the rows overlap). Then I want to keep the rows of dataframe B that are overlapping.

Example:

A = pd.DataFrame([['Smile1'], ['Smile4'], ['Smile6']], columns=['Smiles'])

B = pd.DataFrame([[24, 'Smile1'], [33, 'Smile2'], [2, 'Smile3'], 
[85, 'Smile4'], [68, 'Smile5'], [102, 'Smile6']], columns=['ID', 'Smiles'])

In this example I would like to keep Smile1, 4 and 6 and their IDs, preferably create a new dataframe that contains those columns, like this:

C = pd.DataFrame([[24, 'Smile1'], [85, 'Smile4'], [102, 'Smile6']], columns=['ID', 'Smiles'])

My actual data frames are much bigger.

Thank you for your time!

pandas dataframe python
3 Answers
B[B["Smiles"].isin(A["Smiles"])]

Output:

Out[8]: 
    ID  Smiles
0   24  Smile1
3   85  Smile4
5  102  Smile6
user_32186930

Check with

C = B[B['Smiles'].isin(A['Smiles'])]
C
Out[73]: 
    ID  Smiles
0   24  Smile1
3   85  Smile4
5  102  Smile6
user_79645270

You can use pd.DataFrame.merge

C = pd.merge(B, A, on=['Smiles'])
C
    ID  Smiles
0   24  Smile1
1   85  Smile4
2  102  Smile6

Or With isin and reset_index would be

C = B[B.Smiles.isin(A.Smiles)].reset_index(drop=True)
user_60756990
Related