This post is part of a series. This list contains all of the posts:
I've added a new feature to my site that displays the temperature for all the beers I'm brewing with my Raspberry pi. I used Google Charts for it and had some trouble getting the data formatted properly, because the Google Charts API expects denormalized data, while my temperature data was normalized. This is my experience.
My temperature table similar to the following:
fermentor_id dt temp 1 2016-01-01 00:00:02 65.4 1 2016-01-01 00:01:03 65.5
My fermentor table looks something like this:
id name 1 Saison Pure Pilsener 2 Stout Marris Otter
So joining the tables together results in the following denormalized temperature data:
Name dt temp Saison Pure Pilsener 2016-01-01 00:00:02 65.4 Stout Marris Otter 2016-01-01 00:00:03 70.2 Saison Pure Pilsener 2016-01-01 00:01:03 65.5 Stout Marris Otter 2016-01-01 00:01:04 70.1
However, my problem was that Google Charts wants the data formatted into a denoramlized form, like this:
[
[Date, Saison Pure Pilsener, Stout Marris Otter],
[2016-01-01 00:00:00, 65.4, 70.2],
[2016-01-01 00:01:00, 65.5, 70.1]
]
I am brewing N beers at any given moment. It's also possible that I receive some number less or more than N temperatures for a given minute. So I need to make sure to back fill empty columns with null values and to prevent any bugs that would cause one of the beer's temperatures to accidentally go into another beer's column.
These are the steps to my solution:
Because Google Charts wants denormalized data, I have to break down my normalized temperature data using the following. I've ommitted the @app.route and function definition.
I've used the terrific itertools.groupby function in order to denormalize, as well as take the average of, the normalized temperature data.
from collections import namedtuple
from collections import defaultdict
from itertools import groupby
import json
Temp = namedtuple('Temp', ('name', 'dt', 'temp'))
def make_gc_row(dt, temps, data, name_map):
"""Creates a denormalized google chart row from
normalized temperature data"""
# Initialize empty row with null values with the same
# length as the number of beers brewing in last 24 hours
# The first column for google charts is the date
row = [dt] + [None] * len(name_map)
for temp in temps:
row[name_map[temp.name]] = temp.temp
data.append(row)
def average_temps(dt, temps):
"""Since there may be more temps in one minute
then the number of columns in the chart, make sure
to average them out"""
# group the temps by the beer
grouped = {}
group_func = lambda x: x.name
for key, group in groupby(temps, group_func):
grouped[key] = [g for g in group]
avg_temps = []
for key, group in grouped.iteritems():
count = 0.0
sum = 0.0
for temp in group:
count += 1
sum += temp.temp
avg = sum / count
t = Temp(temp.name, dt, avg)
avg_temps.append(t)
return avg_temps
# Get all names ordered alphabetically from beers that
# have been fermenting in the last 24 hours.
# The length of this, plus the date time, is equal to the
# number of columns in the denormalized Google Chart
names = [row[0] for row in name_query()
# Index the names by the column to be used for Google Charts
# This will help denormalize the temperature rows
name_map = {}
# Start index at 1, because 0 is for the datetime
count = 1
for name in names:
name_map[name] = count
count += 1
# Get all temperatures within last 24 hours
temp_rows = brew_query()
temps = [Temp(*row) for row in temperature_rows]
grouped = {}
# This will group each temperature truncated to the minute
date_format = '%Y-%m-%dT%H:%M'
group_func = lambda x: x.dt.strftime(date_format)
for key, group in groupby(temps, group_func):
grouped[key] = [t for t in group]
# Denormalize and average the temperature data
data = []
for dt, temps in gouped.iteritems():
if len(temps) <= len(names):
avg_temps = average_temps(dt, temps)
make_gc_row(dt, avg_temps, data, name_map)
# Serialize this to json
# In template use {{data|json|safe}}
data = json.dumps(data)
names = json.dumps(names)
return render_template('brew.html', names=names, data=data)
I copy and pasted the Google Charts example and then customized it.
// Load the Visualization API and the corechart package.
google.charts.load('current', {'packages':['corechart']});
// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(drawChart);
// This is how you can send a JSON string to your template
// without any errors
temps = JSON.parse({{data|tojson|safe}});
names = JSON.parse({{names|tojson|safe}});
function drawChart() {
var data = new google.visualization.DataTable();
data.addColumn('datetime', 'Date');
// Because the number of brews is variable
// loop over the names and add them
for (var i = 0; i < names.length; i++) {
data.addColumn('number', names[i]);
}
// We need to convert the json string to a date
for (var i = 0; i < temps.length; i++) {
lol = temps[i];
lol[0] = new Date(lol[0]);
console.log(lol);
data.addRow(lol);
}
// Set chart options
var options = {
title: 'Brewery Temperatures across Time',
curveType: 'function',
legend: { position: 'bottom' },
pointSize: 1,
colors: ['red', 'blue', 'purple', 'black'],
//lineWidth: 0.5,
height: 500,
//width: 1500,
'chartArea': {'width': '90%', 'height': '80%'},
};
// Instantiate and draw our chart, passing in some options.
var chart = new google.visualization.ScatterChart(document.getElementById('chart'));
chart.draw(data, options);
}
This post is part of a series. This list contains all of the posts: