Merge events temporally close to one another in numpy (pandas)
<p>I would like to simplify a list of start and stop times.  When the time between the stop of one and the start of another, I'd like to combine (rows).  The following is a simplification of my data and what I would like as output:</p>

<pre><code>import numpy as np
import pandas as pd
start_time = [  1,  7, 20, 22, 27, 35]
stop_time  = [  5,  9, 22, 26, 30, 40]
events = pd.DataFrame({'start_time': start_time, 'stop_time': stop_time})
allowable_gap = 2.0

desired_start_time = [  1, 20, 35]
desired_stop_time  = [  9, 30, 40]
desired_events = pd.DataFrame({'start_time':desired_start_time, 'stop_time':desired_stop_time})
</code></pre>

<p>I have no requirement that I must use Pandas.  However, I need to at least use numpy.  The number of events is in the order of 1e6.</p>

<p>Thanks for any implementations or guidance.  I know that part of my problem is that I don't "get" Pandas.</p>

<p>My usage likely isn't relevant to the solution.  As background, I'm collecting a large number of events and then plotting them using matplotlib.pyplot.  As the output is complex, the best format I have found is .svg.  IE usually renders fine but takes and exceptionally long time to do so and I hope to reduce the number of lines that it has to draw.  I would love to view time series in a better way but that is outside the scope of this question.</p>

<p>A bit more efficient way to do that:</p>

<pre><code>In [106]: (events.groupby((events.start_time - events.stop_time.shift() &gt; allowable_gap).cumsum())
   .....:        .agg({'start_time':'min', 'stop_time':'max'})[['start_time','stop_time']])
Out[106]:
   start_time  stop_time
0           1          9
1          20         30
2          35         40
</code></pre>

<p>Timing against 60K rows DF:</p>

<pre><code>In [129]: events = pd.concat([events] * 10**4, ignore_index=True)

In [130]: events.shape
Out[130]: (60000, 2)

In [131]: %paste
def f():
    desired_start_time = []
    desired_stop_time  = []

    start = None
    end = None
    for index, row in events.iterrows():
        if start == None and end == None:
            start = row['start_time']
            end = row['stop_time']
        else:
            if end + allowable_gap &gt;= row['start_time']:
                end = row['stop_time']
            else:
                desired_start_time.append(start)
                desired_stop_time.append(end)
                start = row['start_time']
                end = row['stop_time']
    desired_start_time.append(start)
    desired_stop_time.append(end)
## -- End pasted text --

In [132]: %timeit f()
1 loop, best of 3: 16.1 s per loop

In [133]: %%timeit
   .....: (events.groupby((events.start_time - events.stop_time.shift() &gt; allowable_gap).cumsum())
   .....:        .agg({'start_time':'min', 'stop_time':'max'})[['start_time','stop_time']])
   .....:
100 loops, best of 3: 16.9 ms per loop
</code></pre>

<p><strong>Conclusion:</strong> "looping" solution is approx. 1000 times slower</p>

<p>Another timing for 6M rows DF:</p>

<pre><code>In [153]: events = pd.concat([events] * 10**6, ignore_index=True)

In [154]: events.shape
Out[154]: (6000000, 2)

In [155]: %%timeit
   .....: (events.groupby((events.start_time - events.stop_time.shift() &gt; allowable_gap).cumsum())
   .....:        .agg({'start_time':'min', 'stop_time':'max'})[['start_time','stop_time']])
   .....:
1 loop, best of 3: 1.49 s per loop
</code></pre>

<p>given and desired DFs:</p>

<pre><code>In [98]: events
Out[98]:
   start_time  stop_time
0           1          5
1           7          9
2          20         22
3          22         26
4          27         30
5          35         40

In [99]: desired_events
Out[99]:
   start_time  stop_time
0           1          9
1          20         30
2          35         40
</code></pre>

<p>Explanation:</p>

<pre><code>In [107]: events.start_time - events.stop_time.shift()
Out[107]:
0     NaN
1     2.0
2    11.0
3     0.0
4     1.0
5     5.0
dtype: float64

In [108]: (events.start_time - events.stop_time.shift() &gt; allowable_gap)
Out[108]:
0    False
1    False
2     True
3    False
4    False
5     True
dtype: bool

In [109]: (events.start_time - events.stop_time.shift() &gt; allowable_gap).cumsum()
Out[109]:
0    0
1    0
2    1
3    1
4    1
5    2
dtype: int32
</code></pre>

python pandas numpy svg