Return Value Based on Conditional Lookup on Different Pandas DataFrame

Question

Objective: to lookup value from one data frame (conditionally) and place the results in a different dataframe with a new column name

df_1 = pd.DataFrame({'user_id': [1,2,1,4,5],
                    'name': ['abc','def','ghi','abc','abc'],
                    'rank': [6,7,8,9,10]})
df_2 = pd.DataFrame ({'user_id': [1,2,3,4,5]})

df_1 # original data

df_2 # new dataframe

In this general example, I am trying to create a new column named "priority_rank" and only fill "priority_rank" based on the conditional lookup against df_1, namely the following:

• user_id must match between df_1 and df_2
• I am interested in only df_1['name'] == 'abc' all else should be blank

df_2 should end up looking like this:

|user_id|priority_rank|
     1     6
     2     
     3
     4     9
     5     10

Answer 1

One way to do this:

In []:
df_2['priority_rank'] = np.where((df_1.name=='abc') & (df_1.user_id==df_2.user_id), df_1['rank'], '')
df_2

Out[]:
   user_id priority_rank
0        1             6
1        2              
2        3              
3        4             9
4        5            10

Note: In your example df_1.name=='abc' is a sufficient condition because all values for user_id are identical when df_1.name=='abc'. I'm assuming this is not always going to be the case.

Answer 2

Using merge

df_2.merge(df_1.loc[df_1.name=='abc',:],how='left').drop('name',1)
Out[932]: 
   user_id  rank
0        1   6.0
1        2   NaN
2        3   NaN
3        4   9.0
4        5  10.0