This is going be my first question here a stack overflow. I've searched through the forum and found one question which is the exact issue that i am unable to find a solution to. There has been no answer to it though. I've reproduced it here. Any help would be appreciated.
What is pandas syntax for lookup based on existing columns + row values?
Here is a summary of what's already been done
Create a pandas df1 with two columns: 'Date' and 'Price' - done
I add two new columns: 'rollmax' and 'rollmin', where the 'rollmax' is an 8 days rolling maximum and 'rollmin' is a rolling minimum. - done
- Now I need to create another column 'rollmax_date' that would get populated through a look up rule:
For the row n, go to the column 'Price' and parse through the values for the last 8 days and find the maximum, then get the corresponding Date and put this value in the column 'rollingmax_date'.
The same logic for the 'rollingmin_date', but instead of rolling maximum date, we look for the rolling minimum date.
I did the first two and tried the third one, but I'm getting wrong results.
The code below gives me only dates where on the same row df["Price"] is the same as df['rollmax'], but it doesn't bring all the corresponding dates from 'Date' to 'rollmax_date'
df['rollmax_date'] = df.loc[(df["High"] == df.rollmax), 'Date']
The image as in the Q is below:
EDIT:
Here are additional inputs as requested.
For some reason I am unable to paste a copy of the data frame here. It is a typical data frame of a stock. I have columns as 'Date', 'Open', 'High', 'Close'. The Rollmax and Rollmin have been calculated as above. Now, I need to fill the date of Rollmax and Rollmin in the columns 'Rollmax_date' and 'Rollmin_date'
Here's the DataFrame added. This is formatted poorly. Unable to paste this as a table
As seen here, the first rollmax is 11872, which occurs on 04th Jan 2011. I am trying to achieve getting this value into the column 'Rollmax_date'
df = quandl.get(stock,start_date = start, end_date = end)
df['Date']=df.index
df['rollmax'] = df['High'].rolling(period_up).max().shift(-period_up)
df['rollmin'] = df['Low'].rolling(period_down).min().shift(-period_down)
where period_up & period_down = -8 (the '-' is to lookup data in the future to the current date of the particular row)
Open High Low Close Shares Traded \
Date
2011-01-03 11882.10 11912.15 11826.95 11855.75 8895927.0
2011-01-04 11868.40 11872.80 11545.55 11564.05 16041214.0
2011-01-05 11545.75 11545.75 11265.55 11305.45 19689201.0
2011-01-06 11350.70 11377.10 11164.75 11186.80 19559984.0
2011-01-07 11146.45 11258.50 11007.90 11053.35 25192954.0
2011-01-10 11024.50 11072.70 10671.60 10700.65 21584988.0
2011-01-11 10734.20 10951.20 10620.80 10833.55 22297958.0
2011-01-12 10906.40 11161.15 10728.45 11128.15 24855243.0
2011-01-13 11125.95 11127.95 10676.50 10716.65 18520804.0
2011-01-14 10746.80 10907.30 10387.90 10432.90 19299172.0
Turnover (Rs. Cr) Date rollmax rollmin
Date
2011-01-03 909.29 2011-01-03 11872.80 10373.8
2011-01-04 1949.98 2011-01-04 11545.75 10373.8
2011-01-05 1988.86 2011-01-05 11377.10 10373.8
2011-01-06 2114.13 2011-01-06 11258.50 10373.8
2011-01-07 2746.05 2011-01-07 11161.15 10373.8
2011-01-10 2389.16 2011-01-10 11177.85 10373.8
2011-01-11 2407.41 2011-01-11 11256.95 10373.8
2011-01-12 2703.24 2011-01-12 11256.95 10373.8
2011-01-13 1939.18 2011-01-13 11256.95 10373.8
2011-01-14 2054.64 2011-01-14 11256.95 10373.8
