I have a dataframe. I assigned a uniuqe value to each group. But also want to assign a unique value to each element or subgroup of each group.
df = pd.DataFrame({'A':[1,2,3,4,6,3,7,3,2],'B':[4,3,8,2,6,3,9,1,0], 'C':['a','a','c','b','b','b','b','c','c']})
I assigned a unique value to each group as follow
df.groupby('C').ngroup()
But i want output as
index grp subgrp
0 0 0
1 0 1
2 2 0
3 1 0
4 1 1
5 1 2
6 1 3
7 2 1
8 2 2
>Solution :
Adding cumcount after get the grp column
df['grp'] = df.groupby('C').ngroup()
df['subgrp'] = df.groupby('grp').cumcount()
df
Out[356]:
A B C grp subgrp
0 1 4 a 0 0
1 2 3 a 0 1
2 3 8 c 2 0
3 4 2 b 1 0
4 6 6 b 1 1
5 3 3 b 1 2
6 7 9 b 1 3
7 3 1 c 2 1
8 2 0 c 2 2