Introduction
Data cleansing is a crucial process in data engineering that ensures your datasets are accurate, consistent, and ready for analysis. In this guide, I will walk you through 10 basic steps of data cleansing, helping you transform messy data into valuable insights. Whether you’re new to data engineering or need a quick refresher, these steps will equip you with the essential skills to handle data quality issues effectively.
To make the concept easier to understand, I’ve created a dummy retail dataset to illustrate each step. You can download the sample data and Python notebook from GitHub.
0. Import Libraries
The very first step in any data cleansing process is to import the necessary libraries. For Python users, this often includes popular data handling tools like Pandas and NumPy. These libraries provide powerful methods for manipulating and cleaning data efficient.
import pandas as pd
import numpy as np1. Load the Dataset
Loading your dataset is the next critical step. You can load data from various formats, such as CSV, Excel, or SQL databases. In Python, you can use Pandas to load data:
file_path = 'retail_dummy_dataset.csv' # Update the path as needed
df = pd.read_csv(file_path)
print("Initial Dataset:")
display(df.head())The read_csv() function in Pandas is used to read a CSV (Comma Separated Values) file into a DataFrame. It provides many options to customize how the data is read, such as specifying a delimiter, handling missing values, or selecting specific columns. In the given example, pd.read_csv(file_path) loads the dataset from the file path provided and stores it in a DataFrame called df. This function is fundamental to data preprocessing as it helps load data for analysis.
Loading the data correctly sets the foundation for all subsequent data cleansing operations.
2. Converting Data Types
Converting data types is essential to ensure consistency. For example, dates should be converted to datetime objects, and numerical fields should have appropriate data types. This helps to avoid errors in data manipulation and analysis later.
The below codes are used to convert the data type of the CustomerFeedback column in the dataset to an integer type (Int64). The astype('Int64') method is used to convert the CustomerFeedback column to an integer data type. The Int64 type is specifically designed to handle missing values (NaN), which the default integer types in Pandas cannot accommodate.
print("Data types before conversion:")
display(df.dtypes)
# Ensure CustomerFeedback is of type integer
df['CustomerFeedback'] = df['CustomerFeedback'].astype('Int64')
print("Data types after conversion:")
display(df.dtypes)This step is crucial in ensuring that your data has the correct data types, which can prevent errors during analysis and improve the efficiency of data operations.
3. Identify Missing Data
Identifying missing data is crucial to understand the quality of your dataset. You can use functions like .isnull() to detect missing values in your dataset:
print("Missing values per column:")
display(df.isnull().sum())df.isnull() returns a DataFrame of the same shape as df, with True indicating missing values (NaN) and False otherwise.
Applying .sum() to the result counts the number of True values in each column, effectively giving the number of missing values per column.This step helps you identify which columns have missing values and how many, providing insights into the extent of data quality issues.
4. Fill Missing Data
After identifying missing data, you can fill these gaps using techniques like mean or median:
# Impute missing PurchaseAmount with the median
df['PurchaseAmount'] = df['PurchaseAmount'].fillna(df['PurchaseAmount'].median())
# Impute missing DiscountApplied with 0 (assume no discount applied)
df['DiscountApplied'] = df['DiscountApplied'].fillna(0)
# Fill missing PaymentMethod with 'Unknown'
df['PaymentMethod'] = df['PaymentMethod'].fillna('Unknown')
print("Missing values after imputation:")
display(df.isnull().sum())The fillna() function in Pandas is used to fill missing (NaN) values in a DataFrame. It allows you to replace missing values with a specified value or a statistical measure like the mean, median, or a constant.
This code fills the missing values in the PurchaseAmount column with the median of the non-missing values in that column. Using the median is particularly useful when dealing with skewed data, as it is less sensitive to outliers compared to the mean.
5. Drop Missing Data
In some cases, dropping missing data might be more appropriate, especially when the missing values are too many:
# Drop rows where CustomerFeedback is missing (since it is the target variable)
df = df.dropna(subset=['CustomerFeedback'])
print("Dataset after dropping missing data:")
display(df.head())dropna() is used to remove rows or columns with missing values (NaN) in a DataFrame.
The parameter subset=['CustomerFeedback'] specifies that the function should only consider missing values in the CustomerFeedback column. If there are missing values in this column, the corresponding rows will be dropped.
This step is useful when CustomerFeedback is a critical column, and rows with missing feedback are not useful for analysis, making it appropriate to drop them entirely.
6. Removing Duplicate Data
Manual data entry can introduce duplicate records when data is recorded more than once by mistake. Duplicates can significantly affect your data analysis results. Removing duplicate entries ensures that each record in your dataset is unique:
# Check for duplicates
print(f"Number of duplicate rows: {df.duplicated().sum()}")
# Remove duplicates
df = df.drop_duplicates()
print(f"Number of rows after removing duplicates: {len(df)}")Removing duplicate customer records is crucial for maintaining data integrity, ensuring the accuracy of metrics, and preventing overestimation in analyses, such as customer counts or revenue estimations. Duplicates can skew your analysis and lead to inaccurate insights, which is why removing them is a critical data cleansing step.
7. Standardizing Data
Standardizing data ensures consistency. For example, standardizing text data by converting all entries to lowercase or trimming extra spaces can help prevent inconsistencies:
# Standardize ProductCategory to lowercase
df['ProductCategory'] = df['ProductCategory'].str.lower()
# Strip extra spaces in PaymentMethod
df['PaymentMethod'] = df['PaymentMethod'].str.strip()
# Ensure PurchaseAmount is rounded to two decimal places
df['PurchaseAmount'] = df['PurchaseAmount'].round(2)
# Ensure DiscountApplied is rounded to two decimal places
df['DiscountApplied'] = df['DiscountApplied'].round(2)
print("Dataset after standardization:")
display(df.head())This step is essential for consistency, especially when working with categorical data. For example, variations like “ELECTRONICS”, “electronics”, and “Electronics” would be treated as separate categories if not standardized. By converting all entries to lowercase, you ensure uniformity, which makes it easier to filter, group, and analyze the data without inconsistencies due to case differences.
Extra spaces can lead to errors or inconsistencies during analysis. For instance, “Credit Card” and ” Credit Card” would be treated as different values if not cleaned. Stripping leading and trailing spaces ensures that each entry is consistently formatted, making it easier to group and analyze categories accurately.
Rounding numeric values to two decimal places helps standardize monetary values, making them easier to read and ensuring consistency throughout the dataset. This is particularly important when performing financial analyses, where minor discrepancies can lead to confusion or errors in reporting.
These standardization steps help in reducing inconsistencies, improving data quality, and ensuring that the dataset is ready for accurate analysis.
8. Removing Unnecessary Columns
Not all columns in your dataset are always necessary for analysis. Removing irrelevant columns helps in reducing complexity and focusing on meaningful features:
# Remove PurchaseDate for this example
df = df.drop(columns=['PurchaseDate'])
print("Dataset after removing unnecessary columns:")
display(df.head())9. Binning Data
Binning is a technique used to categorize continuous values into bins, making the data easier to analyze. It is often used for age ranges, income levels, etc. The below codes are used to create a new categorical column, PurchaseCategory, in the dataset by grouping the values of PurchaseAmount into defined ranges, which is known as binning.
# Bin PurchaseAmount into categories: Low, Medium, High
bins = [0, 100, 500, 1000]
labels = ['Low', 'Medium', 'High']
df['PurchaseCategory'] = pd.cut(df['PurchaseAmount'], bins=bins, labels=labels)
print("Dataset after binning PurchaseAmount:")
display(df.head())bins = [0, 100, 500, 1000] defines the boundaries of the bins. In this example, PurchaseAmount is divided into three categories:
- 0 to 100 (Low)
- 100 to 500 (Medium)
- 500 to 1000 (High)
labels = ['Low', 'Medium', 'High'] specifies the labels for the bins. Each range is labeled to make the output easily understandable.
pd.cut(df['PurchaseAmount'], bins=bins, labels=labels) takes the PurchaseAmount column and categorizes each value into one of the specified bins, creating a new column, PurchaseCategory, that stores these labels.
10. Save the Cleaned Dataset
After all cleansing steps are complete, it is essential to save your cleaned dataset for future analysis:
# Save the cleaned dataset for further use
cleaned_file_path = 'retail_cleaned_dataset.csv'
df.to_csv(cleaned_file_path, index=False)
print(f"\nCleaned dataset saved to {cleaned_file_path}")Saving the cleaned dataset ensures that the work you’ve done is preserved and can be used in further analysis or modeling.
Conclusion
Data cleansing plays a critical role in data engineering by ensuring that datasets are accurate, consistent, and ready for analysis. By following these 10 steps, you can efficiently address data quality issues, enhance data reliability, and build robust datasets that support meaningful insights and informed decision-making.