Column Mapping on Multi-columns

Question

Df1:

Country Sex Year Bin
NZ      M   2005  1
NZ      F   2005  1
NZ      M   2007  3
KR      F   2005  2

... Df2:

Country Sex Year Rate
NZ      M   2005  0.5
NZ      M   2006  0.4
NZ      F   2005  0.6
NZ      F   2006  0.3

... Desired Outcome:

Country Sex Year Bin Rate
NZ      M   2005  1   0.5
NZ      F   2005  1   0.6
NZ      M   2007  3   NaN
KR      F   2005  2   NaN

How do I join the dataframe of Df2 to achieve the desired outcome by matching the input on [Country, Sex and Year] of Df1. I've attempted merge/join but it seems to only recognize 1 column as the criteria for mapping and ended up joining all inputs together..

What do you get for `df1.merge(df2, how='left')` ? – jpp Apr 29 '18 at 09:39 — jpp, Apr 29 '18 at 09:39

score 1 · Accepted Answer · answered Apr 29 '18 at 09:45

1

Try below code

result = pd.merge(DF1, DF2, how='left', on=['Country', 'Sex','Year'])

answered Apr 29 '18 at 09:45

Gaurav

1,070
9
11

Thanks, wasn't aware of 'on', actually tried it but using double [[..]] for multiple columns.. which ended up in error. Thanks again! – Devin Lee Apr 29 '18 at 09:59
@DevinLee:- welcome!! – Gaurav Apr 29 '18 at 10:00

Column Mapping on Multi-columns

1 Answers1