Data cleansing is the process of identifying, correcting or removing errors, inconsistencies, and inaccuracies from data to improve the data quality. Data cleansing is a crucial step in data preparation, ensuring the data is accurate, complete, consistent, and valid.
Inhaltsverzeichnis
- 1 Data Cleansing – In a Nutshell
- 2 Definition: Data cleansing
- 3 The importance of data cleansing
- 4 Data cleansing – Distinguish dirty from clean data
- 5 Data cleansing – How to do it
- 6 Data cleansing – Validation
- 7 Data cleansing – Screening
- 8 Step 1:
- 9 Step 2:
- 10 Step 3:
- 11 Data cleansing – Diagnosing
- 12 Data cleansing – Missing data
- 13 Data cleansing – Outliers
- 14 FAQs
- 15 Sources
Data Cleansing – In a Nutshell
- Without proper data cleansing, errors and inconsistencies can lead to bias and inaccurate conclusions in the research.
- The aim of data cleansing is to improve the data quality for analysis and decision-making.
- Data cleansing is a crucial step in data preparation before any statistical analysis or hypothesis testing is conducted.
Definition: Data cleansing
Data cleansing refers to removing or correcting inaccurate, incomplete, or irrelevant data to improve quality and consistency. Its purpose is to resolve errors in the data to make it accurate, complete, and valid. An error in data can be defined as any deviation from the expected values or patterns. Here is a step-by-step process for data cleansing:
- Data validation
- Data screening
- Diagnosing data entries
- Developing codes
- Transforming/ removing data1
The importance of data cleansing
Data plays a crucial role in quantitative research, as it makes inferences and predictions about a given population. Tools such as statistical analyses are used to analyze data in quantitative research, and hypothesis testing is used to test the validity of research findings.
However, if data is not cleansed properly, it can lead to bias in research results, such as information bias or omitted variable bias.
Example:
If a study on the effectiveness of a new medication includes only patients with a particular medical condition, the results may not be generalizable to the larger population.2
Data cleansing – Distinguish dirty from clean data
Dirty data is defined as data that contains inconsistencies and errors. Three common sources of dirty data include:
- Poor research design
- Data entry errors
- Inconsistent formatting
| Dirty Data | Clean Data |
| Invalid | Valid |
| Inaccurate | Accurate |
| Incomplete | Complete |
| Inconsistent | Consistent |
| Duplicate | Unique |
| Falsley Formatted | Uniform |
Valid vs. invalid data
Valid data meets the criteria for data validation, such as being within a specific range. Invalid data doesn’t meet these criteria and may be removed or corrected during the data cleansing.
Example of data validation:
Ensuring that all participants in a study are within the specified age limit.
Accurate vs. inaccurate data
Accurate data doesn’t have errors and inconsistencies, while inaccurate data contains errors or inconsistencies.
Example:
A participant’s age is recorded as 25 when they are 35.
Complete vs. incomplete data
Complete data is fully recorded and contains no missing values, while incomplete data contains missing values. Incomplete data can be reconstructed using methods such as imputation or multiple imputations.
Example:
A survey missing the responses for certain questions.
Consistent vs. inconsistent data
Consistent data agrees with other data and doesn’t contain any contradictions. In contrast, inconsistent data contains contradictions or discrepancies.
Example:
A participant’s height is recorded as 6 feet in one survey and 6’1″ in another.
Unique vs. duplicate data
Unique data is distinct and not duplicated, while duplicate data is identical to other data.
Example:
Having two records for the same participant in a study. Eliminating duplicate data through data cleansing is necessary to prevent inaccuracies in the analysis.
Uniform vs. falsely formatted data
Uniform data follows a consistent format and structure, while falsely formatted data deviates from the established format.
Example:
A participant’s phone number being recorded in different formats in different surveys.3
Data cleansing – How to do it
Effective data cleansing is crucial for accurate and reliable quantitative research. It’s important to consider the potential hurdles that may occur during data cleansing, like missing values, outliers, or incorrect formatting. To effectively cleanse data, various techniques can be used, such as data validation, data screening, data diagnosis, code development, and data transformation/ removal.
Data cleansing workflow
A data cleansing workflow is a structured approach to identifying and correcting errors, inconsistencies, and inaccuracies in data. Documenting a data cleansing workflow helps to ensure consistency and reproducibility of results. The various steps of a data cleansing workflow include:
- Data validation techniques to avoid dirty data: Checking data for errors and inconsistencies and removing or correcting invalid data.
- Data screening for errors: Identifying data inconsistencies, like missing values or outliers.
- Diagnosing data entries: Examining individual data entries to identify and correct errors or inconsistencies.
- Developing codes: Creating codes or rules for cleaning and transforming data.
- Transforming or removing data: Data cleansing and transforming it to make it more accurate and reliable for analysis. This can include removing irrelevant data or imputing missing values.4
Data cleansing – Validation
Data validation is a technique to ensure that data meets specific criteria before storing or processing. This can include checking for errors and inconsistencies and removing or correcting invalid data. Data validation is relevant when collecting data to ensure that it’s accurate and reliable for analysis. There are several types of data validation constraints, including:
Data-type constraints
Ensure that data is of a particular type, such as a number or a string. This can include checking if a phone number or date is entered in the correct format.
Example of data-type constraints:
Ensuring that all participants in a study are over the age of 18.
Range constraints
Ensure that data falls within a specific range. This can include checking that a participant’s age is between 18 and 65 or their weight is between 50 and 200 pounds.
Example:
Ensuring that all participants in a study have a BMI within a healthy range.
Mandatory constraints
Ensure that certain data is present before it is stored or processed. This can include checking that a required field is not empty or that a certain number of responses are collected.
Example:
Ensuring that all participants in a study have provided their names and contact information.5
Data cleansing – Screening
Storing a duplicate of data collection is vital for data screening, as it helps analyze the original data with the cleaned data. The process of data cleansing involves:
Step 1:
Structuring the dataset
This involves organizing and formatting data to make it more accurate and reliable for analysis. Important steps to consider when straightening up a dataset include:
- Sorting data
- Removing duplicates
- Standardizing formatting
Step 2:
Scanning data for inconsistencies
The second step in data cleansing involves identifying any errors or inconsistencies in the data, such as missing values or outliers. Questions to consider when scanning data for inconsistencies include:
- Looking for missing data
- Identifying outliers
- Checking for patterns in the data.
Step 3:
Using statistical methods to explore data
Descriptive statistics are crucial in detecting distributions, outliers, and skewness in data. These methods include:
- Boxplots, scatterplots, and histograms, which can be used to visualize data and identify patterns and outliers.
- Normal distribution is a statistical model that can identify abnormal data points.
- Descriptive statistics, such as mean, median, and mode, can summarize data and identify patterns and outliers.
- Frequency tables help identify the most common values in a dataset and outliers.
- Mean, median, and mode are essential in data cleansing as they can identify outliers and errors in data. The mean is the average value of the dataset, the median is the middle value, and the mode is the most common value.
Data cleansing – Diagnosing
Diagnosing data is the process of assessing the data quality in a dataset. This step is crucial for understanding potential issues that may arise when working with the data, such as inaccuracies, inconsistencies, and missing values. If data isn’t properly diagnosed, it can lead to inaccurate conclusions and poor decision-making. Some common problems in dirty data include:
- Duplicate data: Data that appears multiple times in dataset
- Invalid data: Data that doesn’t conform to the expected format or values
- Missing values: Data that is missing in particular fields or observations
- Outliers: Data that is significantly different from the majority of the data in the dataset6
Removing duplicate data
Deduplication is the process of identifying and removing duplicate data from a dataset.
Example:
Using a unique identifier, such as a primary key, to identify and delete duplicate rows in a dataset.
Invalid data
Data standardization ensures that data conforms to a specific format or set of rules. This method helps ensure consistency and accuracy in the data.
Example:
A phone number field that includes letters or symbols.7
Strict string-matching and fuzzy string-matching are methods used to identify and correct invalid data. Strict string-matching compares data precisely as entered, while fuzzy string-matching allows for slight variations in the data.
Example:
If the invalid data is a list of customer names and addresses, strict string-matching would only match “John Smith” to “John Smith,” while fuzzy string-matching would match “John Smith” to “Jhon Smit.” After matching, the next step is to correct or remove the invalid data.8
Data cleansing – Missing data
Random missing data is missing data that occurs entirely at random, while non-random missing data is missing data that is related to the data’s characteristics. Missing data can be tackled by:
| Accepting: | Leaving the missing data as is and treating it as a separate category |
| Removing: | Deleting observations or fields with missing data |
| Recreating; | Using statistical methods to estimate missing data. |
Example of missing data removal:
Removing all observations in a dataset with missing values for a specific field.
You can, however, use imputation to replace missing data with estimated values. To use imputation properly, it’s important to understand the underlying causes of the missing data and to use appropriate statistical methods to estimate the missing values.9
Data cleansing – Outliers
Outliers in a dataset are values significantly different from most data. Outliers can be either true values or errors.
| True Outliners | Error Outliners |
| Genuine values that are unusual or unexpected | Values that are the result of errors or mistakes in data collection or entry |
Identifying outliers
Common methods to detect outliers in a dataset include:
- Using statistical tests such as Z-scores or the interquartile range
- Using visualization methods like box plots or scatter plots
- Comparing data to expected values or ranges.
Retaining or removing outliers
There are several methods for retaining or removing outliers once they are identified in a dataset. One is to remove the outliers or to simply keep them but scale them differently.
Sometimes, it may be best to keep the outliers and use them to inform the analysis. However, it is important to document any outliers found and the decision made about handling them.10
FAQs
Data cleansing is done by identifying and correcting errors in data, such as missing values, duplicate values, or outliers.
Data cleansing is important because it ensures the accuracy and integrity of the data.
Yes, data cleansing can be automated using various tools and software, such as data quality software, data integration software, and data governance software.
The frequency of data cleansing will depend on the specific use case and the nature of the data. Some organizations may perform data*cleansing on a daily or weekly basis, while others may only need to do so on a monthly or quarterly basis.
Sources
1 Tableau. “Guide To Data Cleaning: Definition, Benefits, Components, And How To Clean Your Data.” Accessed January 17, 2023. https://www.tableau.com/learn/articles/what-is-data-cleaning.
2 Fitzgerald, Anna. “Data Cleansing: What It Is, Why It Matters & How to Do It.” HubSpot. March 2, 2022. https://blog.hubspot.com/website/data-cleansing.
3 Couwenbergh, Sofie. “The Importance of Cleaning Dirty Data for Improved Operations and Customer Success.” Validity. August 24, 2022. https://www.validity.com/blog/dirty-data/.
4 Acaps. “Data Cleaning.” April, 2016. https://www.acaps.org/sites/acaps/files/resources/files/acaps_technical_brief_data_cleaning_april_2016_0.pdf.
5 Open Risk Manual. “Data Constraints.” Accessed January 17, 2023. https://www.openriskmanual.org/wiki/Data_Constraints.
6 Elgabry, Omar. “The Ultimate Guide to Data Cleaning.” Towards Data Science. February 28, 2019. https://towardsdatascience.com/the-ultimate-guide-to-data-cleaning-3969843991d4.
7 Simplilearn. “Data Standardization: How It’s Done & Why It’s Important.” December 12, 2022. https://www.simplilearn.com/what-is-data-standardization-article.
8 Kuruvilla, Varghese P. “A Comprehensive Guide to Fuzzy Matchin/Fuzzy Logic.” Nanonets. Accessed January 17, 2023. https://nanonets.com/blog/fuzzy-matching-fuzzy-logic/.
9 InsightSoftware. “How to Handle Missing Data Values While Data Cleaning.” January 17, 2022. https://insightsoftware.com/blog/how-to-handle-missing-data-values-while-data-cleaning/.
10 Sharma, Natasha. “Ways to Detect and remove the Outliers.” Towards Data Science. May 22, 2018. https://towardsdatascience.com/ways-to-detect-and-remove-the-outliers-404d16608dba.