什么是数据清洗
数据清洗是指在数据处理过程中对原始数据进行筛选、转换和修正,以确保数据的准确性、一致性和完整性的过程。它是数据预处理的一部分,旨在处理和纠正可能存在的错误、缺失值、异常值和不一致性等数据质量问题。
为什么要数据清洗
Excel在数据采集场景中非常常用。作为一款电子表格软件,它提供了丰富的功能和易用的界面,使其成为大部分人首选的数据采集工具之一。
而在数据采集的过程中,因为采集渠道多样,数据格式也多种多样,从而会出现部分数据的丢失和不准确的情况,因此为了处理掉这些 “垃圾”数据,需要对数据进行清洗。
哪些数据需要进行清洗
通常在这几种情况下需要进行数据清洗。
1.缺失数据处理:数据在采集或迁移的过程中,出现数据的遗漏。
2.错误数据判断:数据在采集或迁移的过程中与原数据不一致。
3.重复数据处理:一条数据重复出现多次。
4.数据格式转换:数据在采集或迁移的过程中出现了乱码。
数据清洗都需要做些什么
下面让我们看一下数据清洗都会涉及的处理步骤:
- 分析需求:通过对数据原本的格式,特征进行分析,规划数据清洗的业务规则及需求。
- 打开文件:把Excel文件打开,通常这一步需要依赖Excel组件库,比如使用POI,GcExcel,EasyExcel等。
- 读取数据:通过Excel库中的API,读取需要操作的数据,这里比较一下三个产品的特点:
GcExcel提供了IRange(区域)的概念,可以通过API快速的读取有数据的区域。POI和EasyExcel(POJO注解)则需要遍历每一个单元格。
根据业务需求,可以选择使用API,也可以选择遍历所有单元格。
- 数据清洗:根据需求,结合Excel库的API,进行数据清洗。如:用默认值填写缺失数据的单元格,删除整个空行,删除重复数据,把不符合范围的数据删除掉,或者把日期数字的格式统一起来,等等。
- 数据持续化:把处理好的数据回存至Excel文件,或者保存在数据库中或者CSV文件中。
如何使用GcExcel实现数据清洗
GcExcel有IRange的API,可以让数据清洗时代码写的更简单,因此下面我们选择用GcExcel的代码为例解决上面提到的几个场景。
基于IRange,GcExcel提供一些快速查找的API,如下(在文件中查找特殊单元格):
Workbook workbook = new Workbook();
workbook.open("data.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
//寻找sheet中,使用到的所有单元格
IRange usedRange = sheet.getUsedRange();
//寻找所有的公式单元格
IRange allFormulas = sheet.getCells().specialCells(SpecialCellType.Formulas);
//寻找所有的常量单元格
IRange allConstants = sheet.getCells().specialCells(SpecialCellType.Constants);
虽然GcExcel提供了API,但数据清洗时,也可能有需求需要遍历,下面是GcExcel遍历单元格的代码,后面我们就有可能会用到。
public void FetchCellBasedOnRange(IRange area) {
for (int column = 0; column
场景一:缺失数据处理
假如有一个Excel的数据,现在蓝色的格子是空的,我们需要对不同列下的蓝色格子做不同的处理,例如姓名的空格子替换为匿名,年龄替换成-1,身份证号填写N/A,住址填写为未知。
代码如下:
public void replaceBlankCell() {
Workbook workbook = new Workbook();
workbook.open("resources/BlankCells.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
IRa服务器托管网nge blankRanges = sheet.getCells().specialCells(SpecialCellType.Blanks);
for (IRange area : blankRanges.getAreas()) {
for (int column = 0; column
要注意的是,sheet.getCells().specialCells(SpecialCellType.Blanks);返回的区域是多个,因此我们需要遍历通过遍历areas来对每一个区域进行遍历。
cell.getColumn()可以获取到当前格子对应到sheet上的第几列,因此获取默认值时使用该方法。
场景二:错误数据判断
错误数据的判断,与缺失数据处理相似,通过制定一些规则找出错误的值,对于错误值可以通过修服务器托管网改背景颜色进行高亮处理,用来提示,进行人工修改。
通常规则可以有两种选择:
- 使用Java直接编写判断逻辑。
- 使用数据校验(Datavalidation)功能,或者条件格式(ConditionFormat)来进行处理。
假如我们有下面一份数据,其中联系电话中有两条是错误的,位数不够,货物ID有两条是错误的,货物ID不能小于0,我们需要把他们找出来。
public void MarkErrorData(){
Workbook workbook = new Workbook();
workbook.open("resources/ErrorData.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
IRange telRange = sheet.getRange("C2:D5");
for (int r=0; r
在代码中,我们对C2:C5进行遍历,判断字符串长度,然后对长度不合法的数据进行颜色标记。
而对于货物,设置了条件格式,可以让Excel在打开时,自行标记错误的数据。
场景三:重复数据处理
假如我们有一份数据,其中有一些行数据是完全重复的,我们需要删除这些行,如图所示。
public void RemoveDuplicateData() {
Workbook workbook = new Workbook();
workbook.open("resources/DuplicateRows.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
IRange usedRange = sheet.getUsedRange();
HashSet set = new HashSet();
Stack deleteRows = new Stack();
for (int r = 1; r
可以看到,重复的行被移除掉了。代码中用到了哈希set和栈,其中我们用哈希set来查找重复的行。
另外使用栈来记录需要被删除的行,这里特地用了栈,而没有使用队列,数组或者ArraryList的原因是,GcExcel在删除一行时,会让这行下面的数据上移,这样我们之前记录的行就会便宜,导致删除错误的行。
简而言之,我们需要从下向上删除,来避免行位移导致删错的问题。
场景四:数据格式转换
例如我们有一些日期数据,或者货币数据,在数据采集时数据格式不同,我们需要分别统一订单日期,金额的格式。
代码如下:
public void unifyFormat() {
Workbook workbook = new Workbook();
workbook.open("resources/DifferentFormat.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
IRange usedRange = sheet.getUsedRange();
for (int row = 1; row
需要注意的是在处理日期和金额时,由于value的类型不太一致,需要写特定的方法来进行处理。
扩展链接:
Spring Boot框架下实现Excel服务端导入导出
项目实战:在线报价采购系统(React +SpreadJS+Echarts)
React + Springboot + Quartz,从0实现Excel报表自动化
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net
作者:京东科技 周新智 一、引言 近日,IoT 研发团队加入了不少新同学,对 git 分支的命名和管理方式有些许的模糊,分支的命名规范以及管理方式对项目的版本发布至关重要,为了解决实际开发过程中版本发布时代码管理混乱、冲突等比较头疼的问题,我们将在文中阐述如何…