I have two dataframes that has a column with name and surname but in one of them is in different order, in the first one is name surname order and in the second one surname name order.
How can I do the merge by ignoring the order of the name column?
import pandas as pd
df1 = pd.DataFrame({'name':['Dominik Hull D',
'Lulu Castaneda',
'Zachary Pearce',
'Paul Lewis',
'Neave Potts',
'Ruth Callahan',
'Evelyn Haney W',
'Julie Mclaughlin',
'Kaleb Hardin',
'Kayleigh Little',
]})
df2 = pd.DataFrame({'name':['Mclaughlin Julie',
'Hardin Kaleb',
'Hull D Dominik',
'Castaneda Lulu',
'Callahan Ruth',
'Haney W Evelyn',
'Pearce Zachary',
'Lewis Paul',
'Potts Neave',
'Little Kayleigh',
], 'value':[0,1,2,3,4,5,6,7,8,9]})
new_df = pd.merge(df1,df2,on='name', how='left')
print(new_df)
>Solution :
Rearrange name column in df2 on the fly (with pd.Series.str.rsplit):
new_df = pd.merge(df1, df2.assign(name=df2['name'].
str.rsplit(n=1).str[::-1].str.join(' ')),
on='name', how='left')
name value
0 Dominik Hull D 2
1 Lulu Castaneda 3
2 Zachary Pearce 6
3 Paul Lewis 7
4 Neave Potts 8
5 Ruth Callahan 4
6 Evelyn Haney W 5
7 Julie Mclaughlin 0
8 Kaleb Hardin 1
9 Kayleigh Little 9