AIM: The aim of this IPython notebook is to find out the wake up time of an individual in the home using PIR data
from IPython.display import HTML
import requests
import pandas as pd
import MySQLdb
import pandas.io.sql as psql
mysql_conn=MySQLdb.connect(user='root',passwd='password',db='multisensor');
import datetime
import time
import pytz
import time
import matplotlib.dates as mdates
import matplotlib.pyplot as plt
Setting up the styles!
styles = requests.get("https://raw.github.com/CamDavidsonPilon/Probabilistic-Programming-and-Bayesian-Methods-for-Hackers/master/styles/custom.css")
HTML(styles.text)
import json
s = requests.get("https://raw.github.com/CamDavidsonPilon/Probabilistic-Programming-and-Bayesian-Methods-for-Hackers/master/styles/bmh_matplotlibrc.json").json()
matplotlib.rcParams.update(s)
figsize(10,7)
query='select timestamp from pir where node_id=4;'
result=psql.frame_query(query,mysql_conn)
result.describe()
timestamp | |
---|---|
count | 8.181100e+04 |
mean | 1.348430e+09 |
std | 1.928466e+08 |
min | -7.525909e+08 |
25% | 1.370145e+09 |
50% | 1.371938e+09 |
75% | 1.373857e+09 |
max | 1.375716e+09 |
result['pir']=np.ones(len(result.index))
result.describe()
timestamp | pir | |
---|---|---|
count | 8.181100e+04 | 81811 |
mean | 1.348430e+09 | 1 |
std | 1.928466e+08 | 0 |
min | -7.525909e+08 | 1 |
25% | 1.370145e+09 | 1 |
50% | 1.371938e+09 | 1 |
75% | 1.373857e+09 | 1 |
max | 1.375716e+09 | 1 |
result.index=pd.to_datetime(result.timestamp*1e9)
result
<class 'pandas.core.frame.DataFrame'> DatetimeIndex: 81811 entries, 1946-02-25 10:58:49 to 2013-08-05 15:15:58 Data columns (total 2 columns): timestamp 81811 non-null values pir 81811 non-null values dtypes: float64(1), int64(1)
result=result['2012':]
from pandas.tools.plotting import scatter_matrix
scatter_matrix(result)
array([[<matplotlib.axes.AxesSubplot object at 0x58d5950>]], dtype=object)
plt.scatter(result.index,result.pir.values)
<matplotlib.collections.PathCollection at 0x12ec2c50>
x=result.index.values.astype(int)/1e9
x.astype('datetime64[s]')
array(['2013-05-24T14:59:59+0530', '2013-05-24T15:00:11+0530', '2013-05-24T15:00:14+0530', ..., '2013-08-05T20:11:30+0530', '2013-08-05T20:23:13+0530', '2013-08-05T20:45:58+0530'], dtype='datetime64[s]')
df_grouped=result.groupby(result.index.date)
times_first=df_grouped.first().timestamp.values.astype('datetime64[s]')
df2=pd.DataFrame(index=times_first)
df2.index.values[:10]
array(['2013-05-24T14:59:59.000000000+0530', '2013-05-25T15:47:30.000000000+0530', '2013-05-26T11:08:53.000000000+0530', '2013-05-27T06:57:28.000000000+0530', '2013-05-28T05:41:11.000000000+0530', '2013-05-30T15:46:24.000000000+0530', '2013-05-31T05:31:22.000000000+0530', '2013-06-01T05:30:00.000000000+0530', '2013-06-02T08:17:33.000000000+0530', '2013-06-03T05:34:09.000000000+0530'], dtype='datetime64[ns]')
df2.index.hour[:10]
array([ 9, 10, 5, 1, 0, 10, 0, 0, 2, 0], dtype=int32)
df2.index.minute
array([29, 17, 38, 27, 11, 16, 1, 0, 47, 4, 0, 11, 35, 47, 22, 1, 28, 8, 40, 9, 0, 8, 14, 12, 0, 7, 0, 56, 9, 9, 12, 33, 19, 1, 2, 3, 0, 20, 27, 1, 17, 51, 43, 13, 45, 2, 29, 26, 20, 4, 4, 24, 0, 14, 26, 41, 6, 15, 14, 27, 5, 59, 2, 50, 31, 40, 8, 4, 4, 30, 25, 0, 9], dtype=int32)
plt.scatter(df2.index.date,df2.index.hour)
plt.ylim((-1,12))
(-1, 12)
times_last=df_grouped.last().timestamp.values.astype('datetime64[s]')
df3=pd.DataFrame(index=times_last)
plt.bar(df3.index.date,df3.index.hour)
<Container object of 73 artists>
df3.index.values
array(['2013-05-24T20:39:27.000000000+0530', '2013-05-25T16:45:26.000000000+0530', '2013-05-27T04:01:30.000000000+0530', '2013-05-28T05:14:29.000000000+0530', '2013-05-28T12:09:09.000000000+0530', '2013-05-31T05:12:00.000000000+0530', '2013-06-01T05:29:51.000000000+0530', '2013-06-01T10:37:56.000000000+0530', '2013-06-03T05:29:51.000000000+0530', '2013-06-04T05:25:27.000000000+0530', '2013-06-05T05:20:04.000000000+0530', '2013-06-06T05:25:48.000000000+0530', '2013-06-06T23:11:50.000000000+0530', '2013-06-08T05:27:39.000000000+0530', '2013-06-08T23:25:56.000000000+0530', '2013-06-09T22:22:04.000000000+0530', '2013-06-11T05:29:18.000000000+0530', '2013-06-12T05:26:47.000000000+0530', '2013-06-12T18:01:39.000000000+0530', '2013-06-14T05:13:13.000000000+0530', '2013-06-15T03:25:49.000000000+0530', '2013-06-16T05:27:18.000000000+0530', '2013-06-17T04:00:11.000000000+0530', '2013-06-18T05:29:59.000000000+0530', '2013-06-19T05:21:01.000000000+0530', '2013-06-20T05:29:54.000000000+0530', '2013-06-21T05:17:51.000000000+0530', '2013-06-22T03:06:58.000000000+0530', '2013-06-23T05:16:26.000000000+0530', '2013-06-24T02:16:39.000000000+0530', '2013-06-24T22:26:44.000000000+0530', '2013-06-26T05:28:36.000000000+0530', '2013-06-27T03:15:30.000000000+0530', '2013-06-28T05:20:58.000000000+0530', '2013-06-29T05:25:07.000000000+0530', '2013-06-30T05:01:40.000000000+0530', '2013-07-01T05:16:39.000000000+0530', '2013-07-01T17:56:08.000000000+0530', '2013-07-03T05:29:49.000000000+0530', '2013-07-04T05:10:25.000000000+0530', '2013-07-04T18:51:16.000000000+0530', '2013-07-05T22:34:27.000000000+0530', '2013-07-07T04:57:07.000000000+0530', '2013-07-08T04:16:09.000000000+0530', '2013-07-09T05:06:10.000000000+0530', '2013-07-10T03:54:50.000000000+0530', '2013-07-11T05:29:06.000000000+0530', '2013-07-12T05:20:53.000000000+0530', '2013-07-13T03:11:23.000000000+0530', '2013-07-14T04:45:46.000000000+0530', '2013-07-15T05:29:12.000000000+0530', '2013-07-16T05:29:32.000000000+0530', '2013-07-17T05:18:45.000000000+0530', '2013-07-18T05:29:55.000000000+0530', '2013-07-19T05:03:18.000000000+0530', '2013-07-20T05:09:05.000000000+0530', '2013-07-20T21:54:49.000000000+0530', '2013-07-22T05:07:21.000000000+0530', '2013-07-23T05:09:43.000000000+0530', '2013-07-24T05:13:57.000000000+0530', '2013-07-25T05:24:31.000000000+0530', '2013-07-26T05:24:40.000000000+0530', '2013-07-27T04:57:58.000000000+0530', '2013-07-28T05:11:36.000000000+0530', '2013-07-29T04:40:17.000000000+0530', '2013-07-30T05:29:22.000000000+0530', '2013-07-30T21:54:42.000000000+0530', '2013-08-01T05:28:06.000000000+0530', '2013-08-02T05:20:27.000000000+0530', '2013-08-03T02:18:39.000000000+0530', '2013-08-04T05:06:02.000000000+0530', '2013-08-05T05:12:46.000000000+0530', '2013-08-05T20:45:58.000000000+0530'], dtype='datetime64[ns]')
a=np.datetime64()
plt.plot(x.astype('datetime64[s]'),result.pir)
plt.
[<matplotlib.lines.Line2D at 0x5e0c5d0>]
start_date=datetime.datetime(2013,5,28,0,0,0,tzinfo=pytz.timezone('Asia/Kolkata'))
jplug_ids=["001EC00CC4A0","001EC00CC4A1","001EC00CC4AD","001EC00CC49C","001EC00CC49F","001EC00D7A18","001EC00CC49D","001EC00D7A1D","001EC00D7A1C"]
#jplug_ids=["001EC00CC4A0"]
num_days=61
stats={}
for jplug in jplug_ids:
stats[jplug]={}
stats[jplug]['X']=[]
stats[jplug]['count']=[]
stats[jplug]['mean']=[]
stats[jplug]['max']=[]
stats[jplug]['min']=[]
for i in range(num_days):
start=start_date+datetime.timedelta(i)
end=start_date+datetime.timedelta(i+1)
start_timestamp=int(time.mktime(start.timetuple()))
end_timestamp=int(time.mktime(end.timetuple()))
query='select count(*) from jplug_data where mac="%s" and timestamp between %d and %d;' %(jplug,start_timestamp,end_timestamp)
result=psql.frame_query(query,mysql_conn)
stats[jplug]['count'].append(result.values[0][0])
stats[jplug]['X'].append(start)
query='select avg(active_power) from jplug_data where mac="%s" and timestamp between %d and %d;' %(jplug,start_timestamp,end_timestamp)
result=psql.frame_query(query,mysql_conn)
if result.values[0][0] is not None:
stats[jplug]['mean'].append(result.values[0][0])
else:
stats[jplug]['mean'].append(0)
plt.figure()
plt.subplot(2,1,1)
plt.bar(stats[jplug]['X'],stats[jplug]['count'])
plt.title("Number of points for %s" %jplug)
plt.subplot(2,1,2)
plt.bar(stats[jplug]['X'],stats[jplug]['mean'])
plt.title("Average active power for %s" %jplug)
plt.ylabel('Average Active Power (W)')
plt.tight_layout()