Extract value from pandas dataframe based on two condition

Question

I have two dataframe, df1 and df2

df1 = A B
      1 a
      1 
      1 5
      1 b
      1 c
      1 d


df2 = A B C
      1 a apple
      1  cherry
      1 5 apple
      1 b orange

I want to merge these two dataframes based on A and B column. My logic is the following:

if df1['A'][0] is in df2['A'] and df1['B'][0] is in df2['B'] and they are equal:

      then create new column df1['New Product'] = df2['C']

I need to create the third column in df1 if this condition is met.

I tried hard but without any success. I guess index position matters.

This is my solution which does not work:

df1['New Product'] = df2['C'][(df1['A'].isin(df2['A'])) & (df1['B'].isin(df2['B']))]

The expected output should be:

df1 = A B C
      1 a apple
      1  cherry
      1 5 apple
      1 b orange
      1 c nan
      1 d nan

You want a left `merge` for this, `df.merge(df2, on = ['A','B'], how = 'left')` — yatu, Feb 11 '19 at 10:29

Mohamed Thasin ah · Accepted Answer · 2019-02-11T10:31:29.227

1

try simple left join,

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

O/P:

   A  B New Product
0  1  a       apple
1  1         cherry
2  1  5       apple
3  1  b      orange
4  1  c            
5  1  d

edited Feb 11 '19 at 10:31

answered Feb 11 '19 at 10:27

Mohamed Thasin ah

10,754
11
52
111

Same here, when I perform this merge the length of new `df` is more than the length of original `df1` – Okroshiashvili Feb 11 '19 at 10:38
@NodarOkroshiashvili- it means that your df2 has duplicate values in key column, first remove that using df2=df2.drop_duplicates(subset=['A','B']) then perform the above code – Mohamed Thasin ah Feb 11 '19 at 10:45
1

Thanks, man, that perfectly works. I did this but seems I missed something – Okroshiashvili Feb 11 '19 at 11:17

score 1 · Answer 2 · answered Feb 11 '19 at 10:30

you need:

import pandas as pd

df1 = pd.DataFrame({'A':[1]*6, 'B':['a',None,5,'b','c','d']})
df2 = pd.DataFrame({'A':[1]*4, 'B':['a', None, 5, 'b'], 'C':['apple','cherry','apple','orange']})

df = df1.merge(df2, how='left', on=['A','B'])
print(df)

Output:

 A     B       C                                                                                                                    
0  1     a   apple                                                                                                                    
1  1  None  cherry                                                                                                                    
2  1     5   apple                                                                                                                    
3  1     b  orange                                                                                                                    
4  1     c     NaN                                                                                                                    
5  1     d     NaN

Seems to work but after this the length of new `df` increases but I do not need this — Okroshiashvili, Feb 11 '19 at 10:35

Extract value from pandas dataframe based on two condition

2 Answers2