Filling missing dates by imputing on previous dates in Python

Question

I have a time series that I want to lag and predict on for future data one year ahead that looks like:

Date           Energy         Pred Energy      Lag Error        
.
2017-09-01       9                 8.4
2017-10-01       10                9                
2017-11-01       11                10
2017-12-01       12                11.5
2018-01-01        1                1.3
NaT                                           (pred-true)
NaT
NaT
NaT
.
.

All I want to do is impute dates into the NaT entries to continue from 2018-01-01 to 2019-01-01 (just fill them like we're in Excel drag and drop) because there are enough NaT positions to fill up to that point.

I've tried model['Date'].fillna() with various methods and either just repeats the same previous date or drops things I don't want to drop.

Any way to just fill these NaTs with 1 month increments like the previous data?

score 0 · Answer 1 · answered Nov 07 '18 at 20:11

Make the df and set the index (there are better ways to set the index):

"""
Date,Energy,Pred Energy,Lag Error        
2017-09-01,9,8.4
2017-10-01,10,9                
2017-11-01,11,10
2017-12-01,12,11.5
2018-01-01,1,1.3
"""
import pandas as pd

df = pd.read_clipboard(sep=",", parse_dates=True)
df.set_index(pd.DatetimeIndex(df['Date']), inplace=True)
df.drop("Date", axis=1, inplace=True)
df

Reindex to a new date_range:

idx = pd.date_range(start='2017-09-01', end='2019-01-01', freq='MS')
df = df.reindex(idx)

Output:

            Energy  Pred Energy  Lag Error        
2017-09-01     9.0          8.4                NaN
2017-10-01    10.0          9.0                NaN
2017-11-01    11.0         10.0                NaN
2017-12-01    12.0         11.5                NaN
2018-01-01     1.0          1.3                NaN
2018-02-01     NaN          NaN                NaN
2018-03-01     NaN          NaN                NaN
2018-04-01     NaN          NaN                NaN
2018-05-01     NaN          NaN                NaN
2018-06-01     NaN          NaN                NaN
2018-07-01     NaN          NaN                NaN
2018-08-01     NaN          NaN                NaN
2018-09-01     NaN          NaN                NaN
2018-10-01     NaN          NaN                NaN
2018-11-01     NaN          NaN                NaN
2018-12-01     NaN          NaN                NaN
2019-01-01     NaN          NaN                NaN

Help from: Pandas Set DatetimeIndex

Filling missing dates by imputing on previous dates in Python

1 Answers1