CODEWITHSUNDEEP
pythonpandasdataframe
muazfaiz
muazfaiz

Reputation: 5021

Average pandas dataframe on time index for a particular time interval

I have a dataframe where for each timestamp there are some points earned by the user. It looks like the following i.e. data was collected after few seconds 

>> df.head()
                     points
timestamp                                                    
2017-05-29 17:40:45    5
2017-05-29 17:41:53    7
2017-05-29 17:42:34    3
2017-05-29 17:42:36    8 
2017-05-29 17:42:37    6

Then I wanted to resample it to an interval of 5 minutes so I did this

>> df.resample("5min").mean()
                points
timestamp   
5/29/2017 17:40   8
5/29/2017 17:45   1
5/29/2017 17:50   4
5/29/2017 17:55   3
5/29/2017 18:00   8
5/30/2017 17:30   3
5/30/2017 17:35   3
5/30/2017 17:40   7
5/30/2017 17:45   8
5/30/2017 17:50   5
5/30/2017 17:55   7
5/30/2017 18:00   1

Now I want to give an input like this input_time = "17:00-18:00" and I want to divide the input time into 5min interval for e.g. [17:05, 17:10 ... 17:55, 18:00]. After that for each interval I want to get the average points earned for that particular time interval. The results should look like the following

interval points
17:00     -
17:05     -
….  
17:30     3
17:35     3
17:40     7.5
17:45     4.5
17:50     4.5
17:55     5
18:00     4.5

Need your help. Thanks

Upvotes: 2

Views: 1153

Answers (1)

jezrael
jezrael

Reputation: 862651

Create DatetimeIndex by date_range and change format by strftime:

input_time = "17:00-18:00"
s,e = input_time.split('-')
r = pd.date_range(s, e, freq='5T').strftime('%H:%M')
print (r)
['17:00' '17:05' '17:10' '17:15' '17:20' '17:25' '17:30' '17:35' '17:40'
 '17:45' '17:50' '17:55' '18:00']

Also convert original index for groupby with aggregate mean, last reindex by range:

df = df.groupby(df.index.strftime('%H:%M'))['points'].mean().reindex(r)
print (df)
17:00    NaN
17:05    NaN
17:10    NaN
17:15    NaN
17:20    NaN
17:25    NaN
17:30    3.0
17:35    3.0
17:40    7.5
17:45    4.5
17:50    4.5
17:55    5.0
18:00    4.5
Name: points, dtype: float64

Upvotes: 2

Related Questions