'How to use data from different timeframe in a dataframe based on a time series?
In a dataframe containing the price of the Nasdaq index, I want to get the price over different time units.
For the non-traders, the most common price representation is called 'Japanese candle', and takes into account the opening, closing, high and low price for each line. In my case, each line of the dataframe represents a 1min candle.
In trading it is important to take into account the data on different time units (to know the short term, medium and long term trends...) So I want to add a column that will display data based on another time unit: the opening price, the highest and lowest of the day in 15 minutes (in addition to the 1min).
So I used the resample method of pandas to get a dataframe with the information in 15minutes (taking care to rename the columns :
df = pd.DataFrame({
'Time' : ['2022-01-11 09:30:00', '2022-01-11 09:31:00', '2022-01-11 09:32:00', '2022-01-11 09:33:00', '2022-01-11 09:34:00', '2022-01-11 09:35:00', '2022-01-11 09:36:00' ,
'2022-01-11 09:37:00' , '2022-01-11 09:38:00' , '2022-01-11 09:39:00', '2022-01-11 09:40:00', '2022-01-11 09:41:00', '2022-01-11 09:42:00','2022-01-11 09:43:00',
'2022-01-11 09:44:00', '2022-01-11 09:45:00',
'2022-01-11 09:46:00', '2022-01-11 09:47:00', '2022-01-11 09:48:00', '2022-01-11 09:49:00', '2022-01-11 09:50:00', '2022-01-11 09:51:00', '2022-01-11 09:52:00' ,
'2022-01-11 09:53:00' , '2022-01-11 09:54:00' , '2022-01-11 09:55:00', '2022-01-11 09:56:00', '2022-01-11 09:57:00', '2022-01-11 09:58:00','2022-01-11 09:59:00',
'2022-01-11 10:00:00'],
'Open' : [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31],
'High' : [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30],
'Low' : [4,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29],
'Close' : [2,0,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28]})
df['Time'] = pd.to_datetime(df['Time'])
df.set_index(['Time'], inplace =True)
df_15 = df_rand.resample('15min').first()
df_15
I then used the concat method to group the 1min data and the 15min data within the same dataframe :
df_15.rename(columns={'Open' : 'Open_15',
'High' : 'High15',
'Low' : 'Low15',
'Close' : 'Close15'})
The 2 problems I am encountering: First, the change of column name is not taken into account, I have 2 times the column 'Close'... and I cannot create indicators for each individual column:
df_all = pd.concat([df, df_15], axis=1)
df_all['Close']
Second, in this new dataframe, between each quarter of an hour, the rows of the columns Open - Close - High - Low in 15min are empty:
df_all = pd.concat([df, df_15], axis=1)
df_all
What I want is that these rows are filled with the new data that occurs every minute. In the example provided, the Low column in 1min (the one on the left) starts at 4 at 9:30, and this number will only be exceeded at 9:36 by 5. The first lines of the 'Low' column in 15min (the one on the right), should therefore start with 4, 4, 4 , 4, 4 , 4, 5, 6 ... and when a maximum is reached it remains displayed in the column until the next quarter of an hour. At this point the countdown resumes. The process is similar maid with the minima for the 'low' column. For the 'open' column, each line should be the opening price of the 1st minute of each quarter. (and the 'close' column will show the same price for each minute as the one in the close column in 1min)
As there are many websites on trading there must be a solution or library that can display this data.
Can someone help me solve these problems:
- How to definitely rename the columns created for the 15min data?
- How to fill the rows with the new data?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
