Python实现Excel中vlookup函数功能

本文作者：王碧琪

文字编辑：方   言

技术总编：张馨月

爬虫俱乐部云端课程来袭！

爬虫俱乐部将于2020年8月25日至28日在线上举行Stata数据分析法律与制度专题训练营，主要是为了让学员掌握Stata软件进阶操作，涉及内容包括基本字符串函数及其应用、正则表达式、法律与制度数据网络爬虫技巧、判案文书的文本分析等技术。详情请参考《8月Stata数据分析法律与制度专场来啦!》    另外，爬虫俱乐部于2020年7月在线上举办的Stata与Python编程技术训练营已圆满结束。应广大学员需求，我们的课程现已在腾讯课堂双双上线，且继续提供答疑服务。现在关注公众号并在朋友圈转发推文《来腾讯课堂学Stata和Python啦！》，即可获得600元课程优惠券，集赞50个再领200元课程优惠劵！（截图发至本公众号后台领取）原价2400元的课程，现在只要1600元！

使用Excel进行数据分析时常常用到vlookup函数。在《Stata实现Excel中vlookup函数功能》中介绍了vlookup函数的基本用法，今天，我们使用Python中的pandas库来实现该函数的相应功能。

一、简介

（一）数据简介

本文使用的原始数据文件来源于《明星闪闪亮：各校高被引论文》中公布的全国高校经管类核心期刊发文排行榜（前200），文件名为“第一单位排名汇总.xlsx”。

（二）结果文件样式

现在有一部分大学名单，想要提取该大学对应的“发文量”和“发文排名”数据。其中，大学名单为乱序，且可能存在原始文件中找不到的情况（北京第二外国语学院无对应数据）。

本文的处理目的是将上述表格填充完整。

（三）函数简介

实现vlookup函数功能需要用到pandas中的merge()函数。在《Python之数据合并与连接》和《DataFrame数组常用方法（二）》中都有介绍merge的使用方法。常用格式如下：

pd.merge(df1,df2,left_on='col1',right_on='col2',how='inner/left/right/outer')
pd.merge(df1,df2,on=[col1,col2,...],how='inner/left/right/outer')

merge函数的参数见下表：

参数	说明
left	参与合并的左键DataFrame
right	参与合并的右键DataFrame
how	四个取值：inner,outer,left,right。两列的交集（inner），保留第一个数据的所有值（left），保留第二个数据的所有值（right），两列的并集（outer）
on	用于连接的列名。必须存在于左右两个 Data Frame对象中。如果未指定，且其他连接键也未指定，则以left和right列名的交集作为连接键
left_on	左侧 DataFrame中用作连接键的列
right_on	右侧 DataFrame中用作连接键的列
left_index	将左侧的行索引用作其连接键
right_index	类似于 - left index
sort	根据连接键对合并后的数据进行排序，默认为True
suffixes	字符串值元组，用于追加到重叠列名的末尾，默认为（x,y）。例如，如果左右两个Dataframe对象都有“data”，则结果中就会出现data_x和data_y
copy	设置为False，可以在某些特殊情况下避免将数据复制到结果数据结构中，默认值为复制

二、程序处理

（一）准备工作

import os
path=r'F:\vlookup函数'
if not os.path.exists(path): # 如果没有该文件夹，则自动生成
  os.makedirs(path)
os.chdir(path) #更改路径
import pandas as pd
infile="第一单位排名汇总.xlsx" #数据库Excel文件
outfile="out.xlsx" #导出的结果文件

（二）读入infile

df1 = pd.read_excel(infile,encoding = 'utf-8')
print(df1.head())

结果中显示了df1的前几行数据值。

（三）创建outfile

data={'大学':['北京第二外国语学院','中南财经政法大学','深圳大学','上海大学','华中科技大学']}
df2 = pd.DataFrame(data) #把待提取大学名单写入df2中
print(df2)

结果中显示df2成功写入了所需的大学名单。

（四）合并

dfneed=df1[['大学', '发文量','发文排名']] #df1中用到的列
oncol='大学' #按照这一列进行合并
df2=df2.merge(dfneed, how='left', on=oncol) #合并df2和df1中的dfneed
#df2=pd.merge(df2,dfneed,how='left',on=oncol) #与上一行功能相同
print(df2)

这里我们设置保留左侧的DataFrame的全部数据，可以将“北京第二外国语学院”这条数据保留下来（尽管它是空的，显示为NaN）。

（五）导出

df2.to_excel(outfile,index=False) #Excel文件导出

打开Excel文件可以看到：

此时，已成功实现vlookup函数功能，得到目标文件。

三、结语

pandas是Python中处理Excel常用的库，今天我们使用其中的merge函数实现了Excel中的vlookup函数功能，除此之外pandas还有其他许多非常强大的功能，如果需要对大量表格数据进行处理，可以尝试Python中的pandas库。

对我们的推文累计打赏超过1000元，我们即可给您开具发票，发票类别为“咨询费”。用心做事，不负您的支持！

往期推文推荐

关于我们

微信公众号“Stata and Python数据分析”分享实用的stata、python等软件的数据处理知识，欢迎转载、打赏。我们是由李春涛教授领导下的研究生及本科生组成的大数据处理和分析团队。

此外，欢迎大家踊跃投稿，介绍一些关于stata和python的数据处理和分析技巧。

投稿邮箱：[e-mail]

投稿要求：
1）必须原创，禁止抄袭；
2）必须准确，详细，有例子，有截图；
注意事项：
1）所有投稿都会经过本公众号运营团队成员的审核，审核通过才可录用，一经录用，会在该推文里为作者署名，并有赏金分成。
2）邮件请注明投稿，邮件名称为“投稿+推文名称”。
3）应广大读者要求，现开通有偿问答服务，如果大家遇到有关数据处理、分析等问题，可以在公众号中提出，只需支付少量赏金，我们会在后期的推文里给予解答。