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?
>Solution :
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