CODEWITHSUNDEEP
pythonpython-3.xpandasdataframeimputation
mlenthusiast
mlenthusiast

Reputation: 1204

Filling time series column values with last known value

I have a Pandas time series dataframe like this:

id .. .. ..(some cols) 1/1/20 1/2/20 1/3/20 1/4/20 1/5/20 1/6/20 ....
1                      10     20     0      40     0      50
2                      10     30     30     0      0      50
.
.

I want to ffill the 0s in the columns with the last known value to get something like this:

id .. .. ..(some cols) 1/1/20 1/2/20 1/3/20 1/4/20 1/5/20 1/6/20 ....
1                      10     20     20     40     40      50
2                      10     30     30     30     30      50
.
.

Assuming there are some other columns between id and the time series columns, how do I ffill the dataframe like this? I know something like df.ffill(axis = 1) works for Null values, but I wasn't able to find anything to modify it to work with 0.

Upvotes: 0

Views: 365

Answers (2)

Mazziotti Raffaele
Mazziotti Raffaele

Reputation: 411

Maybe my solution can be a bit naive, but you can convert your values of interest to nan and then use fillna method. Just like this:

import pandas as pd
import numpy as np

df = pd.DataFrame(dict(col1=[1,2,3,4,5],col2=[3,0,3,5,0],col3=[0,2,0,0,1]))
df[df==0]=np.nan
df.fillna(method='ffill',axis=1)

Upvotes: 1

BENY
BENY

Reputation: 323376

You can do ffill with mask and update

df.update(df.filter(like='/').mask(lambda x : x==0).ffill(1))

Upvotes: 1

Related Questions