Pandas OHLC aggregation on OHLC data

Question

I understand that OHLC re-sampling of time series data in Pandas, using one column of data, will work perfectly, for example on the following dataframe:

>>df
ctime       openbid
1443654000  1.11700
1443654060  1.11700
...

df['ctime']  = pd.to_datetime(df['ctime'], unit='s')
df           = df.set_index('ctime')
df.resample('1H',  how='ohlc', axis=0, fill_method='bfill')


>>>
                     open     high     low       close
ctime                                                   
2015-09-30 23:00:00  1.11700  1.11700  1.11687   1.11697
2015-09-30 24:00:00  1.11700  1.11712  1.11697   1.11697
...

But what do I do if the data is already in an OHLC format? From what I can gather the OHLC method of the API calculates an OHLC slice for every column, hence if my data is in the format:

             ctime  openbid  highbid   lowbid  closebid
0       1443654000  1.11700  1.11700  1.11687   1.11697
1       1443654060  1.11700  1.11712  1.11697   1.11697
2       1443654120  1.11701  1.11708  1.11699   1.11708

When I try to re-sample I get an OHLC for each of the columns, like so:

                     openbid                             highbid           \
                        open     high      low    close     open     high   
ctime                                                                       
2015-09-30 23:00:00  1.11700  1.11700  1.11700  1.11700  1.11700  1.11712   
2015-09-30 23:01:00  1.11701  1.11701  1.11701  1.11701  1.11708  1.11708 
...
                                        lowbid                             \
                         low    close     open     high      low    close   
ctime                                                                       
2015-09-30 23:00:00  1.11700  1.11712  1.11687  1.11697  1.11687  1.11697   
2015-09-30 23:01:00  1.11708  1.11708  1.11699  1.11699  1.11699  1.11699  
...

                    closebid                             
                        open     high      low    close  
ctime                                                    
2015-09-30 23:00:00  1.11697  1.11697  1.11697  1.11697  
2015-09-30 23:01:00  1.11708  1.11708  1.11708  1.11708

Is there a quick(ish) workaround for this that someone is willing to share please, without me having to get knee-deep in pandas manual?

Thanks.

ps, there is this answer - Converting OHLC stock data into a different timeframe with python and pandas - but it was 4 years ago, so I am hoping there has been some progress.

chrisb · Accepted Answer · 2016-03-25T16:19:28.647

82

This is similar to the answer you linked, but it a little cleaner, and faster, because it uses the optimized aggregations, rather than lambdas.

Note that the resample(...).agg(...) syntax requires pandas version 0.18.0.

In [101]: df.resample('1H').agg({'openbid': 'first', 
                                 'highbid': 'max', 
                                 'lowbid': 'min', 
                                 'closebid': 'last'})
Out[101]: 
                      lowbid  highbid  closebid  openbid
ctime                                                   
2015-09-30 23:00:00  1.11687  1.11712   1.11708    1.117

edited Mar 25 '16 at 16:19

answered Mar 25 '16 at 15:52

chrisb

49,833
8
70
70

Yeah I will go with that one; it means updating pandas, but the way my function was going means that is the preferable option. Thanks. – user3439187 Mar 25 '16 at 16:03
1

Any idea if I could add the `fill_method='bfill'` method into that solution to deal with the NAN's? – user3439187 Mar 25 '16 at 16:13
Forget that last question, the method has changed to .bfill() – user3439187 Mar 25 '16 at 16:18
Here's an alternative if you run into error with ctime index above: df = df.set_index('datetime') – Lionel May 02 '17 at 16:06
1

I had to use these parameters to match it with my charting platform `df.resample('1H', closed = 'right',label = 'right').agg({'open': 'first', 'high': 'max', 'low': 'min', 'close': 'last'})` – gibbz00 Aug 03 '18 at 03:24

Benjamin Crouzier · Answer 2 · 2019-10-18T09:24:05.483

13

You need to use an OrderedDict to keep row order in the newer versions of pandas, like so:

import pandas as pd
from collections import OrderedDict

df['ctime'] = pd.to_datetime(df['ctime'], unit='s')
df = df.set_index('ctime')
df = df.resample('5Min').agg(
    OrderedDict([
        ('open', 'first'),
        ('high', 'max'),
        ('low', 'min'),
        ('close', 'last'),
        ('volume', 'sum'),
    ])
)

edited Oct 18 '19 at 09:24

answered Mar 31 '18 at 20:02

Benjamin Crouzier

40,265
44
171
236

score 5 · Answer 3 · answered Sep 02 '19 at 04:25

Given a dataframe with price and amount columns

def agg_ohlcv(x):
    arr = x['price'].values
    names = {
        'low': min(arr) if len(arr) > 0 else np.nan,
        'high': max(arr) if len(arr) > 0 else np.nan,
        'open': arr[0] if len(arr) > 0 else np.nan,
        'close': arr[-1] if len(arr) > 0 else np.nan,
        'volume': sum(x['amount'].values) if len(x['amount'].values) > 0 else 0,
    }
    return pd.Series(names)

df = df.resample('1min').apply(agg_ohlcv)
df = df.ffill()

score 1 · Answer 4 · answered Dec 23 '19 at 10:34

1

Converstion from OHLC to OHLC for me worked like this:

df.resample('1H').agg({
    'openbid':'first',
    'highbid':'max',
    'lowbid':'min',
    'closebid':'last'
})

answered Dec 23 '19 at 10:34

Datalker

356
3
8

score 0 · Answer 5 · answered Sep 07 '19 at 07:49

0

This one seems to work,

def ohlcVolume(x):
    if len(x):
        ohlc={ "open":x["open"][0],"high":max(x["high"]),"low":min(x["low"]),"close":x["close"][-1],"volume":sum(x["volume"])}
        return pd.Series(ohlc)

daily=df.resample('1D').apply(ohlcVolume)

answered Sep 07 '19 at 07:49

Sivakumar D

69
4

Please explain. What's up with the Volume len()? – not2qubit Dec 09 '20 at 09:45

Pandas OHLC aggregation on OHLC data

5 Answers5

Linked