1

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

enter image description here

Harrison
  • 5,095
  • 7
  • 40
  • 60
  • Please provide the full traceback. – Charlie Clark Jul 22 '16 at 15:43
  • I've made some changes. I don't have the error anymore, however, the pivot table just isn't being drawn. Can you please look at the edits I made to the question? – Harrison Jul 22 '16 at 15:46
  • @Charlie Clark Now my concerns are 1. why isn't the pivot table appearing on the sheet 2. how can I make my pivot table look like the image I provided? – Harrison Jul 22 '16 at 15:55

2 Answers2

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.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
  • If that's the case, what tool do you recommend that can perform table pivoting? – chip Jun 20 '17 at 02:38
  • 1
    I recommend you signup to the openpyxl mailing list so that you get release announcements such as the one for 2.5a1 which introduces support for pivot tables. – Charlie Clark Jun 20 '17 at 07:44
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()
Syed
  • 144
  • 9