Pandas interpolation replacing NaNs after the last data point, but not before the first data point

Question

When using pandas interpolate() to fill NaN values like this:

In [1]: s = pandas.Series([np.nan, np.nan, 1, np.nan, 3, np.nan, np.nan])

In [2]: s.interpolate()
Out[2]: 
0   NaN
1   NaN
2     1
3     2
4     3
5     3
6     3
dtype: float64

In [3]: pandas.version.version
Out[3]: '0.16.2'

, why does pandas replace the values at index 5 and 6 with 3s, but leave the values at 0 and 1 as is?

Can I change this behavior? I'd like to leave NaN at index 5 and 6.

(Actually, I'd like it to do linearly extrapolate to fill all of 0, 1, 5, and 6, but that's kind of a different question. Bonus points if you answer it too!)

See also https://stackoverflow.com/a/70548250/2641825 pandas interpolate "can limit the interpolation to the inner NaN using limit_area='inside' ". — Paul Rougieux, Apr 19 '23 at 17:43

score 12 · Accepted Answer · answered Jul 12 '16 at 09:40

Internally, interpolate method uses a 'limit' parameter which avoids the filling propagation more than a specific threshold.

>>>df=pd.DataFrame( [0, np.nan, np.nan, np.nan, np.nan,np.nan, 2] )
>>>df
df 
    0
0   0
1 NaN
2 NaN
3 NaN
4 NaN
5 NaN
6   2
>>>df.interpolate(limit=2)
          0
0  0.000000
1  0.333333
2  0.666667
3       NaN
4       NaN
5       NaN
6  2.000000

By default, the limitation is applied in the forward direction. In the backward direction, there is a default limit which is set to zero. This is why your first steps are not filled by method. One can change the direction using the 'limit_direction' parameter.

df.interpolate(limit=2, limit_direction='backward')
          0
0  0.000000
1       NaN
2       NaN
3       NaN
4  1.333333
5  1.666667
6  2.000000

To fill the first steps and the last steps of your dataframe, you can should set a non-zero value for 'limit' and 'limit_direction' to 'both':

>>> df=pd.DataFrame( [ np.nan, np.nan, 0, np.nan, 2, np.nan,8,5,np.nan, np.nan] )
>>> df
    0
0 NaN
1 NaN
2   0
3 NaN
4   2
5 NaN
6   8
7   5
8 NaN
9 NaN
>>> df.interpolate(method='spline', order=1, limit=10, limit_direction='both')
          0
0 -3.807382
1 -2.083581
2  0.000000
3  1.364022
4  2.000000
5  4.811625
6  8.000000
7  5.000000
8  4.937632
9  4.138735

The subject has been discussed here

Do you think there is a difference between using limit_direction = 'both' (with limit = None) and using extrapolation, as done here for instace(https://stackoverflow.com/questions/22491628/extrapolate-values-in-pandas-dataframe) ? — Euler_Salter, Jan 30 '18 at 09:07

score 5 · Answer 2 · answered Jul 10 '15 at 12:05

This interpolate behaviour in pandas looks strange. You can use scipy.interpolate.interp1d instead to produce expected result. For linear extrapolation, a simple function can be written to do this task.

import pandas as pd
import numpy as np
import scipy as sp

s = pd.Series([np.nan, np.nan, 1, np.nan, 3, np.nan, np.nan])

# interpolate using scipy
# ===========================================
s_no_nan = s.dropna()
func = sp.interpolate.interp1d(s_no_nan.index.values, s_no_nan.values, kind='linear', bounds_error=False)
s_interpolated = pd.Series(func(s.index), index=s.index)

Out[107]: 
0   NaN
1   NaN
2     1
3     2
4     3
5   NaN
6   NaN
dtype: float64

# extrapolate using user-defined func
# ===========================================
def my_extrapolate_func(scipy_interpolate_func, new_x):
    x1, x2 = scipy_interpolate_func.x[0], scipy_interpolate_func.x[-1]
    y1, y2 = scipy_interpolate_func.y[0], scipy_interpolate_func.y[-1]
    slope = (y2 - y1) / (x2 - x1)
    return y1 + slope * (new_x - x1)

s_extrapolated = pd.Series(my_extrapolate_func(func, s.index.values), index=s.index)

Out[108]: 
0   -1
1    0
2    1
3    2
4    3
5    4
6    5
dtype: float64

Thanks. I'm still hoping someone will reply with an explanation of what's going on with pandas. It should just be just wrapping scipy... — foobarbecue, Jul 10 '15 at 15:19
wrapping scipy would mean that pandas has a dependency on scipy, which I guess they want to avoid. — Jezzamon, Dec 17 '15 at 01:13
thank you. Recycled your answer here for a slightly different question: https://stackoverflow.com/a/68917511/6366770 — David Erickson, Aug 25 '21 at 05:55

Pandas interpolation replacing NaNs after the last data point, but not before the first data point

2 Answers2

Linked