Cleansing data

With our data loaded into EasyMorph, filtered to keep only what we need, and explored and profiled to understand it, the next (and crucial) step is to cleanse our data.

What is data cleansing?

Data cleansing is the process of detecting and correcting errors and inaccuracies in our data. This can involve a variety of operations such as removing unwanted characters, fixing decimal separators or deduplicating rows.

Cleansing data before proceeding with further transformations and automations is very important as it saves a lot of trouble down the line. For example, let’s say we’re an online retailer, and want to obtain the total revenue from customer orders. However, some of the customer orders are duplicated, and this error slips through undetected. This makes the total revenue KPI higher than it actually is, which may lead to incorrect business decisions with far-reaching consequences. Therefore, taking special care to ensure our data is accurate and well-formatted is essential.

Finding errors and inaccuracies in our data

EasyMorph is packed with tools and features to help us to spot potential problems in our data which we may need to cleanse.

One of the key features of EasyMorph is that we can see the full data we’re transforming at every step, in real time. This means that we can more easily spot data errors that might need cleansing.

EasyMorph also provides a complete set of data profiling tools, which may reveal underlying issues in our data. For example, we can easily detect if there are text values in a column where all values should be numbers, such as a column for a price or a quantity.

EasyMorph Insights

One of the most helpful tools for identifying potential problems with our data is Insights. When loading data into EasyMorph, it is automatically checked for possible data quality issues. If anything is found, EasyMorph provides insights, which not only alerts you to the potential problems but also allows you to fix many of them with a single click.

Important: Providing dataset wide insights can be intensive when working with large dataset. Therefore by default, automatically calculating full dataset insights is disabled by default. To enable them, turn on the Auto-calculate full dataset insights option in the Application settings found on the top navigation bar on the About tab.

Just some of the Insights EasyMorph provides include:

• Text which contains leading or trailing spaces, or multiple spaces anywhere in the text.
• Values that appear to be numbers but which are stored as text
• Text containing special characters
• Suspicious duplicate values when the majority of values in the column are unique
• Text that is wrapped in quotes

If there are any potential Issues found, EasyMorph displays a lightbulb icon in two different places:

1. In the table’s header bar when the table is selected - opens the “Dataset Insights” dialog
2. In the column information bar (when a column within a table is selected) - opens Insights in the Column Profiler dialog

The Dataset Insights dialog

When clicking on the lightbulb icon on the header bar of a table, the Dataset Insights dialog appears, listing all columns with one or more insights, as well as how many potential problems were found. Clicking on a column on the left of the dialog will show the details of the insights on the right.

In the insights details, clicking on the Filter button of any suggestion will add an appropriate action to the table to display only the records where the problem exists. Some insights also have a Fix button which when clicked will add the relevant actions to resolve the issue.

In the below example, EasyMorph has found insights for two columns in our data. In the order_total column, numbers are incorrectly stored as text due to the decimal separator being a comma. And in pay_terms, there are repeating and trailing spaces. As you can see, fixing these problems is both quick and simple:

Insights in the column profiler

To check if a particular column has insights, the first step is to select the column in the table. If there are any, we’ll see a lightbulb icon in the column information bar at the top of the workflow canvas:

When we click on the Insights icon, the column profiler dialog opens up, showing the Insights tab with the available insights for the selected column.

As with Insights in the Dataset Insights dialog, clicking on the Filter button of any suggestion will add an appropriate action to the table to filter the data to only the records where the problem exists. As we’ve seen before with “Dataset Insights“, clicking on the Fix button will add the relevant actions to resolve the issue.

Cleansing data with actions

EasyMorph has many actions which are specifically intended for cleansing data. These actions can be divided into two categories:

• Value cleansing - Those which cleanse individual values in the data without changing the number of rows or columns; and
• Row/column cleansing - Those actions that cleanse the structure of the table, by removing problematic rows or columns.

Cleansing actions can be found and added to our workflows in just the same way as any other action.

Actions which cleanse values

Multiple actions in EasyMorph cleanse table values either by removing specific characters or by replacing a cell value.

Cleaning text values

The Sanitize text action removes characters that are sometimes imperceivable but which can still cause problems. These include hidden system characters, leading / trailing / repeating spaces, tabs, line breaks, and HTML/XML tags. You can select which of these problematic characters to remove, and in which columns (or all of them), in a single action. In the following example, we are sanitizing multiple columns at once, removing leading spaces, line breaks and the HTML tag <br>:

Replacing values

In some cases, we need to replace values in a certain column with other values in order to standardize our data. We can achieve this with the Replace action. In the following example, some of our orders have a payment term “In advance”, while others have “Prepaid”, when it means the same thing. All of these should be “Prepaid”, so we replace the incorrect values with this action:

If you’re interested in advanced replacements, such as performing a table-wide replace (i.e. replacing a value across multiple columns) or replacing values based on a lookup table, find more information in the topics for power users section at the bottom of this tutorial.

Actions which remove rows or columns

Apart from fixing specific values, sometimes we find that whole rows or columns of a table need to be removed, either because they are duplicated or empty. We’ve called this structural cleansing, as applying these transformations change the dimensions of the table (rows x columns).

Removing duplicate rows

If there’s something where all us users that manipulate data agree on, is that we hate duplicates. They spoil our data and skew our analytics. The Deduplicate rows action in EasyMorph removes duplicate rows based on values in all columns or selected ones. In the following example, three product ids are duplicated (they have been color-coded in this screenshot for clarity). The “After” table only keeps the first occurrence of a duplicate product id.

Remove empty columns and rows

When we import data to EasyMorph, there may columns or rows that are all empty or whitespace, or columns where all values are the same. The Remove empty columns and Remove empty rows actions take care of deleting them.

In the following example, we first remove all columns that have the same value (in our case, either empty or “#N/A”), and afterwards we remove all empty rows, leaving only the relevant data:

Trim unnecessary rows

In some cases, we may import data into EasyMorph with rows that are not empty but still need to be removed, like an Excel spreadsheet with a totals row at the bottom. In the example below, the sales and margin columns have a row with totals. However, we may sometimes receive the data without the totals, so we don’t always need to trim the last row. Here is where the Trim table by condition action is very useful:

We’re checking if the region column is empty using the isempty([region]) condition. When the condition is satisfied in a certain row, that row along with any below it will be trimmed. Note that in this case it is necessary to check the option Delete the first matching row too.

Advanced techniques for replacing values

Standardizing values and replacing them with the correct ones is one of the pillars of data cleansing. Above we’ve reviewed the standard Replace action, and now we’ll go over more advanced ways of performing replacements.

The natural evolution of the Replace action is the Table-wide replace action, which allows us to replace a value in all (or selected) columns. This action gives us additional options such as choosing if the value to replace should match the whole cell, or could just be a part of it. In the following example, we’re replacing the substring “Fin.” with “Finance” in all columns:

But what if what if we had multiple words to standardize (“Finance“,”Legal”, “Marketing”, “Operations”) and we didn’t want to repeat this same action for each word? Here is where the Replace with lookup action comes in. Instead of manually setting the substring to replace in the action settings, we use a lookup table that contains all the substrings and their corresponding standardized words. This table will be used to perform all the replacements.

Here is the same example as before, but substituting multiple substrings at once thanks to the lookup table called “Mapping“:

Removing specific types of data

In some cases we may want to remove an entire value when it is not the type of data we expect. For example, we may want only numeric values in a column and so any non-numeric values (e.g. text values) should be removed. For more information on data types in EasyMorph, click here.

The Clean up action removes certain data types from our data. In the following example, text and error values from the order_tax column are being emptied out:

In the previous example we applied the action only to column order_tax, but it's possible to apply it to all columns or selected columns using the following dropdown:

Converting data types

Another common error in data is that in a certain column, there are values with different data types (for example, text and number, when all column values should be numbers). One of the most common data type issues are dates stored as text. In EasyMorph, as in Excel, dates are stored as numbers (more info on this here). However, dates may be incorrectly stored as text in occasions when there is inconsistent date formatting.

The Convert data type action allows us to unify the data type in one or more columns by converting those types that aren’t correct.

In the the following example, due to a missing leading “0” in the month for some values, certain dates are being stored as text (note that these values are aligned to the left). We can solve this by using the “Convert data type” action, indicating that any text in the “order date” column should be converted to date (stored as a number). For this, we have to indicate the input format of the dates stored as text, which in this case is yyyy-M-dd (the single “M” is month without the leading “0”).

Note that after the conversion, all the date values are now aligned to the right, stored as numbers.

Apart from converting text to date, this action allows the following conversions:

• Text to number (indicating the decimal/thousand separator being used, which is the likely culprit of the number being stored as text)
• Everything (any data type) to text
• Empty to text or number (replacing empty cells with a specific value or parameter)

Hint: The input format of the dates stored as text can generally be auto-filled by EasyMorph when clicking on the “Detect” button in the action settings. If the input format can’t be detected, you can set it manually, following the format specified in the web-help of this action.