PREPROCESS DATA BY TEXT SIMILARITY

Introduction to Data Preprocessing

In this guide, you will learn how to use two powerful Flookup functions that can make your data cleaning easier, faster and susceptible 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 groups:

• • Group I: Remove diacritical marks, Keep URL domain only, Keep URL path only. These will modify the original dataset in place.

  • Group II: Remove punctuation marks, Remove unwanted words. These will leave the original dataset unchanged.

Here is a condensed look at what each NORMALIZE Mode does:

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 selected text entries.

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

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

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

To normalize text entries in the "Group I" functions, follow the steps below:

1. Head to Extensions > Flookup Data Wrangler > Preprocess data > NORMALIZE in your spreadsheet menu.

2. Select the mode you want this function to run from the drop-down menu. See the explanation below.

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

4. Click the "Normalize text entries" button.

To normalize text entries in the "Group II" functions, follow the steps below:

1. Head to Extensions > Flookup Data Wrangler > Preprocess data > NORMALIZE in your spreadsheet menu.

2. Select the mode you want this function to run from the drop-down menu. Scroll down to see the explanation below.

3. Range of entries to normalize: Select a column of text entries you would like to normalise and then click the "Grab selected range" button.

4. Range of entries to be removed: Select a cell that contains stop words or punctuation marks and then click the "Grab selected range" button.

5. Click an empty cell to indicate the column where you would like your results to be displayed. This cell should be in any sheet inside that same workbook.

6. Click the "Normalize text entries" button.

---

• Threshold: This parameter is only active when removing unwanted words and you should adjust it at step #5 above. 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.

FUZZYMATCH

1. 1. Head to Extensions > Flookup Data Wrangler > Preprocess data > FUZZYMATCH in your spreadsheet menu.

   2. Select the mode you want this function to run from the drop-down menu. Your choices are: Compare short text entries and Compare long text entries.

   3. Primary range: Select range of one or more columns containing multiple cells and click the "Grab selected range" button.

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

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

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

   7. Click an empty cell to mark the column where you would like your results to be displayed, inside that workbook.

   8. Click the "Calculate percentage similarities" button.

Explore More

• Match and Merge Data by Text Similarity

• Removing Duplicates by Text Similarity

• Highlighting Duplicates by Text Similarity