CODEWITHSUNDEEP
pythonexcelpandaschartsxlsxwriter
Michael
Michael

Reputation: 33

Xslxwriter column chart data labels percentage property not working

I am creating some column charts in Excel (2016) for a report by creating tables with python (3.5.3) pandas(0.20.1) and creating tables and charts with xlsxwriter (0.9.6). I would like to label each column with the percentage of the value relative to the sum of all values, like in this image: Desired Output Chart

Based on my understanding of the Xslxwriter documentation, I need only include the 'data_labels':{'percentage': True} property when I add a series to a chart, as I do in the following code:

df = pandas.DataFrame(data=[1,2,3,4,5],index=['a','b','c','d','e'])
writer = pandas.ExcelWriter('C:/Users/Name_A_Place/sandbox.xlsx')
df.to_excel(writer)
wrkbk = writer.book
wrksht = writer.sheets['Sheet1']
chrt = wrkbk.add_chart({'type':'column'})

chrt.add_series({'categories': '=Sheet1!A2:A6',
                 'values': '=Sheet1!B2:B6',
                 'data_labels': {'percentage':True,
                                 'font': {'rotation':-45}},
                 })

wrksht.insert_chart("E1",chrt)
writer.save()

However, this code creates a chart without labels: Actual Output Chart.

Am I misunderstanding how the percentage data label property works?

I have been working around this issue by creating a separate percentage column and labeling with the 'data_labels': {'value': True} property using the values from that column, but if someone could explain why my code did not produce the output I desired, I would appreciate it.

Upvotes: 3

Views: 2068

Answers (2)

Sankar guru
Sankar guru

Reputation: 1015

The percentage property is used to turn on the display of data labels as a Percentage for a series. It is mainly used for pie charts:

import pandas as pd
df = pd.DataFrame(data=[1,2,3,4,5],index=['a','b','c','d','e'])
writer = pd.ExcelWriter('C:/Users/Name_A_Place/sandbox.xlsx')
df.to_excel(writer)
wrkbk = writer.book
wrksht = writer.sheets['Sheet1']
chrt = wrkbk.add_chart({'type':'pie'})

chrt.add_series({'categories': '=Sheet1!A2:A6',
                 'values': '=Sheet1!B2:B6',
                 'data_labels': {'percentage':True,
                                 'font': {'rotation':-45}},
                 })

wrksht.insert_chart("E1",chrt)
writer.save()

Upvotes: 2

Josh
Josh

Reputation: 2835

I think you're just bumping up against limitations of Excel that are not documented clearly in the XlsxWriter documentation. If you open up Excel and create the charts by hand you'll notice that "percentage" option for data labels is not present for bar or column charts (but is for pie and perhaps others). You'll need to continue to use data labels by using separate cells ("value from cells"). I've opened an issue on XlsxWriter GitHub to try and get the docs improved.

A pie chart with percentage data label optionExcel pie chart with percentage data label

A column chart with no percentage data label option Excel column chart with no percentage data label

Upvotes: 2

Related Questions