I have a pandas df with some values, and I am trying to find out the min value of a column or a rolling basis, as well as the indices of those rolling min values.
For example,
df["low"].rolling(200).min()
creates a series of the min "low" in a rolling 200 period.
Can anybody suggest how to get the indices for "low" of that new rolling series?
You can use argmin:
import pandas as pd
import numpy as np
np.random.seed(0)
df = pd.DataFrame({'low': np.random.randint(0,100,20)})
window_size = 4
df['min'] = df['low'].rolling(window_size).min()
df['min_idx'] = (df['low'].rolling(window_size).apply(np.argmin) + df.index - window_size + 1).astype('Int64')
Result:
low min min_idx
0 44 NaN <NA>
1 47 NaN <NA>
2 64 NaN <NA>
3 67 44.0 0
4 67 47.0 1
5 9 9.0 5
6 83 9.0 5
7 21 9.0 5
8 36 9.0 5
9 87 21.0 7
10 70 21.0 7
11 88 36.0 8
12 88 70.0 10
13 12 12.0 13
14 58 12.0 13
15 65 12.0 13
16 39 12.0 13
17 87 39.0 16
18 46 39.0 16
19 88 39.0 16
0 to len(df)-1. If you have any other index it becomes a bit more involved:
np.random.seed(0)
df = pd.DataFrame({'low': np.random.randint(0,100,20)})
df.index = np.random.permutation(20)
window_size = 4
df['min'] = df['low'].rolling(window_size).min()
min_idx = df.index[(df['low'].rolling(window_size).apply(np.argmin) + pd.RangeIndex(len(df)) - window_size + 1)[window_size-1:].astype(int)]
df['min_idx'] = [np.nan]*(window_size - 1) + min_idx.tolist()
df['min_idx'] = df['min_idx'].astype('Int64')
Result:
low min min_idx
6 44 NaN <NA>
14 47 NaN <NA>
16 64 NaN <NA>
10 67 44.0 6
7 67 47.0 14
1 9 9.0 1
15 83 9.0 1
11 21 9.0 1
2 36 9.0 1
12 87 21.0 11
18 70 21.0 11
19 88 36.0 2
0 88 70.0 18
3 12 12.0 3
4 58 12.0 3
9 65 12.0 3
8 39 12.0 3
13 87 39.0 8
5 46 39.0 8
17 88 39.0 8