我有两张纸的Excel文件。我想将3列从第一张纸复制到第二张纸。
注意:
复制的3列标签名称与第二张纸有些重复。但是我应该保留第二张纸的原始数据而不更改它们。
我尝试了很多方法。到目前为止,我最好的尝试是:
df_new_sheet2 = pd.concat([df_old_sheet2, df_three_of_sheet1], axis=1, join_axes=[df_old_sheet2.index])
但这不是所需的输出。
如果熊猫无法做到这一点,您能建议其他一些可行的python软件包吗?
如果我对问题的描述不够清楚,请上传一张可能或多或少有帮助的图片。谢谢你的回答〜
更新[2017.07.24]:
我终于找到我的错!
插入一列带有索引号的列,然后按照b2002的分辨率进行操作,一切都会好起来的。 :)
最佳答案
此方法使用pandas和xlsxwriter。
设置(创建演示excel文件):
import pandas as pd
df1 = pd.DataFrame({'1_A': [1,2,3,4], '1_B': [5,4,6,5],
'1_C': [8,7,9,0], '1_D': [9,7,8,5], '1_E': [2,4,9,8]})
df2 = pd.DataFrame({'1_A': [5,4,1,3], '1_B': [55,2,3,4]})
setup_dict = {'Sheet_1': df1, 'Sheet_2': df2}
with pd.ExcelWriter('excel_file.xlsx',
engine='xlsxwriter') as writer:
for ws_name, df_sheet in setup_dict.items():
df_sheet.to_excel(writer, sheet_name=ws_name)
(从这里开始阅读现有的excel文件)
#Read your excel file, use "sheetname=None" to create a dictionary of
#worksheet dataframes. (Note: future versions of pandas will use
#"sheet_name" vs. "sheetname").
#Replace 'excel_file.xlsx' with the actual path to your file.
ws_dict = pd.read_excel('excel_file.xlsx', sheetname=None)
#Modify the Sheet_2 worksheet dataframe:
#(or, create a new worksheet by assigning concatenated df to a new key,
#such as ws_dict['Sheet_3'] = ...)
ws_dict['Sheet_2'] = pd.concat([ws_dict['Sheet_2'][['1_A','1_B']],
ws_dict['Sheet_1'][['1_A','1_B','1_C']]],
axis=1)
#Write the ws_dict back to disk as an excel file:
#(replace 'excel_file.xlsx' with your desired file path.)
with pd.ExcelWriter('excel_file.xlsx',
engine='xlsxwriter') as writer:
for ws_name, df_sheet in ws_dict.items():
df_sheet.to_excel(writer, sheet_name=ws_name)
可以使用其他方法来合并列,例如连接(例如,具有不同的后缀代表原始工作表)。
由于在Excel文件中所有工作表都转换为数据框
被读取。
编辑(用于新工作表和唯一列名...)
ws_dict = pd.read_excel('excel_file.xlsx', sheetname=None)
#Modify the Sheet_2 worksheet dataframe:
#(or, create a new worksheet by assigning concatenated df to a new key,
#such as ws_dict['Sheet_3'] = ...)
ws_dict['Sheet_3'] = ws_dict['Sheet_2'][['1_A','1_B']].join(ws_dict['Sheet_1'][['1_A','1_B','1_C']],
lsuffix='_sh2', rsuffix='_sh1', how='outer')
#Write the ws_dict back to disk as an excel file:
#(replace 'excel_file.xlsx' with your desired file path.)
with pd.ExcelWriter('excel_file.xlsx',
engine='xlsxwriter') as writer:
for ws_name, df_sheet in ws_dict.items():
df_sheet.to_excel(writer, sheet_name=ws_name)
关于python - Python Pandas在不更改任何数据的情况下将列从一张纸复制到另一张纸?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/45251721/