CODEWITHSUNDEEP
pythonpandasnumpylookup
flying_fluid_four
flying_fluid_four

Reputation: 764

Perform a vectorized lookup when using pandas dataframes/series

I have a [main] dataframe that looks like:

ID  VAR1    VAR2    
1   23      45      
2   12      21      
3   98      45

and a [lookup] Dataframe that looks like:

VAR1_L  VAR2_L  VAL_L
23      45      ABC
21      21      PPC
12      21      XYZ

I need the result [main] with an added column like:

ID  VAR1    VAR2    VAL_L
1   23      45      ABC     
2   98      45      .
3   12      21      XYZ

I know I can use the pd.merge to achieve this. Is there any other vectorized way of achieving this lookup (perhaps in numpy)? I need to run this logic on 10 million rows of data.

Upvotes: 0

Views: 39

Answers (1)

harpan
harpan

Reputation: 8631

You need to perform a left merge.

df1.merge(df2, left_on=['VAR1', 'VAR2'], right_on=['VAR1_L', 'VAR2_L'], how='left').drop(['VAR1_L', 'VAR2_L'],1)

#OR

#df1.merge(df2.rename(columns={'VAR1_L':'VAR1', 'VAR2_L':'VAR2'}), on=['VAR1', 'VAR2'], how='left')


   ID      VAR1   VAR2  VAL_L
0   1        23     45    ABC
1   2        12     21    XYZ
2   3        98     45    NaN

Upvotes: 1

Related Questions