Clean Missing Values

On this Page

Overview

The Clean Missing Values is a Transform type Snap that is used to process any missing values in an incoming dataset by dropping or imputing (replacing) values. The Clean Missing Values Snap supports the following four approaches to process missing values:

  • Drop Row
  • Impute with Average
  • Impute with Popular
  • Impute with Custom Value

Input and Output

Expected input

  • First input view: A document containing the missing values.
  • Second input view: A document containing data statistics computed by the Profile Snap.

The Clean Missing Values Snap processes the data in streams, while the Profile Snap consumes all the data before it derives any statistics. Therefore, while using the Profile Snap, we recommend the following:

  • Build a Pipeline with the Profile Snap to generate data statistics that are stored in JSON format using the JSON Formatter and File Writer Snaps.
  • Use the File Reader and JSON Parser Snaps to read the statistics from the Profile Snap and feed the output data into the Clean Missing Values Snap.

Expected output: A document where all missing values have been processed.

Expected upstream Snaps

  • First input view: A Snap that has a document output view. For example, Mapper, CSV Generator, or Categorical to Numeric.
  • Second input view: A sequence of File Reader and JSON Parser Snaps. These Snaps read the data statistics computed by the Profile Snap in another Pipeline. It is required to select Value distribution in the Profile Snap and set Top value limit according to the number of unique values; or set to 0, which means unlimited.

Expected downstream Snaps: A Snap that has a document input view. For example, Mapper, JSON Formatter, or Type Converter.

Prerequisites

None

Configuring Accounts

Accounts are not used with this Snap.

Configuring Views

Input

This Snap has exactly two document input views, the Data input view and the Profile input view.

OutputThis Snap has exactly one document output view.
ErrorThis Snap has at most one document error view.

Troubleshooting

None

Limitations and Known Issues

None

Modes

Snap Settings


Label

Required. The name for the Snap. You can modify this to be specific, especially if you have more than one of the same Snap in your pipeline.

Policy

Specifies the preferences for fields, missing values, and handling methods. For each policy, select the field with missing values, the handling method, the custom value for Impute with Custom Value, and the missing value to be handled.

Field

The field that contains missing values. This is a suggestible field that suggests all the fields in a dataset.

Default value: None

Rule

The handling method for missing values in the selected field. The available options are:

  • Drop Row: The Snap discards all rows that contain the missing values.
  • Impute with Average: This is applicable only to numeric fields. The Snap replaces missing values with the average computed by the Profile Snap. In the second input view, the Snap requires the data statistics computed by the Profile Snap.
  • Impute with Popular: The Snap replaces all missing values with the most popular value. In the second input view, the Snap requires the data statistics computed by the Profile Snap.
  • Impute with Custom Value: The Snap replaces all missing values with the value you specify in Custom valueIn case of an ambiguous data type, you must specify Custom value type.

Default value: Drop Row

Custom value

The value the Snap uses to replace missing values when you select Impute with Custom Value option.

Default value: None

Custom value type

The type of value that the Snap must use to replace missing values when you select Impute with Custom Value option. The available options are:

  • Auto
  • Integer
  • Floating Point
  • String
  • Date Time

Default value: Auto

Missing value (value to be replaced)

Specify the missing value here. This option is available only when the field has specific missing values. For example, fields that contain "N/A", "None", "Unknown", "-1", etc as the missing value.

Default value: None

Snap Execution

Select one of the following three modes in which the Snap executes:

  • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.

  • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.

  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Default ValueExecute only
Example: Validate & Execute


Example


Handling Missing Values in a Dataset

The pipeline in this example, Clean_Missing_Values_Popular_Value.slp, demonstrates how you can use the Clean Missing Values Snap to process missing values in a dataset with its most popular value.

In this example:

  1. We input a CSV file with some missing values.
  2. We process the smaller dataset in the first input view of the Clean Missing Values Snap.
  3. We process the larger dataset along with data statistics in the second input view. Profile Snap is used to get the data statistics of the input.

Download this pipeline.

 Understanding the pipeline

The input is generated by the CSV Generator Snap. The Type Converter Snap is used downstream of the CSV Generator Snap to automatically convert data types of the values. The output from the Type Converter Snap is then passed into the Copy Snap. The Copy Snap is configured to generate two streams of the input documents. One stream is fed to the Clean Missing Values Snap, and the other is fed to the Profile Snap. Below is an output preview of the Copy Snap, this is the data input for the Clean Missing Values Snap.

One of the documents does not have its corresponding value in the $Category field.

For smaller datasets it is easy to identify if there are any values missing. For large datasets it is easier to identify missing values using the Profile Snap. Since the Clean Missing Values Snap also requires the data statistics of the input when selecting Impute with Popular or Impute with Average in the Rule property, using a Profile Snap is always helpful. In this case, the $popular field in the Profile Snap's output is used. 

The Profile Snap is configured as shown below:

Based on its configuration, the Profile Snap has the following output:

This output denotes that the input documents have one missing value in the $Category field, and the most popular value is Publishing. The Clean Missing Values Snap uses this value to handle the missing value in the document. 

The output from the Copy Snap (data) and the output from the Profile Snap (statistics) serve as the input for the Clean Missing Values Snap. This Snap is configured as shown below:

Based on the Snap's configuration, missing values in the $Category field are handled by the Snap using Impute with Popular as the rule. The Snap treats absence of values, null, and whitespaces as missing values. 

The Snap uses the value of the $popular field in the Profile Snap's output to handle missing values in the $Category field. The output of the Snap is as shown below:

You may write the output from the Clean Missing Values Snap into another file using the File Writer Snap. 

Download this pipeline.

Downloads

Important steps to successfully reuse Pipelines

  1. Download and import the pipeline into the SnapLogic application.
  2. Configure Snap accounts as applicable.
  3. Provide pipeline parameters as applicable.

  File Modified

File Clean_Missing_Values_Popular_Value.slp

Nov 21, 2018 by Mohammed Iqbal

Snap Pack History

 Click to view/expand
Release Snap Pack VersionDateType  Updates

August 2024

main27765

 

Stable

  • Upgraded the org.json.json library from v20090211 to v20240303, which is fully backward compatible.
  • Enhanced the Date Time Extractor Snap to support Date time formats (YYYY-MM-dd HH:mm:ss and YYYY-MM-dd HH:mm:ss.SSS) and allow the root path to auto-convert all fields.

May 2024main26341 StableUpdated and certified against the current SnapLogic Platform release.
February 2024436patches25781 Latest

Enhanced the Deduplicate Snap to honor an interrupt while waiting in the delay loop to manage the memory efficiently.

February 2024main25112 StableUpdated and certified against the current SnapLogic Platform release.
November 2023main23721Nov 8, 2023StableUpdated and certified against the current SnapLogic Platform release.

August 2023

main22460

Aug 16, 2023

Stable

Updated and certified against the current SnapLogic Platform release.

May 2023433patches21572 Latest

The Deduplicate Snap now manages memory efficiently and eliminates out-of-memory crashes using the following fields:

  • Minimum memory (MB)

  • Minimum free disk space (MB)

May 2023433patches21247 Latest

Fixed an issue with the Match Snap where a null pointer exception was thrown when the second input view had fewer records than the first.

May 2023

main21015 

Stable

Upgraded with the latest SnapLogic Platform release.

February 2023main19844 StableUpgraded with the latest SnapLogic Platform release.
December 2022431patches19268 Latest

The Deduplicate Snap now ignores fields with empty strings and whitespaces as no data.

November 2022main18944
 
Stable

Upgraded with the latest SnapLogic Platform release.

August 2022main17386
 
Stable

Upgraded with the latest SnapLogic Platform release.

4.29main15993
 
StableUpgraded with the latest SnapLogic Platform release.
4.28main14627
 
Stable

Enhanced the Type Converter Snap with the Fail safe upon execution checkbox. Select this checkbox to enable the Snap to convert data with valid data types, while ignoring invalid data types.

4.27427patches13730

Enhanced the Type Converter Snap with the Fail safe upon execution checkbox. Select this checkbox to enable the Snap to ignore invalid data types and convert data with valid data types.

4.27427patches13948
 
Latest

Fixed an issue with the Principal Component Analysis Snap, where a deadlock occurred when data is loaded from both the input views.

4.27main12833
 
StableUpgraded with the latest SnapLogic Platform release.
4.26main11181
 
StableUpgraded with the latest SnapLogic Platform release.
4.25425patches10994
 

Fixed an issue when the Deduplicate Snap where the Snap breaks when running on a locale that does not format decimals with Period (.) character. 

4.25main9554
 
StableUpgraded with the latest SnapLogic Platform release.
4.24main8556
 
StableUpgraded with the latest SnapLogic Platform release.
4.23main7430
 
StableUpgraded with the latest SnapLogic Platform release.
4.22main6403
 
StableUpgraded with the latest SnapLogic Platform release.
4.21snapsmrc542
 
Stable
  • Introduces the Mask Snap that enables you to hide sensitive information in your dataset before exporting the dataset for analytics or writing the dataset to a target file.
  • Enhances the Match Snap to add a new field, Match all, which matches one record from the first input with multiple records in the second input. Also, enhances the Comparator field in the Snap by adding one more option, Exact, which identifies and classifies a match as either an exact match or not a match at all.
  • Enhances the Deduplicate Snap to add a new field, Group ID, which includes the Group ID for each record in the output. Also, enhances the Comparator field in the Snap by adding one more option, Exact, which identifies and classifies a match as either an exact match or not a match at all.
  • Enhances the Sample Snap by adding a second output view which displays data that is not in the first output. Also, a new algorithm type, Linear Split, which enables you to split the dataset based on the pass-through percentage.
4.20 Patchmldatapreparation8771
 
Latest

Removes the unused jcc-optional dependency from the ML Data Preparation Snap Pack.

4.20snapsmrc535
 
StableUpgraded with the latest SnapLogic Platform release.
4.19snapsmrc528
 
Stable

New Snap: Introducing the Deduplicate Snap. Use this Snap to remove duplicate records from input documents. When you use multiple matching criteria to deduplicate your data, it is evaluated using each criterion separately, and then aggregated to give the final result.

4.18snapsmrc523
 
StableUpgraded with the latest SnapLogic Platform release.
4.17 PatchALL7402
 
Latest

Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers.

4.17snapsmrc515
 
Latest
  • New Snap: Introducing the Feature Synthesis Snap, which automatically creates features out of multiple datasets that share a one-to-one or one-to-many relationship with each other.
  • New Snap: Introducing the Match Snap, which enables you to automatically identify matched records across datasets that do not have a common key field.
  • Added the Snap Execution field to all Standard-mode Snaps. In some Snaps, this field replaces the existing Execute during preview check box.
4.16snapsmrc508
 
Stable

Added a new Snap, Principal Component Analysis, which enables you to perform principal component analysis (PCA) on numeric fields (columns) to reduce dimensions of the dataset.

4.15snapsmrc500
 
Stable
  • New Snap Pack. Perform preparatory operations on datasets such as data type transformation, data cleanup, sampling, shuffling, and scaling. Snaps in this Snap Pack are: 
    • Categorical to Numeric
    • Clean Missing Values
    • Date Time Extractor
    • Numeric to Categorical
    • Sample
    • Scale
    • Shuffle
    • Type Converter