Trouble writing pivot table to excel file

Question

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.

Here is my code:

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  

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

Answer 1

Since pd.pivot_table returns a dataframe, you can just write the dataframe into excel. Here is how I write my output from a pandas dataframe to an excel template. Please note that if data is already present in the cells where you are trying to write the dataframe, it will not be overwritten and the dataframe will be written to a new sheet which is my i have included a step to clear existing data from the template. I have not tried to write output on merged cells so that might throw an error.

Setup

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
file_path='Template.xlsx'
book=load_workbook(file_path)
writer = pd.ExcelWriter(file_path, engine='openpyxl')
writer.book = book
sheet_name="Template 1"
sheet=book[sheet_name]

Set first row and first column in the excel template where output is to be pasted. If my output is to be pasted starting in cell N2, row_start will be 2 and col_start will be 14

row_start=2
col_start=14

Clear existing data in excel template

for c_idx, col in enumerate(df.columns,col_start):
    for r_idx in range(row_start,10001):
        sheet.cell(row=r_idx, column=c_idx, value="")

Write dataframe to excel template

rows=dataframe_to_rows(df,index=False)
for r_idx, row in enumerate(rows,row_start):
    for c_idx, col in enumerate(row,col_start):
        sheet.cell(row=r_idx, column=c_idx, value=col)

writer.save()
writer.close()

Answer 2

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