4

I have two dataframes df1 and df2. I would like to check if id2 value exists in df2. If exists, fetch the rows in df1 with identical id1 values. I tried left join but it gives only the first row.

pd.merge(df1, df2.rename(columns={'id1':'id2'}), on='id2', how='left')

df1

id1     id2    id3
C45     AB     ZU
C45     ZO     RE
C67     RT     FG
C78     TZ     GH


df2

id1
AB
GH
ZU

Expected output is:

id1     id2    id3
C45     AB     ZU
C45     ZO     RE
rshar
  • 1,381
  • 10
  • 28

2 Answers2

3

I did it in 2 separate steps.

First, find all the items in df1.id1 which match df2.id1 and save them to lookup.

Then, find all the rows which are in lookup within df1.id1 and save them to final.

First,

# Import pandas library
import pandas as pd
import numpy as np
  
# initialize list of lists
data = [['C45', 'AB','ZU'], ['C45', 'ZO','RE'], ['C67', 'RT','FG'],['C78','TZ','GH']]
  
# Create the pandas DataFrame
df1 = pd.DataFrame(data, columns=['id1', 'id2', 'id3'])

# initialize list of lists
data = [['AB'], ['GH'], ['ZU']]
  
# Create the pandas DataFrame
df2 = pd.DataFrame(data, columns=['id1'])

lookup = df1[df1.id2.isin(df2.id1)].id1 # contains a series of the ids in df1.id2 that match df2.id1

final = df1[df1.id1.isin(lookup)]

final looks like this,

    id1     id2     id3
0   C45     AB      ZU
1   C45     ZO      RE
anarchy
  • 3,709
  • 2
  • 16
  • 48
0

Here we go, input:

df1 = pd.DataFrame([['C45','AB','ZU'],['C45','ZO','RE'],
   ['C67','RT','FG',],['C78','TZ','GH']], columns=['id1','id2','id3'])
df2 = pd.DataFrame(['AB','GH','ZU'], columns=['id1'])

the command:

df1[df1.id1.isin(df1.id1[df1.id2.isin(df2.id1)])]

output:

    id1 id2 id3
0   C45 AB  ZU
1   C45 ZO  RE

explanation, df1.id2.isin(df2.id1):

0     True
1    False
2    False
3    False

df1.id1[df1.id2.isin(df2.id1)]:

0    C45

df1.id1.isin(df1.id1[df1.id2.isin(df2.id1)]):

0     True
1     True
2    False
3    False
Zahar Chikishev
  • 59
  • 6