Graphing Data from MQTT using Python/Matplotlib

• Category: Python

Being the weather nerd that I am, if you saw my previous post about parsing temperature and humidity data using MQTT, then consider this a follow up to that.

In short, I have that very Wemos and sensor set up in an undercover/shaded section of the house outside which has been happily sending me temp and humidity data every 30 seconds and updating my OpenHAB setup. Since then, I had some free time over the weekend to play with a python library I've wanting to mess with, that being matplotlip.

Storing the data

Not wanting to run a full fledged SQL db, I've opted for SQLite instead of my usual DB of choice, PostgreSQL. To store said data, I am using the paho-mqtt python library to pull the MQTT data and the sqlite python library to store said data into our db. By doing this, it is dead simple for us to store our data for future review. The below code will do this for us. (Note: I am running this on a server in a screen session, if you wish to implement this into a service of sorts, you're on your own!)

#!/usr/bin/env python
import paho.mqtt.client as mqtt
import sqlite3
import os.path
from datetime import datetime

db_file       = "weather_stats.db"
conn          = sqlite3.connect(db_file)

temp_topic    = "temperature topic"
humid_topic   = "humidity topic"
mqtt_service  = "IP/Host of your MQTT service"

def insert_data(data,type):
    with conn:
        cur = conn.cursor()
        now = datetime.now()
        # 1: temp, 2: humidity
        if(type==1):
            cur.execute("INSERT INTO weather_stats \
                VALUES (null, {}, null, '{}')".format(float(data),now))
        elif(type==2):
            cur.execute("INSERT INTO weather_stats \
                VALUES (null, null, {}, '{}')".format(float(data),now))

def on_connect(client, userdata, flags, rc):
    print("connected to {} with result code {}".format(mqtt_service,rc))
    client.subscribe(temp_topic)
    client.subscribe(humid_topic)

def on_message(client, userdata, msg):
    print(msg.topic+" "+str(msg.payload))
    if(msg.topic == temp_topic):
        insert_data(msg.payload,1)
        print("Parsing temp data " + str(msg.payload))
    elif(msg.topic == humid_topic):
        insert_data(msg.payload,2)
        print("Parsing humidity data " + str(msg.payload))

# init table if needed
def create_table():
    with conn:
        cur = conn.cursor()
        cur.execute("CREATE TABLE weather_stats(\
            id INTEGER PRIMARY KEY,\
            temp DECIMAL(4,2),\
            humidity DECIMAL(4,2),\
            sqltime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)\
        ")

def main():
    try:
        create_table()
    except:
        print("Table already created. Moving on...")
        pass

    client = mqtt.Client()
    client.on_connect = on_connect
    client.on_message = on_message
    client.connect(mqtt_service, 1883, 60)
    client.loop_forever()

if __name__ == '__main__':
    main()

Graph all the things!

And now we can simply make some pretty graphs! This really isn't the cleanest code in the world. but it does the job so long as our data is in order.

#!/usr/bin/env python
import pandas as pd
import numpy as np
from datetime import datetime, timedelta, date
import matplotlib.pyplot as plt
import matplotlib.dates as dates
import matplotlib.patches as mpatches
import sqlite3
import sys

db_file             = "weather_stats.db"
png_file_prefix     = "home"

conn                = sqlite3.connect('weather_stats.db')
data_dict_temp      = []
data_dict_humidity  = []
time_dict           = []

def grab_data(col):
    with conn:
        cur = conn.cursor()
        cur.execute("SELECT {},sqltime FROM weather_stats \
            WHERE {} != 0 \
            AND sqltime > date('now','start of day')".format(col,col))
        row = cur.fetchall()

        for x in row:
            if col == 'temp':
                time_dict.append(x[1])
                data_dict_temp.append(x[0])
            elif col == 'humidity':
                data_dict_humidity.append(x[0])

def make_graph():
    datestrformat = '%Y-%m-%d %H:%M:%S.%f'
    timetostring = []

    for x in range(0,len(time_dict)):
        timetostring.append(datetime.strptime(\
            time_dict[x],datestrformat))

    fig = plt.figure()
    ax1 = fig.add_subplot(111)
    date_format = dates.DateFormatter('%H:%M')
    ax1.xaxis.set_major_formatter(date_format)
    ax1.set_title("Temperature data for {}"\
        .format(str(timetostring[0])[:10]))    
    ax1.set_xlabel('Time')
    ax1.set_ylabel('Degrees/Humidity')
    plt.plot(timetostring,data_dict_temp)
    plt.plot(timetostring,data_dict_humidity)
    lbl_humidity = mpatches.Patch(color='orange', label='Humidity')
    lbl_temp = mpatches.Patch(color='blue', label='Tempereture')
    plt.legend(handles=[lbl_humidity,lbl_temp])
    plt.savefig('{}-{}.png'.format(datetime.now(),png_file_prefix))
    plt.show()

def main():
    grab_data("temp")
    grab_data("humidity")
    make_graph()

if __name__ == '__main__':
    main()

What the above will do is pull our data from our SQLite db, sanitize a bunch of stuff, format even more then punch it all into the pretty graph you see below. If you don't wish to display it in a TKinter window after running and are only interested in the png, you can simply comment out plt.show().

Photo

And it's easy as that! In practice, I have the graph being generated every 24 hours via cron so I can easily look at the variances over the days. With any luck, this $10usd worth of microcontroller and sensor will still be working this time next year! Fingers crossed!