[Python] Tag subsequent rows after value in one columnA series changes based on a condition in...

I have a problem with the following data:

month product version price category
1 A alpha 500 foo
2 A alpha 500 foo
3 A alpha 550 foo
4 A alpha 600 bar
5 A alpha 500 bar
6 A alpha 500 faz
7 A alpha 500 faz
8 A alpha 500 faz
9 A alpha 500 faz
10 A alpha 500 foo
4 A beta 700 bar
5 A beta 800 bar
6 A beta 800 faz
7 A beta 800 faz
8 A beta 800 faz
9 A beta 700 faz
10 A beta 500 foo
1 B alpha 200 foo
2 B alpha 200 foo
3 B alpha 250 foo
4 B alpha 250 bar
5 B alpha 250 bar
6 B alpha 250 faz
7 B alpha 250 faz
8 B alpha 250 faz
9 B alpha 500 faz
10 B alpha 500 foo
4 B beta 700 bar
5 B beta 800 bar
6 B beta 800 faz
7 B beta 800 faz
8 B beta 800 faz
9 B beta 700 faz
10 B beta 500 foo

What I would like to do is group by the product and version, and tag the rows where the price remains the same BUT the category changes from the preceding month for each product+version group. Additionally I want to tag all rows below the first category change where the price is consistent with the last row of the preceding category

I have tried approaches in SQL with LEAD and LAG as well as CASE WHEN exists but I am not able to get the logic to work as desired. My approaches have been able to tag one following month but not several months after a category change.

I am also open to implementing a solution in python.

The tag column below represents the desired output.

month product version price category tag
1 A alpha 500 foo OK
2 A alpha 500 foo OK
3 A alpha 550 foo OK
4 A alpha 600 bar OK
5 A alpha 500 bar OK
6 A alpha 500 faz FLAG
7 A alpha 500 faz FLAG
8 A alpha 500 faz FLAG
9 A alpha 500 faz FLAG
10 A alpha 500 foo FLAG
4 A beta 700 bar OK
5 A beta 800 bar OK
6 A beta 800 faz FLAG
7 A beta 800 faz FLAG
8 A beta 800 faz FLAG
9 A beta 700 faz OK
10 A beta 500 foo OK
1 B alpha 200 foo OK
2 B alpha 200 foo OK
3 B alpha 250 foo OK
4 B alpha 250 bar FLAG
5 B alpha 250 bar FLAG
6 B alpha 250 faz FLAG
7 B alpha 250 faz FLAG
8 B alpha 250 faz FLAG
9 B alpha 500 faz OK
10 B alpha 500 foo OK
4 B beta 700 bar OK
5 B beta 800 bar OK
6 B beta 800 faz FLAG
7 B beta 800 faz FLAG
8 B beta 800 faz FLAG
9 B beta 700 faz OK
10 B beta 500 foo OK

I have tried this approach:

WITH cte AS ( SELECT , ROW_NUMBER() OVER (PARTITION BY product, version ORDER BY month ASC) AS rn FROM table ),
flux AS ( SELECT c., CASE WHEN EXISTS( SELECT 1 FROM cte c2 WHERE (c.product = c2.product AND c.version = c2.version) AND c2.rn < c.rn AND c2.price BETWEEN -1000 AND 1000 AND c.category != c2.category ) THEN 1 ELSE 0 END AS tag FROM cte c ORDER BY month )
SELECT * FROM flux

Continue reading...