CODEWITHSUNDEEP
pythonpandas
Charlie Siu
Charlie Siu

Reputation: 99

Get minimun value of a column by comparing previous n rows in Pandas

I want to get the min value of a column by compare the value in current row with the value in previous 2 rows, I know this can be done by creating 2 columns with the shift(-1) and shift(-2) and return the min value of the row, but I would like to know if there is any way to do it better if I extend the range from previous 2 rows to n rows.

for example in below dataset

 df= pd.DataFrame([12,11,4,15,6,],columns=['score'])
>>> df
   score
0     12
1     11
2      4
3     15
4      6

create new columns prv_score_1, prv_score_2 for previous value

>>> df['prv_score_1'] = df['score'].shift(-1)
>>> df['prv_score_2'] = df['score'].shift(-2)
>>> df
   score  prv_score_1  prv_score_2
0     12         11.0          4.0
1     11          4.0         15.0
2      4         15.0          6.0
3     15          6.0          NaN
4      6          NaN          NaN

Create a Minimum column and get the minimum value of the row

>>> df['Minimum'] = df.min(1)
>>> df
   score  prv_score_1  prv_score_2  Minimum
0     12         11.0          4.0      4.0
1     11          4.0         15.0      4.0
2      4         15.0          6.0      4.0
3     15          6.0          NaN      6.0
4      6          NaN          NaN      6.0

Anyway to do better?

Upvotes: 1

Views: 1281

Answers (3)

a.costa
a.costa

Reputation: 1059

You can check the rolling function:

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rolling.html

In your case, this will do the trick:

df[::-1].rolling(3, min_periods=1).min()[::-1]

Upvotes: 1

muskrat
muskrat

Reputation: 1559

You can achieve this with rolling.min(). For example with window size 2, use: 

df.rolling(2).min()

Then change 2 to n for the more general case.

Upvotes: -1

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

You need rolling min with window 3 i.e 

df['new'] = df['score'][::-1].rolling(3,min_periods=1).min()[::-1]

  score  new
0   12.0  4.0
1   11.0  4.0
2    4.0  4.0
3   15.0  6.0
4    6.0  6.0

Upvotes: 3

Related Questions