Python: Need to reindex dataframe row w.r.t. column values, per group

Got input dataframe df as below:

Flow        Side        Row
Banana      Front       Row 1
PP Banana   Front       Row 1
Peach       Front       Row 1
Red Grape   Right End   Row 1
Kp Mango    Back        Row 1
PP Kp Mango Back        Row 1
Tomato      Left End    Row 1
Cucum       Front       Row 2
PP Cucum    Front       Row 2
Bunch       Right End   Row 2
Capsicum    Back        Row 2
Romo        Left End    Row 2

Scenario: If df['Flow'].str.contains('PP') && df['Side']=='Front', then that particular row need to be shifted as last row to Side 'Back' of same Row value. As in Example for Flows 'PP Banana' and 'PP Cucum'. Note - Other Flow order should not be changed. Also, column 'Side' order should be always Front,Right End,Back,Left End for each Row value in 'Row' column.

Expected Output:

Flow        Side        Row
Banana      Front       Row 1
Peach       Front       Row 1
Red Grape   Right End   Row 1
Kp Mango    Back        Row 1
PP Kp Mango Back        Row 1
PP Banana   Back        Row 1
Tomato      Left End    Row 1
Cucum       Front       Row 2
Bunch       Right End   Row 2
Capsicum    Back        Row 2
PP Cucum    Back        Row 2
Romo        Left End    Row 2

Any help will be appreciated!


You can use a mask to extract the rows that needs to be moved and the trailing ("Left End") rows, then join again using concat and reorder per group.

m1 = df['Flow'].str.contains('PP') & df['Side'].eq('Front')
m2 = df['Side'].eq('Left End')

mask = m1|m2

out = (pd.concat([df[~mask], df[mask].replace({'Side': {'Front': 'Back'}})])
         .sort_values(by='Row')
      )

Alternatively, if you don't want to sort the groups at the end and work on them independently, you could use a custom function:

def move(d):
    m1 = d['Flow'].str.contains('PP') & d['Side'].eq('Front')
    m2 = d['Side'].eq('Left End')
    mask = m1|m2
    return pd.concat([d[~mask], d[mask].replace({'Side': {'Front': 'Back'}})])

out = df.groupby('Row', group_keys=False).apply(move)

output:

           Flow       Side    Row
0        Banana      Front  Row 1
2         Peach      Front  Row 1
3     Red Grape  Right End  Row 1
4      Kp Mango       Back  Row 1
5   PP Kp Mango       Back  Row 1
1     PP Banana       Back  Row 1
6        Tomato   Left End  Row 1
7         Cucum      Front  Row 2
9         Bunch  Right End  Row 2
10     Capsicum       Back  Row 2
8      PP Cucum       Back  Row 2
11         Romo   Left End  Row 2