Data Cleansing Transforms in Data Preparation

Data Cleansing transforms standardize and sanitize datasets by removing inconsistencies and duplicates, preparing data for analysis.

The Data Cleansing transforms help standardize, clean, and sanitize datasets by removing or correcting unwanted values, duplicates, and inconsistencies. These transforms ensure the dataset is ready for analysis, modeling, or reporting.

Best Situations to Use

• Remove invalid, empty, or negative values from columns.

• Fill missing values to ensure dataset completeness.

• Flag or remove duplicate values or rows for data integrity.

• Remove letters, numbers, special characters, or whitespace from columns for consistency.

• Prepare data for feature engineering, modeling, or visualization by cleaning metadata and content.

Clear Cells on Matching Value

The Clear Cells on Matching Value transform clears the contents of cells that match a specified condition or value in a column.

Best Situations to Use

• Remove specific unwanted or placeholder values from columns (e.g., zeros, default entries).

• Standardize columns before applying other transformations or analytics.

Steps

1. Select a column in the Data Grid.

2. Navigate to Transforms > Data Cleansing > Clear Cells on Matching Value.

3. Specify the following:

   • Operator: equals, starts with, ends with, or regex.

   • Value: The value or pattern to search for.

4. Click Submit.

• Cells matching the specified condition are cleared.

Example: Cells with values 0 or 7 in the Quantity column are cleared, resulting in blank cells.

Delete Rows on Matching Value

The Delete Rows on Matching Value transform removes entire rows that match a specified condition in a column.

Best Situations to Use

• Remove invalid or unwanted rows (e.g., placeholders, test data).

• Prepare a dataset for analysis by eliminating rows that do not meet criteria.

Steps

1. Select a column in the Data Grid.

2. Navigate to Transforms > Data Cleansing > Delete Rows on Matching Value.

3. Specify the following:

   • Operator: contains, equals, starts with, ends with, regex.

   • Value: The value or pattern to search for.

4. Click Submit.

• Rows matching the condition are deleted.

Example: Row with a value 5 in the Sl. No. column is removed.

Delete Rows with Empty Cell

Removes rows containing empty cells in a selected column.

Best Situations to Use

• Eliminate incomplete rows from datasets.

• Ensure datasets are clean for aggregation, reporting, or modeling.

Steps

1. Select a column.

2. Navigate to Transforms > Data Cleansing > Delete Rows with Empty Cell.

3. Click the transform name to apply.

• All rows with empty cells in the selected column are removed.

Note: No configuration form is required; the transform applies immediately.

Delete Rows with Invalid Cell

Removes rows containing invalid or inconsistent data in a column.

Best Situations to Use

• Filter out corrupted or non-conforming values.

• Prepare high-quality datasets for analysis or modeling.

Steps

1. Select a column.

2. Navigate to Transforms > Data Cleansing > Delete Rows with Invalid Cell.

3. Click the transform name to apply.

• Rows with invalid values are deleted.

Note: No configuration is required; the transform applies immediately.

Delete Rows with Negative Values

Removes rows containing negative numeric values in a selected column.

Best Situations to Use

• Remove invalid negative values from datasets where only positive numbers are meaningful (e.g., counts, salaries).

• Prepare data for analytics, aggregation, or modeling.

Steps

1. Select a column with numeric values.

2. Navigate to Transforms > Data Cleansing > Delete Rows with Negative Values.

3. Click the transform name to apply.

• Rows with negative values are removed.

Example: Two rows with negative values in the id column are deleted.

Flag Duplicates in Columns

The Flag Duplicates in Columns transform adds a Boolean column indicating duplicate values in a column. Original values are flagged as false, duplicates as true.

Best Situations to Use

• Identify repeated entries in a single column.

• Prepare datasets for cleaning or validation.

Steps

1. Select a column.

2. Navigate to Transforms > Data Cleansing > Flag Duplicates in Columns.

3. Click Submit.

• A new Boolean column is added, marking duplicates as true.

Flag Duplicates in Table

The Flag Duplicates in Table transform adds a Boolean column marking duplicate rows in the table. Original rows are flagged as false, duplicates as true.

Best Situations to Use

• Identify duplicate rows in a dataset.

• Maintain data integrity before analysis or reporting.

Steps

1. Select a column to check for duplicates.

2. Navigate to Transforms > Data Cleansing > Flag Duplicates in Table.

3. Click Submit.

• A new column named with the prefix flag_ flags duplicate rows as true.

Remove Duplicates from Column

Removes duplicate values from selected column(s). Can be applied to single or multiple columns.

Best Situations to Use

• Clean up repeated entries in a column.

• Prepare columns for aggregation or grouping operations.

Steps

1. Select a column.

2. Navigate to Transforms > Data Cleansing > Remove Duplicates from Column.

3. Click Submit.

• Duplicate values are removed from the selected column.

Remove Duplicates from Table

Removes duplicate rows from the dataset entirely.

Best Situations to Use

• Clean datasets with identical rows to ensure uniqueness.

• Prepare data for modeling, reporting, or aggregation.

Steps

1. Select a column.

2. Navigate to Transforms > Data Cleansing > Remove Duplicates from Table.

3. Click Submit.

• Duplicate rows are removed from the table.

Remove Letters

Removes letters from the selected column, optionally creating a new column with the transformed values.

Best Situations to Use

• Extract numeric-only content from mixed data columns.

• Clean columns for numerical analysis or aggregation.

Steps

1. Select a column.

2. Navigate to Transforms > Data Cleansing > Remove Letters.

3. Enable Create New Column (optional).

4. Click Submit.

• All letters are removed; numbers remain.

Remove Numbers

The Remove Numbers transform removes all numeric characters from a selected column, leaving only letters, spaces, and other text content.

Best Situations to Use

• Clean text columns that accidentally contain numeric values.

• Prepare columns for categorical analysis or text processing.

Steps

1. Select a column containing numeric and text values.

2. Navigate to Transforms > Data Cleansing > Remove Numbers.

3. Enable Create New Column (optional) to preserve the original column.

4. Click Submit.

• All numeric characters are removed from the column.

Remove Special Characters from Metadata

The Remove Special Characters transform cleans column headers by removing punctuation and special characters, retaining only letters, numbers, and spaces.

Best Situations to Use

• Standardize column names for analysis, feature engineering, or reporting.

• Ensure column names are compatible with other modules or APIs.

Steps

1. Select a column or metadata header.

2. Navigate to Transforms > Data Cleansing > Remove Special Characters.

3. Enable Create New Column (optional).

4. Click Submit.

• Special characters are removed; the column name or new column is sanitized.

Remove Trail and Lead Quotes

This transform removes leading and trailing single or double quotes from string columns.

Best Situations to Use

• Clean text data imported from CSV, Excel, or external sources that contain extraneous quotes.

• Prepare text for parsing or analysis without manual editing.

Steps

1. Select a string-type column with leading/trailing quotes.

2. Navigate to Transforms > Data Cleansing > Remove Trail and Lead Quotes.

3. Click Submit.

• Quotes at the start or end of cell values are removed.

Remove Trail and Lead Whitespace

Removes leading and trailing spaces from string columns.

Best Situations to Use

• Standardize text columns for data consistency.

• Prepare string data for joins, comparisons, or analytics.

Steps

1. Select a string-type column with leading/trailing whitespace.

2. Navigate to Transforms > Data Cleansing > Remove Trail and Lead Whitespace.

3. Click Submit.

• Leading and trailing whitespace are removed.

Remove Whitespace

Removes all whitespace from a string, including leading, trailing, and in-between spaces.

Best Situations to Use

• Standardize string values that must not contain spaces (e.g., codes, IDs).

• Clean data for feature engineering or text-based operations.

Steps

1. Select a string-type column.

2. Navigate to Transforms > Data Cleansing > Remove Whitespace.

3. Enable Create New Column (optional).

4. Provide a New Column Name.

5. Click Submit.

• All whitespace is removed; the output is either in a new column or overwrites the source column.

Notes:

• If Create New Column is unchecked, the original column is updated in place.

• This transform ensures consistency for text comparisons, joins, or key generation.

Notes:

• Use Clear Cells or Delete Rows to remove invalid or unwanted data.

• Use Fill transforms to handle missing or incomplete data.

• Flag Duplicates helps identify repeated values before removal.

• Removing letters, numbers, or special characters standardizes column content.

• Whitespace and quotes removal improves string consistency for processing or joins.

• Always verify the dataset after cleansing to ensure transformations meet requirements.