logoChat01.ai

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

Créé le : 17 juin 2025

Répondu en utilisant O3 par Chat01

Question

企业ai替代率.xlsx

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

Python

python
import pandas as pd

file_path = '/mnt/data/企业ai替代率.xlsx'
xl = pd.ExcelFile(file_path)
xl.sheet_names

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

Python

python
df1 = pd.read_excel(file_path, sheet_name='Sheet1')
df2 = pd.read_excel(file_path, sheet_name='Sheet2')
df3 = pd.read_excel(file_path, sheet_name='Sheet3')
df4 = pd.read_excel(file_path, sheet_name='Sheet4')

df1.head()

   symbol  year  name industry  enterprise
0       1  2012  平安银行   货币金融服务    0.551907
1       1  2013  平安银行   货币金融服务    0.547184
2       1  2014  平安银行   货币金融服务    0.548828
3       1  2015  平安银行   货币金融服务    0.546525
4       1  2016  平安银行   货币金融服务    0.542396

Python

python
df2.head()

   symbol  year  investment
0       2  2010      0.0003
1       2  2011      0.0002
2       2  2012      0.0002
3       2  2013      0.0002
4       2  2014      0.0002

Python

python
df3.head()

   symbol  year  Major
0       1  2010      1
1       1  2011      1
2       1  2012      1
3       1  2013      1
4       1  2014      1

Python

python
df4.head()

   symbol  year  board  stock
0       1  2010   13.0    3.0
1       1  2011    9.0    3.0
2       1  2012   13.0    4.0
3       1  2013    9.0    2.0
4       1  2014   10.0    3.0

Python

python
# Merge df2 into df1
merged = pd.merge(df1, df2, on=['symbol','year'], how='left')
# Merge df3
merged = pd.merge(merged, df3, on=['symbol','year'], how='left')
# Merge df4
merged = pd.merge(merged, 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'
with pd.ExcelWriter(output_path, engine='xlsxwriter') as writer:
    merged.to_excel(writer, sheet_name='Merged', index=False)
output_path

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

Réflexion

Finalizing the merge and preparing download

I’ve merged the necessary data from sheets 2-4 into sheet1, based on matching "symbol" and "year." The additional columns from each sheet, like "investment," "Major," "board," and "stock," have been added. I’ve saved the result as a new workbook titled “企业ai替代率_merged.xlsx.” Now, I need to explain this to the user in Chinese and provide a link for them to download the file. I’ll include the link like this: Download the Excel file.

Thought for 1m 18s

Python

python
import pandas as pd

# 读取原始文件
file_path = '/mnt/data/企业ai替代率.xlsx'
df1 = pd.read_excel(file_path, sheet_name='Sheet1')
df2 = pd.read_excel(file_path, sheet_name='Sheet2')
df3 = pd.read_excel(file_path, sheet_name='Sheet3')
df4 = pd.read_excel(file_path, sheet_name='Sheet4')

# 依据 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')
)

# 保存到新的 Excel 文件
output_path = '/mnt/data/企业ai替代率_merged.xlsx'
merged.to_excel(output_path, sheet_name='Merged', index=False)

print("合并已完成，文件已保存到:", output_path)

Réponse

已按照 symbol + year 把表格 2、3、4 中的数据左连接到表格 1，只保留了表格 1 中本来就存在的 (symbol, year) 组合；若对应行在其他表缺失，则保持空白，不新增任何行。
合并后的数据放在工作表 Merged 中，您可以直接下载查看：

下载合并后的 Excel 文件

如果还需要调整列顺序、改列名，或导出为其它格式，告诉我即可！

Partager cette Q&R