Pandas how to split dataframe by column by interval
<p>I have a gigantic dataframe with a datetime type column called <code>dt</code>, the data frame is sorted based on <code>dt</code> already. I want to split the dataframe into several dataframes based on <code>dt</code>, each dataframe contains rows within <code>1 hr</code> range.</p>

<p>Split</p>

<pre><code>   dt                    text
0  20160811 11:05        a
1  20160811 11:35        b
2  20160811 12:03        c
3  20160811 12:36        d
4  20160811 12:52        e
5  20160811 14:32        f
</code></pre>

<p>into </p>

<pre><code>   dt                    text
0  20160811 11:05        a
1  20160811 11:35        b
2  20160811 12:03        c

   dt                    text
0  20160811 12:36        d
1  20160811 12:52        e

   dt                    text 
0  20160811 14:32        f
</code></pre>

<p>You need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> by difference of first value of column <code>dt</code> converted to <code>hour</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.astype.html" rel="nofollow"><code>astype</code></a>:</p>

<pre><code>S = pd.to_datetime(df.dt)
for i, g in df.groupby([(S - S[0]).astype('timedelta64[h]')]):
        print (g.reset_index(drop=True))

               dt text
0  20160811 11:05    a
1  20160811 11:35    b
2  20160811 12:03    c
               dt text
0  20160811 12:36    d
1  20160811 12:52    e
               dt text
0  20160811 14:32    f
</code></pre>

<p><code>List comprehension</code> solution:</p>

<pre><code>S = pd.to_datetime(df.dt)

print ((S - S[0]).astype('timedelta64[h]'))
0    0.0
1    0.0
2    0.0
3    1.0
4    1.0
5    3.0
Name: dt, dtype: float64

L = [g.reset_index(drop=True) for i, g in df.groupby([(S - S[0]).astype('timedelta64[h]')])]

print (L[0])
               dt text
0  20160811 11:05    a
1  20160811 11:35    b
2  20160811 12:03    c

print (L[1])
               dt text
0  20160811 12:36    d
1  20160811 12:52    e

print (L[2])
               dt text
0  20160811 14:32    f
</code></pre>

<hr>

<p>Old solution, which split by <code>hour</code>:</p>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.dt.hour.html" rel="nofollow"><code>dt.hour</code></a>, but first need convert <code>dt</code> <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_datetime.html" rel="nofollow"><code>to_datetime</code></a>:</p>

<pre><code>for i, g in df.groupby([pd.to_datetime(df.dt).dt.hour]):
    print (g.reset_index(drop=True))

               dt text
0  20160811 11:05    a
1  20160811 11:35    b
               dt text
0  20160811 12:03    c
1  20160811 12:36    d
2  20160811 12:52    e
               dt text
0  20160811 14:32    f
</code></pre>

<p><code>List comprehension</code> solution:</p>

<pre><code>L = [g.reset_index(drop=True) for i, g in df.groupby([pd.to_datetime(df.dt).dt.hour])]

print (L[0])
               dt text
0  20160811 11:05    a
1  20160811 11:35    b

print (L[1])
               dt text
0  20160811 12:03    c
1  20160811 12:36    d
2  20160811 12:52    e

print (L[2])
               dt text
0  20160811 14:32    f
</code></pre>

<hr>

<p>Or use <code>list comprehension</code> with converting column <code>dt</code> to <code>datetime</code>:</p>

<pre><code>df.dt = pd.to_datetime(df.dt)
L =[g.reset_index(drop=True) for i, g in df.groupby([df['dt'].dt.hour])]

print (L[1])
                   dt text
0 2016-08-11 12:03:00    c
1 2016-08-11 12:36:00    d
2 2016-08-11 12:52:00    e

print (L[2])
                   dt text
0 2016-08-11 14:32:00    f
</code></pre>

<hr>

<p>If need split by <code>date</code>s and <code>hour</code>s:</p>

<pre><code>#changed dataframe for testing
print (df)
               dt text
0  20160811 11:05    a
1  20160812 11:35    b
2  20160813 12:03    c
3  20160811 12:36    d
4  20160811 12:52    e
5  20160811 14:32    f

serie = pd.to_datetime(df.dt)
for i, g in df.groupby([serie.dt.date, serie.dt.hour]):
    print (g.reset_index(drop=True))
               dt text
0  20160811 11:05    a
               dt text
0  20160811 12:36    d
1  20160811 12:52    e
               dt text
0  20160811 14:32    f
               dt text
0  20160812 11:35    b
               dt text
0  20160813 12:03    c    
</code></pre>

python python-2.7 pandas numpy scipy