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><strong>UPDATE:</strong> thanks to <a href="http://stackoverflow.com/questions/39400684/pandas-keeping-only-first-row-of-data-in-each-60-second-bin/39400864?noredirect=1#comment66127957_39400864">@piRSquared</a> - he noticed that my previous solution was incorrect. Here is another attempt:</p>

<p>data:</p>

<pre><code>In [8]: df = pd.DataFrame(dict(time=pd.date_range('2001-01-01', periods=20, freq='9S'), value=np.random.rand(20)))

In [9]: df
Out[9]:
                  time     value
0  2001-01-01 00:00:00  0.440696
1  2001-01-01 00:00:09  0.135540
2  2001-01-01 00:00:18  0.008243
3  2001-01-01 00:00:27  0.389259
4  2001-01-01 00:00:36  0.128253
5  2001-01-01 00:00:45  0.566704
6  2001-01-01 00:00:54  0.386797
7  2001-01-01 00:01:03  0.426411
8  2001-01-01 00:01:12  0.438114
9  2001-01-01 00:01:21  0.918711
10 2001-01-01 00:01:30  0.715565
11 2001-01-01 00:01:39  0.422044
12 2001-01-01 00:01:48  0.199396
13 2001-01-01 00:01:57  0.827872
14 2001-01-01 00:02:06  0.986887
15 2001-01-01 00:02:15  0.305749
16 2001-01-01 00:02:24  0.030092
17 2001-01-01 00:02:33  0.338214
18 2001-01-01 00:02:42  0.773635
19 2001-01-01 00:02:51  0.816478
</code></pre>

<p>Solution:</p>

<pre><code>In [10]: df.groupby((df.time - df.ix[0, 'time']).dt.total_seconds() // 60, as_index=False).first()
Out[10]:
                 time     value
0 2001-01-01 00:00:00  0.440696
1 2001-01-01 00:01:03  0.426411
2 2001-01-01 00:02:06  0.986887
</code></pre>

<p>Explanation:</p>

<pre><code>In [17]: (df.time - df.ix[0, 'time']).dt.total_seconds()
Out[17]:
0       0.0
1       9.0
2      18.0
3      27.0
4      36.0
5      45.0
6      54.0
7      63.0
8      72.0
9      81.0
10     90.0
11     99.0
12    108.0
13    117.0
14    126.0
15    135.0
16    144.0
17    153.0
18    162.0
19    171.0
Name: time, dtype: float64

In [18]: (df.time - df.ix[0, 'time']).dt.total_seconds() // 60
Out[18]:
0    -0.0
1     0.0
2     0.0
3     0.0
4     0.0
5     0.0
6     0.0
7     1.0
8     1.0
9     1.0
10    1.0
11    1.0
12    1.0
13    1.0
14    2.0
15    2.0
16    2.0
17    2.0
18    2.0
19    2.0
Name: time, dtype: float64
</code></pre>

<p><strong>OLD incorrect answer:</strong></p>

<pre><code>In [102]: df[df.time.diff().fillna(pd.Timedelta('60S')) &gt;= pd.Timedelta('60S')]
Out[102]:
                           time     value
0 2016-05-11 13:00:10.841015028  0.215978
1 2016-05-11 13:02:05.760595780  0.155666
9 2016-05-11 13:04:24.660609068  0.211649
</code></pre>

<p>Explanation:</p>

python pandas dataframe