Pandas操作两个Excel实现数据对应行的合并_python

import pandas as pd

df1 = pd.read_excel('data.xlsx', sheet_name='Sheet1', header=0).values.tolist()
df2 = pd.read_excel('data.xlsx', sheet_name='Sheet2', header=0).values.tolist()
for i in range(len(df1)):
    df1[i].append(0)

for i, item in enumerate(df2):
    for j in range(len(df1)):
        if df1[j][-1] == 0 and df1[j][0] == item[0] and df1[j][2] == item[2]:
            df2[i] = df1[j]
            df1[j][-1] = 1
            break
df2 = pd.DataFrame(df2)
print(df2)

with pd.ExcelWriter("data.xlsx", mode='a', engine='openpyxl') as writer:
    df2.to_excel(writer, sheet_name="Sheet3")

import pandas as pd
# pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

df1 = pd.read_excel('data.xlsx', sheet_name='Sheet1', header=0)
df2 = pd.read_excel('data.xlsx', sheet_name='Sheet2', header=0)
# 标记是否匹配过
used = set()

for idx2, row2 in df2.iterrows():
    tmp = df1[(df1['所属行业'] == row2['所属行业']) & (df1['新年份'] == row2['新年份'])]
    for idx1, row1 in tmp.iterrows():
        if idx1 not in used:
            df2.iloc[idx2, :] = row1
            used.add(idx1)
            break

df2.set_index('所属行业', inplace=True)
print(df2)

with pd.ExcelWriter("data.xlsx", mode='a', engine='openpyxl') as writer:
    df2.to_excel(writer, sheet_name="Sheet4")