{"id":1568,"date":"2022-02-24T19:00:10","date_gmt":"2022-02-24T19:00:10","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=1568"},"modified":"2024-11-19T16:08:58","modified_gmt":"2024-11-19T16:08:58","slug":"animated-plots-with-matplotlib-in-excel","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/animated-plots-with-matplotlib-in-excel\/","title":{"rendered":"Animated Plots with matplotlib in Excel"},"content":{"rendered":"\n

Animated plots are a great way to liven up a spreadsheet or show a complex data set in a more intuitive way. We can use matplotlib to create animations and display them directly in Excel using PyXLL’s plotting capabilities<\/a>.<\/p>\n\n\n\n

Preparing some example data<\/h2>\n\n\n\n

To begin with we need some data to plot. I’ve chosen a dataset from the plotly examples we we will load directly from the web, but you could plot data directly from a worksheet or loaded from another source such as a database.<\/p>\n\n\n

\nfrom pyxll import xl_func\nimport pandas as pd\n\n\n@xl_func\ndef load_data():\n    url = "https:\/\/raw.githubusercontent.com\/plotly\/datasets\/master\/volcano.csv"\n    df = pd.read_csv(url)\n\n    # Transform it to a long format\n    df = df.unstack().reset_index()\n    df.columns = ["X", "Y", "Z"]\n\n    # And transform the old column name in something numeric\n    df['X'] = pd.Categorical(df['X'])\n    df['X'] = df['X'].cat.codes\n\n    return df\n<\/pre><\/div>\n\n\n
This function above loads the data from github into a pandas DataFrame and shuffles it into the format ready for plotting with the columns ‘X’, ‘Y’ and ‘Z’. The @xl_func<\/a><\/code> decorator exposes this function to Excel so we can call it directly from our worksheet as =load_data()<\/code>.<\/p>\n\n\n\n
Creating a 3d plot of our data<\/h2>\n\n\n\n
Next we’re ready to plot our data. Let’s start by writing another function which will plot the DataFrame returned by the first function. It would work just as well with any DataFrame from another data source with the columns X, Y and Z. We could easily pass it a range directly from the worksheet if we had some suitable data.<\/p>\n\n\n
\nfrom pyxll import xl_func, plot\nimport matplotlib.pyplot as plt\n\n@xl_func("dataframe: str")\ndef plot_3d(df):\n    # Create the matplotlib figure\n    fig = plt.figure(facecolor='white')\n    ax = fig.gca(projection='3d')\n\n    # Plot our X, Y, Z data\n    ax.plot_trisurf(df['Y'], df['X'], df['Z'], cmap=plt.cm.viridis, linewidth=0.2)\n    ax.view_init(30, 60)\n\n    # Show the plot in Excel\n    plot(fig)\n\n    # Return a string to Excel\n    return "[OK!]"\n<\/pre><\/div>\n\n\n
When we call this function with our loaded DataFrame the plot shows in Excel using the pyxll.plot<\/code><\/a> function. Next we’ll make it animate!<\/p>\n\n\n\n
\"\"<\/a>
Our 3d data plotted in Excel<\/figcaption><\/figure>\n\n\n\n
Animating the 3d plot<\/h2>\n\n\n\n
To make this plot animated we’ll use matplotlib.animation.FuncAnimation<\/a><\/code>. The FuncAnimation class takes a function as an argument that will be called several times to update the plot and build up the frames of our animation. In each frame we want to rotate the graph, which is done by calling ax.view_init(30, angle)<\/code> with the angle we want to set. We’ll do this for angles between 0 and 360 degrees to get a complete rotation.<\/p>\n\n\n\n
The dataset I’ve chosen is quite large and rendering the whole animation takes a little bit of time. Depending on the size of your data set you may want to use more or fewer frames to get the right balance between a smooth animation and not having to wait too long for it to render.<\/p>\n\n\n
\nfrom pyxll import xl_func, plot\nfrom matplotlib.animation import FuncAnimation\nimport matplotlib.pyplot as plt\n\n@xl_func("dataframe: str")\ndef plot_3d(df):\n    fig = plt.figure(facecolor='white')\n    ax = fig.gca(projection='3d')\n\n    def init():\n        ax.plot_trisurf(df['Y'], df['X'], df['Z'], cmap=plt.cm.viridis, linewidth=0.2)\n\n    # The animate function is called for each frame of the animation\n    def animate(angle):\n        ax.view_init(30, angle)\n\n    anim = FuncAnimation(fig,\n                         animate,\n                         init_func=init,\n                         frames=range(0, 360, 10),\n                         interval=200)\n\n    # Show the animation in Excel\n    plot(anim)\n\n    # Return a string to Excel\n    return "[OK!]"\n<\/pre><\/div>\n\n\n
With some small changes to our original plot_3d function we’re now producing an animated plot of our data set. The animate function is called for each angle in the range passed to FuncAnimation, rotating our 3d plot 360 degrees to create the animation. Calling the pyxll.plot<\/a><\/code> function renders the animation into a shape object in Excel.<\/p>\n\n\n\n
\"Animated<\/a>
An animated 3d plot in Excel using matplotlib and PyXLL.<\/figcaption><\/figure>\n\n\n\n
For more information about how to plot using matplotlib and PyXLL please see the following resources:<\/p>\n\n\n\n