pandas increment under specific condition of other column

user_10351685 0
Viewed 26

Given dataframe

df=pd.DataFrame({'A':['a','b','b','b','b','d'],'B':[0,0,1,2,3,0]})

how to get result like in column B?
The idea is when column A row is equal with column A previous row, add 1, else back to 0

I've tried:

df['B']=0  
df['B']=np.where(df['A'].eq(df['A'].shift()),df['B'].shift()+1,0)

however, the result that i got is [0,0,1,1,1,0] instead
do i missed some steps or non logical operation here?
any hint will be very helpful
thank you in advance

pandas dataframe python
1 Answers

Let us try groupby and cumcount:

m = df['A'] != df['A'].shift()
df['B'] = df.groupby(m.cumsum()).cumcount()

   A  B
0  a  0
1  b  0
2  b  1
3  b  2
4  b  3
5  d  0
user_128331660
Related