Pandas new column based on condition on two other dataframe-CodePudding

df1 and df2 are of different sizes. Set the df1(row1, 'Z') value to df2(row2, 'C') value when df1(row1, 'A') is equal to df2(row2, 'B').

What is the recommended way to implement df1['Z'] = df2['C'] if df1['A']==df2['B']?

CodePudding user response：

You can use numpy.where, passing the condition as df1.A equals df2.B, and for true boolean, take df2.C else take df1.Z:

np.where(df1.A.eq(df2.B), df2.C, df1.Z)

Assign above result to df1.Z

SAMPLE:

import pandas as pd
import numpy as np
df1 = pd.DataFrame({'A': np.random.randint(5,10,20), 'Z': np.random.randint(5,10,20)})
df2 = pd.DataFrame({'C': np.random.randint(5,10,20), 'B': np.random.randint(5,10,20)})

>>>df1.Z.values
Out[41]: array([7, 6, 7, 7, 6, 8, 9, 7, 6, 6, 7, 6, 8, 7, 8, 8, 9, 6, 7, 7])
>>> np.where(df1.A.eq(df2.B), df2.C, df1.Z)
Out[42]: array([7, 6, 6, 7, 6, 8, 9, 7, 6, 9, 7, 8, 8, 7, 8, 8, 9, 6, 7, 7])

CodePudding user response：

I would like to try map

df1['Z'] = df1['A'].map(dict(zip(df2['B'],df2['C'])))