CODEWITHSUNDEEP
pythonpandasxlsxwriter
Saara Jackman
Saara Jackman

Reputation: 47

Xlsxwriter Chart Lables not working in Pie Chart

I am using python and xlsxwriter to create tables and charts in an Excel file. When I add lables in the chart they do not show up.

Any help would be appriciated.

Code:

chart1a.add_series({
    'name': '=Totals_FY!$C$3',
    'categories': '=Totals_FY!$B$4:$B$' + str(types_pivot.shape[0]+3), 
    'values': '=Totals_FY!$D$4:$D$' + str(types_pivot.shape[0]+3),
    'data_labels': {
        'value':True, 
        'category_name':True, 
        'position':'outside_end'
    })

Chart1a

 Type   Year 1  Year 2
     a   150    69
     b   48     25
     c   9      0
     d   0      1
     e   67     44
     f   0      3
     g   46     19
     h   0      1

Upvotes: 1

Views: 356

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

There is a typo in the data_labels parameter ('category_name' -> 'category'). Apart from that it should work as expected.

Here is an example based on yours:

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Type':   ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'],
                   'Year 1': [150, 48, 9, 0, 67, 0, 46, 0],
                   'Year 2': [69, 25, 0, 1, 44, 3, 19, 1]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_chart.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Totals_FY', startrow=2)

# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Totals_FY']

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

# Configure the series of the chart from the dataframe data.
chart.add_series({
    'name': '=Totals_FY!$C$3',
    'categories': '=Totals_FY!$B$4:$B$' + str(df.shape[0] + 3),
    'values': '=Totals_FY!$D$4:$D$' + str(df.shape[0] + 3),
    'data_labels': {
        'value': True,
        'category': True,
        'position': 'outside_end'}
})

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

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

Output:

enter image description here

Note, in this type of program where you are creating a chart from a dataframe it is better to use the list syntax for add_series():

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Type':   ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'],
                   'Year 1': [150, 48, 9, 0, 67, 0, 46, 0],
                   'Year 2': [69, 25, 0, 1, 44, 3, 19, 1]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_chart.xlsx', engine='xlsxwriter')

my_sheet_name = 'Totals_FY'

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name=my_sheet_name, startrow=2)

# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets[my_sheet_name]

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

# Configure the series of the chart from the dataframe data.
max_row = df.shape[0] + 2
chart.add_series({
    'name': [my_sheet_name, 2, 2],
    'categories': [my_sheet_name, 3, 1, max_row, 1],
    'values': [my_sheet_name, 4, 4, max_row, 4],
    'data_labels': {
        'value': True,
        'category': True,
        'position': 'outside_end'}
})

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

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

Upvotes: 1

Related Questions