Pandas DataFrame, matplotlib and different SQL data types

Question

I want to create a graph of temperature and time data from a MySQL database. Using matplotlib and pandas with python3 on raspbian I am trying to insert the temperature in the Y axis and the time in the X axis.

The Y axis works fine, it plots the temps (float) without any issues. However, when I try to add time (time), it outputs erroneous data I assume because it has a different data type. If I use another column such as ID (int), then it works. I am unsure if I need to convert time into a string or if there is another way around it.

The answer might lie in Change data type of columns in Pandas which seems similar, but because I am inserting data from MySQL, I am unsure how I could apply it to my own problem.

My end goal is to have a cron job that runs every five minutes and outputs an image based line chart with temps from the last 24 hours on the Y axis, and the time values along the X axis and then copies it to my WWW folder for display via HTML. I know the script is missing anything after image output, but that is easy and I've done that before. I just can't get the chart to display the X axis time values.

Any assistance would be appreciated.

import matplotlib.pyplot as plt
import pandas as pd
import MySQLdb

def mysql_select_all():
    conn = MySQLdb.connect(host='localhost',
                           user='user',
                           passwd='password',
                           db='database')
    cursor = conn.cursor()
    sql = "SELECT id,time,date,temperature FROM table ORDER BY id DESC LIMIT 288"
    cursor.execute(sql)
    result = cursor.fetchall()
    df = pd.DataFrame(list(result),columns=["id","time","date","temperature"])
    x = df.time
    y = df.temperature
    plt.title("Temperature Data from Previous 24 Hours", fontsize="20")
    plt.plot(x, y)
    plt.xlabel("Time")
    plt.ylabel("Temperature (\u2103)")
    plt.tick_params(axis='both',which='major',labelsize=14)
    plt.savefig('test.png')
    cursor.close()

print("Start")

mysql_select_all()

print("End")

The above code currently outputs the below image.

First and Last lines from the DataFrame

id                         688
time           0 days 09:55:01
date                2019-01-24
temperature              27.75
Name: 0, dtype: object
id                         401
time           0 days 10:00:01
date                2019-01-23
temperature               24.4
Name: 287, dtype: object

It isn't outputting an error at all, but if you look at the X axis, it is converting it to a float somehow, rather than outputting correctly which is %H:%M:%S. — Trido, Jan 22 '19 at 04:56
See this:https://matplotlib.org/gallery/ticks_and_spines/date_demo_rrule.html#sphx-glr-gallery-ticks-and-spines-date-demo-rrule-py — I_Al-thamary, Jan 22 '19 at 05:04
Which dtype is the time column? Can you print an example time and the corresponding formatted time you expect, such that we can know how to help you here? — ImportanceOfBeingErnest, Jan 22 '19 at 12:00
I edited my question to include 2 additional secreenshots. First is the output from a DESCRIBE in MySQL of the table in question showing you the data types, and the second is the last few rows of data from the database showing what is stored in the table. My python script inserts the time in the format %H:%M:%S. I want to use that for my charts X axis so I can display the previous 24 hours worth of readings, and have an idea of what time the reading took place. — Trido, Jan 22 '19 at 22:04
Could you rather show the first and last row of your pandas dataframe and state which datetime the number in it would correspond to? — ImportanceOfBeingErnest, Jan 22 '19 at 23:53
Um, do you mean the SQL query results that feed into the DataFrame? If so, running the query `SELECT id,time,temperature FROM table ORDER BY id DESC LIMIT 288` gives me the following. 1st row: id 412, time 10:55:01, temperature 24.32. Last row: id 128, time 11:15:01, temperature 28.46. I'm sorry but not 100% sure what you meant with your second request for info. — Trido, Jan 23 '19 at 01:44
Ok, that's one third of the information needed. From the SQL result, one would still need the `date`. And then the first and last row of the *dataframe*, i.e. `print(df.iloc[0,:]); print(df.iloc[-1,:])`. — ImportanceOfBeingErnest, Jan 23 '19 at 02:34
Thanks for that info about outputting the first and last parts of the DataFrame. I included that in my question. That data looks good except for the 0 days part which I believe is because it is seen as a timedelta64? — Trido, Jan 23 '19 at 23:54
I also made a couple of small changes to the query and DataFrame and updated the question as well. It was only to include the id to order it properly, and the date. — Trido, Jan 23 '19 at 23:59
Am I having so much trouble because I separated date and time? If I substituted the date and time columns with a datetime, would this be easier? I only kept them separate because I thought it would be easier to not have to strip the datetime apart to display a date or time where required. — Trido, Jan 24 '19 at 21:42

score 1 · Answer 1 · answered Jan 23 '19 at 05:39

try pandas.to_datetime() function. It could convert string or integer to datetime format.

original code

    df = pd.DataFrame(list(result),columns=["time","temperature"])
    x = df.time
    y = df.temperature

new code

    df = pd.DataFrame(list(result),columns=["time","temperature"])

    df["time"]=df["time"].astype(np.datetime64)
    #or below code.
    #df["time"]=pd.to_datetime(df["time"])
    #assuming that df.time could be converted to datetime format.
    x = df.time
    y = df.temperature

For other code you can keep it as original though df.plot() could show plot more convenient.

Pandas DataFrame, matplotlib and different SQL data types

1 Answers1