Changing a value in DataFrame where the same value met a condition and the previous value meets anot-CodePudding

I am trying to create a column where if the previous value in the same column meets a condition then that value must become the previous value. For example...

PREVIOUS col | CORRECT col
HW              HW
HW              HW
RF              RF
FW              FW
FW              FW
FWRF            FW
FW              FW

As you can see 'FWRF' has changed to 'FW' if the PREVIOUS col value is == 'FW'. I am trying to get the [CORRECT col] to work but I have run into issues.

Heres what I have tried so far to no avail...

df['CORRECT col'] = df.loc[df['PREVIOUS col'].shift(-1) == 'FWRF', 'PREVIOUS col']

I have also tried to create 'if' statements for this to work and that was even worse... but I am still learning how to use pandas effectively as I can. Your help will be greatly appreciated.

CodePudding user response：

There are 2 possibilities based on your description:

Either

you only need to check previous value is FW to change current value also to FW,

you need also to check that the current value is FWRF together with previous value is FW to change current value to FW

For case 1, you can use .mask() and .shift(), as follows:

df['CORRECT col'] = df['PREVIOUS col'].mask(df['PREVIOUS col'].shift() == 'FW', 'FW')

Here, we get the previous row value of PREVIOUS col using .shift() and check whether it is FW, if yes, we change the current row value also to FW using .mask() (If the condition is False, we keeps the original value).

Result:

print(df)

  PREVIOUS col CORRECT col
0           HW          HW
1           HW          HW
2           RF          RF
3           FW          FW
4           FW          FW
5         FWRF          FW
6           FW          FW

For case 2 where you also want to check the current row value is FWRF, you can change slightly to add a checking, as follows:

df['CORRECT col'] = df['PREVIOUS col'].mask((df['PREVIOUS col'] == 'FWRF') & (df['PREVIOUS col'].shift() == 'FW'), 'FW')

Here, we added the checking (df['PREVIOUS col'] == 'FWRF') and use & (and) with the condition (df['PREVIOUS col'].shift() == 'FW') so that only when BOTH conditions are true we change the value.

Same result for the sample data but see whether your real data require checking one condition or both conditions to work best.