Split DateTimeIndex data based on hour/minute/second

Question

I have time-series data that I would like to split based on hour, or minute, or second. This is generally user-defined. I would like to know how it can be done.

For example, consider the following:

test = pd.DataFrame({'TIME': pd.date_range(start='2016-09-30',
                                           freq='600s', periods=20)})
test['X'] = np.arange(20)

The output is:

    TIME                X
0   2016-09-30 00:00:00 0
1   2016-09-30 00:10:00 1
2   2016-09-30 00:20:00 2
3   2016-09-30 00:30:00 3
4   2016-09-30 00:40:00 4
5   2016-09-30 00:50:00 5
6   2016-09-30 01:00:00 6
7   2016-09-30 01:10:00 7
8   2016-09-30 01:20:00 8
9   2016-09-30 01:30:00 9
10  2016-09-30 01:40:00 10
11  2016-09-30 01:50:00 11
12  2016-09-30 02:00:00 12
13  2016-09-30 02:10:00 13
14  2016-09-30 02:20:00 14
15  2016-09-30 02:30:00 15
16  2016-09-30 02:40:00 16
17  2016-09-30 02:50:00 17
18  2016-09-30 03:00:00 18
19  2016-09-30 03:10:00 19

Suppose I want to split it by hour. I would like the following as one chunk which I can then save to a file.

    TIME                X
0   2016-09-30 00:00:00 0
1   2016-09-30 00:10:00 1
2   2016-09-30 00:20:00 2
3   2016-09-30 00:30:00 3
4   2016-09-30 00:40:00 4
5   2016-09-30 00:50:00 5

The second chunk would be:

    TIME                X
0   2016-09-30 01:00:00 6
1   2016-09-30 01:10:00 7
2   2016-09-30 01:20:00 8
3   2016-09-30 01:30:00 9
4   2016-09-30 01:40:00 10
5   2016-09-30 01:50:00 11

and so on...

Note I can do it purely based on logical conditions such as,

 df[(df['TIME'] >= '2016-09-30 00:00:00') &
    (df['TIME'] <= '2016-09-30 00:50:00')]

and repeat....

but what if my sampling changes? Is there a way to create a mask or something that takes less amount of code and is efficient? I have 10 GB of data.

Answer 1

Option 1
you can groupby series without having them in the object you're grouping.

test.groupby([test.TIME.dt.date,
              test.TIME.dt.hour,
              test.TIME.dt.minute,
              test.TIME.dt.second]):

Option 2
use pd.TimeGrouper

test.set_index('TIME').groupby(pd.TimeGrouper('S'))  # Group by seconds
test.set_index('TIME').groupby(pd.TimeGrouper('T'))  # Group by minutes
test.set_index('TIME').groupby(pd.TimeGrouper('H'))  # Group by hours

Answer 2

You need to use groupby for this, and the grouping should be based on date and hour:

test['DATE'] = test['TIME'].dt.date
test['HOUR'] = test['TIME'].dt.hour
grp = test.groupby(['DATE', 'HOUR'])

You can then loop over the groups and do the operation you want.

Example:

for key, df in grp:
    print(key, df)

((datetime.date(2016, 9, 30), 0),                  TIME  X        DATE  HOUR
0 2016-09-30 00:00:00  0  2016-09-30     0
1 2016-09-30 00:10:00  1  2016-09-30     0
2 2016-09-30 00:20:00  2  2016-09-30     0
3 2016-09-30 00:30:00  3  2016-09-30     0
4 2016-09-30 00:40:00  4  2016-09-30     0
5 2016-09-30 00:50:00  5  2016-09-30     0)


((datetime.date(2016, 9, 30), 1),                   TIME   X        DATE  HOUR
6  2016-09-30 01:00:00   6  2016-09-30     1
7  2016-09-30 01:10:00   7  2016-09-30     1
8  2016-09-30 01:20:00   8  2016-09-30     1
9  2016-09-30 01:30:00   9  2016-09-30     1
10 2016-09-30 01:40:00  10  2016-09-30     1
11 2016-09-30 01:50:00  11  2016-09-30     1)


((datetime.date(2016, 9, 30), 2),                   TIME   X        DATE  HOUR
12 2016-09-30 02:00:00  12  2016-09-30     2
13 2016-09-30 02:10:00  13  2016-09-30     2
14 2016-09-30 02:20:00  14  2016-09-30     2
15 2016-09-30 02:30:00  15  2016-09-30     2
16 2016-09-30 02:40:00  16  2016-09-30     2
17 2016-09-30 02:50:00  17  2016-09-30     2)


((datetime.date(2016, 9, 30), 3),                   TIME   X        DATE  HOUR
18 2016-09-30 03:00:00  18  2016-09-30     3
19 2016-09-30 03:10:00  19  2016-09-30     3)