PARETO

Excel Usage

=PARETO(data, title, xlabel, ylabel, color_bar, color_line, legend)

• data (list[list], required): Input data (Labels, Values).
• title (str, optional, default: null): Chart title.
• xlabel (str, optional, default: null): Label for X-axis.
• ylabel (str, optional, default: null): Label for Y-axis.
• color_bar (str, optional, default: “blue”): Bar color.
• color_line (str, optional, default: “red”): Line color.
• legend (str, optional, default: “false”): Show legend.

Returns (object): Matplotlib Figure object (standard Python) or base64 encoded PNG string (Pyodide).

Examples

Example 1: Simple Pareto chart

Inputs:

data
Defect A	50
Defect B	30
Defect C	15
Defect D	5

Excel formula:

=PARETO({"Defect A",50;"Defect B",30;"Defect C",15;"Defect D",5})

Expected output:

"chart"

Example 2: Pareto chart with custom labels

Inputs:

data		title	xlabel	ylabel
Issue 1	100	Defect Analysis	Defect Type	Count
Issue 2	80
Issue 3	40
Issue 4	20

Excel formula:

=PARETO({"Issue 1",100;"Issue 2",80;"Issue 3",40;"Issue 4",20}, "Defect Analysis", "Defect Type", "Count")

Expected output:

"chart"

Example 3: Pareto with custom colors

Inputs:

data		color_bar	color_line
Cat A	70	green	yellow
Cat B	50
Cat C	30

Excel formula:

=PARETO({"Cat A",70;"Cat B",50;"Cat C",30}, "green", "yellow")

Expected output:

"chart"

Example 4: Pareto chart with legend

Inputs:

data		legend
Problem 1	200	true
Problem 2	150
Problem 3	100
Problem 4	50

Excel formula:

=PARETO({"Problem 1",200;"Problem 2",150;"Problem 3",100;"Problem 4",50}, "true")

Expected output:

"chart"

Python Code

import sys
import matplotlib
IS_PYODIDE = sys.platform == "emscripten"
if IS_PYODIDE:
    matplotlib.use('Agg')
import matplotlib.pyplot as plt
import io
import base64
import numpy as np

def pareto(data, title=None, xlabel=None, ylabel=None, color_bar='blue', color_line='red', legend='false'):
    """
    Create a Pareto chart (bar chart + cumulative line).

    See: https://matplotlib.org/stable/gallery/showcase/pareto_chart.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Input data (Labels, Values).
        title (str, optional): Chart title. Default is None.
        xlabel (str, optional): Label for X-axis. Default is None.
        ylabel (str, optional): Label for Y-axis. Default is None.
        color_bar (str, optional): Bar color. Valid options: Blue, Green, Cyan. Default is 'blue'.
        color_line (str, optional): Line color. Valid options: Red, Yellow, Magenta. Default is 'red'.
        legend (str, optional): Show legend. Valid options: True, False. Default is 'false'.

    Returns:
        object: Matplotlib Figure object (standard Python) or base64 encoded PNG string (Pyodide).
    """
    def to2d(x):
        return [[x]] if not isinstance(x, list) else x

    try:
        data = to2d(data)

        if not isinstance(data, list) or len(data) < 1:
            return "Error: Data must be a non-empty list"

        # Extract labels and values
        labels = []
        values = []
        for row in data:
            if not isinstance(row, list) or len(row) < 2:
                continue
            try:
                labels.append(str(row[0]))
                values.append(float(row[1]))
            except (ValueError, TypeError):
                continue

        if len(labels) == 0 or len(values) == 0:
            return "Error: No valid data rows found"

        if any(v < 0 for v in values):
            return "Error: Values must be non-negative"

        # Sort by values descending
        sorted_pairs = sorted(zip(values, labels), reverse=True)
        values = [p[0] for p in sorted_pairs]
        labels = [p[1] for p in sorted_pairs]

        # Calculate cumulative percentage
        total = sum(values)
        if total == 0:
            return "Error: Total value is zero"

        cumulative = np.cumsum(values)
        cumulative_percent = cumulative / total * 100

        # Create figure with two y-axes
        fig, ax1 = plt.subplots(figsize=(10, 6))

        # Bar chart
        x_pos = np.arange(len(labels))
        ax1.bar(x_pos, values, color=color_bar, alpha=0.7, edgecolor='black')
        ax1.set_xlabel(xlabel if xlabel else 'Categories')
        ax1.set_ylabel(ylabel if ylabel else 'Frequency', color=color_bar)
        ax1.tick_params(axis='y', labelcolor=color_bar)
        ax1.set_xticks(x_pos)
        ax1.set_xticklabels(labels, rotation=45, ha='right')

        # Line chart for cumulative percentage
        ax2 = ax1.twinx()
        ax2.plot(x_pos, cumulative_percent, color=color_line, marker='o', linewidth=2)
        ax2.set_ylabel('Cumulative Percentage (%)', color=color_line)
        ax2.tick_params(axis='y', labelcolor=color_line)
        ax2.set_ylim([0, 105])
        ax2.axhline(y=80, color='gray', linestyle='--', linewidth=1)

        if title:
            ax1.set_title(title)

        # Handle legend
        if legend == "true":
            from matplotlib.patches import Patch
            from matplotlib.lines import Line2D
            legend_elements = [
                Patch(facecolor=color_bar, label='Frequency'),
                Line2D([0], [0], color=color_line, marker='o', label='Cumulative %')
            ]
            ax1.legend(handles=legend_elements, loc="upper left")

        plt.tight_layout()

        if IS_PYODIDE:
            buf = io.BytesIO()
            plt.savefig(buf, format='png', bbox_inches='tight')
            plt.close(fig)
            buf.seek(0)
            img_base64 = base64.b64encode(buf.read()).decode('utf-8')
            return f"data:image/png;base64,{img_base64}"
        else:
            return fig
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator