Excel 数据修复：使用 KNN 算法填充缺失值

Excel 数据修复：使用 KNN 算法填充缺失值

本文将介绍使用 Java 和 Apache POI 库，结合 KNN 邻近算法修复 Excel 数据中的缺失值。

1. 项目环境

• Java 开发环境
• Apache POI 库

2. 代码实现

package 数据修复;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class knn01 {

    public static void main(String[] args) {
        // 定义输入文件路径
        String inputFile = "input.xlsx";
        try (Workbook workbook = WorkbookFactory.create(new FileInputStream(inputFile))) {
            Sheet sheet = workbook.getSheetAt(0);
            DecimalFormat df = new DecimalFormat("#.##");

            // 对每一行进行处理
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Cell cell = row.getCell(1);
                    if (cell != null && cell.getCellType() != CellType.BLANK) {
                        // 如果单元格不为空，则跳过
                    } else {
                        double avg = calculateKNN(sheet, i, 1);
                        if (avg > 0) {
                            if (cell == null) {
                                cell = row.createCell(1);
                            }
                            cell.setCellValue(Double.parseDouble(df.format(avg)));
                        }
                    }
                }
            }

            // 创建新的工作表
            Sheet newDataSheet = workbook.createSheet("数据补");
            // 复制原工作表的数据到新的工作表
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row oldRow = sheet.getRow(i);
                Row newRow = newDataSheet.createRow(i);
                if (oldRow != null) {
                    for (int j = 0; j < oldRow.getLastCellNum(); j++) {
                        Cell oldCell = oldRow.getCell(j);
                        Cell newCell = newRow.createCell(j);
                        if (oldCell != null) {
                            newCell.setCellValue(oldCell.getStringCellValue());
                        }
                    }
                }
            }

            // 将工作簿写入输入文件
            FileOutputStream outputStream = new FileOutputStream(inputFile);
            workbook.write(outputStream);
            outputStream.close();
            System.out.println("Data filling completed.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 计算 KNN 邻近算法填充的值
    private static double calculateKNN(Sheet sheet, int rowIndex, int columnIndex) {
        List<Double> data = new ArrayList<>();
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Cell cell = row.getCell(columnIndex);
                if (cell != null && cell.getCellType() == CellType.NUMERIC) {
                    data.add(cell.getNumericCellValue());
                }
            }
        }
        if (data.size() > 0) {
            double missingValue = 0;
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                Cell cell = row.getCell(columnIndex);
                if (cell == null || cell.getCellType() == CellType.BLANK) {
                    missingValue = 0;
                } else if (cell.getCellType() == CellType.NUMERIC) {
                    missingValue = cell.getNumericCellValue();
                } else if (cell.getCellType() == CellType.STRING) {
                    try {
                        missingValue = Double.parseDouble(cell.getStringCellValue());
                    } catch (NumberFormatException e) {
                        missingValue = 0;
                    }
                }
            }
            if (missingValue > 0) {
                return missingValue;
            } else {
                List<Double> distances = new ArrayList<>();
                for (double value : data) {
                    double distance = Math.abs(value - missingValue);
                    distances.add(distance);
                }
                Collections.sort(distances, new Comparator<Double>() {
                    @Override
                    public int compare(Double o1, Double o2) {
                        return Double.compare(o1, o2);
                    }
                });
                int k = 3;
                double sum = 0;
                int count = 0;
                for (int i = 0; i < k && i < distances.size(); i++) {
                    double value = data.get(distances.indexOf(distances.get(i)));
                    sum += value;
                    count++;
                }
                if (count > 0) {
                    return sum / count;
                } else {
                    return 0;
                }
            }
        } else {
            return 0;
        }
    }

    // 解析日期
    private static Date parseDate(String dateString) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
        try {
            return dateFormat.parse(dateString);
        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }
    }

}

3. 代码说明

1. 该代码使用 Apache POI 库读取和写入 Excel 文件。
2. 代码首先定义输入文件路径，并使用 WorkbookFactory 创建一个 Excel 工作簿对象。
3. 代码遍历工作表中的每一行，并判断第二列单元格是否为空。
4. 如果单元格为空，则调用 calculateKNN 方法计算 KNN 邻近算法填充的值。
5. 将填充的值填入单元格。
6. 创建一个名为“数据补”的新工作表，并将原工作表的数据复制到新工作表。
7. 将工作簿写入输入文件。

4. KNN 算法实现

calculateKNN 方法实现了 KNN 算法，该算法通过计算待填充单元格与其他已知数据单元格之间的距离，找到 k 个最近的邻居，并取其平均值作为填充值。

5. 总结

本文介绍了如何使用 Java 和 Apache POI 库，结合 KNN 算法修复 Excel 数据中的缺失值。该方法可以有效地填充缺失值，提高数据完整性。