Python Pandas - whole row overflow with value of one previous column
New in pandas development. How to forward filling the DataFrame with the value contained in one previously seen column?
Standalone example:
import pandas as pd
import numpy as np
O = [1, np.nan, 5, np.nan]
H = [5, np.nan, 5, np.nan]
L = [1, np.nan, 2, np.nan]
C = [5, np.nan, 2, np.nan]
timestamps = ["2017-07-23 03:13:00", "2017-07-23 03:14:00", "2017-07-23 03:15:00", "2017-07-23 03:16:00"]
dict = {'Open': O, 'High': H, 'Low': L, 'Close': C}
df = pd.DataFrame(index=timestamps, data=dict)
ohlc = df[['Open', 'High', 'Low', 'Close']]
This gives the following DataFrame:
print(ohlc)
Open High Low Close
2017-07-23 03:13:00 1.0 5.0 1.0 5.0
2017-07-23 03:14:00 NaN NaN NaN NaN
2017-07-23 03:15:00 5.0 5.0 2.0 2.0
2017-07-23 03:16:00 NaN NaN NaN NaN
I want to go from the last DataFrame to something like this:
Open High Low Close
2017-07-23 03:13:00 1.0 5.0 1.0 5.0
2017-07-23 03:14:00 5.0 5.0 5.0 5.0
2017-07-23 03:15:00 5.0 5.0 2.0 2.0
2017-07-23 03:16:00 2.0 2.0 2.0 2.0
To have the previous value in "Close" move all rows until a new filled row appears. It's easy enough to fill the Close column like this:
column2fill = 'Close'
ohlc[column2fill] = ohlc[column2fill].ffill()
print(ohlc)
Open High Low Close
2017-07-23 03:13:00 1.0 5.0 1.0 5.0
2017-07-23 03:14:00 NaN NaN NaN 5.0
2017-07-23 03:15:00 5.0 5.0 2.0 2.0
2017-07-23 03:16:00 NaN NaN NaN 2.0
But is there a way to fill lines 03:14:00 and 03:16:00 with the Close value of these lines? And is there a way to do it in one step, using a single forward fill instead of filling the Close column first?
source to share
You seem to need assign
c ffill
and then bfill
per line on axis=1
, but you need the full NaN
line:
df = ohlc.assign(Close=ohlc['Close'].ffill()).bfill(axis=1)
print (df)
Open High Low Close
2017-07-23 03:13:00 1.0 5.0 1.0 5.0
2017-07-23 03:14:00 5.0 5.0 5.0 5.0
2017-07-23 03:15:00 5.0 5.0 2.0 2.0
2017-07-23 03:16:00 2.0 2.0 2.0 2.0
What:
ohlc['Close'] = ohlc['Close'].ffill()
df = ohlc.bfill(axis=1)
print (df)
Open High Low Close
2017-07-23 03:13:00 1.0 5.0 1.0 5.0
2017-07-23 03:14:00 5.0 5.0 5.0 5.0
2017-07-23 03:15:00 5.0 5.0 2.0 2.0
2017-07-23 03:16:00 2.0 2.0 2.0 2.0
source to share