Cleanse
Clean up messy values before you compare or analyze them. Trim text, fix capitals and round decimals. Settings apply across columns, and you can override them for a single column.
Clean names and amounts
Manual Input has four rows with name and amount. Cleanse starts with Trim Whitespace, Collapse Whitespace and Title case on, so the padded, unevenly spaced ada lovelace becomes Ada Lovelace.
Cleanse 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.
| name | amount |
|---|---|
| Ada Lovelace | 12.346 |
| Lin Chen | 8.764 |
| 5.432 | |
| Sam! |
- Expand Decimal Columns, select Round Values and set Round Decimals to
2. The amounts become 12.35, 8.76, 5.43 and NULL. - Under Text Columns, select Remove Punctuation.
Sam!becomesSam; the other names stay unchanged. - Expand Null Values and select Remove Null Rows. Only Ada Lovelace and Lin Chen remain, because the other two rows have a missing name or amount.
When to use
Use Cleanse before comparing or grouping values, when stray spaces, inconsistent capitals or extra decimals would make equal values look different.
Read the output
The output keeps all four rows and both columns. The first name becomes Ada Lovelace, the second becomes Lin Chen, the missing name stays NULL and Sam! keeps its punctuation. The amounts don't change until you turn on Round Values.
Rounding to two decimal places produces 12.35, 8.76 and 5.43. The missing amount stays NULL. Removing null rows then leaves only the first two complete rows.
Cleanse changes values in place. It doesn't add separate cleaned columns. The null-row and null-column checks use the input values, so text that becomes an empty string after cleanup doesn't count as NULL.
To replace missing values rather than remove rows, use Impute.
To extract text using a specific pattern, use RegEx.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
Settings apply to columns of a matching type. A new block changes nothing until you turn on an operation.
- Trim Whitespace
Remove leading and trailing spaces from text.
- Collapse Whitespace
Replace runs of whitespace within text with a single space.
- Remove Letters
Remove ASCII letters A-Z and a-z from text.
- Remove Digits
Remove digits 0-9 from text. Numeric columns are unaffected.
- Remove Punctuation
Remove characters outside ASCII letters, digits, underscores and whitespace. This can also remove accented letters and symbols.
- Modify Case
Choose None, Upper, Lower or Title. Title capitalizes the first character of each space-separated word and lowercases the rest. Default: None.
- Round Values
Enable rounding for decimal columns. Integer columns remain unchanged.
- Round Decimals
Choose the number of decimal places when rounding is enabled. Use 0 for whole-number values; this doesn't cast the column to an integer type.
- Truncate To Date
Convert timestamp columns to DATE, removing the time of day.
- Remove Null Rows
Drop a row if any affected column contains NULL. The global setting applies to every column unless overridden.
- Remove Null Columns
Drop affected columns whose input values are all NULL. A column with even one non-NULL value remains.
- Column Overrides
Expand Per-Column Overrides and then a column to override an individual setting. Unset settings inherit the global options above; an explicit override takes precedence.
Cleanse FAQs
- Does trimming turn empty text into NULL?
- No. Text with only spaces becomes an empty string. Remove Null Rows checks for NULL, so it keeps a row whose cleaned text is empty.
- Can I clean only one column?
- Yes. Leave the global operation off and turn it on in that column's overrides. You can also turn off a global operation for one column.
- In what order are text operations applied?
- Trim, collapse whitespace, remove letters, remove digits, remove punctuation, then change case. There is no second trim after character removal.
- Does Cleanse parse numbers stored as text?
- No. Rounding only applies to decimal number columns. Use Number first to turn formatted text into numbers.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.