Minos Minos - 11 months ago 62
Python Question

How to groupBy time series data per day using custom times in pandas

I want to do a pandas groupby per day (e.g.

df.groupby(pd.Grouper(key='ts', freq='D')
where
ts
is the timestamp column) but I want the day to start and end using custom times (e.g. a day starts at 9.00 instead of 00.00).

Is that possible?

Thank you in advance

Answer Source

Demo:

Setup:

In [257]: df = pd.DataFrame({
     ...:   'ts':pd.date_range('2017-01-01 12:33:00',freq='200T',periods=20),
     ...:   'val':np.random.randint(100, size=(20))
     ...: })
     ...:

In [258]: df
Out[258]:
                    ts  val
0  2017-01-01 12:33:00   39
1  2017-01-01 15:53:00    1
2  2017-01-01 19:13:00   98
3  2017-01-01 22:33:00   82
4  2017-01-02 01:53:00   90
5  2017-01-02 05:13:00   35
6  2017-01-02 08:33:00   58
7  2017-01-02 11:53:00   38
8  2017-01-02 15:13:00    1
9  2017-01-02 18:33:00   52
10 2017-01-02 21:53:00   17
11 2017-01-03 01:13:00   45
12 2017-01-03 04:33:00   22
13 2017-01-03 07:53:00   53
14 2017-01-03 11:13:00   89
15 2017-01-03 14:33:00   74
16 2017-01-03 17:53:00    0
17 2017-01-03 21:13:00   35
18 2017-01-04 00:33:00   83
19 2017-01-04 03:53:00   94

Solution:

In [259]: df.resample('D', on='ts', loffset='9H').sum()
Out[259]:
                     val
ts
2017-01-01 09:00:00  220
2017-01-02 09:00:00  291
2017-01-03 09:00:00  318
2017-01-04 09:00:00  177
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download