Update the column based on two different size dataframe and update the value in first dataframe. (Concept of vlookup in python)

user_13866248 0
Viewed 68

I understand this seems like a very generic and duplicated question but I was unable to find the most relevant solution for my problem.

For example, I have below dataframe (df1):

receipt  car     parts
101      A       oil
102      B       tire
103      B       oil      
104      C       glass

Another df2:

product    name    criteria
Valvoline  oil     winter
Valvoline  oil     performance
Valvoline  oil     regular
Goodyear   tire    regular
Goodyear   tire    performance
Bosch      glass   regular

expected dataframe is:

receipt  car     parts   product
101      A       oil     Valvoline
102      B       tire    Goodyear
103      B       oil     Valvoline      
104      C       glass   Bosch

I did try merge but the total length in df1 was changed.

data_copy = pd.merge(df1, df2, how= "left", left_on = 'parts', right_on = 'name' )

I was thinking to create a unique list from df2 and the join df1. But is there any other elegant way.

Thanks

pandas dataframe merge python
2 Answers

Try this

df['criteria'] = df1.parts.map(df2.drop_duplicates('name')
                                  .set_index('name')['product'])

Or:

df['criteria'] =  df1.parts.map(dict(df2[['name', 'product']].to_numpy()))

Out[216]:
   receipt car  parts   criteria
0      101   A    oil  Valvoline
1      102   B   tire   Goodyear
2      103   B    oil  Valvoline
3      104   C  glass      Bosch
user_101892140

@Andy's method is better for populating single column , something like this if using merge

pd.merge(df1, 
       df2[['name','product']].drop_duplicates().rename(columns={"name":"parts"}),
       how= "left", on="parts" )

   receipt car  parts    product
0      101   A    oil  Valvoline
1      102   B   tire   Goodyear
2      103   B    oil  Valvoline
3      104   C  glass      Bosch
user_98406370
Related