Identify groups that have an A-B-A change

I have to find occurrences in the data that match a change like 'A-B-A'. I have my dataset grouped and sorted in the correct way. The values that need to be compared are the columns VAL1 and VAL2. In this example they are numbers, but they might be string values as well.

Sample Data explanation

All values of VAL1 and VAL2 in group 1 are different and I'm not interested in these A-B-C changes.

All values of VAL1 and VAL2 in group 2 are the same, which is fine too. Changes like A-A-A or even A-A-B-B-B-C are fine and I'm not interested in those either.

In group 3 the first and the third VAL1 and VAL2 are the same. So this is the kind of A-B-A change I'm looking for. Also changes like

• A-B-C-B
• A-B-C-D-A

I am interested in. Basically it does not matter where in the group the previous same row is, as long as there is a different row in between I'm interested in this Group

import pandas as pd

df = pd.DataFrame([
     [273,1,'01.01.2016','16.02.2017','23154','1'],
     [273,1,'16.03.2017','31.10.2019','48625','1'],
     [273,1,'01.11.2019','31.12.2099','32007','1'],
     [274,2,'01.01.2016','16.02.2017','23154','1'],
     [274,2,'16.03.2017','31.10.2019','23154','1'],
     [275,3,'01.01.2016','16.02.2017','78945','1'],
     [275,3,'16.03.2017','31.10.2019','48625','2'],
     [275,3,'01.11.2019','31.12.2099','78945','1'],
 ], columns=['ID','GROUP','FROM','UNTIL','VAL1','VAL2'])

What I want

I would like the resulting dataframe to only have the groups which do have a kind of A-B-A change in them.

So in my example data only the rows of group 3 would remain.

My attempt at the problem

My Idea is to first find any duplicates for each group, then check in the group with duplicates if there are any A-B-A changes and last only select the groups that fullfill the criteria.

So I ran the pandas duplicate function like this

df['DUPES'] = df.duplicated(subset=['GROUP', 'VAL1', 'VAL2'], keep=False)

and got following dataframe, but I do not know how to progress from this point on.

Next I would need to check if there are are A-B-A changes in a group I'd like to find. So my Idea is to check each member in the group

• If it is the first item in the group and it is a Dupe it should be ignored
• If it is NOT the first but a Dupe AND
  • the previous entry in the group has the same Val1 and Val2 it is a valid A-A change
  • the previous entry in the group has NOT the same Val1 and Val2 found an A-B-A change

I know I can use a mask like df['VAL1'].eq(df['VAL1'].shift()) & df['VAL2'].eq(df['VAL2'].shift()) to compare with the previous value, but how would I limit this to the group? Also I'm not checking if the value is ...

Edit: I played around a little more and came up with this mask

df['DUPES2'] = df['DUPES'] & df['GROUP'].eq(df['GROUP'].shift()) & ~(df['VAL1'].eq(df['VAL1'].shift()) & df['VAL2'].eq(df['VAL2'].shift()))

The resulting dataframe looks fine, but I still do not know how to only select the groups

Question to the community

Is there any other way how I could utilize pandas functionality to achieve my goal?

Thanks for helping