Home > Mobile >  Python Pandas DataFrame: Add a counter based on condition for 2 cases
Python Pandas DataFrame: Add a counter based on condition for 2 cases

Time:11-10

I want to produce two counters ("counter1", "counter2") in the following dataframe examples (cases: 1 and 2) using python functions with these characteristics:

case 1:

  1. "counter1": it counts the number of the zeros in the column "case1-0". (cumulative sum of the zeros)
  2. "counter2": it counts the ones in the column "case1-0". However, "counter2" is reseted, starting counting from 1 if "counter1" has the values 3. As shown in the example, "counter2" keeps counting as long as "counter1" less than 3 --> the zeros in column "case1-0" are considered as ones if "counter1" < 3.
case1-0 counter1 counter2
1 0 1
1 0 2
1 0 3
0 1 4
0 2 5
0 3 1
1 0 2
1 0 3
0 1 4
0 2 5
1 0 6

case 2:

  1. "counter1": it counts the number of the ones in the column "case1-0". (cumulative sum of the ones)
  2. "counter2": it counts the number of the zeros in the column "case1-0" but only if the last previous value in "counter1" is greater than 3.
case1-0 counter1 counter2
1 1 0
1 2 0
1 3 0
1 4 0
0 0 1
0 0 2
0 0 3
1 1 0
1 2 0
0 0 0
0 0 0
1 1 0

In the reality I have a time serie. Therefore the approach should be applicable for greater data frames.

CodePudding user response:

For count consecutive 0 values is possible use this solution with create mask by compare by 0. Alterntive is more complicated and slowier, if larger data. It create consecutive groups by compare shifted values and count by GroupBy.cumcount similar like last column counter22 with groups created by cumulative sum if greater or equal 3 by Series.cumsum:

a = df['case1-0'].eq(0)
b = a.cumsum()
df['counter11'] = b.sub(b.mask(a).ffill().fillna(0)).astype(int)
#alternative
#s = df['case1-0'].ne(df['case1-0'].shift()).cumsum()
#df['counter11'] = df.groupby(s).cumcount().add(1).mask(df['case1-0'].ne(0),0)

df['counter22'] = df.groupby(df['counter11'].ge(3).cumsum()).cumcount().add(1)
print (df)
    case1-0  counter1  counter2  counter11  counter22
0         1         0         1          0          1
1         1         0         2          0          2
2         1         0         3          0          3
3         0         1         4          1          4
4         0         2         5          2          5
5         0         3         1          3          1
6         1         0         2          0          2
7         1         0         3          0          3
8         0         1         4          1          4
9         0         2         5          2          5
10        1         0         6          0          6
  • Related