Pandas: keeping only first row of data in each 60 second bin
<p>What's the best way to keep only the first row of each 60 second bin of data in pandas? i.e. For every row that occurs at increasing time <code>t</code>, I want to delete all rows that occur up to <code>t+60</code> seconds.</p>

<p>I know there's some combination of <code>groupby().first()</code> that I can probably use, but the code examples I've seen (e.g. using <code>pandas.Grouper(freq='60s')</code>) will discard the original datetimes in favor of every 60 seconds offset from midnight rather than my original datetimes.</p>

<p>For example, the following:</p>

<pre><code>                            time        value
0  2016-05-11 13:00:10.841015028     0.215978
1  2016-05-11 13:02:05.760595780     0.155666
2  2016-05-11 13:02:05.760903860     0.155666
3  2016-05-11 13:02:18.325613076     0.157788
4  2016-05-11 13:02:18.486519052     0.157788
5  2016-05-11 13:02:20.243748548     0.157788
6  2016-05-11 13:02:20.533101692     0.157788
7  2016-05-11 13:02:20.646061652     0.157788
8  2016-05-11 13:02:21.121409820     0.157788
9  2016-05-11 13:04:24.660609068     0.211649
10 2016-05-11 13:04:24.660845612     0.211649
11 2016-05-11 13:04:24.660957596     0.211649
12 2016-05-11 13:04:24.661378132     0.211649
13 2016-05-11 13:04:24.661450628     0.211649
14 2016-05-11 13:04:24.661607044     0.211649
</code></pre>

<p>should become this:</p>

<pre><code>                            time        value
0  2016-05-11 13:00:10.841015028     0.215978
1  2016-05-11 13:02:05.760595780     0.155666
3  2016-05-11 13:04:24.660609068     0.211649
</code></pre>

<p>see <a class='doc-link' href="http://stackoverflow.com/documentation/pandas/1751/indexing-and-selecting-data/19351/path-dependent-slicing#t=201609082151350926644">Path Dependent Slicing</a></p>

<h3>Solution</h3>

<pre><code>def td60(ta):
    d = np.timedelta64(int(6e10))
    tp = ta + d
    j = 0
    yield j
    for i, tx in enumerate(ta):
        if tx &gt; tp[j]:
            yield i
            j = i

def pir(df):
    slc = list(td60(df.time.values))
    return pd.DataFrame(df.values[slc], df.index[slc])
</code></pre>

<hr>

<p><strong><em>Example usage</em></strong></p>

<pre><code>pir(df)
</code></pre>

<p><a href="http://i.stack.imgur.com/iKaJE.png" rel="nofollow"><img src="http://i.stack.imgur.com/iKaJE.png" alt="enter image description here"></a></p>

<hr>

<h3>Setup for timing 500,000 rows</h3>

<pre><code>pop_n, smp_n = 1000000, 500000
np.random.seed([3,1415])
tidx = pd.date_range('2016-09-08', periods=pop_n, freq='5s')
tidx = np.random.choice(tidx, smp_n, False)
tidx = pd.to_datetime(tidx).sort_values()

df = pd.DataFrame(dict(time=tidx, value=np.random.rand(smp_n)))
</code></pre>

<hr>

<h3>Timing</h3>

<p><a href="http://i.stack.imgur.com/oHpxc.png" rel="nofollow"><img src="http://i.stack.imgur.com/oHpxc.png" alt="enter image description here"></a></p>

<p><strong><em>Cythonize</em></strong><br>
In Jupyter</p>

<pre><code>%load_ext Cython
</code></pre>

<hr>

<pre><code>%%cython
import numpy as np
import pandas as pd

def td60(ta):
    d = np.timedelta64(int(6e10))
    tp = ta + d
    j = 0
    yield j
    for i, tx in enumerate(ta):
        if tx &gt; tp[j]:
            yield i
            j = i

def pir(df):
    slc = list(td60(df.time.values))
    return pd.DataFrame(df.values[slc], df.index[slc])
</code></pre>

<p><strong><em>After Cythonizing</em></strong><br>
Not much different</p>

<p><a href="http://i.stack.imgur.com/IQf1x.png" rel="nofollow"><img src="http://i.stack.imgur.com/IQf1x.png" alt="enter image description here"></a></p>

<hr>

<h3>reference setup for OP example</h3>

<pre><code>from StringIO import StringIO
import pandas as pd

text = """time,value
2016-05-11 13:00:10.841015028,0.215978
2016-05-11 13:02:05.760595780,0.155666
2016-05-11 13:02:05.760903860,0.155666
2016-05-11 13:02:18.325613076,0.157788
2016-05-11 13:02:18.486519052,0.157788
2016-05-11 13:02:20.243748548,0.157788
2016-05-11 13:02:20.533101692,0.157788
2016-05-11 13:02:20.646061652,0.157788
2016-05-11 13:02:21.121409820,0.157788
2016-05-11 13:04:24.660609068,0.211649
2016-05-11 13:04:24.660845612,0.211649
2016-05-11 13:04:24.660957596,0.211649
2016-05-11 13:04:24.661378132,0.211649
2016-05-11 13:04:24.661450628,0.211649
2016-05-11 13:04:24.661607044,0.211649"""

df = pd.read_csv(StringIO(text), parse_dates=[0])
</code></pre>

python pandas dataframe