conditionally use pandas df.fillna(method=bfill)

Question

Forward fill and back fill are handy, but I'm struggling to apply it conditionally or to a specific filtered area of my data frame.  See the follow snip for the input and desired output.

Notice, I am focusing in on ONLY columns unit:cid and rows with 8000 (salmon region).  Also, notice how item b969 does not backfill nor do any 9000 or 7000 rows.   The red numbers are the desired new values.

I've tinkered around with filters, np.where, setting inplace=True, and of course axis=1...all without luck.  I end up with errors or unexpected results.

Following are the input data.

hoc	item	category	unit	fid	cid
bbca	a954	9000	NaN	NaN	400
ccd	b858	9000	NaN	NaN	NaN
aba	b868b	8000	NaN	NaN	350
NaN	b969	8000	NaN	7	NaN
aba	a55	7000	NaN	NaN	450
ccd	c1	7000	NaN	8	NaN

Copy the data above to your clipboard and use pd.read_clipboard() and you should be off and running with the input dataframe.

Much Appreciated.

Tyler

jcarlson · Accepted Answer

For applying to a specific known area, the loc method is probably the best.

df.loc[2:3,['unit', 'fid', 'cid']].fillna(method='bfill', axis=1)

To apply this conditionally, simply provide a boolean test for the rows. For instance, here's me backfilling all of the rows that are not category 8000:

df.loc[df.category != 8000, ['unit', 'fid', 'cid']].fillna(method='bfill', axis=1)