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

Question

专家，我想在两个excels之间执行vlookup，两个不同的列名和输出列名也是不同的。

让我们以下面的例子来理解在源excel file1中，在位置A我的列名为"Computer name“，在源excel file2中在位置B的列名为"short”。我想在excel File1的"Computer name“列和excel File2的"short”列之间执行vlookup(一种sql的左连接)。在vlookup之后，我想在excel file1中添加vlookup的输出作为云名"CMDB crosscheck“，它位于屏幕打印中的位置B，如下所示的最终输出excel文件。请注意，excel file1的其余所有列都将保留在那里，只有新的列将替换excel file1中的位置B

源Excel file1：

​

​

源Excel file2：

​

输出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()