Pandas Dataframe Resample OHLC has wrong open price

Question

I am unable to generate "accurate" OHLC data from tick data using pandas DataFrame.resample() ver 0.18. Specifically I get a "future leak" at the open price of those bars where the price continues across an interval.

Reproduceable Example:

import pandas as pd
import numpy as np

np.random.seed(1234)
size=1000
df = pd.DataFrame(1.26 + np.random.rand(size)/100.0,index=pd.date_range('20160101 09:00:00',periods=size,freq='7s'),columns=['price'])
dfohlc = df.price.resample('1min').ohlc().ffill()

This is the series of randomly generate price ticks:

2016-01-01 09:00:00  1.261915
2016-01-01 09:00:07  1.266221
2016-01-01 09:00:14  1.264377
2016-01-01 09:00:21  1.267854
2016-01-01 09:00:28  1.267800
2016-01-01 09:00:35  1.262726
2016-01-01 09:00:42  1.262765
2016-01-01 09:00:49  1.268019
2016-01-01 09:00:56  1.269581   << this is the market price until 09.01:03
2016-01-01 09:01:03  1.268759   << this price should not be the open price at 09:01
...

The resample() generates these bars:

2016-01-01 09:00:00  1.261915  1.269581  1.261915  1.269581
2016-01-01 09:01:00  1.268759  1.268759  1.260138  1.260138
...

The open and close prices for the 1st OHLC bar at 09:00 are corrrect.

However the 2nd OHLC bar at 09:01:00 has the "wrong" open price 1.268759. This price would not be visible until 09:01:03. The "correct" price at the open was still 1.269581.

Is there any way to get the resample() function to generate the "correct" open price?

Answer 1

Interesting...

It appears the behavior is to group on time and take the first value in that grouped period as the open.

I imagine it makes sense to use the prior close as the opening bar, except for market open perhaps.

An easy fix to take the prior close as open is df['open'] = df['close'].shift().

If you had a flag to indicate the opening bar of the market and you wish to use the first observed price as the open, then this would work:

df.loc[df['market_open_flag'] == False, 'open'] = df['close'].shift()

I'm not sure what the default behavior would be if there are no observed trades, e.g. illiquid stocks.