Pandas fill one dataframe by lookup in other dataframe

Question

I have two dataframe: df1 and df2

df1 = pd.DataFrame([10,22,30,22,10,60],columns=['Type'])
df2 = pd.DataFrame(["A","B","C","D","E"],columns=['Code'],index=[10,22,30,40,60])

I want to create a new column in df1 called 'Code', with the matching index of df2. Then, only if the Type > 50, I don't want to lookup the index of df2, but use Code== B instead.

Desired output looks like:

   Type Code 
0    10 A
1    22 B
2    30 C
3    22 B
4    10 A
5    60 B

Any idea how to do this?

Answer 1

Let us do

df1['Code'] = df1.Type.map(df2.Code).mask(df1['Type'].gt(50),'B') # update by anky_91
df1
Out[10]: 
   Type Code
0    10    A
1    22    B
2    30    C
3    22    B
4    10    A
5    60    B

Answer 2

You could use merge:

df1['Code'] = df1.merge(df2, left_on='Type', right_index=True)['Code']

It gives:

   Type Code
0    10    A
1    22    B
2    30    C
3    22    B
4    10    A
5    60    E