CODEWITHSUNDEEP
pythonexcelpandascharts
Alfred
Alfred

Reputation: 3

Using pandas excel workbook to create pie chart from 1 column

I am trying to create an excel pie chart from a column within a DF that's has 2 values that repeats multiple times ("PASS,FAIL").

enter image description here

I want to have a pie chart that show how many passes and how many fails

excel_file = 'pie.xlsx'
sheet_name = 'Sheet1'
writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')
df.to_excel(writer, sheet_name=sheet_name)
df.to_excel("output.xlsx")
workbook = writer.book
worksheet = writer.sheets[sheet_name]
chart = workbook.add_chart({'type': 'pie'})
chart.add_series({
    'categories': '=Sheet1!D2:D233',
    'values':     '=Sheet1\'Total Counts\'D2:D233'
})
worksheet.insert_chart('G2', chart)
writer.save()

the code results in a blank pie chart

Upvotes: 0

Views: 1980

Answers (2)

jmcnamara
jmcnamara

Reputation: 41574

You can't plot a Pie chart in Excel from a range of values and have them grouped into matching categories and values. Instead you probably have to add some sum/count formulas and plot the results of those instead.

Here is a small working example based on yours:

import pandas as pd
from xlsxwriter.utility import xl_range

# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': ['Fail', 'Pass', 'Fail', 'Pass', 'Pass']})

# Convert the dataframe to an Excel file.
excel_file = 'pie.xlsx'
sheet_name = 'Sheet1'
writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')
df.to_excel(writer, sheet_name=sheet_name)

# Get the max row number for the dataframe.
max_row = len(df)
cell_range = xl_range(1, 1, max_row, 1)

# Access the Pandas xlsxwriter Excel file.
workbook = writer.book
worksheet = writer.sheets[sheet_name]

# Write some text to act as chart category labels.
worksheet.write('D2', "Pass");
worksheet.write('D3', "Fail");

# Count the instances of Pass/Fail to act as chart values.
worksheet.write_formula('E2', '=COUNTIF(%s, "Pass")' % cell_range);
worksheet.write_formula('E3', '=COUNTIF(%s, "Fail")' % cell_range);

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

# Add the chart series.
chart.add_series({
    'categories': '=Sheet1!D2:D3',
    'values':     '=Sheet1!E2:E3'
})

# Insert the chart.
worksheet.insert_chart('A8', chart)

writer.save()

Output:

enter image description here

Upvotes: 1

Lumber Jack
Lumber Jack

Reputation: 622

Here the way to do with a simple example :

df = pd.DataFrame({'col1': [120, 216 , 72],'col2': [12, 24, 48]},index=['A', 'B', 'C'])
plot = df.plot.pie(y='col1', figsize=(5, 5))

OUTPUT:

enter image description here

Upvotes: 0

Related Questions