How can I search a value in two different dataframes and add it to a third one?

Question

I have a dataframe in which I want to find a client number that can be in two either of two different dataframes. My question is, how can I make it in a fast way, rather than merge both dataframes and after asking which one is null and not usefeul.

df0

ClientName     City         State
JohnSmith      Dallas         TX
JaneDoe        Dallas         TX
LucasAult      Dallas         TX
CarsonSeibert  Dallas         TX

df1 to be looked

ClientName     CLientNumber
JohnSmith      100
JaneDoe        101

df2 to be looked

ClientName     CLientNumber
LucasAult        102
CarsonSeibert    103

Expected Result

ClientName     City         State   ClientNumber
JohnSmith      Dallas         TX       100
JaneDoe        Dallas         TX       101
LucasAult      Dallas         TX       102
CarsonSeibert  Dallas         TX       103

so far my proposed solution is this

df0 = pd.merge(df0, df1, how ='left')
df0 = pd.merge(df0, df2, how ='left')

df0['ClientNumber'] = np.where(df0['ClientNumber'] == None, df1['ClientNumber'], df0['ClientNumber'])

You could concatenate `df1` and `df2` and do a single merge. — Quang Hoang, Sep 17 '19 at 14:28
also, `df0['ClientNumber'] == None` doesn't do any good, always returns `False`. Use `df0['ClientNumber'].isna()`. — Quang Hoang, Sep 17 '19 at 14:29
You can follow the section on how to merge multiple dataframes in the dupe — yatu, Sep 17 '19 at 14:30
Actually I was rereading that section and agree with you that the current approach of concat with merge is much simpler for this case @ALollz reopened — yatu, Sep 17 '19 at 14:39

score 3 · Answer 1 · answered Sep 17 '19 at 14:29

You can simply concat df1,df2 and then perform a merge with df0

df0.merge(pd.concat([df1,df2]), on='ClientName')

Output:

ClientName     City         State   ClientNumber
JohnSmith      Dallas         TX       100
JaneDoe        Dallas         TX       101
LucasAult      Dallas         TX       102
CarsonSeibert  Dallas         TX       103

score 2 · Answer 2 · answered Sep 17 '19 at 14:30

map is good enough with a join on one unique key:

d = {**df1.set_index('ClientName').CLientNumber, 
     **df2.set_index('ClientName').CLientNumber}

df0['ClientNumber'] = df0['ClientName'].map(d)

      ClientName    City State  ClientNumber
0      JohnSmith  Dallas    TX           100
1        JaneDoe  Dallas    TX           101
2      LucasAult  Dallas    TX           102
3  CarsonSeibert  Dallas    TX           103

How can I search a value in two different dataframes and add it to a third one?

2 Answers2