Icon

02 Regex, Outliers and Data Quality - Solution

Regex, Outliers and Data Quality


Learning objective: In this exercise, you'll learn how to calculate some data quality measures, use regex in various nodes to extract values from a string, visualize and handle outliers.


Workflow description: This workflow calculates the data quality profile for the product and review datasets. The tables are then processed to extract some values from the strings, such as the product number, price and rating. Finally, it visualizes and handles some outlier values.


You'll find the instructions to the exercises in the yellow annotations.

Step 1. Data Quality Profile

  1. Open the component and complete the steps to calculate the data quality profile for the Products table.

  2. Copy the component to calculate the data quality profile for the Review table below.


Step 2. Extract ASIN from the link

The Link column in the Product table contains the ASIN number of the product. Extract it using a regular expression with the String Replacer node.

  • Pattern: https://www.amazon.com/dp/(.*)

  • Replacement text: $1

  • Append the extracted value in a new column ASIN



Step 3. Parse price

Remove the $ sign from the Price column and convert its value to float.

  1. Use the remove_chars() function in an Expression node.

  2. Make sure to convert the result to a float number.


Step 4. Parse Category and Rating

The provided Expression node already contains expressions to replace the categories with more specific ones. Add the expressions to

  1. Extract the number of reviews from the Global_Ratings column.

  2. Extract the rating of the product from the Rating column.

  3. Make sure to convert the extracted strings to numbers. Use the functions in the Expression node or the String to Number node.


The following regex help you match the numbers in the strings

"((\\d+,)*\\d+).*" matches numbers that might contain ","

  • "(\\d+\\.\\d+).*" matches numbers that contain "."

Step 5. Outliers in Price $

  1. Visualize the Price $ values in a Box Plot. Create one box for every category.

  2. Handle the outliers values the Price $ column Price $ with the Numeric Outliers node. Set the interquartile range multiplier to 1.5 and remove the outlier values.



Handle missing values, rename and sort

The products with missing price are removed. If the brand is missing, it is replaced with the value N/A. These nodes also rename the columns and sort the rows by rating and price.

Step 6. Outliers in Review Length

Handle the outliers values the Review Length column with the Numeric Outliers node. Set the interquartile range multiplier to 3 and remove the outlier values.



Review Date and Country

The Expression node parses the Review Date and Loc column to extract the date and location of each review and store them in single columns. The Review Date is then processed to obtain a string with the yyyy-mm format.

Review Score and Review Length

The Expression node contains the expression to extract the Review Score from the string. Additionally, it creates a new column containing the review length.

Sort by ratingand price
Sorter
Product details
Excel Reader
Data Quality Profile
Column Renamer
Product reviews
Excel Reader
Extract ASINfrom Link
String Replacer
Extractyear and month
Date&Time Part Extractor
Regex replace: Category,Rating, Global_Ratings
Expression
Remove outliersin Price
Numeric Outliers
Concatenateyear and month
Column Aggregator
Price by category
Box Plot
Remove outliersin Review Length
Numeric Outliers
Domain Calculator
Data Quality Profile
Remove rows with missing priceFill missing Brand with N/A
Missing Value
Regex extract:Review CountryReview Date
Expression
Parse Price
Expression
Review ScoreReview Length
Expression
String to Date&Time

Nodes

Extensions

Links