东莞响应式网站,深圳11区将实行居家办公,家庭装修设计软件免费,wordpress504什么是数据清洗
数据清洗是指在数据处理过程中对原始数据进行筛选、转换和修正#xff0c;以确保数据的准确性、一致性和完整性的过程。它是数据预处理的一部分#xff0c;旨在处理和纠正可能存在的错误、缺失值、异常值和不一致性等数据质量问题。
为什么要数据清洗
Exce…什么是数据清洗
数据清洗是指在数据处理过程中对原始数据进行筛选、转换和修正以确保数据的准确性、一致性和完整性的过程。它是数据预处理的一部分旨在处理和纠正可能存在的错误、缺失值、异常值和不一致性等数据质量问题。
为什么要数据清洗
Excel在数据采集场景中非常常用。作为一款电子表格软件它提供了丰富的功能和易用的界面使其成为大部分人首选的数据采集工具之一。
而在数据采集的过程中因为采集渠道多样数据格式也多种多样从而会出现部分数据的丢失和不准确的情况因此为了处理掉这些 “垃圾”数据需要对数据进行清洗。
哪些数据需要进行清洗
通常在这几种情况下需要进行数据清洗。
1.缺失数据处理数据在采集或迁移的过程中出现数据的遗漏。
2.错误数据判断数据在采集或迁移的过程中与原数据不一致。
3.重复数据处理一条数据重复出现多次。
4.数据格式转换数据在采集或迁移的过程中出现了乱码。
数据清洗都需要做些什么
下面让我们看一下数据清洗都会涉及的处理步骤
分析需求通过对数据原本的格式特征进行分析规划数据清洗的业务规则及需求。打开文件把Excel文件打开通常这一步需要依赖Excel组件库比如使用POIGcExcelEasyExcel等。读取数据通过Excel库中的API读取需要操作的数据这里比较一下三个产品的特点
GcExcel提供了**IRange(区域**的概念可以通过API快速的读取有数据的区域。POI和EasyExcelPOJO注解则需要遍历每一个单元格。
根据业务需求可以选择使用API也可以选择遍历所有单元格。
数据清洗根据需求结合Excel库的API进行数据清洗。如用默认值填写缺失数据的单元格删除整个空行删除重复数据把不符合范围的数据删除掉或者把日期数字的格式统一起来等等。数据持续化把处理好的数据回存至Excel文件或者保存在数据库中或者CSV文件中。
如何使用GcExcel实现数据清洗
GcExcel有IRange的API可以让数据清洗时代码写的更简单因此下面我们选择用GcExcel的代码为例解决上面提到的几个场景。
基于IRangeGcExcel提供一些快速查找的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 area.getColumns().getCount(); column) {for (int row 0; row area.getRows().getCount(); row) {IRange cell area.get(row, column);//获取单元格的值Object val cell.getValue();}}}场景一缺失数据处理
假如有一个Excel的数据现在蓝色的格子是空的我们需要对不同列下的蓝色格子做不同的处理例如姓名的空格子替换为匿名年龄替换成-1身份证号填写N/A住址填写为未知。 代码如下
public void replaceBlankCell() {Workbook workbook new Workbook();workbook.open(resources/BlankCells.xlsx);IWorksheet sheet workbook.getActiveSheet();IRange blankRanges sheet.getCells().specialCells(SpecialCellType.Blanks);for (IRange area : blankRanges.getAreas()) {for (int column 0; column area.getColumns().getCount(); column) {for (int row 0; row area.getRows().getCount(); row) {IRange cell area.get(row, column);Object defaultVal getDefaultVal(cell.getColumn());cell.setValue(defaultVal);}}}workbook.save(Result.xlsx);}private Object getDefaultVal(int column) {switch (column) {case 1:return 匿名;case 2:return -1;case 3:return N/A;case 4:return 未知;}return null;}要注意的是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 r0; rtelRange.getRows().getCount();r){IRange cell telRange.get(r,0);if(cell.getValue().toString().length() ! 11){cell.getInterior().setColor(Color.GetOrangeRed());}}IFormatCondition condition (IFormatCondition) sheet.getRange(D2:D5).getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Less, 1, null);condition.getInterior().setColor(Color.GetOrangeRed());workbook.save(Result.xlsx);} 在代码中我们对C2:C5进行遍历判断字符串长度然后对长度不合法的数据进行颜色标记。
而对于货物设置了条件格式可以让Excel在打开时自行标记错误的数据。
场景三重复数据处理
假如我们有一份数据其中有一些行数据是完全重复的我们需要删除这些行如图所示。 public void RemoveDuplicateData() {Workbook workbook new Workbook();workbook.open(resources/DuplicateRows.xlsx);IWorksheet sheet workbook.getActiveSheet();IRange usedRange sheet.getUsedRange();HashSetString set new HashSet();StackIRange deleteRows new Stack();for (int r 1; r usedRange.getRows().getCount(); r) {IRange row usedRange.getRows().get(r);StringBuilder rowKey new StringBuilder();for (int c 0; c row.getColumns().getCount(); c) {rowKey.append(usedRange.get(r, c).getValue().toString());}if (set.contains(rowKey.toString())) {deleteRows.push(row);} else {set.add(rowKey.toString());}}while (!deleteRows.isEmpty()) {deleteRows.pop().delete();}workbook.save(Result.xlsx);} 可以看到重复的行被移除掉了。代码中用到了哈希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 usedRange.getRows().getCount(); row) {IRange dateCell usedRange.get(row, 1);IRange priceCell usedRange.get(row, 2);dateCell.setValue(parseDate(dateCell.getValue()));dateCell.setNumberFormat(yyyy年MM月dd日);priceCell.setValue(parsePrice(priceCell.getValue()));priceCell.setNumberFormat(¥0.00);}sheet.getRange(B1).setNumberFormat();workbook.save(Result.xlsx);}private Double parsePrice(Object value) {if (value null)return null;String val value.toString();if (val.startsWith($) || val.startsWith(¥)) {val val.substring(1);}return Double.parseDouble(val);}private LocalDateTime parseDate(Object value) {if (value null)return null;if (value instanceof LocalDateTime) {return (LocalDateTime) value;}DateTimeFormatter[] formatters {DateTimeFormatter.ofPattern(yyyy/MM/dd),DateTimeFormatter.ofPattern(MM-dd-yyyy),DateTimeFormatter.ofPattern(yyyy年MM月dd日),DateTimeFormatter.ofPattern(yyyy.MM.dd)};LocalDate datetime null;for (DateTimeFormatter formatter : formatters) {try {datetime LocalDate.parse(value.toString(), formatter);break;} catch (DateTimeParseException e) {e.printStackTrace();}}assert datetime ! null;return datetime.atStartOfDay();}需要注意的是在处理日期和金额时由于value的类型不太一致需要写特定的方法来进行处理。 扩展链接
Spring Boot框架下实现Excel服务端导入导出
项目实战在线报价采购系统React SpreadJSEcharts
React Springboot Quartz从0实现Excel报表自动化
