CODEWITHSUNDEEP
pythonpandas
user47467
user47467

Reputation: 1093

Mapping columns in a pandas dataframe

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

Answers (2)

Naga kiran
Naga kiran

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

user9787472
user9787472

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

Related Questions