Create pandas pivot table on new sheet in workbook

Question

I am trying to send my pivot table that I have created onto a new sheet in the workbook, however, for some reason when I execute my code a new sheet is created with the pivot table (sheet is called 'Sheet1') and the data sheet gets deleted.

Here is my code:

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

excel = pd.ExcelFile(filename)
df = pd.read_excel(filename, usecols=['Product Description', 'Supervisor'])

table1 = df[['Product Description', 'Supervisor']].pivot_table(index='Supervisor', columns='Product Description', aggfunc=len, fill_value=0, margins=True, margins_name='Grand Total')



print table1

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

Also, i'm having a bit of trouble with my pivot table design. Here is what the pivot table looks like:

How can I add a column to the end that sums up each row? Like this: (I just need the column at the end, I don't care about formatting it like that or anything)

Answer 1

Just use margins=True and margins_name='Grand Total' parameters when calling pivot_table()

Demo:

In [15]: df = pd.DataFrame(np.random.randint(0, 5, size=(10, 3)), columns=list('abc'))

In [16]: df
Out[16]:
   a  b  c
0  4  3  0
1  1  1  4
2  4  4  0
3  2  3  2
4  1  1  3
5  3  1  3
6  3  3  0
7  0  2  0
8  2  1  1
9  4  2  2

In [17]: df.pivot_table(index='a', columns='b', aggfunc='sum', fill_value=0, margins=True, margins_name='Grand Total')
Out[17]:
                c
b               1    2    3    4 Grand Total
a
0             0.0  0.0  0.0  0.0         0.0
1             7.0  0.0  0.0  0.0         7.0
2             1.0  0.0  2.0  0.0         3.0
3             3.0  0.0  0.0  0.0         3.0
4             0.0  2.0  0.0  0.0         2.0
Grand Total  11.0  2.0  2.0  0.0        15.0