Changing column based on multiple conditions and previous rows values pandas

Question

I have this dataframe. I need to replace NaNs in column rank to a value based on multiple conditions. If column min is higher than 3 previous rows of max column then rank equals to min. Otherwise, I need to copy the previous value of rank

      max     min    rank
0  128.20  117.87  117.87
1  132.72  122.29  122.29
2  138.07  124.89  124.89
3  137.02  128.46     NaN
4  130.91  129.86     NaN
5  200.15  190.54     NaN
6  199.18  191.79     NaN
7  210.44  201.94     NaN

The desired result is

      max     min    rank
0  128.20  117.87  117.87
1  132.72  122.29  122.29
2  138.07  124.89  124.89
3  137.02  128.46  124.89
4  130.91  129.86  124.89
5  200.15  190.54  190.54
6  199.18  191.79  190.54
7  210.44  201.94  201.94

Nk03 · Accepted Answer · 2021-06-21 21:04:35Z

1

IIUC, here's one way:

df['rank'].mask(pd.concat([df['min'].shift(i) for i in range(3)], 1).apply(
    lambda x: x < df['min']).all(1), df['min']).ffill()

OUTPUT:

      max     min    rank
0  128.20  117.87  117.87
1  132.72  122.29  122.29
2  138.07  124.89  124.89
3  137.02  128.46  124.89
4  130.91  129.86  124.89
5  200.15  190.54  190.54
6  199.18  191.79  190.54
7  210.44  201.94  201.94

answered Jun 21, 2021 at 21:04

Nk03

15k2 gold badges11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bogdan Titomir Over a year ago

Thanks, simly perfect

not_speshal · Accepted Answer · 2021-06-21 21:04:54Z

1

You can try:

df["rank"].update(df[df["min"]>df["max"].rolling(3).max().shift(1)]["min"])
df["rank"].ffill(inplace=True)
>>> df
      max     min    rank
0  128.20  117.87  117.87
1  132.72  122.29  122.29
2  138.07  124.89  124.89
3  137.02  128.46  124.89
4  130.91  129.86  124.89
5  200.15  190.54  190.54
6  199.18  191.79  190.54
7  210.44  201.94  201.94

The rolling and shift functions are being used to check if the current min is greater than the max of the three previous max.

The ffill carries forward the previous value.

answered Jun 21, 2021 at 21:04

not_speshal

23.2k2 gold badges18 silver badges33 bronze badges

1 Comment

Bogdan Titomir Over a year ago

Thank you, works perfectly. I chose another answer as correct just because it is only one line of code, but yours also works without any problems

Collectives™ on Stack Overflow

Changing column based on multiple conditions and previous rows values pandas

2 Answers 2

OUTPUT:

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

OUTPUT:

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related