CORRELATION

Excel Usage

=CORRELATION(data, title, corr_colors, values, colorbar)

• data (list[list], required): Input variables.
• title (str, optional, default: null): Chart title.
• corr_colors (str, optional, default: “coolwarm”): Color map.
• values (str, optional, default: “true”): Show values.
• colorbar (str, optional, default: “true”): Show colorbar.

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

Examples

Example 1: Correlation matrix from 3 variables

Inputs:

data
1	2	3	4	5
2	4	6	8	10
5	4	3	2	1

Excel formula:

=CORRELATION({1,2,3,4,5;2,4,6,8,10;5,4,3,2,1})

Expected output:

"chart"

Example 2: Correlation matrix with custom title

Inputs:

data			title
1	2	3	My Correlation
4	5	6
7	8	9

Excel formula:

=CORRELATION({1,2,3;4,5,6;7,8,9}, "My Correlation")

Expected output:

"chart"

Example 3: Correlation with Blues colormap

Inputs:

data			corr_colors
1	2	3	Blues
2	3	4

Excel formula:

=CORRELATION({1,2,3;2,3,4}, "Blues")

Expected output:

"chart"

Example 4: Correlation showing values

Inputs:

data				values
1	2	3	4	true
5	6	7	8

Excel formula:

=CORRELATION({1,2,3,4;5,6,7,8}, "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 correlation(data, title=None, corr_colors='coolwarm', values='true', colorbar='true'):
    """
    Create a correlation matrix heatmap from data.

    See: https://matplotlib.org/stable/gallery/images_contours_and_fields/image_annotated_heatmap.html

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

    Args:
        data (list[list]): Input variables.
        title (str, optional): Chart title. Default is None.
        corr_colors (str, optional): Color map. Valid options: Coolwarm, Viridis, Blues, Reds. Default is 'coolwarm'.
        values (str, optional): Show values. Valid options: True, False. Default is 'true'.
        colorbar (str, optional): Show colorbar. Valid options: True, False. Default is 'true'.

    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 not all(isinstance(row, list) for row in data):
            return "Error: Invalid input - data must be a 2D list"

        # Convert to numpy array
        try:
            arr = np.array(data, dtype=float)
        except (ValueError, TypeError) as e:
            return f"Error: Could not convert data to numeric array: {str(e)}"

        if arr.ndim != 2:
            return "Error: Data must be a 2D array"

        if arr.size == 0:
            return "Error: Data array is empty"

        # Transpose to get variables in columns
        if arr.shape[0] > arr.shape[1]:
            arr = arr.T

        # Calculate correlation matrix
        try:
            corr_matrix = np.corrcoef(arr)
        except Exception as e:
            return f"Error: Could not calculate correlation matrix: {str(e)}"

        if corr_matrix.ndim == 0:
            # Single variable case
            corr_matrix = np.array([[1.0]])

        # Create figure
        fig, ax = plt.subplots(figsize=(8, 6))

        # Create heatmap
        im = ax.imshow(corr_matrix, cmap=corr_colors, aspect='auto', vmin=-1, vmax=1)

        # Add colorbar if requested
        show_colorbar = colorbar.lower() == "true"
        if show_colorbar:
            plt.colorbar(im, ax=ax)

        # Add values if requested
        show_values = values.lower() == "true"
        if show_values:
            for i in range(corr_matrix.shape[0]):
                for j in range(corr_matrix.shape[1]):
                    text = ax.text(j, i, f'{corr_matrix[i, j]:.2f}',
                                 ha="center", va="center", color="black")

        # Set labels
        if title:
            ax.set_title(title)
        else:
            ax.set_title("Correlation Matrix")

        # Set ticks
        n = corr_matrix.shape[0]
        ax.set_xticks(range(n))
        ax.set_yticks(range(n))
        ax.set_xticklabels([f"Var{i+1}" for i in range(n)])
        ax.set_yticklabels([f"Var{i+1}" for i in range(n)])

        plt.tight_layout()

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

Online Calculator