CODEWITHSUNDEEP
pythonpandasnumpy
Fernando Delgado
Fernando Delgado

Reputation: 53

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

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'])

Upvotes: 0

Views: 60

Answers (2)

ALollz
ALollz

Reputation: 59549

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

Upvotes: 2

harpan
harpan

Reputation: 8631

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

Upvotes: 3

Related Questions