Impute
Fill missing values with a fixed value or a statistic such as the column mean. Incomplete rows stay in your table. You can also keep the original values and flag the cells chosen for replacement.
Fill a missing value
Manual Input has one value column containing 10, 20, 30 and NULL. Impute starts with Replace What set to Null and replaces the missing value with 0.
Impute 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.
| value |
|---|
| 10 |
| 20 |
| 30 |
| 0 |
- Change Replace With from
ValuetoMeanfor thevaluecolumn. The missing value becomes 20, the mean of 10, 20 and 30. - Select Separate Columns. The original
valuecolumn keeps its NULL, whilevalue_imputedcontains the filled result. - Select Add Flag.
value_imputed_flagis true for the row that was missing and false for the other three.
When to use
Use Impute when rows with missing values should stay in the table and a fixed value or an average is a fair replacement. To fill gaps from neighboring rows in ordered data, use Interpolation.
Read the output
The initial output contains 10, 20, 30 and 0. Only the missing value changes. Switch to Mean and it becomes 20 instead. All four rows stay.
With Separate Columns, value keeps the original values and value_imputed has the results. Add Flag marks the row that was missing, so you can still find it after it's filled.
Every replacement is an assumption about the missing data. Use a fixed value only when that default makes sense, and Mean when the column average is a fair stand-in.
To fill gaps using neighboring rows in an ordered sequence, use Interpolation.
To separate incomplete rows for inspection, use Filter.
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 replacement method for each column and which cells it replaces. If a chosen column isn't in the input, it's skipped with a warning and its settings are kept.
- Replace With
None leaves a column unchanged. Mean and Median summarize numeric values; Mode uses the most frequent value. Earliest and Latest apply to dates and timestamps. Value uses a literal you supply. Default: None.
- Value
Shown in the column's detail for the Value method. Enter the replacement using the column's type, such as 0 for a number. An incompatible value causes an error.
- Impute all
Choose Mean for numeric columns, Mode for text and Earliest for dates and timestamps. Other types remain unchanged.
- Clear all
Set every column to None and clear fixed replacement values.
- Replace What
Null matches missing values. Null or empty also matches empty strings. Specific value matches the text representation of the value you enter. Default: Null.
- Match Value
For Specific value, enter the value to replace, such as -1 when it represents a missing measurement.
- Separate Columns
Keep the originals and append results named with _imputed. When cleared, replace values in the original columns. Default: false.
- Add Flag
Append a column ending in _imputed_flag for each selected column. It records whether the original cell matched Replace What, even if no replacement could be calculated. Default: false.
Impute FAQs
- Does Mean use values filled earlier in the column?
- No. Statistics use the column's original non-NULL values across the whole input. With Specific value selected, that value still counts toward the statistic unless it's NULL.
- What if the entire column is missing?
- A statistic has no non-NULL values to use, so the result stays NULL. A compatible fixed Value can still fill those cells.
- Are empty strings and spaces the same as NULL?
- No. Null or empty includes empty strings but doesn't include strings containing only spaces.
- Does row order affect the result?
- Mean, Median, Earliest, Latest and fixed Value don't depend on neighboring rows. Mode picks one of the most frequent values. When values tie, don't rely on which one it picks.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.