PREPROCESS DATA BY TEXT SIMILARITY

Introduction to Data Preprocessing

In this section, you will learn how to use two powerful Flookup functions that can make your data cleaning easier, faster and prone to fewer errors: NORMALIZE and FUZZYMATCH.

NORMALIZE can improve the quality and consistency of your data by removing or formatting text entries that might interfere with the fuzzy matching process.

FUZZYMATCH can help you understand your data better by showing you how similar your text entries are. It also gives you a glimpse of the underlying mechanism that drives the other Flookup functions.

NORMALIZE

Normalize function modes can be divided into two broad categories:

• • CATEGORY #1: Remove punctuation marks, Remove unwanted words. These will preserve the original dataset.

  • CATEGORY #2: Remove diacritical marks, Keep URL domain only, Keep URL path only. These will modify the original dataset.

To normalize text entries in the first category of functions, follow the steps below:

1. 1. Head to Extensions > Flookup Data Wrangler > Match and Merge Functions > NORMALIZE in your spreadsheet menu.

   2. Select the mode you want this function to run from the drop-down menu [See Explanation Below].

   3. Range of entries to normalize: Select a column of entries you want to normalise and then click "Grab selected range".

   4. Range of entries to be removed: If running Category #1 functions, select a single cell that contains your list of unwanted words or punctuation marks and then click "Grab selected range".

   5. Threshold: [Optional] This parameter is only active when removing unwanted words. It defines the minimum level of similarity between words to be removed from each text entry in your dataset and the predefined list of unwanted words.

   6. If running Category #1 functions, click an empty cell where you would like your results to be displayed. This cell should be in any sheet inside that same workbook.

   7. Click "Normalize text entries".

NORMALIZE Modes Explained

1. 1. Remove diacritical marks: This removes all diacritical marks from text entries.

   2. Remove punctuation marks: This will remove all specified punctuation marks from the target text entries.

   3. Remove unwanted words: This will remove all specified words from the target text entries.

   4. Keep URL domain only: This strips a URL of everything except its root domain e.g. "www.example.com" to "example.com".

   5. Keep URL path only: This strips a URL of everything except its path e.g. "www.example.com/new/users" to "/new/users".

FUZZYMATCH

1. 1. Head to Extensions > Flookup Data Wrangler > Match and Merge Functions > FUZZYMATCH in your spreadsheet menu.

   2. Primary range: Select range of one or more columns, containing one or more rows and click "Grab selected range".

   3. Secondary range: Select a separate range of one or more columns and then click "Grab selected range".

   4. Left_column: Enter the index of the column of values in "Primary range" that you want to compare to values from "Secondary range".

   5. Right_column: Enter the index of the column of values in "Secondary range" that you want to compare to values from "Primary range".

   6. Click an empty cell in a column to mark where you would like your results to be displayed, inside that same workbook.

   7. Click "Calculate percentage similarities".

You Might Also Like

• Match and Merge Data by Text Similarity

• Removing Duplicates by Text Similarity

• Highlighting Duplicates by Text Similarity