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

问题

企业ai替代率.xlsx

把表格2,3,4的数据匹配到表格一，匹配的的标准为symbol和year，根据这两列，只需要匹配表格1中有的就可以，没有的话不需要填充

Python

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

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

Python

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

Python

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

Python

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

Python

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

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

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

思考

Crafting the code

I'll start by importing pandas and reading the sheets as dictionaries. Then, I'll perform the merges step-by-step. After that, I'll save the file and display the first 20 rows of the DataFrame — showing enough for a helpful preview, since the dataset may be quite large. Once it’s ready, I'll provide a link to the merged file, with text separated from the code part since it's displayed in the final message. We’ll use a tool to show the DataFrame to the user.

Thought for 1m 13s

Python

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}")

回答

已根据 symbol 和 year 把表格 2、3、4 中的字段合并到表格 1——只填充表格 1 中能匹配到的记录，其余保持为空。
上方预览展示了前 20 行结果；完整文件已生成：

下载合并后的 Excel 文件

如需调整列顺序、重命名字段，或进一步过滤/分析数据，请告诉我！

问题

Python

Python

Python

Python

Python

Python

思考

Crafting the code

Python

回答

分享这个问答