In Python Pandas using cumsum with groupby and reset of cumsum when value is 0

Question

I'm rather new at python. I try to have a cumulative sum for each client to see the consequential months of inactivity (flag: 1 or 0). The cumulative sum of the 1's need therefore to be reset when we have a 0. The reset need to happen as well when we have a new client. See below with example where a is the column of clients and b are the dates.

After some research, I found the question 'Cumsum reset at NaN' and 'In Python Pandas using cumsum with groupby'. I assume that I kind of need to put them together. Adapting the code of 'Cumsum reset at NaN' to the reset towards 0, is successful:

cumsum = v.cumsum().fillna(method='pad')
reset = -cumsum[v.isnull() !=0].diff().fillna(cumsum)
result = v.where(v.notnull(), reset).cumsum()

However, I don't succeed at adding a groupby. My count just goes on...

So, a dataset would be like this: import pandas as pd

df =  pd.DataFrame({'a' : [1,1,1,1,1,1,1,2,2,2,2,2,2,2], 
                    'b' : [1/15,2/15,3/15,4/15,5/15,6/15,1/15,2/15,3/15,4/15,5/15,6/15], 
                    'c' : [1,0,1,0,1,1,0,1,1,0,1,1,1,1]})

this should result in a dataframe with the columns a, b, c and d with

'd' : [1,0,1,0,1,2,0,1,2,0,1,2,3,4]

Please note that I have a very large dataset, so calculation time is really important.

Thank you for helping me

Have you looked at this? https://stackoverflow.com/questions/18196811/cumsum-reset-at-nan — Khris, Sep 28 '16 at 08:01

Nickil Maveli · Answer 1 · 2016-09-28T10:47:25.380

Use groupby.apply and cumsum after finding contiguous values in the groups. Then groupby.cumcount to get the integer counting upto each contiguous value and add 1 later.

Multiply with the original row to create the AND logic cancelling all zeros and only considering positive values.

df['d'] = df.groupby('a')['c']                                                            \
            .apply(lambda x: x * (x.groupby((x != x.shift()).cumsum()).cumcount() + 1))

print(df['d'])

0     1
1     0
2     1
3     0
4     1
5     2
6     0
7     1
8     2
9     0
10    1
11    2
12    3
13    4
Name: d, dtype: int64

Another way of doing would be to apply a function after series.expanding on the groupby object which basically computes values on the series starting from the first index upto that current index.

Use reduce later to apply function of two args cumulatively to the items of iterable so as to reduce it to a single value.

from functools import reduce

df.groupby('a')['c'].expanding()                                         \
  .apply(lambda i: reduce(lambda x, y: x+1 if y==1 else 0, i, 0))

a    
1  0     1.0
   1     0.0
   2     1.0
   3     0.0
   4     1.0
   5     2.0
   6     0.0
2  7     1.0
   8     2.0
   9     0.0
   10    1.0
   11    2.0
   12    3.0
   13    4.0
Name: c, dtype: float64

Timings:

%%timeit
df.groupby('a')['c'].apply(lambda x: x * (x.groupby((x != x.shift()).cumsum()).cumcount() + 1))
100 loops, best of 3: 3.35 ms per loop

%%timeit
df.groupby('a')['c'].expanding().apply(lambda s: reduce(lambda x, y: x+1 if y==1 else 0, s, 0))
1000 loops, best of 3: 1.63 ms per loop

score 2 · Answer 2 · answered Sep 28 '16 at 08:42

I think you need custom function with groupby:

#change row with index 6 to 1 for better testing
df =  pd.DataFrame({'a' : [1,1,1,1,1,1,1,2,2,2,2,2,2,2], 
                    'b' : [1/15,2/15,3/15,4/15,5/15,6/15,1/15,2/15,3/15,4/15,5/15,6/15,7/15,8/15], 
                    'c' : [1,0,1,0,1,1,1,1,1,0,1,1,1,1],
                    'd' : [1,0,1,0,1,2,3,1,2,0,1,2,3,4]})

print (df)
    a         b  c  d
0   1  0.066667  1  1
1   1  0.133333  0  0
2   1  0.200000  1  1
3   1  0.266667  0  0
4   1  0.333333  1  1
5   1  0.400000  1  2
6   1  0.066667  1  3
7   2  0.133333  1  1
8   2  0.200000  1  2
9   2  0.266667  0  0
10  2  0.333333  1  1
11  2  0.400000  1  2
12  2  0.466667  1  3
13  2  0.533333  1  4

def f(x):
    x.ix[x.c == 1, 'e'] = 1
    a = x.e.notnull()
    x.e = a.cumsum()-a.cumsum().where(~a).ffill().fillna(0).astype(int)
    return (x)

print (df.groupby('a').apply(f))
    a         b  c  d  e
0   1  0.066667  1  1  1
1   1  0.133333  0  0  0
2   1  0.200000  1  1  1
3   1  0.266667  0  0  0
4   1  0.333333  1  1  1
5   1  0.400000  1  2  2
6   1  0.066667  1  3  3
7   2  0.133333  1  1  1
8   2  0.200000  1  2  2
9   2  0.266667  0  0  0
10  2  0.333333  1  1  1
11  2  0.400000  1  2  2
12  2  0.466667  1  3  3
13  2  0.533333  1  4  4

In Python Pandas using cumsum with groupby and reset of cumsum when value is 0

2 Answers2

Linked