23.Pandas怎样实现Excel的vlookup并且在指定列后面输出？

背景：

1. 有两个excel，他们有相同的一个列；

2. 按照这个列合并成一个大的excel，即vlookup功能，要求：

   • 只需要第二个excel的少量的列，比如从40个列中挑选2个列

   • 新增的来自第二个excel的列需要放到第一个excel指定的列后面；

3. 将结果输出到一个新的excel;

微信公众号：蚂蚁学Python

步骤1：读取两个数据表

In [1]:

import pandas as pd
# 学生成绩表
df_grade = pd.read_excel("./course_datas/c23_excel_vlookup/学生成绩表.xlsx") 
print(df_grade.head())

	班级	学号	语文成绩	数学成绩	英语成绩
0	C01	S001	99	84	88
1	C01	S002	66	95	77
2	C01	S003	68	68	61
3	C01	S004	63	66	82
4	C01	S005	72	95	94

In [3]:

# 学生信息表
df_sinfo = pd.read_excel("./course_datas/c23_excel_vlookup/学生信息表.xlsx") 
print(df_sinfo.head())

Out[3]:

	学号	姓名	性别	年龄	籍贯
0	S001	怠涵	女	23	山东
1	S002	婉清	女	25	河南
2	S003	溪榕	女	23	湖北
3	S004	漠涓	女	19	陕西
4	S005	祈博	女	24	山东

目标：怎样将第二个“学生信息表”的姓名、性别两列，添加到第一个表“学生成绩表”，并且放在第一个表的“学号”列后面？

步骤2：实现两个表的关联

即excel的vloopup功能

In [4]:

# 只筛选第二个表的少量的列
df_sinfo=df_sinfo[['学号','姓名','性别']]
print(df_sinfo.head())

Out[4]:

	学号	姓名	性别
0	S001	怠涵	女
1	S002	婉清	女
2	S003	溪榕	女
3	S004	漠涓	女
4	S005	祈博	女

In [5]:

df_merge=pd.merge(left=df_grade,right=df_sinfo,left_on='学号',right_on='学号')
print(df_merge.head())

pd.merge() 是 Pandas 库中用于合并数据框架（DataFrame）的函数，其使用格式如下：

python复制代码pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,
         left_index=False, right_index=False, sort=True,
         suffixes=('_x', '_y'), copy=True, validate=None)

其中，参数 left 和 right 分别表示要进行合并的两个 DataFrame。参数 how 指定了连接方式，默认值为 'inner'，即使用内连接。其他常用连接方式包括外连接（'outer'）、左连接（'left''）和右连接（'right'）。参数 on 指示哪一列或者多列用作连接键；参数 left_on 和 right_on 则分别指定左右两个 DataFrame 上的连接键名字。如果数据集中的连接键不同，则需要使用 left_on 和 right_on 进行显式指定。

另外，参数left_index 和 right_index 分别指示使用左右两个 DataFrame 的索引作为连接键，而非具体的列名。如果两个 DataFrame 中都没有匹配的列可以用来合并，也可以使用 left_index=True 和 right_index=True 在索引上进行合并。

最后，还可以通过设置 suffixes 来指定在合并时出现列名重复的后缀字符串，该参数默认值为 ('_x', '_y')。

Out[5]:

	班级	学号	语文成绩	数学成绩	英语成绩	姓名	性别
0	C01	S001	99	84	88	怠涵	女
1	C01	S002	66	95	77	婉清	女
2	C01	S003	68	68	61	溪榕	女
3	C01	S004	63	66	82	漠涓	女
4	C01	S005	72	95	94	祈博	女

步骤3：调整列的顺序

In [6]:

print(df_merge.head())

Out[6]:

Index(['班级', '学号', '语文成绩', '数学成绩', '英语成绩', '姓名', '性别'], dtype='object')

问题：怎样将'姓名', '性别'两列，放到'学号'的后面？

接下来需要用Python的语法实现列表的处理

In [7]:

# 将columns变成python的列表形式
new_columns=df_merge.columns.to_list()
print(new_columns)

Out[7]:

['班级', '学号', '语文成绩', '数学成绩', '英语成绩', '姓名', '性别']

In [8]:

# 按逆序insert，会将"姓名"，"性别"放到"学号"的后面
for name in ["姓名", "性别"][::-1]:
    new_columns.remove(name)
    new_columns.insert(new_columns.index("学号")+1, name)

In [9]:

print(new_columns)

Out[9]:

['班级', '学号', '姓名', '性别', '语文成绩', '数学成绩', '英语成绩']

In [10]:

df_merge=df_merge.reindex(columns=new_columns)
print(df_merge.head())

Out[10]:

	班级	学号	姓名	性别	语文成绩	数学成绩	英语成绩
0	C01	S001	怠涵	女	99	84	88
1	C01	S002	婉清	女	66	95	77
2	C01	S003	溪榕	女	68	68	61
3	C01	S004	漠涓	女	63	66	82
4	C01	S005	祈博	女	72	95	94

步骤4：输出最终的Excel文件

In [11]:

df_merge.to_excel("./合并后的数据表.xlsx", index=False)