Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Merge two Dataframe based on Column that contains name and surname but in different order

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)

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading