vlookup between 2 Pandas dataframes
<p>I have 2 pandas Dataframes as follows.</p>

<p>DF1:</p>

<pre><code>Security     ISIN
ABC           I1 
DEF           I2
JHK           I3
LMN           I4
OPQ           I5
</code></pre>

<p>and DF2:</p>

<pre><code>ISIN      Value
 I2        100
 I3        200
 I5        300
</code></pre>

<p>I would like to end up with a third dataframe looking like this:</p>

<p>DF3:</p>

<pre><code>Security   Value
 DEF       100
 JHK       200
 OPQ       300
</code></pre>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a>, by default is inner join, so <code>how=inner</code> is omit and if there is only one common column in both <code>Dataframes</code>, you can also omit parameter <code>on='ISIN'</code>:</p>

<pre><code>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
</code></pre>

<p>Or <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.map.html" rel="nofollow"><code>map</code></a> column <code>ISIN</code> by <code>Series</code> from <code>df1</code>:</p>

<pre><code>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
</code></pre>

python pandas