Python 使用熊猫数据框中的数据创建多个 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21981820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
creating multiple excel worksheets using data in a pandas dataframe
提问by yoshiserry
Just started using pandas and python.
刚开始使用熊猫和python。
I have a worksheet which I have read into a dataframe and the applied forward fill (ffill) method to.
我有一个工作表，我已将其读入数据帧并对其应用了前向填充 (ffill) 方法。
I would then like to create a single excel document with two worksheets in it.
然后我想创建一个包含两个工作表的 Excel 文档。
One worksheet would have the data in the dataframe before the ffill method is applied and the next would have the dataframe which has had the ffill method applied.
在应用填充方法之前，一个工作表将在数据框中包含数据，而下一个将包含应用了填充方法的数据框。
Eventually I intend to create one worksheet for every unique instance of data in a certain column of the dataframe.
最终，我打算为数据框特定列中的每个唯一数据实例创建一个工作表。
I would then like to apply some vba formatting to the results - but i'm not sure which dll or addon or something I would need to call excel vba using python to format headings as bold and add color etc.
然后我想对结果应用一些 vba 格式 - 但我不确定哪个 dll 或插件或我需要使用 python 调用 excel vba 将标题格式化为粗体并添加颜色等。
I've had partial success in that xlsxwriter will create a new workbook and add sheets, but dataframe.to_excel operations don't seems to work on the workbooks it creates, the workbooks open but the sheets are blank.
我在 xlsxwriter 将创建一个新工作簿并添加工作表方面取得了部分成功，但 dataframe.to_excel 操作似乎不适用于它创建的工作簿，工作簿打开但工作表是空白的。
Thanks in advance.
提前致谢。
import os
import time
import pandas as pd
import xlwt
from xlwt.Workbook import *
from pandas import ExcelWriter
import xlsxwriter
#set folder to import files from
path = r'path to some file'
#folder = os.listdir(path)
#for loop goes here
#get date
date = time.strftime('%Y-%m-%d',time.gmtime(os.path.getmtime(path)))
#import excel document
original = pd.DataFrame()
data = pd.DataFrame()
original = pd.read_excel(path,sheetname='Leave',skiprows=26)
data = pd.read_excel(path,sheetname='Leave',skiprows=26)
print (data.shape)
data.fillna(method='ffill',inplace=True)
#the code for creating the workbook and worksheets
wb= Workbook()
ws1 = wb.add_sheet('original')
ws2 = wb.add_sheet('result')
original.to_excel(writer,'original')
data.to_excel(writer,'result')
writer.save('final.xls')
采纳答案by jmcnamara
Your sample code is almost correct except you need to create the writerobject and you don't need to use the add_sheet()methods. The following should work:
您的示例代码几乎是正确的，只是您需要创建writer对象并且不需要使用这些add_sheet()方法。以下应该工作：
# ...
writer = pd.ExcelWriter('final.xlsx')
data.to_excel(writer,'original')
# data.fillna() or similar.
data.to_excel(writer,'result')
writer.save()
# ...
The correct syntax for this is shown at the end of the Pandas DataFrame.to_excel()docs.
正确的语法显示在 PandasDataFrame.to_excel()文档的末尾。
回答by kalyan solasa
import pandas as pd
df1 = pd.DataFrame({'Data': ['a', 'b', 'c', 'd']})
df2 = pd.DataFrame({'Data': [1, 2, 3, 4]})
df3 = pd.DataFrame({'Data': [1.1, 1.2, 1.3, 1.4]})
writer = pd.ExcelWriter('multiple.xlsx', engine='xlsxwriter')
df1.to_excel(writer, sheet_name='Sheeta')
df2.to_excel(writer, sheet_name='Sheetb')
df3.to_excel(writer, sheet_name='Sheetc')
writer.save()
