Is there any good way to group time series Stock Data?

user_14596075 0
Viewed 341

I am facing a problem to group my stock market data which has a custom time frame.. The raw data looks like the following.. I want to group this in 2 hours frame. As my data's starting time is 9:15AM I want to group 9:15:00 to 11:15:00 data for a particular date. also the data for a particular day ends at 15:15:00. but facing some issue while doing so. describing below.

Actual Raw Data
TIME                 Open   High    Low     CLOSE   VOLUME
2019-10-31 09:15:00 235.70  236.50  234.40  234.55  1306585 
2019-10-31 10:15:00 234.55  235.05  233.40  234.00  765419      
2019-10-31 11:15:00 234.00  235.25  233.65  234.10  664682      
2019-10-31 12:15:00 234.10  235.75  234.00  235.70  676993      
2019-10-31 13:15:00 235.75  236.15  234.25  235.25  527381      
2019-10-31 14:15:00 235.25  235.80  227.10  227.45  2416364     
2019-10-31 15:15:00 227.50  228.00  227.05  227.70  613380      
2019-11-01 09:15:00 227.70  232.45  227.50  231.85  1844851     
2019-11-01 10:15:00 231.95  233.05  231.05  232.20  1000537     
2019-11-01 11:15:00 232.20  232.65  231.05  232.60  454966      
2019-11-01 12:15:00 232.60  233.50  231.40  231.70  569539      
2019-11-01 13:15:00 231.70  236.45  231.40  235.35  1388397 

Code Used
df['TIME'] = pd.to_datetime(df['TIME'])
df = df.set_index("TIME")
df = df.groupby(pd.Grouper(freq='2H')).agg({"Open": "first", 
                                             "CLOSE": "last", 
                                             "Low": "min", 
                                             "High": "max"})

OUTPUT
Using the above code I am getting wrong information and calculations as below
TIME                 Open    CLOSE  Low      High
2019-10-31 08:00:00 235.70  234.55  234.40  236.50
2019-10-31 10:00:00 234.55  234.10  233.40  235.25
2019-10-31 12:00:00 234.10  235.25  234.00  236.15
2019-10-31 14:00:00 235.25  227.70  227.05  235.80
2019-10-31 16:00:00 NaN NaN NaN NaN
2019-10-31 18:00:00 NaN NaN NaN NaN
2019-10-31 20:00:00 NaN NaN NaN NaN
2019-10-31 22:00:00 NaN NaN NaN NaN
2019-11-01 00:00:00 NaN NaN NaN NaN
2019-11-01 02:00:00 NaN NaN NaN NaN

I want to have this data to start with 9:15:00 and end with 15:15 for a particular date. Also want to avoid any NAN value which could affect my next date's 2Hour data calculation

I have searched so many places on the internet and had gone through the documentations but could not solve this issue, In the mean Time I also want to do the same calculation for 5 minute data to hourly or custom defined minute interval data keeping the timing of the stock market in mind. Could you please help me with this?

pandas dataframe date python
2 Answers

You can use resample, specifying on the datetime column TIME instead of the index, and using origin='start' to start the resampled time series on the first value in the TIME column. Then just drop null values.

If you've already set the index of the dataframe via df = df.set_index("TIME"), then you can drop on='TIME' from the arguments to resample, as the resampling is done on the index by default.

>>> df.resample('2H', on='TIME', origin='start').agg(
    {"Open": "first", 
     "CLOSE": "last", 
     "Low": "min", 
     "High": "max"}
).dropna()[['Open', 'High', 'Low', 'CLOSE']]
                       Open    High     Low   CLOSE
TIME                                               
2019-10-31 09:15:00  235.70  236.50  233.40  234.00
2019-10-31 11:15:00  234.00  235.75  233.65  235.70
2019-10-31 13:15:00  235.75  236.15  227.10  227.45
2019-10-31 15:15:00  227.50  228.00  227.05  227.70
2019-11-01 09:15:00  227.70  233.05  227.50  232.20
2019-11-01 11:15:00  232.20  233.50  231.05  231.70
2019-11-01 13:15:00  231.70  236.45  231.40  235.35

​

user_24118020

Use .resample(origin='start') to start grouping by 2 hours at your first timestamp.

Don't reinvent the wheel by defining you own open / high / low / close function. There's already a special .ohlc() function for that in pandas :)

# import libaries
import pandas as pd
from io import StringIO

# example data
text = """
TIME                 Open   High    Low     CLOSE   VOLUME
2019-10-31T09:15:00 235.70  236.50  234.40  234.55  1306585 
2019-10-31T10:15:00 234.55  235.05  233.40  234.00  765419      
2019-10-31T11:15:00 234.00  235.25  233.65  234.10  664682      
2019-10-31T12:15:00 234.10  235.75  234.00  235.70  676993      
2019-10-31T13:15:00 235.75  236.15  234.25  235.25  527381      
2019-10-31T14:15:00 235.25  235.80  227.10  227.45  2416364     
2019-10-31T15:15:00 227.50  228.00  227.05  227.70  613380      
2019-11-01T09:15:00 227.70  232.45  227.50  231.85  1844851     
2019-11-01T10:15:00 231.95  233.05  231.05  232.20  1000537     
2019-11-01T11:15:00 232.20  232.65  231.05  232.60  454966      
2019-11-01T12:15:00 232.60  233.50  231.40  231.70  569539      
2019-11-01T13:15:00 231.70  236.45  231.40  235.35  1388397 
"""

# create example df
df = pd.read_csv(StringIO(text), header=0, sep='\s+', parse_dates=['TIME'])

# melt data to get in the right form for resample and .ohlc()
df_melt = pd.melt(
    df, 
    id_vars=['TIME'], 
    value_vars=['Open', 'High', 'Low', 'CLOSE']
).drop(columns=['variable'])

# resample data by 2 hours and use origin=start to start with 
# the first available timestamp, in this case 09:15:00
result_df = (df_melt
    .set_index('TIME')
    .resample('2h', origin='start')
    .ohlc()
    .dropna()
)

Resulting dataframe:

TIME                open    high    low     close       
2019-10-31 09:15:00 235.70  236.50  233.40  234.00
2019-10-31 11:15:00 234.00  235.75  233.65  235.70
2019-10-31 13:15:00 235.75  236.15  227.10  227.45
2019-10-31 15:15:00 227.50  228.00  227.05  227.70
2019-11-01 09:15:00 227.70  233.05  227.50  232.20
2019-11-01 11:15:00 232.20  233.50  231.05  231.70
2019-11-01 13:15:00 231.70  236.45  231.40  235.35
user_34891550
Related