数据清洗

数据处理概述

数据处理可以分为两种主要方式：

类型	全称	特点	典型场景
OLTP	联机事务处理	实时性高、增删改查、事务处理	业务系统、订单处理
OLAP	联机分析处理	数据量大、分析报表、实时性要求低	数据仓库、商业智能

OLTP vs OLAP

code

OLTP特点：
├── 实时性要求高
├── 数据操作频繁（增删改查）
├── 事务处理（ACID）
└── 数据量相对较小

OLAP特点：
├── 数据量巨大
├── 查询复杂（多表关联、聚合）
├── 实时性要求不高
└── 需要数据质量保证

数据质量的重要性

对于数据分析工作，数据质量决定分析结果的上限：

code

数据质量 → 分析结果上限
模型选择 → 决定能否达到上限

无论模型多么先进，如果数据质量差，分析结果都会受到影响。

数据清洗准则

"完全合一"原则

数据清洗遵循"完全合一"四字准则：

准则	含义	检查内容
完	完整性	数据是否存在缺失
全	全面性	数据是否全面、类型是否正确
合	合法性	数据内容是否合法、合理
一	唯一性	数据是否存在重复

常见数据问题

问题类型	具体表现	示例
缺失值	字段值为空	年龄字段为NULL
重复数据	相同记录出现多次	同一用户多条记录
格式不统一	同一字段格式不一致	日期格式混乱
数据异常	数值超出合理范围	年龄为负数或超过150
单位不统一	同一字段单位不同	身高有cm和m两种单位

实战：泰坦尼克号数据清洗

数据集介绍

使用泰坦尼克号乘客生存预测数据集进行演示：

字段名	含义	类型
PassengerId	乘客ID	整数
Survived	是否存活（0/1）	整数
Pclass	舱位等级（1/2/3）	整数
Name	姓名	字符串
Sex	性别	字符串
Age	年龄	数值
SibSp	兄弟姐妹/配偶数量	整数
Parch	父母/子女数量	整数
Ticket	票号	字符串
Fare	票价	数值
Cabin	船舱号	字符串
Embarked	登船港口	字符串

导入数据

sql

-- 创建数据表
CREATE TABLE titanic_train (
    PassengerId INT,
    Survived INT,
    Pclass INT,
    Name VARCHAR(100),
    Sex VARCHAR(10),
    Age DECIMAL(5,2),
    SibSp INT,
    Parch INT,
    Ticket VARCHAR(20),
    Fare DECIMAL(7,4),
    Cabin VARCHAR(20),
    Embarked VARCHAR(1)
);

-- 使用Navicat或其他工具导入CSV数据
-- 或使用LOAD DATA命令
LOAD DATA INFILE 'train.csv'
INTO TABLE titanic_train
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

完整性检查

检查空值数量

单字段检查：

sql

SELECT COUNT(*) AS null_count 
FROM titanic_train 
WHERE Age IS NULL;

多字段检查：

sql

SELECT
    SUM(CASE WHEN Age IS NULL THEN 1 ELSE 0 END) AS age_null_num,
    SUM(CASE WHEN Cabin IS NULL THEN 1 ELSE 0 END) AS cabin_null_num,
    SUM(CASE WHEN Embarked IS NULL THEN 1 ELSE 0 END) AS embarked_null_num,
    SUM(CASE WHEN Fare IS NULL THEN 1 ELSE 0 END) AS fare_null_num
FROM titanic_train;

使用存储过程批量检查

当字段较多时，使用存储过程更高效：

sql

CREATE PROCEDURE check_column_null_num(
    IN schema_name VARCHAR(100), 
    IN table_name2 VARCHAR(100)
)
BEGIN
    DECLARE temp_column VARCHAR(100);
    DECLARE done INT DEFAULT false;
    DECLARE cursor_column CURSOR FOR
        SELECT COLUMN_NAME 
        FROM information_schema.COLUMNS 
        WHERE table_schema = schema_name AND table_name = table_name2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    
    OPEN cursor_column;
    
    read_loop: LOOP
        FETCH cursor_column INTO temp_column;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET @temp_query = CONCAT(
            'SELECT COUNT(*) AS ', temp_column, '_null_num ',
            'FROM ', table_name2, ' ',
            'WHERE ', temp_column, ' IS NULL'
        );
        
        PREPARE stmt FROM @temp_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    
    CLOSE cursor_column;
END

调用存储过程：

sql

CALL check_column_null_num('test', 'titanic_train');

检查结果示例：

code

Age_null_num: 177
Cabin_null_num: 687
Embarked_null_num: 2
Fare_null_num: 0
Name_null_num: 0
...

缺失值处理方法

方法	适用场景	优点	缺点
删除	缺失比例小	简单直接	丢失信息
均值填充	数值型字段	保持数据量	可能引入偏差
中位数填充	有异常值时	抗异常值	同上
众数填充	分类字段	合理	同上
插值法	时间序列	保持趋势	计算复杂

缺失值处理实践

1. Age字段（均值填充）

sql

-- 创建临时表避免MySQL限制
CREATE TABLE titanic_train2 AS SELECT * FROM titanic_train;

-- 使用均值填充
UPDATE titanic_train 
SET Age = (SELECT ROUND(AVG(Age), 1) FROM titanic_train2) 
WHERE Age IS NULL;

注意：MySQL不允许在同一条语句中查询和更新同一张表，需要使用临时表。

2. Cabin字段（保留空值）

sql

-- 检查Cabin字段分布
SELECT COUNT(Cabin), COUNT(DISTINCT Cabin) FROM titanic_train;

-- Cabin字段特点：
-- 1. 空值比例高（约77%）
-- 2. 值分布广泛，难以填充
-- 3. 对分析结果影响可能不大
-- 结论：保留空值，不做处理

3. Embarked字段（众数填充）

sql

-- 查看分布情况
SELECT Embarked, COUNT(*) AS cnt 
FROM titanic_train 
GROUP BY Embarked 
ORDER BY cnt DESC;

-- 结果：S出现频率最高

-- 使用众数填充
UPDATE titanic_train 
SET Embarked = 'S' 
WHERE Embarked IS NULL;

全面性检查

检查字段类型

sql

-- 查看表结构
DESCRIBE titanic_train;

-- CSV导入后所有字段默认为VARCHAR，需要转换

修正字段类型

sql

-- 修改字段类型
ALTER TABLE titanic_train 
    CHANGE PassengerId PassengerId INT NOT NULL PRIMARY KEY;

ALTER TABLE titanic_train 
    CHANGE Survived Survived INT NOT NULL;

ALTER TABLE titanic_train 
    CHANGE Pclass Pclass INT NOT NULL;

ALTER TABLE titanic_train 
    CHANGE SibSp SibSp INT NOT NULL;

ALTER TABLE titanic_train 
    CHANGE Age Age DECIMAL(5,2);

ALTER TABLE titanic_train 
    CHANGE Fare Fare DECIMAL(7,4);

设置非空约束

sql

-- 为关键字段设置NOT NULL约束
ALTER TABLE titanic_train MODIFY Name VARCHAR(100) NOT NULL;
ALTER TABLE titanic_train MODIFY Sex VARCHAR(10) NOT NULL;

合法性检查

数值范围检查

sql

-- 检查年龄是否在合理范围
SELECT MIN(Age), MAX(Age) FROM titanic_train;

-- 检查票价是否合理
SELECT MIN(Fare), MAX(Fare) FROM titanic_train;

-- 检查是否存在负值
SELECT COUNT(*) FROM titanic_train WHERE Age < 0;
SELECT COUNT(*) FROM titanic_train WHERE Fare < 0;

枚举值检查

sql

-- 检查性别字段
SELECT DISTINCT Sex FROM titanic_train;

-- 检查登船港口
SELECT DISTINCT Embarked FROM titanic_train;

-- 检查舱位等级
SELECT DISTINCT Pclass FROM titanic_train;

异常值处理

sql

-- 查找年龄异常的记录
SELECT * FROM titanic_train WHERE Age > 100 OR Age < 0;

-- 处理方式：
-- 1. 核实数据来源
-- 2. 如果确认错误，删除或修正
DELETE FROM titanic_train WHERE Age > 120;

-- 或使用合理值替换
UPDATE titanic_train SET Age = NULL WHERE Age > 120;

唯一性检查

主键检查

sql

-- 设置主键时自动检查唯一性
ALTER TABLE titanic_train 
ADD PRIMARY KEY (PassengerId);

-- 如果报错，说明存在重复

重复数据检查

sql

-- 检查完全重复的记录
SELECT PassengerId, COUNT(*) 
FROM titanic_train 
GROUP BY PassengerId 
HAVING COUNT(*) > 1;

-- 检查特定字段组合重复
SELECT Name, Age, COUNT(*) 
FROM titanic_train 
GROUP BY Name, Age 
HAVING COUNT(*) > 1;

删除重复数据

sql

-- 方法1：使用ROW_NUMBER（MySQL 8.0+）
DELETE FROM titanic_train
WHERE PassengerId IN (
    SELECT PassengerId FROM (
        SELECT PassengerId,
               ROW_NUMBER() OVER(PARTITION BY Name, Age ORDER BY PassengerId) AS rn
        FROM titanic_train
    ) t WHERE rn > 1
);

-- 方法2：创建临时表去重
CREATE TABLE titanic_clean AS
SELECT * FROM titanic_train 
GROUP BY Name, Age;

DROP TABLE titanic_train;
RENAME TABLE titanic_clean TO titanic_train;

数据标准化

字符串标准化

sql

-- 统一大小写
UPDATE titanic_train SET Sex = UPPER(Sex);

-- 去除空格
UPDATE titanic_train SET Name = TRIM(Name);

-- 统一格式
UPDATE titanic_train 
SET Sex = CASE 
    WHEN Sex IN ('M', 'male', 'Male', 'MALE') THEN 'M'
    WHEN Sex IN ('F', 'female', 'Female', 'FEMALE') THEN 'F'
    ELSE Sex
END;

数值标准化

sql

-- 数值归一化（Min-Max标准化）
SELECT 
    PassengerId,
    Age,
    (Age - (SELECT MIN(Age) FROM titanic_train)) / 
    ((SELECT MAX(Age) FROM titanic_train) - (SELECT MIN(Age) FROM titanic_train)) AS age_normalized
FROM titanic_train;

-- Z-Score标准化
SELECT 
    PassengerId,
    Age,
    (Age - (SELECT AVG(Age) FROM titanic_train)) / 
    (SELECT STDDEV(Age) FROM titanic_train) AS age_zscore
FROM titanic_train;

数据清洗完整流程

code

数据清洗流程：
┌─────────────────────────────────────────────────────────┐
│                                                         │
│  1. 数据导入                                             │
│     └── 从CSV/Excel等导入原始数据                        │
│                                                         │
│  2. 完整性检查                                           │
│     ├── 检查缺失值                                       │
│     └── 处理缺失值（删除/填充）                          │
│                                                         │
│  3. 全面性检查                                           │
│     ├── 检查字段类型                                     │
│     └── 修正数据类型                                     │
│                                                         │
│  4. 合法性检查                                           │
│     ├── 检查数值范围                                     │
│     ├── 检查枚举值                                       │
│     └── 处理异常值                                       │
│                                                         │
│  5. 唯一性检查                                           │
│     ├── 检查重复数据                                     │
│     └── 删除重复记录                                     │
│                                                         │
│  6. 数据标准化                                           │
│     ├── 字符串标准化                                     │
│     └── 数值标准化                                       │
│                                                         │
│  7. 数据导出                                             │
│     └── 导出清洗后的数据                                 │
│                                                         │
└─────────────────────────────────────────────────────────┘

数据清洗最佳实践

1. 保留原始数据

sql

-- 创建备份表
CREATE TABLE titanic_train_backup AS SELECT * FROM titanic_train;

-- 在副本上进行清洗操作

2. 记录清洗过程

sql

-- 创建清洗日志表
CREATE TABLE data_cleaning_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50),
    column_name VARCHAR(50),
    operation VARCHAR(100),
    old_value VARCHAR(100),
    new_value VARCHAR(100),
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 记录清洗操作
INSERT INTO data_cleaning_log(table_name, column_name, operation)
VALUES('titanic_train', 'Age', 'NULL values filled with mean');

3. 验证清洗结果

sql

-- 清洗前后对比
SELECT 
    'Before' AS stage,
    COUNT(*) AS total_records,
    SUM(CASE WHEN Age IS NULL THEN 1 ELSE 0 END) AS null_age
FROM titanic_train_backup

UNION ALL

SELECT 
    'After' AS stage,
    COUNT(*) AS total_records,
    SUM(CASE WHEN Age IS NULL THEN 1 ELSE 0 END) AS null_age
FROM titanic_train;

4. 自动化清洗脚本

sql

-- 创建自动化清洗存储过程
CREATE PROCEDURE clean_titanic_data()
BEGIN
    -- 备份原表
    DROP TABLE IF EXISTS titanic_train_backup;
    CREATE TABLE titanic_train_backup AS SELECT * FROM titanic_train;
    
    -- 填充Age缺失值
    UPDATE titanic_train 
    SET Age = (SELECT ROUND(AVG(Age), 1) FROM titanic_train_backup) 
    WHERE Age IS NULL;
    
    -- 填充Embarked缺失值
    UPDATE titanic_train 
    SET Embarked = 'S' 
    WHERE Embarked IS NULL;
    
    -- 修正数据类型
    ALTER TABLE titanic_train MODIFY Age DECIMAL(5,2) NOT NULL;
    
    -- 输出清洗结果
    SELECT 'Data cleaning completed!' AS status;
END

总结

数据清洗是数据分析的基础工作，核心要点如下：

方面	说明
核心原则	完全合一（完整性、全面性、合法性、唯一性）
关键步骤	检查→处理→验证→记录
常用方法	删除、填充、标准化、去重
注意事项	保留原始数据、记录清洗过程

数据清洗的价值：

提高数据质量
保证分析结果准确性
减少后续处理成本
提升数据可信度

良好的数据清洗习惯是数据分析师的基本素养，也是高质量数据分析的前提。