Denormalizing Temperature Data across time for Google Charts in Flask

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:

  1. Get data ordered by date ascending
  2. Group all temperatures into dates, truncated to the minute
  3. Each group could have N temperatures. It is possible that the same beer recorded itself twice, or even Y times, in the minute of this group, and that a different beer didn't record temperature for this minute.
  4. Break down the temperatures in each group such that they get N row for 4 temperatures. If any beer shows up twice, I need to average it, and if any beer doesn't show up at all, I need to set it to null.

The Flask route to Denormalize the Temperature Data

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.temp

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:
    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(, dt, avg)

    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)

Javascript to display the chart

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.

// 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]);

    // 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:


Add Comment




Are you human? + ten = 20