Data Cleansing

Omniscope custom block · Preparation

Every routine clean-up step in one block: empty rows, placeholders, stray spaces, invisible characters, case and number text, with a report of exactly what changed.

Python · pandas · nothing extra to install

The problem it solves

Exported data is rarely clean. Names arrive as * jane SMITH *, cities as LEEDS and *leeds *, missing values as N/A, NULL or a dash, and copy-pasted text carries non-breaking and zero-width spaces that break joins and filters without being visible.

Each fix is easy on its own, but doing them all means a chain of blocks and formulas per field. Data Cleansing is a single checklist, with extras such as invisible-character removal, name-aware Title Case and number conversion.

• 14 → 12 rows (empty rows removed)
• 68 values cleaned in one pass
• 1 text field converted to numbers
• 1 empty legacy field dropped

How it works

• Choose the fields to cleanse, or leave the list empty for all of them; tick the steps you want.
• Placeholders you list (such as NULL, N/A, -) become real missing values first, so the later steps and the number conversion treat them correctly.
• Title Case respects names: o'brien becomes O'Brien, anne-marie becomes Anne-Marie, and deliberate mixed case such as MacLeod is kept.
• Number conversion only happens when every value in a field is a number (currency symbols and thousands separators allowed); fields with leading zeros, such as phone numbers, stay text.
• The second output is an audit trail: per field, how many values changed, how many placeholders became missing and how many gaps were filled.

What goes in

A messy customer contact export

Stray and repeated spaces, inconsistent case, N/A / NULL / - placeholders, a non-breaking space, a zero-width space, two empty rows and an empty legacy field.

14 rows in the sample file.

The options

What comes out

Cleansed data

Orders is now a whole-number field; Phone stays text because of its leading zeros.

Cleansing report

Cleansing report for the demo: how many values each step touched, per field.

Where it pays off

Marketing: Campaign-ready contact lists

Clean names, emails and towns before a mail-merge or CRM import, so personalisation looks right and segment filters catch everyone.

Finance: Reliable joins on ledger and bank data

Invisible spaces and inconsistent case are a common reason reconciliations fail to match. Cleanse both sides first and the join keys line up.

Retail and supply chain: Product and supplier masters

Standardise descriptions and codes from several suppliers' files, turn price text such as £1,250.50 into numbers and drop empty columns from template exports.

Public sector and healthcare: Survey and form returns

Normalise free-text answers and placeholder values before analysis, with the report as evidence of what was changed for data-governance reviews.

Use cases describe typical scenarios and are illustrative; the sample data is synthetic.

Why do this in Omniscope

• Cleaned before anything is matched on it. The checklist sits between the input block and the joins or Fuzzy Match that follow, so each new export is cleaned the same way before a join key is compared.
• A count of what each step touched. The Cleansing report is rebuilt from the source on every run (68 values in the demo), and a text field only becomes a number when every value in it is one, so phone numbers with leading zeros stay text.
• Placeholders and patterns you can extend. The placeholder list and the fields to Title Case are options; the invisible-character and number patterns sit at the top of the block's Python script, ready for a character your source system produces.

How it fits into an Omniscope workflow

File / Database input → Data Cleansing → Fuzzy Match → Report

Put Data Cleansing straight after an input block and before joins, de-duplication or reporting. It pairs naturally with Fuzzy Match: clean first, then match.

Route the Cleansing report output to a Report tab or a Data table so each refresh leaves an audit trail of how much cleaning the source needed.

In one step

Removes empty rows and fields, replaces missing values, strips unwanted characters and changes case together. Note that Omniscope's File and Text Input blocks already read common placeholders such as N/A as missing.

Try it

1. Open the demo project (demo.iox on the demo server, or import demo.ioz from this folder): the raw Customer Contacts have stray spaces, LEEDS vs leeds, N/A and NULL, an empty legacy column.
2. Some problems cannot be seen: a non-breaking space and a zero-width space that would break a join on email.
3. Open the Data Cleansing block: one checklist, plus placeholders, invisible characters and number conversion.
4. Run it and look at the cleansed output: proper-case names including O'Brien and MacLeod, consistent towns, Orders now a number field.
5. Look at the Cleansing report output: per field, how many values changed. That is the audit trail.
6. A natural next step is to feed the clean data into Fuzzy Match to find duplicate customers.

Running in Omniscope

The demo project's workflow: the sample data, the block, and a Validate data check on its output.

Language and libraries

Python. It uses pandas, plus the Python standard library (re).

pandas and numpy are part of Omniscope's Docker Python image, and the block was run in both host mode and Docker mode on an Omniscope server, with the same results. It needs nothing else installed.

Tested

Run on an Omniscope server, the demo workflow finished without errors (12 records in Cleansed data and 10 in Cleansing report). Automated tests also cover normal data, empty input, wrong types and large input.