How can i extract the index value of an element with respect to another value from some other column?

Question

df1['3DaysMaxHigh']=df1['High'].rolling(window=3).max().shift()
df1['Daysfrom3DayMaxHigh']=('NaN','NaN','NaN',3,1,1,2,3)

Date         High      3DaysMaxHigh   Daysfrom3DayMaxHigh

2021-05-03  1361.000000   NaN              NaN
2021-05-04  1354.949951   NaN              NaN
2021-05-05  1343.900024   NaN              NaN
2021-05-06  1364.699951 1361.000000         3
2021-05-07  1373.050049 1364.699951         1
2021-05-10  1352.900024 1373.050049         1
2021-05-11  1341.000000 1373.050049         2
2021-05-12  1330.000000 1373.050049         3

The values of column 'Daysfrom3DayMaxHigh' are to be filled with code. For each value in column '3DaysMaxHigh', no of days passed since the occurrence from that max High value in column 'High' should be extracted into column 'Daysfrom3DayMaxHigh'.

In Excel the same was possible with match() function.

Answer 1

If I understand you correctly, what you want is: similar question.

And you can do as:

In [58]: df1.index[df1['High'].rolling(window=3).apply(np.argmax, raw=True)[2:-1].astype(int)+np.ar
    ...: ange(len(df1['High'])-3)]
Out[58]: Index(['2021-05-03', '2021-05-06', '2021-05-07', '2021-05-07', '2021-05-07'], dtype='object', name='Date')

Answer 2

You can use idxmax() to get the row index of the max value of the rolling window and then substracted by df1.index to get the relative distance between current row and the max value, as follows:

df1['Daysfrom3DayMaxHigh'] = df1.index - df1['High'].rolling(window=3).apply(lambda x: x.idxmax()).shift()

---

In case your Date column is actually the row index, you can reset the index before the operation and restore it afterwards, as follows:

df1 = df1.reset_index()
df1['Daysfrom3DayMaxHigh'] = df1.index - df1['High'].rolling(window=3).apply(lambda x: x.idxmax()).shift()
df1 = df1.set_index('Date')

Result:

print(df1)



                   High  3DaysMaxHigh  Daysfrom3DayMaxHigh
Date                                                      
2021-05-03  1361.000000           NaN                  NaN
2021-05-04  1354.949951           NaN                  NaN
2021-05-05  1343.900024           NaN                  NaN
2021-05-06  1364.699951   1361.000000                  3.0
2021-05-07  1373.050049   1364.699951                  1.0
2021-05-10  1352.900024   1373.050049                  1.0
2021-05-11  1341.000000   1373.050049                  2.0
2021-05-12  1330.000000   1373.050049                  3.0