{
"metadata": {
"name": ""
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "heading",
"level": 5,
"metadata": {},
"source": [
"Data Start and End Times"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"AIM: The aim of this IPython notebook is to find the start and end times of collected data for different sensor streams."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"from IPython.display import HTML\n",
"import requests\n",
"import pandas as pd\n",
"import MySQLdb\n",
"import pandas.io.sql as psql\n",
"import datetime\n",
"import time\n",
"import pytz\n",
"import time\n",
"import matplotlib.dates as mdates\n",
"import matplotlib.pyplot as plt\n",
"from collections import OrderedDict"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 5
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mysql_conn=OrderedDict()\n",
"dbs=[\"multisensor\",\"smart_meter\",\"water_meter\",\"sys_info\",\"jplug\"]\n",
"tables=[\"light_temp\",\"smart_meter_data\",\"water_data\",\"sys_info_data\",\"jplug_data\"]\n",
"for db in dbs:\n",
" mysql_conn[db]=MySQLdb.connect(user='root',passwd='password',db=db)\n"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 9
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Setting up the styles!"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"styles = requests.get(\"https://raw.github.com/CamDavidsonPilon/Probabilistic-Programming-and-Bayesian-Methods-for-Hackers/master/styles/custom.css\")\n",
"HTML(styles.text)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"\n",
""
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 3,
"text": [
""
]
}
],
"prompt_number": 3
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import json\n",
"s = requests.get(\"https://raw.github.com/CamDavidsonPilon/Probabilistic-Programming-and-Bayesian-Methods-for-Hackers/master/styles/bmh_matplotlibrc.json\").json()\n",
"matplotlib.rcParams.update(s)\n",
"figsize(10,7)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 4
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"result={}\n",
"for i in range(len(tables)):\n",
" if tables[i]!=\"sys_info_data\":\n",
" query='select MIN(timestamp) as min,MAX(timestamp) as max from %s where timestamp between 1357277453 and 1388813453;' %tables[i]\n",
" else:\n",
" query='select MIN(server_time) as min,MAX(server_time) as max from %s where server_time between 1357277453 and 1388813453;' %tables[i]\n",
" result[dbs[i]]=psql.frame_query(query,mysql_conn[dbs[i]])\n"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 15
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"result"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 16,
"text": [
"{'jplug': min max\n",
"0 1368980586 1379473855,\n",
" 'multisensor': min max\n",
"0 1369387845 1375716189,\n",
" 'smart_meter': min max\n",
"0 1368696574 1375716796,\n",
" 'sys_info': min max\n",
"0 1370251254 1375720317,\n",
" 'water_meter': min max\n",
"0 1.369903e+09 1.375429e+09}"
]
}
],
"prompt_number": 16
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"for db in dbs:\n",
" print \"%s : Starts at: %s; Ends at: %s\\n\" %(db,datetime.datetime.fromtimestamp(result[db]['min']).strftime('%Y-%m-%d'),datetime.datetime.fromtimestamp(result[db]['max']).strftime('%Y-%m-%d'))"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"multisensor : Starts at: 2013-05-24; Ends at: 2013-08-05\n",
"\n",
"smart_meter : Starts at: 2013-05-16; Ends at: 2013-08-05\n",
"\n",
"water_meter : Starts at: 2013-05-30; Ends at: 2013-08-02\n",
"\n",
"sys_info : Starts at: 2013-06-03; Ends at: 2013-08-05\n",
"\n",
"jplug : Starts at: 2013-05-19; Ends at: 2013-09-18\n",
"\n"
]
}
],
"prompt_number": 21
}
],
"metadata": {}
}
]
}