How to fill zeroes of a day with the previous day values in Pandas

user_663413 0
Viewed 207

I have some days with complete zeroes and would like to replace them with the previous day values as shown here.

Input

       count
2020-02-01 00:00:00     12
2020-02-01 00:01:00     3
2020-02-01 00:02:00     14
2020-02-01 00:03:00     0
2020-02-01 00:04:00     22
2020-02-02 00:00:00     0
2020-02-02 00:01:00     0
2020-02-02 00:02:00     0
2020-02-02 00:03:00     0
2020-02-02 00:04:00     0
2020-02-03 00:00:00     2
2020-02-03 00:01:00     4
2020-02-03 00:02:00     1
2020-02-03 00:03:00     0
2020-02-03 00:04:00     22

Output

        count
2020-02-01 00:00:00     12
2020-02-01 00:01:00     3
2020-02-01 00:02:00     14
2020-02-01 00:03:00     0
2020-02-01 00:04:00     22
2020-02-02 00:00:00     12
2020-02-02 00:01:00     3
2020-02-02 00:02:00     14
2020-02-02 00:03:00     0
2020-02-02 00:04:00     22
2020-02-03 00:00:00     2
2020-02-03 00:01:00     4
2020-02-03 00:02:00     1
2020-02-03 00:03:00     0
2020-02-03 00:04:00     22

I was trying something like this but couldn't solve it.

df = df.fillna(0)
df = df.reset_index()

df['Date'] = df['index'].dt.date
df['Time'] = df['index'].dt.time


df.set_index(pd.to_datetime(df.Date + ' ' + df.Time), inplace=True)
for ind in df[df.count.eq(0)].index:
    df.loc[ind, 'count'] = df.loc[ind - pd.Timedelta('1D'), 'count']
df.reset_index(drop=True, inplace=True)
pandas python
1 Answers

you can use mask to replace the 0s with nan, then groupby the time in the DatetimeIndex and ffill, then fillna with 0 to complete the time where no value before.

df_ = (df.mask(df.eq(0))
         .groupby(df.index.time)
         .ffill() #add the parameter limit=1 if you want to fill only one day after
         .fillna(0)
      )
print (df_)
                     count
2020-02-01 00:00:00   12.0
2020-02-01 00:01:00    3.0
2020-02-01 00:02:00   14.0
2020-02-01 00:03:00    0.0
2020-02-01 00:04:00   22.0
2020-02-02 00:00:00   12.0
2020-02-02 00:01:00    3.0
2020-02-02 00:02:00   14.0
2020-02-02 00:03:00    0.0
2020-02-02 00:04:00   22.0
2020-02-03 00:00:00    2.0
2020-02-03 00:01:00    4.0
2020-02-03 00:02:00    1.0
2020-02-03 00:03:00    0.0
2020-02-03 00:04:00   22.0

If you want to fill with previous values ONLY if all values of the day are 0, then in mask above, change df.eq(0) by df['count'].eq(0).groupby(df.index.date).transform('all'). In this case it does not change the result.

If you want to fill with the average of the same time until the current time, then you can use expanding like:

(df.mask(df.eq(0))
   .groupby(df.index.time)
   .expanding().mean()
   .fillna(0)
   .reset_index(level=0, drop=True).sort_index()
)
user_92747320
Related