Add a column in second DataFrame based on 2 matched column in 2 DataFrame

Question

I have 2 DataFrame, I want to add a column in second DataFrame based on multiple (In my case 2) matched column of both DataFrame I tried the code below, but I don't get the right answer. Can anyone help me please?


result = result.merge(out[out['COL1.']!=''].drop(['month'], axis=1), on=['COL1.'], how='left').merge(out[out['month']!=''].drop(['COL1.'], axis=1), on=['month'], how='left')

df1:

    COL1.       count   month
1   Plassen     1293    4
2   Adamstuen   567     4
3   AHO.        1799    5
4   Akersgata   2418    4

df2 :

    station     month
1   Plassen     4
2   Adamstuen   4
3   AHO.        5
4   Akersgata.  6

What I want is :

    station     month.  count 
1   Plassen     4.       1293
2   Adamstuen   4.       567
3   AHO.        5.       1799

Anurag Dabas · Accepted Answer · 2021-03-31 07:22:13Z

1

Use merge() method and chain drop() method to it:

result=df2.merge(df1,right_on=['COL1.','month'],left_on=['station','month']).drop(columns=['COL1.'])

Now if you print result you will get your desired output:

    station     month   count
0   Plassen     4       1293
1   Adamstuen   4       567
2   AHO.        5       1799

answered Mar 31, 2021 at 7:22

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

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

Comments

Sure13 · Accepted Answer · 2021-03-31 07:30:39Z

0

In this case,df.join() method is better than df.merge() method

df2.rename(columns={'station':'COL1.'},inplace=True)
df1.set_index(['COL1.','month'],inplace=True)
df2.set_index(['COL1.','month'],inplace=True)
df2.join(df1)

answered Mar 31, 2021 at 7:30

Sure13

413 bronze badges

1 Comment

Anurag Dabas Over a year ago

and how it is better?can you please tell

Collectives™ on Stack Overflow

Add a column in second DataFrame based on 2 matched column in 2 DataFrame

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related