python .Query - on sampled data

Question

I have a captured data set from a linear position sensor, shown below that I would like to query. The pandas dataframe I have imported has columns including the linear position sensor data as well as additional columns of data. As the data is a signal acquired via a DAC it is not smooth (noise) and the values are not known (sample rate). I would like to be able to query the dataframe to report the time and position of the linear position sensor as started to move; (about 1.8 seconds in the below) and again report the time and linear position just before impact (about the 2.08sec mark).

Data from the first 5 rows:

     Time   Ch0_LC1   Ch1_LC2   Ch2_LC3  Ch3_Posi  Ch4_Accel   LC1_Cal  
1  0.0000 -0.001179  0.015223 -0.013722  4.755466   4.670560 -0.009261   
2  0.0001 -0.001822  0.016188 -0.012114  4.754823   4.676027 -0.002510   
3  0.0002 -0.001179  0.016188 -0.012757  4.754501   4.680208 -0.009261   
4  0.0003 -0.000858  0.016509 -0.015008  4.754823   4.683746 -0.012632   
5  0.0004 -0.001179  0.016188 -0.013400  4.755466   4.686962 -0.009261   

    LC2_Cal   LC3_Cal    LC_Tot     Posi_Cal  Accel_Cal  Posi_Vel  
1  0.014619 -0.002637  0.002721  1422.726220  -1.067844    0.0000  
2  0.004876 -0.018576 -0.016209  1422.845175  -0.958777    0.4825  
3  0.004876 -0.012202 -0.016587  1422.904745  -0.875366    0.0000  
4  0.001636  0.010111 -0.000886  1422.845175  -0.804783   -0.4825  
5  0.004876 -0.005829 -0.010213  1422.726220  -0.740624   -1.1255

Where the Chx columns are direct DAC data, the _Cal columns have been corrected for calibration and the final _Vel column is a double derivative of the position for instant velocity.

There are examples of the query function and the .loc function being used for this type of data reporting when values are exactly equal to the query, but I can't get the same method to work with a < argument, as it returns all subsequent samples. E.g. Select rows from a DataFrame based on values in a column in pandas

If anyone could assist it would be much appreciated.

Link to DSP file: https://www.dropbox.com/s/z9xs3te23xet1lz/R16-03_moving.dat?dl=0

Code snippet for importing the above file and creating the additional columns.

from IPython import get_ipython
get_ipython().magic('reset -sf') 

import numpy as np
import matplotlib.pyplot as plt
import scipy.fftpack
import pandas as pd
#from scipy import interpolate
from scipy.interpolate import splrep, splev
from scipy.signal import butter, filtfilt, freqz, sosfiltfilt, sosfilt, cheby2, sosfreqz

Start_line = 15
file = 'C:/Users/wesley.heckendorf/Downloads/R84-02_moving.dat' 
output1 = pd.read_csv(file, "r", delimiter=' ', skiprows=Start_line, usecols=[0,1,2,3,4,5], error_bad_lines=False)
#io = pd.read_csv('D:/UCRF_Drop_Commissioning/16.run/R16-03_moving.dat', "r", delimiter=' ', skiprows=21, index_col=False, usecols=[1,2,3,4,5]).to_records() # To read 1st,2nd columns
io2 = pd.read_csv(file, "r", delimiter=' ', skiprows=(Start_line+7), index_col=False, names=['Time', 'Ch0_LC1', 'Ch1_LC2', 'Ch2_LC3', 'Ch3_Posi', 'Ch4_Accel']) # To read 1st,2nd columns

io2[:] = io2[:].convert_objects(convert_numeric=True)

"""constants"""
T = (io2.loc[2,'Time'] - io2.loc[1,'Time'])
fs = 1/T
nyq = 0.5 * (1/T)

""" Extract Calibration Factors and create columns for calibrated and Zero'd data"""
LC1_cal = float(output1.iloc[2,1])
LC2_cal = float(output1.iloc[2,2])
LC3_cal = float(output1.iloc[2,3])
Posi_cal = float(output1.iloc[2,4])
Accel_cal = float(output1.iloc[2,5])
print (LC1_cal, LC2_cal, LC3_cal, Posi_cal, Accel_cal) 
io2["LC1_Cal"] = (io2["Ch0_LC1"]-float(output1.iloc[1,1])) * LC1_cal
io2["LC2_Cal"] = (io2["Ch1_LC2"]-float(output1.iloc[1,2])) * LC2_cal
io2["LC3_Cal"] = (io2["Ch2_LC3"]-float(output1.iloc[1,3])) * LC3_cal
io2["LC_Tot"] = (io2["LC1_Cal"]+io2["LC2_Cal"]+io2["LC3_Cal"])
io2["Posi_Cal"] = ((io2["Ch3_Posi"] - io2["Ch3_Posi"].max())-float(output1.iloc[1,4])) * Posi_cal * -1
io2["Accel_Cal"] = (io2["Ch4_Accel"]-float(output1.iloc[1,5])) * Accel_cal
io2["Posi_Vel"] = (np.nan_to_num(np.gradient(io2["Ch3_Posi"]/-10,T)))
print (io2[1:6])

pls provide a glimpse of data and the code, that failed you. This would help alot — Quickbeam2k1, Sep 19 '17 at 10:38
I've added the first 5 rows of the dataframe above for reference. Code that failed me was: io2.query("Posi_Fil"==42) and io2.Posi_Cal<1400, which is as close as I have been able to get to output. — chopboy, Sep 19 '17 at 10:53
Maybe you can try to get the first row where your speed is positive (or over a certain speed threshold) like `df[df['Posi_Vel']>0].loc[0]`. To be more precise and if you have some noise you can apply some smoothing (rolling mean or whatever) on your timeserie `df.Posi_Vel.rolling(5).mean()`, then check the positive speed as previously. If you could share a sample of your dataframe, and your current code it would help to build a complete solution to your problem. — David Leon, Sep 19 '17 at 13:27
Hi David, Thanks for your response. I expect I could find the same point at velocity = 0, and move the query point back. Can you explain how the .loc works in this case and what the [0] is referencing? — chopboy, Sep 19 '17 at 23:08

score 0 · Answer 1 · answered Sep 21 '17 at 00:35

I have figured out a solution thanks to David's comment above and the link I posted earlier. It might not be the most efficient method but does seem to work, code below for reference.

a_start_pos = io2.loc[io2["Posi_Fil"]<(pressure_smooth[1:15001].mean()-(pressure_smooth[1:15001].mean()-pressure_smooth[1:15001].min())*10),"Posi_Fil"].max().item() a_start_time = io2.loc[io2["Posi_Fil"]<(pressure_smooth[1:15001].mean()-(pressure_smooth[1:15001].mean()-pressure_smooth[1:15001].min())10),"Time"].min().item() a_finish_pos = io2.loc[1:,"Posi_Fil"].min()+20 a_finish_time = io2.loc[io2["Posi_Fil"]<(io2.loc[1:,"Posi_Fil"].min()+20), "Time"].min() a = 2(a_finish_pos-a_start_pos)/1000/(a_finish_time-a_start_time)**2 print ("Drop Carriage Acceleration =", a, "m/s2", a/-9.81*100, "%g")

Where:

a_start_pos - is the position at which the carriage begins moving

a_start_time - the time at which the carriage begins moving

a_finish_pos - is the position at which the carriage approaches the end of it's travel.

a_finish_time - The time at which the carriage aproaches the end of it's travel.

Cheers,

python .Query - on sampled data

1 Answers1