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