使用 pandas 数据框中的数据创建多个Excel工作表 [英] creating multiple excel worksheets using data in a pandas dataframe

问题描述

刚开始使用熊猫和python.

Just started using pandas and python.

我有一个工作表，已将其读入数据框并应用了正向填充(ffill)方法.

I have a worksheet which I have read into a dataframe and the applied forward fill (ffill) method to.

然后，我想创建一个包含两个工作表的Excel文档.

I would then like to create a single excel document with two worksheets in it.

在应用ffill方法之前，一个工作表将在数据框中包含数据，而在下一个工作表中将应用ffill方法的在数据框中.

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.

然后，我想对结果应用某些vba格式-但我不确定哪个dll或插件，或者我需要使用python调用excel vba来将标题格式设置为粗体并添加颜色等.

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.

我已经部分成功了，xlsxwriter将创建一个新的工作簿并添加工作表，但是dataframe.to_excel操作似乎在它创建的工作簿上不起作用，工作簿打开了，但是工作表是空白的.

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.

谢谢.

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')

推荐答案

您的示例代码几乎是正确的，除了需要创建writer对象并且不需要使用add_sheet()方法.以下应该可以工作:

Your sample code is almost correct except you need to create the writer object and you don't need to use the add_sheet() methods. The following should work:

# ...
writer = pd.ExcelWriter('final.xlsx')
data.to_excel(writer,'original')

# data.fillna() or similar.

data.to_excel(writer,'result')
writer.save()
# ...

Pandas的末尾显示了正确的语法

The correct syntax for this is shown at the end of the Pandas DataFrame.to_excel() docs.

另请参见使用Python熊猫和XlsxWriter .

这篇关于使用 pandas 数据框中的数据创建多个Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！