Matplotlib Animations Using Data queried from a SQL Table

In prior examples, I've provided a couple of snippets for writing videos using Matplotlib. In this example, instead of plotting data stored in files, I'm using data stored in a SQL table. In this particular instance, I'm using MySQL for convenience.

These days, your choice of database is about as controversial as discussing politics and religion; it's the third rail of...data? You can choose the best database, and table structure to suit your particular needs. MySQL serves just fine as an example. In any case, SQL syntax is mostly standard across databases. and the Python database interfaces are also quite standard; they mainly consist of instantiating a connection and cursor, and executing SQL commands.

Ok, so now to the code. In prior examples, we needed to know the number of frames to plot, when we initiate the matplotlib animation. In my case, I've structured the data so a given row in the table corresponds to one data frame, and to one plotted frame in the Matplotlib animation. So, I have a couple of utility functions, one to get the number of frames we'll plot, and the other to query a particular table entry, given the desired row [frame] and column.

 import sys  
 import os  
 import time  
 import MySQLdb as mdb  
 from TableDef import columns #Custom definition of the table structure  
   
 def count(table, serverIP, dbUser, password, database):  
   try:  
     con = mdb.connect(serverIP, dbUser, password,database)        
     with con:  
       cur = con.cursor()  
       com="SET sql_mode=\'ANSI_QUOTES\'"  
       cur.execute(com)  
       row_exists=0  
       com="SELECT COUNT(*) FROM \"%s\""%table  
       cur.execute(com)  
       rows=cur.fetchall()  
       count=rows[0][0]        
         
   except mdb.Error, e:   
       print "Error %d: %s" % (e.args[0],e.args[1])  
       sys.exit(1)    
   finally:      
       if con:    
         con.close()   
   return count  
   
 def getdataframe(table,column,frame,serverIP, user, password, database):      
     
   if column not in columns:  
     return  
   if frame>count(table):  
     return  
   try:  
     con = mdb.connect(serverIP, dbUser, password,database);        
     with con:  
       cur = con.cursor()  
       com="SET sql_mode=\'ANSI_QUOTES\'"  
       cur.execute(com)  
       row_exists=0  
       com="SELECT %s "%column  
       com=com+"FROM \"%s\" "%table  
       com=com+"WHERE Frame=%s"%str(frame)        
       cur.execute(com)  
       rows=cur.fetchall()  
       data=rows[0][0]        
         
   except mdb.Error, e:   
       print "Error %d: %s" % (e.args[0],e.args[1])  
       sys.exit(1)    
   finally:      
       if con:    
         con.close()   
           
   return data    

Note that I have a custom Python class called TableDef, where I've declared various parameters relevant to the table and database. This part depends on your particular application, how you structure your data, etc. I could have wrapped these table functions in a class if I wanted a more OO feel.

Now, when I go my Matplotlib animation, I can use these utility functions to get the total number of frames, and to query individual data frames.

 import sys  
 import os  
 import json  
 import numpy as np  
 import matplotlib  
 import matplotlib.pyplot as plt  
 import matplotlib.animation as animation  
 from file_utils import * #table from folder is defined here  
 import table_read as Table  
 from TableDef import columns #Custom definition of the table structure  
 from TableDef import DatabaseCredentials as DB #Custom definition of the table structure  
   
 def init():  
   plotline.set_data([], [])  
   dplotline.set_data([],[])  
   return plotline,dplotline,  
   
 def animate(i):          
   global buffersize    
   if (i%100==0):  
     print i        
   line= Table.getdataframe(table,columns[1],i,DB.serverIP, DB.user, DB.password, DB.name)    
   
   try:  
     jsondata=json.loads(line)    
   except ValueError:  
     print "Invalid JSON"  
     print line  
     f.close()  
     os.system("pause")      
     
   pos=jsondata["Position"]       
   vel=jsondata["Velocity"]  
   
   pos_buf.append(pos)  
   vel_buf.append(vel)  
     
   while len(pos_buf)>buffersize:  
     pos_buf.pop(0)  
   while len(vel_buf)>buffersize:  
     vel_buf.pop(0)  
     
   x=np.linspace(-len(pos_buf)/framerate,0,len(pos_buf))  
   dx=np.linspace(-len(vel_buf)/framerate,0,len(vel_buf))        
     
   plotline.set_data(x,headpose_buf)  
   dplotline.set_data(dx,headvel_buf)    
   return plotline, dplotline,  
   
 table=""   
 framerate=20.0  
 buffersize=5*20  
 vel_buf=[]  
 pos_buf=[]  
   
 fig, ax = plt.subplots()  
 ax.set_xlim(-5.0,2.0)  
 ax.set_ylim(-180,180)  
 my_dpi=96  
 fig.set_size_inches(640/my_dpi, 360/my_dpi)    
 plotline, =ax.plot([],[],lw=2,color='b')  
 dplotline, =ax.plot([],[], lw=2, color='r')  
   
 if __name__=='__main__':  
   if len(sys.argv)<2:  
     print "Usage:\nplot_pos_vel.py folder"  
     exit()  
     
   folder= sys.argv[1]       
   outfile=folder+"/pos_vel.avi"  
   if (os.path.isfile(outfile)):  
     exit()   
   table=table_from_folder(folder)  #Custom function that maps folders to their table representation, user defined  
   line_count=Table.count(table)    
     
   Writer = animation.writers['ffmpeg']  
   writer = Writer(fps=25, metadata=dict(artist='Sayanan'), extra_args=['-vcodec','libx264'])      
   anim = animation.FuncAnimation(fig, animate,init_func=init, frames=line_count-1,blit=True, interval=1, repeat=False)      
   anim.save(outfile,writer=writer)  
     
   #plt.show()  
     

Within the entries in the table, I've further structured my data as json for position and velocity. Again, this is really a design choice, and depending on your database, json encoding might be native.

Other than that, this is the same Matplotlib example as before, but using a database for the data.

Matplotlib Animations, Using Stored Data, Writing Video

There are some good matplotlib animations examples floating around the internet, including here and here. These were useful and I gained a great deal from them, but my particular problem was not directly addressed.

The scenario we're looking at today is the following. We have some stored data on disk, that we would like to plot using a Matplotlib animation, and save this animation as a video file. This is a reasonably common occurrence for researchers, but I had trouble finding tutorials online for this problem. Many of the tutorials generated their data via simulations on the fly, which didn't directly map to the case of recorded data.

There were two stumbling blocks I ran into, that had non-obvious solutions. First, Matplotlib's animation takes a function pointer to a user-defined function, for example def animate(i), but the animate function is then controlled by the iterator i. The second major stumbling block I hit, is that your animation function def animate(i): should return the data structures that need to be updated.

In this example, I have saved some data to a file in JSON format; each entry has a position and a velocity parameter. I read this file in line-by-line, and create a scrolling plot of the time series. I save this animated scrolling plot as a video file to disk, using ffmpeg and x264 for video encoding.

 import sys  
 import os  
 import json  
 import numpy as np  
 import matplotlib  
 import matplotlib.pyplot as plt  
 import matplotlib.animation as animation  
 from time import time  
 import itertools  
   
 from matplotlib.path import Path  
 import matplotlib.patches as patches  
 import threading  
   
   
   
 def init():  
   plotline.set_data([], [])  
   dplotline.set_data([],[])  
   return plotline,dplotline,  
   
 def animate(i):        
   #ax.clear()  
     
   global frame_num, buffersize  
     
   if (frame_num%100==0):  
             print frame_num    
   line=f.readline()  
   jsondata=json.loads(line)  
   frame_num+=1  
     
   if "Position" in jsondata:  
     pos= jsondata["Position"]  
       
   if "Velocity" in jsondata:  
             vel=jsondata["Velocity"]  
   
   pos_buf.append(pos)  
   vel_buf.append(vel)  
     
   while len(pos_buf)>buffersize:  
     pos_buf.pop(0)  
   while len(vel_buf)>buffersize:  
     vel_buf.pop(0)  
     
   x=np.linspace(-len(pos_buf)/framerate,0,len(pos_buf))  
   dx=np.linspace(-len(vel_buf)/framerate,0,len(vel_buf))        
     
   plotline.set_data(x,headpose_buf)  
   dplotline.set_data(dx,headvel_buf)    
   return plotline, dplotline,  
   
 def folder_from_path(file):  
   split_path=file.split("/")  
   folder=split_path[0]  
   for i in range(1,len(split_path)-1):  
     folder+="/"+split_path[i]  
   return folder  
   
 #Setting up globals  
 file=""    
 frame_num=0    
   
 framerate=20.0  
 buffersize=5*20  
 vel_buf=[]  
 pos_buf=[]  
   
 fig, ax = plt.subplots()  
 ax.set_xlim(-5.0,2.0)  
 ax.set_ylim(-180,180)  
 my_dpi=96  
 fig.set_size_inches(640/my_dpi, 360/my_dpi)    
 plotline, =ax.plot([],[],lw=2,color='b')  
 dplotline, =ax.plot([],[], lw=2, color='r')  
   
 if __name__=='__main__':  
   if len(sys.argv)<2:  
     print "Usage:\nplot_data.py file"  
     exit()  
   #Parsing command-line arguments, checking the input file exists.  
   file= sys.argv[1]  
   print file  
   if (os.path.isfile(file)==False):  
       print "Problem with file"  
       exit()   
   folder=folder_from_path(file)  
   #Writing video to same folder  
   outfile=folder+"/data.avi"  
   print folder  
   #Getting number of data points from file. A little hackey, but I couldn't control animate() with the file open()            
   line_count=0  
   with open(file, 'r') as f:  
     for read_data in f:      
       line_count=line_count+1  
   print line_count  
   f=open(file,'r')  
   Writer = animation.writers['ffmpeg']  
   writer = Writer(fps=25, metadata=dict(artist='Sayanan'), extra_args=['-vcodec','libx264'])      
   anim = animation.FuncAnimation(fig, animate,init_func=init, frames=line_count-1,blit=True, interval=1, repeat=False)      
   anim.save(outfile,writer=writer)  
   f.close()  
   #plt.show()  
     

So there you go. A basic program that plots animation, and saves the resulting video to disk, using recorded data, stored in JSON on disk.

Edit: Just a quick addendum. I also had some trouble plotting animations of a grid of custom polygons, which could be useful for mapping, occupancy grids, or similar data. Here's a quick demo for how to generate animations of this sort:

 "Demo of a grid of PathPatch objects."  
 import numpy as np  
 import matplotlib  
 import matplotlib.pyplot as plt  
 import matplotlib.animation as animation  
 from time import time  
 import itertools  
   
 from matplotlib.path import Path  
 import matplotlib.patches as patches  
   
 def animate(i):       
   prob=np.random.rand(np.size(yr)*np.size(xl))  
   ax.clear()  
   cells=[]  
   for i in range(0,len(P1)):    
     verts=[P1[i], P2[i], P3[i], P4[i], P1[i] ]  
     path = Path(verts, codes)  
     patch = patches.PathPatch(path, color=[1-prob[i],prob[i],0], lw=2)  
     cells.append(ax.add_patch(patch))  
   return cells  
     
   
 fig, ax = plt.subplots()  
 fig.set_size_inches(5,20)  
   
 width=3.5  
 height=5.0  
   
 xl=np.arange(-8.75,8.75,width)  
 yr=np.arange(-50.0,50.0,height)  
 ax.set_xlim(xl[0],xl[np.size(xl)-1]+width)  
 ax.set_ylim(yr[0],yr[np.size(yr)-1]+height)  
 cells=[]  
 print yr[10]  
 print np.size(yr)  
 print np.size(xl)  
 P1=list(itertools.product(xl,yr+height))  
 P2=list(itertools.product(xl+width, yr+height))  
 P3=list(itertools.product(xl+width, yr))  
 P4=list(itertools.product(xl,yr))  
 prob=np.random.rand(np.size(yr)*np.size(xl))  
 codes = [Path.MOVETO,  
      Path.LINETO,  
      Path.LINETO,  
      Path.LINETO,  
      Path.CLOSEPOLY,  
      ]  
 for i in range(0,len(P1)):  
   verts=[P1[i], P2[i], P3[i], P4[i], P1[i] ]  
   path = Path(verts, codes)  
   patch = patches.PathPatch(path, color=[1-prob[i],prob[i],0], lw=2)  
   cells.append(ax.add_patch(patch))  
 anim = animation.FuncAnimation(fig, animate, blit=True, save_count=0,interval=10)  
 plt.show()