From: Roman Bazalevsky <rvb@rvb.name>
Date: Thu, 22 Sep 2016 12:21:13 +0000 (+0300)
Subject: Фильтрация выбросов в измерениях
X-Git-Url: https://git.rvb.name/openhab-process.git/commitdiff_plain/f73d4c08f9b3d93ad6e4a6b35b164e4ae0f1e4be?ds=sidebyside

Фильтрация выбросов в измерениях
---

f73d4c08f9b3d93ad6e4a6b35b164e4ae0f1e4be
diff --git a/filter_sensors.py b/filter_sensors.py
new file mode 100644
index 0000000..8a5c0b9
--- /dev/null
+++ b/filter_sensors.py
@@ -0,0 +1,117 @@
+#!/usr/bin/python
+
+import MySQLdb
+import ConfigParser
+
+from pprint import pprint
+import datetime
+
+import numpy as np
+
+import scipy.signal
+
+global database
+
+def GetTables(name):
+  if database:
+    c = database.cursor()
+    c.execute("SELECT * FROM Items WHERE ItemName like %s",[name])
+    return c.fetchall()
+  else:
+    print "No connection to DB"
+    exit()
+
+def Today():
+  dt = datetime.datetime.now()
+  d_truncated = datetime.date(dt.year, dt.month, dt.day)
+  return d_truncated
+  
+def Tomorrow():
+  dt = Today()
+  return dt + datetime.timedelta(days=1)
+
+def Yesterday():
+  dt = Today()
+  return dt - datetime.timedelta(days=1)
+
+def GetData(id,fromDate=Yesterday(),toDate=Today()):
+  if database:
+    c = database.cursor()
+    c.execute("SELECT * FROM Item"+str(id).strip()+" WHERE Time>=%s AND Time<%s",[fromDate.strftime('%Y-%m-%d %H:%M:%S'),toDate.strftime('%Y-%m-%d %H:%M:%S')])
+    return c.fetchall()
+  else:
+    print "No connection to DB"
+    exit()
+
+def FixRecord(id,date,value):
+  if database:
+    c = database.cursor()
+    command="UPDATE Item"+str(id).strip()+" SET Value={} WHERE Time='{}'".format(value,date.strftime('%Y-%m-%d %H:%M:%S'))
+    print command
+    c.execute(command)
+  else:
+    print "No connection to DB"
+    exit()
+
+try:
+
+  cfg = ConfigParser.RawConfigParser(allow_no_value=True)
+  cfg.readfp(open('/etc/openhab-db.conf'))
+  dbhost = cfg.get("mysql","host")
+  dbuser = cfg.get("mysql","user")
+  dbpasswd = cfg.get("mysql","passwd")
+  dbdb = cfg.get("mysql","db")
+
+  itemTemplate = cfg.get("openhab","template")
+  
+  filterWindow = int(cfg.get("filter","window"))
+  filterThreshold = float(cfg.get("filter","threshold"))
+   
+except:
+
+  print "Error reading configuration file"
+  exit()
+
+try:
+
+  database = MySQLdb.connect(host=dbhost,user=dbuser,passwd=dbpasswd,db=dbdb,use_unicode=True)
+  database.set_character_set('utf8')
+  c = database.cursor()
+  c.execute('SET NAMES utf8;')
+
+  print "Connected..."
+
+except:
+
+  print "Error connecting database"
+  exit()
+
+tables = GetTables(itemTemplate)
+
+for id,name in tables:
+
+  print "Processing: "+name
+
+  data=GetData(id)
+  sTime=[]
+  sValue=[]
+  for rec in data:
+    sTime.append(rec[0])
+    sValue.append(rec[1])
+  sValue=np.array(sValue)
+
+  sValueFilt=scipy.signal.medfilt(sValue,5)
+
+  sValueDiff=abs(sValue-sValueFilt)
+  
+  avg=np.mean(sValueDiff)
+
+  for i in range(0,len(sTime)-1):
+    if sValueDiff[i]>avg*filterThreshold:
+      print "fixing %s : %5.2f %5.2f %5.2f" % (sTime[i],sValue[i],sValueFilt[i],sValueDiff[i])
+      FixRecord(id,sTime[i],sValueFilt[i])      
+
+  database.commit()
+
+print "Processed "
+  
\ No newline at end of file