Why Data Cleaning Can't Be Skipped: A Clear Guide to Avoid Common Pitfalls

Many people think data cleaning is just "delete‑modify" and try to skip it, but the author shows that dirty data leads to wrong conclusions and extra rework. An e‑commerce case is cited where mixing browsing and order records inflated a product's repeat‑purchase rate to an impossible 30%.

1. Data Profiling

The first step is to export the raw data to a spreadsheet, review each column, and answer: total rows, field meanings, and obvious issues. Simple statistics such as missing‑rate and duplicate‑rate per column are calculated. For a retail project the author found a 30% missing rate for "customer phone" and 5% duplicate values for "order date".

2. Missing‑Value Handling

The author proposes three scenarios:

Low missing rate & important field: verify with business or fill with a reasonable value.

High missing rate & unimportant field: drop the column.

Medium missing rate & key field: mark as "unknown" and explain in analysis.

He warns against blindly using the average; for example, filling missing ages with the overall mean can distort results, so he prefers median age within the same region and gender, and deletes a field when the missing rate reaches 60%.

3. Duplicate Removal

Duplicate data inflates statistics. The process is two‑fold:

Delete fully identical rows (all fields match).

Inspect partially duplicate rows, such as identical order numbers with different timestamps, and confirm with business whether they are true repeat orders or data entry errors.

4. Outlier Correction

Outliers are values that clearly violate logic, such as an order amount of 100,000 ¥ when the average product price is 100 ¥, or a customer age of 150. Detection methods include checking extreme values and distribution gaps. The author’s medical‑client example shows a sales figure ten times larger than the rest, which turned out to be a legitimate bulk purchase and was kept.

5. Format Standardization

Inconsistent formats prevent aggregation. The author recommends:

Date → "YYYY‑MM‑DD".

Numeric → two decimal places.

Text → capitalize first letter only; phone numbers → remove area codes and spaces.

These rules are best enforced with automated tools rather than manual edits.

6. Data Linking

When analyses require joining tables (e.g., orders with customers), ensure join keys share the same type and format. The author describes a case where "customer ID" was numeric in the order table but text in the customer table, requiring conversion before the join. Unmatched keys (e.g., 100 order IDs not found in the customer table) must be investigated to determine whether the customer table is missing data or the order IDs are erroneous.

Common Pitfalls

Blindly filling missing values with averages.

Deleting outliers without verification.

Ignoring business logic when cleaning.

Skipping validation after cleaning.

For each pitfall the author stresses asking the business, verifying the data, and performing a quick post‑cleaning check (total counts, key field statistics) before proceeding to analysis.

In summary, data cleaning has no shortcuts; following a disciplined, step‑by‑step process saves time and yields reliable analytical results.