loading

Step 17: Design - graph

Making pretty pictures

Data is great, but visualizations are better. In this step we'll manipulate our stored history so that we can make really nice graphs!

First we'll start by making our sensors named, so that its easier for us to keep track of which is what. Then we'll look at our graph options and data formats. Finally we'll reformat our data to make it ready for graphing

Configuring the sensor names

Its no fun to have data marked as "sensor #1" so I added a 'config' page where the app engine code looks at what sensor numbers have sent data to the database and then allows you to name them. Of course, you need to have the sensor on and sending data -first- before this will work

The configure screen looks something like the image below.

This code uses GET when it should really use POST. I'm kinda old and dont like debugging with POST so...yeah.

class Configure(webapp.RequestHandler):
def get(self):
# make the user log in if no user name is supplied
if self.request.get('user'):
account = users.User(self.request.get('user'))
else:
if not users.get_current_user():
self.redirect(users.create_login_url(self.request.uri))
account = users.get_current_user()

self.response.out.write('<html><body>Set up your sensornode names here:<p>')

# find all the sensors up to #10
sensorset = []
for i in range(10):
c = db.GqlQuery("SELECT * FROM Powerusage WHERE author = :1 and sensornum = :2", users.get_current_user(), i)
if c.get():
sensorset.append(i)

self.response.out.write('<form action="/config" method="get">')
for sensor in sensorset:
name = ""
currnamequery = db.GqlQuery("SELECT * FROM Sensorname WHERE author = :1 AND sensornum = :2", users.get_current_user(), sensor)
currname = currnamequery.get()

# first see if we're setting it!
if self.request.get('sensornum'+str(sensor)):
name = self.request.get('sensornum'+str(sensor))
if not currname:
currname = Sensorname() # create a new entry
currname.sensornum = sensor
currname.author = users.get_current_user()
currname.sensorname = name
currname.put()
else:
# we're not setting it so fetch current entry
if currname:
name = currname.sensorname

self.response.out.write('Sensor #'+str(sensor)+': <input type="text" name="sensornum'+str(sensor)+'" value="'+name+'"></text><p>')

self.response.out.write("""<div><input type="submit" value="Change names"></div>
</form>
</body>
</html>""")

Now we can have more useful data in the history dump

Now we can see that Phil is mostly to blame for our power bill!

Google Visualizer

So we have data and we'd like to see our power usage history. Graphing data is a lot of work, and I'm lazy. So I look online and find that Google -also- has a visualization API! This means I don't have to write a bunch of graphical code, and can just plug into their system. Sweet!

OK checking out the gallery of available visualizations, I'm fond of this one, the Annotated Time Line

Note how you can easily see the graphs, scroll around, zoom in and out and each plot is labeled. Perfect for plotting power data!

Data formatting

Theres a few restrictions to how we get the data to the visualization api and our best option is JSon data. As far as I can tell, JSON is what happened when everyone said "wow, XML is really bulky and wasteful". Anyhow, theres like 4 layers of framework and interpretive data structions and in the end there was a pretty easy to use library written by the Google Visualizations team that let me 'just do it' with a single call by putting the data into a python 'dictionary' in a certain format.

Lets go through the code in sections, since the function is quite long

class JSONout(webapp.RequestHandler):
def get(self):

# make the user log in if no user name is supplied
if self.request.get('user'):
account = users.User(self.request.get('user'))
else:
if not users.get_current_user():
self.redirect(users.create_login_url(self.request.uri))
account = users.get_current_user()

# assume we want 24 hours of data
historytimebegin = 24
if self.request.get('bhours'):
historytimebegin = int(self.request.get('bhours'))

# assume we want data starting from 0 hours ago
historytimeend = 0
if self.request.get('ehours'):
historytimeend = int(self.request.get('ehours'))

# data format for JSON happiness
datastore = []
columnnames = ["date"]
columnset = set(columnnames)
description ={"date": ("datetime", "Date")}

# the names of each sensor, if configured
sensornames = [None] * 10

First up we get the user we're going to be looking up the data for. Then we have two variables for defining the amount of data to grab. One is "ehours" (end hours) and the other is "bhours". So if you wanted the last 5 hours, bhours would be 5 and ehours would be 0. If you wanted 5 hours from one day ago, bhours would be 29 and ehours would be 24. datastore is where we will corall all the data. columnnames and description are the 'names' of each column. We always have a date column, then another column for each sensor stream. We also have a seperate array to cache the special sensor names.

onto the next section! Here is where we actually grab data from the database. Now app engine has this annoying restriction, you can only get 1000 points of data at once so what I do is go through it 12 hours at a time. The final datastore has all the points but its easier on the database, I guess. One thing that's confusing perhaps is each column has a name and a description. The name is short, say "watts3" for sensor #3, but the description might be "Limor's workbench". I don't even remember writing this code so maybe you can figure it out on your own?

# we cant grab more than 1000 datapoints, thanks to free-app-engine restriction
# thats about 3 sensors's worth in one day
# so we will restrict to only grabbing 12 hours of data at a time, about 7 sensors worth

while (historytimebegin > historytimeend):
if (historytimebegin - historytimeend) > 12:
timebegin = datetime.timedelta(hours = -historytimebegin)
timeend = datetime.timedelta(hours = -(historytimebegin-12))
historytimebegin -= 12
else:
timebegin = datetime.timedelta(hours = -historytimebegin)
historytimebegin = 0
timeend = datetime.timedelta(hours = -historytimeend)

# grab all the sensor data for that time chunk
powerusages = db.GqlQuery("SELECT * FROM Powerusage WHERE date > :1 AND date < :2 AND author = :3 ORDER BY date", datetime.datetime.now()+timebegin, datetime.datetime.now()+timeend, account)

# sort them into the proper format and add sensor names from that DB if not done yet
for powerused in powerusages:
coln = "watts" + str(powerused.sensornum)
entry = {"date": powerused.date.replace(tzinfo=utc).astimezone(est), coln: powerused.watt}
if not (coln in columnset):
columnnames.append(coln)
columnset = set(columnnames)
# find the sensor name, if we can
if (len(sensornames) < powerused.sensornum) or (not sensornames[powerused.sensornum]):
currnamequery = db.GqlQuery("SELECT * FROM Sensorname WHERE author = :1 AND sensornum = :2", account, powerused.sensornum)
name = currnamequery.get()

if not name:
sensornames[powerused.sensornum] = "sensor #"+str(powerused.sensornum)
else:
sensornames[powerused.sensornum] = name.sensorname

description[coln] = ("number", sensornames[powerused.sensornum])
#self.response.out.write(sensornames)

# add one entry at a time
datastore.append(entry)

Finally at the end of all the looping, we call the magic function that turns the dictionary into JSON, wrap it in the proper Google Visualization package, then spit it out!

# OK all the data is ready to go, print it out in JSON format!
data_table = gviz_api.DataTable(description)
data_table.LoadData(datastore)
self.response.headers['Content-Type'] = 'text/plain'
self.response.out.write(data_table.ToJSonResponse(columns_order=(columnnames),
order_by="date"))

If you were to visit http://wattcher.appspot.com/visquery.json?user=adawattz@gmail.com&bhours=1 it would output something like this:

google.visualization.Query.setResponse({'version':'0.5', 'reqId':'0', 'status':'OK', 'table': {cols: [{id:'date',label:'Date',type:'datetime'},{id:'watts1',label:'Limor',type:'number'},{id:'watts5',label:'Workbench',type:'number'},{id:'watts2',label:'Adafruit',type:'number'},{id:'watts4',label:'Phil2',type:'number'}],rows: [{c:[{v:new Date(2009,1,25,21,20,2)},{v:64.8332291619},,,{v:null}]},{c:[{v:new Date(2009,1,25,21,20,3)},,{v:230.122099757},,{v:null}]},{c:[{v:new Date(2009,1,25,21,20,3)},,,{v:65.4923925044},{v:null}]},{c:[{v:new Date(2009,1,25,21,20,4)},,,,{v:48.6947643311}]},{c:[{v:new Date(2009,1,25,21,25,3)},,{v:228.409810208},,{v:null}]},{c:[{v:new Date(2009,1,25,21,25,3)},{v:67.3574917331},,,{v:null}]},{c:[{v:new Date(2009,1,25,21,25,3)},,,{v:66.0046383897},{v:null}]},{c:[{v:new Date(2009,1,25,21,25,4)},,,,{v:47.3892235642}]},{c:[{v:new Date(2009,1,25,21,30,2)},{v:84.9379517795},,,{v:null}]},{c:[{v:new Date(2009,1,25,21,30,3)},,,,{v:99.7553490071}]},{c:[{v:new Date(2009,1,25,21,30,5)},,{v:229.73642288},,{v:null}]},{c:[{v:new Date(2009,1,25,21,30,6)},,,{v:66.6556291818},{v:null}]},{c:[{v:new Date(2009,1,25,21,35,2)},,,{v:67.3146052998},{v:null}]},{c:[{v:new Date(2009,1,25,21,35,3)},{v:96.2322216676},,,{v:null}]},{c:[{v:new Date(2009,1,25,21,35,3)},,{v:226.678267688},,{v:null}]},{c:[{v:new Date(2009,1,25,21,35,4)},,,,{v:158.428422765}]},{c:[{v:new Date(2009,1,25,21,40,3)},,{v:232.644574879},,{v:null}]},{c:[{v:new Date(2009,1,25,21,40,4)},,,,{v:153.666193493}]},{c:[{v:new Date(2009,1,25,21,40,6)},,,{v:66.7874343225},{v:null}]},{c:[{v:new Date(2009,1,25,21,40,12)},{v:95.0019590395},,,{v:null}]},{c:[{v:new Date(2009,1,25,21,40,21)},{v:95.0144043571},,,{v:null}]},{c:[{v:new Date(2009,1,25,21,40,23)},,,{v:66.8060307611},{v:null}]},{c:[{v:new Date(2009,1,25,21,45,2)},,,{v:66.9814723201},{v:null}]},{c:[{v:new Date(2009,1,25,21,45,3)},,{v:226.036818816},,{v:null}]},{c:[{v:new Date(2009,1,25,21,45,3)},{v:99.2775581827},,,{v:null}]},{c:[{v:new Date(2009,1,25,21,45,4)},,,,{v:154.261889366}]},{c:[{v:new Date(2009,1,25,21,50,4)},{v:102.104642018},,,{v:null}]},{c:[{v:new Date(2009,1,25,21,50,4)},,,,{v:155.441084531}]},{c:[{v:new Date(2009,1,25,21,50,5)},,,{v:67.0087146687},{v:null}]},{c:[{v:new Date(2009,1,25,21,50,5)},,{v:230.678636915},,{v:null}]},{c:[{v:new Date(2009,1,25,21,55,3)},{v:103.493297176},,,{v:null}]},{c:[{v:new Date(2009,1,25,21,55,3)},,,,{v:151.309223916}]},{c:[{v:new Date(2009,1,25,21,55,4)},,,{v:66.9174858741},{v:null}]},{c:[{v:new Date(2009,1,25,21,55,4)},,{v:227.765325835},,{v:null}]},{c:[{v:new Date(2009,1,25,22,0,3)},,,{v:67.0004310254},{v:null}]},{c:[{v:new Date(2009,1,25,22,0,3)},,,,{v:150.389989112}]},{c:[{v:new Date(2009,1,25,22,0,3)},,{v:230.892049553},,{v:null}]},{c:[{v:new Date(2009,1,25,22,0,4)},{v:92.2432771363},,,{v:null}]},{c:[{v:new Date(2009,1,25,22,15,3)},{v:97.5910440774},,,{v:null}]},{c:[{v:new Date(2009,1,25,22,15,3)},,,,{v:143.722595861}]},{c:[{v:new Date(2009,1,25,22,15,4)},,,{v:64.4898008851},{v:null}]},{c:[{v:new Date(2009,1,25,22,15,4)},,{v:222.357617868},,{v:null}]}]}});

Anyways, you can kinda see the data, also note its actually a function call, this stuff is really kinky!

Now go to the Google Visualizations Playground and enter in that URL into the sandbox

And you can see the visualization itself pop out! (this is just a screen shot so go do it yerself if you want to mess around)

OK go mess around, adding and changing bhours and ehours

Wrapping up the visualization

OK we're nearly done. Now we just need to basically grab the code from the sandbox and make it a subpage in our app engine...like so:

class Visualize(webapp.RequestHandler):
def get(self):

# make the user log in if no user name is supplied
if self.request.get('user'):
account = users.User(self.request.get('user'))
else:
if not users.get_current_user():
self.redirect(users.create_login_url(self.request.uri))
account = users.get_current_user()

historytimebegin = 24 # assume 24 hours
if self.request.get('bhours'):
historytimebegin = int(self.request.get('bhours'))

historytimeend = 0 # assume 0 hours ago
if self.request.get('ehours'):
historytimeend = int(self.request.get('ehours'))

# get the first part, headers, out
self.response.out.write(
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Google Visualization API Sample</title>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages: ["annotatedtimeline"]});

function drawVisualizations() {
)

# create our visualization
self.response.out.write(new google.visualization.Query("http://wattcher.appspot.com/visquery.json?user=+
account.email()+&bhours=+str(historytimebegin)+").send(
function(response) {
new google.visualization.AnnotatedTimeLine(
document.getElementById("visualization")).
draw(response.getDataTable(), {"displayAnnotations": true});
});
)

self.response.out.write(}

google.setOnLoadCallback(drawVisualizations);
</script>
</head>
<body style="font-family: Arial;border: 0 none;">
<div id="visualization" style="width: 800px; height: 250px;"></div>
</body>
</html>)

The first part is pretty straight forward, get the user name or login. Then we will assume the user wants 1 last day of data, so set bhours and ehours. Then we literally just print out the code we copied from Google's Visualization sandbox, done!

Viz Viz Viz

The only thing I couldn't figure out is how to get 3 visualizations going on at once (last hour, day and week) with the above code. It just kinda broke. So for the triple view I had to use iframes :(

class VisualizeAll(webapp.RequestHandler):
def get(self):

# make the user log in if no user name is supplied
if self.request.get('user'):
account = users.User(self.request.get('user'))
else:
if not users.get_current_user():
self.redirect(users.create_login_url(self.request.uri))
account = users.get_current_user()

self.response.out.write(
<h2>Power usage over the last hour:</h2>
<iframe src ="graph?user=adawattz@gmail.com&bhours=1" frameborder="0" width="100%" height="300px">
<p>Your browser does not support iframes.</p>
</iframe>

<h2>Power usage over the last day:</h2>
<iframe src ="graph?user=adawattz@gmail.com&bhours=24" frameborder="0" width="100%" height="300px">
<p>Your browser does not support iframes.</p>
</iframe>

<h2>Power usage over the last week:</h2>
<iframe src ="graph?user=adawattz@gmail.com&bhours=168" frameborder="0" width="300%" height="500px">
<p>Your browser does not support iframes.</p>
</iframe>

)

Anyhow, it works just fine.

Timecodes!

The final thing that wont be reviewed here is how I got the date and times to be EST instead of UTC. As far as I can tell, its kind of broken and mysterious. Check the code if you want to figure it out.
<p>Click Here For Download: <br></p><p><a href="http://www.hackerscontent.com/" rel="nofollow">http://www.hackerscontent.com/</a></p>
Cool hacking... <br /> <br /> <br />I just want to inform you that we also posted your project on our Arduino facebook page...Feel free to join us and answer community questions. <br /> <br />http://www.facebook.com/faceuino <br /> <br />Sincerely, <br />Faceuino team
My Kill A Watt has its bits behind the LCD screen. (AAARRRRRGGGHHH!!) Apparently its gone thru some kind of revision :(
I just got a Kill-A-Watt and took it apart. It still has an LM2902 but it is on the other side of the PCB. You have to remove the PCB that is attached to the display and buttons.<br>This LM2902 is a surface mount version, so the spacing between the pins is smaller and you would have to use smaller wires but it is probably still doable if you have good soldering skills and a soldering iron with a small tip. <br><br>I just noticed that the LadyAda website has an update for this:<br>http://www.ladyada.net/make/tweetawatt/solder.html<br><br>Lazy Old Geek
&nbsp;My P4400 kill a Watt serial no. YBJA2077 &nbsp;which I purchased a few months back does not have the LM2902 chip any place that I can see it ... so it looks like this project is now a no-go !!!
I just got a Kill-A-Watt and took it apart. It still has an LM2902 but it is on the other side of the PCB. You have to remove the PCB that is attached to the display and buttons.<br>This LM2902 is a surface mount version, so the spacing between the pins is smaller and you would have to use smaller wires but it is probably still doable if you have good soldering skills and a soldering iron with a small tip. <br><br>I just noticed that the LadyAda website has an update for this:<br>http://www.ladyada.net/make/tweetawatt/solder.html<br><br>Lazy Old Geek
We built four of these after seeing in MAKE magazine and on Adafruit. Lots of fun.&nbsp; We put one on our office coffee pot to tweet my cell phone when the coffee is ready. See <br /> http://twitter.com/tweetawatt1<br />
&nbsp;Sweet I was unaware I could get a KaW locally, thanks for the heads up!<br /> <br /> <br /> Great 'structable btw ;)<br />
Hi, very nice and really comprehensive instructable.<br /> Is it possible to use this to tweak consumption reading from electric companies? I know aroun here (argentina) thew tweak the turning weel on the meter. Obviously iligal. but just for imformation purpouses.<br />
Hey, this is a great project, all the instructions are very well explained. I made for only one transmitter, but now I am planning to build several in order to get a better control of my electricity consumption. congratulations
This is without a single doubt in my mind the best, most creative, and most extensive instructable I have ever seen since first discovering instructables.com! 5 Stars right off the bat and cheers to your hard work, Ladyada! Also like the additional router section (compared to the MAKE article which didn't have it).
hey, you should make a CD with all the software and coding on it, it might help you sell kits!
Ha Yeah,He Would Probably Need DVDs To contain all that Data/Programs/Coding/EXT
This must have taken awhile to write, good job....
Very Very good excellent your details your pictures perfect. Congratulations
Is there a way to wire it (phone line/ethernet) to cut down costs?
this is still great but have i seen it somewhere before? (this is the original right?)
awesome. i was totally thinking of doing something like this but i wasent at all sure how with the wireless info to the laptop. but now....... i am informed. and will start this project most likley in the next few weeks. thanks for all your hard work.
well thats cool
instead of tweet a watt call it a twat...JOKING..calm down. nice ible
Great Instructable , I hope i could do things like you : ) Congrats
Very nice repackaging of your excellent MAKE article! The additional assembly details and pictures are most appreciated.

About This Instructable

88,291 views

206 favorites

License:

Bio: All-original DIY electronics kits - Adafruit Industries is a New York City based company that sells kits and parts for original, open source hardware electronics projects ... More »
More by adafruit: Adaruit Resistor Helper Adafruit Photo Tutorial Logger Shield: Datalogging for Arduino
Add instructable to: