def getFilteredAll(): try: start = sys.argv[1] except IndexError: start = '' try: end = sys.argv[2] except IndexError: end = '' try: msgid = sys.argv[3] except IndexError: msgid = 32 sensors = qs.GetSensorDF() for s in range(len(sensors)): targetTable = sensors.name[s] df = qs.GetRawAccelData(siteid = targetTable, fromTime = start, toTime = end, msgid = msgid) numElements = len(df.index) qs.PrintOut("Number of %s Raw elements: %s" % (targetTable, numElements)) if numElements > 0: df_filtered = fs.applyFilters(df, orthof=True, rangef=True, outlierf=True) numFiltered = len(df_filtered.index) if numFiltered > 0: qs.PrintOut("Number of %s filtered elements: %s" % (targetTable, numFiltered)) #print df_filtered else: qs.PrintOut("No valid filtered data for %s" % (targetTable))
def GetSingleLGDPM(site, node, startTS): query = "SELECT timestamp, id, xvalue, yvalue, zvalue" if len(site) == 5: query = query + ", msgid" query = query + " from %s WHERE id = %s and timestamp < '%s' " % (site, node, startTS) if len(site) == 5: query = query + "and (msgid = 32 or msgid = 11) " # query = query + "ORDER BY timestamp DESC LIMIT 2" # else: query = query + "ORDER BY timestamp DESC LIMIT 240" lgdpm = GetDBDataFrame(query) # if len(site) == 5: # if len(set(lgdpm.timestamp)) == 1: # lgdpm.loc[(lgdpm.msgid == 11) | (lgdpm.msgid == 32)] # else: # try: # lgdpm = lgdpm.loc[lgdpm.timestamp == lgdpm.timestamp[0]] # except: # print 'no data for node ' + str(node) + ' of ' + site if len(site) == 5: lgdpm.columns = ['ts','id','x','y','z', 'msgid'] else: lgdpm.columns = ['ts','id','x','y','z'] lgdpm = lgdpm[['ts', 'id', 'x', 'y', 'z']] lgdpm = filterSensorData.applyFilters(lgdpm) lgdpm = lgdpm.sort_index(ascending = False)[0:1] return lgdpm
def GenerateLastGoodData(): db = MySQLdb.connect(host = Hostdb, user = Userdb, passwd = Passdb) cur = db.cursor() #cur.execute("CREATE DATABASE IF NOT EXISTS %s" %nameDB) query = """ DROP TABLE IF EXISTS `senslopedb`.`lastgooddata`; CREATE TABLE `senslopedb`.`lastgooddata` ( `name` varchar(8) NOT NULL DEFAULT '', `id` int(11) NOT NULL DEFAULT '0', `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `xvalue` int(11) DEFAULT NULL, `yvalue` int(11) DEFAULT NULL, `zvalue` int(11) DEFAULT NULL, PRIMARY KEY (`name`,`id`) ); """ cur.execute(query) db.close() slist = GetSensorList() for s in slist: print s.name, s.nos df = GetRawAccelData(s.name,'',s.nos) df = filterSensorData.applyFilters(df,True,True,False) dflgd = GetLastGoodData(df,s.nos,True) del df PushLastGoodData(dflgd,s.name)
def GetSingleLGDPM(site, node, startTS): query = "SELECT timestamp,id, xvalue, yvalue, zvalue" if len(site) == 5: query = query + ", msgid" query = query + " from %s WHERE id = %s and timestamp < '%s' " % (site, node, startTS) if len(site) == 5: query = query + "and (msgid = 32 or msgid = 11) " # query = query + "ORDER BY timestamp DESC LIMIT 2" # else: query = query + "ORDER BY timestamp DESC LIMIT 240" lgdpm = GetDBDataFrame(query) lgdpm['name'] = site # if len(site) == 5: # if len(set(lgdpm.timestamp)) == 1: # lgdpm.loc[(lgdpm.msgid == 11) | (lgdpm.msgid == 32)] # else: # try: # lgdpm = lgdpm.loc[lgdpm.timestamp == lgdpm.timestamp[0]] # except: # print 'no data for node ' + str(node) + ' of ' + site if len(site) == 5: lgdpm.columns = ['ts','id','x','y','z', 'msgid','name'] else: lgdpm.columns = ['ts','id','x','y','z','name'] lgdpm = lgdpm[['ts', 'id', 'x', 'y', 'z','name']] lgdpm = filterSensorData.applyFilters(lgdpm) lgdpm = lgdpm.sort_index(ascending = False)[0:1] return lgdpm
def getDF(): site = sys.argv[1] fdate = sys.argv[2] tdate = sys.argv[3] mid = sys.argv[4] nodeid = sys.argv[5] df= GetRawAccelData(siteid = site, fromTime = fdate, toTime = tdate, maxnode = 40, msgid = int(mid), targetnode = int(nodeid) , batt=1, returndb=True) df_filt = filterSensorData.applyFilters(df, orthof=True, rangef=True, outlierf=True) df_filt = df_filt.set_index(['ts']) dfajson = df_filt.reset_index().to_json(orient='records',date_format='iso') dfajson = dfajson.replace("T"," ").replace("Z","").replace(".000","") print dfajson
def getSensor(start,end,colname,smooth=True): df = q.GetRawAccelData(siteid=colname, fromTime=start, toTime=end) seg_len = getSegLen(colname) df = f.applyFilters(df) df['xz'],df['xy']=accel_to_lin_xz_xy(seg_len,df.x.values,df.y.values,df.z.values) df=df.drop(['x','y','z'],axis=1) df = df.drop_duplicates(['ts', 'id']) df['gts'] = df.ts.apply(datenum) df=df.set_index('ts') df=df[['id','xz','xy','gts']] return df
def getSensor(start, end, colname, smooth=True): df = q.GetRawAccelData(siteid=colname, fromTime=start, toTime=end) seg_len = getSegLen(colname) df = f.applyFilters(df) df['xz'], df['xy'] = accel_to_lin_xz_xy(seg_len, df.x.values, df.y.values, df.z.values) df = df.drop(['x', 'y', 'z'], axis=1) df = df.drop_duplicates(['ts', 'id']) df['gts'] = df.ts.apply(datenum) df = df.set_index('ts') df = df[['id', 'xz', 'xy', 'gts']] return df
def GenerateLastGoodData(): db = mysqlDriver.connect(host = Hostdb, user = Userdb, passwd = Passdb) cur = db.cursor() #cur.execute("CREATE DATABASE IF NOT EXISTS %s" %nameDB) #Separated the consecutive drop table and create table in one query in # order to fix "commands out of sync" error query = "DROP TABLE IF EXISTS `senslopedb`.`lastgooddata`;" cur.execute(query) query = """ CREATE TABLE `senslopedb`.`lastgooddata` ( `name` varchar(8) NOT NULL DEFAULT '', `id` int(11) NOT NULL DEFAULT '0', `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `xvalue` int(11) DEFAULT NULL, `yvalue` int(11) DEFAULT NULL, `zvalue` int(11) DEFAULT NULL, PRIMARY KEY (`name`,`id`) ); """ cur.execute(query) db.close() slist = GetSensorList() for s in slist: print s.name, s.nos df = GetRawAccelData(siteid=s.name,maxnode=s.nos) df = filterSensorData.applyFilters(df,True,True,False) dflgd = GetLastGoodData(df,s.nos,True) del df try: PushLastGoodData(dflgd,s.name) except (AttributeError,TypeError): PrintOut("Error. Empty database")
def genproc(col, window, config, fixpoint, realtime=False): monitoring = q.GetRawAccelData(col.name, window.offsetstart, window.end) #identify the node ids with no data at start of monitoring window NodesNoInitVal = GetNodesWithNoInitialData(monitoring, col.nos, window.offsetstart) #get last good data prior to the monitoring window (LGDPM) lgdpm = pd.DataFrame() for node in NodesNoInitVal: temp = q.GetSingleLGDPM(col.name, node, window.offsetstart.strftime("%Y-%m-%d %H:%M")) lgdpm = lgdpm.append(temp, ignore_index=True) monitoring = monitoring.append(lgdpm) try: monitoring = flt.applyFilters(monitoring) LastGoodData = q.GetLastGoodData(monitoring, col.nos) q.PushLastGoodData(LastGoodData, col.name) LastGoodData = q.GetLastGoodDataFromDb(col.name) except: LastGoodData = q.GetLastGoodDataFromDb(col.name) print 'error' if len(LastGoodData) < col.nos: print col.name, " Missing nodes in LastGoodData" monitoring = monitoring.loc[monitoring.id <= col.nos] #assigns timestamps from LGD to be timestamp of offsetstart monitoring.loc[(monitoring.ts < window.offsetstart) | (pd.isnull(monitoring.ts)), ['ts']] = window.offsetstart monitoring['xz'], monitoring['xy'] = accel_to_lin_xz_xy( col.seglen, monitoring.x.values, monitoring.y.values, monitoring.z.values) monitoring = monitoring.drop(['x', 'y', 'z'], axis=1) monitoring = monitoring.drop_duplicates(['ts', 'id']) monitoring = monitoring.set_index('ts') monitoring = monitoring[['name', 'id', 'xz', 'xy']] nodes_noval = GetNodesWithNoData(monitoring, col.nos) nodes_nodata = pd.DataFrame({ 'name': [0] * len(nodes_noval), 'id': nodes_noval, 'xy': [np.nan] * len(nodes_noval), 'xz': [np.nan] * len(nodes_noval), 'ts': [window.offsetstart] * len(nodes_noval) }) nodes_nodata = nodes_nodata.set_index('ts') monitoring = monitoring.append(nodes_nodata) max_min_df, max_min_cml = err.cml_noise_profiling(monitoring, config, fixpoint, col.nos) #resamples xz and xy values per node using forward fill monitoring = monitoring.groupby('id').apply( resamplenode, window=window).reset_index(level=1).set_index('ts') nodal_proc_monitoring = monitoring.groupby('id') if not realtime: to_smooth = config.io.to_smooth to_fill = config.io.to_fill else: to_smooth = config.io.rt_to_smooth to_fill = config.io.rt_to_fill filled_smoothened = nodal_proc_monitoring.apply( fill_smooth, offsetstart=window.offsetstart, end=window.end, roll_window_numpts=window.numpts, to_smooth=to_smooth, to_fill=to_fill) filled_smoothened = filled_smoothened[['xz', 'xy', 'name']].reset_index() monitoring = filled_smoothened.set_index('ts') filled_smoothened[ 'td'] = filled_smoothened.ts.values - filled_smoothened.ts.values[0] filled_smoothened['td'] = filled_smoothened['td'].apply( lambda x: x / np.timedelta64(1, 'D')) nodal_filled_smoothened = filled_smoothened.groupby('id') disp_vel = nodal_filled_smoothened.apply(node_inst_vel, roll_window_numpts=window.numpts, start=window.start) disp_vel = disp_vel[['ts', 'xz', 'xy', 'vel_xz', 'vel_xy', 'name']].reset_index() disp_vel = disp_vel[['ts', 'id', 'xz', 'xy', 'vel_xz', 'vel_xy', 'name']] disp_vel = disp_vel.set_index('ts') disp_vel = disp_vel.sort_values('id', ascending=True) # return procdata(col,monitoring.sort(),disp_vel.sort_index(),max_min_df,max_min_cml) return procdata(col, monitoring.sort_index(), disp_vel.sort_index(), max_min_df, max_min_cml)
def getFilteredData(isCmd = True, inSite = "", inNode = 1, inStart = "", inEnd = "", inMsgid = 32): if isCmd == True: try: #site selection site = sys.argv[1] except IndexError: print "No site has been selected. Script unable to run!" return try: #node selection node = sys.argv[2] if node == 'nil': node = -1 except IndexError: node = -1 try: #start date start = sys.argv[3] if start == 'nil': start = '' except IndexError: start = '' try: #end date end = sys.argv[4] if end == 'nil': end = '' except IndexError: end = '' try: #switch between accel 1 and 2 msgid = sys.argv[5] if msgid == 'nil': msgid = 32 except IndexError: msgid = 32 else: site = inSite node = inNode start = inStart end = inEnd msgid = inMsgid #print "variables: %s %s %s %s %s" % (site,node,start,end,msgid) df = qs.GetRawAccelData(siteid = site, fromTime = start, toTime = end, msgid = msgid, targetnode = node) numElements = len(df.index) qs.PrintOut("Number of %s Raw elements: %s" % (site, numElements)) if numElements > 0: df_filtered = fs.applyFilters(df, orthof=True, rangef=True, outlierf=False) numFiltered = len(df_filtered.index) if numFiltered > 0: qs.PrintOut("Number of %s filtered elements: %s" % (site, numFiltered)) return df_filtered else: qs.PrintOut("No valid filtered data for %s" % (site)) return pd.DataFrame() #return empty dataframe return pd.DataFrame()
import numpy as np from datetime import timedelta as td from datetime import datetime as dt import sqlalchemy from sqlalchemy import create_engine import sys import requests site = sys.argv[1] fdate = sys.argv[2] tdate = sys.argv[3] nid = sys.argv[4].replace("-", ",") #site = "blcb" #fdate = "2014-05-25" #tdate = "2016-06-25" #nid = "1-2-3".replace("-",",") engine = create_engine( 'mysql+mysqldb://updews:[email protected]/senslopedb') query = "SELECT * FROM senslopedb.%s where timestamp between '%s ' and '%s' and id in (%s)" % ( site, fdate, tdate, nid) df = pd.io.sql.read_sql(query, engine) df.columns = ['ts', 'id', 'x', 'y', 'z', 's'] df['name'] = site df_filt = filterSensorData.applyFilters(df, orthof=True, rangef=True, outlierf=True) dfajson = df_filt.reset_index().to_json(orient='records', date_format='iso') dfajson = dfajson.replace("T", " ").replace("Z", "").replace(".000", "") print dfajson
def generate_proc(colname, num_nodes, seg_len, custom_end,roll_window_length,data_dt,rt_window_length,num_roll_window_ops,filt=False,for_plots=False): #1. setting date boundaries for real-time monitoring window # roll_window_numpts=int(1+roll_window_length/data_dt) roll_window_numpts=int(1+roll_window_length/data_dt) end, start, offsetstart,monwin=get_rt_window(rt_window_length,roll_window_numpts,num_roll_window_ops,custom_end) # print "end inside generate_proc ------------>>>> %s" %str(end) # generating proc monitoring data for each site print "Generating PROC monitoring data for:-->> %s - %s <<--" %(str(colname),str(num_nodes)) #3. getting accelerometer data for site 'colname' if filt: if for_plots: custom_start = offsetstart - timedelta(days=4) monitoring=qdb.GetRawAccelData(colname,custom_start) monitoring = ffd.filt(monitoring,keep_orig=True) earliest_ts = monitoring.ts.min() print "offsetstart ---------> %s " %str(offsetstart) print "earliest_ts ---------> %s " %str(earliest_ts) monitoring = monitoring[(monitoring.ts >= custom_start) & (monitoring.ts <= end)] return monitoring else: custom_start = offsetstart - timedelta(days=4) monitoring=qdb.GetRawAccelData(colname,custom_start) # monitoring=qdb.GetRawAccelData(colname,offsetstart) monitoring = ffd.filt(monitoring) monitoring = monitoring[(monitoring.ts >= offsetstart) & (monitoring.ts <= end)] else: monitoring=qdb.GetRawAccelData(colname,offsetstart) monitoring = monitoring[(monitoring.ts >= offsetstart) & (monitoring.ts <= end)] #3.1 identify the node ids with no data at start of monitoring window NodesNoInitVal=GetNodesWithNoInitialData(monitoring,num_nodes,offsetstart) # print NodesNoInitVal #4: get last good data prior to the monitoring window (LGDPM) lgdpm = pd.DataFrame() for node in NodesNoInitVal: temp = qdb.GetSingleLGDPM(colname, node, offsetstart.strftime("%Y-%m-%d %H:%M")) temp = fsd.applyFilters(temp) temp = temp.sort_index(ascending = False)[0:1] lgdpm = lgdpm.append(temp,ignore_index=True) #5 TODO: Resample the dataframe together with the LGDOM monitoring=monitoring.append(lgdpm) #6. evaluating which data needs to be filtered # try: monitoring=fsd.applyFilters(monitoring) LastGoodData=qdb.GetLastGoodData(monitoring,num_nodes) qdb.PushLastGoodData(LastGoodData,colname) LastGoodData = qdb.GetLastGoodDataFromDb(colname) print 'Done' if len(LastGoodData)<num_nodes: print colname, " Missing nodes in LastGoodData" #5. extracting last data outside monitoring window LastGoodData=LastGoodData[(LastGoodData.ts<offsetstart)] #6. appending LastGoodData to monitoring monitoring=monitoring.append(LastGoodData) #7. replacing date of data outside monitoring window with first date of monitoring window monitoring.loc[monitoring.ts < offsetstart, ['ts']] = offsetstart #8. computing corresponding horizontal linear displacements (xz,xy), and appending as columns to dataframe monitoring['xz'],monitoring['xy']=accel_to_lin_xz_xy(seg_len,monitoring.x.values,monitoring.y.values,monitoring.z.values) #9. removing unnecessary columns x,y,z monitoring=monitoring.drop(['x','y','z'],axis=1) monitoring = monitoring.drop_duplicates(['ts', 'id']) #10. setting ts as index monitoring=monitoring.set_index('ts') #11. reordering columns monitoring=monitoring[['id','xz','xy']] return monitoring,monwin
def genproc(col, window, config, fixpoint='', realtime=False, comp_vel=True): if fixpoint == '': fixpoint = config.io.column_fix monitoring = q.GetRawAccelData(col.name, window.offsetstart, window.end) monitoring = flt.applyFilters(monitoring) try: LastGoodData = q.GetLastGoodData(monitoring,col.nos) q.PushLastGoodData(LastGoodData,col.name) except: pass #identify the node ids with no data at start of monitoring window NodesNoInitVal=GetNodesWithNoInitialData(monitoring,col.nos,window.offsetstart) #get last good data prior to the monitoring window (LGDPM) if len(NodesNoInitVal) != 0: lgdpm = q.GetSingleLGDPM(col.name, NodesNoInitVal, window.offsetstart) if len(lgdpm) != 0: lgdpm = flt.applyFilters(lgdpm) lgdpm = lgdpm.sort_index(ascending = False).drop_duplicates('id') if len(lgdpm) != 0: monitoring=monitoring.append(lgdpm) monitoring = monitoring.loc[monitoring.id <= col.nos] #assigns timestamps from LGD to be timestamp of offsetstart monitoring.loc[(monitoring.ts < window.offsetstart)|(pd.isnull(monitoring.ts)), ['ts']] = window.offsetstart invalid_nodes = q.GetNodeStatus(1) invalid_nodes = invalid_nodes[invalid_nodes.site == col.name] if len(invalid_nodes) != 0: stat = invalid_nodes.groupby('node', as_index=False) monitoring = stat.apply(remove_invalid, df=monitoring) nodes_noval = GetNodesWithNoData(monitoring, col.nos) nodes_nodata = pd.DataFrame({'name': [0]*len(nodes_noval), 'id': nodes_noval, 'x': [0]*len(nodes_noval), 'y': [0]*len(nodes_noval), 'z': [0]*len(nodes_noval), 'ts': [window.offsetstart]*len(nodes_noval)}) monitoring = monitoring.append(nodes_nodata) max_min_df, max_min_cml = err.cml_noise_profiling(monitoring, config, fixpoint, col.nos) monitoring['xz'], monitoring['xy'] = accel_to_lin_xz_xy(col.seglen,monitoring.x.values,monitoring.y.values,monitoring.z.values) monitoring = monitoring.drop_duplicates(['ts', 'id']) monitoring = monitoring.set_index('ts') #resamples xz and xy values per node using forward fill monitoring = monitoring.groupby('id').apply(resamplenode, window = window).reset_index(level=1).set_index('ts') nodal_proc_monitoring = monitoring.groupby('id') if not realtime: to_smooth = config.io.to_smooth to_fill = config.io.to_fill else: to_smooth = config.io.rt_to_smooth to_fill = config.io.rt_to_fill filled_smoothened = nodal_proc_monitoring.apply(fill_smooth, offsetstart=window.offsetstart, end=window.end, roll_window_numpts=window.numpts, to_smooth=to_smooth, to_fill=to_fill) filled_smoothened = filled_smoothened[['xz', 'xy', 'x', 'y', 'z', 'name']].reset_index() filled_smoothened['depth'] = filled_smoothened['x']/np.abs(filled_smoothened['x']) * np.sqrt(col.seglen**2 - filled_smoothened['xz']**2 - filled_smoothened['xy']**2) filled_smoothened['depth'] = filled_smoothened['depth'].fillna(value=col.seglen) monitoring = filled_smoothened.set_index('ts') if comp_vel == True: filled_smoothened['td'] = filled_smoothened['ts'].values - filled_smoothened['ts'].values[0] filled_smoothened['td'] = filled_smoothened['td'].apply(lambda x: x / np.timedelta64(1,'D')) nodal_filled_smoothened = filled_smoothened.groupby('id') disp_vel = nodal_filled_smoothened.apply(node_inst_vel, roll_window_numpts=window.numpts, start=window.start) disp_vel = disp_vel.reset_index(drop=True) disp_vel = disp_vel.set_index('ts') disp_vel = disp_vel.sort_values('id', ascending=True) else: disp_vel = monitoring return procdata(col,disp_vel.sort_index(),max_min_df,max_min_cml)
import filterSensorData as fs sensors = qs.GetSensorDF() #print sensors for s in range(len(sensors)): targetTable = sensors.name[s] #df = qs.GetRawAccelData(siteid = targetTable, fromTime = "2013-01-01", msgid = 33) df = qs.GetRawAccelData(siteid = targetTable, fromTime = "2013-01-01") numElements = len(df.index) if numElements > 0: df_resampled = fs.applyFilters(df, orthof=True, rangef=True, outlierf=False) df_filtered = fs.applyFilters(df, orthof=False, rangef=False, outlierf=True) numFiltered= len(df_filtered.index) drawcountx = df_resampled.x.count() drawcounty = df_resampled.y.count() drawcountz = df_resampled.z.count() dfinalcountx = df_filtered.x.count() dfinalcounty = df_filtered.y.count() dfinalcountz = df_filtered.z.count() if numFiltered > 0: qs.PrintOut("Data Count Summary for %s" %(targetTable)) qs.PrintOut("Raw Data (resampled without pad)") qs.PrintOut("Xraw: %s" % (drawcountx)) qs.PrintOut("Yraw: %s" % (drawcounty))
def process(df_node): dff=fsd.applyFilters(df_node) #time fromTime=time-td(days=1, hours=4) toTime=time-td(hours=4) dfr = df_node[(df_node.id==nid)&(df_node.ts>=fromTime) & (df_node.ts<=toTime)] df = dff[(dff.id==nid)&(dff.ts>=fromTime) & (dff.ts<=toTime)] df = df.set_index('ts') #Integer index N=len(df.index) df['i']=range(1,N+1,1) x_corr = (df['x'].corr(df.i))**2 y_corr = (df['y'].corr(df.i))**2 z_corr = (df['z'].corr(df.i))**2 stx,sty,stz=df[['x','y','z']].std() dft=df_node[(df_node.ts>=toTime)&(df_node.ts<=time)&(df_node.id==nid)] c= 100*dfr.x.count()/48 cf= 100*df.x.count()/dfr.x.count() if not dft.empty: df1=dft[['x','y','z']].loc[max(dft.index)] x,y,z=df1 xdeg,ydeg,zdeg=np.degrees(np.arcsin(df1/1024.0)) else: print ("No data!\n") return 1 x,y,z=np.nan,np.nan,np.nan xdeg,ydeg,zdeg=np.nan,np.nan,np.nan dfft=dff[(dff.ts>=toTime)&(dff.ts<=time)&(dff.id==nid)] dfft=dfft[['x','y','z']].loc[max(dfft.index)] delx=dfft.x-df.x.mean() dely=dfft.y-df.y.mean() delz=dfft.z-df.z.mean() xdegdel=np.degrees(np.arcsin(dfft.x/1024))-np.degrees(np.arcsin(df.x.mean()/1024)) ydegdel=np.degrees(np.arcsin(dfft.y/1024))-np.degrees(np.arcsin(df.y.mean()/1024)) zdegdel=np.degrees(np.arcsin(dfft.z/1024))-np.degrees(np.arcsin(df.z.mean()/1024)) #slope, intercept, r_value, p_value, std_err = stats.linregress(df.x,df.i) print("########################################################################\n") print(col+str(nid)+' ('+str(time)+')') #tag query='''SELECT * FROM senslopedb.node_status where site='%s' and node=%d and inUse=1 order by date_of_identification desc'''%(col,nid) dfs=qdb.GetDBDataFrame(query) if not dfs.empty: print ('\nStatus:\t\t%s'%dfs.status[0]) print ('Comment:\t%s'%dfs.comment[0]) else: print ('\nStatus:\t\tOK') print('\t\tx\ty\tz') print('raw data=\t\t(%d,\t%d,\t%d)' %(x,y,z)) print("standard dev= \t%.2f,\t%.2f,\t%.2f" %(stx,sty,stz)) print("correlation= \t%.2f, \t%.2f, \t%.2f" %(x_corr,y_corr,z_corr)) print("bit delta= \t\t%.2f, \t%.2f, \t%.2f" %(delx,dely,delz)) print('%%data sent count= \t%.0f%%' %c) print('%%filter/raw count= \t%.0f%%' %cf) print('data theta(deg)= \t(%.2f,\t%.2f,\t%.2f)' %(xdeg,ydeg,zdeg)) print("delta theta(deg)= \t%.2f, \t%.2f, \t%.2f" %(xdegdel,ydegdel,zdegdel)) if len(col)==5: batt=dft.batt.loc[max(dft.index)] query='''SELECT site_name,node_id,version,vmax,vmin FROM senslopedb.node_accel_table where site_name='%s' and node_id=%d'''%(col,nid) dfb=qdb.GetDBDataFrame(query) print('\nBattery:') print('battery (min,max)= \t(%.2fV,\t%.2fV)'%(dfb.vmin[0],dfb.vmax[0])) print('battery voltage= \t%.2fV' %batt) if batt>dfb.vmax[0]: batt_del=batt-dfb.vmax[0] elif batt<dfb.vmin[0]: batt_del=batt-dfb.vmin[0] else: batt_del=0 print('Delta Battery= \t\t%.2fV'%batt_del) print("\n########################################################################\n") xyz.xyzplot(dff,col,nid,time)
import querySenslopeDb as qs import filterSensorData as fs sensors = qs.GetSensorDF() #print sensors for s in range(len(sensors)): targetTable = sensors.name[s] #df = qs.GetRawAccelData(siteid = targetTable, fromTime = "2013-01-01", msgid = 33) df = qs.GetRawAccelData(siteid=targetTable, fromTime="2013-01-01") numElements = len(df.index) if numElements > 0: df_resampled = fs.applyFilters(df, orthof=True, rangef=True, outlierf=False) df_filtered = fs.applyFilters(df, orthof=False, rangef=False, outlierf=True) numFiltered = len(df_filtered.index) drawcountx = df_resampled.x.count() drawcounty = df_resampled.y.count() drawcountz = df_resampled.z.count() dfinalcountx = df_filtered.x.count() dfinalcounty = df_filtered.y.count() dfinalcountz = df_filtered.z.count() if numFiltered > 0:
def genproc(col, window, config, fixpoint, realtime=False, comp_vel=True): monitoring = q.GetRawAccelData(col.name, window.offsetstart, window.end) monitoring = flt.applyFilters(monitoring) try: LastGoodData = q.GetLastGoodData(monitoring,col.nos) q.PushLastGoodData(LastGoodData,col.name) LastGoodData = q.GetLastGoodDataFromDb(col.name) except: LastGoodData = q.GetLastGoodDataFromDb(col.name) #identify the node ids with no data at start of monitoring window NodesNoInitVal=GetNodesWithNoInitialData(monitoring,col.nos,window.offsetstart) #get last good data prior to the monitoring window (LGDPM) if len(NodesNoInitVal) != 0: lgdpm = q.GetSingleLGDPM(col.name, NodesNoInitVal, window.offsetstart) if len(lgdpm) != 0: lgdpm = flt.applyFilters(lgdpm) lgdpm = lgdpm.sort_index(ascending = False).drop_duplicates('id') if len(lgdpm) != 0: monitoring=monitoring.append(lgdpm) monitoring = monitoring.loc[monitoring.id <= col.nos] #assigns timestamps from LGD to be timestamp of offsetstart monitoring.loc[(monitoring.ts < window.offsetstart)|(pd.isnull(monitoring.ts)), ['ts']] = window.offsetstart invalid_nodes = q.GetNodeStatus(1) invalid_nodes = invalid_nodes[invalid_nodes.site == col.name] if len(invalid_nodes) != 0: stat = invalid_nodes.groupby('node', as_index=False) monitoring = stat.apply(remove_invalid, df=monitoring) nodes_noval = GetNodesWithNoData(monitoring, col.nos) nodes_nodata = pd.DataFrame({'name': [0]*len(nodes_noval), 'id': nodes_noval, 'x': [0]*len(nodes_noval), 'y': [0]*len(nodes_noval), 'z': [0]*len(nodes_noval), 'ts': [window.offsetstart]*len(nodes_noval)}) monitoring = monitoring.append(nodes_nodata) max_min_df, max_min_cml = err.cml_noise_profiling(monitoring, config, fixpoint, col.nos) monitoring['xz'], monitoring['xy'] = accel_to_lin_xz_xy(col.seglen,monitoring.x.values,monitoring.y.values,monitoring.z.values) monitoring = monitoring.drop_duplicates(['ts', 'id']) monitoring = monitoring.set_index('ts') #resamples xz and xy values per node using forward fill monitoring = monitoring.groupby('id').apply(resamplenode, window = window).reset_index(level=1).set_index('ts') nodal_proc_monitoring = monitoring.groupby('id') if not realtime: to_smooth = config.io.to_smooth to_fill = config.io.to_fill else: to_smooth = config.io.rt_to_smooth to_fill = config.io.rt_to_fill filled_smoothened = nodal_proc_monitoring.apply(fill_smooth, offsetstart=window.offsetstart, end=window.end, roll_window_numpts=window.numpts, to_smooth=to_smooth, to_fill=to_fill) filled_smoothened = filled_smoothened[['xz', 'xy', 'x', 'y', 'z', 'name']].reset_index() filled_smoothened['depth'] = filled_smoothened['x']/np.abs(filled_smoothened['x']) * np.sqrt(col.seglen**2 - filled_smoothened['xz']**2 - filled_smoothened['xy']**2) filled_smoothened['depth'] = filled_smoothened['depth'].fillna(value=col.seglen) filled_smoothened['net_dist'] = np.sqrt((filled_smoothened['xz'] ** 2) + (filled_smoothened['xy'] ** 2)) monitoring = filled_smoothened.set_index('ts') if comp_vel == True: filled_smoothened['td'] = filled_smoothened['ts'].values - filled_smoothened['ts'].values[0] filled_smoothened['td'] = filled_smoothened['td'].apply(lambda x: x / np.timedelta64(1,'D')) nodal_filled_smoothened = filled_smoothened.groupby('id') disp_vel = nodal_filled_smoothened.apply(node_inst_vel, roll_window_numpts=window.numpts, start=window.start) disp_vel = disp_vel.reset_index(drop=True) disp_vel = disp_vel.set_index('ts') disp_vel = disp_vel.sort_values('id', ascending=True) else: disp_vel = monitoring return procdata(col,disp_vel.sort(),max_min_df,max_min_cml)
def generate_proc(colname, num_nodes, seg_len, custom_end,f=False,for_plots=False): #1. setting date boundaries for real-time monitoring window # roll_window_numpts=int(1+roll_window_length/data_dt) roll_window_numpts=int(1+roll_window_length/data_dt) end, start, offsetstart,monwin=get_rt_window(rt_window_length,roll_window_numpts,num_roll_window_ops,custom_end) # generating proc monitoring data for each site print "Generating PROC monitoring data for:-->> %s - %s <<--" %(str(colname),str(num_nodes)) #3. getting accelerometer data for site 'colname' monitoring=qdb.GetRawAccelData(colname,offsetstart) if f: if for_plots: monitoring = ffd.filt(monitoring,keep_orig=True) return monitoring else: monitoring = ffd.filt(monitoring) else: monitoring = monitoring.loc[(monitoring.ts >= offsetstart) & (monitoring.ts <= end)] #3.1 identify the node ids with no data at start of monitoring window NodesNoInitVal=GetNodesWithNoInitialData(monitoring,num_nodes,offsetstart) # print NodesNoInitVal #4: get last good data prior to the monitoring window (LGDPM) lgdpm = pd.DataFrame() for node in NodesNoInitVal: temp = qdb.GetSingleLGDPM(colname, node, offsetstart.strftime("%Y-%m-%d %H:%M")) temp = fsd.applyFilters(temp) temp = temp.sort_index(ascending = False)[0:1] lgdpm = lgdpm.append(temp,ignore_index=True) #5 TODO: Resample the dataframe together with the LGDOM monitoring=monitoring.append(lgdpm) #6. evaluating which data needs to be filtered # try: monitoring=fsd.applyFilters(monitoring) LastGoodData=qdb.GetLastGoodData(monitoring,num_nodes) qdb.PushLastGoodData(LastGoodData,colname) LastGoodData = qdb.GetLastGoodDataFromDb(colname) print 'Done' if len(LastGoodData)<num_nodes: print colname, " Missing nodes in LastGoodData" #5. extracting last data outside monitoring window LastGoodData=LastGoodData[(LastGoodData.ts<offsetstart)] #6. appending LastGoodData to monitoring monitoring=monitoring.append(LastGoodData) #7. replacing date of data outside monitoring window with first date of monitoring window monitoring.loc[monitoring.ts < offsetstart, ['ts']] = offsetstart #8. computing corresponding horizontal linear displacements (xz,xy), and appending as columns to dataframe monitoring['xz'],monitoring['xy']=accel_to_lin_xz_xy(seg_len,monitoring.x.values,monitoring.y.values,monitoring.z.values) #9. removing unnecessary columns x,y,z monitoring=monitoring.drop(['x','y','z'],axis=1) monitoring = monitoring.drop_duplicates(['ts', 'id']) #10. setting ts as index monitoring=monitoring.set_index('ts') #11. reordering columns monitoring=monitoring[['id','xz','xy']] return monitoring,monwin