把表格2,3,4的数据匹配到表格一，匹配的的标准为symbol和year，根据这两列，只需要匹配表格...

python
file_path = '/mnt/data/企业ai替代率.xlsx'
sheets = pd.read_excel(file_path, sheet_name=None)
list(sheets.keys())

['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4']

python
for name, df in sheets.items():
    print(name, df.shape)
    print(df.head())
    print()

python
print(sheets['Sheet2'].head())

python
print(sheets['Sheet3'].head())

python
df1 = sheets['Sheet1']
df2 = sheets['Sheet2']
df3 = sheets['Sheet3']
df4 = sheets['Sheet4']

# merge left
merged = df1.merge(df2, on=['symbol','year'], how='left')
merged = merged.merge(df3, on=['symbol','year'], how='left')
merged = merged.merge(df4, on=['symbol','year'], how='left')

merged.head()

   symbol  year  name industry  enterprise  investment  Major  board  stock
0       1  2012  平安银行   货币金融服务    0.551907         NaN    1.0   13.0    4.0
1       1  2013  平安银行   货币金融服务    0.547184         NaN    1.0    9.0    2.0
2       1  2014  平安银行   货币金融服务    0.548828         NaN    1.0   10.0    3.0
3       1  2015  平安银行   货币金融服务    0.546525         NaN    1.0    9.0    2.0
4       1  2016  平安银行   货币金融服务    0.542396         NaN    1.0    9.0    2.0

python
output_path = '/mnt/data/企业ai替代率_merged.xlsx'
merged.to_excel(output_path, index=False)
output_path

'/mnt/data/企业ai替代率_merged.xlsx'

python
import pandas as pd
from ace_tools import display_dataframe_to_user

# Load the original workbook
file_path = "/mnt/data/企业ai替代率.xlsx"
sheets = pd.read_excel(file_path, sheet_name=None)

# Tables
df1 = sheets["Sheet1"]  # 表格1
df2 = sheets["Sheet2"]  # 表格2
df3 = sheets["Sheet3"]  # 表格3
df4 = sheets["Sheet4"]  # 表格4

# 合并：以 symbol 和 year 为键，左连接，只匹配表格1已有的行
merged = (
    df1.merge(df2, on=["symbol", "year"], how="left")
       .merge(df3, on=["symbol", "year"], how="left")
       .merge(df4, on=["symbol", "year"], how="left")
)

# 保存新的工作簿
output_path = "/mnt/data/企业ai替代率_merged.xlsx"
merged.to_excel(output_path, index=False)

# 向用户展示前 20 行预览
display_dataframe_to_user("匹配结果预览 (前 20 行)", merged.head(20))

print(f"文件已保存至 {output_path}")