hleggs hleggs - 4 months ago 65
Python Question

Create pandas pivot table on new sheet in workbook

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:

enter image description here

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)

enter image description here

Here is the full code for the function (it's a flask app by the way):

def process(filename):


VP = ['ZYJD', 'ZYJC', 'ZYKC', 'ZYKA', 'ZYKB', 'ZYKD', 'ZYKE', 'ZYKF', 'ZYJB', 'ZYJX', 'ZYKG', 'ZYKH', 'ZYJE', 'ZYJA',
'ZYKI', 'ZYKX', 'ZYKK', 'ZYKJ', 'ZYJF', 'ZYJK', 'ZYJG', 'ZYJJ', 'ZYKL', 'ZYKM', 'ZYKN']

VA = ['ZYIC', 'ZYIB', 'ZYHC', 'ZYIA', 'ZYHA', 'ZYHG', 'ZYHB', 'ZYID', 'ZYDA', 'ZYIE', 'ZYHD', 'ZYIG', 'ZYIX', 'ZYHE',
'ZYIF', 'ZYHX', 'ZYDE', 'ZYHF', 'ZYLB', 'ZYAC', 'ZYCF', 'ZYDF', 'ZYBG', 'ZYDG', 'ZYDD', 'ZYDH', 'ZYCB', 'ZYCA',
'ZYWA', 'ZYWB', 'ZYWC', 'ZYWD', 'ZYWE', 'ZYWF', 'ZYWG', 'ZYWI', 'ZYWJ']

Gordon = ['ZYDB', 'ZYDX', 'ZYEB', 'ZYED', 'ZYEC', 'ZYEA', 'ZYEX', 'ZYFE', 'ZYFX', 'ZYFD', 'ZYFA', 'ZYFC', 'ZYFB',
'ZYGC', 'ZYGA', 'ZYGX', 'ZYGB', 'ZYGF', 'ZYGG', 'ZYGD', 'ZYLA', 'ZYBF', 'ZYBE', 'ZYLD', 'ZYKM', 'ZYKN',
'ZYCC', 'ZYCE']

Pete = ['ZYAD', 'ZYBX', 'ZYAX', 'ZYAB', 'ZYBC', 'ZYBA', 'ZYBB', 'ZYAA', 'ZYBD', 'ZYLE', 'ZYCX', 'ZYAE', 'ZYCC', 'ZYCE',
'ZYLA', 'ZYBF', 'ZYBE', 'ZYLD']

Mike = ['ZYKP', 'ZYAP', 'ZYHP', 'ZYJP', 'ZYFP', 'ZYJR', 'ZYCP', 'ZYIR', 'ZYAR', 'ZYBP', 'ZYKR', 'ZYJS', 'ZYIP', 'ZYHR',
'ZYEP', 'ZYFF', 'ZYGP', 'ZYKS', 'ZYEE', 'ZYJH', 'ZYII', 'ZYHH', 'ZYJW']

workbook = load_workbook(filename)
worksheet = workbook.active

columns_to_hide = ['B', 'D', 'E', 'F', 'G', 'H', 'I', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AM', 'AN', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CU']

for col in columns_to_hide:
worksheet.column_dimensions[col].hidden = True


routecolumn = worksheet.columns[58]
i = 2
supervisorheader = worksheet.cell("CV1")
s = Style(font=Font(bold=True))
supervisorheader.style = s

worksheet['CV1'] = 'Supervisor'
for route in routecolumn:
if route.value == 'Responsible Route':
continue
if route.value in Gordon:
pos = Gordon.index(route.value)
worksheet['CV' + str(i)].value = 'Gordon'
i += 1
elif route.value in VA:
pos = VA.index(route.value)
worksheet['CV' + str(i)].value = 'Vinny A'
i += 1
elif route.value in VP:
pos = VP.index(route.value)
worksheet['CV' + str(i)].value = 'Vinny P'
i += 1
elif route.value in Pete:
pos = Pete.index(route.value)
worksheet['CV' + str(i)].value = 'Pete'
i += 1
elif route.value in Mike:
pos = Mike.index(route.value)
worksheet['CV' + str(i)].value = 'Mike'
i += 1
elif route.value not in Gordon or route.value not in VA or route.value not in VP or route.value not in Pete \
or route.value not in Mike:
worksheet['CV' + str(i)].value = 'Building'
i += 1




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




newfilename = filename.strip(".xlsx")
newfilename = newfilename + ".xls"
os.rename('/home/CENSORING MY NAME/'+filename, '/home/CENSORING MY NAME/'+newfilename)

return send_file(newfilename, attachment_filename='tdx.xls', as_attachment=True), os.remove ('/home/CENSORING MY NAME/'+newfilename)

Answer

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