• 日期: 2027-09-29 05:25:17
  • 标签: 常规

Excel 数据自动补全：KNN 算法与时间规律

本文介绍了一种使用 KNN 算法和时间规律自动补全 Excel 数据的方法，该方法可以识别每个月的数据组数，并在数据不足的情况下自动插入缺失的数据。

代码示例

package org1;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Data_Recovery {
    private static final DecimalFormat df = new DecimalFormat("#.##");
    public static void main(String[] args) {
        // 定义输入文件和输出文件的路径
        String inputFile = "input.xlsx";
        String outputFile = "output1.xlsx";
        try (Workbook workbook = WorkbookFactory.create(new FileInputStream(inputFile));
             FileOutputStream outputStream = new FileOutputStream(outputFile)) {
            Sheet sheet = workbook.getSheetAt(0);
            DecimalFormat df = new DecimalFormat("#.##");
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM");
            int groupCount = getGroupCount(sheet, dateFormat);
            // 对每个月进行处理
            for (int i = 1; i <= groupCount; i++) {
                int rowIndex = getFirstRowIndex(sheet, i, dateFormat);
                int rowCount = getRowCount(sheet, rowIndex, i, dateFormat);
                if (rowCount < 5) {
                    Date startDate = getStartDate(sheet, rowIndex, dateFormat);
                    for (int j = rowIndex + rowCount - 1; j >= rowIndex; j--) {
                        Row row = sheet.getRow(j);
                        if (row != null) {
                            Cell cell = row.getCell(1);
                            if (cell != null && cell.getCellType() == CellType.NUMERIC) {
                                double value = cell.getNumericCellValue();
                                for (int k = 0; k < 5 - rowCount; k++) {
                                    Row newRow = sheet.createRow(rowIndex + rowCount + k);
                                    Cell newCell = newRow.createCell(0);
                                    Date newDate = getNextMonth(startDate, k + 1);
                                    newCell.setCellValue(dateFormat.format(newDate));
                                    Cell valueCell = newRow.createCell(1);
                                    valueCell.setCellValue(Double.parseDouble(df.format(value)));
                                }
                                break;
                            }
                        }
                    }
                }
            }
            workbook.write(outputStream);
            System.out.println("Data filling completed.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取每个月的数据组数
    private static int getGroupCount(Sheet sheet, SimpleDateFormat dateFormat) {
        int count = 0;
        String lastDate = null;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Cell cell = row.getCell(0);
                if (cell != null && cell.getCellType() == CellType.STRING) {
                    String dateString = cell.getStringCellValue();
                    Date date = parseDate(dateString, dateFormat);
                    if (date != null) {
                        String currentDate = dateFormat.format(date);
                        if (lastDate == null || !lastDate.equals(currentDate)) {
                            count++;
                            lastDate = currentDate;
                        }
                    }
                }
            }
        }
        return count;
    }

    // 获取该月份第一组数据的行索引
    private static int getFirstRowIndex(Sheet sheet, int month, SimpleDateFormat dateFormat) {
        int rowIndex = 1;
        String targetDate = String.format("%04d/%02d", 2023, month);
        while (rowIndex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                Cell cell = row.getCell(0);
                if (cell != null && cell.getCellType() == CellType.STRING) {
                    String dateString = cell.getStringCellValue();
                    Date date = parseDate(dateString, dateFormat);
                    if (date != null && dateFormat.format(date).equals(targetDate)) {
                        return rowIndex;
                    }
                }
            }
            rowIndex++;
        }
        return rowIndex;
    }

    // 获取该月份的数据组数
    private static int getRowCount(Sheet sheet, int rowIndex, int month, SimpleDateFormat dateFormat) {
        int count = 0;
        String targetDate = String.format("%04d/%02d", 2023, month);
        while (rowIndex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                Cell cell = row.getCell(0);
                if (cell != null && cell.getCellType() == CellType.STRING) {
                    String dateString = cell.getStringCellValue();
                    Date date = parseDate(dateString, dateFormat);
                    if (date != null && dateFormat.format(date).equals(targetDate)) {
                        count++;
                    } else {
                        break;
                    }
                }
            }
            rowIndex++;
        }
        return count;
    }

    // 获取该月份第一组数据的日期
    private static Date getStartDate(Sheet sheet, int rowIndex, SimpleDateFormat dateFormat) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            Cell cell = row.getCell(0);
            if (cell != null && cell.getCellType() == CellType.STRING) {
                String dateString = cell.getStringCellValue();
                return parseDate(dateString, dateFormat);
            }
        }
        return null;
    }

    // 计算KNN邻近算法填充的值
    private static double calculateKNN(Sheet sheet, int rowIndex) {
        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(1);
                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(1);
                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);
                }
                distances.sort(Double::compare);
                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) {
        try {
            return dateFormat.parse(dateString);
        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }
    }

    // 获取下一个月的日期
    private static Date getNextMonth(Date date, int count) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
        String dateString = dateFormat.format(date);
        String[] dateArray = dateString.split("/");
        int year = Integer.parseInt(dateArray[0]);
        int month = Integer.parseInt(dateArray[1]);
        int day = Integer.parseInt(dateArray[2].substring(0, 2));
        int hour = Integer.parseInt(dateArray[2].substring(3));
        int daysInMonth = getDaysInMonth(year, month);
        if (day > daysInMonth) {
            day = daysInMonth;
        }
        if (hour >= 24) {
            hour = 0;
            day++;
            if (day > daysInMonth) {
                day = 1;
                month++;
                if (month > 12) {
                    month = 1;
                    year++;
                }
            }
        }
        month += count;
        if (month > 12) {
            month = 1;
            year++;
        }
        daysInMonth = getDaysInMonth(year, month);
        if (day > daysInMonth) {
            day = daysInMonth;
        }
        String newDateString = String.format("%04d/%02d/%02d %02d:00", year, month, day, hour);
        return parseDate(newDateString, dateFormat);
    }

    // 获取指定年份和月份的天数
    private static int getDaysInMonth(int year, int month) {
        switch (month) {
            case 2 -> {
                if (year % 4 == 0 && year % 100 != 0 || year % 400 == 0) {
                    return 29;
                } else {
                    return 28;
                }
            }
            case 4, 6, 9, 11 -> {
                return 30;
            }
            default -> {
                return 31;
            }
        }
    }
}

代码解析

  1. 识别每个月的数据组数

    • getGroupCount 方法接收工作表和日期格式化器作为参数，并返回每个月的数据组数。该方法通过遍历每一行，判断当前行日期是否与上一行日期相同，如果不同则组数加 1。

  2. 补全数据

    • 主方法中调用 getGroupCount 方法获取每个月的数据组数，并对每个月进行补全数据的操作。
    • 对于没有五组数据的月份，在该月的最后一组数据下面插入缺失的数据。
    • 插入数据的操作与原来的代码类似，只是需要根据当前行的日期计算下一个月的日期，并使用 KNN 算法填充数据。

总结

该方法可以有效地识别每个月的数据组数，并在数据不足的情况下自动插入缺失的数据，提高了 Excel 数据补全的效率和准确性。

注意：

  • 该方法假设数据按照月份顺序排列，并且每个月份的日期格式相同。
  • 数据填充的准确性取决于 KNN 算法的设置和数据本身的质量。

未来改进方向：

  • 可以将 KNN 算法替换为其他更复杂的算法，以提高数据填充的准确性。
  • 可以添加对不同日期格式的支持。
  • 可以将该方法封装成一个可复用的工具，方便其他用户使用。

希望本文能够帮助您更好地理解 Excel 数据自动补全的实现原理。如果您有任何问题或建议，欢迎留言讨论。

Excel 数据自动补全：KNN 算法与时间规律

原文地址: https://www.cveoy.top/t/topic/oXpw 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录