1

I'm using a temperature and humidity sensor connected to a Raspberry Pi to log those in regular intervals into a sqlite3 DB with the following schema: 

sqlite> .schema
CREATE TABLE sensor_data (tdate DATE, ttime TIME, temperature NUMERIC, humidity NUMERIC);

For test purposes there are just two entries: 

sqlite> SELECT * FROM sensor_data;
2017-03-02|19:04:42|19.8999996185303|51.5
2017-03-02|19:26:08|19.8999996185303|51.2000007629395

Now I'd like to analyze and plot the data using pandas: 

#! /usr/bin/python
from datetime import datetime
import sqlite3
import pandas as pd

conn = sqlite3.connect('test.db')

    with conn:
        curs = conn.cursor()
        df = pd.read_sql_query("SELECT * FROM sensor_data", conn,
                        parse_dates={'tdate':"%Y-%m-%d",'ttime':"%H:%M:%S"})
        print(df)

conn.close()

The result: 

$ ./test.py 
   tdate               ttime  temperature   humidity
0 2017-03-02 1900-01-01 19:04:42         19.9  51.500000
1 2017-03-02 1900-01-01 19:26:08         19.9  51.200001

My question is: How do I get rid of the column containing the 1900-01-01's and then combine tdate and ttime into one column?

Thanks in advance!

Cheers, Lars

MattR
  • 4,887
  • 9
  • 40
  • 67
Lars B.
  • 13
  • 3
  • is `1900-01-01` its own column or is a part of another column's value? – MattR Mar 02 '17 at 20:38
  • [this](http://stackoverflow.com/questions/17978092/combine-date-and-time-columns-using-python-pandas) might also come in handy when wanting to combine those dates – MattR Mar 02 '17 at 20:46
  • It looks like it is part of df['ttime'], maybe my formatting string is incorrect? – Lars B. Mar 02 '17 at 20:51

1 Answers1

0

Try this:

In [13]: df = pd.read_sql_query("SELECT * FROM sensor_data", conn,
    ...:                        parse_dates=['tdate','ttime']).drop('tdate', 1)

In [14]: df
Out[14]:
                ttime  temperature  humidity
0 2017-03-02 19:04:42         19.9      51.5
1 2017-03-02 19:26:08         19.9      51.2
MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419