create a column counting the number of consecutive negative days

Use groupby().cumsum() on the negation of the criteria to identify the blocks, then groupby the blocks again:

blocks = df['amount'].ge(0).groupby(df['companyId']).cumsum()
df['negCount'] = df.groupby([df['companyId'],blocks]).cumcount()

Output:

   companyId dateBalance  amount  negCount
0          1  2020-04-17     100         0
1          1  2020-04-18      40         0
2          1  2020-04-19      20         0
3          1  2020-04-20     -40         1
4          1  2020-04-21      30         0
5          2  2020-04-18       5         0
6          2  2020-04-19       1         0
7          2  2020-04-20      -6         1
8          2  2020-04-21     -60         2
9          2  2020-04-22     200         0

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top