Python Pandas: Groupby Cumulative Sum, but avoiding sum where flag is 0

Question

There's some questions about this topic already (like Pandas: Cumulative sum of one column based on value of another) however, none of them full fill my requirements. Let's say I have a dataframe like this one:

id  flag
a   1
a   1
a   0
a   0
a   1
b   0
b   0
b   1
b   1
b   1
b   1
c   0
c   1
c   1
c   0
c   1

I want to compute the cumulative sum of flag grouping by id, but avoiding sum where flag is 0 and also cumsum reset to 0 again. I tried summing it up using shift(), groupby(id)['flag']cumsum() within np.where but no luck. Desired Output should be:

id  flag    cum_flag
a   1   1
a   1   2
a   0   0
a   0   0
a   1   1
b   0   0
b   0   0
b   1   1
b   1   2
b   1   3
b   1   4
c   0   0
c   1   1
c   1   2
c   0   0
c   1   1

The DDL to generate the dataframe:

df = pd.DataFrame({'id': [a, a, a, a, a, b, b, b, b, b, b, c, c, c, c, c],
                 'flag': [1, 1, 0, 0, 1, 0, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1]})

Thanks for your help!

Quang Hoang · Accepted Answer · 2020-05-05 14:49:03Z

2

You can use cumsum() to identify the consecutive blocks of 1, and then groupby:

df['cum_flag'] = df.groupby(['id',(df['flag']==0).cumsum()]).cumsum()

Output:

   id  flag  cum_flag
0   a     1         1
1   a     1         2
2   a     0         0
3   a     0         0
4   a     1         1
5   b     0         0
6   b     0         0
7   b     1         1
8   b     1         2
9   b     1         3
10  b     1         4
11  c     0         0
12  c     1         1
13  c     1         2
14  c     0         0
15  c     1         1

answered May 5, 2020 at 14:49

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python Pandas: Groupby Cumulative Sum, but avoiding sum where flag is 0

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related