CODEWITHSUNDEEP
pythonpandasdataframe
user14269252
user14269252

Reputation: 450

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

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

Upvotes: 0

Views: 58

Answers (2)

Sure13
Sure13

Reputation: 41

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)

Upvotes: 0

Anurag Dabas
Anurag Dabas

Reputation: 24304

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

Upvotes: 1

Related Questions