Eight Essential Data Cleaning Techniques Every Analyst Should Know

1. Handling Missing Values

Missing values arise from device failures, manual entry errors, or incomplete sources and are the most basic data‑cleaning problem. The article distinguishes three missing‑value mechanisms:

MCAR (Missing Completely at Random) : the missingness is unrelated to any variable, e.g., a questionnaire item left blank.

MAR (Missing at Random) : missingness depends on other observed variables but not on the missing variable itself, e.g., older users tend not to report income.

MNAR (Missing Not at Random) : the missingness is directly related to the value of the variable, e.g., high‑income individuals avoid reporting income.

Based on the missing‑value proportion, the recommended actions are:

If the proportion is < 5 %, delete the rows directly.

If the proportion is between 5 % and 30 %, fill with mean/median/mode, or use K‑Nearest Neighbors or multiple imputation.

If the proportion exceeds 30 %, consider dropping the column unless it holds critical information.

For MNAR, create a binary “is missing” feature to retain the missingness as information.

2. Handling Duplicate Data

Duplicate records are common when integrating multiple sources and can inflate statistics. The article defines two cases:

Exact duplicates : all columns match; resolved with drop_duplicates().

Partial (near) duplicates : core fields match but some attributes differ, such as a user registered on two channels with the same name and phone but different email. These require defining a business key and deciding which record to keep based on timestamps or source priority.

When duplicates stem from manual entry errors (e.g., inconsistent company name spellings), string‑similarity algorithms like edit distance or cosine similarity are used to identify and merge them.

3. Handling Outliers

Outliers are extreme values that can originate from entry mistakes, sensor faults, or special business scenarios. The article stresses a two‑step process: precise identification followed by targeted treatment.

3‑Sigma rule : values beyond mean ± 3 × standard deviation for normally distributed data.

IQR method : values outside Q1‑1.5×IQR or Q3+1.5×IQR, robust for non‑normal distributions.

Isolation Forest : an unsupervised algorithm suitable for high‑dimensional data without distribution assumptions.

Visualization : box plots or scatter plots to spot outliers visually.

After detection, options include deletion, Winsorization (capping), dedicated modeling, or flagging the outlier for downstream use, depending on business context and outlier proportion.

4. Data Type Conversion

Raw data from heterogeneous sources often suffer from mixed formats and inconsistent types, such as dates stored as strings, numeric fields polluted with currency symbols, or categorical variables encoded as integers without ordinal meaning. The article advises converting each column to a type that matches its semantic meaning before any analysis.

Standardize date strings to a uniform date format.

Strip special characters from numeric fields and cast to numeric types.

Encode categorical variables appropriately (e.g., keep as strings or use proper encoding).

Unify boolean representations (0/1, "yes"/"no", etc.).

Checking data types early is presented as a good habit.

5. Standardization and Normalization

Features often have vastly different scales (e.g., height in centimeters vs. income in yuan). Scaling mitigates dominance of large‑magnitude features during modeling.

Min‑Max Scaling (Normalization) : transforms values to the [0,1] interval using x' = (x - min) / (max - min). Suitable when the distribution is not normal or when a fixed range is required (e.g., neural‑network inputs).

Z‑Score Standardization : converts data to zero mean and unit variance with z' = (x - mean) / std. Works well for approximately normal data or distance‑based algorithms (KNN, SVM, PCA).

Both techniques are linear transformations that do not alter the underlying distribution shape; extreme outliers should be handled before scaling.

6. Handling Inconsistent Data

Inconsistent data refer to the same meaning expressed differently across sources, such as varied date formats, mixed units (kilometers vs. miles), or divergent gender encodings (1/0, M/F, 男/女). The article recommends building a unified data dictionary that defines the standard format for each field and applying rule‑based or mapping‑table transformations to enforce consistency.

Standardize date representations.

Convert all units to a single system.

Map disparate categorical encodings to a common schema.

Normalize case for textual fields.

When multiple business systems (ERP, CRM, MES, WMS) coexist, the effort can be substantial, and specialized data‑integration tools may be employed to automate the mapping.

7. Feature Encoding

Most machine‑learning models cannot consume raw categorical strings; they must be transformed into numeric representations. The article lists four common encodings and their trade‑offs:

Label Encoding : maps each category to an integer; suitable for ordered categories but can mislead models for unordered data.

One‑Hot Encoding : creates a binary column per category; preserves non‑ordinal nature but can explode dimensionality for high‑cardinality features.

Target Encoding : replaces a category with the mean of the target variable for that category; effective for high‑cardinality features but prone to over‑fitting unless combined with cross‑validation.

Frequency Encoding : substitutes the category with its occurrence frequency; simple, no target leakage, and safe from over‑fitting.

The choice depends on whether the variable is ordered, the number of distinct categories, and the downstream model (tree‑based models are tolerant, while linear models and neural networks are sensitive).

8. Text Data Cleaning

Text fields require more extensive preprocessing than structured columns. The article outlines a typical pipeline:

Noise removal : strip HTML tags, special symbols, extra spaces, and unify half‑width/full‑width characters.

Case normalization : convert English text to lower case.

Tokenization : use jieba for Chinese segmentation; split English on spaces while handling hyphens and abbreviations.

Stop‑word removal : discard high‑frequency words with little semantic value (e.g., “的”, “了”, “是”).

Lemmatization / stemming : reduce English words to their base form (run, running → run).

Entity handling : decide whether to keep numbers, dates, names, or locations based on the task.

The article emphasizes that the exact steps depend on the specific downstream task—sentiment analysis may treat stop‑words differently from keyword extraction.

Conclusion

Data cleaning is not a one‑off activity but an iterative process; each pass reveals new issues and deepens understanding of the dataset. Robust cleaning lays the foundation for reliable analysis and modeling.