How to do the classification and count of DataFrame columns?

user_15393431 0
Viewed 111

I want to count consecutive 0s, if there are 0s, count the consecutive numbers, and assign the numbers to the count column, and if they encounter 1, recount.

I also tried several methods, but none of them achieved my results.

An example of my Dataframe is as follows:

import numpy as np
import pandas as pd
np.random.seed(2021)
a = np.random.randint(0, 2, 20)
df = pd.DataFrame(a, columns=['No.'])
print(df)

     No.
0     0
1     1
2     1
3     0
4     1
5     0
6     0
7     0
8     1
9     0
10    1
11    1
12    1
13    1
14    0
15    0
16    0
17    0
18    0
19    0

The result I need:

    No. count
0   0   1
1   1   0
2   1   0
3   0   1
4   1   0
5   0   3
6   0   3
7   0   3
8   1   0
9   0   1
10  1   0
11  1   0
12  1   0
13  1   0
14  0   6
15  0   6
16  0   6
17  0   6
18  0   6
19  0   6

I tried the following methods, but none of them achieved my results. What should I do?

groups = df['No.'].ne(0).cumsum()

df['count'] = df['No.'].eq(0).groupby(groups).count()

df['count'] = df['No.'].eq(0).groupby(groups).agg(len)

df['count'] = df['No.'].groupby(groups).agg(len)

df['count'] = df['No.'].groupby(groups).count()
pandas dataframe group-by python
1 Answers

For your groups variable, calculate diff first, so you assign an id to each consecutive sequence that contains the same value. And to get the equal sized count Series that can be assigned to original data frame, use transform instead of agg:

df['count'] = 0
groups = df['No.'].diff().ne(0).cumsum()
df.loc[df['No.'] == 0, 'count'] = df['No.'].groupby(groups).transform('size')
df

    No.  count
0     0      1
1     1      0
2     1      0
3     0      1
4     1      0
5     0      3
6     0      3
7     0      3
8     1      0
9     0      1
10    1      0
11    1      0
12    1      0
13    1      0
14    0      6
15    0      6
16    0      6
17    0      6
18    0      6
19    0      6
user_49834500
Related