How to resample ohlc data properly in pandas / custom fill method per column

Question

I have got OHLC data with missing time frames. Suppose I have the following pandas dataframe denoted by the variable df:

                     Open     High     Low      Close
2019-04-19 00:00:00  0.67068  0.67123  0.67064  0.67123
2019-04-19 00:02:00  0.67062  0.67425  0.67060  0.67223

Now, I resample that pandas dataframe to fill the missing gap and I get the following:

df = df.resample('T').ffill()

                     Open     High     Low      Close
2019-04-19 00:00:00  0.67068  0.67123  0.67064  0.67123
2019-04-19 00:01:00  0.67068  0.67123  0.67064  0.67123
2019-04-19 00:02:00  0.67062  0.67425  0.67060  0.67223

From the above, we can see that the missing gap (00:01:00) is filled with the help of ffill(). However, the data in that row (row starting with 00:01:00) is not displayed properly as the opening price should be the same as the closing price of the previous row (row starting with 00:00:00). Likewise, the closing price of that row (row starting with 00:01:00) should be the same as the opening price of the next row (row starting with 00:02:00). The desired output should look like this:

                     Open     High     Low      Close
2019-04-19 00:00:00  0.67068  0.67123  0.67064  0.67123
2019-04-19 00:01:00  0.67123  0.67123  0.67064  0.67062
2019-04-19 00:02:00  0.67062  0.67425  0.67060  0.67223

How would I resolve this problem in pandas?

Did you expect `.ffill()` to magically interpret your data and think that closing values should be the opening values of another row? You need a bit more effort for that than just `ffill()`. And what do you want to do if you are missing more than 1 minute? — Niels Henkens, Jan 27 '22 at 12:45
yes you are right. Do yo have an idea how to solve this problem in an elegant manner? — freak11, Jan 27 '22 at 13:38

score 3 · Answer 1 · edited Jun 03 '22 at 23:44

The answer by @mozway is the right direction, but it has two issues:

It will not work if there are more than one consecutive rows of missing values,
The filling of "High" and "Low" are not logical in the OHLC context.

The below is what I would do (after resampling):

Cross-fill the first missing row

result = df.copy()
result['open'] = df['open'].fillna(df['close'].ffill(),limit=1)
result['close'] = df['close'].fillna(df['open'].bfill(),limit=1)

Now, propagate equal values of open and close through the rest of the rows:

result['open'] = result['open'].fillna(result['close'].ffill())
result['close'] = result['close'].fillna(result['close'].ffill())

Finally, set high and low logically as min and max of open and close values:

result['high'] = result['high'].fillna(result[['open','close']].max(axis=1))
result['low'] = result['low'].fillna((result[['open','close']].min(axis=1)))

This method will work in situations with multiple consecutive missing rows and also treats missing values for 'high' and 'low' logically.

mozway · Accepted Answer · 2022-01-27T13:45:35.917

Unfortunately, you can't directly specify a fill method per column.

A workaround would be not to fill the values during the resampling but to do it afterwards:

df = df.resample('T').fillna(None)

df['Open'], df['Close'] = (df['Open'].fillna(df['Close'].ffill()),
                           df['Close'].fillna(df['Open'].bfill()))

df = df.ffill()

output:

                        Open     High      Low    Close
2019-04-19 00:00:00  0.67068  0.67123  0.67064  0.67123
2019-04-19 00:01:00  0.67123  0.67123  0.67064  0.67062
2019-04-19 00:02:00  0.67062  0.67425  0.67060  0.67223

previous answer (incorrect for OHLC but interesting as generalization)

(df.resample('T')
   .fillna(None)
   .assign(Close=lambda d: d['Close'].bfill())  # bfill for Close
   .ffill()                                     # ffill for others
)

output:

                        Open     High      Low    Close
2019-04-19 00:00:00  0.67068  0.67123  0.67064  0.67123
2019-04-19 00:01:00  0.67068  0.67123  0.67064  0.67223
2019-04-19 00:02:00  0.67062  0.67425  0.67060  0.67223

cross filling the values:

(df.resample('T')
   .fillna(None)
   .assign(Open=lambda d: d['Open'].fillna(d['Close'].ffill())) # Open = last Close
   .ffill()  # ffill the others
)

output:

                        Open     High      Low    Close
2019-04-19 00:00:00  0.67068  0.67123  0.67064  0.67123
2019-04-19 00:01:00  0.67123  0.67123  0.67064  0.67123
2019-04-19 00:02:00  0.67062  0.67425  0.67060  0.67223

more options

Here is another example where we'll interpolate High and leave Low as NaNs:

(df.resample('T')
   .fillna(None)
   .assign(Open=lambda d: d['Open'].ffill(),
           Close=lambda d: d['Close'].bfill(),
           High=lambda d: d['High'].interpolate()
          )
)

output:

                        Open     High      Low    Close
2019-04-19 00:00:00  0.67068  0.67123  0.67064  0.67123
2019-04-19 00:01:00  0.67068  0.67274      NaN  0.67223
2019-04-19 00:02:00  0.67062  0.67425  0.67060  0.67223

I don't think that's what he/she meant. The closing value of one row, should be the opening value of the next row. So for the fill of 00:01:00. The opening value should be 0.67123 and the closing value should be 0.67062 (the opening value of the next available row). — Niels Henkens, Jan 27 '22 at 12:51
@Niels, I see, then it's easy, just cross fill the values. I'll update the answer with an example — mozway, Jan 27 '22 at 12:56

How to resample ohlc data properly in pandas / custom fill method per column

2 Answers2

previous answer (incorrect for OHLC but interesting as generalization)

cross filling the values:

more options