如何使用python在excels（不同的列名）上执行vlookup

Question

专家们，我想用两个不同的列名在两个excel之间执行vlookup，并且输出列名也要不同

让我们以下面的例子来理解这个场景 在源excel文件1中，我在位置A处的列名为“Computer name”，在源excel文件2中，我在位置B处的列名为“short”。我想在excel文件1的“Computer name”列和excel文件2的“short”列之间执行vlookup（sql的一种左连接）。在vlookup之后，我想在excel文件1中添加vlookup的输出，作为cloumn名称，作为“CMDB交叉检查”，它位于最终输出excel文件下面显示的屏幕打印的位置B。请注意，excel文件1的其余所有列将保留在那里，只有新列将出现在excel文件1中的位置B

源Excel文件1：

源Excel文件2：

输出excel文件：

我使用下面的代码，但它不工作。请你提出建议

import pandas as pd
import numpy as np

avclient_workbook ="AV_Clients1.xlsx"
cmdb_workbook = "cmdb_all.xlsx"

output_workbook = "AVClientCMDBAll.xlsx"
df_avclient_workbook = pd.read_excel(avclient_workbook)
df_cmdb_workbook = pd.read_excel(cmdb_workbook)

#print(df_avclient_workbook.columns)
#print(df_cmdb_workbook.columns)
df_avclient_workbook.rename(columns={'Computer name':'short'}, inplace=True) #just trying to rename it
#not able to achive :(
df_3 = pd.merge(df_avclient_workbook, df_cmdb_workbook[['short', 'short']], on='short',how='left')
print(df_3)

Answer 1

伙计们，我终于用下面的代码完成了。虽然我已经手动添加了列列表，但仍然找到了一种方法，可以动态地给出这个列列表

import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')

avclient_workbook ="File1.xlsx"
cmdb_workbook = "File2.xlsx"

output_workbook = "File3.xlsx"
df_avclient_workbook = pd.read_excel(avclient_workbook)
df_cmdb_workbook = pd.read_excel(cmdb_workbook)

merged_dataset = pd.merge(df_avclient_workbook, df_cmdb_workbook, how='left',
                          left_on='Computer name',
                          right_on='short')
final_dataset = merged_dataset[df_avclient_workbook.columns]
final_dataset['CMDB crosscheck'] = merged_dataset['short']
#list of column names which i want to populate
final_dataset = final_dataset[["Computer name","CMDB crosscheck","Computer DNS name","IP address","User account","Management server","Group name","Vendor","Product name","Product version","FW policy","Definition version","Server definition","Delta time","Definition date","Delta range","Last connection (UTC)","Last connection range","Last AD connection","AV status","FW status","IPS status","ATP status","Agent GUID","Agent version","Scan engine","FW version","Hotfixes","OS name","OS version","Platform","Platform type","Architecture","Group path","Cloud / infra","Management proxy","Management proxy IP","ITSM name","ITSM priority","ITSM install status","ITSM responsible group","Tags","Excluded tags","Tree sorting","Last update (UTC)","Problem found","Problem description"]]

final_dataset.to_excel(output_workbook, index=False)

#writer.save()