def query_data_old(VAV_Obj, sensor_name, start_date='4/1/2015',
end_date='4/2/2015', interpolation_time='5min', limit=-1,
externalID=None, useOptions=False):
if useOptions:
start_date = Options.data['starttime']
end_date = Options.data['endtime']
interpolation_time = Options.data['interpolationtime']
limit = eval(Options.data['limit'])
client_obj = SmapClient(VAV_Obj.serverAddr)
if (VAV_Obj.sensors is None or VAV_Obj.sensors.get(sensor_name) is None) and externalID is None:
print 'no ' + sensor_name + ' info'
return None
if externalID is None:
sensorID = VAV_Obj.sensors.get(sensor_name)[0]
else:
sensorID = externalID
if start_date is None and end_date is None:
#print 'select data before now limit ' + str(limit) + ' where uuid = \'' + self.sensors.get(sensor_name)[0] + '\''
# x = client_obj.query('select data before now limit ' + str(limit) + ' where uuid = \'' + self.sensors.get(sensor_name)[0] + '\'')
q = client_obj.query('select data before now limit ' + str(limit) + ' where uuid = \'' + sensorID + '\'')
else:
#print 'select data in (\'' + start_date + '\', \'' + end_date + '\') limit ' + str(limit) + ' where uuid = \'' + self.sensors.get(sensor_name)[0] + '\''
q = client_obj.query('select data in (\'' + start_date + '\', \'' + end_date + '\') limit ' + str(limit) + ' where uuid = \'' + sensorID + '\'')
data_table = pd.DataFrame(q[0]['Readings'], columns=['Time', sensor_name])
data_table['Time'] = pd.to_datetime(data_table['Time'].tolist(), unit='ms').tz_localize('UTC').tz_convert('America/Los_Angeles')
data_table.set_index('Time', inplace=True)
data_table = data_table.groupby(pd.TimeGrouper(interpolation_time)).mean().interpolate(method='linear').dropna()
return data_table
def queryData(address, where=None, fullQ=None):
c = SmapClient(address)
if fullQ is None:
qList = c.query("select uuid, Path where " + where)
else:
qList = c.query(fullQ)
return qList
class SMAPDataRetriever(object):
def __init__(self):
self.smapc = SmapClient("http://new.openbms.org/backend")
def get_data(self, room, building, startDate, endDate):
print "Pulling in data for room : ", room
data_temp = self.smapc.query("apply window(mean, field='minute',width=10) to data in ('%s','%s') where Metadata/Name='STA_%s____ART'" % (startDate, endDate, room))
data_reheat = self.smapc.query("apply window(mean, field='minute',width=10) to data in ('%s','%s') where Metadata/Name='STA_%s____RVP'" % (startDate, endDate, room))
data_flow = self.smapc.query("apply window(mean, field='minute',width=10) to data in ('%s','%s') where Metadata/Name='STA_%s___SVEL'" % (startDate, endDate, room))
data_oat = self.smapc.query("apply window(mean, field='minute',width=10) to data in ('%s','%s') where Metadata/Name='STA_%s__OAT'" % (startDate, endDate, building))
data = {}
for reading in data_temp[0]["Readings"]:
data[int(reading[0])] = {}
data[int(reading[0])]["temp"] = float(reading[1])
for reading in data_reheat[0]["Readings"]:
data[int(reading[0])]["reheat"] = float(reading[1])
for reading in data_flow[0]["Readings"]:
data[int(reading[0])]["flow"] = float(reading[1])
for reading in data_oat[0]["Readings"]:
data[int(reading[0])]["outtemp"] = float(reading[1])
return data
def openBMS(nodeDic):
resList = []
c = SmapClient('http://ar1.openbms.org:8079')
#counts = c.query(("apply count to data in (%s) streamlimit 50 "
counts = c.query(("apply count to data in (%s) limit -1 "
"where Metadata/Instrument/PartNumber = '%s'") %
(nodeDic['periodStr'], nodeDic['id']))
for v in counts:
#print "readings : %s" % v['Readings']
r = np.array(v['Readings'])
Bcnt = int(v['Metadata']['Instrument']['PartNumber'])
if len(r):
if( 0 < (Bcnt-1600) and (Bcnt-1600) < 151 ):
Pcnt = np.sum(r[:, 1]) * 100 / (int(nodeDic['period']) * 60 / 5)
print "THL : %s (%s %s)" % \
(v['Metadata']['Instrument']['PartNumber'], Pcnt, "%")
print "\t(cnt : %s) - %s" % (np.sum(r[:, 1]), v['Properties']['UnitofMeasure'])
elif( 0 < (Bcnt-2000) and (Bcnt-2000) < 151 ):
Pcnt = np.sum(r[:, 1]) * 100 / (int(nodeDic['period']) * 60 / 5)
print "CO2 : %s (%s %s)" % \
(v['Metadata']['Instrument']['PartNumber'], Pcnt, "%")
print "\t(cnt : %s) - %s" % (np.sum(r[:, 1]), v['Properties']['UnitofMeasure'])
elif( 0 < (Bcnt-5000) and (Bcnt-5000) < 151 ):
Pcnt = np.sum(r[:, 1]) * 100 / (int(nodeDic['period']) * 60 / 10)
print "PIR : %s (%s %s)" % \
(v['Metadata']['Instrument']['PartNumber'], Pcnt, "%")
print "\t(cnt : %s) - %s" % (np.sum(r[:, 1]), v['Properties']['UnitofMeasure'])
resList.append(Pcnt)
#print r[:, 0] #date
#print r[:, 1] #data
#prrs.append(np.sum(r[:, 1]) / (3600 * (HOURS) / rate))
return average(resList)
def read_stream_data(self, num_days=1):
self.points = {}
c = SmapClient("http://new.openbms.org/backend")
for point in self.input_variables:
q = "apply window(mean, field='second',width='%d') to data in (\"03/01/2015\" -%ddays, \"03/07/2015\") where Metadata/Name='%s'" % \
( self.rate, num_days, point )
print q
result = c.query(q)
readings = result[0]["Readings"]
self.points[point] = [ r[1] for r in result[0]["Readings"] ]
for point in self.state_variables:
query = "apply window(mean, field='second',width='%d') to data in (\"03/01/2015\" -%ddays, \"03/07/2015\") where Metadata/Name='%s'" % \
( self.rate, num_days, point )
result = c.query(query)
readings = result[0]["Readings"]
self.points[point] = [ r[1] for r in result[0]["Readings"] ]
self.predictions = []
self.model_params = []
self.actual_outputs = []
def read_stream_data(self, num_days=1):
self.points = {}
c = SmapClient("http://new.openbms.org/backend")
for point in self.input_variables:
q = "apply window(mean, field='second',width='%d') to data in (\"03/01/2015\" -%ddays, \"03/07/2015\") where Metadata/Name='%s'" % \
( self.rate, num_days, point )
print q
result = c.query(q)
readings = result[0]["Readings"]
self.points[point] = [r[1] for r in result[0]["Readings"]]
for point in self.state_variables:
query = "apply window(mean, field='second',width='%d') to data in (\"03/01/2015\" -%ddays, \"03/07/2015\") where Metadata/Name='%s'" % \
( self.rate, num_days, point )
result = c.query(query)
readings = result[0]["Readings"]
self.points[point] = [r[1] for r in result[0]["Readings"]]
self.predictions = []
self.model_params = []
self.actual_outputs = []
restrict = " Metadata/SourceName = '%s' and "%source\
+ ' and '.join(["Path ~ '%s'"] * len(path_list_and))\
%tuple(path_list_and) + " and ("\
+ ' or '.join(["Path ~ '%s'"] * len(path_list_or)) \
%tuple(path_list_or) + ")"
tags = c.tags(restrict)
startDate = "01/01/2014"
endDate = "01/02/2014"
print "Start date: ", str(startDate),
print "End date: ", str(endDate)
name = 'Brower_data_V3.csv'
dt_format = '%Y-%m-%d %H:%M:%S'
query_data = 'apply window(first, field="hour") to data in ("' + startDate + '" , "' + str(endDate) + '") limit 10000000 where' + restrict
data = c.query(query_data)
N=len(data)
df = pd.DataFrame()
for i in range(N):
d = np.array(data[i]['Readings'])
if d.any():
df['timestamp'] = d[:,0]
df['datetime'] = [datetime.datetime.fromtimestamp(x/1000).strftime(dt_format) for x in d[:,0]]
for p in path_list_or:
if p in data[i]['Path']:
try:
df[p] = d[:,1]
except:
pdb.set_trace()
print "Data for " + p + " downloaded."
@author Stephen Dawson-Haggerty <*****@*****.**>
"""
import datetime
from smap.archiver.client import SmapClient
from smap.contrib import dtutil
import numpy as np
import matplotlib.pyplot as plt
c = SmapClient('http://ar1.openbms.org:8079')
HOURS = 5
RATES = [#("#", 10),
("ppm", 5), ("C", 5)]
prrs = []
for unit, rate in RATES:
counts = c.query(("apply count to data in now -%ih, now "
"limit -1 streamlimit 1000 where "
"Properties/UnitofMeasure = '%s' and "
"Metadata/SourceName = 'KETI Motes'") %
(HOURS, unit))
for v in counts:
r = np.array(v['Readings'])
if len(r):
prrs.append(np.sum(r[:, 1]) / (3600 * (HOURS) / rate))
plt.hist(prrs, bins=25)
plt.show()
from smap.archiver.client import SmapClient
from smap.contrib import dtutil
from datetime import datetime
import os
# make a client
c = SmapClient("http://new.openbms.org/backend")
start = datetime.now()
f = open('uuid','r')
uuid = [i.strip('\n') for i in open('uuid', 'r').readlines()]
# ts = range(len(uuid))
# table = dict(zip(uuid,ts))
f.close()
# for u in uuid:
stnc = "select data before now streamlimit -1 where Metadata/SourceName ~ '.*'"
out = c.query(stnc)
for rd in out:
table[rd['uuid']] = (int)(rd['Readings'][0][0]/1000)
f = open('freshTable','w')
f.writelines("%s:%s\n"%(k,v) for k,v in table.items())
f.close()
print "Time elapsed:", str(datetime.now()-start)
if not os.path.isdir(outputDir):
print "File with the same name exists. Delete it first"
exit()
else:
os.makedirs(outputDir)
startDate = "6/08/2015"
endDate = "6/09/2015"
numRooms = len(validVAVs)
count = 0
for room in validVAVs:
count += 1
print "Pulling in data for room : ", room, "(%d/%d)" % (count, numRooms)
for sensors in validVAVs[room]:
for sensor in validVAVs[room][sensors]:
data = c.query("select data in ('%s','%s') where uuid='%s'" %
(startDate, endDate, sensor))
data_table = pd.DataFrame(data[0]['Readings'], columns=['Time', 'Readings'])
data_table['Time'] = pd.to_datetime(data_table['Time'].tolist(), unit='ms').tz_localize('UTC').tz_convert('America/Los_Angeles')
data_table.set_index('Time', inplace=True)
data_table = data_table.resample(interpolation_time)
data_table.to_csv(outputDir + '/' + sensor)
AHUSENSOR = "a7aa36e6-10c4-5008-8a02-039988f284df"
data = c.query("select data in ('%s','%s') where uuid='%s' " % (startDate, endDate, AHUSENSOR))
data_table = pd.DataFrame(data[0]['Readings'], columns=['Time', 'Readings'])
data_table['Time'] = pd.to_datetime(data_table['Time'].tolist(), unit='ms').tz_localize('UTC').tz_convert('America/Los_Angeles')
data_table.set_index('Time', inplace=True)
data_table = data_table.resample(interpolation_time)
data_table.to_csv(outputDir + '/' + AHUSENSOR)
if os.path.exists(outputDir):
if not os.path.isdir(outputDir):
print "File with the same name exists. Delete it first"
exit()
else:
os.makedirs(outputDir)
startDate = "05/27/2015"
endDate = "07/04/2015"
numRooms = len(roomMetadata)
count = 0
for room in roomMetadata:
count += 1
print "Pulling in data for room : ", room , "(%d/%d)" % (count, numRooms )
for sensor in roomMetadata[room]:
data = c.query("select data in ('%s','%s') where Metadata/Name='%s'" %
(startDate, endDate, roomMetadata[room][sensor]) )
outf = open(outputDir + "/" + roomMetadata[room][sensor], "w")
if len(data) == 0:
continue
for reading in data[0]["Readings"]:
outf.write( str(int(reading[0])).strip() + "," + str(reading[1]).strip() + "\n" )
outf.close()
from smap.contrib import dtutil
from matplotlib import pyplot
from matplotlib import dates
import os
# make a client
c = SmapClient("http://new.openbms.org/backend")
# start and end values are Unix timestamps
t_start = "1-1-2012 0:00"
t_end = "1-1-2013 0:00"
start = 1000*dtutil.dt2ts(dtutil.strptime_tz(t_start, "%m-%d-%Y %H:%M"))
end = 1000*dtutil.dt2ts(dtutil.strptime_tz(t_end, "%m-%d-%Y %H:%M"))
stnc = "select distinct Path where Metadata/SourceName='Cory Hall Dent Meters' and Path ~ '(?i)power$' and not Path ~ '.*ABC.*'"
pathlist = c.query(stnc) #the result is a list
pathlist = pathlist[275:]
for path in pathlist:
print "==========Fetching streams in path %s=========="%path
for s in path:
# fetch the metadata of path wanted
tags = c.tags("Path='%s'"%path)
# mkdir for each path
path1 = "/Users/hdz_1989/Downloads/SDB/Cory"
# folder = path
# if not os.path.exists(path1+'/'+folder):
# os.makedirs(path1+'/'+folder)
# ft = open(path+'/'+folder+'/' + 'date.txt', 'w')
from smap.archiver.client import SmapClient
import time
import datetime
import json
import pandas as pd
#pd.options.display.mpl_style = 'default'
client = SmapClient('http://ciee.cal-sdb.org:8079')
# timestamps
end = int(time.time())
#start = end - 60*60*24*30 # last month
start = end - 60*60*24*7 # last week
zones = client.query('select distinct Metadata/HVACZone')
def getdataasjson(query, start, end):
tmp_data = client.data(query,start,end,cache=True,limit=500000)
if not len(tmp_data[0]):
return {}
tmp = pd.DataFrame(tmp_data[1][0])
if len(tmp.notnull()) == 0:
return {}
tmp = tmp[pd.np.abs(tmp[1] - tmp[1].mean()) <= 5*tmp[1].std()]
tmp[0] = pd.to_datetime(tmp[0], unit='ms')
tmp.index = tmp[0]
tmp = tmp.drop_duplicates()
del tmp[0]
return json.loads(tmp.to_json())["1"]
def get_demand():
if end == '-d':
end = int(time.time()*1000)
else:
end = dtutil.dt2ts(dtutil.strptime_tz("%s" %end, "%m-%d-%Y %H:%M")) * 1000
if start == '-d':
start = end - 7*24*60*60*1000
else:
start = dtutil.dt2ts(dtutil.strptime_tz("%s" %start, "%m-%d-%Y %H:%M")) * 1000
applySum = "apply nansum(axis=1) < paste < window(first, field='minute', width=15) < units to data in (%f, %f) limit -1 streamlimit 10000 \
where (Metadata/Extra/System = 'total' or Metadata/Extra/System = 'electric') \
and (Properties/UnitofMeasure = 'kW' or Properties/UnitofMeasure = 'Watts' or Properties/UnitofMeasure = 'W') \
and Metadata/Location/Building like '%s%%' and not Metadata/Extra/Operator like 'sum%%' \
and not Path like '%%demand' and not Path like '/Cory_Hall/Electric_5A7/ABC/real_power' and not Path like '/Cory_Hall/Electric_5B7/ABC/real_power'" \
%(start, end, bldg)
result = c.query(applySum) #the result is a list
reading = result[0]['Readings']
# output readings to file
fw = open('energy.csv', 'w')
fw.writelines("%s,%s\n" %(rd[0],rd[1]) for rd in reading)
fw.close()
# # adujst to data to an array of even length
# data = np.asarray(reading)
# data = data[:,1]
# if len(data)%2==0:
# data = data
# else:
# data = data[:len(data)-1]
# # print len(data)
from smap.contrib import dtutil
from matplotlib import pyplot
from matplotlib import dates
import os
import re
# make a client
c = SmapClient("http://new.openbms.org/backend")
# get tag list
tag = [i.strip('\n') for i in open('tagList.txt', 'r').readlines()]
# stnc = "select distinct"
# tag = c.query(stnc)
#prompt user to input
input = raw_input("keyword to query: ")
# regular expression search goes here...
# kw = input.split(' ')
# regex = re.compile("(?i).*(name).*")
# entry = [m.group(0) for l in tag for m in [regex.search(l)] if m]
# for e in entry:
for e in tag:
# stnc = "select distinct Path where %s like '%%%s%%'"%(e, input)
stnc = "select distinct Path where %s ~ '(?i).*%s.*'"%(e, input)
print ">>>>>>>>>>querying thru entry %s>>>>>>>>>>"%e
list = c.query(stnc) #the result is a list
if list:
print "the entry getting matches is", e
print '\n'.join(i for i in list)
outputDir = sys.argv[1].strip()
if os.path.exists(outputDir):
if not os.path.isdir(outputDir):
print "File with the same name exists. Delete it first"
exit()
else:
os.makedirs(outputDir)
startDate = "05/27/2015"
endDate = "07/04/2015"
numRooms = len(roomMetadata)
count = 0
for room in roomMetadata:
count += 1
print "Pulling in data for room : ", room, "(%d/%d)" % (count, numRooms)
for sensor in roomMetadata[room]:
data = c.query("select data in ('%s','%s') where Metadata/Name='%s'" %
(startDate, endDate, roomMetadata[room][sensor]))
outf = open(outputDir + "/" + roomMetadata[room][sensor], "w")
if len(data) == 0:
continue
for reading in data[0]["Readings"]:
outf.write(
str(int(reading[0])).strip() + "," + str(reading[1]).strip() +
"\n")
outf.close()
# make a dict mapping uuids to data vectors
path = "/Users/hdz_1989/Downloads/SDB/SDH"
folder = tags[0]['Path'].split('/')[-2]
if not os.path.exists(path+'/'+folder):
os.makedirs(path+'/'+folder)
ft = open(path+'/'+folder+'/' + 'date.txt', 'w')
ft.write(t_start + ' ~ ' + t_end)
ft.close()
for timeseries in tags:
uuid = timeseries['uuid']
filename = timeseries['Path'].split('/')[-1]
clause = "select data in (%.0f, %.0f) limit -1 where uuid = '%s'" \
%(start, end, uuid)
result = c.query(clause) #the result is a list
d = result[0]['Readings']
f = open(path+'/'+folder+'/'+filename + '.csv', 'w')
f.writelines(["%.0f, %.2f\n"%(float(i[0])/1000, float(i[1])) for i in d])
# f.writelines(["%.2f\n"%(float(i[1])) for i in d])
f.close()
# # plot all the data
# for timeseries in tags:
# d = data_map[timeseries['uuid']]
# # since we have the tags, we can add some metadata
# label = "%s (%s)" % (timeseries['Path'],
# timeseries['Properties/UnitofMeasure'])
# # we can plot all of the series in their appropriate time zones
# pyplot.plot_date(dates.epoch2num(d[:, 0] / 1000), d[:, 1], '-',
# label=label,
import os
import sys
from smap.archiver.client import SmapClient
from smap.contrib import dtutil
import re
import json
c = SmapClient("http://new.openbms.org/backend", key="NAXk19YY45TTiXlajiQGQ8KTp283oHfp2Uly")
rooms = c.query("select distinct Metadata/room-id where Metadata/site='STA'")
metadata = {}
count = 0
numRooms = len(rooms)
for room in rooms:
count += 1
print "Building Metadata for room : %s (%d/%d)" % (room, count, numRooms )
metadata[room] = {}
sensors = c.query("select * where Metadata/room-id='" + str(room) + "' and Metadata/site='STA'")
for i in range(len(sensors)):
if "Name" not in sensors[i]["Metadata"]:
continue
pointName = sensors[i]["Metadata"]["Name"]
roomMetadata = sensors[i]["Metadata"]
if "room_temp" in roomMetadata:
metadata[room]["room_temp"] = pointName
if "supply_air_velocity" in roomMetadata or "supply_air_volume" in roomMetadata:
metadata[room]["supply_air_velocity"] = pointName
if "reheat_valve_position" in roomMetadata:
metadata[room]["reheat_valve_position"] = pointName
train_data = [] test_data = [] search_train = [] # anomaly_train = [] search_test = [] # anomaly_test = [] count = 0 valid = [] for i in sdh_path: count += 1 if count%50==0: print '%.2f%% done...'%(float(count)/len(sdh_path)*100) q = "select data in (%.0f, %.0f) limit -1 where Path = '%s'" \ %(start1, end1, i) rd = c.query(q) if rd: if rd[0]['Readings']: if rd[0]['Readings'][0][1] and len(rd[0]['Readings'])>1: flag = 0 data = rd[0]['Readings'] train_data.append(get_feature(data)) if i in search_path: # for d in data: # if d[1]>72 or d[1]<60: # anomaly_path.append(i) # count1 += 1 # flag = 1; # break;
from matplotlib import pyplot
from matplotlib import dates
import os
import re
import json
import ast
from datetime import datetime
# make a client
c = SmapClient("http://new.openbms.org/backend")
# get tag list
#tag = [i.strip('\n') for i in open('tagList.txt', 'r').readlines()]
stnc = "select distinct Path"
result = c.query(stnc)
outFile = open("MetadataDump","w")
finalStructure = {}
count = 0
lastTime = datetime.now()
print len(result)
print "Experiment started at : ",lastTime
for tag in result:
queryString = "select * where Path='" + tag + "'"
out = c.query(queryString)
#finalStructure[tag] = out[0]
count += 1
if count % 100 == 0:
timeNow = datetime.now()
timeTaken = (timeNow - lastTime).seconds
import os
import sys
from smap.archiver.client import SmapClient
from smap.contrib import dtutil
import re
import json
c = SmapClient("http://new.openbms.org/backend",
key="NAXk19YY45TTiXlajiQGQ8KTp283oHfp2Uly")
rooms = c.query("select distinct Metadata/room-id where Metadata/site='STA'")
metadata = {}
count = 0
numRooms = len(rooms)
for room in rooms:
count += 1
print "Building Metadata for room : %s (%d/%d)" % (room, count, numRooms)
metadata[room] = {}
sensors = c.query("select * where Metadata/room-id='" + str(room) +
"' and Metadata/site='STA'")
for i in range(len(sensors)):
if "Name" not in sensors[i]["Metadata"]:
continue
pointName = sensors[i]["Metadata"]["Name"]
roomMetadata = sensors[i]["Metadata"]
if "room_temp" in roomMetadata:
metadata[room]["room_temp"] = pointName
if "supply_air_velocity" in roomMetadata or "supply_air_volume" in roomMetadata:
metadata[room]["supply_air_velocity"] = pointName
if "reheat_valve_position" in roomMetadata:
metadata[room]["reheat_valve_position"] = pointName
from smap.contrib import dtutil
from matplotlib import pyplot
from matplotlib import dates
import os
# make a client
c = SmapClient("http://new.openbms.org/backend")
# start and end values are Unix timestamps
t_start = "6-12-2013 8:00"
t_end = "6-19-2013 8:00"
start = 1000*dtutil.dt2ts(dtutil.strptime_tz(t_start, "%m-%d-%Y %H:%M"))
end = 1000*dtutil.dt2ts(dtutil.strptime_tz(t_end, "%m-%d-%Y %H:%M"))
stnc = "select distinct Metadata/Location/RoomNumber where Metadata/SourceName='KETI Motes'"
roomlist = c.query(stnc) #the result is a list
#roomlist = roomlist[16:]
#roomlist = ['621A','621B','621C','621D','621E']
for room in roomlist:
print "==========Fetching streams in Room %s=========="%room
stnc = "select Path where Metadata/Location/RoomNumber='%s' and not Path ~ '.*pir.*'" %room
streams = c.query(stnc)
if len(streams)>0:
# print "----%d streams in Room %s----"%(len(streams), room)
for s in streams:
# fetch the metadata of path wanted
tags = c.tags("Path='%s'"%s['Path'])
# mkdir for each room
from smap.archiver.client import SmapClient
import time
import datetime
import json
import pandas as pd
#pd.options.display.mpl_style = 'default'
client = SmapClient('http://ciee.cal-sdb.org:8079')
# timestamps
end = int(time.time())
#start = end - 60*60*24*30 # last month
start = end - 60 * 60 * 24 * 7 # last week
zones = client.query('select distinct Metadata/HVACZone')
def getdataasjson(query, start, end):
tmp_data = client.data(query, start, end, cache=True, limit=500000)
if not len(tmp_data[0]):
return {}
tmp = pd.DataFrame(tmp_data[1][0])
if len(tmp.notnull()) == 0:
return {}
tmp = tmp[pd.np.abs(tmp[1] - tmp[1].mean()) <= 5 * tmp[1].std()]
tmp[0] = pd.to_datetime(tmp[0], unit='ms')
tmp.index = tmp[0]
tmp = tmp.drop_duplicates()
del tmp[0]
return json.loads(tmp.to_json())["1"]
