CODEWITHSUNDEEP
pythonexcelpandasxlsxwriter
vagabond
vagabond

Reputation: 3594

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.

Upvotes: 2

Views: 3085

Answers (2)

jmcnamara
jmcnamara

Reputation: 41644

In Excel, and in XlsxWriter, the names of the data points in a Pie Chart come from the "Categories". This is different from other "2D" chart types where the names come from the series name. This is because a pie chart is a special case of a single series chart.

Anyway, if you point your Categories to the names you want they will be displayed. Like this:

import pandas as pd

# Some sample data to plot.
data = {'apples': 10, 'berries': 32, 'squash': 21, 'melons': 13, 'corn': 18}

# Create a Pandas dataframe from the data.
df = pd.DataFrame([data], index=['Farm'])

# Create a Pandas Excel writer using XlsxWriter as the engine.
excel_file = 'pie.xlsx'
sheet_name = 'Sheet1'

writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')
df.to_excel(writer, sheet_name=sheet_name)

# Access the XlsxWriter workbook and worksheet objects from the dataframe.
workbook = writer.book
worksheet = writer.sheets[sheet_name]

# Create a chart object.
chart = workbook.add_chart({'type': 'pie'})

# Configure the chart from the dataframe data.
chart.add_series({
    'categories': ['Sheet1', 0, 1, 0, 5],
    'values':     ['Sheet1', 1, 1, 1, 5],
})

# Insert the chart into the worksheet.
worksheet.insert_chart('A4', chart)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Also, note the use of the list for the Categories and Values instead of range strings. This optional format is easier where dealing with variable data and it handles any sheet name quoting.

Output:

enter image description here

Upvotes: 1

Julien Marrec
Julien Marrec

Reputation: 11905

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

Upvotes: 5

Related Questions