Using Matplotlib from Excel with pyxll

The pyxll documentation has many examples of plotting in Excel using Matplotlib and other packages, but I find the multiple options confusing and hard to follow, so this post works through the examples in the Matplotlib Users Guide tutorial. The sample spreadsheet and python code can be downloaded from:

Matplotlib Tute.zip

Note that all the examples below were taken from the manual for Release 2.0.2. The current release is 3.4.3, and includes several additional examples, and considerably more background information.

The first example plots a simple line graph using hard coded data. I have added a modified version that will transfer the data from a selected row on the spreadsheet.

import matplotlib.pyplot as plt
from matplotlib import colors

import pyxll
from pyxll import xl_func, xl_arg, xl_return
from pyxll import  plot

@xl_func
def MPLTute_1():
    # Create the figure
    fig = plt.subplots()[0]
    # Plot the data
    plt.plot([1,2,3,4])
    plt.plot([0,2,4,6])
    plt.ylabel('some numbers')
    # Display the figure in Excel
    plot(fig)

@xl_func
@xl_arg('data','float[]')
def MPLTute_1a(data):
    fig, ax = plt.subplots()
    ax.plot(data)
    ax.set(ylabel='some numbers')
    plot(fig)

The first function follows the code in the tutorial as closely as possible, with the addition of a second plotted line. The second function, in addition to reading the data to be plotted from the spreadsheet, follows the coding used in the pyxll examples more closely.

The next example plots XY data, read from the spreadsheet as a list of lists. The string ‘ro’ plots the line as red circles for each data point (see the manual for details):

@xl_func
@xl_arg('data','float[][]')
def MPLTute_2(data):
    fig, ax = plt.subplots()
    ax.plot(data[0], data[1], 'ro')
    ax.set_xbound(0, 6)
    ax.set_ybound(0, 20)
    ax.set(ylabel='some numbers')
    plot(fig)

Function 3 plots 3 lines, each with a different format string. The lines are hard coded functions of the range “t”. The lines are created with a single .plot, as a sequence of 3 sets of X values, Y values, line format string:

@xl_func
def MPLTute_3():
    t = np.arange(0., 5., 0.2)
    fig, ax = plt.subplots()
    ax.plot(t, t, 'r--', t, t**2, 'bs', t, t**3, 'g^')
    plot(fig)

Function 4 plots one of the lines from the previous example, but instead of using a single string to format the line, a dictionary is passed from the spreadsheet, allowing multiple format properties to be specified:

@xl_func
@xl_arg('props','dict<str, var>')
def MPLTute_4(props):
    x = np.arange(0., 5., 0.2)
    y = x**2
    fig, ax = plt.subplots()
    ax.plot(x, y, **props)
    plot(fig)

The spreadsheet includes a list of available properties, but see the manual for full details:

Function 5 plots a histogram, using the plt.hist method:

@xl_func
def MPLTute_5():
    np.random.seed(19680801)
    mu, sigma = 100, 15
    x = mu + sigma * np.random.randn(10000)
    
    fig, ax = plt.subplots()
    # the histogram of the data
    # Changed from the tutorial example: "n, bins, patches = plt.hist(x, 50, normed=1, facecolor='g', alpha=0.75)" 
    # because use of normed now raises an error.
    n, bins, patches = plt.hist(x, 50, density=1, facecolor='g', alpha=0.75)
 
    plt.xlabel('Smarts')
    plt.ylabel('Probability')
    plt.title('Histogram of IQ')
    plt.text(60, .025, r'$\mu=100,\ \sigma=15$')
    plt.axis([40, 160, 0, 0.03])
    plt.grid(True)
    
    plot(fig)

The comment noting the change from the code in the tutorial refers to the document for Release 2.02. The current tutorial (Release 3.4.3) has been corrected.

Function 6 illustrates the addition of text and graphics to the graph:

@xl_func
def MPLTute_6():
    fig, ax = plt.subplots()
    t = np.arange(0.0, 5.0, 0.01)
    s = np.cos(2*np.pi*t)
    line, = plt.plot(t, s, lw=2)
    plt.annotate('local max', xy=(2, 1), xytext=(3, 1.5),
    arrowprops=dict(facecolor='black', shrink=0.05),)
    plt.ylim(-2,2)

    plot(fig)

Finally Functions 7 and 7a illustrate the use of different axis types, and returning multiple graphs from a single function:

@xl_func
@xl_arg('scaletype', 'int')
def MPLTute_7(scaletype):
    fig, ax = plt.subplots()
    from matplotlib.ticker import NullFormatter # useful for `logit` scale
    # Fixing random state for reproducibility
    np.random.seed(19680801)
    # make up some data in the interval ]0, 1[
    y = np.random.normal(loc=0.5, scale=0.4, size=1000)
    y = y[(y > 0) & (y < 1)]
    y.sort()
    x = np.arange(len(y))
    # plot with selected axes scale
    if scaletype == 1:
        # plt.figure(1)
        # # linear
        # plt.subplot(221)
        plt.plot(x, y)
        plt.yscale('linear')
        plt.title('linear')
        plt.grid(True)
        # log
    elif scaletype == 2:
        # plt.subplot(222)
        plt.plot(x, y)
        plt.yscale('log')
        plt.title('log')
        plt.grid(True)
        # symmetric log
    elif scaletype == 3:
        # plt.subplot(223)
        plt.plot(x, y - y.mean())
        plt.yscale('symlog', linthreshy=0.01)
        plt.title('symlog')
        plt.grid(True)
        # logit
    elif scaletype == 4:
        # plt.subplot(224)
        plt.plot(x, y)
        plt.yscale('logit')
        plt.title('logit')
        plt.grid(True)
    else:
        return 'scaletype must be between 1 and 4'
    # Format the minor tick labels of the y-axis into empty strings with
    # `NullFormatter`, to avoid cumbering the axis with too many labels.
    plt.gca().yaxis.set_minor_formatter(NullFormatter())
    # Adjust the subplot layout, because the logit one may take more space
    # than usual, due to y-tick labels like "1 - 10^{-3}"
    # Not required, Excel version returns only 1 chart
    # plt.subplots_adjust(top=0.92, bottom=0.08, left=0.10, right=0.95, hspace=0.25,
    # wspace=0.35)

    plot(fig)


@xl_func
def MPLTute_7a():
    # Multiplot version
    fig, ax = plt.subplots()
    from matplotlib.ticker import NullFormatter # useful for `logit` scale
    # Fixing random state for reproducibility
    np.random.seed(19680801)
    # make up some data in the interval ]0, 1[
    y = np.random.normal(loc=0.5, scale=0.4, size=1000)
    y = y[(y > 0) & (y < 1)]
    y.sort()
    x = np.arange(len(y))
    # plot with various axes scales
    
    fig = plt.figure(1)
    # # linear
    plt.subplot(221)
    plt.plot(x, y)
    plt.yscale('linear')
    plt.title('linear')
    plt.grid(True)
    # log
    plt.subplot(222)
    plt.plot(x, y)
    plt.yscale('log')
    plt.title('log')
    plt.grid(True)
    # symmetric log
    plt.subplot(223)
    plt.plot(x, y - y.mean())
    plt.yscale('symlog', linthreshy=0.01)
    plt.title('symlog')
    plt.grid(True)
    # logit
    plt.subplot(224)
    plt.plot(x, y)
    plt.yscale('logit')
    plt.title('logit')
    plt.grid(True)

    # Format the minor tick labels of the y-axis into empty strings with
    # `NullFormatter`, to avoid cumbering the axis with too many labels.
    plt.gca().yaxis.set_minor_formatter(NullFormatter())
    # Adjust the subplot layout, because the logit one may take more space
    # than usual, due to y-tick labels like "1 - 10^{-3}"
    plt.subplots_adjust(top=0.92, bottom=0.08, left=0.10, right=0.95, hspace=0.25,
    wspace=0.35)

    plot(fig)

This entry was posted in Charts, Charts, Drawing, Excel, Link to Python, PyXLL, UDFs and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.