Pandas GroupBy Datetime and Mean using Rolling

Question

I want to calculate the mean of values rolling over Datetime index with a window of an P period of time centering on a Q period of time.

I have a dataset that have records in second:

Time                    Temperature     
2017-11-28 22:00:03     58.34
2017-11-28 22:00:05     57.54
2017-11-28 22:00:07     58.18
2017-11-28 22:00:09     58.18
2017-11-28 22:00:12     58.18

I would like to group meaning the values 2.5 minutes before and after 5 minutes frecuency and get something like this:

Time                    Temperature     
2017-11-28 22:05:00     mean(5 minutes before and after)
2017-11-28 22:10:00     mean(5 minutes before and after)
2017-11-28 22:15:00     mean(5 minutes before and after)
2017-11-28 22:20:00     mean(5 minutes before and after)
2017-11-28 22:25:00     mean(5 minutes before and after)

I'm trying with

hum_sta.set_index('Time').groupby(pd.Grouper(freq='5Min')).rolling("5min").mean()

But I'm not getting what I want, I just getting Multiindex Pandas Dataframe:

               Time  Time                Temperature    
2017-11-28 22:00:00  2017-11-28 22:00:03 NaN
                     2017-11-28 22:00:05 57.94
                     2017-11-28 22:00:07 57.86
                     2017-11-28 22:00:09 58.18
                     2017-11-28 22:00:12 58.18

Then you probably need to sort the df by `Time` before trying this — roganjosh, Aug 25 '18 at 13:41
Well clearly it isn't because you get `ValueError: index must be monotonic`. A side note, I can't help but read "!" as you shouting at me and I don't think you really intend that — roganjosh, Aug 25 '18 at 13:44
@roganjosh Sorry about that. I sorted and i'm getting a new error. — ambigus9, Aug 25 '18 at 14:00
It's fine, I just wanted to make sure you're aware how it might come across in text :) What is the new error? Please update the question — roganjosh, Aug 25 '18 at 14:22
I don't think pandas right now can support all the operations needed for this. What about creating a new column, `rounded_time` which is (as you've guessed) the time rounded to 5 minutes, and then just `groupby("rounded_time").mean()` ? — Qusai Alothman, Aug 27 '18 at 12:36
@QusaiAlothman Sure, It might work, but I need the meaning of 2.5 minutes before and after each "rounded_time". — ambigus9, Aug 27 '18 at 21:59

Pandas GroupBy Datetime and Mean using Rolling

0 Answers0