Pandas groupby when group criteria repeat

saras 0
Viewed 23
A B C
a b  1
c d  1
e f 2
g h 2
i j 2
K l 1
J K 1
L M  1

I have a dataset that looks something like this. I want to group them based on C. The data is sequential and I want to give unique ids to each group. How can I achieve this?

dataframe pandas
1 Answers

The classical trick is to use the non-equality between successive rows (True where this happens), then a cumulative sum to forward fill and increment the Trues as increasing numerical values.

Using shift and ne, then cumsum to form the group. ngroup to get the group ID:

grouper = df['C'].ne(df['C'].shift()).cumsum()
df['group'] = df.groupby(grouper).ngroup()

Or with diff, and ne then cumsum:

grouper = df['C'].diff().ne(0).cumsum()

output:

   A  B  C  group
0  a  b  1      0
1  c  d  1      0
2  e  f  2      1
3  g  h  2      1
4  i  j  2      1
5  K  l  1      2
6  J  K  1      2
7  L  M  1      2

Intermediates of the logic to construct the grouper:

   C  non-eq  implicit int  cumsum
0  1    True             1       1
1  1   False             0       1
2  2    True             1       2
3  2   False             0       2
4  2   False             0       2
5  1    True             1       3
6  1   False             0       3
7  1   False             0       3
mozway0
Related