Python - function similar to VLOOKUP (Excel)

Question

i am trying to join two data frames but cannot get my head around the possibilities Python has to offer.

First dataframe:

ID MODEL   REQUESTS ORDERS
1  Golf    123      4
2  Passat  34       5
3  Model 3 500      8
4  M3      5        0

Second dataframe:

MODEL   TYPE  MAKE
Golf    Sedan Volkswagen
M3      Coupe BMW
Model 3 Sedan Tesla

What I want is to add another column in the first dataframe called "make" so that it looks like this:

ID MODEL   MAKE       REQUESTS ORDERS
1  Golf    Volkswagen 123      4
2  Passat  Volkswagen 34       5
3  Model 3 Tesla      500      8
4  M3      BMW        5        0

I already looked at merge, join and map but all examples just appended the required information at the end of the dataframe.

Answer 1

I think you can use insert with map by Series created with df2 (if some value in column MODEL in df2 is missing get NaN):

df1.insert(2, 'MAKE', df1['MODEL'].map(df2.set_index('MODEL')['MAKE']))
print (df1)
   ID    MODEL        MAKE  REQUESTS  ORDERS
0   1     Golf  Volkswagen       123       4
1   2   Passat         NaN        34       5
2   3  Model 3       Tesla       500       8
3   4       M3         BMW         5       0

Answer 2

Although not in this case, but there might be scenarios where df2 has more than two columns and you would just want to add one out of those to df1 based on a specific column as key. Here is a generic code that you may find useful.

df = pd.merge(df1, df2[['MODEL', 'MAKE']], on = 'MODEL', how = 'left')