In pandas, why does interpolate() on sensor readings with an irregular DatetimeIndex give different values from interpolate(method='time'), and what do limit, limit_direction and limit_area control?
answer
- the default ignores the index
- rows treated as equally spaced
- weight by elapsed time
- forward direction keeps leading gaps
- inside versus outside the readings
basics
~20 sThe default method='linear' ignores the index and treats rows as equally spaced; method='time' weights estimates by elapsed time on a DatetimeIndex. limit caps consecutive fills, limit_direction picks which ends fill, and limit_area restricts filling to inside or outside the readings.
solid answer
~40 s`interpolate()` defaults to `method='linear'`, which the docstring says ignores the index and treats values as equally spaced, so a reading one minute after 10.0 and fifty-nine minutes before 20.0 becomes 15.0. `method='time'` uses the timestamps and gives about 10.17; it requires a `DatetimeIndex` and raises `ValueError` otherwise, and `'index'` or `'values'` do the same for a numeric index. `limit` is the maximum number of consecutive gaps filled, so a long outage is only partly filled from its start. `limit_direction` defaults to `'forward'`, which leaves leading gaps alone and holds the last value flat over trailing ones; `'both'` fills both ends. `limit_area='inside'` fills only between valid readings. In pandas 3.0 a `str` or `object` column raises `TypeError`, so interpolate the numeric columns only.
code
python · 22 linesimport numpy as np
import pandas as pd
stamps = pd.to_datetime(
["2026-03-01 00:00", "2026-03-01 00:01", "2026-03-01 01:00"]
)
temp = pd.Series([10.0, np.nan, 20.0], index=stamps)
print(temp.interpolate().iloc[1]) # 15.0: rows treated as equally spaced
print(round(temp.interpolate(method="time").iloc[1], 4)) # 10.1667: weighted by elapsed time
frame = pd.DataFrame(
{"temp_c": [10.0, np.nan, 20.0], "site": ["north", None, "north"]},
index=stamps,
)
try:
frame.interpolate(method="time")
except TypeError as err:
print(err)
numeric = frame.select_dtypes("number").interpolate(method="time")
print(numeric["temp_c"].round(4).tolist())go deeper
Recall that interpolate estimates missing values from their neighbours and that method='time' exists for timestamped data.
Explain that the default linear method ignores the index, and what limit, limit_direction and limit_area each decide about leading, inside and trailing gaps.
Diagnose a smooth line drawn across a multi-hour outage, explain why limit truncates rather than skips, and interpolate only numeric columns under pandas 3.0.
Weigh whether interpolated readings should ever reach downstream reports unmarked, and require a flag column recording which values were estimated.
## What interpolate does `Series.interpolate()` and `DataFrame.interpolate()` replace missing values with **estimates computed from the valid values around them**, instead of copying a neighbour as `ffill()` does. The signature in pandas 3.0 is `interpolate(method='linear', *, axis=0, limit=None, inplace=False, limit_direction=None, limit_area=None, **kwargs)`. The result has the same shape as the input. ## Why the default disagrees with method='time' The default **`method='linear'`** does not look at the index at all. The docstring is explicit: it ignores the index and treats the values as **equally spaced**. Internally pandas replaces the index with positions 0, 1, 2 before interpolating. That is right for a regular one-minute series and wrong for an irregular one. Take readings of 10.0 at 00:00, a gap at 00:01, and 20.0 at 01:00: - `method='linear'` sees three equally spaced rows and puts **15.0** in the middle. - `method='time'` sees that the gap sits one minute into a sixty-minute interval and puts **about 10.17** there. The methods that use the index: | `method` | Uses | Requirement | |---|---|---| | `'linear'` (default) | row positions only | none | | `'time'` | elapsed time between timestamps | a `DatetimeIndex`, else `ValueError` | | `'index'`, `'values'` | the numeric values of the index | a numeric or datetime index | | `'polynomial'`, `'spline'` | the numeric index, through SciPy | SciPy installed and an `order` argument | On a DataFrame the estimate is computed **column by column** along the index (the default `axis=0`), so each sensor column is interpolated from its own neighbours; a column without gaps is returned unchanged. The SciPy-backed methods such as `'polynomial'` or `'spline'` need SciPy to be installed and fail at call time when it is not. Two further constraints: only `'linear'` works on a `MultiIndex`, and an index that itself contains missing values raises `NotImplementedError`. ## limit, limit_direction and limit_area These three arguments decide **which** gaps get filled. - **`limit`** is the maximum number of **consecutive** missing values to fill. A gap longer than `limit` is only **partly** filled: in the default forward direction its first `limit` rows receive estimates along the line towards the far reading, and the rest stay `NaN`. - **`limit_direction`** is `'forward'`, `'backward'` or `'both'`. With `'forward'` (the default for linear and time) leading gaps before the first valid value are kept as `NaN`; with `'backward'` trailing gaps are kept; with `'both'` both ends can be filled. - **`limit_area`** is `None`, `'inside'` or `'outside'`. `'inside'` fills only gaps surrounded by valid values; `'outside'` fills only the leading and trailing gaps. A behaviour that surprises people: with the default forward direction, **trailing** gaps after the last reading are filled with that last reading, a flat line, because there is no later point to interpolate towards. Pass `limit_area='inside'` when values beyond the last real reading must stay missing. ## The long-outage trap For IoT temperatures with dropouts of a few minutes and one outage of several hours, a plain `interpolate(method='time')` draws a straight line across the whole outage. The chart looks smooth and every hour of the outage now has plausible readings that were never measured. Two points to make in a senior answer: 1. `limit` does not solve this on its own, because it fills the **first** rows of the long gap rather than skipping it. Leaving long gaps empty needs a mask built from the length of each run of gaps. 2. `limit` counts **rows**, not time. On an irregular index, five rows can be five seconds or five hours, so derive the threshold from the timestamps when spacing varies. ## Dtypes and the pandas 3.0 change Interpolation needs numbers or datetimes. pandas 2.1 deprecated interpolating `object` data and pandas 3.0 enforces it: an `object` column raises `TypeError`. Because pandas 3.0 also infers text as the new `str` dtype, a frame-wide `df.interpolate()` on a table with a site or device column raises `TypeError` ("Cannot interpolate with str dtype"). Select the numeric columns first, for example with `df.select_dtypes('number').interpolate(method='time')`. pandas 3.0 also rejects `method='ffill'`, `'pad'`, `'bfill'` and `'backfill'`; those fills belong to `ffill()` and `bfill()`. ## Boundaries Filling the empty bins created by upsampling with `resample` belongs with resampling, and interpolating inside each device group belongs with group transforms. Whether a linear estimate is defensible for a given measurement is a modelling decision, not something `interpolate` can answer.
- Why does interpolate(limit=1) still fill the last row of a series that ends with a gap?In the default forward direction the trailing gap is one row long and sits after the last valid value, so it is within the limit and receives that last value, a flat extrapolation. To keep trailing rows empty, pass `limit_area='inside'`, which fills only gaps that have valid readings on both sides.
- What happens if you call interpolate(method='time') on a Series with a default RangeIndex?It raises `ValueError` saying time-weighted interpolation only works with a `DatetimeIndex`. Set the timestamp column as the index first with `set_index`, or use `method='index'` when the index is numeric and its values carry the spacing.
- When is the default linear method perfectly adequate for time series?When the rows are equally spaced in time, such as a complete one-minute grid. Then positions and timestamps are proportional, so `'linear'` and `'time'` give the same estimates and the faster default is fine.
- Assuming the default interpolate() uses the timestamps in the index.
- Believing limit skips gaps longer than limit instead of filling their start.
- Expecting interpolate() to leave trailing gaps empty by default.
- Expecting interpolate() to skip a text column in a pandas 3.0 frame.
- Treating limit as a time span on an irregular DatetimeIndex.
Assumes pandas 3.0: interpolating object or str columns raises TypeError (deprecated for object in 2.1), and method='ffill' or 'pad' is no longer accepted.