Python Pandas -- Forward filling entire rows with value of one previous column

2024/10/6 16:21:20

New to pandas development. How do I forward fill a DataFrame with the value contained in one previously seen column?

Self-contained 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 yields 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 that 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

So that the previously-seen value in 'Close' forward fills entire rows until there's a new populated row seen. It's simple enough to fill column 'Close' like so:

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 across the 03:14:00 and 03:16:00 rows with the 'Close' value of those rows? And is there a way to do it in one step using one forward fill instead of filling the 'Close' column first?

Answer

It seems you need assign with ffill and then bfill per row by axis=1, but necessary full NaNs rows:

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 is same as:

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
https://en.xdnf.cn/q/70345.html

Related Q&A

Microsoft Visual C++ 14.0 is required - error - pip install fbprophet

I am trying pip install fbprophet. I am getting that error: "Microsoft Visual C++ 14.0 is required" It has been discussed many times (e.g. Microsoft Visual C++ 14.0 is required (Unable to fin…

find position of item in for loop over a sequence [duplicate]

This question already has answers here:Closed 12 years ago.Possible Duplicate:Accessing the index in Python for loops list = [1,2,2,3,5,5,6,7]for item in mylist:...How can I find the index of the item…

How to convert BeautifulSoup.ResultSet to string

So I parsed a html page with .findAll (BeautifulSoup) to variable named result. If I type result in Python shell then press Enter, I see normal text as expected, but as I wanted to postprocess this res…

How can I manually place networkx nodes using the mouse?

I have a fairly large and messy network of nodes that I wish to display as neatly as possible. This is how its currently being displayed:First, I tried playing with the layout to see if it could genera…

How to create a vertical scroll bar with Plotly?

I would like to create a vertical scroll for a line chart in Plotly. For visualisation, the vertical scroll is something depicted in the figure below.Assume, we have 6 line chart as below, then how ca…

Django 1.7 makemigrations renaming tables to None

I had to move a few models from one app to another, and I followed the instructions on this answer https://stackoverflow.com/a/26472482/188614. Basically I used the CreateModel migrations generated by …

TypeError on CORS for flask-restful

While trying the new CORS feature on flask-restful, I found out that the decorator can be only applied if the function returns a string. For example, modifying the Quickstart example:class HelloWorld(r…

struct.error: unpack requires a string argument of length 16

While processing a PDF file (2.pdf) with pdfminer (pdf2txt.py) I received the following error:pdf2txt.py 2.pdf Traceback (most recent call last):File "/usr/local/bin/pdf2txt.py", line 115, in…

SELECT EXISTS vs. LIMIT 1

I see SELECT EXISTS used a lot like:if db.query("""SELECT EXISTS (SELECT 1 FROM checkoutWHERE checkout_id = %s)""" % checkout_id).getresult()[0][0] == t:vs. what i prefer:…

How to access a specific start_url in a Scrapy CrawlSpider?

Im using Scrapy, in particular Scrapys CrawlSpider class to scrape web links which contain certain keywords. I have a pretty long start_urls list which gets its entries from a SQLite database which is …