CODEWITHSUNDEEP
pythonpandasdataframe
Andres Green
Andres Green

Reputation: 61

Count number of times value appears consecutively in a column

So I have this dataframe:

data = {'value':[1,1,1,0,1,0,1,0,0,0,0,0,1,1,1,0,0,1,0,1]}

df = pd.DataFrame(data)
row value
0 1
1 1
2 1
3 0
4 1
5 0
6 1
7 0
8 0
9 0
10 0
11 0

I would like to add another column called "Cumulative" which will count how many times a number appears consecutively and will stop counting when the value is no longer the same as the one before. Then it should start counting again. This would be the resulting dataframe:

row value Cumulative
0 1 0
1 1 1
2 1 2
3 0 0
4 1 0
5 0 0
6 1 0
7 0 0
8 0 1
9 0 2
10 0 3
11 0 4
12 1 0

I have tried a couple of built-in functions like where, mask, and cumsum, but I'm honestly clueless when it comes to iterating and creating for loops and I'm positive that this is probably where the answer lies. Is there a function that I am not aware of that could do this? Or is there no avoiding for loops?

Upvotes: 0

Views: 925

Answers (2)

gerda die gandalfziege
gerda die gandalfziege

Reputation: 772

This code adds the column "Cumulative" and calculates the number of consecutive

# add col named "Cumulative"
df['Cumulative'] = [0 for i in range(len(df))]

last = 0
# if value is 1, add 1 to the value in the col named "Cumulative"
for i in range(len(df)):
    if df['value'][i] == 1:
        df['Cumulative'][i] = last + 1
        last += 1

    else:
        df['Cumulative'][i] = df['Cumulative'][i]
        last = 0
print(df)

The output is this:

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

EDIT

This code now works for all different numbers.

import pandas as pd

data = {'value': [1, 1, 1, 0, 1, 0, 1, 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 1, 0, 1]}

df = pd.DataFrame(data)

# add col named "Cumulative"
df['Cumulative'] = [0 for i in range(len(df))]

last_count = 0
# if value is 1, add 1 to the value in the col named "Cumulative"
last_num = df['value'][0]

for i in range(len(df)):

    if df['value'][i] == last_num:

        df['Cumulative'][i] = last_count + 1
        last_count += 1

    else:
        df['Cumulative'][i] = 1
        last_num = df['value'][i]
        last_count = 1

print(df)

The result:

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

Upvotes: 0

rhug123
rhug123

Reputation: 8780

Try:

df.groupby(df['value'].diff().ne(0).cumsum()).cumcount()

Output:

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

Upvotes: 3

Related Questions