Pandas - Grouping rows in time buckets
<p>I have a data frame with thousands of line looking like this:</p>

<pre><code>    time                type      value
0   09:30:01.405735     EVENT_0     2.1
0   09:30:01.405761     EVENT_0     2.1
0   09:30:01.419743     EVENT_0     1.1
1   09:30:02.419769     EVENT_0     32.1
2   09:30:02.419775     EVENT_0     2.15
3   09:30:02.419775     EVENT_0     24.1
4   09:30:06.419775     EVENT_0     3.1
5   09:30:06.419793     EVENT_0     1.1
6   09:30:06.419793     EVENT_0     2.4
....     
</code></pre>

<p>We define a "window" as a continuous list of events that are not separated by more than 1 second (that is, a gap of 1 second or more between two consecutive events create a new window)</p>

<p>Here we would have 3 windows:</p>

<pre><code>    time                type      value
0   09:30:01.405735     EVENT_0     2.1
0   09:30:01.405761     EVENT_0     2.1
0   09:30:01.419743     EVENT_0     1.1
</code></pre>

<p>====================================</p>

<pre><code>1   09:30:02.419769     EVENT_0     32.1
2   09:30:02.419775     EVENT_0     2.15
3   09:30:02.419775     EVENT_0     24.1
</code></pre>

<p>====================================</p>

<pre><code>4   09:30:06.419775     EVENT_0     3.1
5   09:30:06.419793     EVENT_0     1.1
6   09:30:06.419793     EVENT_0     2.4
....     
</code></pre>

<p>I have trying to find a way to compute the average of the "value" column for each window, but can't find a way to do it properly in pandas.</p>

<p>Assuming you time column is of datetime format and the data frame is sorted according to the time column:</p>

<pre><code># calculate the windows, gives a unique number per entry associating it to its respective window
windows = (data.time.diff().apply(lambda x: x.total_seconds()) &gt;= 1).astype(int).cumsum()
# group by these windows and compute the value mean
data.groupby(windows).value.mean()
</code></pre>

python pandas