I have a Pandas dataframe with two columns which are speed
and time
.
speed date
54.72 1:33:56
49.37 1:33:59
37.03 1:34:03
24.02 7:39:58
28.02 7:40:01
24.04 7:40:04
24.02 7:40:07
25.35 7:40:10
26.69 7:40:13
32.04 7:40:16
28.02 11:05:43
30.71 11:05:46
29.36 11:05:49
18.68 11:05:52
54.72 11:05:55
34.69 10:31:34
25.03 10:31:38
56.04 10:31:40
44.03 10:31:43
I want to calculate the average of speeds per bins of 30 minutes. For example, the average speed during the 4th bin (1:31 - 2:00) is (54.72 + 49.37 + 37.03)/3. I have thought of converting hours, minutes and seconds to seconds from 00:00 and then have bins of 1800 seconds. I have tried to do use binned_statistic from scipy.stats but my main issue is that I cannot find a way to separate bins based on date and get the average of speeds.
Any ideas?