无法将数据透视表写入Excel文件 [英] Trouble writing pivot table to excel file

问题描述

我正在使用pandas/openpyxl处理一个excel文件，然后创建一个数据透视表以添加到当前工作簿中的新工作表中.当我执行代码时，将创建新的工作表，但不会将数据透视表添加到工作表中.

I am using pandas/openpyxl to process an excel file and then create a pivot table to add to a new worksheet in the current workbook. When I execute my code, the new sheet gets created but the pivot table does not get added to the sheet.

这是我的代码:

worksheet2 = workbook.create_sheet()
worksheet2.title = 'Sheet1'
workbook.save(filename)

excel = pd.ExcelFile(filename)
df = excel.parse(sheetname=0)
df1 = df[['Product Description', 'Supervisor']]

table1 = pd.pivot_table(df1, index = ['Supervisor'],
                           columns = ['Product Description'],
                          values = ['Product Description'],
                           aggfunc = [lambda x: len(x)], fill_value = 0)



print table1

writer = pd.ExcelWriter(filename)
table1.to_excel(writer, 'Sheet1')
writer.save()
workbook.save(filename)

当我打印出我的桌子时，我得到了:

When I print out my table I get this:

                               <lambda>                         \
Product Description EXPRESS 10:30 (doc) EXPRESS 10:30 (nondoc)   
Supervisor                                                       
Building                              0                      1   
Gordon                                1                      0   
Pete                                  0                      0   
Vinny A                               0                      1   
Vinny P                               0                      1   

                                                                \
Product Description EXPRESS 12:00 (doc) EXPRESS 12:00 (nondoc)   
Supervisor                                                       
Building                              0                      4   
Gordon                                1                      2   
Pete                                  1                      0   
Vinny A                               1                      1   
Vinny P                               0                      1   


Product Description MEDICAL EXPRESS (nondoc)  
Supervisor                                    
Building                                   0  
Gordon                                     1  
Pete                                       0  
Vinny A                                    0  
Vinny P                                    0  

我希望数据透视表看起来像这样:(如果我的数据透视表代码不能使它看起来像这样，有人可以帮我使它看起来像吗?我不确定如何添加总计列.这与数据透视表的aggfunc部分有关吗?)

I would like the pivot table to look like this: (if my pivot table code won't make it look like this could someone help me make it look like that? I'm not sure how to add the grand total column. It has something to do with the aggfunc portion of the pivot table right?)

推荐答案

您不能执行此操作，因为openpyxl当前不支持数据透视表.有关更多信息，请参见 https://bitbucket.org/openpyxl/openpyxl/issues/295

You can't do this because openpyxl does not currently support pivot tables. See https://bitbucket.org/openpyxl/openpyxl/issues/295 for further information.

这篇关于无法将数据透视表写入Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！