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

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.

Comment From: 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

Comment From: 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

Comment From: kawochen

this is a bit like consolidating fillna and interpolate

Comment From: 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

Comment From: 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.

Comment From: AdamRJensen

This seems like a very real issue, as from my perspective the whole point of having the "limit" argument is to avoid filling spaces which are too large, i.e. the assumption for filling does not hold.

Is anyone still looking at this issue?

Another very similar issue is #16457 and on stackoverflow: https://stackoverflow.com/questions/43077166/interpolate-only-if-single-nan/43079055#43079055 https://stackoverflow.com/questions/43082316/mask-only-where-consecutive-nans-exceeds-x#

Comment From: notuntoward

I've had to do this many, many times, and adding this capability would be a good improvement.

The most common use case is when you can justify interpolating short missing data gaps within a signal's temporal autcorrelation range, but cannot justify interpolating long gaps.

Another use case is when data is being upsampled using pd.resample(), which inserts NaNs for timestamps to be interpolated. Say you're upsampling by a factor of three. In this case, you can do the upsample by interpolating across at most 2 consecutive NaNs. Three or more consecutive NaNs indicate the presence of missing data in the original time series; you don't want to interpolate across that.

Comment From: donglai96

Is someone still working on this problem?

Comment From: veenstrajelmer

I read this was already implemented, but as of today this still seems to be not the case. It would be a valuable improvement I think, would it be possible to make this available?

Comment From: jreback

@veenstrajelmer this is an open issue and we would likely take a PR for implementation