Interpolation
Fill gaps in an ordered sequence using neighboring values, such as a missing measurement between two observations. Sort the rows first, then choose a fill method for each column.
Fill a gap in a sequence
Manual Input has three steps out of order. Sort puts step 1, 2 and 3 in order, with values 10, NULL and 30. Interpolation applies Linear to value, filling the middle gap with 20.
Interpolation interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| step | value |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
- Change Method for
valuetoForward fill. The missing middle value becomes 10, copied from the previous value. - Change Method to
Backward fill. The gap becomes 30, copied from the next value. - Change Method back to
Linearand select Separate Columns.valuekeeps its original NULL whilevalue_interpolatedcontains 20.
When to use
Use Interpolation to fill gaps in ordered data, such as a missing reading between two measurements. Sort the rows first. To fill with a fixed value or an average, use Impute.
Read the output
After sorting, step contains 1, 2 and 3. Linear interpolation produces 10, 20 and 30 in value. The middle row is halfway between the two observations, so its estimate is halfway between 10 and 30.
The Sort block is needed here because the example rows arrive in step order 3, 1, 2. Without sorting, the missing value would be at the end and Linear would copy the nearest known value, 10.
With Separate Columns, value keeps the missing cell and value_interpolated shows the estimate alongside it. Leave the step column's Method as None so only the measurement is filled.
Other numeric methods
Polynomial fits a polynomial using up to five nearby known observations. Spline uses a natural cubic spline through the known observations. With only two known points, both give the same middle value as Linear in this example; more observations can produce different curves.
To establish the sequence before filling gaps, use Sort.
To fill with a fixed value or a column statistic, use Impute.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
Choose a method per column after sorting the rows. A chosen column missing from the input is skipped with a warning, and its settings are kept.
- Method
None keeps a column unchanged. Forward fill uses the nearest earlier non-NULL value; Backward fill uses the nearest later one. Linear fills numeric gaps proportionally between surrounding values. Polynomial and Spline fit curves to numeric observations. Default: None.
- Interpolate all
Choose Linear for numeric columns and Forward fill for text, dates and timestamps. Other types remain unchanged.
- Clear all
Set every column's method to None.
- Separate Columns
Keep the source columns and append results ending in _interpolated. When cleared, replace the selected columns. Default: false.
- Add Flag
Append a column ending in _interpolated_flag for each selected column. True marks an originally NULL cell, even if it couldn't be filled. Default: false.
Interpolation FAQs
- Does it use the distances between timestamps or step numbers?
- No. Numeric interpolation uses row positions as equally spaced points. A timestamp or step column can set the sort order, but the gaps between its values aren't used in the calculation.
- What happens at the beginning or end?
- Linear, Polynomial and Spline copy the nearest known value outside the observed range. Forward fill leaves leading gaps NULL; Backward fill leaves trailing gaps NULL.
- What if every value is missing?
- There's no value to copy or interpolate from, so the values stay NULL.
- Does interpolation change existing values?
- It fills NULL cells and keeps existing values. Numeric methods produce decimal output, so an integer column becomes a floating-point column. All rows stay.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.