What is Data Cleaning?

Data cleaning describes the process of turning messy data into clean datasets that can be used for research and data science purposes. For example, tidy data will be in a wide format: every column contains a variable, and every row contains one case. Also, data cleaning means getting rid of corrupt data with very little utility.

Related Tutorial: Data Preparation in Python

How to Clean Unstructured Data in Python?

Most data in the real world is messy and unstructured or semi-structured. Working in data science, most of your time will be spent on cleaning and structuring data.

In research, data from surveys or experiments is mostly already structured into Excel or CSV tables.

In companies, data can be stored in databases, Excel files, or distributed all over the company. This data can be in emails, documents, folders, images, and note apps.

This study shows, that most companies are having problems handling unstructured or semi-structured data, and almost half of them don’t even know where their data is located.

Unstructured data includes videos, images, and text or speech messages. Unstructured data from the web is mainly acquired by web scraping.

Semi-structured data is data found in documents, emails, social media posts, and if acquired from the web, it can be in HTML, JSON, or any other web format.

Is Web Scraping Legal?

Important: Even though web scraping is possible does not mean it is always legal!

If the data is publicly available and not copyrighted, it is mostly safe to scrape. But also pay attention to data privacy laws and do not scrape personal data.

Scraping data from social media websites, for example, is mostly illegal, as it is not publicly available without logging in and contains personal data.

There are also many services to get data via an API. To be safe, refer to this guide about what is legal when web scraping:

What is the Goal of Data Cleaning?

The goal of data cleaning and cleaning unstructured or semi-structured data is to create tidy data with which you can work. Tidy data will be in a wide format: every column contains a variable, and every row contains one case.

To demonstrate both perspectives, this article is divided into two parts:

• First, we will scrape, load, and wrangle some semi-structured data from the web.
• Second, we will clean this data. This second step is also valid for structured data as it is about finding missing, outliers and duplicates.

I recommend doing an exploratory data analysis before or during cleaning data to get a good feeling of the data you have. You can easily combine exploring and cleaning.

Python HTML scraping

First, we will scrape a table from Wikipedia of the largest cities in the world. Scraping from Wikipedia is legal because

1. The data is publicly available
2. The data works under a creative commons deed, which means the content is free to copy, share and adapt

First, we load our packages:

• pandas as our go-to library for data wrangling and analysis,
• the standard library for HTTP requests, and
• BeautifulSoup for easily reading HTML data.

import pandas as pd # pandas for data wrangling, cleaning, and analysis

import requests # for http requests

from bs4 import BeautifulSoup # html reading

The wiki page that holds the table we are looking for can be found here:

• https://en.wikipedia.org/wiki/List_of_largest_cities

We pass this URL into requests. The table in the HTML page is within <table class> , and the tables in wiki pages are called wiki tables. We can check this by looking at the HTML page in our browser or the HTML text file later to confirm we pulled the right table.

With requests.get(url).text, we pull the HTML from the page.

url='https://en.wikipedia.org/wiki/List_of_largest_cities'

url_response=requests.get(url).text

BeautifulSoup will pull the data table from the HTML file and save us time. We will pass the url_response from our request into the html.parser. With soup.find() we can tell it to look exactly for the wikitable. The output also tells us the name of the table.

soup = BeautifulSoup(url_response.text, 'html.parser')

html_listofcities=soup.find('table',{'class':'wikitable'})

html_listofcities

Output:

Then we use pandas to read the HTML file and turn it into a pandas data frame, just like we would load any other data into Python.

df=pd.read_html(str(listofcities))

cities=pd.DataFrame(df[0])

print(cities.head())

Output:

The data looks messy. Now we get to clean!

Python HTML Cleaning

Let us inspect our newfound data:

Cities

Output:

I will drop the columns of the metropolitan area and the urban area because I am just interested in the population of the actual city. This can be done in several ways.

Here are two:

cities.drop(cities.columns[[7, 8, 9, 10, 11, 12]], axis=1, inplace=True)

# or

cities= cities.drop(cities.iloc[:,7:], axis = 1)

Then we will drop the first header column as it does not contain any useful information and rename the remaining header column.

# Dropping the first index column

cities.columns = cities.columns.droplevel(0) 

Output:

# rename the header column

cities.columns = ['city', 'country', 'UN_2018_population_estimates', 'city_definition', 'city_population', 'city_area_km2', ' city_density/km2']

Output:

The heading looks clean. Now we explore the dataset to find information to clean.

With df.info() and df.describe() we get a quick overview of the data we scraped.

cities.info()

Output:

cities.describe()

Output:

It is immediately clear that the city_density/km2 is not a float even though it is supposed to be numerical.

Inspecting the data frame, you might have already noticed that the columns contain numbers following numbers in brackets, like [12]. This turns this data into an object, so we will have to get rid of this.

There are different ways to remove characters from a string in Python. We could just remove the last three characters of each string.

However, this would not work if some of our data points do not have the brackets at the end or more than that. So we’ll use the slicing method str.partition() to cut the brackets from our numbers.

First, we make sure our object type is a string that we can work string operations on. Then we apply the str.partition() method and advise the function to cut off at the first bracket [.

cities[' city_density/km2'] = cities['city_density/km2'].astype('string')

city_density_str = cities['city_density/km2'].str.partition('[')

print(city_density_str)

Output:

What we want is the first column, so we pick this one [0] and write it back on a variable.

city_density_str_col_1 = city_density_str[0]

print(city_density_str_col_1)

Output:

The commas in the variable will prevent us from converting the string into a float, so we’ll remove the comma with str.replace() before turning the string to a float with s.astype('float') and assigning it back to our data frame.

city_density_str_col_1 = city_density_str_col_1.str.replace(',','')

cities['city_density/km2'] = city_density_str_col_1.astype('float')

print(cities['city_density/km2'])

Output:

The variable now shows up when we look at df.describe() and we’ll want the results rounded for better readability:

cities.describe().round(1)

Output:

Cleaning Structured Data in Python

Following the cleaning of the scraped data we can now use it like a structured data frame with data we collected or downloaded.

This also can be cleaned of missing data, outliers and duplicates but does not always need data wrangling. However, with a data frame with many strings the cleaning process also often involves a lot of string manipulation.

Important note:

If you want to apply machine learning algorithms to your data, do split your dataset before feature engineering and data transformation as this can create data leakage!

Dropping duplicates is easy:

cities = cities.drop_duplicates()

There didn’t seem to be duplicates in our df, as the size remained the same.

When dealing with missing values, we must decide how to handle them based on our data.

We can either

• Drop missing values
• Replace or impute the values
• Leave missing values in the dataset
• Transform the information that they’re missing into a new variable

First, we inspect our missing data. The function df.isnull() is a boolean function, that tells us for the whole data frame if data is missing or not.

We can sum it up to determine, how many values are missing in each column.

cities.isnull().sum()

Output:

We can drop rows with missing values completely.

This will cause us to lose useful information in other columns. But as the first row is completely empty anyway, we can drop this one.

The df.dropna() function has useful features that help us pick what missing data we want to remove. So, I just want to remove the one row, or all of them if there are more, with all missing values.

cities = cities.dropna(how='all')

This will look like this:

What is left are the missing values for 8 cities for population, area and density. We will replace those.

Of course, you can look up the data on Wikipedia and reinsert them. For the sake of the exercise and because most of the time it is not possible to look up missing data, we will not do this.

The dataset now contains the data of the 73 biggest cities in the world, using the average of these to impute the missing values in the other 8 is the only and closest guess we have. This does not create much more information but keeps us from losing other information from these 8 cities.

The alternative option would be to drop those 8 cities completely.

So, we’ll replace the missing values in the area column with the average area size of all the other cities. First, we create the mean of the city area sizes, then we fill the missing values in the column with this value.

Pandas has the right function for this: df.fillna()

city_area_mean = cities['city_area_km2'].mean()

cities['city_area_km2'] = cities['city_area_km2'].fillna(value=city_area_mean)

cities.isnull().sum()

Output:

The output shows that we now have replaced and eliminated several missing values.

For the population, we luckily have another column that shows the population estimates of the UN in 2018 for each city.

So, we can use these to impute the missing population data, as it is as close as we can get to replacing them accurately.

cities['city_population'] = cities['city_population'].fillna(cities['UN_2018_population_estimates'])

cities['city_population'].describe().round(1)

Output:

Now we still have missing values in the city density column. This one we can calculate now by dividing the population by the area.

So, we create a new variable to calculate the density with our new imputed data. Then we fill the missing values with this calculated density.

city_density = cities['city_population']/cities['city_area_km2']

cities['city_density/km2'] = cities['city_density/km2'].fillna(value=city_density)

cities['city_density/km2'].describe().round(1)

Output:

We can check back our missing values and the description of our dataset.

cities.isnull().sum()

cities.describe().round(1)

There is still one value missing in our city definition. Let’s have a look at these categories.

cities['city_definition'].value_counts()

Output:

As we don’t know if the missing city is a municipality or a capital, we could just replace the missing value with the generic description of “city”, as we know they all are cities.

If you’d want to calculate the differences between these categories, it would be useful to categorize and merge these single entries into bigger categories.

For now, we will just replace the missing value with “city”, as I am more interested in the size of the cities than the category.

cities['city_definition'] = cities['city_definition'].fillna('City')

cities.isnull().sum()

Great! We got rid of all the missing values.

Info: For many statistical operations, missing values will be dropped by default and don’t create a problem. For machine learning algorithms missing values must be removed before modelling.

We can also create dummy variables (information is missing/ not missing) as the fact that the data is missing might be useful information. This way, the fact that they’re missing can be included in the data analysis process.

Visualization

Now we visualize our data and check for outliers with a seaborn scatterplot.

import seaborn as sns

sns.scatterplot(data=cities, x="city_population", y="city_area_km2", size="city_population")

Output:

The city in the right top corner is clearly an outlier, but not one we would want to remove or equalize as it is not a measurement error. It is just the biggest city (or metropolitan area) in the world!

Let’s find out which one it is with df.sort_values(), using ascending=False to sort the city population from high to low.

cities.sort_values('city_population', ascending=False)

Output:

The biggest city on earth by size and city population is the municipality Chongqing in China with over 32 million inhabitants!

To visualize our cities more beautifully, we can use a sns.relplot() with color and different sizing.

sns.relplot(x="city_population", y="city_area_km2", hue="country", size="city_population", sizes=(40, 400), alpha=.5, palette="muted", height=6, data=cities)

Output:

Now keep on cleaning!

• What is PSVR 2?

Problem Formulation and Solution Overview

In this article, you’ll learn how to check a List for Duplicates in Python.

To make it more fun, we have the following running scenario:

The Finxter Academy has given you an extensive list of usernames. Somewhere along the line, duplicate entries were added. They need you to check if their List contains duplicates. For testing purposes, a small sampling of this List is used.

Question: How would we write Python code to check a List for duplicate elements?

We can accomplish this task by one of the following options:

• Method 1: Use set() and List to return a Duplicate-Free List
• Method 2: Use set(), For loop and List to return a List of Duplicates found.
• Method 3: Use a For loop to return Duplicates and Counts
• Method 4: Use any() to check for Duplicates and return a Boolean
• Method 5: Use List Comprehension to return a List of all Duplicates

Method 1: Use set() and List to return a Duplicate-Free List

This method uses set()which removes any duplicate values (set(users)) to produce a Duplicate-Free set(). This set is then converted to a List (list(set(users))).

users = ['AmyP', 'ollie3', 'shoeguy', 'kyliek', 'ollie3', 'stewieboy', 'csealker', 'shoeguy', 'cdriver', 'kyliek'] dup_free = list(set(users))

print(dup_free)

This code declares a small sampling of Finxter usernames and saves them to users.

Next, set() is called and users is passed as an argument to the same. Then, the new set is converted to a List and saved to dup_free.

If dup_free was output to the terminal before converting to a List, the result would be a set(), which is not subscriptable. Meaning the elements are inaccessible in this format.

Output

Note: Any attempt to access an element from a set will result in a not subscriptable error.

In this example, the set() was converted to a List, and displays a List of Duplicate-Free values.

Output

Note: An empty set will result if no argument is passed.

Method 2: Use set(), For loop, and List to return a List of Duplicates Found

This method uses set(), and a For loop to check for and return any Duplicates found (set(x for x in users if ((x in tmp) or tmp.add(x)))) to dups. The set() is then converted to a List (print(list(dups))).

Here’s an example:

users = ['AmyP', 'ollie3', 'shoeguy', 'kyliek', 'ollie3', 'stewieboy', 'csealker', 'shoeguy', 'cdriver', 'kyliek'] tmp = set()

dups = set(x for x in users if (x in tmp or tmp.add(x)))

print(list(dups))

This code declares a small sampling of Finxter usernames and saves them to users.

Next, a new empty set, tmp is declared. A For loop is then instantiated to check each element in users for duplicates. If a duplicate is found, it is appended to tmp. The results save to dups as a set().

Output

In this example, the set() was converted to a List and displays a List of Duplicates values found in the original List, users.

Method 3: Use a For loop to return Duplicates and Counts

This method uses a For loop to navigate through and check each element of users while keeping track of all usernames and the number of times they appear. A Dictionary of Duplicates, including the Usernames and Counts returns.

Here’s an example:

count = {}

dup_count = {}

for i in users: if i not in count: count[i] = 1 else: count[i] += 1 dup_count[i] = count[i]

print(dup_count)

This code declares two (2) empty sets, count and dup_count respectively.

A For loop is instantiated to loop through each element of users and does the following:

• If the element i is not in count, then the count element (count[i]=1) is set to one (1).
• If element i is found in count, it falls to else where one (1) is added to count (count[i]+=1) and then added to dup_count (dup_count[i]=count[i])

This code repeats until the end of users has been reached.

At this point, a Dictionary containing the Duplicates, and the number of times they appear displays.

Output

Method 4: Use Any to Check for Duplicate Values

This example uses any(), and passes the iterable users to iterate and locate Duplicates. If found, True returns. Otherwise, False returns. Best used on small Lists.

users = ['AmyP', 'ollie3', 'shoeguy', 'kyliek', 'ollie3', 'stewieboy', 'csealker', 'shoeguy', 'cdriver', 'kyliek'] dups = any(users.count(x) > 1 for x in users)

print(dups)

This code declares a small sampling of Finxter usernames and saves them to users.

Next, any() is called and loops through each element of users checking to see if the element is a duplicate. If found, True is assigned. Otherwise, False is assigned. The result saves to dups and the output displays as follows:

Output

Method 5: Use List Comprehension to return a List of all Duplicates

This method uses List Comprehension to loop through users, checking for duplicates. If found, the Duplicates are appended to dups.

Here’s an example:

users = ['AmyP', 'ollie3', 'shoeguy', 'kyliek', 'ollie3', 'stewieboy', 'csealker', 'shoeguy', 'cdriver', 'kyliek'] dups = [x for x in users if users.count(x) >= 2]

print(dups)

This code declares a small sampling of Finxter usernames and saves them to users.

Next, List Comprehension extracts and displays duplicate usernames and save them to a List. The duplicate values are output to the terminal

Output

Summary

These five (5) methods of checking a List for Duplicates should give you enough information to select the best one for your coding requirements.

Good Luck & Happy Coding!

In the last article I wrote about setting up a WooCommerce product enquiry form using existing third-party plugins. There are many WordPress plugins for having an enquiry form on a WordPress site.

As a developer we can build a WooCommerce product enquiry form custom plugin easily. It will be lightweight, good for enhancement and maintenance, above all fun too.

The 3-party plugins give a massive package with voluminous functionalities and features. They have form builders, email template builders and all.

For having an enquiry form with a few fields, it can be customized simply without third party bundles. It helps to have a tiny and self-manageable code which is best.

If you want to know how to create a WooCommerce contact form with 3-party plugins, see my last article linked.

Steps to create a WooCommerce product enquiry form plugin

1. Create a contact form plugin directory and file.
2. Design a WooCommerce product enquiry form template.
3. Create the CSS and JavaScript assets of a plugin.
4. Load the form templates into the page content.
5. Enqueue the WordPress plugin assets.
6. Send product enquiry via WordPress AJAX on form submit.
7. Hook wp_ajax to send product enquiry mail.

Create a contact form plugin directory and file

Create a directory named “woocommerce-contact-form” in the WordPress plugin directory.

<wordpress-application-root>/wp-content/plugins/


Then, create a file woocommerce-contact-form.php into it with the following plugin header. The header must have the plugin name, URI, author and more details.

/* Plugin Name: WooCommerce Contact Form Plugin URI: https://phppot.com Description: A simple custom enquiry form plugin to a WooCommerce site. It has name, email and message fields to collect data from the customers. Version: 1.0 Author: Vincy Author URI: https://phppot.com */

Design WooCommerce product enquiry form

Create a template file inside the plugin directory as <woocommerce-contact-form>/templates/contact-form.php.

Put this HTML into the file to display a contact form with Name, Email and Message fields.

This template file is loaded into the product page using a WordPress filter hook via this plugin.

<h3>Product enquiry</h3>

<div id="woocommerce-contact-form"> <form method="post" id="frmContactForm" action=""> <div class="display-flex"> <input name="customer_name" id="customer_name" placeholder="Name" /><input name="customer_email" id="customer_email" placeholder="Email" /> </div> <div> <textarea name="customer_message" id="customer_message" placeholder="Enquire seller about order, product.."></textarea> </div> <div> <input type="submit" name="send_mail" class="btn-send" value="Send Enquiry" /> </div> <div id="response"></div> </form>

</div>

Create plugin assets

This plugin uses simple CSS and JavaScript assets. The CSS provides minimal styles to make the enquiry form suit any shop theme.

If you want to change this WooCommerce product enquiry form theme, this CSS file will be useful.

#woocommerce-contact-form { width: 500px; border: #CCC 1px solid; padding: 25px 5px 25px 25px; border-radius: 3px;

} #woocommerce-contact-form input { border: #CCC 1px solid; width: 50%; padding: 10px; margin: 15px 20px 15px 0px; border-radius: 3px;

} #woocommerce-contact-form textarea { border: #CCC 1px solid; width: 96%; border-radius: 3px; box-sizing: border-box; padding: 10px; margin: 15px 20px 15px 0px;

} .display-flex { display: flex;

} #woocommerce-contact-form input.btn-send { color: #FFF; background: #232323; border-radius: 3px; border: #000 1px solid;

} #response { display: none;

}

JavaScript enquiry form validation and submission via AJAX

This script uses jQuery to handle form validation and submit the posted data via AJAX.

All the fields are required to be entered to send the product enquiry from the WooCommerce page.

Once the conditions return true, then the jQuery AJAX will serialize the form data and post it to the server.

All client-side and server-side responses are sent to UI using jQuery .text().

$(document).ready(function(e) { // WooCommerce product enquiry form submit event handler $("#frmContactForm").on('submit', function(e) { e.preventDefault(); var name = $('#customer_name').val(); var email = $('#customer_email').val(); var message = $('#customer_message').val(); // Validate all fields in client side if (email == "" || message == "" || name == "") { $("#response").show(); $("#response").text("All fields are required."); } else { $("#response").hide(); $('.btn-send').hide(); $('#loader-icon').show(); // Post form via wp-ajax $.ajax({ url: "/wordpress/send-contact-email/", type: "POST", data: $("#frmContactForm").serialize(), success: function(response) { $("#response").show(); $('#loader-icon').hide(); if (!response) { $('.btn-send').show(); $("#response").html("Problem occurred."); } else { $('.btn-send').hide(); $("#response").html("Thank you for your message.") }; }, error: function() { } }); } });

});

The AJAX script points to a URL that is mapped with the wp_ajax endpoint via .htaccess.

The send_contact_email action param hooks the wp_ajax action filter in the plugin file.


# BEGIN Shop
<IfModule mod_rewrite.c>
RewriteRule send-contact-email/ /wordpress/wp-admin/admin-ajax.php?action=send_contact_email [L,QSA]
</IfModule>
# END Shop


WooCommerce product enquiry form plugin file

This is the main file that hooks the required action and filter callback of WordPress. These callbacks are to do the following by activating this plugin.

1. Load the contact form template using ‘the_content’ filter hook.
2. Enqueue the form.css and form.js files created for this WooCommerce product enquiry form. (Also enqueued the jQuery CDN path).
3. Hook the wp_ajax_nopriv_* to execute the send_contact_email callback by listening the AJAX request.

The load_contact_form function reads the template content by using PHP file_get_contents() function. It appends the content to the page content on a condition basic.

Before adding the WooCommerce product enquiry form it checks if it is a product page. If so, is_product() returns boolean true.

<?php function load_contact_form($contactHTML) { // Load the contact form on a single product page if ( is_product() ){ $template_path = plugin_dir_path( __FILE__ ) . 'templates/contact-form.php'; $contactHTML .= file_get_contents( $template_path); } return $contactHTML; }

// add filter to hook 'the_content' with a plugin functions.

add_filter('the_content', 'load_contact_form'); /** * Enqueues scripts and styles for front end. * * @return void */

function woocommerce_contact_form_styles()

{ if (is_product()) { wp_enqueue_style('contact-form-style', plugin_dir_url( __FILE__ ) . 'assets/form.css', array(), null); wp_enqueue_script('contact-form-jquery', 'https://code.jquery.com/jquery-3.6.0.min.js', array(), null); wp_enqueue_script('contact-form-script', plugin_dir_url( __FILE__ ) . 'assets/form.js', array(), null); }

}

add_action('wp_enqueue_scripts', 'woocommerce_contact_form_styles'); 

Hook wp_ajax action with the contact email sending callback

The below code is part of the woocommerce_contact_form.php plugin file. It defines a callback send_contact_email().

The WooComerce product enquiry form plugin has the action and filter callbacks. It is called when it receives the wp_ajax request from the following URL.

/wordpress/wp-admin/admin-ajax.php?action=send_contact_email.

It builds the wp_mail() parameters to send the product enquiry to the shop admin or seller. The $to holds the recipient’s address.

It builds the mail body with the data posted via the WooCommerce product enquiry form.

Using wp_mail() is a simple way of sending email via WordPress. It is as similar to PHP mail as it has a one-line code for sending emails. You may configure SMTP to send email via WordPress.

 function send_contact_email()

{ $customerName = filter_var($_POST["customer_name"], FILTER_SANITIZE_STRING); $customerEmail = filter_var($_POST["customer_email"], FILTER_SANITIZE_STRING); $customerMessage = filter_var($_POST["customer_message"], FILTER_SANITIZE_STRING); $to = 'vincysmtp@gmail.com'; $subject = 'Product enquiry'; $body = 'The customer details: <br/><br/>'; if(!empty($customerName)) { $body = 'Customer Name:' . $customerName . '<br/>'; $body .= 'Customer Email:' . $customerEmail . '<br/>'; $body .= 'Customer Message:' . '<br/>'; $body .= $customerMessage . '<br/>'; } $headers = array('Content-Type: text/html; charset=UTF-8'); $emailSent = wp_mail( $to, $subject, $body, $headers ); print $emailSent; exit;

}

add_action("wp_ajax_nopriv_send_contact_email", "send_contact_email"); 

WooCommence product page with contact form

With all prerequisites and complete coding of the custom plugin, it’s time to enable it.

Go to WordPress admin and navigate via Plugins->Installed Plugins using the left menu.

You may see the ‘WooCommerce Contact Form’ plugin in the installed list. Activate the plugin and visit the product page of the shop.

See the screenshot shown at the beginning of the article. It displays the WooCommerce product enquiry form on a product page.

Conclusion

Thus, we have created a plugin for a WooCommerce product enquiry form with simple code. If you want any customization with this plugin, please let me know in the comments section.

Let us see the other methods of displaying a contact form on a WooCommerce site in the upcoming article.

↑ Back to Top