ETL_数据清洗/数据转换/数据集成

1 数据清洗及转换流程

        ①数据清洗:处理数据缺失、数据重复、数据异常等问题,提升数据质量.

        ②数据转换:涵盖基本数据转换、语义数据转换、衍生数据转换和隐私数据转换,适配分析需求.

        ③数据集成:整合多源数据.

2 数据清洗

2.1 数据缺失

2.1.1 数值型数据缺失

        数值型列的部分数值不存在,例如在员工薪资数据中个别薪资值缺失.

(1) 识别方法

        ①数据完整性特征分析:统计缺失值的数量和比例,能直观地呈现数据缺失的程度.

        ②描述性统计分析:计算均值、中位数、标准差等指标,缺失值的存在可能导致均值和中位数发生较大波动,标准差异常增大.

(2) 处理方法

        ①填充法:计算非缺失数据的平均值、中位数进行填补,或者填充默认值.均值易受极端值影响,适用于数据分布相对均匀的情况;当数据存在极端值时,中位数更能代表数据的一般水平.

         ②模型预测填补:利用其他相关特征建立机器学习模型,如线性回归模型.以预测房屋价格为例,若部分房屋价格数据缺失,可基于房屋面积、房龄、周边配套等特征构建模型来预测缺失的价格值.此方法能挖掘数据内在关系,但模型构建较复杂.

        ③删除策略:若该列对整体分析并非关键,且大量缺失使其无法提供有效信息,可删除此列;若缺失值所在记录的其他信息价值不大,也可删除整行记录,但要注意可能丢失潜在信息.

2.1.2 类别型数据缺失

        类别型数据集中部分类别信息不存在,例如在产品类别统计数据里部分产品的类别标注缺失.

(1) 识别方法

        ①分布分析:计算各分类的频率和占比.缺失值的存在会导致某些分类的频率异常低或占比失衡.例如在统计客户所属行业分布时,若某行业频率极低,可能存在缺失值.

        ②合规性检查_数据字典匹配:建立数据字典,将类别数据与字典中的合法取值进行比对,不在字典范围内或无值的视为缺失.比如产品类别有明确规定的取值范围,通过比对可找出缺失值.

(2) 处理方法

        ①众数填补:找出该列出现频率最高的类别,用众数填补缺失值.例如在客户职业信息中,"公司职员"出现频率最高,缺失的职业信息可用 "公司职员"填补.

        ②单独分类:将缺失值作为一个新的类别处理.如在商品类别数据中,将缺失的商品类别标记为 "未知类别”,在后续分析中单独考虑.

        ③删除策略:若该列对整体分析并非关键,且大量缺失使其无法提供有效信息,可删除此列;若缺失值所在记录的其他信息价值不大,也可删除整行记录,但要注意可能丢失潜在信息.

2.2 数据重复

2.2.1 全字段重复

        数据集中存在完全相同的记录.

(1) 识别方法

        数据重复特征分析_基于全字段匹配:将数据集中的每一条记录与其它所有记录进行比较,判断所有字段的值是否完全相同.这种方法准确性高,但计算量较大,适用于数据量较小的情况.

(2) 处理方法

        保留一条并删除其余:根据业务需求,通常保留首次出现的记录,删除后续重复的,以保证数据的唯一性,减少冗余.

2.2.2 关键字段重复

        数据集中部分记录在关键标识字段上的值相同,这些关键字段能唯一标识一条记录的特定含义.例如在订单数据表中,"订单编号"是关键字段,若出现多条记录的"订单编号"相同,但其他订单信息有差异.

(1) 识别方法

        数据重复特征分析_基于关键字段匹配:根据业务逻辑确定关键字段,比较这些字段的值来判断记录是否重复.此方法计算效率较高,但需要准确选择关键字段,否则可能会误判或漏判重复数据,适用于数据量较大的情况.

(2) 处理方法

        ①数据核对与修正:对于关键字段重复但其他信息有差异的记录,需进一步核对业务数据来源,确定正确的记录内容,修正错误或重复的数据.

        ②合并相关信息:如果重复记录的其他信息都有保留价值,可按照一定规则合并.比如将重复订单记录中的不同备注信息合并到一条记录中,同时保证关键字段的唯一性.

2.3 数据值异常

2.3.1 数值型数据异常

        数值型数据中存在与其他数据分布明显不同的值,这些异常值可能因数据录入错误、测量误差或特殊情况产生.例如学生考试成绩数据中,多数成绩集中在 50 - 100 分,却有成绩为 150 分的.

(1) 识别方法

        ①异常值检测_范围检查法:依据业务知识或数据经验设定合理取值范围.例如考试成绩设定在 0 - 100 分,超出此范围视为异常.

        ②异常值检测_三倍标准差法(3σ 原则):利用正态分布特性,通过计算均值与标准差确定异常值范围.计算简便,但对极端值敏感.适用于符合或近似正态分布的数据场景.

        ③异常值检测_四分位距法(IQR):基于数据分位数来识别异常值,计算简单,对极端值不敏感.适用于数据分布有一定规律,需快速定位明显异常值的场景.

(2) 处理方法

        ①调查核实:先调查异常值产生原因,如果属于特殊情况,确认后可特殊标注保留.

        ②修正异常值:若能确定是录入错误,直接修正.

        ③替代异常值:使用均值、中位数等统计量替代异常值.

        ④模型预测修正:借助其他相关特征构建模型预测合理值替代异常值.如结合学生平时表现、作业成绩等预测考试成绩,替代异常成绩.

        ⑤删除异常值:当异常值对分析影响极大且无法修正或替代时,可删除该记录,但需谨慎,以防丢失信息.

2.3.2 类别型数据异常

        类别型数据中出现不符合常规类别设定的值,这些异常值可能因数据录入错误、测量误差或特殊情况产生.例如在性别字段中,预定类别为"男"和"女",却出现 "中"这样的非法类别.

(1) 识别方法

        ①分布分析:计算各类别出现的频率,设定频率阈值,低于该阈值的类别视为罕见类别.

        ②合规性检查_数据字典匹配:建立数据字典,包含所有合法类别值.将数据与字典比对,不在字典中的即为非法类别.

(2) 处理方法

        ①调查确认:先调查异常值产生原因,如果属于特殊情况,确认后可特殊标注保留.

        ②修正为合法值:若能确定是录入错误,直接修正.

        ③归为其他类别:若无法确定正确类别,将异常类别归为"其他"类别,以便后续分析.

        ④合并或删除:对于不合理的罕见类别,可将其合并到相近类别或删除相关记录.

2.4 数据格式异常

2.4.1 日期格式异常

        数据中日期的表示形式不符合统一规范或约定格式.

(1) 识别方法

        ①正则表达式匹配:使用正则表达式匹配常见日期格式,不匹配的日期数据可能存在格式异常.

        ②函数识别:尝试将日期数据转换为标准格式,转换失败的即为格式异常数据.

(2) 处理方法

        格式转换:根据数据整体的日期格式倾向,利用工具函数将异常格式转换为统一标准格式.

2.4.2 数值格式异常

        数值数据的表示方式不符合常规的数字格式规范,包括小数点位数、千位分隔符使用不当或数据类型错误等.例如在产品价格数据中,价格的形式有"1,000.50"、"1000.50" 、"一千元".

(1) 识别方法

        ①数据类型检查:检查数据列的数据类型,若应为数值型却显示为其他类型,则可能存在格式异常.

        ②格式匹配:对于特定格式要求的数据,使用正则表达式匹配格式,不匹配的视为格式异常.

(2) 处理方法

        ①类型转换:将文本类型的数值转换为合适的数值类型.

        ②格式修正:按照业务需求,统一转成需要的格式

2.4.3 文本格式异常

        文本数据出现不符合预期的格式,如大小写混乱、包含多余空格、特殊字符不当使用等.

(1) 识别方法

        ①正则表达式检查:使用正则表达式匹配规范文本格式,不匹配的文本可能存在格式异常.

        ②字符串函数判断:通过字符串函数判断格式异常       

(2) 处理方法

        ①字符串大小写统一

        ②去除多余空格

        ③去除特殊字符

3 数据转换

3.1 数据格式转换

        ①格式转换:统一数据类型,如字符串转日期、整型转浮点型

        ②规范格式:如统一小数位数和字符串长度.

        ③编码转换:统一字符编码,防止数据显示和处理乱码.

3.2 语义数据转换

        依业务规则改变数据值,如标准化、离散化.

3.3 衍生数据转换

        ①通过运算生成衍生字段:利用现有数据字段进行数学运算、逻辑运算等生成新字段.

        ②数据聚合:对数据进行汇总操作.

3.4 隐私数据转换

        采用掩码处理等方式,对身份证号、手机号等加密,以保护敏感信息.

4 数据集成

4.1 数据源集成

        将不同来源、不同形式的数据整合一份完整的数据.

4.2 数据格式集成

        ①结构统一:不同数据源的数据结构可能不同,例如一个数据源中客户地址信息是单个文本字段,而另一个数据源按省、市、区等分开存储,集成时需统一结构.

        ②格式规范:将不同来源的数据,统一为一种类型和标准格式.

4.3 数据语义集成

        ①消除歧义:相同的数据字段在不同数据源可能有不同含义,需要统一对字段的理解.

        ②统一编码与标准:建立统一的编码标准,将各数据源的编码映射到标准编码.