CODEWITHSUNDEEP
pythonpandas
Xingfang Lee
Xingfang Lee

Reputation: 63

Dataframe merging in Pandas

I have two dataframes. First (df1) contains Name, ID and PIN. Second contains Identifier, City and Country. Dataframe shown below. 

df1 = pd.DataFrame({"Name": ["Sam", "Ajay", "Lee", "Lee Yong Dae", "Cai Yun"], "ID": ["S01", "A01", "L02", "L03", "C01"], "PIN": ["SM392", "AA09", "Lee101", "Lee201", "C101"]})


df2 = pd.DataFrame({"Identifier": ["Sam", "L02", "C101"], "City": ["Moscow", "Seoul", "Beijing"], "Country": ["Russia", "Korea", "China"]})

I want to merge the dataframes if either name or ID or PIN matches with the identifier of df2. The expected output is: City Country Name PIN Student ID 0 Moscow Russia Sam SM392 S01 1 0 0 Ajay AA09 A01 2 Seoul Korea Lee Lee101 L02 3 0 0 Lee Yong Dae Lee201 L03 4 Beijing China Cai Yun C101 C01

Upvotes: 0

Views: 81

Answers (2)

Andy
Andy

Reputation: 181

This is perhaps not the most elegant solution, but it works for me. You have to create 3 separate merges and combine the results.

The code below gives the expected output (with nan values instead of 0 for the unmatched elements of the DataFrame)

import numpy as np
import pandas as pd

#Initial data
df1 = pd.DataFrame({"Name": ["Sam", "Ajay", "Lee", "Lee Yong Dae", "Cai Yun"], "ID": ["S01", "A01", "L02", "L03", "C01"], "PIN": ["SM392", "AA09", "Lee101", "Lee201","C101"]})

df2 = pd.DataFrame({"Identifier": ["Sam", "L02", "C101"], "City": ["Moscow", "Seoul", "Beijing"], "Country": ["Russia", "Korea", "China"]})

def merge_three(df1,df2):

    #Perform three seperate merges
    df3=df1.merge(df2, how='outer', left_on='ID', right_on='Identifier')
    df4=df1.merge(df2, how='outer', left_on='Name', right_on='Identifier')
    df5=df1.merge(df2, how='outer', left_on='PIN', right_on='Identifier')

    #Copy 2nd and 3rd merge results to df3
    df3['City_x']=df4['City']
    df3['Country_x']=df4['Country']

    df3['City_y']=df5['City']
    df3['Country_y']=df5['Country']

    #Merge the correct City and Country values. Use max to remove the NaN values
    df6=df3[['City','Country','Name','PIN','ID']]
    df6['City']=np.max([df3['City'],df3['City_x'],df3['City_y']],axis=0)
    df6['Country']=np.max([df3['Country'],df3['Country_x'],df3['Country_y']],axis=0)

    #Remove extra un-matched rows from merge
    df_final=df6[df6['Name'].notnull()]

    return df_final

df_out = merge_three(df1,df2)

Output:

df_out
      City Country          Name     PIN   ID
0   Moscow  Russia           Sam   SM392  S01
1      NaN     NaN          Ajay    AA09  A01
2    Seoul   Korea           Lee  Lee101  L02
3      NaN     NaN  Lee Yong Dae  Lee201  L03
4  Beijing   China       Cai Yun    C101  C01

Upvotes: 1

Andrey Lukyanenko
Andrey Lukyanenko

Reputation: 3851

Not sure, but maybe this is what you are looking for:

a = df1.merge(df2, left_on='ID', right_on='Identifier')
b = df1.merge(df2, left_on='Name', right_on='Identifier')
с = df1.merge(df2, left_on='PIN', right_on='Identifier')
df = a.append(b).append(с)
df
    ID  Name    PIN City    Country Identifier
0   L02 Lee Lee101  Seoul   Korea   L02
0   S01 Sam SM392   Moscow  Russia  Sam
0   C01 Cai Yun C101    Beijing China   C101

Upvotes: 0

Related Questions