Finding the min. value in a specific column for range of future rows with pandas/python

Question

I have the following data:

datetime    price
2017-10-02 08:03:00 12877
2017-10-02 08:04:00 12877.5
2017-10-02 08:05:00 12879
2017-10-02 08:06:00 12875.5
2017-10-02 08:07:00 12875.5
2017-10-02 08:08:00 12878
2017-10-02 08:09:00 12878
2017-10-02 08:10:00 12878
2017-10-02 08:11:00 12881
2017-10-02 08:12:00 12882.5
2017-10-02 08:13:00 12884.5
2017-10-02 08:14:00 12882
2017-10-02 08:15:00 12880.5
2017-10-02 08:16:00 12881.5
2017-10-02 08:17:00 12879
2017-10-02 08:18:00 12879
2017-10-02 08:19:00 12880
2017-10-02 08:20:00 12878.5

I am trying to find the min. price for range (the range is defined with windows_size which can be 1/2/3 etc.) of 'datetime' using:

df['MinPrice'] = df.ix[window_size:,'price']

which gives me the price on the last row in the window or using

df['MinPrice'] = df.ix[window_size:,'price'].min()

which gives me the min value of all the column.

Pls advice how to get the min. value of the specific rows declared by the window.

edited: the expected result will be as follow: if the windows size is 3, i would like to get the min. value of 3 lines. so at 08:05:00 i will get 12877 and for 08:06:00 i will get 12875.5

Answer 1

You may want to keep the length of the dataframe the same:

df['Price_Low3'] = np.where(pd.isna(df.price.shift(periods=2)),df.price,df.price.rolling(3).min())

As a result you get:

            datetime    price  Price_Low3
0   02/10/2017 08:03  12877.0     12877.0
1   02/10/2017 08:04  12877.5     12877.5
2   02/10/2017 08:05  12879.0     12877.0
3   02/10/2017 08:06  12875.5     12875.5
4   02/10/2017 08:07  12875.5     12875.5
5   02/10/2017 08:08  12878.0     12875.5
6   02/10/2017 08:09  12878.0     12875.5
7   02/10/2017 08:10  12878.0     12878.0
8   02/10/2017 08:11  12881.0     12878.0
9   02/10/2017 08:12  12882.5     12878.0
10  02/10/2017 08:13  12884.5     12881.0
11  02/10/2017 08:14  12882.0     12882.0
12  02/10/2017 08:15  12880.5     12880.5
13  02/10/2017 08:16  12881.5     12880.5
14  02/10/2017 08:17  12879.0     12879.0
15  02/10/2017 08:18  12879.0     12879.0
16  02/10/2017 08:19  12880.0     12879.0
17  02/10/2017 08:20  12878.5     12878.5

Answer 2

Since it looks like you have 1 minute intervals, you may want to take advantage of resample, that way you can define the window using datetime

df.resample('3T',on='datetime').min()

                             datetime    price
datetime                                        
2017-10-02 08:03:00 2017-10-02 08:03:00  12877.0
2017-10-02 08:06:00 2017-10-02 08:06:00  12875.5
2017-10-02 08:09:00 2017-10-02 08:09:00  12878.0
2017-10-02 08:12:00 2017-10-02 08:12:00  12882.0
2017-10-02 08:15:00 2017-10-02 08:15:00  12879.0
2017-10-02 08:18:00 2017-10-02 08:18:00  12878.5

To set the values back to the initial dataframe, use transform

df['minPrice'] = df.resample('3T',on='datetime').transform('min')

             datetime    price  minPrice
0  2017-10-02 08:03:00  12877.0   12877.0
1  2017-10-02 08:04:00  12877.5   12877.0
2  2017-10-02 08:05:00  12879.0   12877.0
3  2017-10-02 08:06:00  12875.5   12875.5
4  2017-10-02 08:07:00  12875.5   12875.5
5  2017-10-02 08:08:00  12878.0   12875.5
6  2017-10-02 08:09:00  12878.0   12878.0
7  2017-10-02 08:10:00  12878.0   12878.0
8  2017-10-02 08:11:00  12881.0   12878.0
9  2017-10-02 08:12:00  12882.5   12882.0
10 2017-10-02 08:13:00  12884.5   12882.0
11 2017-10-02 08:14:00  12882.0   12882.0
12 2017-10-02 08:15:00  12880.5   12879.0
13 2017-10-02 08:16:00  12881.5   12879.0
14 2017-10-02 08:17:00  12879.0   12879.0
15 2017-10-02 08:18:00  12879.0   12878.5
16 2017-10-02 08:19:00  12880.0   12878.5
17 2017-10-02 08:20:00  12878.5   12878.5

Answer 3

Have a look at pandas.DataFrame.rolling

df.rolling(window=3).apply(min).dropna()

will give the expected result:

datetime    price
2017-10-02  08:05:00    12877.0
2017-10-02  08:06:00    12875.5
2017-10-02  08:07:00    12875.5
2017-10-02  08:08:00    12875.5
2017-10-02  08:09:00    12875.5
2017-10-02  08:10:00    12878.0
2017-10-02  08:11:00    12878.0
2017-10-02  08:12:00    12878.0
2017-10-02  08:13:00    12881.0
2017-10-02  08:14:00    12882.0
2017-10-02  08:15:00    12880.5
2017-10-02  08:16:00    12880.5
2017-10-02  08:17:00    12879.0
2017-10-02  08:18:00    12879.0
2017-10-02  08:19:00    12879.0
2017-10-02  08:20:00    12878.5