Interpolate date in pandas

user_11539856 0
Viewed 224

I have a data frame with a date column. There are almost 7k rows and 10 of them are NaN. So I wanted to interpolate the date. I checked out the documentation and they used .interpolate(). However, when I tried that, I was not getting the desired result.

One sample row:

0     November 1, 2019
1          May 1, 2017
2                  NaN
3    December 15, 2017
4        March 9, 2018

My approach:

main_df['date_added'].interpolate(method='linear', inplace=True)

When I viewed the rows, they remain NaN.

Is there a way to fill that date? I have 10 such cases in the data frame.

Thank you in advance.

pandas dataframe nan interpolation python
1 Answers

You don't get to do usual arithmetic operators on datetime type, e.g. multiplication/division. So you don't get to interpolate the dates linearly. One option is to convert the dates into float by subtracting a time stamp, then dividing by a period:

first_date=pd.to_datetime('1900-01-01')
periods = pd.Timedelta('1s')

(df['date'].sub(first_date).div(periods)
  .interpolate(method='linear')
  .mul(periods).add(first_date)
)

Output:

0   2019-11-01
1   2017-05-01
2   2017-08-23
3   2017-12-15
4   2018-03-09
Name: date, dtype: datetime64[ns]
user_42384080
Related