Can I use two keys from one of the data sets and one key from another dataset when I merge two data sets?

user19562955 0
Viewed 26

I would like to have an index for countries. But I have two columns of country names. One column is for the origin of the FDI and the other one is for the destination of the FDI.

origin destination FDI
US UK 120
ITA US 90
TR SPA 40

This is the other data set I will use.

Country Index
ITA 0
UK 1
TR 0
SPA 1

Should I merge the latest data set two times with the first one changing the key for each time. Or there is a better way of doing that?

pandas
1 Answers

The expected output is unclear, but you can map as many columns as you want:

mapper = df2.set_index('Country')['Index']

df1[['new_origin', 'new_destination']] = (df1[['origin', 'destination']]
                                          .apply(lambda s: s.map(mapper))
                                          )

Or with join:

out = df1.join(df1.drop(columns='FDI')
                  .apply(lambda s: s.map(mapper))
                  .add_prefix('new_'))

output:

  origin destination  FDI  new_origin  new_destination
0     US          UK  120         NaN              1.0
1    ITA          US   90         0.0              NaN
2     TR         SPA   40         0.0              1.0
mozway0
Related