Pandas实现Excel的vlookup并且在指定列后面输出
背景：
- 有两个excel，他们有相同的一个列；
- 按照这个列合并成一个大的excel，即vlookup功能
要求：
- 只需要第二个excel的少量的列，比如从40个列中挑选2个列
- 新增的来自第二个excel的列需要放到第一个excel指定的列后面；
- 将结果输出到一个新的excel;
import pandas as pd
# 文件路径
grade_path = r'C:\TELCEL_MEXICO_BOT\A\学生成绩表.xlsx'
sinfo_path = r'C:\TELCEL_MEXICO_BOT\A\学生信息表.xlsx'
output_path = r'C:\TELCEL_MEXICO_BOT\A\合并后的数据表.xlsx' # 保存路径
# 读取数据
df_grade = pd.read_excel(grade_path)
df_sinfo = pd.read_excel(sinfo_path)
# 打印前几行数据
print("学生成绩表：")
print(df_grade)
学生成绩表：
班级 学号 语文 数学 英语
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
5 C01 S006 80 97 97
6 C01 S007 78 95 90
7 C01 S008 56 99 89
8 C01 S009 66 100 97
9 C01 S010 89 99 67
print("\n学生信息表：")
print(df_sinfo)
学生信息表：
学号 姓名 性别 年龄 籍贯
0 S001 怠涵 女 23 山东
1 S002 婉清 女 25 河南
2 S003 溪榕 女 23 湖北
3 S004 漠涓 女 19 陕西
4 S005 祈博 女 24 山东
# 选择需要的列
df_sinfo = df_sinfo[['学号', '姓名', '性别']]
print(df_sinfo)
学号 姓名 性别
0 S001 怠涵 女
1 S002 婉清 女
2 S003 溪榕 女
3 S004 漠涓 女
4 S005 祈博 女
# 合并数据.
#左表df_grade,右表df_sinfo, how='left'以左表中的'学号'为键在右表进行vlookup查找
df_merged = pd.merge(df_grade, df_sinfo, how='left', on=['学号'])
# 调整列的顺序
cols = df_merged.columns.tolist() # 获取所有列名
cols = cols[:2] + cols[-2:] + cols[2:-2] # 重新排列列的顺序
df_merged = df_merged[cols]
# 打印合并后的数据
print("\n合并后的数据表：")
print(df_merged)
合并后的数据表：
班级 学号 姓名 性别 语文 数学 英语
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
5 C01 S006 NaN NaN 80 97 97
6 C01 S007 NaN NaN 78 95 90
7 C01 S008 NaN NaN 56 99 89
8 C01 S009 NaN NaN 66 100 97
9 C01 S010 NaN NaN 89 99 67
# 将合并后的数据保存到指定路径
df_merged.to_excel(output_path, index=False)
print(f"\n合并后的数据已保存到 {output_path}")
合并后的数据已保存到 C:\TELCEL_MEXICO_BOT\A\合并后的数据表.xlsxvlookup后的异常值处理
# 读取数据
df_grade = pd.read_excel(grade_path)
班级 学号 语文 数学 英语
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
5 C01 S006 80 97 97
6 C01 S007 78 95 90
7 C01 S008 56 99 89
8 C01 S009 66 100 97
9 C01 S010 89 99 67
df_sinfo = pd.read_excel(sinfo_path)
学号 姓名 性别 年龄 籍贯
0 S001 怠涵 女 23 山东
1 S002 婉清 女 25 河南
2 S003 溪榕 女 23 湖北
3 S004 漠涓 女 19 陕西
4 S005 祈博 女 24 山东
# 选择需要的列
df_sinfo = df_sinfo[['学号', '姓名', '性别']]
print(df_sinfo)
学号 姓名 性别
0 S001 怠涵 女
1 S002 婉清 女
2 S003 溪榕 女
3 S004 漠涓 女
4 S005 祈博 女
##左表df_grade，右表df_sinfo， 左右两表都有相同列'学号',how=left表示以左表进行vlookup，右表中的V到左表中填充，sort表示是否排序，fillna表示V不到的空值填充
df_merged_2=(df_grade.merge(df_sinfo,on='学号',how = 'left',sort = 'false')).fillna(0)
print(df_merged_2)
班级 学号 语文 数学 英语 姓名 性别
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 祈博 女
5 C01 S006 80 97 97 0 0
6 C01 S007 78 95 90 0 0
7 C01 S008 56 99 89 0 0
8 C01 S009 66 100 97 0 0
9 C01 S010 89 99 67 0 0