Getting smallest value of a column in last n rows

Question

The title of this might be a bit confusing but i will try to explain what i am trying to do. I have an OHLC csv of stocks. I am trying to add a new column named L and H. Here what represents. I need to check for the smallest Open price in last n days (say 5). and i need to set it's Low price to L and High price as H.

Here is my Dataframe

            Date     Open     High      Low
5303  2018-06-11  1681.51  1694.24  1680.59
5304  2018-06-12  1693.00  1699.51  1691.52
5305  2018-06-13  1702.81  1713.75  1700.12
5306  2018-06-14  1713.48  1724.80  1708.87
5307  2018-06-15  1714.00  1720.87  1708.52
5308  2018-06-18  1706.26  1726.74  1702.56
5309  2018-06-19  1709.04  1736.11  1700.39
5310  2018-06-20  1742.50  1762.93  1741.36
5311  2018-06-21  1760.00  1763.10  1717.56
5312  2018-06-22  1742.62  1743.00  1711.90

I have tried the following way but it only returns the minimum value not the Series i can grab High and Low

df['Open'].rolling(5).min()

Here i am trying to get the index or whole Dataframe series of rolling() to grab Hig and Low values of this rolling. I hope I could explain.

EDIT: Added expected output

Let's say index 5308 is the smallest Open in last 5 rows. So L will be equal to 5308's Low column and H will be equal to 5308's High column in the rest of data

            Date     Open     High      Low        H        L
5309  2018-06-19  1709.04  1736.11  1700.39  1726.74  1702.56
5310  2018-06-20  1742.50  1762.93  1741.36  1726.74  1702.56
5311  2018-06-21  1760.00  1763.10  1717.56  1726.74  1702.56
5312  2018-06-22  1742.62  1743.00  1711.90  1726.74  1702.56

I guess you need to use an `apply` on `df.rolling(5)` you can check this [example](https://stackoverflow.com/questions/40954560/pandas-rolling-apply-custom) — rpanai, Jun 28 '18 at 12:43
At index 5309 the previous 5 rows index 5305 is lowest opening, See solutions below. — Scott Boston, Jun 28 '18 at 13:26

Scott Boston · Accepted Answer · 2018-06-28T12:47:05.057

IIUC, use idxmin in apply with rolling:

df[['L','H']] = (df.reindex(df['Open'].rolling(5)
                                      .apply(lambda x: x.idxmin(), 
                                             raw=False))[['Low','High']].values)

Output:

            Date     Open     High      Low        L        H
5303  2018-06-11  1681.51  1694.24  1680.59      NaN      NaN
5304  2018-06-12  1693.00  1699.51  1691.52      NaN      NaN
5305  2018-06-13  1702.81  1713.75  1700.12      NaN      NaN
5306  2018-06-14  1713.48  1724.80  1708.87      NaN      NaN
5307  2018-06-15  1714.00  1720.87  1708.52  1680.59  1694.24
5308  2018-06-18  1706.26  1726.74  1702.56  1691.52  1699.51
5309  2018-06-19  1709.04  1736.11  1700.39  1700.12  1713.75
5310  2018-06-20  1742.50  1762.93  1741.36  1702.56  1726.74
5311  2018-06-21  1760.00  1763.10  1717.56  1702.56  1726.74
5312  2018-06-22  1742.62  1743.00  1711.90  1702.56  1726.74

Actually i am trying to get High and Low columns of the minimum Open. Not the min max values of the column — Don Coder, Jun 28 '18 at 12:35
in your example last row you have index 5308 as L. What i need is index 5308's High and Low values — Don Coder, Jun 28 '18 at 12:37

Getting smallest value of a column in last n rows

1 Answers1