Creating Pie Chart In Python

I have created my pie chart but right now I am using a range of cells like this: chart3.add_series({ 'name': 'Pie data', 'categories': '=Pivots!$A$3:$A$10', 'values':

Solution 1:

Assuming that it's an option for you to dynamically hide the rows without data, then there's a fairly straightforward solution.

I reworked some sample data that are used in the xlsxwriter docs and then looked for any Nan values. If there are any then those are hidden and thus not in the pie chart.

import xlsxwriter
import pandas as pd

headings = ['Category', 'Values']
data = [
['Apple', 30],['Cherry', 20],['Pecan',15],['Blueberry', 10],['Pumpkin', 10],
['Mince', float('nan')],['Custard', 3],['Potato', 2],
]


pies = pd.DataFrame(data, columns = headings)

nan_rows = pies.loc[pies['Values'].isnull()].index.values
hide_rows = nan_rows + 2

workbook = xlsxwriter.Workbook('test.xlsx', {'strings_to_numbers':  True,
                                             'strings_to_formulas': True,
                                             'nan_inf_to_errors': True})

ws = workbook.add_worksheet('Pivots')

ws.write('A2', headings[0])
ws.write('F2', headings[1])
ws.write_column('A3', pies['Category'])
ws.write_column('F3', pies['Values'])


chart3 = workbook.add_chart({'type': 'pie'})

chart3.add_series({
'name': 'Pie data',
'categories': '=Pivots!$A$3:$A$10',
'values':     '=Pivots!$F$3:$F$10'})

chart3.set_title({'name': 'Popular Pie Types'})

chart3.set_style(10)
ws.insert_chart('G11', chart3, {'x_offset': 25, 'y_offset': 10})

#This is where we hide the rows in list called hide_rowsfor row_position in hide_rows:
    ws.set_row(row_position, None, None, {'hidden': True})

workbook.close()