Pandas interpolation enhancement request : specifying the maximum gap to interpolate.

[delie0512]

Currently, Pandas interpolation interpolates all gaps, regardless of there size and the limit parameter is used to limit the number of replacement : if there is a gap of 3 values and limit=2, pandas replaces the first 2 values.

I have difficulty understanding why someone would want to do interpolation on only a few missing items in a consecutive series of missing.

Personally, depending on the length of the gap, I would like to decide to interpolate the whole gap or none of it. For example, in an hourly time-series, interpolation of missing hours up to a maximum of 3 consecutive hours:
gaps <= 3 would be interpolated
gaps > 3 remain untouched.

I would appreciated a option for interpolation such as R "na.approx" in which a "maxgap" parameters is available. maxgap: maximum number of consecutive NAs to fill. Any longer gaps will be left unchanged.

[jreback]

you can do something this. Not sure how useful this is generally as IIRC this is first request about this type of filling

In [79]: s = Series([0]+[np.nan]*2+[3,4,5]+[np.nan]*4+[10],index=pd.date_range('20130101',periods=11,freq='T'))

In [80]: s
Out[80]: 
2013-01-01 00:00:00     0
2013-01-01 00:01:00   NaN
2013-01-01 00:02:00   NaN
2013-01-01 00:03:00     3
2013-01-01 00:04:00     4
2013-01-01 00:05:00     5
2013-01-01 00:06:00   NaN
2013-01-01 00:07:00   NaN
2013-01-01 00:08:00   NaN
2013-01-01 00:09:00   NaN
2013-01-01 00:10:00    10
Freq: T, dtype: float64

In [81]: indexer = s.groupby(pd.Grouper(freq='3T')).filter(lambda x: x.isnull().sum()==len(x))

In [82]: indexer
Out[82]: 
2013-01-01 00:06:00   NaN
2013-01-01 00:07:00   NaN
2013-01-01 00:08:00   NaN
Freq: T, dtype: float64

In [83]: s2 = s.interpolate()                   

In [84]: s.interpolate().loc[indexer.index]
Out[84]: 
2013-01-01 00:06:00    6
2013-01-01 00:07:00    7
2013-01-01 00:08:00    8
Freq: T, dtype: float64

In [86]: s2 = s.copy()

In [87]: s2.loc[indexer.index] = s.interpolate()

In [88]: s2
Out[88]: 
2013-01-01 00:00:00     0
2013-01-01 00:01:00   NaN
2013-01-01 00:02:00   NaN
2013-01-01 00:03:00     3
2013-01-01 00:04:00     4
2013-01-01 00:05:00     5
2013-01-01 00:06:00     6
2013-01-01 00:07:00     7
2013-01-01 00:08:00     8
2013-01-01 00:09:00   NaN
2013-01-01 00:10:00    10
Freq: T, dtype: float64

In [89]: s.resample('3T',how='sum')
Out[89]: 
2013-01-01 00:00:00     0
2013-01-01 00:03:00    12
2013-01-01 00:06:00   NaN
2013-01-01 00:09:00    10
Freq: 3T, dtype: float64

In [90]: s2.resample('3T',how='sum')
Out[90]: 
2013-01-01 00:00:00     0
2013-01-01 00:03:00    12
2013-01-01 00:06:00    21
2013-01-01 00:09:00    10
Freq: 3T, dtype: float64

[delie0512]

Based on the original values of "s" above, the results that I looking for "interpolating gaps up to 3 hours" in a hourly time-series would be as follows. The first gap (2 values) is interpolated, but for the second gap ( 4 values) no interpolation is performed at all. I could write code to do what I want, but I would prefer using an existing tool for which the user can specific the maximum gap to interpolate (maxgap).

2013-01-01 00:00:00     0
2013-01-01 00:01:00     1 interpolated, since gap = =2 <=maxgap
2013-01-01 00:02:00     2 interpolated, since gap = =2 <=maxgap
2013-01-01 00:03:00     3
2013-01-01 00:04:00     4
2013-01-01 00:05:00     5
2013-01-01 00:06:00   NaN   not interpolated, since gap = 4 > maxgap
2013-01-01 00:07:00   NaN   not interpolated, since gap = 4 > maxgap
2013-01-01 00:08:00   NaN   not interpolated, since gap = 4 > maxgap
2013-01-01 00:09:00   NaN   not interpolated, since gap = 4 > maxgap
2013-01-01 00:10:00    10
Freq: T, dtype: float64

[kawochen]

this is a bit like consolidating fillna and interpolate

[tomchor]

I've actually had to do something like this before. See this answer to check out how it was achieved.

I can actually say this is pretty useful. I know a number of quality-control techniques that use this kind of reasoning: if gap < maxgap do nothing, do something else otherwise (or vice-versa).

I would recommend something like passing an extra maxgap and mingap keywords to interpolate, that control maximum number of consecutive gaps to consider when interpolating and the minimum. The OP's original example would become something like

interpolated = df.interpolate(maxgap=3, mingap=None)

@delie0512 @jreback

[jreback]

yeah iterative grouping is usefule, see the last example here: http://pandas.pydata.org/pandas-docs/stable/cookbook.html#grouping

I suppose if someone wanted to contribute a maxgaps would take it.