CODEWITHSUNDEEP
pythonpandasdataframe
Oiko
Oiko

Reputation: 139

Pandas: New column from conditions and from another data frame

I have two data frames, df1 and df2.

df1                df2
A   B              C   D   E
ad  df             ad  se  1
ad  se             xc  je  2
xc  je             ad  df  3
...                ...

I need to create a new column in df1 with the values contained in column E from df2. For this, I need to find the matching values between columns A and C, and from B and D from df1 and df2 respectively.

where A == C & B == D 
      XX = E

The result should be like this:

df1                
A   B   XX         
ad  df  3          
ad  se  1          
xc  je  2         
...

This might be straightforward but I'm quite new to pandas and haven't really found a way to accomplish this.

Any advice is very welcome!

Upvotes: 2

Views: 159

Answers (3)

Safwan Samsudeen
Safwan Samsudeen

Reputation: 1707

You can merge both the dataframes, based on the columns you mentioned.

>>> result = df1.merge(df2, left_on=['A', 'B'], right_on=['C', 'D'])
>>> result
    A   B   C   D   E
0   ad  df  ad  df  3
1   ad  se  ad  se  1
2   xc  je  xc  je  2

And drop the columns C and D.

result.drop(columns=['C', 'D'], inplace=True)

Else, a more elegant solution. Rename the columns from C and D to A and B, and you can just perform the merge.

>>> result = df1.merge(df2.rename(columns={'C': 'A', 'D': 'B'}))
>>> result
    A   B   E
0   ad  df  3
1   ad  se  1
2   xc  je  2

```py

Upvotes: 2

geoffrey
geoffrey

Reputation: 106

I think what you want can be accomplished with a simple call to the pd.merge or df.merge.

Minimal example

import pandas as pd
                                                                                       
df1 = pd.DataFrame({'A': ['ad', 'ad', 'xc'], 'B': ['df', 'se', 'je']})
df2 = pd.DataFrame({'C': ['ad', 'xc', 'ad'], 'D': ['se', 'je', 'df'], 'E': [1, 2, 3]})
                                                                                       
                                                                                       
                                                                                       
df1.merge(df2, left_on=['A', 'B'], right_on=['C', 'D'])

Output

    A   B   C   D  E
0  ad  df  ad  df  3
1  ad  se  ad  se  1
2  xc  je  xc  je  2

Upvotes: 2

Andreas
Andreas

Reputation: 9197

try this:

pd.merge(df1, df2.rename(columns={'C':'A', 'D':'B'}), on=['A', 'B'], how='left')

Upvotes: 5

Related Questions