Mapping columns in a pandas dataframe

Question

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

Naga kiran · Accepted Answer · 2019-02-02 12:36:19Z

1

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

edited Feb 2, 2019 at 12:36

answered Feb 2, 2019 at 12:11

Naga kiran

4,6071 gold badge21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user47467 Over a year ago

Cool, this works :) but how do I only append the answer, not the ID?

Naga kiran Over a year ago

Hi @user47467, I just incorporated the change :-)

user9787472 · Accepted Answer · 2019-02-02 12:12:06Z

0

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

answered Feb 2, 2019 at 12:12

user9787472

Collectives™ on Stack Overflow

Mapping columns in a pandas dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related