Pandas Delete And Shift Cells In A Column Basis Multiple Conditions

I have a situation where I would want to delete and shift cells in a pandas data frame basis some conditions. My data frame looks like this : Value_1 ID_1 Value_2 ID

Solution 1:

You can create mask by condition, here for greater values like ID_1 by DataFrame.gt::

cols1 = ['Value_2','Value_3']
cols2 = ['ID_2','ID_3']

m = df[cols2].gt(df['ID_1'], axis=0)
print (m)
    ID_2   ID_3
0FalseFalse1TrueFalse2TrueTrue3TrueFalse

Then replace missing values if match mask by DataFrame.mask:

df[cols2] = df[cols2].mask(m) 
df[cols1] = df[cols1].mask(m.to_numpy()) 

And last use DataFrame.shift with set new columns by Series.mask:

df1 = df[cols2].shift(-1, axis=1)
df['ID_2'] =  df['ID_2'].mask(m['ID_2'], df1['ID_2'])
df['ID_3'] =  df['ID_3'].mask(m['ID_2'])

df2 = df[cols1].shift(-1, axis=1)
df['Value_2'] =  df['Value_2'].mask(m['ID_2'], df2['Value_2'])
df['Value_3'] =  df['Value_3'].mask(m['ID_2'])

print (df)
  Value_1  ID_1 Value_2  ID_2 Value_3  ID_3
0       A     1       D   1.0       G   1.0
1       B     1       H   1.0     NaN   NaN
2       C     1     NaN   NaN     NaN   NaN
3       C     1       H   1.0     NaN   NaN

And last if necessary replace by empty strings:

df[cols1] = df[cols1].fillna('')
print (df)
  Value_1  ID_1 Value_2  ID_2 Value_3  ID_3
0       A     1       D   1.0       G   1.0
1       B     1       H   1.0           NaN
2       C     1           NaN           NaN
3       C     1       H   1.0           NaN