How can I efficiently get the average of the data within the specified window from a large dataset?

user_13122452 0
Viewed 85

I have a dataset that has about 30,000 samples. Here's how the dataset looks like:

  X         Y           Z       Magnitude   CoordinateID

-17.34  29.519999   -20.520000  27.942548       0                         
-17.40  29.279999   -20.220000  27.751907       0

x1000 similar rows

-16.14  29.880000   -22.920000  29.078961       10  
-19.619 29.099998   -22.140000  30.550350       22                      

x1000 similar Rows and so on

I want to get an average of first 100 samples (of each rows in columns X, Y, Z and Magnitude) then the next 100 samples and so on and store the results into a new dataframe. How to implement this?

EDIT: I also need CoordinateID to be a part of my new dataframe. Like lets suppose the first 1000 rows have coordinateID with 0, other 1000 rows have 1 as their coordinateID, so how can I maintain that in my new dataframe?

pandas numpy python
3 Answers

Is this what you're after? It takes a rolling average of a specified column, where 100 refers to the window size.

df['X_rolling_avg'] = df.X.rolling(100).mean() 
df['Y_rolling_avg'] = df.Y.rolling(100).mean() 
df['Magn_rolling_avg'] = df.Magnitude.rolling(100).mean()
user_69508840

If you need the mean for every 100 lines (0 - 99, 100 - 199) and so on, you can do it using groupby

grouper = df.groupby(['CoordinateID',df.reset_index().index // 100])
new_df = grouper.agg(X_mean = ('X', 'mean'), 
                     Y_mean = ('Y', 'mean'),
                     Z_mean = ('Z', 'mean')).reset_index(level=0)
user_58478540 
import numpy as np
import time
import random
import statistics as s

data = random.sample(range(1,60000),50000)

start_time = time.time()
nrml_avg = s.mean(data)
end_time = time.time()
print(f'Normal mean function takes {end_time - start_time} -> Ans : {nrml_avg}')

start_time = time.time()
numpy_avg = np.average(data)
end_time = time.time()
print(f'Normal mean function takes {end_time - start_time} -> Ans : {numpy_avg}')

Normal mean function takes 0.03384280204772949 -> Ans : 29984.97692
Nnumpy mean function takes 0.008012771606445312 -> Ans : 29984.97692

[50000 data analysis]
I hope you get the fastest algo to get avg.
user_118462490
Related