Assign sequential progressive ID as value in pandas series changes

user_8618380 0
Viewed 41

I have the following DataFrame:

date           product_code     discount
01/01/2022          1              0.7
01/01/2022          2              0.5

02/01/2022          1              0.1
02/01/2022          1              0.1
02/01/2022          2              0.5

03/01/2022          1              0.4

04/01/2022          1              0.1
04/01/2022          2              0.1

05/01/2022          1              0.1

06/01/2022          1              0.1
06/01/2022          1              0.5
...

And I would like to efficiently assign a sequential progressive ID, whenever the discount ratio changes, for each 'product_code' and discount ratio combination.

Thus, obtaining:

date           product_code     discount   promotion_id
01/01/2022          1              0.7          1
01/01/2022          2              0.5          1

02/01/2022          1              0.1          2
02/01/2022          1              0.1          2
02/01/2022          2              0.5          1

03/01/2022          1              0.4          3

04/01/2022          1              0.1          4
04/01/2022          2              0.1          2

05/01/2022          1              0.1          4

06/01/2022          1              0.1          4
06/01/2022          1              0.5          5
...

To better illustrate, for a single product case it would be:

date           product_code     discount   promotion_id
01/01/2022          1              0.7          1

02/01/2022          1              0.1          2
02/01/2022          1              0.1          2

03/01/2022          1              0.4          3

04/01/2022          1              0.1          4

05/01/2022          1              0.1          4

06/01/2022          1              0.1          4
06/01/2022          1              0.5          5
...

How can I achieve that?

pandas group-by python
1 Answers

You may check with diff with cumsum within groupby

df['id'] = df.groupby('product_code',sort=False)['discount'].apply(lambda x : x.diff().ne(0).cumsum())
df
Out[644]: 
          date  product_code  discount  id
0   01/01/2022             1       0.7   1
1   01/01/2022             2       0.5   1
2   02/01/2022             1       0.1   2
3   02/01/2022             1       0.1   2
4   02/01/2022             2       0.5   1
5   03/01/2022             1       0.4   3
6   04/01/2022             1       0.1   4
7   04/01/2022             2       0.1   2
8   05/01/2022             1       0.1   4
9   06/01/2022             1       0.1   4
10  06/01/2022             1       0.5   5
user_79645270
Related