Adding Sparkline to Pandas Dataframe

I would like to draw and append sparklines to my dataframe. I have worked through a bunch of examples which ended up running into Base64 encoded output. I was hoping to use a simple approach, perhaps using matplotlib. I need to visualise linecharts for each row, I am unsure whether a plt figure can be appended to a dataframe cell? My guess is no.

My best attempt is below:

## Imports    

from scipy import stats
import matplotlib.pyplot as plt
%matplotlib inline
import sparklines
import base64
from io import BytesIO
import matplotlib
matplotlib.use("Agg")

## Create example dataset

def sparkline(data, figsize=(4,0.25),**kwags):
    data = list(data)
    fig,ax = plt.subplots(1,1,figsize=figsize,**kwags)
    ax.plot(data)
    for k,v in ax.spines.items():
        v.set_visible(False)
    ax.set_xticks([])
    ax.set_yticks([])
    
    plt.plot(len(data)-1, data[len(data)-1], 'r.')
    
    ax.fill_between(range(len(data)), data, len(data)*[min(data)], alpha=0.1)
    
    img = BytesIO()
    plt.savefig(img, transparent=True, bbox_inches='tight')
    img.seek(0)
    plt.close()
    
    return base64.b64encode(img.read()).decode("UTF-8")

## Create sparkline (output is Base64 sting - Should be linechart)

df = pd.DataFrame(price, columns=['Close'])
df = df.T
df['Trend'] = df.apply(sparkline, axis=1)
pd.set_option('display.max_colwidth', -1)
df

Not sure why my script doesn’t decode the Base64 string to a linechart. Is there an easy way?

Current Output: Close iVBORw0KGgoAAAANSUhEUgAAAO0AAAAbCAYAAACQhgiLAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8QZhcZAAAFEElEQVR4nO3afYxcVRnH8c9daBdKMVSpadSYaxRMiQKp+NJgG19AozdUNESiiSb8AdYYNEYhV3xLNIRrQkLUqCghVCNSUFMCvYoCajQR62vUCCnVeCMtGihqsCzQ7u74x3M33Taz0+7MbtuZnu8/mzvnzDPP3bm/8/zOcybrdDoSicTwMHa0E0gkEvMjiTaRGDKSaBOJISOJNpEYMpJoE4kh48SjncCRIC/rt+Bbut/v/3BFUxX3HtmsEon+OC5Ei8uxFHd2GTsfd+dlfUlTFVvzsj4BZ+NV+Bu2NVUxceRSTSR6k436OW1e1idjN76HT3SZchq+I4S6R4h7fNb4PtyATzdVsXdxs00kDs3xUGkvxDL8cI7x/+JSXIHlmMJf8EfkeAeuxhvzsv5+O765qYqdi5t2ItGd46HSbsLFOEdUzX54O76A57bXf8arm6p4duAEE4l5MtLd47ysl2AD7tW/YOEHQvQvxWV4JT43cIKJRB+Muj2+GCvMbY3nwzSewY9xK67Kyxom0OAX7d95WZemKkbb6iT2k2Vr8Qb8TKfzQN9hRtEe52V9Jr6Et2IX1gvBLRSniMbW2QPG2YNf4vd6O4Gn8I2mKv4z80Je1svwAbEozTCFO5qqeGjAvA6LvKxX40246eAmXV7WY8LJdZqqmOoRo8COpioeXqCczhDf95amKv69EDEXhCxbOy37CZ3xjGcy3tyvcEdOtHlZr8Rv8Bx8Fd8UD/1iMYYz8FqcPs/3no7X4OXIeszL8Ajeh1/hLFHtVzuwsmdicfooNule9ffOp7rnZb30oNyeh9fj3XhXO3YzLp+Jm5f1RW1+p4rF6JqmKq7vEvvF+DseEz2CQzb38rLORIcf9jVVMT3r9cvwZdF43INvY7GEu7Wpige65NSN7MYt195+wY5tG07sTOswmfEZnc51/XzwwKJt941L5hienM8xSXtGOn7IiXOzBFtxHt6JPw0Q61jiXHxFdLNneAwfFrZ8hpXCYazvEetx/BxbcHtTFZMzA+3Dd3J7uR6fEufY3XgSt+AkUfE/hhtxkRDLg7gHa3ABPovPz14w8rK+FiWexnbR6Z9xRBnOxDq8oL3O27xWtXMmhFP5V5vnS9rrG/Be0UA8ocf/ol/GMIlL8Fdxv2t6vWHNrofcdts100un9k1nsZAdvUqbl/VGfG2O4adxaVMVd7dzx3VfkZZjI650oN3rlw/irgWIcyxxijiaWoa9wp53qyKZ2Mu/cI6xl+F1eJGocneK/XouBLFy1vxd+K74HmeYwO/EsdhkG/MmvG3WnG14v6h2Y7i+zf0WXNlUxVPts/AP/EGck28yt9vYK1zD7jb2jjbnVfY7nN/ip9jcji0mpwkn8QohwAmRf68C9citmz+587ydD54zPrXv/qO6p83L+lxxltmt2m4Qq9/5Yu9znViZ5+Ie8UAMwnbcP2CM44ELxSK5ur3ejV/jYSGQR1E7vK77ScIuLxdbkTscKPQMH8dHhOA+hOeLB/89ovKvE1352fxTiPTRed3ZkeFU4SwmcZVwPofDE4MeFS7InjYv6xX226rZrBLHJStEhb1P2JeDmRZf3PaBk0kcy6zFF4ULeFaIcZ15dtyHnIFFu1BHPk8KK3QwjwurdrOw0F9foM9LDCd34UfCOm8Ue8/DrVCjwuShp/Rm5LrHicSoM9K/iEokRpEk2kRiyEiiTSSGjCTaRGLISKJNJIaMJNpEYsj4P1RkeFLWS0WaAAAAAElFTkSuQmCC Name: Trend, dtype: object

should look like this:

Answer

So I worked this one out myself.

I defined the function to generate the sparkline:

def sparkline(data, figsize=(4,0.25),**kwags):
    data = list(data)
    fig,ax = plt.subplots(1,1,figsize=figsize,**kwags)
    ax.plot(data)
    
    for k,v in ax.spines.items():
        v.set_visible(False)
    
    ax.set_xticks([])
    ax.set_yticks([])
    
    plt.plot(len(data)-1, data[len(data)-1], 'r.')
    
    ax.fill_between(range(len(data)), data, len(data)*[min(data)], alpha=0.1)
    
    img = BytesIO()
    plt.savefig(img, transparent=True, bbox_inches='tight')
    img.seek(0)
#     plt.show()
    plt.close()
    
    return base64.b64encode(img.read()).decode("utf-8")

With this function in memory, I was then able to generate the required PNG images and then save them to a directory. Working from excel, I imported the images.

        d2 = pd.DataFrame(price, columns=['Close'])
        d2 = d2.T
        d2['Trend'] = d2.apply(sparkline, axis=1)
        data = str(list(d2['Trend']))
        im = Image.open(BytesIO(base64.b64decode(data)))
        im.save('C:/Users/Toby/Desktop/IB/Training/Lines/'+str(t)+'.png', 'PNG')

Advertisement

Answer