Forward Fill Pandas Dataframe Horizontally (along rows) without forward filling last value in each row

Question

I have a Pandas dataframe that I want to forward fill HORIZONTALLY but I don't want to forward fill past the last entry in each row. This is time series pricing data on products where some have been discontinued so I dont want the last value recorded to be forward filled to current.

FWDFILL.apply(lambda series: series.iloc[:,series.last_valid_index()].ffill(axis=1))

^The code I have included does what I want but it does it VERTICALLY. This could maybe help people as a starting point.

>>> print(FWDFILL)

1      1     NaN     NaN     2     NaN  
2     NaN     1      NaN     5     NaN  
3     NaN     3       1     NaN    NaN  
4     NaN    NaN     NaN    NaN    NaN  
5     NaN     5      NaN    NaN     1  

Desired Output:

1      1      1      1     2     NaN  
2     NaN     1      1     5     NaN  
3     NaN     3      1    NaN    NaN  
4     NaN    NaN    NaN   NaN    NaN  
5     NaN     5      5     5      1

Answer 1

IIUC, you need to apply with axis=1, so you are applying to dataframe rows instead of dataframe columns.

df.apply(lambda x: x[:x.last_valid_index()].ffill(), axis=1)

Output:

     1    2    3    4    5
0                         
1  1.0  1.0  1.0  2.0  NaN
2  NaN  1.0  1.0  5.0  NaN
3  NaN  3.0  1.0  NaN  NaN
4  NaN  NaN  NaN  NaN  NaN
5  NaN  5.0  5.0  5.0  1.0

Answer 2

Usage of bfill and ffill

s1=df.ffill(1)
s2=df.bfill(1)
df=df.mask(s1.notnull()&s2.notnull(),s1)
df
Out[222]: 
     1    2    3    4    5
1  1.0  1.0  1.0  2.0  NaN
2  NaN  1.0  1.0  5.0  NaN
3  NaN  3.0  1.0  NaN  NaN
4  NaN  NaN  NaN  NaN  NaN
5  NaN  5.0  5.0  5.0  1.0

Or just using interpolate

df.mask(df.interpolate(axis=1,limit_area='inside').notnull(),df.ffill(1))
Out[226]: 
     1    2    3    4    5
1  1.0  1.0  1.0  2.0  NaN
2  NaN  1.0  1.0  5.0  NaN
3  NaN  3.0  1.0  NaN  NaN
4  NaN  NaN  NaN  NaN  NaN
5  NaN  5.0  5.0  5.0  1.0

Answer 3

You can use numpy to find the last valid indices and mask your ffill. This allows you to use the vectorized ffill and then a vectorized mask.

---

u = df.values
m = (~np.isnan(u)).cumsum(1).argmax(1)
df.ffill(1).mask(np.arange(df.shape[0]) > m[:, None])

     0    1    2    3    4
0  1.0  1.0  1.0  2.0  NaN
1  NaN  1.0  1.0  5.0  NaN
2  NaN  3.0  1.0  NaN  NaN
3  NaN  NaN  NaN  NaN  NaN
4  NaN  5.0  5.0  5.0  1.0

---

Info

>>> np.arange(df.shape[0]) > m[:, None]
array([[False, False, False, False,  True],
       [False, False, False, False,  True],
       [False, False, False,  True,  True],
       [False,  True,  True,  True,  True],
       [False, False, False, False, False]])

Answer 4

Little modification to - Most efficient way to forward-fill NaN values in numpy array's solution, solves it here -

def ffillrows_stoplast(arr):
    # Identical to earlier solution of forward-filling
    mask = np.isnan(arr)
    idx = np.where(~mask,np.arange(mask.shape[1]),0)
    idx_acc = np.maximum.accumulate(idx,axis=1)
    out = arr[np.arange(idx.shape[0])[:,None], idx_acc]

    # Perform flipped index accumulation to get trailing NaNs mask and
    # accordingly assign NaNs there
    out[np.maximum.accumulate(idx[:,::-1],axis=1)[:,::-1]==0] = np.nan
    return out

Sample run -

In [121]: df
Out[121]: 
     A    B    C    D    E
1  1.0  NaN  NaN  2.0  NaN
2  NaN  1.0  NaN  5.0  NaN
3  NaN  3.0  1.0  NaN  NaN
4  NaN  NaN  NaN  NaN  NaN
5  NaN  5.0  NaN  NaN  1.0

In [122]: out = ffillrows_stoplast(df.to_numpy())

In [123]: pd.DataFrame(out,columns=df.columns,index=df.index)
Out[123]: 
     A    B    C    D    E
1  1.0  1.0  1.0  2.0  NaN
2  NaN  1.0  1.0  5.0  NaN
3  NaN  3.0  1.0  NaN  NaN
4  NaN  NaN  NaN  NaN  NaN
5  NaN  5.0  5.0  5.0  1.0

Answer 5

I think of using where on ffill to flip back to NaN those got ignored on bfill

df.ffill(1).where(df.bfill(1).notna())    

Out[1623]:
     a    b    c    d    e
1  1.0  1.0  1.0  2.0  NaN
2  NaN  1.0  1.0  5.0  NaN
3  NaN  3.0  1.0  NaN  NaN
4  NaN  NaN  NaN  NaN  NaN
5  NaN  5.0  5.0  5.0  1.0