Search code examples
pandasdataframeconditional-statements

Add counter because of conditional statement in other column

I am trying to have a conditional counter in a pandas data table. It is supposed to add up when values are below 1, as soon as a value is above 1, the counter is supposed to start again at 0. I think it's simple, but unfortunately I haven't found the solution.

Week A B
01-2022 0.8 1
02-2022 0.6 2
03-2022 1.5 0
04-2022 0.6 1
05-2022 0.8 2
06-2022 0.8 3

Thanks for helping me out.

Solution

  • You can use a groupby.cumcount for each group starting on a value >0, then correct the first group in case it doesn't start with such a case:

    # is the value >01?
m = df['A'].gt(1)
# for groups starting on values >1
g = m.groupby(m.cumsum())
# cumcount per group + 1 (-1 if the group starts with a value >1)
df['B'] = g.cumcount().add(1)-g.transform('any')

    output:

          Week    A  B
0  01-2022  0.8  1
1  02-2022  0.6  2
2  03-2022  1.5  0
3  04-2022  0.6  1
4  05-2022  0.8  2
5  06-2022  0.8  3