pandasを使ってexcelのvlookup関数を実装する

1. データを読み込む

import openpyxl
import pandas as pd
df1 = pd.DataFrame(pd.read_excel('table1.xls',sheet_name = 'Sheet1'))
df2 = pd.DataFrame(pd.read_excel('table2.xls',sheet_name = 'Sheet1'))

>>> df1
   Student Number Name Class
0 1 Zhang San 1 class
1 2 Li Si 1 class
2 3 Wang Wu 2 class
3 4 Zhao Liu 3 class
4 5 Qian Qi 3 class
>>> df2
   No. Name Score Rank
0 1 Zhang San 100 1
1 2 Li Si 99 2
2 3 Wang Wu 98 3
3 4 Zhao Liu 96 4

2. vlookup関数の実装

# only add the score column in table1, not the rank column
result = pd.merge(df1,df2.loc[:,['number','score']],how='left',on = 'number')
>>> result
   Number Name Class Score
0 1 Zhang San 1 class 100.0
1 2 Li Si 1 class 99.0
2 3 Wang Wu 2 class 98.0
3 4 Zhao Liu 3 class 96.0
4 5 Qian Qi 3 class NaN

3. 結果の保存

writer = pd.ExcelWriter('result.xlsx')
result.to_excel(writer,index=False)
writer.save()