def get_filtered_accel_data_json(site_column, start_date, end_date, node_id,
version):
return_data = pd.DataFrame()
accel_id = [1]
if version in [2, 3, 4, 5]:
accel_id.append(2)
for a_id in accel_id:
raw_data = query.get_raw_accel_data(tsm_name=site_column,
from_time=start_date,
to_time=end_date,
node_id=node_id,
accel_number=a_id,
batt=True)
filtered_data = fd.apply_filters(raw_data)
combined_data = pd.DataFrame({
"data": [raw_data, filtered_data],
"type": ["raw", "filtered"]
})
if len(accel_id) == 1:
return_data["v1"] = [combined_data]
else:
return_data[a_id] = [combined_data]
return return_data.to_json(orient="records", date_format="iso") \
.replace("T", " ").replace("Z", "") \
.replace(".000", "")
def filter_counter(tsm_id = '', days_interval = 3):
time_now = pd.to_datetime(dt.now())
from_time = time_now-td(days = days_interval)
memc = memcache.Client(['127.0.0.1:11211'], debug=1)
accelerometers=memc.get('DF_ACCELEROMETERS')
accel = accelerometers[accelerometers.tsm_id ==tsm_id]
df = qdb.get_raw_accel_data(tsm_id=tsm_id, from_time=from_time, batt =1)
if not df.empty:
count_raw = df['ts'].groupby(df.accel_id).size().rename('raw')
dfv = fsd.volt_filter(df)
count_volt = dfv['ts'].groupby(dfv.accel_id).size().rename('voltf')
dfr = fsd.range_filter_accel(df)
count_range = dfr['ts'].groupby(dfr.accel_id).size().rename('rangef')
dfo = fsd.orthogonal_filter(dfr)
count_ortho = dfo['ts'].groupby(dfo.accel_id).size().rename('orthof')
dfor = outlierf(dfo)
count_outlier = dfor['ts'].groupby(dfor.accel_id).size().rename('outlierf')
dfa=pd.concat([count_raw,count_volt, count_range, count_ortho, count_outlier],axis=1)
# dfa[np.isnan(dfa)]=0
dfa = dfa.reset_index()
else:
dfa = pd.DataFrame(columns = ['accel_id','raw','voltf','rangef','orthof','outlierf'])
dfa = dfa.astype(float)
dfa = pd.merge(accel[['accel_id']], dfa, how = 'outer', on = 'accel_id')
dfa[(np.isnan(dfa))]=0
dfa['ts'] = time_now
dfa['percent_raw'] = dfa.raw / (48 * days_interval) * 100
dfa['percent_voltf'] = dfa.voltf / dfa.raw * 100
dfa['percent_rangef'] = dfa.rangef / dfa.raw * 100
dfa['percent_orthof'] = dfa.orthof / dfa.raw * 100
dfa['percent_outlierf'] = dfa.outlierf / dfa.raw *100
dfa.ts = dfa.ts.dt.round('H')
dfa = dfa.round(2)
return dfa[['accel_id','ts','percent_raw','percent_voltf','percent_rangef','percent_orthof','percent_outlierf']]
def proc_data(tsm_props,
window,
sc,
realtime=False,
comp_vel=True,
analysis=True):
monitoring = qdb.get_raw_accel_data(tsm_name=tsm_props.tsm_name,
from_time=window.offsetstart,
to_time=window.end,
analysis=analysis)
monitoring = monitoring.loc[monitoring.node_id <= tsm_props.nos]
monitoring = filt.apply_filters(monitoring)
#identify the node ids with no data at start of monitoring window
no_init_val = no_initial_data(monitoring, tsm_props.nos,
window.offsetstart)
#get last good data prior to the monitoring window (LGDPM)
if len(no_init_val) != 0:
lgdpm = qdb.get_single_lgdpm(tsm_props.tsm_name,
no_init_val,
window.offsetstart,
analysis=analysis)
lgdpm = filt.apply_filters(lgdpm)
lgdpm = lgdpm.sort_index(ascending=False).drop_duplicates('node_id')
monitoring = monitoring.append(lgdpm, sort=False)
invalid_nodes = qdb.get_node_status(tsm_props.tsm_id,
ts=window.offsetstart)
monitoring = monitoring.loc[~monitoring.node_id.isin(invalid_nodes)]
lgd = get_last_good_data(monitoring)
#assigns timestamps from LGD to be timestamp of offsetstart
monitoring.loc[(monitoring.ts < window.offsetstart) |
(pd.isnull(monitoring.ts)), ['ts']] = window.offsetstart
monitoring = accel_to_lin_xz_xy(monitoring, tsm_props.seglen)
monitoring = monitoring.drop_duplicates(['ts', 'node_id'])
monitoring = monitoring.set_index('ts')
monitoring = monitoring[['tsm_name', 'node_id', 'xz', 'xy']]
nodes_noval = no_data(monitoring, tsm_props.nos)
nodes_nodata = pd.DataFrame({
'tsm_name': [tsm_props.tsm_name] * len(nodes_noval),
'node_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, sort=False)
max_min_df, max_min_cml = err.cml_noise_profiling(monitoring, sc,
tsm_props.nos)
#resamples xz and xy values per node using forward fill
monitoring = monitoring.groupby('node_id', as_index=False).apply(
resample_node, window=window).reset_index(drop=True).set_index('ts')
if not realtime:
to_smooth = int(sc['subsurface']['to_smooth'])
to_fill = int(sc['subsurface']['to_fill'])
else:
to_smooth = int(sc['subsurface']['rt_to_smooth'])
to_fill = int(sc['subsurface']['rt_to_fill'])
tilt = monitoring.groupby('node_id', as_index=False).apply(
fill_smooth,
offsetstart=window.offsetstart,
end=window.end,
roll_window_numpts=window.numpts,
to_smooth=to_smooth,
to_fill=to_fill).reset_index(level='ts').set_index('ts')
if comp_vel == True:
tilt.loc[:, 'td'] = tilt.index.values - \
monitoring.index.values[0]
tilt.loc[:, 'td'] = tilt['td'].apply(lambda x: x / \
np.timedelta64(1,'D'))
nodal_filled_smoothened = tilt.groupby('node_id', as_index=False)
tilt = nodal_filled_smoothened.apply(node_inst_vel,
roll_window_numpts=window.numpts,
start=window.start)
tilt = tilt.drop(['td'], axis=1)
tilt = tilt.sort_values('node_id', ascending=True)
tilt = tilt.reset_index(level='ts').set_index('ts')
return ProcData(invalid_nodes, tilt.sort_index(), lgd, max_min_df,
max_min_cml)
def proc_subsurface(tsm_props, window, sc):
monitoring = qdb.get_raw_accel_data(tsm_name=tsm_props.tsm_name,
from_time=window.offsetstart,
to_time=window.end)
monitoring = monitoring.loc[monitoring.node_id <= tsm_props.nos]
monitoring = filt.apply_filters(monitoring)
monitoring = monitoring.groupby('node_id', as_index=False).apply(
magnitude, tsm_props.seglen)
monitoring = theta_yz(monitoring)
#identify the node ids with no data at start of monitoring window
no_init_val = no_initial_data(monitoring, tsm_props.nos,
window.offsetstart)
#get last good data prior to the monitoring window (LGDPM)
if len(no_init_val) != 0:
lgdpm = qdb.get_single_lgdpm(tsm_props.tsm_name, no_init_val,
window.offsetstart)
lgdpm = filt.apply_filters(lgdpm)
lgdpm = lgdpm.sort_index(ascending=False).drop_duplicates('node_id')
monitoring = monitoring.append(lgdpm, sort=False)
invalid_nodes = qdb.get_node_status(tsm_props.tsm_id,
ts=window.offsetstart)
monitoring = monitoring.loc[~monitoring.node_id.isin(invalid_nodes)]
lgd = get_last_good_data(monitoring)
#assigns timestamps from LGD to be timestamp of offsetstart
monitoring.loc[(monitoring.ts < window.offsetstart) |
(pd.isnull(monitoring.ts)), ['ts']] = window.offsetstart
monitoring = monitoring.drop_duplicates(['ts', 'node_id'])
monitoring = monitoring.set_index('ts')
#monitoring = monitoring.loc[monitoring['node_id'].isin(range(1,8,1))] select nodes
monitoring = monitoring[[
'tsm_name', 'node_id', 'x', 'y', 'z', 'magnitude', 'theta_yz'
]]
nodes_noval = no_data(monitoring, tsm_props.nos)
nodes_nodata = pd.DataFrame({
'tsm_name': [tsm_props.tsm_name] * len(nodes_noval),
'node_id':
nodes_noval,
'magnitude': [np.nan] * len(nodes_noval),
'theta_yz': [np.nan] * len(nodes_noval),
'ts': [window.offsetstart] * len(nodes_noval)
})
nodes_nodata = nodes_nodata.set_index('ts')
monitoring = monitoring.append(nodes_nodata, sort=False)
# print ('\n\n\n####### monitoring #######\n\n\n', monitoring)
max_min_df, max_min_cml = err.cml_noise_profiling(monitoring, sc,
tsm_props.nos)
monitoring = monitoring.groupby('node_id', as_index=False).apply(
proc.resample_node,
window=window).reset_index(drop=True).set_index('ts')
to_smooth = int(sc['subsurface']['to_smooth'])
to_fill = int(sc['subsurface']['to_fill'])
monitoring = monitoring.groupby('node_id', as_index=False).apply(
fill_smooth,
offsetstart=window.offsetstart,
end=window.end,
roll_window_numpts=window.numpts,
to_smooth=to_smooth,
to_fill=to_fill).reset_index(level='ts').set_index('ts')
monitoring.loc[:,
'td'] = monitoring.index.values - monitoring.index.values[0]
monitoring.loc[:, 'td'] = monitoring['td'].apply(lambda x: x / \
np.timedelta64(1,'D'))
monitoring = monitoring.groupby('node_id', as_index=False).apply(
slope_intercept, roll_window_numpts=window.numpts,
start=window.start).reset_index(level='ts').set_index('ts')
monitoring = displacement(monitoring)
monitoring = monitoring.groupby('node_id', as_index=False).apply(
node_inst_vel, roll_window_numpts=window.numpts,
start=window.start).reset_index(level='ts').set_index('ts')
# monitoring = monitoring.groupby('node_id', as_index=False).apply(accel,
# roll_window_numpts=window.numpts,
# start=window.start).reset_index(level='ts').set_index('ts')
tilt = alert_generator(monitoring, 0.05, 0.032)
return ProcData(invalid_nodes, tilt, lgd, max_min_df, max_min_cml)
def xyzplot(tsm_id, nid, time, OutputFP=''):
memc = memcache.Client(['127.0.0.1:11211'], debug=1)
tsm_sensors = memc.get('DF_TSM_SENSORS')
accelerometers = memc.get('DF_ACCELEROMETERS')
accel = accelerometers[(accelerometers.tsm_id == tsm_id)
& (accelerometers.in_use == 1)]
tsm_name = tsm_sensors.tsm_name[tsm_sensors.tsm_id == tsm_id].values[0]
nid_up = nid - 1
nid_down = nid + 1
time = pd.to_datetime(time)
from_time = time - td(days=6)
to_time = time
#dataframe
df_node = qdb.get_raw_accel_data(tsm_id=tsm_id,
from_time=time - td(weeks=1),
to_time=time,
analysis=True,
batt=True)
dff = fsd.apply_filters(df_node)
#raw and filter counter
raw_count = float(df_node.ts[(df_node.node_id == nid)
& (df_node.ts >= time - td(days=3))].count())
filter_count = dff.ts[(dff.node_id == nid)
& (dff.ts >= time - td(days=3))].count()
try:
percent = filter_count / raw_count * 100.0
except ZeroDivisionError:
percent = 0
#valid invalid
query_na = ("SELECT COUNT(IF(na_status=1,1, NULL))/count(ts)*100.0 as "
"'percent_valid' FROM senslopedb.node_alerts "
"where tsm_id = {} and node_id = {} and na_status is not NULL "
"group by tsm_id, node_id".format(tsm_id, nid))
df_na = qdb.get_db_dataframe(query_na)
if df_na.empty:
validity = np.nan
else:
validity = df_na.percent_valid.values[0]
fig = plt.figure()
fig.suptitle("{}{} ({})".format(tsm_name, str(nid),
time.strftime("%Y-%m-%d %H:%M")),
fontsize=11)
#accelerometer status
query = '''SELECT status,remarks FROM senslopedb.accelerometer_status as astat
inner join
(select * from accelerometers where tsm_id={} and node_id={}
and in_use=1) as a
on a.accel_id=astat.accel_id
order by stat_id desc limit 1'''.format(tsm_id, nid)
dfs = qdb.get_db_dataframe(query)
if not dfs.empty:
stat_id = dfs.status[0]
if stat_id == 1:
stat = 'Ok'
elif stat_id == 2:
stat = 'Use with Caution'
elif stat_id == 3:
stat = 'Special Case'
elif stat_id == 4:
stat = 'Not Ok'
com = dfs.remarks[0]
else:
stat = 'Ok'
com = ''
fig.text(0.125,
0.95,
'Status: {}\nComment: {}'.format(stat, com),
horizontalalignment='left',
verticalalignment='top',
fontsize=8,
color='blue')
# end of accelerometer status
#filter/raw
fig.text(0.900,
0.95,
'%%filter/raw = %.2f%%\n%%validity = %.2f%%' %
(percent, validity),
horizontalalignment='right',
verticalalignment='top',
fontsize=8,
color='blue')
df0 = dff[(dff.node_id == nid_up) & (dff.ts >= from_time) &
(dff.ts <= to_time)]
dfr0 = df_node[(df_node.node_id == nid_up) & (df_node.ts >= from_time) &
(df_node.ts <= to_time)]
if not df0.empty:
df0 = df0.set_index('ts')
dfr0 = dfr0.set_index('ts')
ax1 = plt.subplot(3, 4, 1)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
df0['x'].plot(color='green')
ax1.tick_params(axis='both', direction='in', labelsize=7)
plt.ylabel(tsm_name + str(nid_up), color='green', fontsize=14)
plt.title('x-axis', color='green', fontsize=8, verticalalignment='top')
ax2 = plt.subplot(3, 4, 2, sharex=ax1)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
df0['y'].plot(color='green')
ax2.tick_params(axis='both', direction='in', labelsize=7)
plt.title('y-axis', color='green', fontsize=8, verticalalignment='top')
ax3 = plt.subplot(3, 4, 3, sharex=ax1)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
df0['z'].plot(color='green')
ax3.tick_params(axis='both', direction='in', labelsize=7)
plt.title('z-axis', color='green', fontsize=8, verticalalignment='top')
try:
axb1 = plt.subplot(3, 4, 4, sharex=ax1)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
dfr0['batt'].plot(color='green')
axb1.tick_params(axis='both', direction='in', labelsize=7)
axb1.axhline(accel.voltage_max[accel.node_id == nid_up].values[0],
color='black',
linestyle='--',
linewidth=1)
axb1.axhline(accel.voltage_min[accel.node_id == nid_up].values[0],
color='black',
linestyle='--',
linewidth=1)
plt.title('batt',
color='green',
fontsize=8,
verticalalignment='top')
except:
print("v1")
plt.xlim([from_time, to_time])
# for t in time:
# ax1.axvline(time, color='gray', linestyle='--', linewidth=0.7)
# ax2.axvline(time, color='gray', linestyle='--', linewidth=0.7)
# ax3.axvline(time, color='gray', linestyle='--', linewidth=0.7)
df = dff[(dff.node_id == nid) & (dff.ts >= from_time) &
(dff.ts <= to_time)]
dfr = df_node[(df_node.node_id == nid) & (df_node.ts >= from_time) &
(df_node.ts <= to_time)]
if not df.empty:
df = df.set_index('ts')
dfr = dfr.set_index('ts')
ax4 = plt.subplot(3, 4, 5)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
df['x'].plot(color='blue')
ax4.tick_params(axis='both', direction='in', labelsize=7)
plt.ylabel(tsm_name + str(nid), color='blue', fontsize=14)
plt.title('x-axis', color='blue', fontsize=8, verticalalignment='top')
ax5 = plt.subplot(3, 4, 6, sharex=ax4)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
df['y'].plot(color='blue')
ax5.tick_params(axis='both', direction='in', labelsize=7)
plt.title('y-axis', color='blue', fontsize=8, verticalalignment='top')
ax6 = plt.subplot(3, 4, 7, sharex=ax4)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
df['z'].plot(color='blue')
ax6.tick_params(axis='both', direction='in', labelsize=7)
plt.title('z-axis', color='blue', fontsize=8, verticalalignment='top')
try:
axb2 = plt.subplot(3, 4, 8, sharex=ax4)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
dfr['batt'].plot(color='blue')
axb2.tick_params(axis='both', direction='in', labelsize=7)
axb2.axhline(accel.voltage_max[accel.node_id == nid].values[0],
color='black',
linestyle='--',
linewidth=1)
axb2.axhline(accel.voltage_min[accel.node_id == nid].values[0],
color='black',
linestyle='--',
linewidth=1)
plt.title('batt',
color='blue',
fontsize=8,
verticalalignment='top')
except:
print("v1")
plt.xlim([from_time, to_time])
# for t in time:
# ax4.axvline(time, color='gray', linestyle='--', linewidth=0.7)
# ax5.axvline(time, color='gray', linestyle='--', linewidth=0.7)
# ax6.axvline(time, color='gray', linestyle='--', linewidth=0.7)
df1 = dff[(dff.node_id == nid_down) & (dff.ts >= from_time) &
(dff.ts <= to_time)]
dfr1 = df_node[(df_node.node_id == nid_down) & (df_node.ts >= from_time) &
(df_node.ts <= to_time)]
if not df1.empty:
df1 = df1.set_index('ts')
dfr1 = dfr1.set_index('ts')
ax7 = plt.subplot(3, 4, 9)
df1['x'].plot(color='red')
ax7.tick_params(axis='both', direction='in', labelsize=7)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
plt.ylabel(tsm_name + str(nid_down), color='red', fontsize=14)
plt.title('x-axis', color='red', fontsize=8, verticalalignment='top')
ax8 = plt.subplot(3, 4, 10, sharex=ax7)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
df1['y'].plot(color='red')
ax8.tick_params(axis='both', direction='in', labelsize=7)
plt.title('y-axis', color='red', fontsize=8, verticalalignment='top')
ax9 = plt.subplot(3, 4, 11, sharex=ax7)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
df1['z'].plot(color='red')
ax9.tick_params(axis='both', direction='in', labelsize=7)
plt.title('z-axis', color='red', fontsize=8, verticalalignment='top')
try:
axb3 = plt.subplot(3, 4, 12, sharex=ax7)
plt.axvspan(time - td(days=3), time, facecolor='yellow', alpha=0.4)
dfr1['batt'].plot(color='red')
axb3.tick_params(axis='both', direction='in', labelsize=7)
axb3.axhline(
accel.voltage_max[accel.node_id == nid_down].values[0],
color='black',
linestyle='--',
linewidth=1)
axb3.axhline(
accel.voltage_min[accel.node_id == nid_down].values[0],
color='black',
linestyle='--',
linewidth=1)
plt.title('batt', color='red', fontsize=8, verticalalignment='top')
except:
print("v1")
plt.xlim([from_time, to_time])
# for t in time:
# ax7.axvline(time, color='gray', linestyle='--', linewidth=0.7)
# ax8.axvline(time, color='gray', linestyle='--', linewidth=0.7)
# ax9.axvline(time, color='gray', linestyle='--', linewidth=0.7)
# plt.show()
plt.savefig(OutputFP + tsm_name + str(nid) + '(' +
time.strftime("%Y-%m-%d %H%M") + ')',
dpi=400)
def drift_detection(acc_id="", f_time=pd.to_datetime(dt.now() - td(weeks=12))):
accelerometers = memory.get('DF_ACCELEROMETERS')
acc_det = accelerometers[accelerometers.accel_id == acc_id].iloc[0]
try:
df = q.get_raw_accel_data(tsm_id=acc_det.tsm_id,
node_id=acc_det.node_id,
accel_number=acc_det.accel_number,
from_time=f_time)
#lagpas yung node_id
except ValueError:
return 0
#walang table ng tilt_***** sa db
except AttributeError:
return 0
#walang laman yung df
if df.empty:
return 0
#Resample 30min
df = df.set_index('ts').resample('30min').first()
#Integer index
N = len(df.index)
df['i'] = range(1, N + 1, 1)
# Compute accelerometer raw value
df.x[df.x < -2048] = df.x[df.x < -2048] + 4096
df.y[df.y < -2048] = df.y[df.y < -2048] + 4096
df.z[df.z < -2048] = df.z[df.z < -2048] + 4096
# Compute accelerometer magnitude
df['mag'] = ((df.x / 1024) * (df.x / 1024) + (df.y / 1024) *
(df.y / 1024) + (df.z / 1024) * (df.z / 1024)).apply(np.sqrt)
#count number of data
dfw = pd.DataFrame()
dfw['count'] = df.mag.resample('1W').count()
# Filter data with very big/small magnitude
df[df.mag > 3.0] = np.nan
df[df.mag < 0.5] = np.nan
# Compute mean and standard deviation in time frame
df['ave'] = df.mag.rolling(window=12, center=True).mean()
df['stdev'] = df.mag.rolling(window=12, center=True).std()
# Filter data with outlier values in time frame
df[(df.mag > df.ave + 3 * df.stdev) & (df.stdev != 0)] = np.nan
df[(df.mag < df.ave - 3 * df.stdev) & (df.stdev != 0)] = np.nan
#interpolate missing data
df = df.interpolate()
# Resample every six hours
df = df.resample('6H').mean()
# Recompute standard deviation after resampling
df.stdev = df.mag.rolling(window=2, center=False).std()
df.stdev = df.stdev.shift(-1)
df.stdev = df.stdev.rolling(window=2, center=False).mean()
# Filter data with large standard deviation
df[df.stdev > 0.05] = np.nan
# Compute velocity and acceleration of magnitude
df['vel'] = df.mag - df.mag.shift(1)
df['acc'] = df.vel - df.vel.shift(1)
#Resample 1week
dfw['vel_week'] = df.vel.resample('1W').mean()
dfw['acc_week'] = df.acc.resample('1W').mean()
dfw['corr'] = df.resample('1W').mag.corr(df.i)
dfw['corr'] = dfw['corr']**2
# Get the data that exceeds the threshold value
dfw = dfw[(abs(dfw['acc_week']) > 0.000003) & (dfw['corr'] > 0.7) &
(dfw['count'] >= 84)]
#Compute the difference for each threshold data
if len(dfw) > 0:
dfw = dfw.reset_index()
dfw['diff_TS'] = dfw.ts - dfw.ts.shift(1)
dfw['sign'] = dfw.vel_week * dfw.vel_week.shift(1)
#Check if there are 4 weeks consecutive threshold data
week = 1
days = td(days=0)
while days < td(days=28) and week < len(dfw.index):
if ((dfw.loc[week]['diff_TS'] <= td(days=14)) &
(dfw.loc[week]['sign'] > 0)):
days = days + dfw.loc[week]['diff_TS']
else:
days = td(days=0)
week = week + 1
if days >= td(days=28):
print(acc_id, dfw.ts[week - 1])
# df['mag'].plot()
# plt.savefig(OutputFP+col+nids+a+"-mag")
# plt.close()
dft = pd.DataFrame(columns=['accel_id', 'ts_identified'])
dft.loc[0] = [acc_id, dfw.ts[week - 1]]
#save to db
db.df_write(smsclass.DataTable("drift_detection", dft))
print("very nice!")