slice a Dataframe into smaller DatafFames

user_7796562 0
Viewed 179

Im using 12 hours sensor data at 25Hrz that I query from mongo db into a dataframe I'm trying to extract a list or a dict of 1 minute dataframes from the 12 hours.

I use a window of 1 minute and a stride/ step of 10 seconds. The goal is to build a dataset by creating al list or dict of 1 minute dataframes/samples from 12 hours of data, that will be converted to tensor and fed to a deep learning model.
The index of the dataframe is datetime and 4 columns of sensor values. here is how part of the data looks like:

                             A       B      C       D
2020-06-17 22:00:00.000  1.052  -0.147  0.836   0.623
2020-06-17 22:00:00.040  1.011  -0.147  0.820   0.574
2020-06-17 22:00:00.080  1.067  -0.131  0.868   0.607
2020-06-17 22:00:00.120  1.033  -0.163  0.820   0.607
2020-06-17 22:00:00.160  1.030  -0.147  0.820   0.607

below is a sample code that is similar to how I extract windows of 1 minutes data. For 12 hours it takes 5 minutes-which is a long time.. Any ideas on how to reduce the running time in this case?

 step= 10*25
 w=60*25
 df # 12 hours df data
 sensor_dfs=[] 

 df_range = range(0, df.shape[0]-step, step)
 for a in df_range:
    sample = df.iloc[a:a+w]
    sensor_dfs.append(sample)
pandas deep-learning dataframe python
1 Answers

I created random data and made the following experiments looking at runtime:

# create random normal samples

 w= 60*25 # 1 minute window
 step=w # no overlap
 num_samples=50000
 data= np.random.normal(size=(num_samples,3))
 date_rng=pd.date_range(start="2020-07-09 00:00:00.000", 
 freq="40ms",periods=num_samples)
 data=pd.DataFrame(data, columns=["x","y","z"], index=date_rng)
 data.head()

                             x          y           z
2020-07-09 00:00:00.000 -1.062264   -0.008656   0.399642
2020-07-09 00:00:00.040 0.182398    -1.014290   -1.108719
2020-07-09 00:00:00.080 -0.489814   -0.020697   0.651120
2020-07-09 00:00:00.120 -0.776405   -0.596601   0.611516
2020-07-09 00:00:00.160 0.663900    0.149909    -0.552779

numbers are of type float64

data.dtypes
x    float64
y    float64
z    float64
dtype: object

using for loops

  minute_samples=[]
  for i in range(0,len(data)-w,step):
      minute_samples.append(data.iloc[i:i+w])     
  
 result:6.45 ms ± 256 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

using list comprehension

minute_samples=[data.iloc[i:i+w] for i in range(0,len(data)-w,step)]
result: 6.13 ms ± 181 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

using Grouper with list comprehension

minute_samples=[df for i, df in data.groupby(pd.Grouper(freq="1T"))]
result:7.89 ms ± 382 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

using grouper with dict

minute_samples=dict(tuple(data.groupby(pd.Grouper(freq="1T"))))
result: 7.41 ms ± 38 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

resample is also an option here but since behind the scenes it uses grouper then I don't think it will be different in terms of runtime

It seems like list comprehension is slightly better than the rest

user_77965620
Related