Reputation: 1093
I'm trying to map 2 columns in a dataframe based on another dataframe.
The first dataframe, df1, has the following structure:
ID1 ID2 check_ID
1 jason becky 1
2 becky tina 1
3 becky joe 1
4 jason joe 2
5 jason becky 2
The second dataframe, df2, has the following structure:
ID check_ID answer
1 jason 1 yes
2 becky 1 yes
3 tina 1 no
4 joe 1 yes
5 jason 2 no
6 joe 2 no
7 becky 2 no
The output I'm looking for is:
ID1 ID2 check_ID answer_ID1 answer_ID2
1 jason becky 1 yes yes
2 becky tina 1 yes no
3 becky joe 1 yes yes
4 jason joe 2 no no
5 jason becky 2 no no
So that answer_ID1 corresponds to ID1 and check_ID in df2, and likewise, answer_ID2 corresponds to ID2 and check_ID.
What is the best way of doing this? I don't quite understand the difference between map and apply, or whether I should replace..
Thanks in advance
Upvotes: 1
Views: 68
Reputation: 4607
you can use merge with inner join on dataframe columns
df.merge(df1,left_on=['ID1','check_ID'],right_on=['ID','check_ID'],how='inner')
** Edit**
df.merge(df1.rename(columns={'ID':'ID1'}),left_on=['ID1','check_ID'],right_on=['ID1','check_ID'],how='inner')
Out:
ID1 ID2 check_ID answer
0 jason becky 1 yes
1 becky tina 1 yes
2 becky joe 1 yes
3 jason joe 2 no
4 jason becky 2 no
Upvotes: 1
Reputation:
you must join them like this
df1.set_index('key').join(df2.set_index('key'))
that key in df1 present ID1 and key in df2 present ID
Upvotes: 0