vlookup between 2 Pandas dataframes

Question

I have 2 pandas Dataframes as follows.

DF1:

Security     ISIN
ABC           I1 
DEF           I2
JHK           I3
LMN           I4
OPQ           I5

and DF2:

ISIN      Value
 I2        100
 I3        200
 I5        300

I would like to end up with a third dataframe looking like this:

DF3:

Security   Value
 DEF       100
 JHK       200
 OPQ       300

jezrael · Answer 1 · 2016-10-12T07:59:10.623

You can use merge, by default is inner join, so how=inner is omit and if there is only one common column in both Dataframes, you can also omit parameter on='ISIN':

df3 = pd.merge(df1, df2)
#remove column ISIN
df3.drop('ISIN', axis=1, inplace=True)
print (df3)
  Security  Value
0      DEF    100
1      JHK    200
2      OPQ    300

Or map column ISIN by Series from df1:

print (df1.set_index('ISIN')['Security'])
ISIN
I1    ABC
I2    DEF
I3    JHK
I4    LMN
I5    OPQ
Name: Security, dtype: object

#create new df by copy of df2
df3 = df2.copy()
df3['Security'] = df3.ISIN.map(df1.set_index('ISIN')['Security'])
#remove column ISIN
df3.drop('ISIN', axis=1, inplace=True)
#change order of columns
df3 = df3[['Security','Value']]
print (df3)
  Security  Value
0      DEF    100
1      JHK    200
2      OPQ    300

thanks again Jazrael. This was extremely helpful. Now, if I am not too cheecky, again, My resulting DF has tree columns and the last 2 have only values as follows: — Andrei Cozma, Oct 12 '16 at 08:36

score 4 · Answer 2 · answered Oct 12 '16 at 08:07

You can use pd.merge to automatically do an inner join on ISIN. The following line of code should get you going:

df3 = pd.merge(df1, df2)[['Security', 'Value']]

Which results in df3:

  Security  Value
0      DEF    100
1      JHK    200
2      OPQ    300

The fully reproducible code sample looks like:

import pandas as pd

df1 = pd.DataFrame({
        'Security': ['ABC', 'DEF', 'JHK', 'LMN', 'OPQ'],
        'ISIN' : ['I1', 'I2', 'I3', 'I4', 'I5']
    })
df2 = pd.DataFrame({
        'Value': [100, 200, 300],
        'ISIN' : ['I2', 'I3', 'I5']
    })

df3 = pd.merge(df1, df2)[['Security', 'Value']]
print(df3)

vlookup between 2 Pandas dataframes

2 Answers2

Linked