Documentation

    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.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    stepvalue
    110
    220
    330
    1. Change Method for value to Forward fill. The missing middle value becomes 10, copied from the previous value.
    2. Change Method to Backward fill. The gap becomes 30, copied from the next value.
    3. Change Method back to Linear and select Separate Columns. value keeps its original NULL while value_interpolated contains 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.