CODEWITHSUNDEEP
pythonpandasdataframegroup-byconditional-statements
Sasiwut Chaiyadecha
Sasiwut Chaiyadecha

Reputation: 183

Index range based on condition

I have the pandas dataframe like below:

account   have  
  A         0     
  A         1     
  A         2     
  A         1     
  A         1     
  A         2     
  A         0     
  A         1     
  A         1     
  B         0     
  B         0     
  B         0     
  B         0     
  B         0     
  B         1     
  B         1     
  B         1     
  B         1

I want to result like this:

  account   have  
  A         12    
  A         12    
  A         0     
  A         1     
  A         2     
  A         0     
  A         1     
  A         2     
  A         3     
  B         12    
  B         12    
  B         12    
  B         12    
  B         12    
  B         12    
  B         12    
  B         12    
  B         12

I want to rank order after the specific value found and changed by account otherwise, it will return 12. For example, the specific value is 2, if any value that greater than or equal to 2 will be consider. In account A, it found 2 at index 2, the index will be change to 0 and any value after that less than 2 will be ranked order starting at 1.

In account B, there is no value greater than or equal to 2, the results return 12.

In fact, I have like a million account, if there is any suggestion or point to start of, please help me.

Upvotes: 4

Views: 271

Answers (1)

user3483203
user3483203

Reputation: 51165

Use cumsum to find your groups, and cumcount to fill them in.

m = df["have"].ge(2).groupby(df["account"]).cumsum()
df.groupby([df["account"], m]).cumcount().where(m.ne(0), 12)

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

Upvotes: 6

Related Questions