Example: Pareto Chart#
Example of creating a Pareto chart with a secondary chart and axis.

#######################################################################
#
# An example of creating of a Pareto chart with Python and XlsxWriter.
#
# SPDX-License-Identifier: BSD-2-Clause
# Copyright 2013-2023, John McNamara, jmcnamara@cpan.org
#
import xlsxwriter
workbook = xlsxwriter.Workbook("chart_pareto.xlsx")
worksheet = workbook.add_worksheet()
# Formats used in the workbook.
bold = workbook.add_format({"bold": True})
percent_format = workbook.add_format({"num_format": "0.0%"})
# Widen the columns for visibility.
worksheet.set_column("A:A", 15)
worksheet.set_column("B:C", 10)
# Add the worksheet data that the charts will refer to.
headings = ["Reason", "Number", "Percentage"]
reasons = [
"Traffic",
"Child care",
"Public Transport",
"Weather",
"Overslept",
"Emergency",
]
numbers = [60, 40, 20, 15, 10, 5]
percents = [0.44, 0.667, 0.8, 0.9, 0.967, 1]
worksheet.write_row("A1", headings, bold)
worksheet.write_column("A2", reasons)
worksheet.write_column("B2", numbers)
worksheet.write_column("C2", percents, percent_format)
# Create a new column chart. This will be the primary chart.
column_chart = workbook.add_chart({"type": "column"})
# Add a series.
column_chart.add_series(
{
"categories": "=Sheet1!$A$2:$A$7",
"values": "=Sheet1!$B$2:$B$7",
}
)
# Add a chart title.
column_chart.set_title({"name": "Reasons for lateness"})
# Turn off the chart legend.
column_chart.set_legend({"position": "none"})
# Set the title and scale of the Y axes. Note, the secondary axis is set from
# the primary chart.
column_chart.set_y_axis({"name": "Respondents (number)", "min": 0, "max": 120})
column_chart.set_y2_axis({"max": 1})
# Create a new line chart. This will be the secondary chart.
line_chart = workbook.add_chart({"type": "line"})
# Add a series, on the secondary axis.
line_chart.add_series(
{
"categories": "=Sheet1!$A$2:$A$7",
"values": "=Sheet1!$C$2:$C$7",
"marker": {"type": "automatic"},
"y2_axis": 1,
}
)
# Combine the charts.
column_chart.combine(line_chart)
# Insert the chart into the worksheet.
worksheet.insert_chart("F2", column_chart)
workbook.close()