I have a pandas data frame with 1 column and a time-based index. I want to resample the data for every two seconds and compute the average of the values in the column. Here is an example:
index = pd.date_range('1/1/2000', periods=10, freq='S')
data = {'value':[23,23,12,14,14,57,67,32,56,89]}
series = pd.DataFrame(data, index=index)
The above code gives this result.
Now, I compute the average of the values for every two seconds.
series['resample_value'] =
series['value'].resample('2S').mean()
This gives me the result as shown in the image below.
But I would like to have the results as shown in image 3. I would like the computed average values to be put back in the original dataframe which was not resampled. How do I obtain it?
Thanks in advance.
You can groupby the resampled value using floor
, then calculate means for each group and broadcast this to the original rows using transform
:
series['value'] = series.groupby(series.index.floor('2S')).value.transform('mean')
print(series)
value
2000-01-01 00:00:00 23.0
2000-01-01 00:00:01 23.0
2000-01-01 00:00:02 13.0
2000-01-01 00:00:03 13.0
2000-01-01 00:00:04 35.5
2000-01-01 00:00:05 35.5
2000-01-01 00:00:06 49.5
2000-01-01 00:00:07 49.5
2000-01-01 00:00:08 72.5
2000-01-01 00:00:09 72.5