I have the following Pandas DataFrame
Key Value
A 10
A 20
B 30
B 40
C 50
A 60
A 70
A 70
B 80
A 90
And I need to create an index that auto increment only when the key repeats after sequence of different keys. So, I need this output:
Key Value Index
A 10 1
A 20 1
B 30 1
B 40 1
C 50 1
A 60 2
A 70 2
A 70 2
B 80 2
A 90 3
Thanks!
I try with the method groupby and cumcount() + 1 but it does not work.
>Solution :
Using an ordered Categorical and numpy.cumsum:
import numpy as np
s = pd.Categorical(df['Key'], ordered=True)
df['Index'] = np.cumsum(s<s.shift())+1
Output:
Key Value Index
0 A 10 1
1 A 20 1
2 B 30 1
3 B 40 1
4 C 50 1
5 A 60 2
6 A 70 2
7 A 70 2
8 B 80 2
9 A 90 3