Better Excel Charts
Excel charts are one of the most common tools for visualizing and presenting data. While they're fine for basic analysis, you can make much better graphs with matplotlib or another high-caliber graphing tool. You can use DataNitro to output pylab charts directly into Excel, avoiding Excel charts altogether.
Plotting a lot of data
Here's what a decaying sine curve looks like in python:
And here's the same plot in Excel:
Excel's rendering engine makes the graph line unreadable; this is unacceptable for important data.
Setting up the chart is just as easy in Python as in Excel. If your data is in columns A and B, here's one way to do it:
import numpy as np import nitroplot active_sheet("sine") t, s = Cell("A1").vertical, Cell("B1").vertical nitroplot.plot(t, s) nitroplot.graph()
While you may not graph a sine curve every day, nice graphs are also helpful in more common applications. For example, here's three years worth of stock data for Apple, with the Python plot on the left and the Excel chart on the right:
Stock data, plotted in Python on the left and in Excel on the right
Even with a basic line chart, the Python plot is clearer. If you start adding more information to the chart, the Excel chart can quickly become unreadable, while the Python plot doesn't.
Another advantage of sophisticated plotting is the range of plots you can use. While line charts are more or less the limit of what Excel can do, Python can do much more. For example, here's a graph of a mandelbrot fractal:
The mandelbrot fractal, plotted in Python
There's no way to make something like this in Excel. You can handle the calculations and plotting in Python, with key parameters stored in Excel for easy updating:
from numpy import * import nitroplot def mandelbrot( h,w, maxit=20, d = 2 ): y,x = ogrid[ -1.4:1.4:h*1j, -2:0.8:w*1j ] c = x+y*1j z = c divtime = maxit + zeros(z.shape, dtype=int) for i in xrange(maxit): z = z**d + c diverge = z*conj(z) > 2**2 # who is diverging div_now = diverge & (divtime==maxit) # who is diverging now divtime[div_now] = i # note when z[diverge] = 2 # avoid diverging too much return divtime active_sheet("fractal") nitroplot.imshow(mandelbrot(400, 400, d = Cell("B1").value)) nitroplot.graph()
This makes it easy to create interactive visualizations:
Do you have data stuck in Excel that's hard to chart natively? Give this a try!
(You can download the examples from this post here.)