How to resample pandas timeseries df into new rows representing equal cumsum of some measurement?

user_4509798 0
Viewed 41

Is it possible to resample timeseries data by "bins" of cumulative sum of some column? I mean if my raw df is:

+------+------------+-------+----------------+
| time | value      | bool  | someothervalue |
+------+------------+-------+----------------+
| 00:01| 3          | True  | 5              |
| 00:03| 1          | True  | 3              |
| 00:04| 2          | False | 6              |
| 00:20| 2          | True  | 7              |
| 00:27| 4          | True  | 4              |
| 00:28| 1          | False | 6              |
| 00:29| 1          | True  | 7              |
| 00:30| 2          | True  | 3              |
+------+------------+-------+----------------+

I would like to resample it "by value" so that every resampled row would represent aggregated value of 4:

+-------+-------+-----+---------+--------------+-------------+
| start | end   | valuesum | truecount | somevaluesum | sampledrows |
+-------+-------+----------+-----------+--------------+-------------+
| 00:01 | 00:03 | 4        | 2         | 8            | 2           |
| 00:04 | 00:20 | 4        | 1         | 13           | 2           |
| 00:27 | 00:27 | 4        | 1         | 4            | 1           |
| 00:28 | 00:30 | 4        | 2         | 16           | 3           |
+-------+-------+----------+-----------+--------------+-------------+

My current solution is "traditional" df.itertuples() but it is very slow and my target dataset is 100's milions of rows and I have to resample it for many different intervals. I'm looking for efficient solution similar to df.resample.ohlc() but based on "value" intervals not time intervals.

Edit: my exapmle is oversimplyfied, my real data is float so counting modulo is harder, also I need first (open) and last (close) values in resampled data. I promise not to overly simplify my problems in future, this is my first SO question.

pandas time-series dataframe python
1 Answers

Let us try get the mod then cumsum create the key , and we can do the groupby + agg 

s=df.groupby(((df.value.cumsum()%4)==0).iloc[::-1].cumsum()).agg(st=('time','min'),
                                         end=('time','max'),
                                         truecount=('bool','sum'),
                                         somevaluesum=('someothervalue','sum'),
                                         sampledrows=('someothervalue','count')) 
          st    end  truecount  somevaluesum  sampledrows
value                                                    
1      00:28  00:30        2.0            16            3
2      00:27  00:27        1.0             4            1
3      00:04  00:20        1.0            13            2
4      00:01  00:03        2.0             8            2
user_79645270
Related