Pandas - Swap Column value to another from a different dataset

Question

I have a list of Football Team names

Names = pd.DataFrame({
         'Name': ['Adelaide', 'Central Coast','Perth' ],       
    })

I would like to search through another dateset and find the name and swap it with another column

Nameswap = pd.DataFrame({
         'Namecheck': ['Adelaide', 'Brisbane Roar', 'Central Coast','Melbourne City', 'Melbourne Victory', 'Newcastle Jets','Perth' ],       
    'BFName': ['Adelaide United', 'Brisbane Roar', 'Central Coast Mariners','Melbourne City', 'Melbourne Victory', 'Newcastle Jets','Perth Glory' ],    
    })

My desired output would be

NewNames = pd.DataFrame({
         'Name': ['Adelaide United', 'Central Coast Mariners','Perth Glory' ],       
    })

I have tired the following but I believe because the index is not equal in length it wont work. Is there a way round this or a different way I should be looking at.

key_list = list(Names['Name'])
dict_lookup = dict(zip(Nameswap['Name'], Nameswap['BFName']))
df_clean['Home'] = [dict_lookup[item] for item in key_list]

you can map `Names['Name'].map(dict(Nameswap[['Namecheck','BFName']].to_numpy()))` or same but `replace` instead of `map` — anky, Jun 19 '20 at 16:46

score 1 · Accepted Answer · answered Jun 19 '20 at 16:50

1

You can use merge here:

df = Names.merge(Nameswap, left_on=['Name'], right_on=['Namecheck'])
print(df[['BFName']])

                   BFName
0         Adelaide United
1  Central Coast Mariners
2             Perth Glory

answered Jun 19 '20 at 16:50

NYC Coder

7,424
2
11
24

Pandas - Swap Column value to another from a different dataset

1 Answers1