A data cleaning method, apparatus, device, and storage medium

Abstract

This invention discloses a data cleaning method, apparatus, device, and storage medium, relating to the field of data processing technology. The method includes: acquiring temperature data from a wave soldering equipment; determining whether the temperature data follows a normal distribution; if it does, identifying the temperature data as target data; calculating a confidence interval based on the target data; identifying target data within the confidence interval as target import data and importing it into an execution management system; using the execution management system to calculate a threshold based on the target import data and preset quantiles; determining a preset threshold range based on the threshold; performing a preset data deletion operation based on the preset threshold range to obtain cleaned data; and performing a preset data filling operation based on an automatic data filling function and the cleaned data to obtain the final data. This invention widens the gap between the upper and lower thresholds by using quantiles to calculate the threshold range, reducing the false positive rate; and improves the accuracy and processing efficiency of streaming data cleaning by using an automatic data filling function.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a data cleaning method, apparatus, device, and storage medium. Background Technology

[0002] With the rapid development of big data and the internet, data volume is exploding. In the data analysis process of digital production in the automotive manufacturing industry, raw data often needs to be cleaned to ensure data quality and accuracy. Traditional data cleaning methods, when processing streaming data, can lead to the corruption or loss of some information and cannot meet the efficiency and accuracy requirements of wave soldering data cleaning in the electronics manufacturing industry. Existing data cleaning algorithms have many limitations for MES (Manufacturing Execution System) systems and the big data and streaming data of electronics manufacturing production lines. For example, when using common methods such as column averages to fill missing values, the independence between covariates is implicitly assumed. Furthermore, using common quantile methods to identify outliers also has many limitations. Because much data in the electronics manufacturing industry follows continuous distribution functions such as normal and exponential distributions, traditional Q1 / 4 and Q3 / 4 quantiles, when dealing with large datasets (more than about two thousand data points), can misclassify multiple data points belonging to the normal distribution function and located at its boundaries as outliers due to the narrow gap between the upper and lower limits, ultimately leading to over-deletion. Summary of the Invention

[0003] In view of this, the purpose of this invention is to provide a data cleaning method, apparatus, device, and storage medium that can reduce the false positive rate and improve the accuracy and processing efficiency of streaming data cleaning. The specific solution is as follows:

[0004] In a first aspect, the present invention discloses a data cleaning method, comprising:

[0005] Acquire temperature data from the wave soldering equipment and determine whether the temperature data follows a normal distribution;

[0006] If the temperature data follows a normal distribution, then the temperature data is determined as the target data, and a confidence interval is calculated based on the target data. The target data located within the confidence interval is determined as the target import data.

[0007] The target import data is imported into the execution management system, so that the execution management system can calculate a threshold based on the target import data and a preset quantile, and determine a preset threshold range based on the threshold.

[0008] A preset data deletion operation is performed based on the preset threshold range to obtain cleaned data, and a preset data filling operation is performed based on the automatic data filling function and the cleaned data to obtain the final data.

[0009] Optionally, acquiring the temperature data of the wave soldering equipment and determining whether the temperature data follows a normal distribution includes:

[0010] The temperature data of a preset number of preheating zones are periodically retrieved from the memory of the wave soldering equipment.

[0011] A preset data type detection function is used to determine whether the temperature data is a numerical type.

[0012] The temperature data whose data type is not numerical is identified as the data to be corrected, and the data to be corrected is corrected using a preset data debugging script to obtain the corrected data;

[0013] The corrected data is determined as the new temperature data, and the process re-enters the step of using a preset data type detection function to determine whether the data type of the temperature data is numerical.

[0014] If the data type of the temperature data is numerical, then a preset distribution fitting operation is performed on the temperature data to determine whether the temperature data follows the normal distribution.

[0015] Optionally, the step of calculating a confidence interval based on the target data and determining the target data located within the confidence interval as the target import data includes:

[0016] The parameters of the normal distribution are determined based on the target data; the parameters include the standard deviation and the mean.

[0017] Calculate the probability density function value of the normal distribution;

[0018] Based on the probability density function value and the parameters, and using the Wald method and / or the likelihood method, the calculation result is obtained;

[0019] The minimum value in the calculation results is determined as the first threshold lower limit, the maximum value in the calculation results is determined as the first threshold upper limit, and the confidence interval is determined based on the first threshold lower limit and the first threshold upper limit;

[0020] The target data is sequentially determined to be within the confidence interval, and the target data within the confidence interval is determined as the target import data.

[0021] Optionally, the step of importing the target import data into the execution management system, and using the execution management system to calculate a threshold based on the target import data and a preset quantile, and determining a preset threshold range based on the threshold, includes:

[0022] The target imported data is imported into the execution management system, so that the execution management system can determine whether the data type of the target imported data is the numeric type;

[0023] If the data type of the target imported data is numerical, then the upper limit of the second threshold and the lower limit of the second threshold are calculated based on the target imported data, the 10th percentile, and the 90th percentile.

[0024] The preset threshold range is determined based on the second upper threshold and the second lower threshold.

[0025] Optionally, after importing the target import data into the execution management system, and using the execution management system to calculate a threshold based on the target import data and a preset quantile, and determining a preset threshold range based on the threshold, the method further includes:

[0026] Determine whether the target imported data is within the preset threshold range;

[0027] If the target imported data is outside the preset threshold range, the target imported data will be marked as an outlier.

[0028] Accordingly, the step of performing a preset data deletion operation based on the preset threshold range to obtain cleaned data includes:

[0029] The data marked as outliers in all the target imported data are deleted to obtain the cleaned data, and the cleaned data is stored.

[0030] Optionally, the step of performing a preset data filling operation based on the automatic data filling function and the cleaned data to obtain the final data includes:

[0031] Specify all columns in the entire data matrix table, and in each column, identify the missing positions where the cleaned data does not exist;

[0032] Using a filling model and based on the target lower limit and target upper limit, target filling data is filled into the missing positions, and the final data is obtained based on the cleaned data and the target filling data.

[0033] Optionally, before performing a preset data filling operation based on the automatic data filling function and the cleaned data to obtain the final data, the method further includes:

[0034] The cleaned data is divided into a training set, a validation set, and a test set. Centralization and uniform scaling are then performed on the training set, the validation set, and the test set to obtain the processed training set, the processed validation set, and the processed test set.

[0035] Determine the missing positions in each column of data in each of the processed training sets, and fill the missing positions with the target filling data to obtain the filled training set;

[0036] Based on the target data, it is determined whether the preset adjustment parameters meet the user's needs; wherein, the preset adjustment parameters include the upper limit of the dimension and the maximum number of iterations;

[0037] If the preset adjustment parameters meet the user's needs, then the filled model is fitted based on the filled training set and the preset adjustment parameters;

[0038] The bias value of the imputed model is calculated using the processed validation set, and the bias value is minimized to obtain the processed bias value;

[0039] The processed bias value is subjected to rank reduction to obtain the target lower limit value;

[0040] The rank reduction is performed on the data in the processed test set to obtain the target upper limit value.

[0041] Secondly, the present invention discloses a data cleaning apparatus, comprising:

[0042] The normal distribution judgment module is used to acquire the temperature data of the wave soldering equipment and determine whether the temperature data follows a normal distribution.

[0043] The confidence interval determination module is used to determine the temperature data as target data if the temperature data follows a normal distribution, calculate a confidence interval based on the target data, and determine the target data located within the confidence interval as target import data.

[0044] The threshold interval determination module is used to import the target import data into the execution management system, so that the execution management system can calculate a threshold based on the target import data and a preset quantile, and determine a preset threshold interval based on the threshold.

[0045] The data cleaning module is used to perform a preset data deletion operation based on the preset threshold range to obtain cleaned data;

[0046] The data filling module is used to perform a preset data filling operation based on the automatic data filling function and the cleaned data to obtain the final data.

[0047] Thirdly, the present invention discloses an electronic device, comprising:

[0048] Memory, used to store computer programs;

[0049] A processor is used to execute the computer program to implement the steps of the data cleaning method disclosed above.

[0050] Fourthly, the present invention discloses a computer-readable storage medium for storing a computer program; wherein, when the computer program is executed by a processor, it implements the data cleaning method disclosed above.

[0051] As can be seen, the present invention provides a data cleaning method, comprising: acquiring temperature data of a wave soldering equipment and determining whether the temperature data follows a normal distribution; if the temperature data follows a normal distribution, determining the temperature data as target data, calculating a confidence interval based on the target data, and determining the target data located within the confidence interval as target import data; importing the target import data into an execution management system, using the execution management system to calculate a threshold based on the target import data and a preset quantile, and determining a preset threshold interval based on the threshold; performing a preset data deletion operation based on the preset threshold interval to obtain cleaned data, and performing a preset data filling operation based on an automatic data filling function and the cleaned data to obtain final data. Therefore, the present invention improves the accuracy and processing efficiency of streaming data cleaning by judging and classifying data, extracting target data that follows a normal distribution, identifying outliers based on quantile values ​​to reduce the false positive rate, and automatically filling out outliers and missing values ​​based on an automatic data filling function. Attached Figure Description

[0052] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the provided drawings without creative effort.

[0053] Figure 1 This is a flowchart of a data cleaning method disclosed in this invention;

[0054] Figure 2 This is a flowchart of a specific data cleaning method disclosed in this invention;

[0055] Figure 3 This is a flowchart of a specific data cleaning method disclosed in this invention;

[0056] Figure 4This is a flowchart of the automatic data filling method disclosed in this invention;

[0057] Figure 5 This is a schematic diagram of the data filling model and subsequent prediction model of the stream processing method disclosed in this invention;

[0058] Figure 6 This is a schematic diagram illustrating the self-diagnosis principle of the temperature sensor fault disclosed in this invention.

[0059] Figure 7 This is a schematic diagram of the data cleaning device provided by the present invention;

[0060] Figure 8 This invention provides a structural diagram of an electronic device. Detailed Implementation

[0061] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0062] Currently, existing data cleaning algorithms have many limitations for MES systems and the large and streaming data of electronic manufacturing production lines. For example, when using common methods such as column averages to fill missing values, the independence of covariates is implicitly assumed. Furthermore, using common quantile methods to identify outliers also has many limitations. Because much data in the electronic manufacturing industry follows continuous distribution functions such as normal and exponential distributions, traditional Q1 / 4 and Q3 / 4 quantiles, when dealing with large datasets (more than approximately two thousand data points), can misclassify multiple data points belonging to the normal distribution function and located on its boundaries as outliers due to the narrow gap between the upper and lower limits, ultimately leading to over-deletion. Therefore, this invention provides a data cleaning method that can reduce the false positive rate and improve the accuracy and processing efficiency of streaming data cleaning.

[0063] This invention discloses a data cleaning method, see [link to relevant documentation]. Figure 1 As shown, the method includes:

[0064] Step S11: Obtain the temperature data of the wave soldering equipment and determine whether the temperature data follows a normal distribution.

[0065] In this embodiment, temperature data from the wave soldering equipment is acquired, and it is determined whether the temperature data follows a normal distribution. Specifically, temperature data from a preset number of preheating zones is periodically retrieved from the memory of the wave soldering equipment; a preset data type detection function is used to determine whether the data type of the temperature data is numerical; temperature data whose data type is not numerical is identified as data to be corrected, and a preset data debugging script is used to correct the data to be corrected to obtain corrected data; the corrected data is identified as the new temperature data, and the process re-enters the step of using the preset data type detection function to determine whether the data type of the temperature data is numerical; if the data type of the temperature data is numerical, a preset distribution fitting operation is performed on the temperature data to determine whether the temperature data follows the normal distribution.

[0066] For example, when a wave soldering production line experiences multiple alarm shutdowns due to sensor, fixture, or equipment malfunctions, the temperature monitoring data for the preheating zone output by the digital MES system becomes abnormal. This necessitates data identification, classification, and cleaning of a dataset of hundreds of thousands of data points daily. Therefore, this invention identifies the data distribution of the preheating zone during the process design phase, confirming whether it follows a log-normal distribution. Then, hundreds of thousands of data points are exported from the MES system to a more common format, such as a CSV file (comma-separated value format), using Python and JMP software. Specifically, every 50 milliseconds, temperature data for 10 preheating zones (125 data points per zone, totaling 1250 data points) are exported from the wave soldering equipment's memory, named "XXXXY_XXMXXD_XXXXXXXX_Preheating_Temperature.csv", and automatically saved to the server. Import necessary libraries into Python, including NumPy (Numerical Python, an extension library for the Python language) for numerical computation and SciPy for scientific computing. Then, use Pandas to convert the data into a clean and ordered DataFrame. Use Python to read the CSV file and create a DataFrame of the preheating temperature zone data. Use the `isinstance()` function to check if the data type of all temperature data conforms to the numeric type. If there is any temperature data that does not conform to the numeric type, output a prompt and log it to the log file. Simultaneously, use a Python script to automatically debug and correct temperature data with incorrect formatting. After correcting the data type, rerun the `isinstance()` function for verification. If the verification passes, the data type is determined to be numeric. When the data type conforms to the numeric type, perform distribution fitting on the 1250 data points mentioned above to determine if they follow a log-normal distribution.

[0067] Step S12: If the temperature data follows a normal distribution, the temperature data is determined as the target data, and a confidence interval is calculated based on the target data. The target data located within the confidence interval is determined as the target import data.

[0068] In this embodiment, temperature data from the wave soldering equipment is acquired, and it is determined whether the temperature data follows a normal distribution. If the temperature data follows a normal distribution, the temperature data is identified as target data, and a confidence interval is calculated based on the target data. Target data located within the confidence interval is identified as target import data. Specifically, the parameters of the normal distribution are determined based on the target data; these parameters include the standard deviation and the mean. The probability density function value of the normal distribution is calculated. Based on the probability density function value and the parameters, calculations are performed using the Wald method and / or the likelihood method to obtain the calculation results. The minimum value in the calculation results is identified as a first lower threshold, and the maximum value in the calculation results is identified as a first upper threshold. The confidence interval is determined based on the first lower threshold and the first upper threshold. It is then sequentially determined whether the target data is located within the confidence interval, and the target data located within the confidence interval is identified as target import data.

[0069] Understandably, if the data does not conform to a log-normal distribution, it is marked as outlier data for subsequent processing. For example, for outlier data, a PID (Proportional Integral Derivative) outlier handling program is invoked for automatic diagnosis and debugging. If the temperature data follows a normal distribution, it is determined as the target data. Based on the log-normal temperature curve fitted to the standard temperature values ​​of all selected (e.g., 10) preheating temperature zones, a 95% confidence interval is calculated using the Wald and / or likelihood methods. The minimum lower limit is automatically determined as the first threshold lower limit based on the data volume, and the maximum upper limit is determined as the first threshold upper limit. The confidence interval is determined based on the first threshold lower limit and the first threshold upper limit. If the target data is an outlier exceeding the confidence interval threshold range, PID automatic diagnosis and debugging are used. Specifically, after determining whether the data follows a log-normal distribution, the system calculates whether the temperature conforms to a log-normal distribution. The specific statement is as follows: `def is_temperature_in_confidence_interval(temperatures, a=1, b=1)`. If it does not conform to a log-normal distribution, it is marked as outlier data. If it does conform, the system continues to calculate the parameters of the log-normal distribution, including the standard deviation and mean. It also calculates the probability density function value of the log-normal distribution. The system uses a normality handling program in PID control to automatically diagnose and adjust the target data. PID control automatically selects and uses an interval fitting value that conforms to the log-normal distribution. Based on the probability density function value and the parameters, it uses the Wald method and / or the likelihood method to calculate the result. The Wald method is chosen when the data volume is large, the likelihood method is chosen when the data volume is small, and the Wald method and the likelihood method can be combined when the data volume is uncertain. Determine whether the target data is within the confidence interval. If the target data is within the confidence interval, it is identified as the target import data and marked as "normal production data". Import the target import data into the MES system in sequence according to the serial number. If the target data is not within the confidence interval, call the abnormal operation handling program to process it.

[0070] It should be noted that the formula for using the likelihood method is as follows:

[0071]

[0072] The formula for calculating the Wald method is as follows:

[0073]

[0074] The formula for calculating the upper confidence limit is as follows:

[0075]

[0076] The formula for calculating the lower confidence limit is as follows:

[0077]

[0078] In the above formula, μ is the population mean, t is the critical value of the distribution, α is the significance level, s is the standard deviation, and n is the sample size.

[0079] Understandably, if the PID controller cannot process values ​​exceeding the upper and lower limits of the confidence interval, a message is sent to the equipment management platform in the production system. Equipment engineers then remotely analyze the data to determine if the problem stems from the temperature sensor, fixture, or wave soldering equipment. Specifically, before importing the data into the MES, the PID module identifies the temperature data collected by all sensors in the preheating zone and fits the temperature data in real time to confirm whether the temperature data conforms to the specified preheating zone curve (e.g., within the upper and lower limits of the 95% confidence interval). Data within the confidence interval is identified as normal production data; data outside the range is identified as sensor or equipment fault data. This normal production flow data (temperature data from the preheating zone) is then imported into the MES system according to its time series labeling.

[0080] Step S13: Import the target import data into the execution management system, and use the execution management system to calculate a threshold based on the target import data and a preset quantile, and determine a preset threshold range based on the threshold.

[0081] In this embodiment, after calculating the confidence interval based on the target data and determining the target data located within the confidence interval as the target import data, the target import data is imported into the execution management system. The execution management system then calculates a threshold based on the target import data and a preset quantile, and determines a preset threshold interval based on the threshold.

[0082] Understandably, the target data for the preheating temperature zone is exported from the MES system in .csv format, data = XXXXY_XXMXXD_XXXXXXXX_Preheating_Temperature.csv; then the isinstance() function is used to determine whether the data type of all target imported data is numeric. If it does not, it is marked as an abnormal concurrency message; if it is numeric, a threshold is calculated based on the target imported data and the preset quantile, and a preset threshold range is determined based on the threshold.

[0083] Step S14: Perform a preset data deletion operation based on the preset threshold range to obtain cleaned data, and perform a preset data filling operation based on the automatic data filling function and the cleaned data to obtain the final data.

[0084] In this embodiment, the target import data is imported into the execution management system. The execution management system calculates a threshold based on the target import data and a preset quantile, determines a preset threshold range based on the threshold, and then performs a preset data deletion operation based on the preset threshold range to obtain cleaned data. Finally, a preset data filling operation is performed based on an automatic data filling function and the cleaned data to obtain the final data. Specifically, all columns are specified from the entire data matrix table, and empty positions where the cleaned data does not exist are determined in each column. The target filling data is then filled into the empty positions using a filling model based on the target lower limit and target upper limit. The final data is obtained based on the cleaned data and the target filling data.

[0085] Understandably, before obtaining the final data, a preset data deletion operation is performed based on the preset threshold range to obtain cleaned data. Then, a preset data filling operation is performed based on the automatic data filling function and the cleaned data. The cleaned data is then divided into a training set, a validation set, and a test set. Centralization and uniform scaling are performed on the training set, the validation set, and the test set to obtain processed training sets, processed validation sets, and processed test sets. Finally, the missing positions in each column of data in each processed training set are determined, and the target filling data is used to fill these missing positions. The process involves obtaining a padded training set; determining whether preset adjustment parameters meet user requirements based on the target padded data; wherein the preset adjustment parameters include an upper dimension limit and a maximum number of iterations; if the preset adjustment parameters meet user requirements, fitting the padded model based on the padded training set and the preset adjustment parameters; calculating the bias value of the padded model using the processed validation set and minimizing the bias value to obtain a processed bias value; performing rank reduction on the processed bias value to obtain the target lower limit; and performing rank reduction on the data in the processed test set to obtain the target upper limit.

[0086] This invention employs a self-developed PID automatic diagnosis and debugging method, combined with 10th and 90th quantile methods and an ADF (Automated Data Function) automatic data filling function to identify and clean outliers in data, effectively improving the accuracy and reliability of streaming data processing. It can automatically identify and clean outliers, reducing manual processing workload; it can process multiple data types simultaneously, making it suitable for various data processing scenarios; and it can handle situations where missing values ​​and outliers coexist, improving the comprehensiveness of data processing.

[0087] As can be seen, the present invention provides a data cleaning method, comprising: acquiring temperature data of a wave soldering equipment and determining whether the temperature data follows a normal distribution; if the temperature data follows a normal distribution, determining the temperature data as target data, calculating a confidence interval based on the target data, and determining the target data located within the confidence interval as target import data; importing the target import data into an execution management system, using the execution management system to calculate a threshold based on the target import data and a preset quantile, and determining a preset threshold interval based on the threshold; performing a preset data deletion operation based on the preset threshold interval to obtain cleaned data, and performing a preset data filling operation based on an automatic data filling function and the cleaned data to obtain final data. Therefore, the present invention improves the accuracy and processing efficiency of streaming data cleaning by judging and classifying data, extracting target data that follows a normal distribution, identifying outliers based on quantile values ​​to reduce the false positive rate, and automatically filling out outliers and missing values ​​based on an automatic data filling function.

[0088] See Figure 2 As shown, this embodiment of the invention discloses a data cleaning method. Compared with the previous embodiment, this embodiment further explains and optimizes the technical solution.

[0089] Step S21: Obtain the temperature data of the wave soldering equipment and determine whether the temperature data follows a normal distribution.

[0090] Step S22: If the temperature data follows a normal distribution, the temperature data is determined as the target data, and a confidence interval is calculated based on the target data. The target data located within the confidence interval is determined as the target import data.

[0091] Step S23: Import the target import data into the execution management system, and use the execution management system to determine whether the data type of the target import data is the numeric type.

[0092] In this embodiment, after calculating the confidence interval based on the target data and determining the target data located within the confidence interval as the target import data, the target import data is imported into the execution management system. The execution management system is then used to determine whether the data type of the target import data is numeric. For example, the isinstance() function is used to determine whether the data type of all target import data is numeric. If the data type of the target import data is not numeric, it is marked as an abnormal concurrency message.

[0093] Step S24: If the data type of the target imported data is the numerical type, then calculate the second threshold upper limit and the second threshold lower limit based on the target imported data, the 10th percentile and the 90th percentile.

[0094] In this embodiment, after the execution management system determines whether the data type of the target imported data is numeric, if the data type of the target imported data is numeric, then a second upper threshold and a second lower threshold are calculated based on the target imported data, the 10th percentile, and the 90th percentile. It can be understood that the nanpercentile() function is used to calculate the 10th percentile of all data; the nanpercentile() function is used to calculate the 90th percentile of all data. The formula for calculating the lower threshold corresponding to the 10th percentile is as follows: threshold_min = pct10 - 3 * (pct90 - pct10); the formula for calculating the upper threshold corresponding to the 90th percentile is as follows: threshold_max = pct90 + 3 * (pct90 - pct10).

[0095] Quantiles are numerical points that divide the probability distribution range of a random variable into several equal parts. Commonly used quantiles include the median (i.e., quartiles), quartiles, and percentiles. This invention uses 10% and 90% quantiles, and changes 1.5*IQR (Interquartile Range) in the Wisker upper and lower bound formulas to 3*IQR, widening the gap between the upper and lower thresholds and avoiding the problem of over-judging outliers.

[0096] Step S25: Determine the preset threshold range based on the second threshold upper limit and the second threshold lower limit.

[0097] In this embodiment, after calculating the second upper threshold and the second lower threshold based on the target imported data, the 10th percentile, and the 90th percentile, the preset threshold interval is determined based on the second upper threshold and the second lower threshold. It is then determined whether the target imported data is within the preset threshold interval; if the target imported data is outside the preset threshold interval, the target imported data is marked as an outlier. It can be understood that the `np.where()` function is used to mark data below the lower threshold `threshold_min` as outliers; and the `np.where()` function is used to mark data above the upper threshold `threshold_max` as IM (Induced Missing Value) outliers.

[0098] Step S26: Perform a preset data deletion operation based on the preset threshold range to obtain cleaned data, and perform a preset data filling operation based on the automatic data filling function and the cleaned data to obtain the final data.

[0099] In this embodiment, after determining the preset threshold range based on the second threshold upper limit and the second threshold lower limit, a preset data deletion operation is performed based on the preset threshold range to obtain cleaned data. Then, a preset data filling operation is performed based on the automatic data filling function and the cleaned data to obtain the final data. Data marked as outliers in all the target imported data is deleted to obtain the cleaned data. The cleaned data is then stored, and a preset data filling operation is performed based on the automatic data filling function and the cleaned data to obtain the final data. Figure 3 As shown, the quantile function is called to identify outliers, and after deleting outliers, the ADI function is called to automatically fill in the data.

[0100] Understandably, the following steps are taken: using the `numpy.delete()` function to remove all outliers; saving all data as a CSV file and importing it into the JMP software; calling the automatic data filling method in the SAS JMP software's JSL script editor via the PyWin32 module; calling the `Scoring Impute()` function in JMP to provide streaming functionality for the automatic data filling function ADF, ensuring its directional and dynamic data transmission and avoiding situations where there is no data or the amount of data is unreasonable; and using the `Matrix()` function to select all columns in the preheating temperature zone from the entire data matrix table, and then automatically filling all missing values ​​in the columns using the automatic data filling function ADF (for example, using an extended method of matrix completion from the Netflix Challenge).

[0101] like Figure 4As shown, before performing preset data imputation operations based on the automatic data imputation function and the cleaned data to obtain the final data, all data are divided into training sets, validation sets, and test sets. Each dataset is centered and uniformly scaled. For each column of data in each dataset (training / validation / test set), missing values ​​are found, and IM values ​​(introduced missing values) are added to these missing values ​​to ensure the smoothness of the calculation process and avoid errors or termination during calculation. For the training set, an imputation model is fitted according to the adjustment parameters (parameters that need to be set and adjusted before the automatic data imputation method begins: upper dimension limit, maximum number of iterations, etc.). The IM values ​​are used to determine and judge whether these adjustment parameters are optimal (optimal value or optimal solution). If not, the adjustment parameters are reset, and the process is repeated until the optimal value is found. If it is the optimal value, the bias of each imputation model is calculated using the validation set data (a large bias will lead to misfit), and the bias value is minimized. Then, rank reduction is performed using these bias values ​​as the lower limit. Rank reduction is performed using the data in the test set. Rank minimization is used to calibrate the imputation model for streaming data, preventing overfitting, and this rank is used as an upper limit. After validation, the imputation model is used, and missing values ​​are automatically imputed based on the upper and lower limits. The Python software is launched using the "work with Python" feature in JMP; a data cleaning log and report are created: `log_file_name="XXXX data_lod.txt"`, recording key information and results of the entire data cleaning process; each automatic data imputation process is converted into Python code for further engineering. The automatic data imputation method used in this invention is more accurate than common methods such as column mean. It automatically fits the training set data, and after obtaining the fitted model, it uses the test set data for validation, avoiding problems such as misfit and overfitting. Furthermore, this method set, combined with Python programming, has engineered the entire process, making it easy to port to other production lines or convert to a fully automated process.

[0102] like Figure 5 As shown, the automatic data imputation method used in this invention avoids information corruption and loss in the dataset when processing streaming data, ensuring the accuracy of the entire data cleaning process. First, a model is fitted using the training set and the introduced missing values ​​IM. Then, data is generated based on this model and imputed to the previous training, validation, and test sets using a streaming imputation method.

[0103] Other examples Figure 6 As shown, through the signal input from the temperature sensor, the chip in the PID controller determines the overall operating area and abnormal operating area of ​​the wave soldering equipment based on the voltage.

[0104] This invention automatically diagnoses and classifies the large amounts of streaming data from modern digital wave soldering production lines, automatically segmenting and extracting streaming data during normal production; it identifies outliers within quantile ranges (10% and 90%) of the streaming data, reducing the false positive rate; and it uses an automatic data filling method to automatically fill in outliers and missing values, improving the accuracy and efficiency of streaming data cleaning. This invention can be applied to big data processing, automated data management, data processing and optimization in the automotive parts manufacturing industry, streaming data processing, and digital wave soldering production lines.

[0105] For details regarding steps S21 and S22, please refer to the corresponding content disclosed in the foregoing embodiments, which will not be repeated here.

[0106] As can be seen, this embodiment of the application acquires temperature data from a wave soldering equipment and determines whether the temperature data follows a normal distribution. If the temperature data follows a normal distribution, the temperature data is identified as target data, and a confidence interval is calculated based on the target data. The target data located within the confidence interval is identified as target imported data. The target imported data is imported into an execution management system, which then determines whether the data type of the target imported data is numerical. If the data type of the target imported data is numerical, a second upper threshold and a second lower threshold are calculated based on the target imported data, the 10th percentile, and the 90th percentile. A preset threshold interval is determined based on the second upper threshold and the second lower threshold. A preset data deletion operation is performed based on the preset threshold interval to obtain cleaned data. A preset data filling operation is then performed based on an automatic data filling function and the cleaned data to obtain the final data. This reduces the false positive rate and improves the accuracy and processing efficiency of stream data cleaning.

[0107] See Figure 7 As shown, this embodiment of the invention also discloses a data cleaning apparatus, comprising:

[0108] The normal distribution judgment module 11 is used to acquire the temperature data of the wave soldering equipment and determine whether the temperature data follows a normal distribution.

[0109] The confidence interval determination module 12 is used to determine the temperature data as target data if the temperature data follows a normal distribution, calculate a confidence interval based on the target data, and determine the target data located within the confidence interval as target import data.

[0110] The threshold interval determination module 13 is used to import the target import data into the execution management system, so as to use the execution management system to calculate the threshold based on the target import data and the preset quantile, and determine the preset threshold interval based on the threshold.

[0111] The data cleaning module 14 is used to perform a preset data deletion operation based on the preset threshold range to obtain cleaned data;

[0112] The data filling module 15 is used to perform a preset data filling operation based on the automatic data filling function and the cleaned data to obtain the final data.

[0113] As can be seen, the present invention includes: acquiring temperature data from a wave soldering equipment and determining whether the temperature data follows a normal distribution; if the temperature data follows a normal distribution, determining the temperature data as target data, calculating a confidence interval based on the target data, and determining the target data located within the confidence interval as target import data; importing the target import data into an execution management system, using the execution management system to calculate a threshold based on the target import data and a preset quantile, and determining a preset threshold interval based on the threshold; performing a preset data deletion operation based on the preset threshold interval to obtain cleaned data, and performing a preset data filling operation based on an automatic data filling function and the cleaned data to obtain final data. Therefore, the present invention improves the accuracy and processing efficiency of streaming data cleaning by judging and classifying data, extracting target data that follows a normal distribution, identifying outliers based on quantile values ​​to reduce the false positive rate, and automatically filling out outliers and missing values ​​using an automatic data filling function.

[0114] In some specific embodiments, the normal distribution determination module 11 specifically includes:

[0115] The temperature data acquisition unit is used to periodically acquire temperature data of a preset number of preheating temperature zones from the memory of the wave soldering equipment;

[0116] The first numerical type judgment unit is used to determine whether the data type of the temperature data is numerical using a preset data type detection function;

[0117] The data to be corrected determination unit is used to determine the temperature data whose data type is not the numerical type as the data to be corrected;

[0118] A data correction unit is used to correct the data to be corrected using a preset data debugging script to obtain corrected data.

[0119] A new temperature data determination unit is used to determine the corrected data as the new temperature data and re-enter the step of using a preset data type detection function to determine whether the data type of the temperature data is numerical.

[0120] The normal distribution determination unit is used to perform a preset distribution fitting operation on the temperature data if the data type of the temperature data is numerical, so as to determine whether the temperature data follows the normal distribution.

[0121] In some specific embodiments, the confidence interval determination module 12 specifically includes:

[0122] A parameter determination unit is used to determine the parameters of the normal distribution based on the target data; the parameters include the standard deviation and the mean.

[0123] A probability density function value calculation unit is used to calculate the probability density function value of the normal distribution;

[0124] The calculation result acquisition unit is used to perform calculations based on the probability density function value and the parameters, using the Wald method and / or the likelihood method, to obtain the calculation result;

[0125] The first threshold lower limit determination unit is used to determine the minimum value in the calculation result as the first threshold lower limit;

[0126] The first threshold upper limit determination unit is used to determine the maximum value in the calculation result as the first threshold upper limit;

[0127] A confidence interval determination unit is configured to determine the confidence interval based on the first lower threshold and the first upper threshold;

[0128] The first data range determination unit is used to sequentially determine whether the target data is located within the confidence interval;

[0129] The target import data determination unit is used to determine the target data located within the confidence interval as the target import data.

[0130] In some specific embodiments, the threshold interval determination module 13 specifically includes:

[0131] The second numerical type judgment unit is used to import the target imported data into the execution management system, so as to use the execution management system to determine whether the data type of the target imported data is the numerical type;

[0132] The second threshold calculation unit is used to calculate the upper limit of the second threshold and the lower limit of the second threshold based on the target imported data, the 10th percentile and the 90th percentile if the data type of the target imported data is the numerical type.

[0133] A preset threshold interval determination unit is used to determine the preset threshold interval based on the second threshold upper limit and the second threshold lower limit;

[0134] The second data range determination unit is used to determine whether the target imported data is within the preset threshold range;

[0135] An outlier marking unit is used to mark the target imported data as an outlier if the target imported data is outside the preset threshold range.

[0136] In some specific embodiments, the data cleaning module 14 specifically includes:

[0137] The data cleaning unit is used to delete the data marked as outliers from all the target imported data to obtain the cleaned data, and to store the cleaned data.

[0138] In some specific embodiments, the data filling module 15 specifically includes:

[0139] A dataset partitioning unit is used to divide the cleaned data into a training set, a validation set, and a test set.

[0140] The dataset processing unit is used to perform centralization and uniform scaling on the training set, the validation set, and the test set to obtain the processed training set, the processed validation set, and the processed test set.

[0141] The first missing position determination unit is used to determine the missing position in each column of data in each of the processed training sets.

[0142] The first data filling unit is used to fill the missing positions with the target filling data to obtain the filled training set;

[0143] A preset adjustment parameter judgment unit is used to determine whether the preset adjustment parameters meet the user's needs based on the target filling data; wherein, the preset adjustment parameters include the upper limit of the dimension and the maximum number of iterations;

[0144] A filling model determination unit is used to fit the filling model based on the filled training set and the preset adjustment parameters if the preset adjustment parameters meet the user's needs.

[0145] A bias value calculation unit is used to calculate the bias value of the imputed model using the processed validation set;

[0146] A bias value minimization unit is used to minimize the bias value to obtain the processed bias value;

[0147] The target lower limit value determination unit is used to perform rank reduction on the processed bias value to obtain the target lower limit value;

[0148] The target upper limit value determination unit is used to perform the rank reduction on the data in the processed test set to obtain the target upper limit value;

[0149] The column specification cell is used to specify all columns from the entire data matrix table;

[0150] The second missing position determination unit is used to determine the missing position in each column where the cleaned data does not exist;

[0151] The second data filling unit is used to fill the missing positions with target filling data based on the target lower limit and the target upper limit using a filling model;

[0152] The final data acquisition unit is used to obtain the final data based on the cleaned data and the target filling data.

[0153] Furthermore, embodiments of the present invention also provide an electronic device. Figure 8 This is a structural diagram of an electronic device 20 according to an exemplary embodiment. The content of the diagram should not be construed as limiting the scope of the invention.

[0154] Figure 8 This is a schematic diagram of the structure of an electronic device 20 provided in an embodiment of the present invention. Specifically, the electronic device 20 may include: at least one processor 21, at least one memory 22, a power supply 23, a communication interface 24, an input / output interface 25, and a communication bus 26. The memory 22 stores a computer program, which is loaded and executed by the processor 21 to implement the relevant steps in the data cleaning method disclosed in any of the foregoing embodiments. Furthermore, the electronic device 20 in this embodiment may specifically be an electronic computer.

[0155] In this embodiment, the power supply 23 is used to provide operating voltage for each hardware device on the electronic device 20; the communication interface 24 can create a data transmission channel between the electronic device 20 and external devices, and the communication protocol it follows can be any communication protocol applicable to the technical solution of this invention, and is not specifically limited here; the input / output interface 25 is used to acquire external input data or output data to the outside world, and its specific interface type can be selected according to specific application needs, and is not specifically limited here.

[0156] In addition, the memory 22, as a carrier for resource storage, can be a read-only memory, random access memory, disk or optical disk, etc. The resources stored thereon can include operating system 221, computer program 222, etc., and the storage method can be temporary storage or permanent storage.

[0157] The operating system 221 is used to manage and control the various hardware devices on the electronic device 20 and the computer program 222, which may be Windows Server, Netware, Unix, Linux, etc. In addition to including a computer program capable of performing the data cleaning method executed by the electronic device 20 as disclosed in any of the foregoing embodiments, the computer program 222 may further include computer programs capable of performing other specific tasks.

[0158] Furthermore, this embodiment of the invention also discloses a computer-readable storage medium storing a computer program, which, when loaded and executed by a processor, implements the data cleaning method steps disclosed in any of the foregoing embodiments.

[0159] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For the apparatus disclosed in the embodiments, since it corresponds to the method disclosed in the embodiments, the description is relatively simple; relevant parts can be referred to in the method section.

[0160] Finally, it should be noted that in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0161] The above provides a detailed description of the data cleaning method, apparatus, device, and storage medium provided by the present invention. Specific examples have been used to illustrate the principles and implementation methods of the present invention. The description of the above embodiments is only for the purpose of helping to understand the method and core ideas of the present invention. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of the present invention. Therefore, the content of this specification should not be construed as a limitation of the present invention.

Claims

1. A data cleaning method, characterized in that, include: Acquire temperature data from the wave soldering equipment and determine whether the temperature data follows a normal distribution; If the temperature data follows a normal distribution, then the temperature data is determined as the target data, and a confidence interval is calculated based on the target data. The target data located within the confidence interval is determined as the target import data. The target import data is imported into the execution management system, so that the execution management system can calculate a threshold based on the target import data and a preset quantile, and determine a preset threshold range based on the threshold. A preset data deletion operation is performed based on the preset threshold range to obtain cleaned data, and a preset data filling operation is performed based on the automatic data filling function and the cleaned data to obtain the final data; The step of acquiring temperature data from the wave soldering equipment and determining whether the temperature data follows a normal distribution includes: The temperature data of a preset number of preheating zones are periodically retrieved from the memory of the wave soldering equipment. A preset data type detection function is used to determine whether the temperature data is a numerical type. The temperature data whose data type is not numerical is identified as the data to be corrected, and the data to be corrected is corrected using a preset data debugging script to obtain the corrected data; The corrected data is determined as the new temperature data, and the process re-enters the step of using a preset data type detection function to determine whether the data type of the temperature data is numerical. If the data type of the temperature data is numerical, then a preset distribution fitting operation is performed on the temperature data to determine whether the temperature data follows the normal distribution.

2. The data cleaning method according to claim 1, characterized in that, The step of calculating a confidence interval based on the target data, and determining the target data located within the confidence interval as the target import data, includes: The parameters of the normal distribution are determined based on the target data; the parameters include the standard deviation and the mean. Calculate the probability density function value of the normal distribution; Based on the probability density function value and the parameters, and using the Wald method and / or the likelihood method, the calculation result is obtained; The minimum value in the calculation results is determined as the first threshold lower limit, the maximum value in the calculation results is determined as the first threshold upper limit, and the confidence interval is determined based on the first threshold lower limit and the first threshold upper limit; The target data is sequentially determined to be within the confidence interval, and the target data within the confidence interval is determined as the target import data.

3. The data cleaning method according to claim 1, characterized in that, The step of importing the target import data into the execution management system, and then using the execution management system to calculate a threshold based on the target import data and a preset quantile, and determining a preset threshold range based on the threshold, includes: The target imported data is imported into the execution management system, so that the execution management system can determine whether the data type of the target imported data is the numeric type; If the data type of the target imported data is numerical, then the upper limit of the second threshold and the lower limit of the second threshold are calculated based on the target imported data, the 10th percentile, and the 90th percentile. The preset threshold range is determined based on the second upper threshold and the second lower threshold.

4. The data cleaning method according to any one of claims 1 to 3, characterized in that, After importing the target import data into the execution management system, and using the execution management system to calculate a threshold based on the target import data and a preset quantile, and determining a preset threshold range based on the threshold, the process further includes: Determine whether the target imported data is within the preset threshold range; If the target imported data is outside the preset threshold range, the target imported data will be marked as an outlier. Accordingly, the step of performing a preset data deletion operation based on the preset threshold range to obtain cleaned data includes: The data marked as outliers in all the target imported data are deleted to obtain the cleaned data, and the cleaned data is stored.

5. The data cleaning method according to claim 4, characterized in that, The process of performing a preset data filling operation based on the automatic data filling function and the cleaned data to obtain the final data includes: Specify all columns in the entire data matrix table, and in each column, identify the missing positions where the cleaned data does not exist; Using a filling model and based on the target lower limit and target upper limit, target filling data is filled into the missing positions, and the final data is obtained based on the cleaned data and the target filling data.

6. The data cleaning method according to claim 5, characterized in that, Before the step of performing a preset data filling operation based on the automatic data filling function and the cleaned data to obtain the final data, the method further includes: The cleaned data is divided into a training set, a validation set, and a test set. Centralization and uniform scaling are then performed on the training set, the validation set, and the test set to obtain the processed training set, the processed validation set, and the processed test set. Determine the missing positions in each column of data in each of the processed training sets, and fill the missing positions with the target filling data to obtain the filled training set; Based on the target data, it is determined whether the preset adjustment parameters meet the user's needs; wherein, the preset adjustment parameters include the upper limit of the dimension and the maximum number of iterations; If the preset adjustment parameters meet the user's needs, then the filled model is fitted based on the filled training set and the preset adjustment parameters; The bias value of the imputed model is calculated using the processed validation set, and the bias value is minimized to obtain the processed bias value; The processed bias value is subjected to rank reduction to obtain the target lower limit value; The rank reduction is performed on the data in the processed test set to obtain the target upper limit value.

7. A data cleaning apparatus, characterized in that, include: The normal distribution judgment module is used to acquire the temperature data of the wave soldering equipment and determine whether the temperature data follows a normal distribution. The confidence interval determination module is used to determine the temperature data as target data if the temperature data follows a normal distribution, calculate a confidence interval based on the target data, and determine the target data located within the confidence interval as target import data. The threshold interval determination module is used to import the target import data into the execution management system, so that the execution management system can calculate a threshold based on the target import data and a preset quantile, and determine a preset threshold interval based on the threshold. The data cleaning module is used to perform a preset data deletion operation based on the preset threshold range to obtain cleaned data; The data filling module is used to perform a preset data filling operation based on the automatic data filling function and the cleaned data to obtain the final data; The normal distribution determination module includes: The temperature data acquisition unit is used to periodically acquire temperature data of a preset number of preheating temperature zones from the memory of the wave soldering equipment; The first numerical type judgment unit is used to determine whether the data type of the temperature data is numerical using a preset data type detection function; The data to be corrected determination unit is used to determine the temperature data whose data type is not the numerical type as the data to be corrected; A data correction unit is used to correct the data to be corrected using a preset data debugging script to obtain corrected data. A new temperature data determination unit is used to determine the corrected data as the new temperature data and re-enter the step of using a preset data type detection function to determine whether the data type of the temperature data is numerical. The normal distribution determination unit is used to perform a preset distribution fitting operation on the temperature data if the data type of the temperature data is numerical, so as to determine whether the temperature data follows the normal distribution.

8. An electronic device, characterized in that, include: Memory, used to store computer programs; A processor for executing the computer program to implement the steps of the data cleaning method as described in any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, Used to store computer programs; wherein, when the computer programs are executed by a processor, they implement the data cleaning method as described in any one of claims 1 to 6.

Images