CODEWITHSUNDEEP
pythonpandas
user12511644
user12511644

Reputation:

Count ids based by occurence and on sequencial order Pandas

Current dataset:

month  ID     Bool
1      333    0
2      444    0
3      111    0
4      222    0
5      999    0
6      111    1
7      111    1
8      111    1
9      222    1
10     555    1
11     666    1
12     777    1

Two things need to be defined in one column named level by:

  1. Counting total occuring ID's with Bool 1 and add in new column with same ID Bool 0
  2. For every Bool 1 count the ID's that are underneath based on month. When it is the last one show 0.

Required result is a new column named level:

month  ID     Bool   Level
1      333    0      0
2      444    0      0
3      111    0      4
4      222    0      1
5      999    0      0
6      111    1      3
7      111    1      2
8      111    1      1
9      222    1      0
10     555    1      0
11     111    1      0
12     777    1      0

Upvotes: 0

Views: 47

Answers (2)

Keith Galli
Keith Galli

Reputation: 171

Here's a possible solution

for index, row in df.iterrows():
  remainder = df.iloc[index:,:][(df['ID'] == row['ID']) & (df['Bool']==1)]
  df.loc[index, 'Level'] = remainder.shape[0]

This solution iterates over all of the rows and as it does that looks at the remaining df (df.iloc[index:,:]) and counts how many occurrences of the current row ID with a Bool value of 1 occur.

Hope this helps! :)

Upvotes: 1

BENY
BENY

Reputation: 323266

You can do cumcount with reversed order 

df['level']=df.iloc[::-1].groupby('ID').cumcount()
df
Out[66]: 
    month   ID  Bool  Level  level
0       1  333     0      0      0
1       2  444     0      0      0
2       3  111     0      4      4
3       4  222     0      1      1
4       5  999     0      0      0
5       6  111     1      3      3
6       7  111     1      2      2
7       8  111     1      1      1
8       9  222     1      0      0
9      10  555     1      0      0
10     11  111     1      0      0
11     12  777     1      0      0

Upvotes: 1

Related Questions