How to generate a column of sequential numbers when finding repeated numbers in another column in Pandas: Python

user_12432400 0
Viewed 233

I'm using the following dictionary and developing in pandas to manipulate it in a dataframe:

    data = {"Value": [4, 4, 2, 1, 1, 1, 0, 7, 0, 4, 1, 1, 3, 0, 3, 0, 7, 0, 4, 1, 0, 1, 0, 1, 4, 4, 2, 3],
    "IdPar": [0, 0, 0, 0, 0, 0, 10, 10, 10, 10, 10, 0, 0, 22, 22, 28, 28, 28, 28, 0, 0, 38, 38 , 0, 0, 0, 0, 0]
    }

    df = pd.DataFrame(data)

I would like to achieve that when it finds a repeated number in the IdPar column, a sequential number is generated in the same row but in a new column called Count, with the condition that if it finds 0 it repeats the value of 0 in the new column. Next I show what I expect to get:

      Value  IdPar  Count
    0     4       0     0
    1     4       0     0
    2     2       0     0
    3     1       0     0
    4     1       0     0
    5     1       0     0
    6     0      10     1
    7     7      10     2
    8     0      10     3
    9     4      10     4
    1     1      10     5
    1     1       0     0
    1     3       0     0
    1     0      22     1
    1     3      22     2
    1     0      28     1
    1     7      28     2
    1     0      28     3
    1     4      28     4
    1     1       0     0
    2     0       0     0
    2     1      38     1
    2     0      38     2
    2     1       0     0
    2     4       0     0
    2     4       0     0
    2     2       0     0
    2     3       0     0

What I've done is review pandas information, I've tried many functions and what I've found is the use of ne, shift, cumsum, groupby, pivot_table or transform functions, but it isn't the result I want:

    s = df.pivot_table(index = ['IdPar'], aggfunc = 'size')
    print(s)

     t = df['IdPar'].ne(df['IdPar'].shift()).cumsum()
    print(t)

    df ['Count'] = df['IdPar'].isin(df['Id_Par'])
    df ['Count'] = df.loc[df ['Count'] == True, 'IdPar']
    print(df)

How far I've come is to place in the column Count the sum of the repetitions in front of the row in which it is presented or that the repetition of the number in the IdPar column begins, which is the code below, but I don't want that either:

     df['Count'] = df.groupby(['IdPar'])['Value'].transform('count')
     print(df['Count'])

I really appreciate anyone who can help me. Any comment helps.

pandas dataframe python
1 Answers

Try cumcount:

df['Count'] = df.groupby('IdPar')['IdPar'].cumcount() + 1
df.loc[df['IdPar'] == 0, 'Count'] = 0
print(df)

Or try in one line:

df['Count'] = df.groupby('IdPar').cumcount().add(1).mask(df['IdPar'].eq(0), 0)

Both codes output:

    IdPar  Value  Count
0       0      4      0
1       0      4      0
2       0      2      0
3       0      1      0
4       0      1      0
5       0      1      0
6      10      0      1
7      10      7      2
8      10      0      3
9      10      4      4
10     10      1      5
11      0      1      0
12      0      3      0
13     22      0      1
14     22      3      2
15     28      0      1
16     28      7      2
17     28      0      3
18     28      4      4
19      0      1      0
20      0      0      0
21     38      1      1
22     38      0      2
23      0      1      0
24      0      4      0
25      0      4      0
26      0      2      0
27      0      3      0
user_87083640
Related