vagabond vagabond - 17 days ago 8
Python Question

xlsxwriter - legend for Excel pie Chart - python

I have an excel file with 6 tabs (worksheets). Each worksheet is of the same structure and contains two columns - Col 1 contains brand names and Col 2 contains values corresponding to each brand. For each sheet in the the excel file, I want to make a pie chart showing % share for each brand.

The example xls file you can use to run the script on is here

The code i wrote is quite simple and generates the charts. The problem is that the legend for the chart takes serial number names instead of the names of the brand.

import pandas as pd
import xlsxwriter as excel

df = pd.read_excel("/Users/jack/Documents/python-pptx/filename", sheetname=None)


workbook = excel.Workbook('/Users/jack/Documents/python-pptx/chart_pie.xlsx')

for sheetname, data in df.iteritems():
if len(data) > 0:
worksheet = workbook.add_worksheet(sheetname)
chart = workbook.add_chart({'type': 'pie'})
worksheet.write_column('A1', data['Brand'])
worksheet.write_column('B1', data['Share_of_interactions'])
chart.add_series({'categories': '='+sheetname+'!$A$1:$A$'+str(len(data)),
'values': '='+sheetname+'!$B$1:$B$'+str(len(data)),
'name': '='+sheetname+'!$A$1:$A$'+str(len(data))})

## insert chart into the worksheet
worksheet.insert_chart('C3', chart)

## Close the workbook
workbook.close()


Here is a screen shot of the chart :

enter image description here

IF you notice in the chart the legend says 1, 2, 3 .. . . 7 . It actually should be saying the brand name . I've added the name parameter to
chart.add_series
as mentioned in the documentation of
xlsxwriter
- http://xlsxwriter.readthedocs.io/chart.html . Any help would be much appreciated.

Answer

The problem is that you have a space in your sheet name, like Sheet 1. You need to enclose it in single quotes:

df = pd.read_excel("/Users/julien/Downloads/SO_Example_Df.xlsx", sheetname=None)


workbook = excel.Workbook('/Users/julien/Downloads/SO_chart_pie.xlsx')

for sheetname, data in df.items():
    if len(data) > 0: 
        worksheet = workbook.add_worksheet(sheetname)
        chart = workbook.add_chart({'type': 'pie'})
        worksheet.write_column('A1', data['Brand'])
        worksheet.write_column('B1', data['Share_of_interactions'])
        # Here, add single quotes around the sheetname
        chart.add_series({'categories': "='"+sheetname+"'!$A$1:$A$"+str(len(data)),
                          'values':     "='"+sheetname+"'!$B$1:$B$"+str(len(data)),
                          'name':       'My pie chart'})

        ## insert chart into the worksheet
        worksheet.insert_chart('C3', chart)

## Close the workbook
workbook.close()

enter image description here

Comments